Snowflake半構造化データ処理

SnowflakeはVARIANT型を中心に、JSON・Parquet・Avro・ORC・XMLといった半構造化データをリレーショナルテーブル内にネイティブに格納し、SQLで直接クエリできます。コロン表記（:）によるパスアクセス、LATERAL FLATTENによる配列展開、OBJECT_CONSTRUCT/ARRAY_AGGによる構築が試験で頻出の3大操作パターンです。

VARIANT型の基礎

VARIANT型はJSON・オブジェクト・配列・スカラー値などあらゆる半構造化データを格納できる汎用型です。最大16MBのデータを1値に格納でき、内部的には列指向形式で圧縮保存されます。

-- VARIANTカラムを持つテーブルの作成
CREATE OR REPLACE TABLE raw.events (
  event_id   INT AUTOINCREMENT,
  received_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
  payload    VARIANT
);

-- JSONデータの直接INSERT
INSERT INTO raw.events (payload)
SELECT PARSE_JSON('{
  "user_id": 42,
  "action": "purchase",
  "items": [
    {"sku": "A100", "qty": 2, "price": 1500},
    {"sku": "B200", "qty": 1, "price": 3200}
  ],
  "metadata": {"source": "web", "version": "2.1"}
}');

コロン表記（:）によるパスアクセス

VARIANT型のデータにはコロン表記（:）でキーを指定してアクセスします。ネストしたオブジェクトにはドット（.）、配列要素にはブラケット（[]）を使います。

-- 第1階層のキーアクセス
SELECT
  payload:user_id::INT       AS user_id,
  payload:action::STRING     AS action
FROM raw.events;

-- ネストしたオブジェクトのアクセス
SELECT
  payload:metadata.source::STRING  AS source,
  payload:metadata.version::STRING AS api_version
FROM raw.events;

-- 配列要素へのアクセス（0始まりインデックス）
SELECT
  payload:items[0].sku::STRING   AS first_sku,
  payload:items[0].price::NUMBER AS first_price
FROM raw.events;

::（キャスト演算子）を使って明示的に型変換を行わないと、結果はVARIANT型のまま返されます。WHERE句やJOINで使用する場合は必ずキャストが必要です。

LATERAL FLATTENによる配列展開

LATERAL FLATTENは、VARIANT型内の配列やオブジェクトを行に展開する関数です。JSONの配列要素を1行ずつリレーショナル形式に変換する際の標準的な手法です。

-- 配列内の各アイテムを行に展開
SELECT
  e.event_id,
  e.payload:user_id::INT    AS user_id,
  f.index                   AS item_index,
  f.value:sku::STRING       AS sku,
  f.value:qty::INT          AS quantity,
  f.value:price::NUMBER     AS price
FROM raw.events e,
  LATERAL FLATTEN(INPUT => e.payload:items) f;

FLATTENの出力カラム

多段ネストの展開

-- 2段ネストの展開（orders → items）
SELECT
  o.value:order_id::INT      AS order_id,
  i.value:product::STRING    AS product,
  i.value:amount::NUMBER     AS amount
FROM raw.batch_data d,
  LATERAL FLATTEN(INPUT => d.payload:orders) o,
  LATERAL FLATTEN(INPUT => o.value:items) i;

OBJECT_CONSTRUCTによるオブジェクト構築

OBJECT_CONSTRUCTは、リレーショナルカラムからVARIANT型のJSONオブジェクトを構築する関数です。データのアンロード（JSON形式でのエクスポート）やAPI連携用のペイロード生成に活用します。

-- リレーショナルデータからJSONオブジェクトを構築
SELECT OBJECT_CONSTRUCT(
  'order_id',   o.order_id,
  'customer',   o.customer_name,
  'total',      o.total_amount,
  'order_date', o.order_date::STRING
) AS order_json
FROM analytics.orders o
WHERE o.order_date = '2026-03-27';

-- OBJECT_CONSTRUCT_KEEP_NULL（NULLキーを保持）
SELECT OBJECT_CONSTRUCT_KEEP_NULL(
  'name',  e.name,
  'email', e.email,
  'phone', e.phone
) AS employee_json
FROM hr.employees e;

ARRAY_AGGによる配列集約

ARRAY_AGGは、複数行の値を1つのARRAY型に集約する関数です。GROUP BYと組み合わせて、リレーショナルデータを非正規化JSON構造に変換する際に使用します。

-- 顧客ごとの注文IDリストを配列に集約
SELECT
  customer_id,
  ARRAY_AGG(DISTINCT order_id) WITHIN GROUP (ORDER BY order_date)
    AS order_ids,
  ARRAY_SIZE(ARRAY_AGG(DISTINCT order_id)) AS order_count
FROM analytics.orders
GROUP BY customer_id;

-- OBJECT_CONSTRUCTとARRAY_AGGの組み合わせ
SELECT OBJECT_CONSTRUCT(
  'customer_id', customer_id,
  'orders', ARRAY_AGG(
    OBJECT_CONSTRUCT(
      'order_id', order_id,
      'amount',   total_amount
    )
  )
) AS customer_orders_json
FROM analytics.orders
GROUP BY customer_id;

半構造化データのロードパターン

JSONファイルのロードでは、STRIP_OUTER_ARRAYオプションで最外部の配列を行に分解するパターンが頻出です。

-- JSON File Formatの定義
CREATE OR REPLACE FILE FORMAT json_format
  TYPE = JSON
  STRIP_OUTER_ARRAY = TRUE
  ALLOW_DUPLICATE = FALSE;

-- ステージからVARIANTカラムへロード
COPY INTO raw.events (payload)
FROM @data_stage/events/
FILE_FORMAT = json_format;

-- Parquetファイルのロード（スキーマ自動検出）
COPY INTO raw.parquet_data
FROM @data_stage/parquet/
FILE_FORMAT = (TYPE = PARQUET)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

その他の主要関数

問題で確認

よくある質問