Snowflake で半構造化データを取り込んで可視化してみる

みなさん、こんにちは！

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

## 準備
[東京都 新型コロナウイルス感染症対策サイト](https://github.com/Tokyo-Metro-Gov/covid19)で公開されている COVID-19 の陽性率データを使用します。
```ruby:qiita.rb
{
    "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 にアップロードしておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/053bb1f8-f325-46f1-a57e-c7ccf3e4e948.png)

## テーブル作成

JSONデータのロードに使用するテーブル`json_covid19_data`を作成しておきます。
```ruby:qiita.rb
CREATE TABLE json_covid19_data(v VARIANT);
```

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

## 外部ステージ作成
[こちら](https://www.reqtc.com/blog/snowflake-visualization.html)の記事を参考に認証設定を行い、対象となるデータの外部ステージを作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/17193717-e257-42db-a797-6e53aa688bbb.png)


## データロード
以下のSQLを実行し、データをロードします。
```ruby:qiita.rb
COPY INTO json_covid19_data
FROM @ext_stage_s3 FILE_FORMAT=(TYPE=JSON);
```

データがロードされていることを確認します。
```ruby:qiita.rb
SELECT * FROM json_covid19_data;
```

結果の行をクリックすると、右側にフォーマットされたJSONが表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/a5597216-a49f-4ec1-a649-b7a1335fe0ad.png)

## ビューの作成とクエリ
以下のSQLを実行し、JSONデータからビュー`json_covid19_data_view`を作成します。
```ruby:qiita.rb
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`として参照可能です。
その他、半構造化データのクエリ詳細については[こちら](https://docs.snowflake.com/ja/user-guide/querying-semistructured)の記事をご参照ください。

上記を実行すると、以下のようなビューが作成されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/72a24091-3b81-4ee8-b9da-734de95b0b6b.png)

作成したビューをもとに以下のクエリを実行すると、日付ごとの陽性者数が確認できます。
```ruby:qiita.rb
SELECT diagnosed_date, positive_count
FROM json_covid19_data_view
ORDER BY 1;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/9529fc9c-015d-4746-8f19-7f846ebc0d98.png)

チャートを開くと、以下の通り陽性者数の推移を視覚的に確認することができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/c94ca033-a054-40fd-9cbd-3bf43f0dd408.png)

## 参考
* [Snowflake入門 - ゼロからはじめるSnowflake](https://quickstarts.snowflake.com/guide/getting_started_with_snowflake_ja)