はじめに
これは TROCCO Advent Calender 2024 24日目 ＆ Frosty Friday Advent Calender 2024 24日目の記事です。
Frosty Friday で出題される問題は、基本的には Snowsight のエディタでSQLを実行して解ける設定になっており、問題によっては dbt でも解けたりします。（CLIを使う、など指定があるチャレンジもあり）
特に外部ツールを使うな！なんてルールがあるわけじゃないんですね。
ということは、 TROCCO を使っても良いのでは？
ということで、今回の記事では Frosty Friday Week 1 の問題を TROCCO で解いてみます！
Week 1 問題
csv ファイルが置かれた S3 バケットに対して外部ステージを作成し、ステージからテーブルにデータをロードしてね！という内容。
例えばこんな感じでできそうです。[1]
-- 外部ステージを作成
create temp stage if not exists frosty_friday_stage
url = 's3://frostyfridaychallenges/challenge_1/';
-- テーブルを作成
create or replace temp table challenge_1_result (
result varchar,
file_name varchar,
row_number int
);
-- テーブルにデータをロード
copy into challenge_1_result
from (
select
$1::varchar as result,
metadata$filename,
metadata$file_row_number
from
@frosty_friday_stage
)
file_format = (
type = csv
skip_header = 1
null_if = ('NULL')
)
;
最後に次のクエリを実行すると...
select
listagg(result, ' ') within group (order by file_name, row_number) as result
from
challenge_1_result
where
result is not null
and result != 'totally_empty'
;
you have gotten it right congratulations! が現れる、という問題です。
それでは早速 Frosty Friday Challenge していきましょう！
その1
Snowflake 上でのデータ加工なので、データマートで解けそうな気がしますね。
では、データマートを定義していきましょう。
いま実行したいのは CREATE STAGE や COPY INTO なので、クエリ実行モードを「自由記述モード」に設定し、データマートを設定していきます。
データマート定義
クエリ定義のみ抜粋。
frosty_friday_week_1_1：
frosty_friday_week_1_2：
frosty_friday_week_1_3：
作成したデータマート定義を、このようにワークフローで直列に繋げて、実行します。
正常に終了しました。
さて、テーブルにロードされたデータは...
できてますね！
通常の転送ではできない DDL や DML も、データマートの自由記述モードを使えば楽勝ですね。
その2
話は変わるんですが、私、「TROCCO の転送元で一番好きなのは HTTP(S)」って自己紹介に書くぐらいには、好きなんですよ。HTTP(S)転送。それができたら結構何でもできるじゃん可能性無限大じゃん、って思っちゃう機能が好きなんです。
「え、突然何を言い出すんですか、Snowflake に HTTP(S) 転送を使うところなんてないじゃん」と思ったあなた。
Snowflake には REST API がありまして、2024/11/13 に一般提供となっております。
せっかく一般提供になったんだし、もう、叩くしかないですよね？？ね？？？？？？（圧）
ということで、解法その2では、REST API で解いてみようと思います。
準備：Snowflake REST API（OAuth）
一部の操作のために、TROCCO で Snowflake REST API を呼べるようにしておきます。Snowflake REST API で使用可能な認証方法はキーペア認証（JWT トークンを使用）と OAuth 認証の2つです。
ここでは、Snowflake OAuth を構成し、OAuth 認証で REST API を呼んでみます。
Snowflake 側での設定
まずは Snowflake で セキュリティ統合（Snowflake OAuth）を作成。
use role accountadmin;
create or replace security integration trocco_oauth
type = oauth
oauth_client = custom
oauth_client_type = 'CONFIDENTIAL'
oauth_redirect_uri = 'https://trocco.io/connections/http/callback'
enabled = true
;
oauth_redirect_uri に指定する URL は、TROCCO ドキュメント 「HTTP・HTTPSの接続情報」に記載されているリダイレクトURLを指定します。
セキュリティ統合を作成したら、OAUTH_CLIENT_ID と OAUTH_CLIENT_SECRET の値を取得しておきます。
select system$show_oauth_client_secrets( 'TROCCO_OAUTH' );
出力（ダミー）：
{
"OAUTH_CLIENT_SECRET_2":"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=",
"OAUTH_CLIENT_SECRET":"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=",
"OAUTH_CLIENT_ID":"AAAAAAAAAAAAAAAAAAAAAAAAAAA="
}
TROCCO 側の設定
続いて、TROCCO 側で接続情報を作成します。先ほど参照した「HTTP・HTTPS接続情報」で設定していきます。
今回設定した項目は次のとおり。他の項目は未設定（デフォルト）です。
| 設定項目 | 値 |
|---|---|
| グラントタイプ | 認可コード |
| クライアントID | OAUTH_CLIENT_ID |
| クライアントシークレット | OAUTH_CLIENT_SECRET |
| 認可URL |
<snowflake_account_url>/oauth/authorize[3]
|
| アクセストークンURL |
<snowflake_account_url>/oauth/token-request[3:1]
|
実際の設定画面はこんな感じです。
設定を入力したら、「認証」ボタンを押下。Snowflake へのサインインを求められるので、適切な認証情報（API 叩くマン）でサインインする。
この表示が出ればOK！
REST API でやってみよう
先ほどと同じく、ワークフローで実行します。
HTTP（ワークフローでは「HTTP リクエスト」）で実行するのは、CREATE STAGE、CREATE TABLE の箇所です。
HTTPリクエストの設定内容
frosty_friday_week_1_1：
詳細はこちらを参照：
frosty_friday_week_1_2：
詳細はこちらを参照：
COPY INTO は REST API 対応一覧になかった（2024年12月25日現在）ので、一旦データマートのものを拝借。
では実行していきます。。。
正常に終了したので、ロード先のテーブルを確認しに行きます。その1とはテーブル名を変更しています。（challenge_1_result → challenge_1_result_2）
問題なくロードされていました！
おわりに
Frosty Friday には TROCCO のような外部ツールを使う楽しみ方もあるぞ！と言いたい一心でやってみました。
Frosty Friday Live Challenge では出演者を募集しています。
自分の推しツールでこんなことができるぞアピールしたい方自分だけの解法を紹介してみたい方、ぜひぜひお声がけください！！
-
手前味噌で恐縮ですが、私の解答例です https://github.com/allllllllez/frosty_friday/blob/main/sql/week-1.sql ↩︎
-
写真提供...gakuさん（Frosty Friday も TROCCO もありがとうございます！） ↩︎
Discussion