どんな人むけの記事か

dbtを導入前で
- 導入したいけど周囲のアナリストに価値を感じてもらえなさそうで断念しているデータエンジニア
dbt coreを導入している
- けどアナリストが使ってくれないなと思っているデータエンジニア
- けどdbtのための作業工数増えてめんどくさいなと思ってるアナリスト
dbt cloudを導入している
- けどコストカットしたいと思っている人

はじめに

初めてテックブログを書かせていただきます株式会社onerootsの山口と申します。現在はデータアナリストを中心として幅広めに仕事しております。

早速ですがdbtってよくできてますよね。公式が下記でアナリティクスエンジニアという定義をしているようにアナリストができることを拡張してくれて欲しいところに手が届いてるなという印象です。

具体的に要素を挙げると

テーブルの上流下流関係を自動で考慮した順番でテーブル更新してくれる
テーブルの関係性であるリネージュを自動で可視化することで障害箇所やテーブルの概要を簡単に把握できる
テーブル更新後にテーブルのテストもsqlベースで実装できる(非nullなどの簡単なものは実装いらず)
descriptionもテーブルと一緒のgitで管理するため、記載漏れや更新漏れを防げる
etc…

※下記が公式に書いてあるdbtの良さ

これらをデータエンジニアを介在させずにsql(正確にはjinja-sql)を書くだけで実装できるようにしています。

それはそれとして、無料で使えるdbt coreを導入してみてもいかんせん使ってくれる人が増えないんですよね。

その心は？というと、機能としてよくできていることと、運用におけるユーザビリティが別問題だからって話でしかないと思うのですが、クエリをDWHのコンソールなりでそれぞれ作った後で、dbt用の環境を立ち上げて、クエリをjinjaに書き換えて移植してcompileしてbuildしてっていうのが単純にめんどくさいからだと想像してます。

dbt core導入時の一番の課題感はここかなと思うのですが、エンジニアがやっていたことをアナリストができるってアナリストからしたら仕事増えるだけですし、dbtの色々も覚えないといけないしで進んでやりたくはないですよね。

この問題のシンプルな対応としては、そういうものだからとルール化するっていうのが一つ(問題を権力の力でなかったことにする)、もう一つはお金をかけてクラウドベースで諸々のUIから提供しているdbt cloudを導入するというやり方かなと思います。

権力もお金もない私としてはユーザビリティを最強にしてアナリストに「これは便利」「使はない方が損」という形に持っていくしかないわけで、1ヶ月くらいかけて作業したらいい感じのdbt用の環境ができたので今回記事という形で共有できればなと思った次第です。

なお、今回はdbt coreユーザー向けの環境構築をまとめようと思うのですが、現在TreasureData + dbtという結構ニッチなところを試行錯誤しながら作っているので、そちらに関しても今後記事にできればなと思っております。

設計思想

そもそもユーザビリティを最強にするアイデアはコードエディターである「Visiual Studio Code」(以後、vscode)の拡張機能(エクステンション)である「Power User for dbt Core™」(以後、dbt power user)を発見したことから始まっています。

※vscodeや拡張機能に関してはたくさん記事があると思うので、そちらに説明は譲ります。

下記に添付しているURLを見ていただけばわかるかと思うのですが、本来dbt compileを実行して生成されたファイルを開いて確認しないといけないcompile結果をワンポチで見れたり

dbt docs generateからのserveをしなくてもリネージュが見れたり、dbt coreの環境からクエリの実行結果の可視化・グラフ化ができたりと

端的にいうとdbt cloudっぽい(私も公式が提供してるオンラインコースレベルでしか知らないですが。。。)ことができるわけです。

特にdbt buildもワンポチでできるのでdbt build -s +テーブル名とか覚えなくてもいいところがポイント高い。

※ちなみにdbt cloud導入している方向けにdbt power userとのユーザビリティを比較してる記事が下記になるのでご参考までに(ここで書かれているdbt power userの挙動は若干古めです)。

※dbt power userには記載した以外にも色々機能がありますが高機能なものがお金がかかるのでご注意を。無料範囲でも十分パワフルなので当方は有料機能を使っておりません。

dbt power userを前提にすると(DWHのコンソールなどを使わず)環境内でsqlを書いてあとはdbt系に関してはポチポチするだけでよくなるわけで、そもvscodeならgit系も下記拡張機能でプルリクまでポチポチでできるし、なんでもポチポチでよくなる。これが最強なわけです。

