Zenn
✨

第2回Snowflake Rookies Camp ～FrostyFriday week3～

に公開
SQL
Snowflake
tech

こんばんは！
Snowflake Rookies Camp幹事のKaoriです。
Snowflake Rookies Camp　第2回の勉強会ハンズオン予定のFrostyFriday Week3の回答例を確認できるように事前に準備しておきました。予習・復習にご利用ください！

FrostyFriday　Week3の回答例

SYSADMINロールで作業を行いたいが、SYSADMIN単体ではcompute_whの利用権限が無いため
ACCOUNTADMIN→SYSADMINに権限を付与する

use role accountadmin;
grant usage on warehouse compute_wh to role sysadmin;

compute_whの利用権限を付与したら、SQLを実行するアカウントをSYSADMINに変更する

use role sysadmin;

クエリ実行時に使用するWarehouseをcompute_whに設定する

use warehouse compute_wh;

データベースをまず定義し、その中にスキーマ・テーブルを作成する
まずはデータベース作成（空っぽの枠だけ）

create or replace database db_week3;

次にスキーマ作成（空っぽの枠だけ）

create or replace schema schema_week3;

S3のデータを参照してSnowflakeの外部ステージを作成

create or replace stage stage_week3
url = 's3://frostyfridaychallenges/challenge_3/';

ステージ内のファイル一覧を確認（どのようなファイルが入っているか確認）

list @stage_week3;

keywords.csvの内容を確認
1カラム目～4カラム目のフィールドを指定→3カラム目までデータが入っている
必要なのは1カラム目のkeyword
$記法は位置ベースのカラム参照（CSVでヘッダが無いorわからない場合に使用する）

SELECT $1,$2,$3,$4 FROM @stage_week3 (PATTERN => '.*keywords\.csv');

検索対象データの構造を確認するためのサンプル読み込み
HPに書いてあった回答例のファイルを1つ確認してみる

SELECT 
  METADATA$FILENAME, -- 読み込んだファイル名
  METADATA$FILE_ROW_NUMBER, -- 行番号
  $1,$2,$3,$4,$5,$6 
FROM @stage_week3 (PATTERN => '.*week3_data2_stacy_forgot_to_upload\.csv');

ファイルフォーマットを定義する
CSVファイルであること、1行目を飛ばすという定義を設定

create file format file_format_week3
  type = 'CSV' -- ファイルタイプ
  field_delimiter = ',' -- 区切り文字の設定
  skip_header = 1; -- 1行目をスキップ（ヘッダー付きCSV対応）

【1つ目】keywordテーブル（空）を定義

create table Table_keyword_week3 (
  keyword   varchar,
  added_by  varchar,
  nonsense  varchar
);

【2つ目】検索対象テーブル（空）を定義

create or replace table Table_SEARCH_week3 (
  FILENAME        VARCHAR,
  FILE_ROW_NUMBER INT,
  ID              varchar,
  FIRST_NAME      varchar,
  LAST_NAME       varchar, 
  CATCH_PHRASE    VARCHAR,
  TIMESTAMP       date
);

KEYWORDテーブルにS3からデータロード

COPY INTO Table_keyword_week3 FROM (
  SELECT $1,$2,$3 FROM @stage_week3
)
PATTERN = '.*keywords\.csv'
file_format = 'file_format_week3';

KEYWORDテーブルの内容を確認

SELECT * FROM TABLE_KEYWORD_WEEK3;

検索対象テーブルにS3からデータロード（全部のファイルを入れる）

COPY INTO Table_SEARCH_week3 FROM (
  SELECT 
    METADATA$FILENAME,
    METADATA$FILE_ROW_NUMBER,
    $1,$2,$3,$4,$5 
  FROM @stage_week3
)
file_format = 'file_format_week3';

keywords.csvのkeywordを含むファイル名をSEARCHテーブルから検索

SELECT * 
FROM table_search_week3
WHERE EXISTS (
  SELECT keyword
  FROM table_keyword_week3
  WHERE CONTAINS(table_search_week3.filename, table_keyword_week3.keyword)
);

最終的な答えと一致するようにファイル名で集計する

SELECT filename, COUNT(*) 
FROM table_search_week3
WHERE EXISTS (
  SELECT keyword
  FROM table_keyword_week3
  WHERE CONTAINS(table_search_week3.filename, table_keyword_week3.keyword)
)
GROUP BY filename;

コード全文

use role accountadmin;
grant usage on warehouse compute_wh to role sysadmin;

use role sysadmin;
use warehouse compute_wh;

create or replace database db_week3;
create or replace schema schema_week3;

create or replace stage stage_week3
url = 's3://frostyfridaychallenges/challenge_3/';

list @stage_week3;
SELECT $1,$2,$3,$4 FROM @stage_week3 (PATTERN => '.*keywords\.csv');
SELECT 
  METADATA$FILENAME, -- 読み込んだファイル名
  METADATA$FILE_ROW_NUMBER, -- 行番号
  $1,$2,$3,$4,$5,$6 
FROM @stage_week3 (PATTERN => '.*week3_data2_stacy_forgot_to_upload\.csv');

create file format file_format_week3
  type = 'CSV' -- ファイルタイプ
  field_delimiter = ',' -- 区切り文字の設定
  skip_header = 1; -- 1行目をスキップ（ヘッダー付きCSV対応）

create table Table_keyword_week3 (
  keyword   varchar,
  added_by  varchar,
  nonsense  varchar
);

create or replace table Table_SEARCH_week3 (
  FILENAME        VARCHAR,
  FILE_ROW_NUMBER INT,
  ID              varchar,
  FIRST_NAME      varchar,
  LAST_NAME       varchar, 
  CATCH_PHRASE    VARCHAR,
  TIMESTAMP       date
);

COPY INTO Table_keyword_week3 FROM (
  SELECT $1,$2,$3 FROM @stage_week3
)
PATTERN = '.*keywords\.csv'
file_format = 'file_format_week3';

SELECT * FROM TABLE_KEYWORD_WEEK3;

COPY INTO Table_SEARCH_week3 FROM (
  SELECT 
    METADATA$FILENAME,
    METADATA$FILE_ROW_NUMBER,
    $1,$2,$3,$4,$5 
  FROM @stage_week3
)
file_format = 'file_format_week3';

SELECT * 
FROM table_search_week3
WHERE EXISTS (
  SELECT keyword
  FROM table_keyword_week3
  WHERE CONTAINS(table_search_week3.filename, table_keyword_week3.keyword)
);

SELECT filename, COUNT(*) 
FROM table_search_week3
WHERE EXISTS (
  SELECT keyword
  FROM table_keyword_week3
  WHERE CONTAINS(table_search_week3.filename, table_keyword_week3.keyword)
)
GROUP BY filename;

Discussion