Zenn
😊

dbt CLIでSnowflake その1 - インストールと接続

2022/03/25に公開
Snowflake
dbt
tech

目的

  • dbt CLIを使って、Snowflake上のデータを扱えるようにする
  • 開発はMacOS
  • devはローカル開発、SSO認証
  • stgはCI/CD想定、Keypair認証

dbt CLIのインストール

私は、MacBookProを使っているので、Homebrewをつかったインストールを行います。
https://docs.getdbt.com/dbt-cli/install/homebrew
↑をメモしながら訳したもの（内容はDeepLとかで翻訳したまんま・・・ですが）
https://zenn.dev/gak_t12/articles/8b4f5db79a4453

$ brew update
$ brew tap dbt-labs/dbt
$ brew install dbt-snowflake

としました。dbtのインストールは結構時間がかかり、私の場合は26分32秒かかりました。

dbt project のセットアップ

レポジトリの用意

将来的に、GitActionsでCI/CDを行うようにしたいので、Gitのレポジトリを準備します。

https://zenn.dev/gak_t12/articles/9e5ffb88afbf44

的に実稼働するつもりです。
https://github.com/randypitcherii/cloud_cost_monitoring
を参考に作っていきます。

まず、github.com上にレポジトリを作成
レポジトリ名は(クライアント名)-cdp-dbtとする

ローカルPCのターミナルにて

cd $GIT_HOME
git clone git@github.com:$ORGANIZATION/$CLIENT_NAME-cdp-dbt.git
echo "# $CLIENT_NAME-cdp-dbt" >> README.md
git init
git add README.md
git commit -m "first commit"
git branch -M main # デフォルトのブランチ名を mainに変更する
git remote add origin git@github.com:$ORGANIZATION/$CLIENT_NAME-cdp-dbt.git
git push -u origin main

dbtプロジェクトの初期化を行う

$ cd $GIT_HOME/$CLIENT_NAME-cdp-dbt
$ dbt --version
installed version: 1.0.4
   latest version: 1.0.4

Up to date!

Plugins:
  - snowflake: 1.0.0 - Up to date!

次にdbtプロジェクトの初期化

$ dbt init hoge_cdp                                                                                               [main]
14:46:26  Running with dbt=1.0.4
Which database would you like to use?
[1] snowflake