ここで最初かつ一番の壁として立ちはだかってくるのが環境を構築するところですよね。これも可能な限りターミナルを叩かないポチポチな方法で実装してます(私見だが今時っ子はアプリネイティブでターミナルなんて叩けないと想定してるし、アナリストはビジネス理解をした上でsql叩ければ十分だと思ってる)。

つまりポチポチでできる拡張機能を使える環境をポチポチで作れることが勝利条件となっています。

環境構築方法

どうやって実現するかは次に回すとして、先にどんな環境構築になるかアナリストが行うことを説明すると、

※前提：dbt core用のファイル群がgithub内にrepository管理している

githubアカウントを作ってdbtのリポジトリに招待してもらう

vscodeをインストール

docker desktopをインストール

ローカルにgitをインストールする

gitのconfigを入力する
- 　下記に従って2行だけ※ここだけターミナルを叩かないといけない

vscodeにDev Containersの拡張機能をインストール

dbtのリポジトリをクローンして開く
.devcontainer/devcontainer.envにapi_keyなどのgitに載せれない系の変数を入れる
- ex. TREASURE_DATA_API_KEY=hogehoge
左下から「コンテナで再度開く」を押す

これで環境構築は完了になります。おそらく最強です。これを見たらわかる人はvscodeのdevcontainerを使ってるんだなと思っていただけると思います。

vscodeのdevcontainerの詳細についてはこちらをご覧ください。

ざっくり私の理解をいうとローカルの色々を継承したコンテナをvscode上で開けるって感じですかね。

コンテナがわからなくてもポチポチでコンテナを立てられる点がポイント高くて採用しています。devcontainerではローカルのgit config情報は継承されるのでそこの部分だけローカルでコマンドで叩く必要が発生してしまいます(とても悔しい)。

また、githubに載せるべきでないkeyの情報も手運用で入力する必要があり、今回は.devcontainer/devcontainer.envをgit管理外にしてそこに入力する形を採用しています(2時間くらい検討した中だとこれが一番ユーザビリティが良かった)。

ちなみに、実運用に関していうと

branchを切る
クエリを叩きながらのsqlの開発(compileやリネージュを確認しながら)
buildする
description書く
sqlのformat
pull request発行

をコマンドを一才叩くことなくできます(すべて拡張機能様様なわけですが)。

拡張機能の動作が見たいだけであれば以降の環境構築は必要ないので、dbt power userをvscodeにインストールして下記の手順に従って初期設定して使ってみるでも使い勝手の良さが分かるかと思います(dbt coreに関する記事はそこらじゅうにありますし、dbt power userに関する記事も少ないですがあるのでこれ以上の詳細はそちらに譲ります)。

完成系と各ファイルの説明

ここから実際に作成した環境の関連部分に関して実際のコードと説明を記載していきます。

フォルダ構成

gitリポジトリのフォルダ(repository_folder_name)

|-.devcontainer

|-Dockerfile

|-devcontainer.json

|-postCreateCommand.sh

|-.gitignore

|-.gitattributes

|-dbt関連のフォルダ(dbt_folder)

.devcontainer/Dockerfile

FROM python:3.9-bullseye
USER root

RUN apt-get update && \
    apt-get -y install --reinstall ca-certificates 

# Install git
RUN apt-get -y install git

# Install td
RUN apt-get -y install curl && \
    curl -o td-agent-apt-source.deb https://packages.treasuredata.com/4/debian/bullseye/pool/contrib/f/fluentd-apt-source/fluentd-apt-source_2020.8.25-1_all.deb && \
    apt install -y ./td-agent-apt-source.deb && \
    apt update && \
    apt install -y td-agent

# Install Basic Packages
RUN pip install --upgrade pip
RUN pip install ipykernel jupyter
RUN pip install poetry pandas numpy pytd

Dockerfileに関しては正直今回の環境構築に関係するところはほとんどないです。

pythonコンテナを立てたかっただけです(3.9なのはpoetryでdbt環境を構成した当時の状態にlockされているからで理由はないです)。

gitをインストールしたりTreasure Data用にtdコマンドをインストールしたりpythonのpackageをinstallしたり(pythonの分析もしたいなという欲)してますが、後述のpostCreateCommand.sh側に寄せても問題ないはずです。

唯一dbt coreをpoetryから作った仮想環境上に展開してるので、poetryはこれからも時々出てきますが、コンテナに直で展開する場合や別の仮想環境で展開する場合はよしなにしていただけますと。

.devcontainer/devcontainer.json

