More than 1 year has passed since last update.

Snowflake x dbt サクッと初期設定

Last updated at 2023-10-09Posted at 2023-10-07

サクッとまとめ

自端末はmacの想定とする。

Snowflakeアカウントを作成する
- こちらから作成する
- SnowflakeのWebUIにログインする
- 以下を控えておく
  - ユーザー名
  - パスワード
  - アカウント識別子(XXXXXXX.XX00000 のような値。dbtに設定する際は「.」を「-」に読み替えておく。)
- SYSADMINユーザーでウェアハウスを作成しておく
  - 例 : MY_WH
  - サイズ等他の設定は任意
- SYSADMINでデータベースを作成しておく
  - 例 : MY_DB
- SYSADMINでスキーマを作成しておく
  - 例 : MY_SCHEMA
dbt (Snowflake) を自端末にインストールする
- pip install dbt-snowflake

dbtの初期設定を行う

dbt init my_dbt (my_dbtは任意の名前)

以降、表示されたプロンプトに従う

Enter a number: 1
account (https://<this_value>.snowflakecomputing.com): アカウント識別子(ただし「.」は「-」に書き換えること)
user (dev username):ユーザー名
Desired authentication type option (enter a number):1
password (dev password):パスワード
role (dev role):SYSADMIN
warehouse (warehouse name):作成したウェアハウス(例:MY_WH)
database (default database that dbt will build objects in):作成したデータベース(例:MY_DB)
schema (default schema that dbt will build objects in):作成したスキーマ(例:MY_SCHEMA)
threads (1 or more) [1]: 1 (何も入力せずエンターでOK)

作成されたディレクトリに移動 cd my_dbt
dbt debug を実行して接続確認
dbt run を実行してSnowflakeにオブジェクトを作成してみる

Snowflake(WebUI)でdbtの実行結果を確認する

ワークシート(SQL)を開く

以下を実行する

use role sysadmin;
use warehouse my_wh;
select * from my_db.my_schema.my_first_dbt_model;
select * from my_db.my_schema.my_second_dbt_model;

ここまででとりあえずの初期セットアップとしてはOKかな。

個人的dbtの活用方法

Snowflakeトライアルアカウントを作成する度に実行して同じ環境を再現できる
からの...
- Snowflakeの勉強、挙動確認など
- SQLの練習

といった感じ。

もうちょい設定

とりあえずSnowflakeとdbtの初期設定ができたので、もうちょい使えるようにカスタマイズする。

キーペアで認証する方式に変えてみる

個人用途ならパスワード認証でもよさそうだが、実践向けの設定も兼ねてやってみる。

また、dbtでキーを使用する際の設定について少し注意点があるため、その件もここで理解しておきたいので取り上げておく。

Snowflake用のキーペアを作成する

以下を実行する。

mkdir ~/.snowflake
openssl genrsa 4096 | openssl pkcs8 -topk8 -inform PEM -v2 aes-256-cbc -out ~/.snowflake/snowflake_key.p8 -nocrypt
openssl rsa -in ~/.snowflake/snowflake_key.p8 -pubout -out ~/.snowflake/snowflake_key.pub

公開鍵をSnowflakeに設定する

以下のコマンドを実行して、公開鍵をコピーする。

cat ~/.snowflake/snowflake_key.pub | tr -d "\n" | sed "s/-----BEGIN PUBLIC KEY-----//g" | sed "s/-----END PUBLIC KEY-----//g" | pbcopy

Snowflakeにログインし、SQLワークシートを開く。
上記コマンドで取得したキーを貼り付けて実行する。

ALTER USER ユーザー名 SET RSA_PUBLIC_KEY = 'MIICI....';

dbtの認証でキーを使用する

ここを見ると、The private_key string should be in either Base64-encoded DER format, representing the key bytes, or a plain-text PEM formatと書いてある。

いくつかやり方がある模様。

(通常はこちらがよさそう) PEMを使用する場合

作成した秘密鍵のパスを指定すれば使用できる。
dbtの設定ファイル ~/.dbt/profiles.yml を編集する。

vi ~/.dbt/profiles.yml

以下のように「password」部分を削除して、「private_key_path」を追記する。

my_dbt:
  outputs:
    dev:
      account: アカウント識別子
      database: MY_DB
      # password: パスワード
      role: SYSADMIN
      schema: MY_SCHEMA
      threads: 1
      type: snowflake
      user: ユーザー名
      warehouse: MY_WH
      private_key_path : /Users/[自端末のユーザー名]/.snowflake/snowflake_key.p8
  target: dev

ここでprivate_key_pathを指定する際の注意点は、~/.snowflake/snowflake_key.p8が使用できない点。
dbt run で以下のエラーになってしまう。

dbt run

...

15:25:20  Encountered an error:
Runtime Error
  Database error while listing schemas in database "MY_DB"
  Database Error
    [Errno 2] No such file or directory: '~/.snowflake/snowflake_key.p8'

(別案) DER形式のキーを作成する

The private_key string should be in either Base64-encoded DER formatと書いてある部分に注目する。

この記載に従って、dbt向けにDER形式の鍵を作成してみる。

openssl rsa -inform PEM -outform DER -in ~/.snowflake/snowflake_key.p8 -out ~/.snowflake/snowflake_key.der

cat  ~/.snowflake/snowflake_key.der | base64 | pbcopy

ここで得たキー値を、dbtの設定ファイル ~/.dbt/profiles.yml に書き込む。

vi ~/.dbt/profiles.yml

以下のように「password」部分を削除して、「private_key」を追記してキーを貼り付ける。

my_dbt:
  outputs:
    dev:
      account: アカウント識別子
      database: MY_DB
      # password: パスワード
      role: SYSADMIN
      schema: MY_SCHEMA
      threads: 1
      type: snowflake
      user: ユーザー名
      warehouse: MY_WH
      private_key : 'MIIJK.....'
  target: dev

認証を試す

以下が通ればOK。

dbt debug

キーペア認証でdbt runができるようになる。

dbt run

これで認証、接続周りの土台ができたかな。

seedでテストデータを入れてみる

例えば、こちらの達人SQL本のSQLを元に勉強したいので、このデータをdbtのseedsを使って突っ込んでみる。

seedsのディレクトリmy_dbt/seeds/にファイルを作成する。

# my_dbtにいるとする。
touch seeds/PopTbl.csv

csvファイル(PopTbl.csv)には以下のようにデータを入れてみる。

pref_name,population
"徳島",100
"香川",200
"愛媛",150
"高知",200
"福岡",300
"佐賀",100
"長崎",200
"東京",400
"群馬",50

dbt seedを実行する。

dbt seed

10:57:50  Running with dbt=1.6.3
10:57:54  Registered adapter: snowflake=1.6.2
10:57:54  Found 2 models, 1 seed, 4 tests, 0 sources, 0 exposures, 0 metrics, 372 macros, 0 groups, 0 semantic models
10:57:54
10:57:58  Concurrency: 1 threads (target='dev')
10:57:58
10:57:58  1 of 1 START seed file MY_SCHEMA.PopTbl ..................................... [RUN]
10:58:02  1 of 1 OK loaded seed file MY_SCHEMA.PopTbl ................................. [INSERT 9 in 4.24s]
10:58:02
10:58:02  Finished running 1 seed in 0 hours 0 minutes and 7.92 seconds (7.92s).
10:58:02
10:58:02  Completed successfully
10:58:02
10:58:02  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

これで、Snowflakeにデータが作成される。
テーブル名はcsvファイルに従う模様。

SnowflakeのSQLワークシートで確認してみる。

select * from MY_DB.MY_SCHEMA.PopTbl;

ちゃんと入ってる。

本書P.5にあるSQLを試してみる。

use role sysadmin;
use database my_db;
use schema my_schema;
use warehouse my_wh;

-- P.5 既存のコード体系を新しい体系に変換して集計する
SELECT CASE pref_name
          WHEN '徳島' THEN '四国'
          WHEN '香川' THEN '四国'
          WHEN '愛媛' THEN '四国'
          WHEN '高知' THEN '四国'
          WHEN '福岡' THEN '九州'
          WHEN '佐賀' THEN '九州'
          WHEN '長崎' THEN '九州'
          ELSE 'その他' END AS district,
       SUM(population)
  FROM PopTbl
 GROUP BY CASE pref_name
             WHEN '徳島' THEN '四国'
             WHEN '香川' THEN '四国'
             WHEN '愛媛' THEN '四国'
             WHEN '高知' THEN '四国'
             WHEN '福岡' THEN '九州'
             WHEN '佐賀' THEN '九州'
             WHEN '長崎' THEN '九州'
             ELSE 'その他' END;

実行できた!

こんな感じで、Snowflake x dbtの設定がてら、Snowflake上で達人SQLを試す環境を用意することができた。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up