LoginSignup
2
0

More than 1 year has passed since last update.

@tosisisijp(tosisisi)

SnowflakeのIDENTIFIER()を利用する実践的なサンプルコード

Last updated at Posted at 2022-09-04

記事作成に至った背景

Snowflakeに「identifier ※リンクはSnowflake公式」というリテラルまたは変数でオブジェクト名(テーブル名や列名その他)を指定できる機能がある事を知ったが、
言語上の制約などで実際に組み上げるまでに様々な課題にあたった。
基本的に動くサンプルのテンプレートがあれば次回以降、同じことで悩まなくても良いかもと考え、
コピペのみで動作し、できるだけ躓かずに作業を進めれるようになると考えこの記事を書く事とした。

今回サンプルコードが解決しようとしている要件の設定

SRC_A_TABLEと、B_SRC_TABLE、C_SOURRCE_TBLからMART_TABLEを作成するが、
それぞれ列の定義が異なっている。さらに将来的に他のテーブル(列名など未定)が変わっても使えるようにする。
TARGET_TABLE_LISTというテーブル毎の列定義情報を保存するデータを作成し、
identifierで処理する事でテーブル毎の列定義の違いを吸収する。

利用するテーブル

テーブル名 内容
SRC_A_TBL Aシステムのデータ。今回の元データのうちの一つ
B_SRC_TABLE Bシステムのデータ。今回の元データのうちの一つ
C_SOURCE_TBL Cシステムのデータ。今回の元データのうちの一つ、列数が異なる
MART_TABLE 今回のデータを投入先テーブル。
TARGET_TABLE_LIST 処理対象テーブルの列定義をデータで保存するためのテーブル。

サンプルコード

※下記はSnowflakeのワークシートにコピペで動作させることを想定して記載している。

success_template.sql
-- Ⅰ-①処理対象の元データ「SRC_A_TBL」。
create or replace table SRC_A_TBL as (
  select 'A山' AS "名前", '12-34' AS "管理番号"
);

-- Ⅰ-②処理対象の元データ「B_SRC_TABLE」。 Ⅰ-①の定義と異なる。
create or replace table B_SRC_TABLE as (
  select 'B海' AS "名称", 98765 AS "番号"
);

-- Ⅰ-③処理対象の元データ「B_SRC_TABLE」。 Ⅰ-①、Ⅰ-②の定義と異なり、列数も異なる
create or replace table C_SOURCE_TBL as (
  select 'C沼' AS "TITLE"
);

-- Ⅰ-④処理対象の格納先「MART_TABLE」。 Ⅰ-①、Ⅰ-②の定義と異なる。
create or replace table MART_TABLE (
  SRC_TBL STRING
  , NAME STRING
  , CODE_ORIGINAL STRING
);

-- Ⅱ-① 処理対象のテーブル名、列定義をデータ化したテーブル。TBL_NAMEは必須、*_COLは必要に応じて定義を追加、変更すると良い。
create or replace table TARGET_TABLE_LIST as (
  select 
    'SRC_A_TBL' as TBL_NAME, '"名前"' as NAME_COL, '"管理番号"' as CODE_ORIGINAL_COL
  union all
  select 
    'B_SRC_TABLE' as TBL_NAME, '"名称"' as NAME_COL, '"番号"' as CODE_ORIGINAL_COL
  union all
  select 
    'C_SOURCE_TBL' as TBL_NAME, '"TITLE"' as NAME_COL, null as CODE_ORIGINAL_COL -- Ⅱ-② 変換対象の列定義が無い場合はnullを設定する
);

-- Ⅲ 実行スクリプトここから
SET(TBL_NAME, NAME_COL, CODE_ORIGINAL_COL) 
  = ('NULL_COL', 'NULL_COL', 'NULL_COL'); -- Ⅲ-⑭★SQL変数はSnowflakeスクリプト(begin内)で利用する場合は事前にSETが必要

-- Ⅲ-⑬★Snowflakeスクリプト(declare、begin)で実装するためexecute immediateを利用
execute immediate $$
declare
  wrk_TBL_NAME STRING; -- Ⅲ-⑫★カーソル行変数から一時的に受け取り、その後のWHERE句で使うためのSnowflakeスクリプト変数「wrk_TBL_NAME」定義
  crs_TARGET_TABLE_LIST cursor for select * from TARGET_TABLE_LIST; -- Ⅲ-⑪データソース毎に異なる定義をデータで取得するためのカーソル定義
