❄️
第1回Snowflake Rookies Camp開催～Frosty Friday Week1に挑戦～

2025/02/21に公開
Snowflake Rookies Camp 幹事メンバーのhueです。

この記事では2025年2月12日と2025年2月18日に開催した

「第1回Snowflake Rookies Camp～初心者向け勉強会～」の内容を解説させていただければと思います。

改めて、当日参加いただいた皆さん、ありがとうございます！！
勉強会で実施した内容はFrosty Fridayのweek1となります。

 Frosty Friday Week 1取り組む内容は

FrostyFriday Inc., your benevolent employer, has an S3 bucket that is filled with .csv data dumps. This data is needed for analysis. Your task is to create an external stage, and load the csv files directly from that stage into a table.
要は

外部ステージにあるcsvデータをSnowflakeに取り込んでテーブルに書き込みをしよう！

 Week１図解

 ログインまず、snowflakeにログインし、「＋作成」から「SQLワークシート」を選択

すると、シートが作成されます。

忘れずにタイトルを変えて実際に処理していきましょう！


 ①ウェアハウス利用権限割当※クエリの全体像は最後に！
No.1
--SYSADMINを前提でSQLを動かそうとすると、SYSADMINにWAREHOUSEを使う権限設定が必要
--ACCOUNTADMINはデフォルトで一番権限が強いので、最初にACCOUNTADMIN→SYSADMINに権限を渡してあげる
USE ROLE ACCOUNTADMIN;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE SYSADMIN;
--権限を付与したら自分の権限をSYSADMINに変更し、それを使用する
USE ROLE SYSADMIN;
--自分が使うウェアハウスをCOMPUTE_WHに設定する
USE WAREHOUSE COMPUTE_WH;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE SYSADMIN;

を実行すると、、、
メニューの「管理者」から「ウェアハウス」を選択



「SYSADMIN」に「USAGE」と付くようになります。

 ②データベース作成No.2
--データベース作成(空っぽの枠だけ)
CREATE OR REPLACE DATABASE DB_WEEK1;
この処理で「データベース」に「DB_WEEK1」が作成されます。作成されていない場合、右上の矢印をクリックしましょう！


 ③スキーマ作成No.3
--スキーマ作成(空っぽの枠だけ)
CREATE OR REPLACE SCHEMA SCHEMA_WEEK1;
実行すると、DB_WEEK1内にスキーマ「SCHEMA_WEEK1」が作成されます。



No.3:スキーマ作成

 ④外部ステージ作成No.4
--S3のデータを参照してSNOWFLAKEの外部ステージを作成
CREATE OR REPLACE STATGE STAGE_WEEK1
url = 's3://frostyfridaychallenges/challenge_1/';
実行するとステージが作成されます。



No.4:ステージ作成

ステージのデータはこちらのコマンドで見ることができます。
No.5
LIST @STAGE_WEEK1;
結果からcsvと名前の付いたファイルと確認できます。



No.5:結果
今回、デモ中にselectを実施しました
SELECT * FROM @STAGE_WEEK1;


ステージは * 使うのは×
SELECT $1,$2 FROM STAGE_WEEK1;


ステージの前に＠を入れないと×
データの内容を確認する場合はこのようにしましょう！
NO.6
SELECT $1,$2 FROM @STAGE_WEEK1;
※ $1は１列目を表しています。

クエリの結果はこちら。


No.6:結果
No.5の結果からファイルは３つ
１列目にしかデータが入っていない
文章になっている
「result」という文字が間隔をあけて入ってきている

ということがわかります。
確認のためのクエリをもう１つ実施
No.7
SELECT 
  $1,
  metadata$filename,
  metadata$file_row_number
FROM @STAGE_WEEK1
ORDER BY metadata$filename,metadata$file_row_number ;
やっていることは
１列目の情報を取得
ファイルの名称を取得（metadata$filename）
ファイルの行数を取得する（metadata$file_row_number）

metadata〜〜はsnowflake特有のもの！こちらを参照ください！



No.7:結果

 ⑤テーブル作成No.8
CREATE OR REPLACE TABLE TABLE_WEEK1(
    result varchar,
    filename varchar,
    file_row_number int
);
コマンドを実行することでテーブルが作成されます。



No.8:テーブル作成

 ⑥データロード（COPY INTO）No.9
COPY INTO TABLE_WEEK1 FROM(
  SELECT
    $1,
    metadata$filename,
    metadata$file_row_number FROM @STAGE_WEEK1
);
データをS3からsnowflakeに取り込むことができました!

実際にできたデータを確認していきましょう。
No.10
SELECT * FROM TABLE_WEEK1 ORDER BY filename, file_row_number;


No.10:結果

No.7:結果でもわかりますが、各ファイルの１行目に「result」という文字が入っているようです。
No.11
--resultがヘッダ行(行番号1)なのでそれを除いて確認してみる
SELECT * FROM TABLE_WEEK1 
WHERE file_row_number !=1
ORDER BY filename, file_row_number;


No.11:結果
「You have gotten it right congraturations!」

訳：あなたはそれを正しく理解した。

（Nullやemptyの文字も含まれていますが、勉強会ではここまで！！）
これで完成！！
今回学んでいただきたいことは
S3のデータもSnowflakeに取り込める
アカウントに権限を割り当ててから実施しよう
ステージのデータを見るときにはお作法がある
SnowflakeにはMETADATAを取得する手段がある
お疲れ様でした！！

今後も継続的に初心者でも取り組みやすい課題を扱っていこうと思います！

 実施したクエリ全量--SYSADMINを前提でSQLを動かそうとすると、SYSADMINにWAREHOUSEを使う権限設定が必要
