dbt(CLI)をとりあえず動かしてみた(BigQuery)

6872 ワード
BigQuery dbt BigQuery テキストリンク

これの続きです。

dbtを動かしてみた

とりあえずデフォルトで入っている2ファイルで流してみる。
/models/example/配下
my_first_dbt_model.sql
my_second_dbt_model.sql

$ dbt run
18:03:44  Running with dbt=1.0.4
18:03:44  Found 2 models, 4 tests, 0 snapshots, 0 analyses, 188 macros, 0 operations, 
0 seed files, 0 sources, 0 exposures, 0 metrics
18:03:44
18:03:45  Concurrency: 1 threads (target='dev')
18:03:45
18:03:45  1 of 2 START table model dataset.my_first_dbt_model........................ [RUN]
18:03:47  1 of 2 OK created table model dataset.my_first_dbt_model................... [CREATE TABLE (2.0 rows, 0 processed) in 2.61s]
18:03:47  2 of 2 START view model dataset.my_second_dbt_model........................ [RUN]
18:03:49  2 of 2 OK created view model dataset.my_second_dbt_model................... [OK in 1.22s]
18:03:49
18:03:49  Finished running 1 table model, 1 view model in 4.88s.
18:03:49
18:03:49  Completed successfully
18:03:49
18:03:49  Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

ここで失敗する場合はGoogleCloudのiamのBigQueryの権限を要確認。

エラー例（抜粋）

17:51:20  Encountered an error:
Database Error
  Access Denied: Project [Project ID]: User does not have bigquery.datasets.create permission in project [Project ID].

→roles/bigquery.userを与えれば解決

17:53:46  Completed with 1 error and 0 warnings:
17:53:46
17:53:46  Database Error in model my_first_dbt_model (models\example\my_first_dbt_model.sql)

→roles/bigquery.dataEditorを与えれば解決

成功していれば、下記2つができているはず。

上記内容はこちら参照

テストをする

/models/example/配下
schema.yml
のtestsの項目をテストしてくれているみたい。

schema.yml

version: 2

models:
  - name: my_first_dbt_model
    description: "A starter dbt model"
    columns:
      - name: id
        description: "The primary key for this table"
        tests:
          - unique
          - not_null

  - name: my_second_dbt_model
    description: "A starter dbt model"
    columns:
      - name: id
        description: "The primary key for this table"
        tests:
          - unique
          - not_null

いざ、テスト

$ dbt test
18:19:22  Running with dbt=1.0.4
18:19:22  Found 2 models, 4 tests, 0 snapshots, 0 analyses, 188 macros, 0 operations, 
0 seed files, 0 sources, 0 exposures, 0 metrics
18:19:22
18:19:22  Concurrency: 1 threads (target='dev')
18:19:22
18:19:22  1 of 4 START test not_null_my_first_dbt_model_id................................ [RUN]
18:19:24  1 of 4 FAIL 1 not_null_my_first_dbt_model_id.................................... [FAIL 1 in 1.77s]
18:19:24  2 of 4 START test not_null_my_second_dbt_model_id............................... [RUN]
18:19:26  2 of 4 PASS not_null_my_second_dbt_model_id..................................... [PASS in 1.82s]
18:19:26  3 of 4 START test unique_my_first_dbt_model_id.................................. [RUN]
18:19:28  3 of 4 PASS unique_my_first_dbt_model_id........................................ [PASS in 1.71s]
18:19:28  4 of 4 START test unique_my_second_dbt_model_id................................. [RUN]
18:19:30  4 of 4 PASS unique_my_second_dbt_model_id....................................... [PASS in 2.01s]
18:19:30
18:19:30  Finished running 4 tests in 7.93s.
18:19:30
18:19:30  Completed with 1 error and 0 warnings:
18:19:30
18:19:30  Failure in test not_null_my_first_dbt_model_id (models\example\schema.yml)  
18:19:30    Got 1 result, configured to fail if != 0
18:19:30
18:19:30    compiled SQL at target\compiled\dbt_test\models\example\schema.yml\not_null_my_first_dbt_model_id.sql
18:19:30
18:19:30  Done. PASS=3 WARN=0 ERROR=1 SKIP=0 TOTAL=4

テストはこちら参照

という感じで使っていくようです。