こんにちは、プロダクトマネージメント@for Apps の宮本です！
データレプリケーションツールのCData Syncで、SnowflakeのVariant型データも簡単にレプリケーション可能になりました！これによって、複雑な半構造化データも効率よくSnowflakeに同期できます。
これまでも何度かお問合せいただくことがあり、その際にはVARCHAR 形式でのレプリケーションをご案内していましたが、今回の機能アップデートにより、例えばPostgreSQL のJSONB データをそのままJSON としてSnowflake に保存できるようになりました。これに伴い、連携後のデータがより扱いやすくなります。
それではCData Sync でSnowflake のVariant 型を使う方法をご紹介します。
Snowflake Variant 型とは
https://docs.snowflake.com/ja/sql-reference/data-types-semistructured#common-uses-for-a-variant
”Snowflakeは、データを JSON、Avro、 ORC、またはParquet形式から ARRAY、 OBJECT、および VARIANT データの内部階層に変換し、その階層データを VARIANT に直接格納できます。”
とリファレンスに書かれていますように、さまざまフォーマット形式の半構造化データをスキーマレスでSnowflake に格納することができ、特定のフィールドの値が簡単に取得できるようになります。
本記事のデータ連携構成
本記事ではJSONB 型カラムを含んだPostgreSQL から、Snowflake へのレプリケーションを行っていきます。
これまでのビルドでは、JSONやJSONB などのカラムは全てVarchar でSnowflake のテーブルにカラムが作成されていましたが、最新ビルドではVariant 型で作成されるようになります。
CData Sync から Snowflake へのレプリケーション時のステップ
初回連携時であれば、①Snowflake にテーブルを作成します。2回目以降かつ差分更新モードなら一時テーブルを作成します。作成が完了したら、②Snowflake 上に内部ステージを作成し、③でファイルをSync 環境から作成したステージにアップロードします。ファイルアップロードが完了したら、④でファイルの中身を①で作成したテーブルにロードします。この際、2回目以降かつ差分更新モードの場合は一時テーブルへのロードを行います。2回目以降かつ差分更新モードの場合は⑤で一時テーブルと対象テーブルでマージを行います。
手順
データソース／同期先のコネクション作成、ジョブ作成、実行という順でやっていきます。
コネクション作成
まずはデータソース側のPostgtreSQL のコネクションを作成します。
Server やUser、Password を入力します。
次に高度な設定タブを開き、Other プロパティに以下を入力します。
supportnativedatatype=true;
この設定で、CData Sync からPostgreSQL にアクセスした際に、JSONB データをVarchar に変換しないようになります。
同期先DB のSnowflake のコネクションも作成します。
ジョブ作成
次はジョブ作成していきましょう。データソースはPostgreSQL、同期先はSnowflake で設定します。
種類は標準でも変更データキャプチャでもどちらでも大丈夫です。
PostgreSQL のテーブルを選択します。今回はorders テーブルを使用しますが、
そのテーブルの中身は以下のようになっています。
JSONB列には以下のようなJSON データが格納されています。
[
{
"price": 1200,
"quantity": 1,
"product_id": 101,
"product_name": "Laptop"
},
{
"price": 50,
"quantity": 1,
"product_id": 102,
"product_name": "Mouse"
}
]
これでPostgreSQL のorders テーブルのデータを、Snowflake 上にorders テーブルを作成してレプリケーションするジョブが作成できました。
ジョブ実行と差分更新の確認
それでは実行してみましょう。成功すれば以下のようにSuccessful というマークが表示されます。
続いて、Snowflake の方も確認しましょう。
まずはテーブル定義ですが、正しく３つのカラムがVariant 型で定義されています。
テーブルの中身も、PostgreSQL に格納されていたJSON データがそのまま保存されていることが確認できました。
最後に、配列形式で格納されていたJSON データを FLATTEN 関数で展開してみるとこのように分割して取得することができます。
SELECT
"order_id",
item.value:product_id::NUMBER AS product_id,
item.value:product_name::STRING AS product_name,
item.value:quantity::NUMBER AS quantity,
item.value:price::NUMBER AS price
FROM "orders",
LATERAL FLATTEN(input => "order_details") item;
まとめ
いかがでしたでしょうか！Snowflake のVariant 型でのレプリケーションをご紹介させていただきましたが、データソース側でJSON 形式でデータを保持していても、ETLツールを通すとVarchar になってしまうのはもったいないので、ぜひこちらの機能をご利用ください。
なお、CData Sync は30日間の無償トライアルが可能です。ぜひお試しくださいませ！
https://www.cdata.com/jp/s
関連コンテンツ