(Don't see the one you want? https://docs.getdbt.com/docs/available-adapters)

Enter a number: 1

account (https://<this_value>.snowflakecomputing.com): ******.ap-northeast-1.aws

クラシックUIのURLならば
https://******.ap-northeast-1.aws.snowflakecomputing.com/
の「*****.ap-northeast-1.aws」　の部分

Snowsightの場合は
https://app.snowflake.com/ap-northeast-1.aws/***** になり、整形して「******.ap-northeast-1.aws」の文字列

user (dev username): ******
[1] password
[2] keypair
[3] sso
Desired authentication type option (enter a number): 3
authenticator ('externalbrowser' or a valid Okta URL) [externalbrowser]:

認証方法は3種類。
※私の場合はローカルからSnowflakeへのアクセスはSSOなので、　[3] ssoを選択

role (dev role): SYSADMIN
warehouse (warehouse name): COMPUTE_WH
database (default database that dbt will build objects in): DEV_DB_DBT
schema (default schema that dbt will build objects in): CDP_LAKE_CORE_DB
threads (1 or more) [1]:

ここまで適宜、自分の環境に合わあせて入力。

14:54:48  Profile <CLIENT_NAME>_cdp written to /Users/tashiro.gaku/.dbt/profiles.yml using target's profile_template.yml and your supplied values. Run 'dbt debug' to validate the connection.
14:54:48  
Your new dbt project "hoge_cdp" was created!

For more information on how to configure the profiles.yml file,
please consult the dbt documentation here:

  https://docs.getdbt.com/docs/configure-your-profile

One more thing:

Need help? Don't hesitate to reach out to us via GitHub issues or on Slack:

  https://community.getdbt.com/

Happy modeling!

これでカレントディレクトリに hoge_cdpというdbtプロジェクトのディレクトリが作成される
profile.ymlは、ユーザのホームディレクトリに作られる。（dbt init hoge_cdpの実行結果に記載されている）

ci_profiles/を作成し、~/.dbt/profiles.ymlをコピーしてくる。

.
├── README.md
├── analyses
├── ci_profiles
│   └── profiles.yml
├── dbt_project.yml
├── macros
├── models
│   └── example
│       ├── my_first_dbt_model.sql
│       ├── my_second_dbt_model.sql
│       └── schema.yml
├── seeds
├── snapshots
└── tests

となる。
ci_profiles/profiles.ymlの中身は

ci_profiles/profiles.yml
hoge_cdp:
  outputs:
    dev:
      account: ******.ap-northeast-1.aws
      authenticator: externalbrowser
      database: DEV_DB_DBT
      role: SYSADMIN
      schema: CDP_LAKE_CORE_DB
      threads: 1
      type: snowflake
      user: dbt_user
      warehouse: COMPUTE_WH
  target: dev

というファイルになっている。

Snowflakeへの接続確認（dev, SSO認証）

$ dbt debug

を実行して、接続確認

15:50:54  Running with dbt=1.0.4
dbt version: 1.0.4
python version: 3.9.10
python path: /usr/local/Cellar/dbt-snowflake/1.0.0_3/libexec/bin/python3.9
os info: macOS-10.15.7-x86_64-i386-64bit
Using profiles.yml file at /Users/a11052/.dbt/profiles.yml
Using dbt_project.yml file at /Users/a11052/git/aoc-daisetsu-cdp-dbt/daisetu_cdp/dbt_project.yml

Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]

Required dependencies:
 - git [OK found]

Connection:
  account: ******.ap-northeast-1.aws
  user: tashiro.gaku
  database: DEV_DB_DBT
  schema: CDP_LAKE_CORE_DB
  warehouse: COMPUTE_WH
  role: SYSADMIN
  client_session_keep_alive: False
Initiating login request with your identity provider. A browser window should have opened for you to complete the login. If you can't see it, check existing browser windows, or your OS settings. Press CTRL+C to abort and try again...

ブラウザによるSSO認証が入って、ログインモード。

  Connection test: [OK connection ok]

All checks passed!

認証が通って接続OKでした！！

一旦ここで、gitにコミットしてPush

Snowflakeへの接続確認 (stg, KeyPair認証）

profiles.ymlのtargetにstgを追加して、こちらはKeyPair認証で認証する設定にしたいと思います。（※ prod環境もKeyPair認証にするつもり）

（仕掛中）
https://docs.getdbt.com/reference/warehouse-profiles/snowflake-profile#key-pair-authentication

https://dev.classmethod.jp/articles/snowflake-key-pair-auth/

Snowflakeに　ユーザ「DBT」を作成

※私個人の場合では、社内フェデレーション機能の同期のため、除外ユーザ設定をし、PRだす。

Snowflakeにて下記のようにしてユーザを作成する。

create user DBT;
grant role SYSADMIN to user DBT;

KeyPair認証のセッティング

※パスフレーズは、Bitwardenを使って、チームに共有することとする。
あまりやりたくはないのですが、秘密鍵のファイルをレポジトリに配備
パスフレーズをSecretなどにいれるか、環境変数として入れる

パスフレーズを生成する by Bitwarden

パスフレーズを作成するには色々ありますが、弊社ではBitwardenを使っているので、その機能を使って生成しました。

  • チェックボックスを「Passphrase」にする
  • 4ワードにする（デフォルトは 3）　※3のままでもいいかもですが・・・
  • 大文字を含める (Capitalize)
  • 数字を含める　（Include　Number）

その後、Bitwardenに格納しておきます。

秘密鍵を生成する

秘密鍵をレポジトリ内に置くのは、結構抵抗があるのですが・・・ private repositoryなので・・・

パスフレーズは、環境変数経由でｄｂｔに設定することとします。

$ mkdir $PROJECT_DIR/ci_profile/keys
$ cd $PROJECT_DIR/ci_profile/keys
$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8
Generating RSA private key, 2048 bit long modulus
.....................+++
.................................................................+++
e is 65537 (0x10001)
Enter Encryption Password:　#先程生成したパスフレーズ
Verifying - Enter Encryption Password: #先程生成したパスフレーズ

$　cat rsa_key.p8
-----BEGIN ENCRYPTED PRIVATE KEY-----
MIIE6TAbBgkqhkiG9w0BBQMwDgQIDdSS6pZBatMCAggABIIEyEN6n8G8+nnrcvkP
・・・・・・
tChz30fgiAemd/3yCg==
-----END ENCRYPTED PRIVATE KEY-----

公開鍵を生成する

秘密鍵から公開鍵を生成します。使うパスフレーズは、先程生成したパスフレーズ

$ cd $PROJECT_DIR/ci_profile/keys
$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
Enter pass phrase for rsa_key.p8:　#先程生成したパスフレーズ
writing RSA key

$ cat rsa_key.pub
-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvk5AFTDTbeLx+SVOaRYC
.........
YwIDAQAB
-----END PUBLIC KEY-----

Snowflakeのユーザ「DBT」に公開鍵を割り当てる。

% cat rsa_key.pub | \                                                                   [main]
tr -d "\n" | \
sed "s/-----BEGIN PUBLIC KEY-----//g" | \
sed "s/-----END PUBLIC KEY-----//g"
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvk5AFTDTbeLx+SVOaRYCqL7wT/27i0nby
.......wIDAQAB

次に、SnowflakeのWebコンソールで、ただし、ロールは、SECURITYADMINで行う。

USE ROLE SECURITYADMIN;
ALTER USER foo_bar SET rsa_public_key='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvk5A...';

ci_profiles/profiles.ymlに target=stg を作成する

  • USERはdbt
  • KeyPair認証を行う
    で設定を追加する。

https://docs.getdbt.com/reference/warehouse-profiles/snowflake-profile#key-pair-authentication

target=devがSSO認証だったので、そこに設定を追加する

ci_profiles/profiles.yml
hoge_cdp:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: ******.ap-northeast-1.aws
      user: tashiro.gaku
      role: SYSADMIN

      # SSO config
      authenticator: externalbrowser

      database: DEV_DB_DBT
      warehouse: COMPUTE_WH
      schema: CDP_LAKE_CORE_DB
      threads: 4
      query_tag: dbt_dev

    stg:
      type: snowflake
      account: ******.ap-northeast-1.aws
      user: DBT
      role: SYSADMIN

      # Keypair config
      private_key_path: keys/rsa_key.p8
      private_key_passphrase: "{{ env_var('DBT_PROFILE_PASSPHRASE') }}"

      database: STG_DB_DBT
      warehouse: COMPUTE_WH
      schema: CDP_LAKE_CORE_DB
      threads: 4
      query_tag: dbt_stg

profiles.ymlを　ci_profiles/にコピーしたので動作確認を行う

dev:sso認証

$ cd (dbtプロジェクトのトップディレクトリ）
$ dbt debug --profiles-dir ./ci_profiles --target dev
08:48:46  Running with dbt=1.0.4
dbt version: 1.0.4
python version: 3.9.10
Using profiles.yml file at $GIT_REPOGITORY/hoge_cdp/ci_profiles/profiles.yml
Using dbt_project.yml file at $GIT_REPOGITORY/hoge_cdp/dbt_project.yml

Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]

Required dependencies:
 - git [OK found]

Connection:
  account: ******.ap-northeast-1.aws
  user: tashiro_gaku
  database: DEV_DB_DBT
  schema: CDP_LAKE_CORE_DB
  warehouse: COMPUTE_WH
  role: SYSADMIN
  client_session_keep_alive: False
Initiating login request with your identity provider. A browser window should have opened for you to complete the login. If you can't see it, check existing browser windows, or your OS settings. Press CTRL+C to abort and try again...
  Connection test: [OK connection ok]

All checks passed!

OK

target=stg Keypair認証

$ export DBT_PROFILE_PASSPHRASE=（生成したパスフレーズを入力）
$ dbt debug --profiles-dir ./ci_profiles --target stg
08:57:36  Running with dbt=1.0.4
dbt version: 1.0.4
python version: 3.9.10
python path: .....
Using profiles.yml file at $GIT_REPO/hoge_cdp/ci_profiles/profiles.yml
Using dbt_project.yml file at $GIT_REPO/hoge_cdp/dbt_project.yml

Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]

Required dependencies:
 - git [OK found]

Connection:
  account: ******.ap-northeast-1.aws
  user: DBT
  database: STG_DB_DBT
  schema: CDP_LAKE_CORE_DB
  warehouse: COMPUTE_WH
  role: SYSADMIN
  client_session_keep_alive: False
  Connection test: [OK connection ok]

All checks passed!

終わりに

dev (ローカル開発、SSO認証）、 stg　（CI/CD、GitAction想定、Keypair認証）　で　ｄｂｔからSnowflakeにつなぐところまでできました。

次回は、実際にmodelを記載して、手動で構築したデータ基盤をｄｂｔに移行していきたいと思います。

Discussion