Redshift と DynamoDB のゼロ ETL 統合後にQuickSight で分析した際のデータ型エラーへの対応

事象

DynamoDB のデータを Zero-ETL を利用して Redshift に連携し QuickSight で分析しようとしたところ、「サポートされていないデータ型」のため取り込まれませんでした。

原因

DynamoDBの「DynamoDB JSON」データをRedshiftに連携させると、SUPER型としてマッピングされ、QuickSightはSUPER型に対応しておらず、取り込みに失敗していました。

対策

マテリアライズドビューによる構造化

JSON 内の要素をそれぞれ抽出し、Redshift 上の適切なデータ型にキャストし、マテリアライズドビューを作成します。

-- 書き込み可能なデータベースを作成
CREATE DATABASE "DB名";

-- 大文字小文字を区別する識別子を有効にする
SET enable_case_sensitive_identifier TO true;

-- マテリアライズドビューを作成
CREATE MATERIALIZED VIEW my_structured_data
-- 元テーブルの変更を自動的に取り込むようにする
AUTO REFRESH YES
AS
SELECT
    -- SUPER 型の JSON からそれぞれの項目を抽出し、Redshift の型にキャストする
    value.id."N"::integer   AS id,
    value.sample_key."N"::integer     AS sample_key,
    value.sample."M".message."S"   AS sample,
    value.created_at."S"::timestamp AS created_at,
FROM
    "DB名".public."テーブル名"
WHERE
    "任意の内容";

上記で作成したマテリアライズドビューを通常のテーブル同様に QuickSight に取り込み、分析対象とすることで、SUPER 型を気にすることなく可視化を実施できるようになります。

注意点

• JSON スキーマ変更への追随
  DynamoDB 側のスキーマが変更されると、マテリアライズドビューやビューの定義の修正が必要になる場合があります。新しいフィールドを取得したい場合や、フィールドのデータ型が変わった場合も同様です。

参考

https://docs.aws.amazon.com/redshift/latest/mgmt/zero-etl.reqs-lims.html

宛先データベースは読み取り専用です。宛先データベースにテーブル、ビュー、またはマテリアライズド ビューを作成することはできません。ただし、ターゲット データ ウェアハウス内の他のテーブルでマテリアライズド ビューを使用することはできます。

→ ゼロETLでRedshiftに連携されたDBは読み取り専用のため、viewは別DBを作成する必要があります。

https://docs.aws.amazon.com/redshift/latest/mgmt/zero-etl-using.querying-and-creating-materialized-views.html#zero-etl-using.transforming

マテリアライズドビューを使用して複製されたデータをクエリする
ローカルの Amazon Redshift データベースにマテリアライズドビューを作成して、ゼロ ETL 統合によってレプリケートされたデータを変換できます。

→ 読み取り専用のゼロETLされたDBに対してクエリを実行するため、マテリアライズどビューを作成します。

https://docs.aws.amazon.com/redshift/latest/dg/cross-database_example.html

Amazon Redshift データベースを参照するデータベース間クエリを設定

→ Redshiftでは、データベース間でクエリを実行するクロスデータベースクエリを利用でき、クロスデータベースクエリによってゼロETLされたDBのマテリアライズドビューを作成します。

まとめ

今回はMATERIALIZED VIEWで実装しましたが、QuickSightのカスタムSQL側で対応する方針もありそうです。本記事がどなたかの役に立てば幸いです。