目次 › 第4章 › 4-5

第4章 パフォーマンス最適化・クエリ・変換 / 想定学習時間:30〜40分 / 最終確認:2026年8月

4-5. 半構造化データ(VARIANT・FLATTEN)

1. 半構造化データ型:VARIANT・OBJECT・ARRAY

Snowflake は JSON・Avro・ORC・Parquet・XML といった半構造化データを、リレーショナルなテーブルの中で第一級のデータとして扱えます。その受け皿となるのが次の3つのデータ型です。

実務・試験でもっとも登場するのは VARIANT です。JSON をまるごと1列に格納でき、スキーマを事前に定義せず、読むときに構造を解釈する schema-on-read のアプローチを実現します。重要な制限として、VARIANT 列に格納できる1つの値のサイズ上限は最大128MB(非圧縮データ)です(内部的なオーバーヘッドにより、実際に格納できるサイズはこれよりやや小さくなる場合があります)。これを超える巨大な JSON はロード時に分割する必要があります。なお、この上限は以前は16MB(圧縮後)でしたが、その後128MBへ拡大されました。試験では旧仕様の「16MB」が選択肢に登場する可能性もあるため、「現在は128MB(非圧縮)」と整理して覚えておきましょう。

また、Snowflake は VARIANT に格納された半構造化データの内部で、頻出するパスを自動的に列のように格納(サブカラム化)します。このおかげで、VARIANT 内のフィールドへのクエリでも列指向ストレージやプルーニングの恩恵をある程度受けられ、性能が大きく劣化しません。すべてのパスが同じ型で安定しているデータほど、この自動列化が効きやすくなります。

2. ロード:PARSE_JSON・STRIP_OUTER_ARRAY・INFER_SCHEMA

半構造化データをテーブルに入れる主な方法を整理します。

-- 文字列を VARIANT に変換する
CREATE TABLE events (v VARIANT);
INSERT INTO events
SELECT PARSE_JSON('{"user": "u1", "items": [{"sku": "A", "qty": 2}]}');

-- ステージ上の JSON ファイルを COPY INTO でロードする
-- ファイルが [ {...}, {...}, ... ] という外側配列のとき、
-- STRIP_OUTER_ARRAY = TRUE で外側配列を外し、要素ごとに1行としてロードする
COPY INTO events
FROM @json_stage
FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = TRUE);

Parquet や Avro のようにスキーマ情報をファイル自身が持つ形式では、INFER_SCHEMA 関数でステージ上のファイルから列定義を検出し、その結果を使って CREATE TABLE ... USING TEMPLATE で構造化テーブルを自動作成することもできます。「VARIANT の1列に入れて後で解釈する」か「最初から列に展開して入れる」かを選べる、と理解しておきましょう。

3. アクセス:コロン記法とキャスト

VARIANT 内の値には、コロン(:)とドット・角括弧のパス記法でアクセスします。取り出した値は VARIANT 型のままなので、比較や表示のために :: で目的の型にキャストするのが基本形です。

-- v = {"user": {"name": "u1"}, "items": [{"sku": "A", "qty": 2}, {"sku": "B", "qty": 1}]}

SELECT
  v:user.name::STRING      AS user_name,   -- ネストは ドット でたどる
  v:items[0].sku::STRING   AS first_sku,   -- 配列は [インデックス](0始まり)
  v:items[0].qty::NUMBER   AS first_qty,
  GET_PATH(v, 'user.name') AS same_thing   -- 関数形式でも同じパスを取れる
FROM events;

• パスに存在しないキーを指定してもエラーにはならず、NULL(SQLのNULL)が返ります。
• キャストしない VARIANT の文字列値は表示上ダブルクォート付き("u1")になります。::STRING でキャストするとクォートが外れるのは、実際に触ると気づく代表的な挙動です。
• GET_PATH(v, 'path') はコロン記法と同等の関数形式です。

4. LATERAL FLATTEN:配列を行に展開する

「1行の中の配列」を「複数の行」に変換するのがテーブル関数 FLATTEN です。元のテーブルと組み合わせるときは LATERAL キーワードとともに使い、行ごとにその行の配列を展開して結合します。

-- items 配列の要素を1行ずつに展開する
SELECT
  v:user.name::STRING AS user_name,
  f.value:sku::STRING AS sku,
  f.value:qty::NUMBER AS qty,
  f.index             AS item_index
FROM events,
LATERAL FLATTEN(input => v:items) f;

-- 配列が空/NULLの行も残したい場合は OUTER => TRUE
SELECT v:user.name::STRING, f.value
FROM events,
LATERAL FLATTEN(input => v:items, OUTER => TRUE) f;

FLATTEN の出力には、展開後の要素が入る value 列のほか、配列内の位置を示す index、キー名(オブジェクト展開時)の key、パスを示す path などの列が含まれます。既定では配列が空または NULL の行は結果から消えますが、OUTER => TRUE を指定すると、その行も value が NULL の行として残ります(外部結合と同じ発想です)。

5. 生成:OBJECT_CONSTRUCT・ARRAY_AGG・TO_JSON

逆に、リレーショナルなデータから半構造化データを組み立てる関数も揃っています。アンロード(3-6)で JSON 形式に書き出す前段などで使います。

-- 注文明細を、注文ごとに1つのJSONオブジェクトへまとめる
SELECT
  order_id,
  TO_JSON(
    OBJECT_CONSTRUCT(
      'order_id', order_id,
      'skus',     ARRAY_AGG(sku)
    )
  ) AS order_json
FROM order_items
GROUP BY order_id;
-- 結果例: {"order_id": 1001, "skus": ["A", "B"]}

練習問題