SnowflakeのUSING TEMPLATE+INFER SCHEMAの処理にカラムを追加する #SQL

概要

SnowflakeのINFER_SCHEMAでCSVなどからファイルのヘッダを参照したりして
USING　TEMPLATEでファイル定義を元にテーブルを作れる機能があるのですが

参考)

https://docs.snowflake.com/ja/sql-reference/sql/create-table
https://docs.snowflake.com/ja/sql-reference/functions/infer_schema

基本はCSV内に存在する列名を取ってきてTEMPLATEとして渡す形になってしまうので
追加カラム、主にメタデータ項目とかがデフォルトではないという形になってしまい。

CREATE後にALTERで追加すれば出来はしますが
やっぱ自動でついかしたいよね？とかあると思うので作成時に追加できるかを試してみました。

デフォルトの出力を確認してみる

infer_schemaの例では

SQL

CREATE TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@mystage/json/',
          FILE_FORMAT=>'my_json_format'
        )
      ));

INFER_SCHEMAの戻りをOBJECT_CONSTRUCTして最終的にARRAY_AGGで1行にしたデータをTEMPLATEとして渡しているようです

そこでARRAY_AGGのデータまでの状態でちょっと出力をしてみます

JSON

[
  {
    "COLUMN_NAME": "c1",
    "EXPRESSION": "$1::NUMBER(5, 0)",
    "FILENAMES": "utf_add_2403 2.csv",
    "NULLABLE": true,
    "ORDER_ID": 0,
    "TYPE": "NUMBER(5, 0)"
  },
...
  {
    "COLUMN_NAME": "c15",
    "EXPRESSION": "$15::NUMBER(1, 0)",
    "FILENAMES": "utf_add_2403 2.csv",
    "NULLABLE": true,
    "ORDER_ID": 14,
    "TYPE": "NUMBER(1, 0)"
  }
]

基本は↓のキーがセットになっているJSONの配列のようなので

COLUMN_NAME
EXPRESSION
FILENAMES
NULLABLE
ORDER_ID
TYPE

JSON要素をARRAY_APPENDで追加してみる

このJSONをARRAY_APPENDで追加したものを返したらできるんじゃない？という事で追加してみます

SQL

CREATE OR REPLACE TRANSIENT TABLE mytable
  USING TEMPLATE (
    SELECT
        ARRAY_APPEND(
            ARRAY_AGG(OBJECT_CONSTRUCT(*)),
              {
                'COLUMN_NAME': 'FILENAME',
                'EXPRESSION': 'metadata$filename::TEXT',
                'FILENAMES': 'utf_add_2403 2.csv',
                'NULLABLE': true,
                'ORDER_ID': -1,
                'TYPE': 'TEXT'
              }::VARIANT)
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@TEST_STAGE/',
          FILE_FORMAT=>'test_csv_format'
        )));

問題なく作成できてしまったようです

作祭されたテーブルのDDLを確認しても

SQL

create or replace TRANSIENT TABLE TEST_DB.TEST.MYTABLE (
	"c1" NUMBER(5,0),
...
	"c15" NUMBER(1,0),
	FILENAME VARCHAR(16777216)
);

きちんと末尾に入っているようです

おまけ

値を追加で渡すとして

EXPRESSION
FILENAMES
ORDER_ID

とか計算して取ってくるのキツくない？とおもったので省略できるかもためしてみました

カラム名と型のみ渡すJSONに修正

SQL

CREATE OR REPLACE TRANSIENT TABLE mytable
  USING TEMPLATE (
    SELECT
        ARRAY_APPEND(
            ARRAY_AGG(OBJECT_CONSTRUCT(*)),
              {
                'COLUMN_NAME': 'FILENAME',
                'TYPE': 'TEXT'
              }::VARIANT)
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@TEST_STAGE/',
          FILE_FORMAT=>'test_csv_format'
        )));

削った他の部分は省略できそうですがNULLABLEのみは必須のようです

Invalid template: NULLABLE field is missing in {"COLUMN_NAME":"FILENAME","TYPE":"TEXT"}

最終系

CREATE OR REPLACE TRANSIENT TABLE mytable
  USING TEMPLATE (
    SELECT
        ARRAY_APPEND(
            ARRAY_AGG(OBJECT_CONSTRUCT(*)),
              {
                'COLUMN_NAME': 'FILENAME',
                'NULLABLE': true,
                'TYPE': 'TEXT'
              }::VARIANT)
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@TEST_STAGE/',
          FILE_FORMAT=>'test_csv_format'
        )));

まとめ

INFER_SCHEMAから取得したデータを元にテーブルを作って要素を追加するという場合

COLUMN_NAME
NULLABLE
TYPE

があれば、現状ではCREATE時にカラム追加可能なようです