【dbt】dbt ~ dbt CLI ~

■ はじめに

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

で、 dbt の run とか show とか使ったが
dbt CLI (dbt コマンド)として改めてまとめておいた方がよさそうなので
徐々にではあるが、まとめていく。

目次

【0】dbt CLI
 1)環境設定
【1】run
【2】run-operation
【3】show
【4】init
【5】seed
【6】debug
【7】docs
【8】source

【0】dbt CLI

https://docs.getdbt.com/reference/dbt-commands#available-commands

Command Explanations
run モデル実行
run-operation マクロを呼び出す
show 指定したModelや任意のSELECT文の出力結果を表示
init 新規DBTプロジェクトの初期化処理
seed CSVをロードしDBに挿入
debug DBTコネクションとプロジェクトのデバッグ
docs ドキュメント生成
source sourceデータを扱うためのツールを提供

1)環境設定

* dbt core をインストールする必要がある
* 以下の関連記事を参照のこと

dbt ~ 環境設定編 ~
https://dk521123.hatenablog.com/entry/2023/12/16/152147

【1】run

* プロジェクト内のモデルを実行する

https://docs.getdbt.com/reference/commands/run
コマンド例

dbt run --target dev --project-dir users/

dbt run -h より

Usage: dbt run [OPTIONS]

  Compile SQL and execute against the current target database.

Options:
  --cache-selected-only / --no-cache-selected-only
                                  At start of run, populate relational cache
                                  only for schemas containing selected nodes,
                                  or for all schemas of interest.
  -d, --debug / --no-debug        Display debug logging during dbt execution.
                                  Useful for debugging and making bug reports.
  --defer / --no-defer            If set, resolve unselected nodes by
                                  deferring to the manifest within the --state
                                  directory.
  --defer-state DIRECTORY         Override the state directory for deferral
                                  only.
  --deprecated-favor-state TEXT   Internal flag for deprecating old env var.
  -x, --fail-fast / --no-fail-fast
                                  Stop execution on first failure.
  --favor-state / --no-favor-state
                                  If set, defer to the argument provided to
                                  the state flag for resolving unselected
                                  nodes, even if the node(s) exist as a
                                  database object in the current environment.
  --indirect-selection [eager|cautious|buildable|empty]
                                  Choose which tests to select that are
                                  adjacent to selected resources. Eager is
                                  most inclusive, cautious is most exclusive,
                                  and buildable is in between. Empty includes
                                  no tests at all.
  --log-cache-events / --no-log-cache-events
                                  Enable verbose logging for relational cache
                                  events to help when debugging.
  --log-format [text|debug|json|default]
                                  Specify the format of logging to the console
                                  and the log file. Use --log-format-file to
                                  configure the format for the log file
                                  differently than the console.
  --log-format-file [text|debug|json|default]
                                  Specify the format of logging to the log
                                  file by overriding the default value and the
                                  general --log-format setting.
  --log-level [debug|info|warn|error|none]
                                  Specify the minimum severity of events that
                                  are logged to the console and the log file.
                                  Use --log-level-file to configure the
                                  severity for the log file differently than
                                  the console.
  --log-level-file [debug|info|warn|error|none]
                                  Specify the minimum severity of events that
                                  are logged to the log file by overriding the
                                  default value and the general --log-level
                                  setting.
  --log-path PATH                 Configure the 'log-path'. Only applies this
                                  setting for the current run. Overrides the
                                  'DBT_LOG_PATH' if it is set.
  --partial-parse / --no-partial-parse
                                  Allow for partial parsing by looking for and
                                  writing to a pickle file in the target
                                  directory. This overrides the user
                                  configuration file.
  --populate-cache / --no-populate-cache
                                  At start of run, use `show` or
                                  `information_schema` queries to populate a
                                  relational cache, which can speed up
                                  subsequent materializations.
  --print / --no-print            Output all {{ print() }} macro calls.
  --printer-width INTEGER         Sets the width of terminal output
  --profile TEXT                  Which existing profile to load. Overrides
                                  setting in dbt_project.yml.
  -q, --quiet / --no-quiet        Suppress all non-error logging to stdout.
                                  Does not affect {{ print() }} macro calls.
  -r, --record-timing-info PATH   When this option is passed, dbt will output
                                  low-level timing stats to the specified
                                  file. Example: `--record-timing-info
                                  output.profile`
  --send-anonymous-usage-stats / --no-send-anonymous-usage-stats
                                  Send anonymous usage stats to dbt Labs.
  --state DIRECTORY               Unless overridden, use this state directory
                                  for both state comparison and deferral.
  --static-parser / --no-static-parser
                                  Use the static parser.
  -t, --target TEXT               Which target to load for the given profile
  --use-colors / --no-use-colors  Specify whether log output is colorized in
                                  the console and the log file. Use --use-
                                  colors-file/--no-use-colors-file to colorize
                                  the log file differently than the console.
  --use-colors-file / --no-use-colors-file
                                  Specify whether log file output is colorized
                                  by overriding the default value and the
                                  general --use-colors/--no-use-colors
                                  setting.
  --use-experimental-parser / --no-use-experimental-parser
                                  Enable experimental parsing features.
  -V, -v, --version               Show version information and exit
  --version-check / --no-version-check
                                  If set, ensure the installed dbt version
                                  matches the require-dbt-version specified in
                                  the dbt_project.yml file (if any).
                                  Otherwise, allow them to differ.
  --warn-error                    If dbt would normally warn, instead raise an
                                  exception. Examples include --select that
                                  selects nothing, deprecations,
                                  configurations with no associated models,
                                  invalid test configurations, and missing
                                  sources/refs in tests.
  --warn-error-options WARNERROROPTIONSTYPE
                                  If dbt would normally warn, instead raise an
                                  exception based on include/exclude
                                  configuration. Examples include --select
                                  that selects nothing, deprecations,
                                  configurations with no associated models,
                                  invalid test configurations, and missing
                                  sources/refs in tests. This argument should
                                  be a YAML string, with keys 'include' or
                                  'exclude'. eg. '{"include": "all",
                                  "exclude": ["NoNodesForSelectionCriteria"]}'
  --write-json / --no-write-json  Whether or not to write the manifest.json
                                  and run_results.json files to the target
                                  directory
  --exclude TUPLE                 Specify the nodes to exclude.
  -f, --full-refresh              If specified, dbt will drop incremental
                                  models and fully-recalculate the incremental
                                  table from the model definition.
  --profiles-dir PATH             Which directory to look in for the
                                  profiles.yml file. If not set, dbt will look
                                  in the current working directory first, then
                                  HOME/.dbt/
  --project-dir PATH              Which directory to look in for the
                                  dbt_project.yml file. Default is the current
                                  working directory and its parents.
  --empty / --no-empty            If specified, limit input refs and sources
                                  to zero rows.
  -s, -m, --select, --models, --model TUPLE
                                  Specify the nodes to include.
  --selector TEXT                 The selector name to use, as defined in
                                  selectors.yml
  --target-path PATH              Configure the 'target-path'. Only applies
                                  this setting for the current run. Overrides
                                  the 'DBT_TARGET_PATH' if it is set.
  --threads INTEGER               Specify number of threads to use while
                                  executing models. Overrides settings in
                                  profiles.yml.
  --vars YAML                     Supply variables to the project. This
                                  argument overrides variables defined in your
                                  dbt_project.yml file. This argument should
                                  be a YAML string, eg. '{my_variable:
                                  my_value}'
  -h, --help                      Show this message and exit.

