こんにちは。Ozawaです。
Alteryxでは、Snowflakeに対してデータをアップをしたり、Snowflakeからデータをインポートすることができます。
AlteryxとSnowflakeとの接続方法はこちら
Snowflake 上の Prepper Open Data Bank に Alteryx から接続する | Tableau-id Press -タブロイド- (truestar.co.jp)
特に、AlteryxからSnowflakeにテーブルをアップするには、出力ツールのSnowflake BULKを使用します。
AlteryxからSnowflakeにテーブルをアップする際、4つの出力オプションがあります。
- Create New Table : テーブルを作成する
- Delete Data & Append : テーブルの中身を上書きする
- Append Exiting : 既存のテーブルにデータを追加する
- Overwrite Table : テーブルを上書きする
今回は、AlteryxからSnowflakeにテーブルをアップするとき、Snowflake上ではどのような処理をしているのかを、Snowflake側で実行されたSQLやコマンドを追ってみることで確認します。
Snowflakeで実行したSQLの確認方法
Snowflake側でどのような処理が起こっているかを知るためには、Query Historyから実行したSQLを確認します。
Activity → Query Activity → Query Historyを開きます。
これを見ることで、どんなSQLを実行したかがわかります。
AlteryxからSnowflakeへのアップで何が処理されている？
検証を行うために、SANDBOX.OZAWAというスキーマの中に、PREF_CODE_NAMEというテーブルを用意しました。中身は以下の通りです。
PREF_CODE_NAMEテーブルをAlteryxからSnowflakeへ4つの出力オプションでアップし、どのような処理が発生するかを検証しました。
Create New Table
put 'file://C:\\Users\\<Alteryx Temp Path>\\e70c30d124c5.csv.gz' @~ AUTO_COMPRESS = FALSE SOURCE_COMPRESSION = GZIP PARALLEL=4;
copy into "SANDBOX"."OZAWA"."PREF_CODE_NAME" ("PREF_CODE","PREF_NAME") from @~/e70c30d124c5.csv.gz file_format = (type=CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY='"' ESCAPE=NONE ESCAPE_UNENCLOSED_FIELD=NONE);
rm @~/e70c30d124c5.csv.gz
Create New Tableによって、実行された処理を整理します。
はじめにPUTコマンドを使って、Alteryxの一時ファイルcsv.gzをSnowflake上のステージにアップします。
次にCOPY INTOクエリを使って、ステージにアップされたテーブルを、PREF_CODE_NAMEテーブルに挿入しています。
最後にrmコマンドでSnowflakeのステージ上にアップされた一時ファイルcsv.gzを削除します。ステージにファイルが置きっぱなしだと、料金がかかってしまうため、自動で削除してくれるのはうれしいですね！
このようにして、新しいテーブルを作成することができます。
Delete Data & Append
DELETE FROM "SANDBOX"."OZAWA"."PREF_CODE_NAME"
put 'file://C:\\Users\\<Alteryx Temp Path>\\e70c30d124c5.csv.gz' @~ AUTO_COMPRESS = FALSE SOURCE_COMPRESSION = GZIP PARALLEL=4;
copy into "SANDBOX"."OZAWA"."PREF_CODE_NAME" ("PREF_CODE","PREF_NAME") from @~/e70c30d124c5.csv.gz file_format = (type=CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY='"' ESCAPE=NONE ESCAPE_UNENCLOSED_FIELD=NONE);
rm @~/e70c30d124c5.csv.gz
次にDelete Data & Appendによって、実行された処理を整理します。
はじめにDELETE FROM クエリを使って、PREF_CODE_NAMEテーブルの中身を削除します。この作業が最初に発生するのがCREATE NEW TABLEとは異なる点です。
この後は、Create New Tableと同様、PUTでSnowflakeのステージにテーブルをアップし、COPY INTOでPREF_CODE_NAMEテーブルにステージのデータをINSERTし、最後にrmコマンドでステージ上の一時ファイルを削除します。
上記の処理の流れのように、テーブルの中身を削除したあとにテーブルの中身を再格納することで、テーブルの中身を更新することができます。
Append Existing
put 'file://C:\\Users\\<Alteryx Temp Path>\\e70c30d124c5.csv.gz' @~ AUTO_COMPRESS = FALSE SOURCE_COMPRESSION = GZIP PARALLEL=4
copy into "SANDBOX"."OZAWA"."PREF_CODE_NAME" ("PREF_CODE","PREF_NAME") from @~/e70c30d124c5.csv.gz file_format = (type=CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY='"' ESCAPE=NONE ESCAPE_UNENCLOSED_FIELD=NONE)
rm @~/e70c30d124c5.csv.gz
次にAppend Existingによって、実行された処理を整理します。
はじめにPUTコマンドでステージ上に一時ファイルをアップします。
次にCOPY INTOを使って、既に中身が存在するPREF_CODE_NAMEテーブルにステージのデータを挿入します。
最後にステージ上の一時ファイルを削除します。
上記の処理を行うことで、テーブルの中身が入ったまま、追加データを挿入することができます。
Overwrite Table
DROP TABLE "SANDBOX"."OZAWA"."PREF_CODE_NAME"
CREATE TABLE "SANDBOX"."OZAWA"."PREF_CODE_NAME" ("PREF_CODE" char(2),"PREF_NAME" char(4))
put 'file://C:\\Users\\<Alteryx Temp Path>\\e70c30d124c5.csv.gz' @~ AUTO_COMPRESS = FALSE SOURCE_COMPRESSION = GZIP PARALLEL=4
copy into "SANDBOX"."OZAWA"."PREF_CODE_NAME" ("PREF_CODE","PREF_NAME") from @~/e70c30d124c5.csv.gz file_format = (type=CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY='"' ESCAPE=NONE ESCAPE_UNENCLOSED_FIELD=NONE)
rm @~/e70c30d124c5.csv.gz
最後にOverwrite Tableによって、実行された処理を整理します。
はじめにDROP TABLEを実行し、テーブル自体を削除します。
次にCREATE TABLEコマンドで、PREF_CODE_NAMEテーブルを作成します。
その後はCreate New Tableと同様に、ステージ上に一時ファイルをアップし、PREF_CODE_NAMEテーブルに挿入し、最後に一時ファイルを削除します。
上記の処理を行うことで、テーブルの中身のデータだけでなく、テーブル自体を上書きすることができます。
※ ただしOverwriteでは、DROP TABLEを実行することから、共有されているテーブルに対して使わないことをお勧めします。テーブルを一度でも削除することで、共有が切れてしまいます。
※ 対してDelete Table & Append や Append Existingでは、テーブルの中身を入れ替えるだけなので、共有には影響しません。
おわりに
今回はAlteryxからSnowflakeへ4つの出力オプションで出力した場合に、どんな処理が行われたかをQuery Historyから調べてみました。
普段ツールのUI上で実行する操作も、Snowflake上のクエリを順に追ってみることで、どういった処理を組み合わせて挙動しているのかを知ることができ、非常に面白いですね！
今後も、SnowflakeやAlteryxの記事を書いていきます。お楽しみに。
それでは！
