dbtなんもわからん部~コンテナでやりたい！~

# dbtなんもわからん
dbtなんも分かりません。わからないのでおててを動かしていきます。
(今週は時間がないので省エネ記事です！！)

# dbtとは？
gpt4oくんに聞いてみました！

> dbt（data build tool）は、データトランスフォーメーション（変換）をSQLで管理・自動化するためのオープンソースツールです。

だそうです！

## dbt_project.ymlを見てみよう！

まず初めに、projectファイルを見てみましょう。gpt4oにも聞きました。

```dbt_project.yml

# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'my_new_project' # プロジェクト名
version: '1.0.0' # プロジェクトバージョン

# This setting configures which "profile" dbt uses for this project.
profile: 'default' 
# dbtが使用する接続設定の名前
# profiles.ymlに定義されたプロファイル名と一致する必要あり

# These configurations specify where dbt should look for different types of files.
# The `source-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
source-paths: ["models"] # モデルを格納する場所
analysis-paths: ["analysis"] # 分析用のSQLスクリプト用
test-paths: ["tests"] # カスタムテスト保存先
data-paths: ["data"] # dbt seedで使うcsvなどのシートデータの保存先
macro-paths: ["macros"] # jinjaで定義されたカスタムマクロを格納
snapshot-paths: ["snapshots"] # dbt snapshotで使用するスナップショット定義

target-path: "target"  # directory which will store compiled SQL files
# dbtが生成するコンパイル済みSQLや成果物を格納するディレクトリ
clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_modules"
# dbt cleanコマンドで削除されるディレクトリを指定

# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In dbt, the default materialization for a model is a view. This means, when you run 
# dbt run or dbt build, all of your models will be built as a view in your data platform. 
# The configuration below will override this setting for models in the example folder to 
# instead be materialized as tables. Any models you add to the root of the models folder will 
# continue to be built as views. These settings can be overridden in the individual model files
# using the `{{ config(...) }}` macro.

models:
  my_new_project:
    # Applies to all files under models/example/
    example: # models/examlpe/配下のすべてのモデルに設定
      +materialized: table
      # 作成される形式をtableに指定

```

という感じのようです。ほえー

## インクリメンタルモデルってなーに？

gptが追加で教えてくれるとのことなので聞いてみました。

> # インクリメンタルモデルとは？
> materialized='incremental' に設定されたモデルは、以下の2つのパターンで動作します：
>
> 1\. 初回実行（テーブルが存在しない場合）：
> → テーブルを新規作成して、全件データを投入。
> 
> 2\. 2回目以降の実行（テーブルがすでに存在する場合）：
>→ 指定条件にマッチする新しい行のみを追加/更新。
>
>これにより、ETLの効率が劇的に改善され、無駄な再処理を回避できます。

とのことです。つまり、増分に対処することができるそうです。
実際にサンプルコードを以下のサイトから持ってきました。

https://dev.classmethod.jp/articles/dbt-materialization-incremental/

```sql:incremental_model sample

{{
    config(
        materialized='incremental'
    )
}}
-- incrementalの指定

SELECT
_fivetran_synced
,_modified
,change
,price
,sector
,ticker_symbol

FROM `tamai-rei.kinesis.dev_stream_data`
-- DB.schema.table

{% if is_incremental() %}
-- 増分がある場合にのみwhere句が起動

  where _fivetran_synced > (select max(_fivetran_synced) from {{ this }})
-- 現在のデータモデルのカラムの最新の日付をとって、それ以降の日付のレコードだけを増分更新する

