SnowflakeでJSONファイルをテーブルデータにしてみた。

はじめに

「SnowflakeにJSONファイルを取り込めることはすごいのか～」と
ドキュメントを見ながら思っていた去年。
まさか、「まじでJSONファイル取り込めるとか神かよ！ありがとうSnowflake！！」
と拝み倒すことになるとは当時は思いもしませんでした。
今回は業務でJSONファイルをSnowflakeに取り込む処理を作成した際のことを
必要だと思う設定と手順を備忘録を兼ねて書いてみました。

目次

1.前提条件
2.ファイルフォーマット作成
3.既存のストレージ統合の設定
4.ステージ作成
5.JSONファイルを取り込むテーブル作成
6.ビューでテーブルの形に加工
おまけ.テーブル作成時にJSONファイルを取り込む
8.おわりに
9.参考ドキュメント

1.前提条件

今回処理を行った環境の前提条件を記載します。

1.既にSnowflakeとS3の接続ができている環境で行う
2.実行アカウントがSYSADMIN、MYINTの管理がACCOUNTADMINである

皆さんが使っている環境を確認して参照していただければと思います。

2.ファイルフォーマット作成

今回取り込むJSONファイル(サンプル)は以下になります。

sample.json

[
  {
    "item_id": 1001,
    "name": "ミカン",
    "price": 100
  },
  {
    "item_id": 1002,
    "name": "リンゴ",
    "price": 150
  },
  {
    "item_id": 1003,
    "name": "パイナップル",
    "price": 800
  }
]

{}ごとのデータを1行として取得を行いたいのですが、
JSONファイルはarray[]で囲まれているので
[]を取り除いた状態で取り込みを行う設定をします。※1

JSON_FORMAT.sql

create or replace file format JSON_FORMAT --JSON_FORMATという名前のファイルフォーマットを作成
  type = 'JSON'                           --JSONファイルを取り込むのでJSONを指定してあげます
  strip_outer_array = true;               --オプションで外部配列を削除したいのでTrueにします

3.既存のストレージ統合の設定

ストレージ統合についての管理をACCOUNTADMINで行っているので
ACCOUNTADMINに切り替えて次の処理を実行しました。※2

MYINT.sql

ALTER STORAGE INTEGRATION MYINT SET		--既存のストレージ統合のプロパティを変更します(今回はMYINTという名前で設定しています)
STORAGE_ALLOWED_LOCATIONS = (			--統合を使用する外部ステージのストレージの場所を参照することを明示的に制限します
's3://bucket/path/');					--S3のストレージの場所を指定

4.ステージ作成

SYSADMINに戻り、既存のストレージ統合のプロパティを変更したのでそれに伴ったステージを作成します。※3

JSON_STAGE.sql

create or replace stage JSON_STAGE	--外部ステージを作成
URL = 's3://bucket/path/'			--前のセクションで指定したパスを記載
STORAGE_INTEGRATION = MYINT			--外部クラウドストレージの認証責任をSnowflake IDおよびアクセス管理（IAM）エンティティに委任するために使用されるストレージ統合の名前を指定
file_format = JSON_FORMAT			--前のセクションで作成したファイルフォーマットを記載

5.JSONファイルを取り込むテーブル作成

JSONファイルを取り込むテーブルを作成します。

create SAMPLE_FRUIT.sql

create or replace table SAMPLE_FRUIT (	--json_dataという項目にvariant値で入るように作成
  json_data variant);

作成したテーブルにJSONファイルを取り込む

copy into SAMPLE_FRUIT.sql

copy into SAMPLE_FRUIT
  from @JSON_STAGE/
  on_error = 'skip_file';

外部配列が取り除かれている状態で1行ずつデータが格納されました！

6.ビューでテーブルデータの形に加工

取り込んだデータのままではテーブルとして利用することはできません。
ビューで加工してテーブルデータとして利用できるようにしてあげます。※4

create WV_SAMPLE_FRUIT

create or replace VIEW WV_SAMPLE_FRUIT	--ビューを作成
   (item_id								--表示する項目を書き出していく
   ,name
   ,price
   )as
   select								--テーブル名SAMPLE_FRUITの項目名json_dataの中身をフラット化する
JSON_DATA:item_id::varchar AS item_id,	--[column_name]:[値]::[データ型] AS [任意の項目名],という形で書く
JSON_DATA:name::varchar AS name,
JSON_DATA:price::number AS price
from SAMPLE_FRUIT;

作成後、想定通りかSELECT文で呼び出し

SELECT文.sql

SELECT * FROM WV_SAMPLE_FRUIT;

うまくいってますね！

おまけ.テーブル作成時にJSONファイルを取り込む

SAMPLE_FRUITテーブルから直接テーブルに加工して格納する方法はないのか
という疑問が出た人のためにその方法も共有いたします。

TRN_SAMPLE_FRUIT.sql

create or replace TABLE TRN_SAMPLE_FRUIT
as
select
JSON_DATA:item_id::varchar(4) AS item_id,								
JSON_DATA:name::varchar(50) AS name,
JSON_DATA:price::number(10,0) AS price
from SAMPLE_FRUIT;

結果

as select以下はビュー作成時と同じになります。一つ違うのはデータ型指定を行っています。
先ほどのビュー構文のように指定なしにすると各データ型の最大値を設定されてしまいます。

指定なし

指定あり

7.おわりに

JSONファイルの中身が未知数だったため、ビューに落とし込むという手順を行いました。

今回はビューまでの実装を書かせていただきましたが、
実業務としては6の作業後、蓄積データとしてトランザクションテーブルを作成して洗替処理を行いました。

このJSONファイルをS3に用意するところまでが自分にとって大きな壁でした汗
その時の備忘録は別記事で作成できたらと思います。

8.参考ドキュメント

※1

※2

※3

※4