こんにちは！ がく＠ちゅらデータエンジニアです。
こちらの5日目の記事になります。
Frosty Friday Week1 Basic External Storage
こちらは、2022年7月に、Frosty Fridayの最初のチャレンジです。
ここから始まったんですね！！
チャレンジ内容は、凄くシンプルで、
「外部テーブルからデータを読み込んで、テーブルにロードしましょう！」
Snowflakeを使おうと思ったら、いの一番にやることですね！
誰もがやるBasic、初回Week1にはとてもふさわしいのではないでしょうか
この回はtomoさんが解説なさってますね。18分30秒ぐらいからWeek1の解説ですね
Week1のチャレンジ内容
世界有数のホワイト企業であるFrostyFriday株式会社には、分析に使用するcsvデータがS3バケットに配置されています。
今回は、外部STAGEを作成し、この分析用csvデータをテーブルへロードすることです。
S3バケットのURIは、s3://frostyfridaychallenges/challenge_1/になります。
環境を整備する
use role sysadmin;
use warehouse gaku_wh;
create database gaku_frosty_friday_db;
create schema week001;
ここで今後ずーーーーっとつかっていくデータベース「Frosty_Friday」なを作りました♪
利用するスキーマ（week001）も作っていきます。
次にStageを作りましょう
create or replace stage week_ext_stage
URL='s3://frostyfridaychallenges/challenge_1/';
こちらでStageを作りました。
非常にシンプルなSQLで、file formatも使ってないですね！
ちなみに、なんの指定もしないと、Type=CSV だけが設定されているのと同じになるはずです
※JSONファイルとかを読み込むと改行で複数レコードに読み込まれてしまったり
ファイルの中身を確認
それではStageの中身（実体は、s3://frostyfridaychallenges/challenge_1/）を見てみましょう。
list @week_ext_stage;
と3つのファイルが有ることがわかります。
※このチャレンジは2022年7月なのに、ファイルのタイムスタンプは2023年2月・・・・置き換えたんだなぁｗ
それでは、Stage上のcsvファイルの中身を見てみましょう
select
$1
, $2
, $3
, metadata$filename
, metadata$file_row_number
from
@week_ext_stage
;
最初にファイルの中身を見るときには、
- $1, $2, $3 みたいに、csvファイルの1カラム目、2カラム目、3カラム目を出してみます
- もしnullになったらcsvにはそのカラムがないってことと判断するようにしてます
- なので、$3で内容が出力されたら、$4, $5 ・・・って増やして試してみます
- また、METADATAも出力します
- FILENAME（ファイル名）
- FILE_ROW_NUMBER（ファイル内での何行目か）
テーブルを作る
それでは格納するテーブルをとりあえず作りましょう。
※これぐらい小さいファイルなら、Stage上のファイルをクエリするでも出来なくはないのですが
create or replace table week1_table (
result text
, filename text
, file_row_number number
)
;
desc table week1_table;
読み込む際のFILE FORMATの作成
読み込む際のfile formatを作成しましょう。
＞FILE FORMATについて
SnowflakeのFILE FORMATは、かなり便利だと思っています。
BigQueryでは、読み込むファイルの文字コードがutf8しか許容できなくて、読み込ませる前に、別手段で文字コードを変更しなければならなかったりしました（現時点2024年12月ではどうかちょっとしらべ切れていません・・・）
SnowflakeのFILE FORMATの利点としては
- 対応フォーマットが多い（csv, json, avro, orc, parquet, xml） ※ここはSnowProCoreにでます！
- とくに、csv, json, parquetはよく使うかなと思います
- parquetは、最近よくみるIcebergテーブルに使われていますね、csvよりも耐エラー性が高いと思っているので、できれば今後はparquetでファイルのやり取りはしたい・・・・
- 文字コードが設定できる（UTF8がデフォルトだけど、SJISとか）
- ドキュメントに記載があります＞対応文字コード
- Snowflakeの内部的にはUTF8で保存（取込時に変換をかける）
今回は
create or replace file format week1_csv_format
type = CSV
skip_header = 1
null_if = ('NULL', 'null')
empty_field_as_null = true
;
チャレンジで準備されてるデータの中を見ると
- ファイルは .csv → TYPE=CSV
- 一行目に resultがある → 1行目はヘッダー → skip_header=1が必要
- 文字列で「NULL」がある → null_ifで、「NULL」って文字は nullとして扱う
- 空白のみはnullとして扱う → empty_field_as_null = true
他にもたくさんオプションがありますが今回使うのはこれぐらいでしょうか
データの投入と整形
-- データの投入
copy into week1_table ( result, filename, file_row_number)
from
(
select
$1
, metadata$filename
, metadata$file_row_number
from
@week_ext_stage
)
FILE_FORMAT = (FORMAT_NAME = 'week1_csv_format');
;
データを投入
select * from week1_table ;
で確かめてみると・・・・
なにやら意味ありげ文になりそう・・・
you have gotten it right (null) totally_empty congraturations!
- nullを外したい
- totally_emptyもいらない・・・感じがする！
最終的には
with source as (
select
result
, filename
, file_row_number
from
week1_table
where
result is not null
and result != 'totally_empty'
)
, final as (
select
LISTAGG(result,' ') WITHIN GROUP (ORDER BY filename, file_row_number ) as answer
from source
)
-- select * from source;
select * from final;
totally_emptyの除去については、file formatでやると
create or replace file format week1_csv_format
type = CSV
skip_header = 1
null_if = ('NULL', 'null', 'totally_empty')
empty_field_as_null = true
;
と null_ifにいれる方法も使えそうですね
最後に、LISTAGG という関数を使ってます
FILENAMEとFILE_ROW_NUMBERでソートして、「 （スペース）」を区切り文字として、連結させています。
まとめ
今回は、Frosty Fridayの最初の一歩 Week1 をやりました。
ここからFrosty Fridayが始まったかと思うとなかなか感慨深いです笑
外部ステージからの取込という、Snowflakeでの最初の第一歩ですね
こちらで私の解法を公開しています。
Discussion