begin
  for row_TARGET_TABLE_LIST in crs_TARGET_TABLE_LIST do -- Ⅲ-⑩データソース毎に異なる定義を処理するための for文・行変数定義「row_TARGET_TABLE_LIST」定義
    wrk_TBL_NAME := row_TARGET_TABLE_LIST.TBL_NAME; -- Ⅲ-⑨★カーソル行変数を直接SQLに渡せないため、Snowflakeスクリプト変数で受ける

    -- Ⅲ-⑧★SQL変数へTARGET_TABLE_LISTを代入する。カーソル行変数を直接SQL変数に渡せないため同じTARGET_TABLE_LISTからselect文で取得する。
    -- Ⅲ-⑦※カーソル行変数を一時的なSnowflakeスクリプト変数で受け、Snowflakeスクリプト変数からSQL変数に代入しても対応可能
    SET(
      TBL_NAME
      , NAME_COL
      , CODE_ORIGINAL_COL
    ) = (
      select 
        TBL_NAME -- Ⅲ-⑦ソーステーブルの名前指定は必須
        , NVL(NAME_COL, 'NULL_COL') -- Ⅲ-⑥★identifierを利用しつつ、TARGET_TABLE_LISTの列定義がNULLの場合に対応するため、nullの場合ダミーの'NULL_COL'を指定
        , NVL(CODE_ORIGINAL_COL, 'NULL_COL') -- Ⅲ-⑥★identifierを利用しつつ、TARGET_TABLE_LISTの列定義がNULLの場合に対応するため、nullの場合ダミーの'NULL_COL'を指定
      from 
        TARGET_TABLE_LIST 
      where 
        TARGET_TABLE_LIST.TBL_NAME = :wrk_TBL_NAME
    );

    -- Ⅲ-⑤MART_TABLE から処理対象データを削除
    delete from MART_TABLE where SRC_TBL = $TBL_NAME; 

    -- Ⅲ-④MART_TABLE にデータソースから追加
    insert into MART_TABLE 
    (
      select 
        $TBL_NAME AS SRC_TBL
        , identifier($NAME_COL) AS NAME_COL -- Ⅲ-③★identifier()へ列名をSQL変数で渡す。※identifier()の仕様でカーソル行変数やSnowfalkeスクリプト変数は指定できないため。
        , identifier($CODE_ORIGINAL_COL) AS CODE_ORIGINAL -- Ⅲ-③★identifier()へ列名をSQL変数で渡す。※identifier()の仕様でカーソル行変数やSnowfalkeスクリプト変数は指定できないため。
      from (
        select
          null as NULL_COL -- Ⅲ-②★TARGET_TABLE_LISTの列定義がNULLの場合に備えて'NULL_COL'を定義
          , *
        from
          identifier($TBL_NAME) -- Ⅲ-①★identifier()へ対象テーブルをSQL変数で渡して指定する。※identifier()の仕様でカーソル行変数やSnowfalkeスクリプト変数は指定できないため。
      )
    );
  end for;
  return 'Success';
end;
$$

処理解説

Ⅰ.データソース、データマートの定義

Ⅰについては前提の要件としてテーブルを定義しているのみ。Ⅰ-③には列数が異なる場合の例として記載している。

Ⅱ.列定義情報テーブル

ⅡについてはMART_TABLEに必要な列が増えた場合は、TARGET_TABLE_LISTに[対象列名]_COL の列定義を追加する方式とし、
別のデータソーステーブルを処理対象とする際には、TARGET_TABLE_LISTに必要な行を追加する。

Ⅲ.実行スクリプト部分の処理説明

Ⅲについては説明順を処理順と逆転している。これは、要件に対して事前準備を行うような考え方で設計する方が自然と考えたためである。私独自の取組となっていので、参照の際は番号とコードの場所を注意して確認いただければ幸いである。

  • Ⅲ-①★identifier()へ対象テーブルをSQL変数で渡して指定する。※identifier()の仕様でカーソル行変数やSnowfalkeスクリプト変数は指定できないため。
  • Ⅲ-②★TARGET_TABLE_LISTの列定義がNULLの場合に備えて'NULL_COL'を定義
  • Ⅲ-③★identifier()へ列名をSQL変数で渡す。※identifier()の仕様でカーソル行変数やSnowfalkeスクリプト変数は指定できないため。
  • Ⅲ-④MART_TABLE にデータソースから追加
  • Ⅲ-⑤MART_TABLE から処理対象データを削除
  • Ⅲ-⑥★identifierを利用しつつ、TARGET_TABLE_LISTの列定義がNULLの場合に対応するため、nullの場合ダミーの'NULL_COL'を指定
  • Ⅲ-⑧★SQL変数へTARGET_TABLE_LISTを代入する。カーソル行変数を直接SQL変数に渡せないため同じTARGET_TABLE_LISTからselect文で取得する。
  • Ⅲ-⑦※カーソル行変数を一時的なSnowflakeスクリプト変数で受け、Snowflakeスクリプト変数からSQL変数に代入しても対応可能
  • Ⅲ-⑨★カーソル行変数を直接SQLに渡せないため、Snowflakeスクリプト変数で受ける
  • Ⅲ-⑩データソース毎に異なる定義を処理するための for文・行変数定義「row_TARGET_TABLE_LIST」定義
  • Ⅲ-⑪データソース毎に異なる定義をデータで取得するためのカーソル定義
  • Ⅲ-⑫★カーソル行変数から一時的に受け取り、その後のWHERE句で使うためのSnowflakeスクリプト変数「wrk_TBL_NAME」定義
  • Ⅲ-⑬★Snowflakeスクリプト(declare、begin)で実装するためexecute immediateを利用
  • Ⅲ-⑭★SQL変数はSnowflakeスクリプト(begin内)で利用する場合は事前にSETが必要

まとめ

参考情報 制約と処理の依存関係

下図にどの処理がなんの制約に関わるかを示している。
なぜこの処理なのか？に興味がある方は見ていただければと思う。
※mermaidのスキルが足らず、見づらい図となり申し訳ない。

上図の凡例

  • 処理順
  • 制約の対処をしている処理
  • 処理を起因として発生した制約
2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0