Snowflake では、半構造化データ（JSON, Parquet, Avroなど）を変換なしで簡単にロードしたりクエリしたりすることができます。本記事では、JSONデータを例として半構造化データの操作方法についてご紹介します。

準備

東京都 新型コロナウイルス感染症対策サイトで公開されている COVID-19 の陽性率データを使用します。

{
    "date": "2023\/5\/8 16:45",
    "data": [
        {
            "diagnosed_date": "2020-02-15",
            "positive_count": 8,
            "negative_count": 122,
            "positive_rate": null,
            "weekly_average_diagnosed_count": null,
            "pcr_positive_count": 8,
            "pcr_negative_count": 122,
            "antigen_positive_count": null,
            "antigen_negative_count": null
        },
        ...
    ]
}

JSONファイルをダウンロードし、AWS S3 にアップロードしておきます。

テーブル作成

JSONデータのロードに使用するテーブルjson_covid19_dataを作成しておきます。

CREATE TABLE json_covid19_data(v VARIANT);

Snowflake にはJSONオブジェクト全体を単一の行として格納するVARIANTという特殊なデータ型があります。VARIANTデータ型により、スキーマの事前定義の必要なく半構造化データを取り込むことができます。

外部ステージ作成

こちらの記事を参考に認証設定を行い、対象となるデータの外部ステージを作成します。

データロード

以下のSQLを実行し、データをロードします。

COPY INTO json_covid19_data
FROM @ext_stage_s3 FILE_FORMAT=(TYPE=JSON);

データがロードされていることを確認します。

SELECT * FROM json_covid19_data;

結果の行をクリックすると、右側にフォーマットされたJSONが表示されます。

ビューの作成とクエリ

以下のSQLを実行し、JSONデータからビューjson_covid19_data_viewを作成します。

CREATE OR REPLACE VIEW json_covid19_data_view AS
SELECT
  value:diagnosed_date::string as diagnosed_date,
  value:positive_count::int as positive_count,
  value:negative_count::int as negative_count,
  value:positive_rate::float as positive_rate,
  value:weekly_average_diagnosed_count::float as weekly_average_diagnosed_count,
  value:pcr_positive_count::int as pcr_positive_count,
  value:pcr_negative_count::int as pcr_negative_count,
  value:antigen_positive_count::int as antigen_positive_count,
  value:antigen_negative_count::int as antigen_negative_count
FROM
  json_covid19_data,
  LATERAL FLATTEN(input => v:data);

FLATTENは配列データを個別のデータとして展開する関数であり、展開されたデータはvalueとして参照可能です。その他、半構造化データのクエリ詳細についてはこちらの記事をご参照ください。

上記を実行すると、以下のようなビューが作成されます。

作成したビューをもとに以下のクエリを実行すると、日付ごとの陽性者数が確認できます。

SELECT diagnosed_date, positive_count
FROM json_covid19_data_view
ORDER BY 1;

チャートを開くと、以下の通り陽性者数の推移を視覚的に確認することができます。

参考

• Snowflake入門 - ゼロからはじめるSnowflake