Frosty Friday TROCCO Challenge ～データマートによる解法 REST APIを添えて～

はじめに

これは 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 問題

https://frostyfriday.org/blog/2022/07/14/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 上でのデータ加工なので、データマートで解けそうな気がしますね。

https://documents.trocco.io/docs/trocco-tutorial-datamart-definitions

では、データマートを定義していきましょう。

いま実行したいのは 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)転送。それができたら結構何でもできるじゃん可能性無限大じゃん、って思っちゃう機能が好きなんです。

^[2]

「え、突然何を言い出すんですか、Snowflake に HTTP(S) 転送を使うところなんてないじゃん」と思ったあなた。
Snowflake には REST API がありまして、2024/11/13 に一般提供となっております。

https://medium.com/snowflake/announcing-ga-for-snowflake-rest-python-control-plane-apis-empowering-developers-with-d39f6f5cc6ce

せっかく一般提供になったんだし、もう、叩くしかないですよね？？ね？？？？？？（圧）

ということで、解法その2では、REST API で解いてみようと思います。

準備：Snowflake REST API（OAuth）

一部の操作のために、TROCCO で Snowflake REST API を呼べるようにしておきます。Snowflake REST API で使用可能な認証方法はキーペア認証（JWT トークンを使用）と OAuth 認証の2つです。
https://docs.snowflake.com/en/developer-guide/snowflake-rest-api/authentication

ここでは、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を指定します。

https://documents.trocco.io/docs/connection-configuration-http-https

簡単のため、最小限のパラメータで作成しています。実際に構成する際は必要に応じてネットワークポリシー等々設定しましょうね！全てのパラメータはドキュメントをご参照ください：
https://docs.snowflake.com/ja/sql-reference/sql/create-security-integration-oauth-snowflake

セキュリティ統合を作成したら、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：

詳細はこちらを参照：
https://docs.snowflake.com/developer-guide/snowflake-rest-api/reference/stage#post--api-v2-databases-database-schemas-schema-stages

frosty_friday_week_1_2：

詳細はこちらを参照：
https://docs.snowflake.com/developer-guide/snowflake-rest-api/reference/table#post--api-v2-databases-database-schemas-schema-tables

COPY INTO は REST API 対応一覧になかった（2024年12月25日現在）ので、一旦データマートのものを拝借。

では実行していきます。。。

正常に終了したので、ロード先のテーブルを確認しに行きます。その1とはテーブル名を変更しています。（challenge_1_result → challenge_1_result_2）

問題なくロードされていました！

おわりに

Frosty Friday には TROCCO のような外部ツールを使う楽しみ方もあるぞ！と言いたい一心でやってみました。

Frosty Friday Live Challenge では出演者を募集しています。
自分の推しツールでこんなことができるぞアピールしたい方自分だけの解法を紹介してみたい方、ぜひぜひお声がけください！！

脚注

1. 手前味噌で恐縮ですが、私の解答例です https://github.com/allllllllez/frosty_friday/blob/main/sql/week-1.sql

2. 写真提供...gakuさん（Frosty Friday も TROCCO もありがとうございます！）

3. https://docs.snowflake.com/en/user-guide/oauth-custom