Snowflakeで半構造化データのカラム定義検出を試してみた

こんにちは！DA(データアナリティクス)事業本部 インテグレーション部の大高です。

SnowflakeのPreview機能として、半構造化データのカラム定義検出機能が公開されたそうなので、早速試してみました！

Parquetファイルを準備する

2021年6月 現在ではParquet,Avro,ORCに対応しているとのことなので、Parquetファイルを検証用データとして作成します。

用意するParquetファイルはsnowflake_sample_dataデータベースのサンプルデータをParquetファイルとしてUNLOADして作成します。また、UNLOAD先はユーザーステージにしています。

-- コンテキスト設定
USE DATABASE ootaka_sandbox_db;
USE SCHEMA public;
USE WAREHOUSE x_small_wh;

-- ファイルフォーマットを定義
CREATE FILE FORMAT ootaka_parquet_format TYPE = PARQUET;

-- サンプルデータをユーザーステージにParquetファイルとしてエクスポート
COPY INTO @~/unload/region.parquet
FROM snowflake_sample_data.tpch_sf1.region
FILE_FORMAT = ootaka_parquet_format
HEADER = TRUE
;

-- UNLOADしたファイルの確認
LIST @~/unload/region.parquet;
╒════════════════════════════════════════════╤══════╤══════════════════════════════════╤═══════════════════════════════╕
│ name                                       │ size │ md5                              │ last_modified                 │
╞════════════════════════════════════════════╪══════╪══════════════════════════════════╪═══════════════════════════════╡
│ unload/region.parquet_0_0_0.snappy.parquet │ 1520 │ 08283b0392a5158b03a8090ae395f984 │ Mon, 14 Jun 2021 04:53:50 GMT │
╘════════════════════════════════════════════╧══════╧══════════════════════════════════╧═══════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.404s

これで、ファイルの準備ができました。

Parquetファイルのカラム定義を検出する

では、早速INFER_SCHEMAを利用して、Parquetファイルのカラム定義を検出してみましょう。

下記のドキュメントを参考に、試してみます。

-- Parquetファイルのカラム定義を検出
SELECT * FROM
TABLE(
  INFER_SCHEMA(
    LOCATION => '@~/unload/region.parquet',
    FILE_FORMAT => 'ootaka_parquet_format'
  )
);
╒═════════════╤═══════════════╤══════════╤═══════════════════════════════╤═══════════════════════════════════════════════╕
│ COLUMN_NAME │ TYPE          │ NULLABLE │ EXPRESSION                    │ FILENAMES                                     │
╞═════════════╪═══════════════╪══════════╪═══════════════════════════════╪═══════════════════════════════════════════════╡
│ R_REGIONKEY │ NUMBER(38, 0) │ False    │ $1:R_REGIONKEY::NUMBER(38, 0) │ @~/unload/region.parquet_0_0_0.snappy.parquet │
├─────────────┼───────────────┼──────────┼───────────────────────────────┼───────────────────────────────────────────────┤
│ R_NAME      │ TEXT          │ False    │ $1:R_NAME::TEXT               │ @~/unload/region.parquet_0_0_0.snappy.parquet │
├─────────────┼───────────────┼──────────┼───────────────────────────────┼───────────────────────────────────────────────┤
│ R_COMMENT   │ TEXT          │ True     │ $1:R_COMMENT::TEXT            │ @~/unload/region.parquet_0_0_0.snappy.parquet │
╘═════════════╧═══════════════╧══════════╧═══════════════════════════════╧═══════════════════════════════════════════════╛
3 Row(s) produced. Time Elapsed: 1.519s

良い感じに出力されていますね。一応、元のテーブルにおけるファイル定義と比較してみましょう。

DESCRIBE TABLE snowflake_sample_data.tpch_sf1.region;
╒═════════════╤══════════════╤════════╤═══════╤═════════╤═════════════╤════════════╤═══════╤════════════╤═════════╤═════════════╕
│ name        │ type         │ kind   │ null? │ default │ primary key │ unique key │ check │ expression │ comment │ policy name │
╞═════════════╪══════════════╪════════╪═══════╪═════════╪═════════════╪════════════╪═══════╪════════════╪═════════╪═════════════╡
│ R_REGIONKEY │ NUMBER(38,0) │ COLUMN │ N     │ NULL    │ N           │ N          │ NULL  │ NULL       │ NULL    │ NULL        │
├─────────────┼──────────────┼────────┼───────┼─────────┼─────────────┼────────────┼───────┼────────────┼─────────┼─────────────┤
│ R_NAME      │ VARCHAR(25)  │ COLUMN │ N     │ NULL    │ N           │ N          │ NULL  │ NULL       │ NULL    │ NULL        │
├─────────────┼──────────────┼────────┼───────┼─────────┼─────────────┼────────────┼───────┼────────────┼─────────┼─────────────┤
│ R_COMMENT   │ VARCHAR(152) │ COLUMN │ Y     │ NULL    │ N           │ N          │ NULL  │ NULL       │ NULL    │ NULL        │
╘═════════════╧══════════════╧════════╧═══════╧═════════╧═════════════╧════════════╧═══════╧════════════╧═════════╧═════════════╛
3 Row(s) produced. Time Elapsed: 0.412s

Parquetで出力した元テーブルの定義と同じとなっており、問題なくカラム定義が検出されていることがわかりますね。（※カラム型については、あくまでParquetファイルの定義検出なので、ここはTEXTとVARCHAR(XX)が異なっているのは問題ない認識です）

また、以下のようにGENERATE_COLUMN_DESCRIPTIONを利用することでカラム定義を生成することもできます。

SELECT
  GENERATE_COLUMN_DESCRIPTION(ARRAY_AGG(OBJECT_CONSTRUCT(*)), 'table') AS COLUMNS
FROM TABLE(
  INFER_SCHEMA(
    LOCATION => '@~/unload/region.parquet',
    FILE_FORMAT => 'ootaka_parquet_format'
  )
);
╒══════════════════════════════╕
│ COLUMNS                      │
╞══════════════════════════════╡
│ "R_REGIONKEY" NUMBER(38, 0), │
│ "R_NAME" TEXT,               │
│ "R_COMMENT" TEXT             │
╘══════════════════════════════╛
1 Row(s) produced. Time Elapsed: 1.442s

更に応用として、 CREATE TABLE … USING TEMPLATE 構文でテーブルを作成することもできるようですが、こちらは「TEMPLATE」の記述フォーマットが見つけられなかったため、また機会があったら試してみたいと思います！

まとめ

以上、半構造化データのカラム定義検出を試してみました。

Snowflakeでは半構造化データをVARIANT型で簡単に登録することができますが、今回追加されたこの機能をうまく利用すれば「構造化したテーブルにデータを移したい！」という時にも簡単にテーブル定義が決められそうですね。

どなたかのお役に立てば幸いです。それでは！