SnowflakeでJSONを扱う時はjson_extract_path_textを使うのではなく、半構造化データを使おう

深澤 (@qluto) です。

私は現在、バクラクのAI-OCRチームでマネージャーとして開発を進めています。

プロダクトの進化やスケーリングを図るうえで、データ基盤の改善もどんどんと進んでおり、日々その恩恵を授かりながら開発をしています。最近はBigQueryからSnowflakeへの移行が進行中です。

Google BigQueryからSnowflakeへ。バクラクのデータ基盤技術移管事例 - Findy Tools

本記事では、移行作業の中で学んだSnowflakeの半構造化データについて紹介します。

半構造化データを知らなかった私

Snowflakeを触り始めた当初は、知らずにjson_extract_path_textなどを使った遅いクエリを量産していたのですが、Snowflakeの半構造化データを理解し活用すると格段に効率が上がることに気づきました。

半構造データ型として格納済みのJSONデータが入ったフィールドに対して、以下のようなクエリを書いていました。

with extracted as (
    select
        json_extract_path_text(json_data, 'payload.a_nested_value_list')
    from my_table
)
select
-- 以下抽出したデータに対する加工・集計処理など

これは確かに動作はしますが、何も知らずしてjson_extract_path_textを使うのはだいぶ勿体無いです。データ量によってはクエリ実行時間もかかりますし、お財布にも優しくないです。

これがなぜよくないのかということを、半構造化データを解説しながら解き明かしていきます。

BigQueryにもjson_extract_scalarという似たような関数があり、Snowflakeでも同じような考え方で見つけたjson_extract_path_textを使ってしまっていました。 BigQueryにはネイティブJSON型があり、それを駆使すればクエリ量も少なく計算効率も良くJSONデータを処理することができますが、Snowflakeにはそれ以上に柔軟かつ計算効率を高くすることができる工夫があります。

それは半構造化データ型です。

Snowflakeにおける半構造化データ

一般的な構造化データはもちろんのこと、Snowflakeでは半構造化データを扱うための専用の型（VARIANT, OBJECT, ARRAY）が提供されています。これらの型を使うことで、JSON、XML、Avro、Parquetなどのいわゆる「厳密なスキーマを持たない（もしくは緩やかな）データ」をそのまま扱えるのです。

半構造化データのロードの概要 | Snowflake Documentation

Snowflakeでは上記ファイル形式の構造を全く明示的に指定せずとも、そのまま非構造化データとして取り込むことが可能です。取り込まれたデータは、主にVARIANT, OBJECT, ARRAYの3種類のデータ型で表現される半構造データとなります。

半構造化データの利点

列指向 x マイクロパーティションで高速化

Snowflakeは内部でデータを列指向かつサイズ最大16MBのマイクロパーティション単位で管理しています。

マイクロパーティションとデータクラスタリング | Snowflake Documentation

構造化データの際はもちろんながら、半構造化データのロード時には、JSONやXMLなどの半構造化データを解析し、ツリー構造的に持つキーやパスをメタデータとして列指向形式でマイクロパーティションに格納します。クエリ実行時には、必要なパスだけを参照し、不要なパーティションをスキップすることで高速化が可能になるのです。

以下のようなクエリで、JSONのネスト構造を直接指定して取り出せます。ロードされた段階でSnowflakeが内部的に列としてメタ情報を管理しているため、文字列から都度JSONとしてパースする必要がありません。

select
    data:object.key1::varchar as col1,
    data:object.key2::varchar as col2
from my_table;

これにより、パース処理やフルスキャンを回避しながら効率的にデータへアクセスできます。

json_extract_path_textはダメなのか？

Snowflakeが提供するjson_extract_path_textは、実は下記のような動作に相当します。

TO_VARCHAR(
    GET_PATH(
        PARSE_JSON(JSON文字列), 'PATH'
    )
)

つまり、毎回クエリの実行時に文字列をJSONとしてパースし、その後にパスをたどって値を取り出しているのです。これでは、せっかくSnowflakeが持つ “半構造化データを列指向ストアで格納する利点” をまったく活かせません。

基本的には最初から VARIANT型にロードする設計にして、Snowflakeの列指向エンジンをフルに活用するほうが圧倒的に効率的でしょう。

variant型を使用するにあたって、列指向ストアに抽出されるかどうかは、少し条件があるので注意が必要です。

全てにわたってnull値しか存在しない項目や、文字列と数値とが混在する項目、200以上の項目を超えるものなどは列指向形式で抽出されませんが、一般的な多くのケースでは適用されるでしょう。

VARIANT に格納されている半構造化データに関する考慮事項 | Snowflake Documentation

まとめ

Snowflakeの “VARIANT型による半構造化データ” は列指向形式に最適化して格納される。
json_extract_path_text のような関数は文字列から毎回JSONをパースするため、パフォーマンス・コスト面で非効率。
入力ファイルをできるだけ最初から VARIANT 型にロードし、パーティションスキップなどのSnowflakeの強みを活かす設計が望ましい。

SnowflakeでJSONやXMLなどを扱う際は、ぜひ VARIANT/OBJECT/ARRAY などの半構造化データ型を積極的に検討してみてください。そのうえで、クエリパフォーマンスの状況やマイクロパーティションの分割状況を観測し、必要に応じてテーブルのクラスタリングやパーティショニングを最適化することで、Snowflakeの強力な処理能力を最大限引き出せるはずです。