{% endif %}
```

`from {{ this }}`について、gpt4oに聞いてみました！

> dbtのインクリメンタルモデルでよく使われる {{ this }} は、「現在のモデルが作成されるテーブル（またはビュー）の完全な参照名」を返す特殊なJinja変数です。

だそうです！差分をとるときに使う、という気持ちでよさそうな感じ！

# まとめ
というわけで、ひとまずdbtはデータの変換用のツールであり、便利機能も備えていることが分かりました。
しかしながら、肝心なところは何もわかってないので、今回はdbtを実際に触って操作する体験をしてみようと思います。というわけでハンズオン！

# おててを使って遊ぶ(格好よくいうとハンズオン)

実際におててを動かして学んでいきます。
今回の目的は、snowflakeにwindowsコンテナ環境から接続することです。
stepとしては
1\. 秘密鍵の生成
2\. snowflakeへの秘密鍵の登録
3\. コンテナ環境の作成
4\. dbtを用いたsnowflakeへの接続テスト
の四つになります。

(直近の研修で同期が作ってくれたものをコピペしています。マジで感謝)

それとは別に、コンテナで使えるサンプルとしては以下のものがありましたので、もしうまくいかなかったらこちらを参照してもいいかもしれません。

https://github.com/nakashimn/dbt-workspace

## step1 秘密鍵の生成

秘密鍵の生成および登録は、こちらのドキュメントに従って実行しました。

https://docs.snowflake.com/ja/user-guide/key-pair-auth

しかしながら、windowsにはデフォルトではOpenSSLが入っていません。ネイティブなwindows環境に入れるのは何か嫌なので、コンテナにopensslを入れることで対処しました。

```dockerfile
from python:3.11-slim

RUN pip install -U pip

# 次のステップで使うdockerfileをベースにしているので、余計なものがいろいろと入っている
COPY requirements.txt requirements.txt
RUN pip install -r requirements.txt
RUN apt-get -y update && apt-get -y install git
RUN apt update

RUN apt install -y openssl # opensslのinstall
```

このファイルを置いた階層で、次のコマンドを実行し、opensslを使った秘密鍵生成を行いました。
```
# cd dockerfileを置いた階層への移動
docker image build .
docker image ls # ここでビルドしたコンテナidをコピー
docker container run -it --mount type=bind,source="$(pwd)"/.ssh,target=/root/.ssh <image id> bash
# .sshフォルダーへのバインドマウントを設定
root@user_id:/# openssl version
> OpenSSL 3.0.16 11 Feb 2025 (Library: OpenSSL 3.0.16 11 Feb 2025)
# opensslのinstallを確認

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out /root/.ssh/rsa_key.p8
# 秘密鍵の生成
# ここで入力した暗号化キーを記憶

openssl rsa -in /root/.ssh/rsa_key.p8 -pubout -out /root/.ssh/rsa_key.pub
# 公開鍵の生成
# 先ほど入力した暗号化キーを入力

```

## step2 snowflakeへの秘密鍵の登録

次に、snowflakeに対して公開鍵の登録を行いました。
snowflake上でワークシートを開き、画面右上からロールをsecurityadminやaccountadminに変更し、その後以下のコマンドを実行しました。

```sql
-- 公開鍵の登録
ALTER USER "アカウントのユーザー名" SET RSA_PUBLIC_KEY='作成したpublicキーの中身';

-- 登録する公開鍵が2つ目である場合はこちらで登録
ALTER USER "アカウントのユーザー名" SET RSA_PUBLIC_KEY_2='作成したpublicキーの中身';

-- ユーザー情報の確認(公開鍵登録の有無も確認可能)
DESC USER "アカウントのユーザー名";
```

これによって、自身のsnowflakeアカウントに公開鍵が登録されました。

## step3 コンテナ環境の作成

以下のようなファイル構成のコンテナ環境を整備しました。

```plaintext
C:.
│  .dockerignore
│  .env
│  .gitignore
│  docker-compose.yml
│  dockerfile
│  requirements.txt
│  
├─.ssh # 公開鍵・秘密鍵系
│              
└─dbt # dbt作業用フォルダ
   profiles.yml # 接続用の情報を置いておくファイル
```

参考 profiles.ymlについて

https://docs.getdbt.com/docs/core/connect-data-platform/connection-profiles

ファイルの中身は以下の通りです。

<details><summary>Dockerfile</summary>

```dockerfile:dockerfile
from python:3.11-slim

RUN pip install -U pip

COPY requirements.txt requirements.txt
RUN pip install -r requirements.txt
RUN apt-get -y update && apt-get -y install git
```

</details>

<details><summary>docker-compose</summary>

```yml:docker-compose.yml
# version: "3.8"

services:
  dbt:
    build:
      context: .
      dockerfile: Dockerfile
    image: dbt
    container_name: dbt
    volumes: # バインドマウントの指定
      - ./dbt:/root/work/dbt
      - ./.ssh:/root/work/.ssh
    entrypoint: /bin/bash
    env_file: # 環境変数の読み出し
      - .env
