RedShiftでETL処理向けの擬似JSON型カラムViewを作成する

こんにちは。データサイエンティスト兼、データ基盤エンジニアのshobyです。

今回は、RedShiftでETL処理向けに、擬似的にJSON型のカラムを持つViewを作成する方法をご紹介します。

擬似JSON型のカラムを使用することで、データマートへデータを移す際のデータ量を圧縮することができ、処理を効率化することができます。

概要

  • RedShiftにおけるJSON型の対応状況
  • 擬似的なJSON型の用途
  • Viewを用いたETL処理
  • 擬似JSONカラムの作成
  • TVISION INSIGHTSでの活用事例

RedShiftにおけるJSON型の対応状況

RedShiftはPostgreSQL互換の分散型DBですが、JSON型には対応していません*1。

docs.aws.amazon.com

ただし、JSON形式の文字列に対する操作を行う関数は存在するため、 JSON形式の文字列をVARCHAR型で保持することで、擬似的なJSON型として扱うことができます。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/json-functions.html

擬似的なJSON型の用途

擬似的なJSON型は、データマートへデータを移す際のデータ圧縮に効果を発揮します。

RedShift単体で考えた場合、JSON型が適正な用途はあまり多くありません。 スパースで分散処理の必要がないデータを圧縮して保持する場合など、ごく限られたケースでは有用ですが、 大量のデータを並列分散処理できるというRedShiftのメリットが生かせなくなります。

しかし、RedShift外まで考えた場合、データマート用の別DBと連携する場合などには有用です。

RedShiftに格納している多量のデータをデータマートに移す際には、データ転送効率を考える必要があります。 この際、擬似JSON型を利用することでデータ量を圧縮できる場合は、データ転送量を抑えてETL処理を効率化することができます。

具体的には、効率を考えて非正規化しているテーブルのデータを、JSON型でまとめるケースなどです。 以下のようなケースでは、3行のレコードを1行にまとめることができ、転送量を抑えることができます。

非正規化テーブル

common_value different_value
1 2
1 3
1 4

擬似JSON型テーブル

common_value different_values
1 "[2,3,4]"

Viewを用いたETL処理

RedShiftのデータを別DBのデータマートに移す場合、Viewを用いることで、ETL処理のうち、抽出（Extract）と変換（Transform）処理を、RedShift内で完結させることができます。

View側でSQLを用いて対象データの抽出と、必要な形式への変換を行うことができるため、ETL処理を行うbatchのコードはロードのみのシンプルな実装にすることができます。

擬似JSONカラムの作成

LISTAGG関数を用いることで、既存テーブルに格納されているレコードをまとめて、擬似JSON型のカラムを作成することができます。

docs.aws.amazon.com

具体的なコードは以下のようになります。

SELECT
  '[' || LISTAGG(different_value, ',')
  WITHIN GROUP (ORDER BY different_value) || ']' AS different_values
FROM data_table
GROUP BY
  common_value;

TVISION INSIGTHSでの活用事例

TVISION INSIGHTSでは、データウェアハウスにはRedShift、アプリケーション向けのデータマートはMySQLという構成を採用しており、 RedShiftとMySQL間のETL処理向けに、擬似JSON型カラムを持つのViewを作成し、使用しています。

この構成を使用することで、転送対象のデータ量の圧縮と、ETL処理の実装における作業分担による効率化を行うことができました。

TVISION INSIGHTSの分析用アプリケーションでは、TV視聴者を多くの属性に分けて分析を行うことができるため、 セグメントごとのデータを擬似JSON型カラムで表すことで、データ量の圧縮に貢献しました。

セグメント単位のデータ

segment_name value
all 2
men 3
women 4

擬似JSON型カラムView

values
{"all": 2, "men": 3, "women": 4}

また、このような構成を取る事により、データサイエンティストがViewでデータを用意し、ETLバッチとデータマート側をエンジニアが実装、といった形で分業化を進めることができました。

まとめ

ETL処理向けに擬似JSON型のカラムを採用したViewを使用することで、データマートへデータを移す際のデータ量を圧縮することができ、処理を効率化することができます。

また、ETL処理の責務のうち抽出・変換をView、ロードをbatch側に持たせることで、実装作業の分担と効率化を行うことができます。

*1:2018-10-26現在