--ACCOUNTADMINはデフォルトで一番権限が強いので、最初にACCOUNTADMIN→SYSADMINに権限を渡してあげる
USE ROLE ACCOUNTADMIN;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE SYSADMIN;
--権限を付与したら自分の権限をSYSADMINに変更し、それを使用する
USE ROLE SYSADMIN;
--自分が使うウェアハウスをCOMPUTE_WHに設定する
USE WAREHOUSE COMPUITE_WH;

--データベース・スキーマの作成(空っぽの枠だけ)
CREATE OR REPLACE DATABASE DB_WEEK1;
CREATE OR REPLACE SCHEMA SCHEMA_WEEK1;

--S3のデータを参照してSNOWFLAKEの外部ステージを作成する
CREATE OR REPLACE STAGE STAGE_WEEK1
url = 's3://frostyfridaychallenges/challenge_1/';

--格納されているファイル一覧を下記listコマンドでみられる
LIST @STAGE_WEEK1;

--select *だとエラーになってデータが出ないよの確認
SELECT * FROM @STAGE_WEEK1;
--ステージは前に@を入れないとエラーになってデータが出ないよの確認
SELECT $1,$2 FROM STAGE_WEEK1;

--項目に$を入れて、ステージの前に@を入れるとデータの内容が見られる
SELECT $1,$2 FROM @STAGE_WEEK1;

--取得したステージの内容を確認する
--ファイルが3つあったので、ファイルの順番に並べるには、メタデータをとる必要がある。
--ファイル名：METADATA$FILENAME 行番号:METADATA$FILE_ROW_NUMBERで出せる(メタデータは下記公式ページから確認できる)
--https://docs.snowflake.com/ja/user-guide/querying-metadata#metadata-columns
SELECT $1,
    metadata$filename,
    metadata$file_row_number FROM @STAGE_WEEK1
ORDER BY metadata$filename,metadata$file_row_number ;

   
--ステージの内容でCOPY INTOを使ってテーブルを作ってデータを入れてみる
--するとエラーが出ちゃう
COPY INTO TEST_TABLE_WEEK1 FROM @STAGE_WEEK1;
--テーブルを作る(枠だけ)
CREATE OR REPLACE TABLE TEST_TABLE_WEEK1(
    RESULT VARCHAR
);
--もう1回入れてみる→枠を作っていたらうまくいくはず
COPY INTO TEST_TABLE_WEEK1 FROM @STAGE_WEEK1;
--テーブル内容を確認 項目は見ることができるが、順番はランダムになっている
SELECT * FROM TEST_TABLE_WEEK1;

--もう１回テーブルを作る(枠だけ・FilenameとFile row numberを追加)
CREATE OR REPLACE TABLE TABLE_WEEK1(
    result varchar,
    filename varchar,
    file_row_number int
);

--外部ステージの内容をテーブルにコピーする
--file nameとfile row numberも一緒にコピーする
COPY INTO TABLE_WEEK1 FROM(
  SELECT
    $1,
    metadata$filename,
    metadata$file_row_number FROM @STAGE_WEEK1
);

--テーブル内容を確認
SELECT * FROM TABLE_WEEK1 ORDER BY filename, file_row_number;

--resultがヘッダ行(行番号1)なのでそれを除いて確認してみる
SELECT * FROM TABLE_WEEK1 
WHERE file_row_number !=1
ORDER BY filename, file_row_number;

 Frosty Friday Live Challenge！我々幹事のフォローとして

Frosty Friday Live ChallengeでForsty Fridayの解説をされているtomoさん、がくさんに参加いただきました。
tomoさんの作成されたクエリはこちらです！！
がくさんはこちらです！！
nullやtoday_emptyを除外されています。ぜひ参考に実施してください！

 当日いただいた質問を紹介！一部回答を共有します！
AWS S3とsnowflake上のテーブルを同期させることは可能なのでしょうか
→AWS S3にあるファイルを置かれた瞬間にロードするというSnowpipeという仕組みを使えば可能です。
S3上のデータを直接snowflakeから触ることは可能なのでしょうか
→可能です。外部テーブル（External Table）という仕組みをつかうとSELECTであればできます。（InsertやDeleteはできない）ただ外部テーブルはパフォーマンス的にはやはりSnowflakeのテーブルより遅くなるので、少ないデータなどの場合はおすすめな仕組みです。
select $1 from @stage の$1ってなんでしょうか？
→$1はファイルの1カラム目という意味になります。4カラム目を見たい場合は$4になります。
S3においてるファイルは普通のCSVでしょうか（それともSnowflake用に特別な加工されたものでしょうか）
→CSVですが、タイトル有無、カンマ区切りとかタブ区切りとかもオプションで指定できます。

カンマ区切りであったり、タブであったりは以下のようなというパラメータで指定します。 FILE_FORMAT = ( TYPE = CSV)

COPY INTO のリファレンスはこちらを御覧ください。
GRANT ~ COMPUTE_WHはもともと存在するUSAGEに権限を付与する？それとも、GRANTで作成される？？
→作成されるのではなく、accountadminの権限をsysadminに付与します。

下位のロールは上位のロールの権限を使えません。
他にも多くの質問をいただきました！

SnowVillage内にある、snowflake-rookies-campにも質問いただき回答させていただいております！

ぜひSnowVillageへ参加のうえRookies Campのチャンネルもお探しください！
Frosty Friday Week 1

Week１図解

ログイン

①ウェアハウス利用権限割当

②データベース作成

③スキーマ作成

④外部ステージ作成

⑤テーブル作成

⑥データロード（COPY INTO）

実施したクエリ全量

Frosty Friday Live Challenge！

当日いただいた質問を紹介！

Discussion