```

</details>

<details><summary>requirements</summary>

```text:requirements.txt
dbt-snowflake==1.9.4 # dbt-snowflake接続用
dbt-core==1.9.4
shandy-sqlfmt[jinjafmt]==0.21.2
```

</details>

<details><summary>.env</summary>

```.env

export SNOWFLAKE_ACCOUNT=アカウント名
# snowflakeコンソール画面のアカウント/サーバーURLの.snowflakecomputing.comより前の部分
export SNOWFLAKE_ROLE=SYSADMIN
export SNOWFLAKE_WAREHOUSE_NAME=使うウェアハウス
export SNOWFLAKE_DATABASE_NAME=使うDB
export SNOWFLAKE_SCHEMA_NAME=使うスキーマ
export SNOWFLAKE_USER_NAME=使うアカウントのユーザー名
export SNOWFLAKE_PRIVATE_KEY_PATH=/root/work/.ssh/rsa_key.p8
export SNOWFLAKE_PRIVATE_KEY_PASSPHRASE="秘密鍵生成時に入力したパスフレーズ"

# 後で説明するprofiles.ymlのディレクトリ
export DBT_PROFILES_DIR=/root/work/dbt
```

</details>

<details><summary>profiles.yml</summary>


```
# example profiles.yml file
dbt_test:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      # この書き方によって、環境変数を使用できる
      role: "{{ env_var('SNOWFLAKE_ROLE') }}"
      database: "{{ env_var('SNOWFLAKE_DATABASE_NAME') }}"
      warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE_NAME') }}"
      schema: "{{ env_var('SNOWFLAKE_SCHEMA_NAME') }}"
      threads: 1
      connect_timeout: 60
      user: "{{ env_var('SNOWFLAKE_USER_NAME') }}"
      private_key_path: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PATH') }}"
      private_key_passphrase: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PASSPHRASE') }}"
```

</details>

## step4 dbtを用いたsnowflakeへの接続テスト

dbt作業用のフォルダに移動し、以下のコマンドを入力しました。

```
dbt init
# ここで、project名を入力
# すると、必要なファイル群が大量に作られる

dbt debug
# dbt_project.ymlがないというエラー以外のエラーは出ない
# ここで接続エラーが出ている場合はアカウント情報などを確認
```

ここで、`Connection test: [OK connection ok]`というログが出たので、接続自体は完了しました。
基本的に、dbt debugは、dbt_project.ymlと同じファイル階層で実施するものっぽい。
今回はサンプルのsqlコードがすでに作成されているので、それを走らせてみます。

```
dbt test
# ファイル検証
dbt run
# 実行

# 今回、profiles.ymlでschemaをpublicに設定しているので、public schemaにファイルが生成された。
```

実行されたコードは以下の通り
(長いので折り畳み)

<details><summary>初期サンプルコード</summary>

```sql:my_first_dbt_model.sql

/*
    Welcome to your first dbt model!
    Did you know that you can also configure models directly within SQL files?
    This will override configurations stated in dbt_project.yml

    Try changing "table" to "view" below
*/

{{ config(materialized='table') }}

with source_data as (

    select 1 as id
    union all
    select null as id

)

select *
from source_data

/*
    Uncomment the line below to remove records with null `id` values
*/

-- where id is not null

```

```sql:my_second_dbt_model.sql

-- Use the `ref` function to select from other models

select *
from {{ ref('my_first_dbt_model') }}
# dbt上で作成したテーブルについてはこのように参照できる。
# こうすることで、dbtにテーブル間の依存関係を認識させることが出来る。
where id = 1

```

</details>

以上で終了です

# まとめ
dbtのsnowflakeへの接続をテストしました。
コンテナ上での接続なので、再現性は高いかと思います。多分！
正直dbtの便利さを書けてないので、そこはいろいろとフォローアップしないといけないなあという感じです。(dbt docs generateとdbt docs serveコマンドでドキュメントを自動生成できます。)
そして、ここにさらにAirflowでタスク実行管理させるとか、リソース作成をterraformで実施するなどすれば、データ基盤作成になるので、次はAirflowあたりもやっていきたいなと思います。

(来週はちゃんと時間とります)