Snowflakeの「CREATE TABLE … USING TEMPLATE」構文を試してみた

2021.06.15

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

先日、SnowflakeのPreview機能として公開された、半構造化データのカラム定義検出を試したのですが、その際にCREATE TABLE ... USING TEMPLATEの利用の仕方が分からずに試せていませんでした。

こちらについて、Mineaki Motohashiさん (@mmotohas)から情報を頂いたので早速試してみたいと思います。(いつもありがとうございます!

前提条件

ユーザーステージの下記のパスに、今回利用するParquetファイルが配置済みとなります。

  • @~/unload/region.parquet

CREATE TABLE ... USING TEMPLATE 構文でテーブルを作成する

では、早速試してみます。

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

-- CREATE TABLE ... USING TEMPLATE
CREATE OR REPLACE TABLE region
USING TEMPLATE(
  SELECT
    ARRAY_AGG(OBJECT_CONSTRUCT(*))
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION => '@~/unload/region.parquet',
      FILE_FORMAT => 'ootaka_parquet_format'
    )
  )
);
╒════════════════════════════════════╕                                          
│ status                             │
╞════════════════════════════════════╡
│ Table REGION successfully created. │
╘════════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 1.738s

作成できました!テーブル定義も見てみましょう。

-- テーブル内容の表示
DESCRIBE TABLE ootaka_sandbox_db.public.region;
╒═════════════╤═══════════════════╤════════╤═══════╤═════════╤═════════════╤════════════╤═══════╤════════════╤═════════╤═════════════╕
│ name        │ type              │ kind   │ null? │ default │ primary key │ unique key │ check │ expression │ comment │ policy name │
╞═════════════╪═══════════════════╪════════╪═══════╪═════════╪═════════════╪════════════╪═══════╪════════════╪═════════╪═════════════╡
│ R_REGIONKEY │ NUMBER(38,0)      │ COLUMN │ Y     │ NULL    │ N           │ N          │ NULL  │ NULL       │ NULL    │ NULL        │
├─────────────┼───────────────────┼────────┼───────┼─────────┼─────────────┼────────────┼───────┼────────────┼─────────┼─────────────┤
│ R_NAME      │ VARCHAR(16777216) │ COLUMN │ Y     │ NULL    │ N           │ N          │ NULL  │ NULL       │ NULL    │ NULL        │
├─────────────┼───────────────────┼────────┼───────┼─────────┼─────────────┼────────────┼───────┼────────────┼─────────┼─────────────┤
│ R_COMMENT   │ VARCHAR(16777216) │ COLUMN │ Y     │ NULL    │ N           │ N          │ NULL  │ NULL       │ NULL    │ NULL        │
╘═════════════╧═══════════════════╧════════╧═══════╧═════════╧═════════════╧════════════╧═══════╧════════════╧═════════╧═════════════╛
3 Row(s) produced. Time Elapsed: 0.117s

テーブル定義も良さそうですね。

TEMPLATEについて

ところで、TEMPLATEの内容が気になるので、こちらも少し確認してみます。

SELECT
  ARRAY_AGG(OBJECT_CONSTRUCT(*))
FROM TABLE(
  INFER_SCHEMA(
    LOCATION => '@~/unload/region.parquet',
    FILE_FORMAT => 'ootaka_parquet_format'
  )
);
╒═══════════════════════════════════════════════════════════════════╕           
│ ARRAY_AGG(OBJECT_CONSTRUCT(*))                                    │
╞═══════════════════════════════════════════════════════════════════╡
│ [                                                                 │
│   {                                                               │
│     "COLUMN_NAME": "R_REGIONKEY",                                 │
│     "EXPRESSION": "$1:R_REGIONKEY::NUMBER(38, 0)",                │
│     "FILENAMES": "@~/unload/region.parquet_0_0_0.snappy.parquet", │
│     "NULLABLE": false,                                            │
│     "TYPE": "NUMBER(38, 0)"                                       │
│   },                                                              │
│   {                                                               │
│     "COLUMN_NAME": "R_NAME",                                      │
│     "EXPRESSION": "$1:R_NAME::TEXT",                              │
│     "FILENAMES": "@~/unload/region.parquet_0_0_0.snappy.parquet", │
│     "NULLABLE": false,                                            │
│     "TYPE": "TEXT"                                                │
│   },                                                              │
│   {                                                               │
│     "COLUMN_NAME": "R_COMMENT",                                   │
│     "EXPRESSION": "$1:R_COMMENT::TEXT",                           │
│     "FILENAMES": "@~/unload/region.parquet_0_0_0.snappy.parquet", │
│     "NULLABLE": true,                                             │
│     "TYPE": "TEXT"                                                │
│   }                                                               │
│ ]                                                                 │
╘═══════════════════════════════════════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.975s