{
	"name": "dbt container",
	"build": {
		// Sets the run context to one level up instead of the .devcontainer folder.
		"context": ".",
		// Update the 'dockerFile' property if you aren't using the standard 'Dockerfile' filename.
		"dockerfile": "Dockerfile"
    },
	"customizations": {
        "vscode": {
			"settings": {
				"files.eol": "\n", // eol=lf
				"python.interpreter.infoVisibility": "always",
				"DBT_PROFILES_DIR": "/workspaces/repository_folder_name/dbt_folder_name",
				"dbt.dbtPythonPathOverride": "/root/.cache/pypoetry/virtualenvs/hogehoge/bin/python",
				"dbt.sqlFmtPath": "/root/.cache/pypoetry/virtualenvs/hogehoge/bin/sqlfmt",
				"files.associations": {
					"*.yml": "jinja-yaml",
					"*.sql": "jinja-sql"
				}   
            },
            "extensions": [
                "ms-python.python",
				"ms-toolsai.jupyter",
				"GitHub.vscode-pull-request-github",
                "innoverio.vscode-dbt-power-user"
            ]
        }
    },
	"runArgs": ["--env-file",".devcontainer/devcontainer.env"],
	"postCreateCommand": "/bin/bash .devcontainer/postCreateCommand.sh"
}

これがdevcontainerの立ち上げに関する情報がまとまっていて、ここに環境構築を自動で行うためのエッセンスが多めに詰まってます。

まず

"name": "dbt container",
"build": {
	// Sets the run context to one level up instead of the .devcontainer folder.
	"context": ".",
	// Update the 'dockerFile' property if you aren't using the standard 'Dockerfile' filename.
	"dockerfile": "Dockerfile"
  },

こちらは前述のDockerfile使いますって書いてるだけで特にポイントはないです。

"files.eol": "\n", // eol=lf

はvscode内の改行コードをLFに指定しています。

これがないとwindowsの人は基本的に改行コードがCRLFなので、今回作ってるコンテナのlinux系のLFと食い違いが発生します。

その設定をdevcontainer作成時に自動で指定することでユーザーが気にしなくてよくなります。

"python.interpreter.infoVisibility": "always",
"DBT_PROFILES_DIR": "/workspaces/repository_folder_name/dbt_folder_name",
"dbt.dbtPythonPathOverride": "/root/.cache/pypoetry/virtualenvs/hogehoge/bin/python",
"dbt.sqlFmtPath": "/root/.cache/pypoetry/virtualenvs/hogehoge/bin/sqlfmt",
"files.associations": {
	"*.yml": "jinja-yaml",
	"*.sql": "jinja-sql"
}

これらは主にdbt power user向けの設定をdevcontainer作成時に指定している箇所になります。

最初の

"python.interpreter.infoVisibility": "always"

はpythonのインタプリタをpythonを実行するたびに設定させています。

dbt power userの公式にしたがって指定しています。

私の環境は前述の通りdbt coreはpoetryの仮想環境上に作ってるので、ちょっとしたpythonの分析をする時は普通のpythonで実行するなどのように指定できるのであってもいいかなという感じなのですが、そうじゃない場合必要なのか？と思ったりするところではあります。

"DBT_PROFILES_DIR": "/workspaces/repository_folder_name/dbt_folder_name"

これはdbtのprofile.ymlがあるディレクトリをdbt power userがわかるように指定している場所になります。

"dbt.dbtPythonPathOverride": "/root/.cache/pypoetry/virtualenvs/hogehoge/bin/python"

これはdbtがインストールされているpythonのpathを指定していてpoetry含め仮想環境を使ってる場合は特にポイントになります(poetryのpython path見つけるのが大変でした)。

ちなみに指定するのは公式曰く非推奨なのですが、指定しないとpython path迷子者が続出するのでユーザビリティを優先しています。

記載内容はpoetry経由の場合なので、ご自身のdbtがインストールされているpythonのpathを指定してください。

"dbt.sqlFmtPath": "/root/.cache/pypoetry/virtualenvs/hogehoge/bin/sqlfmt"

dbtにはsqlfmtというsqlをフォーマットしてくれるものを用意してくれています。dbt power userではそれを使ってsqlファイル上で右クリックして「ドキュメントのフォーマット」を押すだけでsqlfmtに沿ってフォーマットすることが可能になっています。

このポチポチsql formatを使うために必要なのがsqlfmtのpath指定になります。

前提としてsqlのformatをしない場合は必要ないですし、formatをする場合は下記に従ってpipでsqlfmtをインストールしている必要があります。

"files.associations": {
	"*.yml": "jinja-yaml",
	"*.sql": "jinja-sql"
}

