Zenn
Open7

【dbt】notebook

Jupyter Notebook
dbt
YuichiYuichi

https://dk521123.hatenablog.com/entry/2024/07/20/034930

from dbt.cli.main import dbtRunner, dbtRunnerResult
import pandas as pd

# dbtRunnerを初期化
dbt = dbtRunner()

# CLIの引数をリストとして作成
cli_args = ["show", "--inline", "SELECT * FROM {{ ref('~~~') }}","--limit" ,500]

# コマンドを実行
res: dbtRunnerResult = dbt.invoke(cli_args)

# 結果をデータフレームに変換
df = pd.DataFrame([row.dict() for r in res.result for row in r.agate_table])

# データフレームを表示
display(df)

https://docs.getdbt.com/reference/commands/show

デフォルトでは、dbt showクエリ結果の最初の 5 行が表示されます。これは、表示する行数を表す--limit nフラグを渡すことでカスタマイズできます。n

YuichiYuichi

動いたやったね！

from IPython.core.magic import (Magics, magics_class, line_magic, cell_magic)
from dbt.cli.main import dbtRunner, dbtRunnerResult
import pandas as pd

@magics_class
class DbtMagics(Magics):
    def __init__(self, shell):
        super(DbtMagics, self).__init__(shell)
        self.dbt = dbtRunner()
        self.limit = 20
        self.quiet = True

    @line_magic
    def config(self, line):
        key, value = line.split('=')
        key = key.strip()
        value = value.strip()
        if key == 'dbt.limit':
            self.limit = int(value)
        elif key == 'dbt.quiet':
            self.quiet = value.lower() == 'true'

    @cell_magic
    def dbt_show(self, line, cell):
        cli_args = ["show", "--inline", cell, "--limit", str(self.limit)]
        if self.quiet:
            cli_args.append("--quiet")
        
        res: dbtRunnerResult = self.dbt.invoke(cli_args)
        df = pd.DataFrame([row.dict() for r in res.result for row in r.agate_table])
        
        if line.strip() == 'df':
            self.shell.user_ns['df'] = df
            print("Results stored in 'df' variable")
        else:
            display(df)

    @cell_magic
    def dbt_compile(self, line, cell):
        cli_args = ["compile", "--inline", cell]
        if self.quiet:
            cli_args.append("--quiet")
        
        res: dbtRunnerResult = self.dbt.invoke(cli_args)
        compiled_sql = res.result[0].node.compiled_code
        print(compiled_sql)

# マジックコマンドを登録
ip = get_ipython()
ip.register_magics(DbtMagics)

%config dbt.limit = 20
%config dbt.quiet = false

%%dbt_show
SELECT * FROM {{ ref('~~') }}

%%dbt_compile
SELECT * FROM {{ ref('~~') }}

%%dbt_show df
SELECT * FROM {{ ref('~~') }}

df.describe()
YuichiYuichi

スクリプトの外だし

import sys
sys.path.append('/workspaces/~~/ipython')
from dbt_magics import DbtMagics
# マジックコマンドを登録
ip = get_ipython()
ip.register_magics(DbtMagics)
/workspaces/~~/ipython/dbt_magics.py
from IPython.core.magic import (Magics, magics_class, line_magic, cell_magic)
from dbt.cli.main import dbtRunner, dbtRunnerResult
import pandas as pd

@magics_class
class DbtMagics(Magics):
    def __init__(self, shell):
        super(DbtMagics, self).__init__(shell)
        self.dbt = dbtRunner()
        self.limit = 20
        self.quiet = True

    @line_magic
    def config(self, line):
        key, value = line.split('=')
        key = key.strip()
        value = value.strip()
        if key == 'dbt.limit':
            self.limit = int(value)
        elif key == 'dbt.quiet':
            self.quiet = value.lower() == 'true'

    @cell_magic
    def dbt_show(self, line, cell):
        cli_args = ["show", "--inline", cell, "--limit", str(self.limit)]
        if self.quiet:
            cli_args.append("--quiet")
        
        res: dbtRunnerResult = self.dbt.invoke(cli_args)
        df = pd.DataFrame([row.dict() for r in res.result for row in r.agate_table])
        
        if line.strip() == 'df':
            self.shell.user_ns['df'] = df
            print("Results stored in 'df' variable")
        else:
            display(df)

    @cell_magic
    def dbt_compile(self, line, cell):
        cli_args = ["compile", "--inline", cell]
        if self.quiet:
            cli_args.append("--quiet")
        
        res: dbtRunnerResult = self.dbt.invoke(cli_args)
        compiled_sql = res.result[0].node.compiled_code
        print(compiled_sql)
YuichiYuichi

よくわからんが実行しても結果が帰ってこない事象発生
dbt compileしたら復旧した

追加設定項目

dbt show -h
dbt compile -h
の項目見ながら設定項目確認していこう
これも明示的にしてもいいかも
-q, --quiet / --no-quiet Suppress all non-error logging to stdout.
Does not affect {{ print() }} macro calls.