Snowflakeでロード中のデータ変換を試してみた

こんにちは！エノカワです。

Snowflakeでは、データをテーブルにロードする際のデータ変換をサポートしています。

こちらの記事でステージにあるファイルのデータのクエリを紹介しましたが、
クエリの結果をCOPYコマンドでテーブルにロードするイメージです。

今回はロード中のデータ変換を実際に試してみました。

準備

サンプルデータベースSNOWFLAKE_SAMPLE_DATAのTPCH_SF1.ORDERSを利用します。

TPCH_SF1.ORDERSのデータをステージのCSVファイルにアンロードし、
別のテーブルにロードする際にデータ変換を試してみましょう。

Snowsightのワークシート上で、以下のクエリを実行していきます。

ウェアハウス作成

検証で使用するウェアハウスDEMO_WHを作成します。

create or replace warehouse DEMO_WH with
  WAREHOUSE_SIZE = 'XSMALL'
;
use warehouse DEMO_WH;

データベース作成

検証で使用するデータベースDEMO_DBを作成します。

create or replace database DEMO_DB;
use DEMO_DB;

ステージ作成

CREATE STAGEコマンドでアンロード先のステージを作成します。
今回は名前付きステージを使用します。

create or replace stage ORDERS_UNLOAD_STAGE;

データのアンロード

TPCH_SF1.ORDERSからデータをアンロードします。
アンロード先は作成した名前付きステージORDERS_UNLOAD_STAGEです。
アンロード後、LISTコマンドでステージングされたファイルのリストを取得します。

copy into @ORDERS_UNLOAD_STAGE
  from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
  file_format = (type = CSV compression = 'GZIP');

list @ORDERS_UNLOAD_STAGE;

5個のCSVファイルがアンロードされました。

テーブル作成

TPCH_SF1.ORDERSと同じ構造を持つテーブルを作成します。

作成する前に、TPCH_SF1.ORDERSのデータをプレビューしておきましょう。

注文ID、金額、注文日、店員などの列を持つ注文データです。

以下のクエリでテーブルを作成します。
テーブル作成後、DESCRIBEコマンドでテーブルの構造を確認します。

create or replace table SF1_ORDERS
  like SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;

describe table SF1_ORDERS;

null?が全てN（NULL値を許容しない）になっています。

列プロパティ変更

検証でロードしない列が出てくるので、以下4つの列をY（NULL値を許容する）に変更しておきます。

alter table SF1_ORDERS alter (
   O_ORDERSTATUS drop not null,
   O_ORDERPRIORITY drop not null,
   O_SHIPPRIORITY drop not null,
   O_COMMENT drop not null
);

describe table SF1_ORDERS;

4つの列（赤枠囲み）がNULL値許容に変更されました。

それでは実際にデータの変換を試してみましょう。

データの変換

テーブルデータのサブセットのロード

データの一部の列をテーブルにロードすることができます。

COPYコマンドでロード先のカラムを選択、SELECTステートメントでロードする列を選択します。
ロード後、SELECTステートメントでテーブルの中身を確認します。

copy into SF1_ORDERS(
            O_ORDERKEY, O_CUSTKEY, O_TOTALPRICE, O_ORDERDATE, O_CLERK)
  from (select
            $1, $2, $4, $5, $7 from
          @ORDERS_UNLOAD_STAGE)
  file_format = (type = CSV);

select * from SF1_ORDERS;

選択した5つの列のみロードされました。
ロードされなかった列はデフォルト値のNULL値が挿入されています。

ロード中の CSV 列の並べ替え

テーブルにロードする前に、ステージングされた CSV ファイルの列データを並べ替えることができます。
さらに、SUBSTR関数を使用して、文字列を挿入する前に切り出すこともできます。

SELECTステートメントで1番目と2番目の列を並び替えてみましょう。
ロード先のカラムの方も同じように1番目と2番目の列を並び替えます。
6番目の列はSUBSTR関数で先頭1文字を切り出し、O_ORDERPRIORITYにロードします。

truncate table SF1_ORDERS;

copy into SF1_ORDERS(
            O_CUSTKEY, O_ORDERKEY, O_TOTALPRICE, O_ORDERDATE, O_CLERK, O_ORDERPRIORITY)
  from (select
            $2, $1, $4, $5, $7, substr($6,1,1)
          from @ORDERS_UNLOAD_STAGE)
  file_format = (type = CSV);

select * from SF1_ORDERS;

列を並び替えてロードすることができました。
O_ORDERPRIORITYも1文字だけロードされていますね。

ロード中のデータ型の変換

データのロード中にデータ型を変換することができます。

変換関数TO_BINARY, TO_NUMBER, TO_TIMESTAMP_NTZで試してみましょう。　　

変換後のデータをロードする列を用意する必要があるので、
ALTER TABLEコマンドで3つの列をまとめて追加します。

truncate table SF1_ORDERS;

alter table SF1_ORDERS
add
  O_ORDERKEY_BINARY binary,
  O_TOTALPRICE_NUMBER number,
  O_ORDERDATE_TIMESTAMP timestamp_ntz;

describe table SF1_ORDERS;

3つの列（赤枠囲み）が追加されました。

以下のクエリを実行してみましょう。

copy into SF1_ORDERS(
            O_ORDERKEY, O_CUSTKEY, O_TOTALPRICE, O_ORDERDATE, O_CLERK,
            O_ORDERKEY_BINARY, O_TOTALPRICE_NUMBER, O_ORDERDATE_TIMESTAMP)
  from (select
            $1, $2, $4, $5, $7,
            to_binary($1, 'utf-8'), to_number($4), to_timestamp_ntz($5)
          from @ORDERS_UNLOAD_STAGE)
  file_format = (type = CSV);

select
  O_ORDERKEY, O_ORDERKEY_BINARY,
  O_TOTALPRICE, O_TOTALPRICE_NUMBER,
  O_ORDERDATE, O_ORDERDATE_TIMESTAMP
from SF1_ORDERS;

追加した3つの列に変換後のデータがロードされていますね。

まとめ

以上、ロード中のデータ変換を実際に試してみました。

SELECTステートメントを使用してサブセットのロードや並び替え、データ型変換をすることができました。

この機能のおかげで、データのロード中に列を並べ替える際に、
一時テーブルを使用して事前に変換されたデータを保存する必要がなくなります。

ETL処理の簡素化に活用できそうですね。

参考

• ロード中のデータの変換 — Snowflake Documentation
• COPY INTO <テーブル> — Snowflake Documentation
• ALTER TABLE — Snowflake Documentation
• DESCRIBE TABLE — Snowflake Documentation
• TO_DECIMAL , TO_NUMBER , TO_NUMERIC — Snowflake Documentation