Snowflakeデータロード時の豆知識
今回は外部テーブルからデータをロードする際に欲しいデータを抽出する方法を試したいともいます。
前回の記事の続きです。
特定のカラムを指定してロードする
前回記事で使用したデータを使用します。
今回想定するケース
ORDER_IDとAMOUNTの2つのカラムを指定しロードしてみます。
テーブルの作成
CREATE OR REPLACE TABLE OUR_FIRST_DB.PUBLIC.ORDERS_EX (
ORDER_ID VARCHAR(30),
AMOUNT INT
)
カラムを指定したロード
Snowflakeはデータのロード時に絡む指定をする際はエイリアス指定する必要があります。
詳細はドキュメントを参照ください。
COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
FROM (select s.$1, s.$2 from @MANAGE_DB.external_stages.aws_stage s)
file_format= (type = csv field_delimiter=',' skip_header=1)
files=('OrderDetails.csv');
確認
ロードした結果は下記の通り、ORDER_IDとAMOUNTのみロードされている。
データ型を変換し条件指定を含んだロード
今回想定するケース
OrderDetails.csvの3カラム目のPROFITの値で0以下の場合、PROFITABLE_FLAGにnot profitableを入れ、0以上の場合はprofitableを書き込む。
テーブルの作成
先ほど作成したテーブルORDERS_EXにPROFITABLE_FLAG VARCHAR(30)を追加し、再作成します。
CREATE OR REPLACE TABLE OUR_FIRST_DB.PUBLIC.ORDERS_EX (
ORDER_ID VARCHAR(30),
AMOUNT INT,
PROFIT INT,
PROFITABLE_FLAG VARCHAR(30)
)
変換と条件指定したロード
CASE WHENは条件式関数であり、ここでは$3の値が0以下ならnot~で0以上ならprofitableをPROFITABLE_FLAGに書き込む
その際CAST関数で$3の値を数値型に変換している。
COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
FROM (select
s.$1,
s.$2,
s.$3,
CASE WHEN CAST(s.$3 as int) < 0 THEN 'not profitable' ELSE 'profitable' END
from @MANAGE_DB.external_stages.aws_stage s)
file_format= (type = csv field_delimiter=',' skip_header=1)
files=('OrderDetails.csv');
確認
コマンドの結果通りにロードされていることを確認する。
フィールド値の加工
今回想定するケース
OrderDetails.csvのCATEGORYカラムのフィールド値の先頭5文字だけを取得します。
テーブルの作成
CREATE OR REPLACE TABLE OUR_FIRST_DB.PUBLIC.ORDERS_EX (
ORDER_ID VARCHAR(30),
AMOUNT INT,
PROFIT INT,
CATEGORY_SUBSTRING VARCHAR(5)
)
先頭5文字抽出ロード
5文字抽出のためにsubstring関数を利用しています。
詳しくはドキュメントを参照ください。
COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
FROM (select
s.$1,
s.$2,
s.$3,
substring(s.$5,1,5)
from @MANAGE_DB.external_stages.aws_stage s)
file_format= (type = csv field_delimiter=',' skip_header=1)
files=('OrderDetails.csv');
確認
CATEGORY_SUBSTRINGを確認するとすべて先頭5文字のみ抽出されています。
指定したカラムのみロードし、それ以外はNULLにする
今回想定するケース
下記カラムがあるとし、その中でORDER_IDとPROFITのみロードしたい
ORDER_ID | AMOUNT | PROFIT | PROFITLABEL_FLAG |
---|
テーブルの作成
CREATE OR REPLACE TABLE OUR_FIRST_DB.PUBLIC.ORDERS_EX (
ORDER_ID VARCHAR(30),
AMOUNT INT,
PROFIT INT,
PROFITABLE_FLAG VARCHAR(30)
)
カラムを指定しロード
1行目の(ORDER_ID,PROFIT)でカラムを指定している
COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX (ORDER_ID,PROFIT)
FROM (select
s.$1,
s.$3
from @MANAGE_DB.external_stages.aws_stage s)
file_format= (type = csv field_delimiter=',' skip_header=1)
files=('OrderDetails.csv');
結果
コマンド通りの結果となっている。
自動インクリメントロード
今回想定するケース
1からの連番で番号を振りなおしてみます。
テーブルの作成
ORDER_IDカラムに自動インクリメントを設定します。
CREATE OR REPLACE TABLE OUR_FIRST_DB.PUBLIC.ORDERS_EX (
ORDER_ID number autoincrement start 1 increment 1,
AMOUNT INT,
PROFIT INT,
PROFITABLE_FLAG VARCHAR(30)
)
データのロード
COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX (PROFIT,AMOUNT)
FROM (select
s.$2,
s.$3
from @MANAGE_DB.external_stages.aws_stage s)
file_format= (type = csv field_delimiter=',' skip_header=1)
files=('OrderDetails.csv');
Discussion