これらは公式の指定そのままです。

sqlファイルなどをjinja-sqlとして読み替えてくれます。refなどのjinja的な書き方を自動保管で入力できるようになったりファイルのアイコンが変わったりします。

上記が主にdbt power user関連の設定の指定になりますが、他の設定に関しても下記に記載のものを呼び出すと指定できたりするので参考までに。

"extensions": [
                "ms-python.python",
				"ms-toolsai.jupyter",
				"GitHub.vscode-pull-request-github",
                "innoverio.vscode-dbt-power-user"
            ]

これらはvscodeの拡張機能をインストールするための記述になります。

devcontainerの便利なところとしてローカルのvscodeの拡張機能とdevcontainer用のwindowのvscodeの拡張機能を別々で管理することができ、ここで記載しているのはdevcontainer用の拡張機能になり、devcontainerを立ち上げたタイミングで記載の拡張機能が自動でインストールされます(ローカルの拡張機能はDev Containersだけになっている)。

一番最後の"innoverio.vscode-dbt-power-user"が本丸のdbt power userの拡張機能になります。

それ以外のは下から2番目がpull requestの拡張機能(よく使う)、一番上と二つ目がpythonとjupyterでこれらはdbtという文脈では必要ないです(今回の記事の範囲ではないですが、当方は一部処理をpythonで実装してるのでそれを開発環境でワンポチ実行するためにpythonは使っていたりします)。

"runArgs": ["--env-file",".devcontainer/devcontainer.env"]

これは前述のapi keyなどのgithubに載せるべきでないセンシティブな環境変数を.devcontainer/devcontainer.envから取得する方法になります。公式で書いている下記のやり方をそのまま参考にしています(後述されますが.gitignore内に.devcontainer/devcontainer.envを指定することでgit管理外にしている)。

"postCreateCommand": "/bin/bash .devcontainer/postCreateCommand.sh"

これはdevcontainerが立ち上がった後に自動で実行するコマンドを指定しており、コマンドとしてはbashファイルを実行しています。このshファイルの中でdbtのインストールなどを行なっていたりしますが具体ファイル内容は後続に記載します。

.devcontainer/postCreateCommand.sh

set -eux

# dbtディレクトリに移動
cd dbt_folder_name

# 必要なライブラリのインストール
poetry install

# dbt utilsを使えるようにする
poetry run dbt deps

# 接続テスト
poetry run dbt debug --profile dbt_folder_name

特にこのやり方がbetterであるという理由はないですが、poetry環境を作るところをshファイルに寄せています(本来Dockerfile内の処理も大部分はこちらに寄せてもいいはず)。

poetryに記載がある諸々のライブラリをインストール(この中にdbtやsqlfmtが含まれている)後に、dbt系の初期設定を行なっています。

比較的記載の自由がきくところではあるので、ここでユーザーが本来実行しないといけない諸々を書くのがユーザービリティへのポイントだったりします。(注意：ここで設定した環境変数は永続化されないので環境変数設定はDockerFileか前述のやり方を使ってください)

.gitignore

logs/
target/
.DS_Store
dbt_packages/
.devcontainer/devcontainer.env
.vscode/settings.json

ここに記述したファイルはgit管理外になるわけですが、前述の通り.devcontainer/devcontainer.envを指定しています。

またdbtの実行のたびに変わるものなどgit管理すべきでないものも詰め込んでいます。

.gitattributes

* text eol=lf

これはgitからcheckoutしたときのファイル形式をLFに統一するというものです。

前述のvscodeのsettingとモチベーションは同じなのですが、前述のsettingではあくまでもこれから作るファイルがLFになるというだけで、checkoutしたときの動作は保証されていません。

windowsのgitの設定によってはcheckout時に自動で改行をCRLFに変更するということもあるのでそれをLFにしています。

一点注意なのですが、今の指定だと全ファイルをLFに変換しており、改行がCRLFである.pngなどが壊れます(実体験)。そういうファイルを管理する必要がある場合には下記を参考にpngについては変更しないなどの指定を追加してください。

まとめ

実際に色々な人に環境構築してもらいましたが、「すんなりいけたよ」との声をいただいています(すんなりいった場合って問題が起きてるわけでもないしどれだけ自動化させてるかもわからないからほめられないっていうね。。。嬉しいような悲しいような)。

説明も細かめに書いてるつもりなので、エッセンスだけでも誰かのためになったら嬉しいなと思っています。

次回はTreasureData + dbtに関してdbt要素多めで書いていこうかなと思っています。