Snowflake x dbt サクッと初期設定

## サクッとまとめ

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

1. Snowflakeアカウントを作成する
    - [こちら](https://signup.snowflake.com/?_l=ja&utm_source=google&utm_medium=paidsearch&utm_campaign=ap-jp-jp-brand-core-phrase&utm_content=go-rsa-evg-ss-free-trial&utm_term=c-g-snowflake-p&_bt=610748255444&_bk=snowflake&_bm=p&_bn=g&_bg=142614212367&gclsrc=aw.ds&gad=1&gclid=CjwKCAjwg4SpBhAKEiwAdyLwvHqmRvhscZ_6yeXjT0xGOh7Lul7Lu1liSGN0VHreZBZBbmzmMX5SRxoCcDQQAvD_BwE)から作成する
    - SnowflakeのWebUIにログインする
    - 以下を控えておく
        - ユーザー名
        - パスワード
        - アカウント識別子(XXXXXXX.XX00000 のような値。dbtに設定する際は「.」を「-」に読み替えておく。)
    - SYSADMINユーザーでウェアハウスを作成しておく
        - 例 : MY_WH
        - サイズ等他の設定は任意
    - SYSADMINでデータベースを作成しておく
        - 例 : MY_DB
    - SYSADMINでスキーマを作成しておく
        - 例 : MY_SCHEMA
2. dbt (Snowflake) を自端末にインストールする
    - `pip install dbt-snowflake`
3. dbtの初期設定を行う
    - `dbt init my_dbt` (`my_dbt`は任意の名前)
    - 以降、表示されたプロンプトに従う

        ```bash
        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にオブジェクトを作成してみる
4. Snowflake(WebUI)でdbtの実行結果を確認する
    - ワークシート(SQL)を開く
    - 以下を実行する
        
        ```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用のキーペアを作成する

以下を実行する。

```bash
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に設定する

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

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

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

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

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

[ここ](https://docs.getdbt.com/docs/core/connect-data-platform/snowflake-setup)を見ると、`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` を編集する。

```bash
vi ~/.dbt/profiles.yml
```

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

```yml
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` で以下のエラーになってしまう。

```bash
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形式の鍵を作成してみる。

```bash
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` に書き込む。

```bash
vi ~/.dbt/profiles.yml
```

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

```yml
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。

```bash
dbt debug
```

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

```bash
dbt run
```

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

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

例えば、こちらの[達人SQL本](https://www.shoeisha.co.jp/book/detail/9784798157825)のSQLを元に勉強したいので、このデータをdbtのseedsを使って突っ込んでみる。

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

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

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

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

`dbt seed`を実行する。

```bash
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ワークシートで確認してみる。

```SQL
select * from MY_DB.MY_SCHEMA.PopTbl;
```

ちゃんと入ってる。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3475867/d1835dda-9ba1-69e3-21ee-624e9d1a014e.png)

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

```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;
```

実行できた!

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3475867/2a5fc84c-595a-fd48-43b6-fdb465505a13.png)

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