【2】run-operation

* データベースに対して任意のメンテナンスSQLを実行するなど、マクロを呼び出す。

https://docs.getdbt.com/reference/commands/run-operation

【3】show

* 指定したModelや任意のSELECT文の出力結果を表示

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

--inline

* SQL文を直接指定

limit n
https://docs.getdbt.com/sql-reference/limit#how-to-use-the-limit-clause-in-a-query

* n : 表示数(デフォルト値:5)

参考文献
https://dev.classmethod.jp/articles/dbt-try-show-command/

【4】init

* 新規DBTプロジェクトの初期化処理

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

 => 以下の関連記事で実際に使用している

dbt ~ 入門編 ~
https://dk521123.hatenablog.com/entry/2023/05/30/151003

【5】seed

* CSVをロードしDBに挿入

dbt ~ 基本編 / Seed ~
https://dk521123.hatenablog.com/entry/2023/11/20/230946

【6】source

* sourceデータを扱うためのツールを提供

【7】docs

* ドキュメント生成に関するコマンド

https://docs.getdbt.com/reference/commands/cmd-docs

* 詳細は、以下の関連記事を参照のこと

dbt ~ ドキュメント化 / dbt docs ~
https://dk521123.hatenablog.com/entry/2023/12/10/125512

1)サブコマンド

generate

* dbt docs generate コマンドで、ドキュメントを生成する

server

* ブラウザが立ち上がり、作成したドキュメントを閲覧できる

関連記事

dbt ~ 基礎知識編 ~
https://dk521123.hatenablog.com/entry/2023/06/30/000000
dbt ~ 環境設定編 ~
https://dk521123.hatenablog.com/entry/2023/12/16/152147
dbt ~ 入門編 ~
https://dk521123.hatenablog.com/entry/2023/05/30/151003
dbt ~ 基本編 / Seed ~
https://dk521123.hatenablog.com/entry/2023/11/20/230946
dbt ~ ドキュメント化 / dbt docs ~
https://dk521123.hatenablog.com/entry/2023/12/10/125512
dbt で生成されるSQL文を確認する
https://dk521123.hatenablog.com/entry/2024/08/25/002741
Python with dbt ~ 入門編 ~
https://dk521123.hatenablog.com/entry/2024/07/20/034930