やってみた: Shopify → Fivetran → Snowflake → dbt Cloud → steep PART 1

pre-amble
Snowflake 入門
steps
感想

pre-amble

「Shopify のデータを Fivetran で ETL して、 semantic layer を使って BI にデータ出してみよう」というお題をいただいたのでやってみた。今回の記事は dbt Cloud に持ってくとこまで。

特別なことをしてないのでわりとチュートリアル通りだけど、概念の整理とかトラシューとかを書いていく。

ETL セットアップの流れとしてはこんな感じ↓

Fivetran に Snowflake destination を作成
Shopify の Fivetran connector を作る
Snowflake のスキーマ確認
dbt Cloud の接続

Snowflake 入門

Fivetran に destination を作る前に、そもそも Snowflake が初見なので 30分くらいで入門してみた。30分は盛ってるかもだけど、半日はかけなかった。

アカウント作成

Snowflake は free trial ができるので、とりあえずアカウントを作る。すると、自動的に Getting started 系のデータや Worksheets っていうやつを作ってくれるので、そこで UI の操作とか概念を学べる。

ちなみに、アカウント選択時に「よく使う言語はなんですか?」と聞かれて、そこで選択した言語に応じてサンプル Worksheets を作ってくれる。自分は Python と SQL を選択したので、こういう感じで作ってもらえた↓

各種リソース

自分の理解だと、リソースはだいたいこんな感じ↓

Worksheets
Data
Warehouse
Users, Roles

Worksheets

Worksheets っていうのは「ファイル」的なもので、 Python, SQL, Java など各種言語で記述できる。

驚きポイントとしては、 Worksheet 1枚で ETL が実現できることで、 Getting started の Worksheet だと S3 の public データから Snowflake にデータを流すことまでやってた。

SQL の Worksheet のコードを抜粋するとこうなる↓

-------------------------------------------------------------------------------------------
    -- Step 3: To connect to the Blob Storage, let's create a Stage
        -- Creating an S3 Stage: https://docs.snowflake.com/en/user-guide/data-load-s3-create-stage
-------------------------------------------------------------------------------------------

---> create the Stage referencing the Blob location and CSV File Format
CREATE OR REPLACE STAGE tasty_bytes_sample_data.public.blob_stage
url = 's3://sfquickstarts/tastybytes/'
file_format = (type = csv);

---> query the Stage to find the Menu CSV file
LIST @tasty_bytes_sample_data.public.blob_stage/raw_pos/menu/;


-------------------------------------------------------------------------------------------
    -- Step 4: Now let's Load the Menu CSV file from the Stage
        -- COPY INTO <table>: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table
-------------------------------------------------------------------------------------------

---> copy the Menu file into the Menu table
COPY INTO tasty_bytes_sample_data.raw_pos.menu
FROM @tasty_bytes_sample_data.public.blob_stage/raw_pos/menu/;

Data

データ系のリソースは階層になってて、 Database > Schema > Table という感じ。たとえば、下の Fivetran → Shopify 用の Database だと、

FIVETRAN_DATA という Database の中に
SHOPIFY という Schema があって、
その中に ORDER CUSTOMER PRODUCT という各種テーブルがある、という構造になる。

自分は BigQuery の2層構造に慣れていたので Snowflake の3層構造に若干戸惑ったけど、 SQL を書くときは

select count(*) from "FIVETRAN_DATA"."SHOPIFY"."ORDER"

と、 BQ の <project>.<dataset>.<table or view> というのとあんまり変わらないのでそこまで違和感はないかも?

Warehouse

ここが一番わかりにくかったんだけど、 Warehouse とはざっくりいうと "compute server" である、という Reddit の投稿がわかりやすかった。

In summary, a warehouse is just a computer server. In Snowflake you need one because a warehouse does the work of running a query.

Databases and schemas are just an organizational structure for tables. There are other things that can live inside schemas, like views and UDFs. You need to know about this structure because Snowflake wants you to be specific about which tables to access. A well used Snowflake can have thousands of tables, so this structure does help.

https://www.reddit.com/r/snowflake/comments/13xjxly/can_someone_please_explain_accessing_warehouses/?rdt=52733

