こんにちは！ がく＠ちゅらデータエンジニアです。
こちらの10日目の記事になります。
Frosty Friday Week Week3 Basic Metadata Queries
チャレンジ内容は「Stageファイルに関するMetadataを使って色々やろうぜ！」
Week3のチャレンジ内容
Week1では、S3に配置したデータの取り込みについて説明しましたが、ここWeek3では更に一歩進んだチャレンジです。
基本的に驚くことはありませが、作り始めると頭を悩ませるかもしれません
世界でも有数のホワイト企業であるFrosty Friday 株式会社 には、csvデータが多数配備されているS3バケットがあります。
これらのデータはそれほど複雑なデータではなく、すべて同じカラム構成のデータです。
これらのファイルをすべて一つのテーブルに取り込む必要があります。
ただし、重要なデータもアップロードされる場合があります。
この重要なファイルには通常とは異なる命名法則があり、その場合には特別な処理をしなくてはいけません。
参照用にメタデータを別途テーブルに保持する必要があります。
S3内には、keyword.csv というファイルで、この中には重要なファイルを示すキーワードが登録されています。
課題：
ステージ内の keyword.csvに登録されているキーワードのいずれかを含むファイルをリストアップするテーブルを作成してください。
S3bakettono URIは、s3://frostyfridaychallenges/challenge_3/ になります。
結果は、
これを作る形になります。
Week3をやっていこう
環境設定
use role sysadmin;
use warehouse gaku_wh;
use database frosty_friday;
create or replace schema week3;
いつものようにまず環境設定とスキーマ（week3）を作っていきます。
Stageを作ろう
S3のバケットは、s3://frostyfridaychallenges/challenge_3/ が指定されているのでこちらを使ってStageを作っていきます。
create or replace stage week3_ext_stage
URL='s3://frostyfridaychallenges/challenge_3/';
list @week3_ext_stage;
keyword.csvを読み込む
問題には「keywords.csv には、ファイルを重要としてマークするすべてのキーワードが含まれています。」
ということなので、keywords.csvに入っているワードを調べましょう
select
$1
, $2
, $3
, $4
, $5
, metadata$filename
, metadata$file_row_number
from
@week3_ext_stage/keywords.csv
;
これから分かるのは
- csvファイルである（TYPE=CSV） ※ファイルの拡張子で分かるけど念の為
- 1行目はヘッダー SKIP_HEADER=1を使うか、PARSE_HEADERを使うか
- カラム数は3つ（$4、$5がnullなので）
この情報を元にkeywordテーブルを作ります。
※こちらはINFER_SCHEMAを使わない方法でいきます
ファイル取込〜INFER_SCHEMAを使わない
create or replace table week3_keyword_table_1 (
keyword text
, added_by text
, nonsense number
, file_name text
, file_row_number number
)
;
select * from week3_keyword_table_1;
create or replace file format week3_csv_format_1
type = CSV
skip_header = 1
;
copy into week3_keyword_table_1 ( keyword, added_by, nonsense, file_name, file_row_number)
from
(
select
$1
, $2
, $3
, metadata$filename
, metadata$file_row_number
from
@week3_ext_stage
)
FILES=('keywords.csv')
FILE_FORMAT = (FORMAT_NAME = 'week3_csv_format');
こちらで取り込めました。
select * from week3_keyword_table_1;
week3_data*.csvの取込
次に、dataを取り込みましょう。
※ただ、こちらは解法には使わないので、必要なかったりする・・・
select
$1
, $2
, $3
, $4
, $5
, $6
, $7
, $8
, metadata$filename
, metadata$file_row_number
from
@week3_ext_stage/week3_data1.csv
;
- ファイル名は、「week3_data*」というパターン
- カラムは5つ（id:number, first_name:text, last_name:text, catch_phrase:text, timestamp:date）
file_nameとfile_row_numberを入れたテーブルを作ります。
create or replace table week3_data_table_1 (
id number
, first_name text
, last_name text
, catch_phrase text
, timestamp date
, file_name text
, file_row_number number
)
;
データを投入します。
copy into week3_data_table_1 ( id, first_name, last_name, catch_phrase, timestamp, file_name, file_row_number)
from
(
select
$1
, $2
, $3
, $4
, $5
, metadata$filename
, metadata$file_row_number
from
@week3_ext_stage
)
PATTERN='challenge_3/week3_.*'
FILE_FORMAT = (FORMAT_NAME = 'week3_csv_format')
;
select * from week3_data_table_1;
COPY INTOの際のPATTERNは注意が必要です。フォルダ名的なのも入れないと引っかからないことがあります。ここは、ステージングのファイルをSELECTで読む時の挙動と違ったりするので、注意が必要です。
keywordの名前を含むファイルを抽出する
こちらで使うテクニックは
- list @stage;
- last_query_id()
- set value = <value>
- result_scan(<id>)
です。
listは、stageのファイル一覧を取得するコマンド
でも、直接Selectでフィルターを噛ますことが出来ません。
そのために使うテクニックが、last_query_id() と result_scan()
last_query_id()は、直前に実行したSQLのQUERY_IDを出力します。
引数指定で、「何回前のクエリID」を取ることはできるのですが、めんどくさい
なので、
set query_id = last_query_id();
-- result_scan($query_id) $をつけてセッション変数として使っている
で、QUERY_IDを保持し、何回でも使えるようにしています。※ちょっとしたテクニック
あとは、INNER JOINと、ONにCONSTRUCTを使うところが、またテクニックでしょうか。
list @week3_ext_stage;
set query_id = last_query_id();
with data_files as (
select
"name" as filename
from
table(result_scan($query_id))
)
-- select * from data_files;
, keyword as (
select keyword from week3_keyword_table_1
)
-- select keyword from keyword;
select
filename
from
data_files
inner join keyword as k
on contains(filename, k.keyword)
;
INFER_SCHEMAを使ったテーブル作成とデータ取り込み
FILE FORMATを作ります。今回は、「PARSE_HEADER」を使います。
create or replace file format week3_csv_format_2
type = CSV
parse_header = true
;
※ちなみに、SKIP_HEADERとPARSE_HEADERは併用できないので注意
create or replace table week3_keyword_table_2
using template (
select array_agg({*})
from table (
infer_schema (
location => '@week3_ext_stage'
, files => 'week3_data1.csv'
, file_format => 'week3_csv_format_2'
, ignore_case => true
)
)
);
desc table week3_keyword_table_2;
INFER_SCHEMAでは、「ignore_case => true」を入れるのがおすすめ。つけないとテーブル名が「"で括らないといけない」形式になります。Snowflake的にもめんどくさいので、私はたいていIGNORE_CASEはつけますね
copy into week3_keyword_table_2
FROM @week3_ext_stage
FILES = ('keywords.csv')
FILE_FORMAT = (FORMAT_NAME = 'week3_csv_format_2')
MATCH_BY_COLUMN_NAME = CASE_SENSITIVE
;
INFER_SCHAMAを使ってはこんな感じで、データの取込をします。
METADATAを付与するのは、
create or replace file format week3_csv_format_2_2
type = CSV
parse_header = true
error_on_column_count_mismatch=false
;
create or replace table week3_keyword_table_2_2
using template (
select
array_cat (
array_agg(object_construct('COLUMN_NAME', column_name, 'TYPE', type, 'NULLABLE', nullable))
-- * にすると16MBを超える場合もあるので、カラムを絞る
, [
{'COLUMN_NAME':'FILENAME', 'TYPE':'STRING', 'NULLABLE':true}
, {'COLUMN_NAME':'FILE_ROW_NUMBER', 'TYPE':'NUMBER', 'NULLABLE':true}
, {'COLUMN_NAME':'START_SCAN_TIME', 'TYPE':'TIMESTAMP_LTZ', 'NULLABLE':true}
]::variant
)
from table (
infer_schema (
location => '@week3_ext_stage'
, files => 'keywords.csv'
, file_format => 'week3_csv_format_2_2'
, ignore_case => true
)
)
);
と、array_aggで、メタデータなカラムを追加してやってから、テーブルを作ります。
copy into week3_keyword_table_2_2
from
@week3_ext_stage
match_by_column_name = case_insensitive
files = ('keywords.csv')
file_format = (FORMAT_NAME = 'week3_csv_format_2_2')
include_metadata = (
filename = METADATA$FILENAME
, file_row_number = METADATA$FILE_ROW_NUMBER
, start_scan_time = METADATA$START_SCAN_TIME
)
;
include_metadataを使って、メタデータを取り込んでいます。
こチラを使うには、FILE FORMATに「error_on_column_count_mismatch=false」をしていないと、取込ファイルとテーブルのカラム数が違ってエラーが出ますので、そこは注意です。
まとめ
今回は、METADATAと言ってはいますが、データの取込の方法とlist @stage の結果をうまく使おうぜ！な解法になってしまいました。
でも、取込の際にMETADATA（特に、FILE_NAME, FILE_ROW_NUMBER）を取り込むことはすごく重要で、常に取り込もうぜ！なので、一度はこちらをやってみることをオススメします。
にてコードを公開しています！
Discussion