ということで、JSONの配列(ARRAY型)になっているんですね。そうすると、ARRAY型を渡せばテーブルが作成できそうな気もしたので試してみます。

-- TEMPLATEにARRAY型を渡してみる
CREATE OR REPLACE TABLE region_with_json_str
USING TEMPLATE(
  SELECT
    AS_ARRAY(
      PARSE_JSON('
        [
          {
            "COLUMN_NAME": "R_REGIONKEY",
            "NULLABLE": false,
            "TYPE": "NUMBER(38, 0)"
          },
          {
            "COLUMN_NAME": "R_NAME",
            "NULLABLE": false,
            "TYPE": "TEXT"
          }
        ]
      ')
    )
);
000002 (0A000): Unsupported feature 'INFER_SCHEMA function must be used in the TEMPLATE sub-query'.

怒られてしまいました。TEMPLATEのサブクエリではINFER_SCHEMAを利用しないとダメだよ、ということですね。

ParquetファイルのデータをCOPYしてみる

データのCOPYについては、

テーブルを作成した後は、COPYコマンドでロードする際にmatch_by_column_nameオプションを指定してあげると、列名とparquetファイルの列名を自動でマッチングしてくれてロードできます!

とのことでしたので、最後にテーブル作成元にしたParquetファイルのCOPYもmatch_by_column_nameオプションを指定して試してみます。

-- ParquetファイルをCOPY
COPY INTO ootaka_sandbox_db.public.region
FROM '@~/unload/region.parquet'
FILE_FORMAT = 'ootaka_parquet_format'
MATCH_BY_COLUMN_NAME = CASE_SENSITIVE
;
╒════════════════════════════════════════════╤════════╤═════════════╤═════════════╤═════════════╤═════════════╤═════════════╤══════════════════╤═══════════════════════╤═════════════════════════╕
│ file                                       │ status │ rows_parsed │ rows_loaded │ error_limit │ errors_seen │ first_error │ first_error_line │ first_error_character │ first_error_column_name │
╞════════════════════════════════════════════╪════════╪═════════════╪═════════════╪═════════════╪═════════════╪═════════════╪══════════════════╪═══════════════════════╪═════════════════════════╡
│ unload/region.parquet_0_0_0.snappy.parquet │ LOADED │           5 │           5 │           1 │           0 │ NULL        │             NULL │                  NULL │ NULL                    │
╘════════════════════════════════════════════╧════════╧═════════════╧═════════════╧═════════════╧═════════════╧═════════════╧══════════════════╧═══════════════════════╧═════════════════════════╛
1 Row(s) produced. Time Elapsed: 2.105s

無事にCOPYできました!以下のように想定通りデータが入っています。

-- テーブルデータを確認
SELECT
  R_REGIONKEY,
  R_NAME,
  R_COMMENT
FROM
  ootaka_sandbox_db.public.region
;
╒═════════════╤═════════════╤═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ R_REGIONKEY │ R_NAME      │ R_COMMENT                                                                                                           │
╞═════════════╪═════════════╪═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│           0 │ AFRICA      │ lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to  │
├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│           1 │ AMERICA     │ hs use ironic, even requests. s                                                                                     │
├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│           2 │ ASIA        │ ges. thinly even pinto beans ca                                                                                     │
├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│           3 │ EUROPE      │ ly final courts cajole furiously final excuse                                                                       │
├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│           4 │ MIDDLE EAST │ uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl        │
╘═════════════╧═════════════╧═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╛
5 Row(s) produced. Time Elapsed: 1.256s

まとめ

以上、Snowflakeの「CREATE TABLE ... USING TEMPLATE」構文を試してみました。

この機能を利用すれば、スキーマ構造が不明なファイルが複数あった場合にも「テーブル作成」→「データロード」の流れをスムーズに実行することができそうです。こちらは2021年6月現在はまだPreview機能なのですが、正式リリースが待ち遠しい機能です。

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