Warehouse の画面は↓の2枚のような感じで、たしかにメニューも Data のところじゃなくて Admin のところにあるし、 "Size" という項目もあるので、「サーバっぽい」っていうのは納得できた。名前的に最初 BQ の dataset なのか...? って思ってたけど全然違った、ワラ。

Users/Roles

ここは割と名前の通りで、ブラウザでログインしている自分用の User や、 Fivetran とか dbt に接続する User がいる。 Role も名前の通り User に対して付与するもの。

ちなみに、 Roles の画面だとこういうグラフも見れて感動した↓

Users, Roles & Privileges with Worksheets

privilege って単語、毎回綴りを毎回間違えるんだけど

Snowflake の Worksheet 画面では右上で実行する Role と Warehouse を設定できて、これを Context とよぶ。実行する「権限」と実行する「環境」を選んでるっていうのが自分の理解。

Role は右上から選ぶ以外にも、 Worksheet の中で

use role hoge_role;
create database hoge_db;

のように途中で role を切り替えることもできるし、 use warehouse hoge_warehouse も構文としてあるので、 warehouse も切り替えられそう (実際 Getting started だと use role も use warehouse もあった)。

User には Role を付与し、 Role には Privilege を付与することで誰が何をできるっていうのを制御できて、たとえば

grant select on database hoge_db to role hoge_role;

のような SQL 文で管理できる。次の ETL の設定ではこの権限周りが結構だるかった。

steps

1. Snowflake destination として作成

基本 Fivetran のガイド通りにつくるんだけど、ちょくちょくトラブって、 Fivetran の提供する Worksheet 用のコードをいじる必要があった。

ガイド: https://fivetran.com/docs/destinations/snowflake/setup-guide

今回はとりあえずつなぐことが目的だったので、プライベート接続的なセキュアにするオプションは全部すっとばしたところ、ガイドにある Worksheet コードを動かすところがここの作業の9割だった。

トラブったところは GRANT 系の SQL で、適宜 use role の位置をずらしたり、実行している Role へ GRANT を発行したりしたのだが、正しいのかちょっと怪しいので作業内容は割愛 (次回整理するという目的もこめつつ...)。

2. Shopify connector を作成

これもガイド通りに Shopify の自分のストアに Fivetran app をインストールすれば OK。ポイントとして、接続する前に Shopify にブラウザでログインしておくとスムーズかも。画面遷移的には

Shopify connector を新しくつくる画面
→ install Fivetran app のリンクをクリック
→ Shopify に遷移
→ また Shopify connector を新しくつくる画面

という流れなので、若干 janky ではあった。

3. dbt Cloud プロジェクトの作成

多分 dbt core でもできるような気がするが、そっちは前にやったことがあるのでマネージド版を使ってみたくて dbt Cloud の方にした。

基本は↓の Quickstart に沿いつつ、構成が若干違うので適宜アレンジする。
https://docs.getdbt.com/guides/snowflake

Quickstart の 1-3 はSnowflake にデータをいれる話なので、 4 の "Connect dbt Cloud to Snowflake" からやる。接続には manual と Partner Connect っていう自動で接続設定を作ってくれる機能を使う2つの方法があって、今回は Partner Connect を使ってみた。

Partner Connect は Snowflake ↔ dbt の partner で、 Snowflake 側の market place で dbt を選択すると、自動で dbt Cloud に Snowflake の接続設定を持ったアカウントを作ってくれる。

この「アカウント」っていうのがポイントで、実はもともとメールアドレス1で Snowflake 、メールアドレス2で dbt Cloud のアカウントをそれぞれ持っていて、てっきり Snowflake で Partner connect をクリックしたら dbt Cloud の認証画面に行くのかと思ったんだけど、いきなり Snowflake のメールアドレス1で dbt Cloud のアカウントごと作成されて若干戸惑った。まあ、別にどちらのメアドもプライベート用なのでいいんだけど一瞬混乱した。

あとは、 QuickStart だと Partner Connect をつくるときに dbt が Snowflake の Database から読めるように権限をつけているんだけど、自分はそれをしていなかったので↓の GRANT を発行した。

grant usage on database fivetran_data to pc_dbt_role;
grant usage on schema fivetran_data.shopify to pc_dbt_role;
grant select on all tables in schema fivetran_data.shopify to pc_dbt_role;
grant select on future tables in schema fivetran_data.shopify to pc_dbt_role;