dbtとBQMLを使った高速なMLモデル構築のパイプライン


## モチベーション

- dbtがDWH/DataMartの整備やデータ品質のテストやリネージが効率的に行えそう。
- データマート加工よりもMLOpsの１つの方法として、MLパイプラインを確認してみたい。

### BQMLについて

https://cloud.google.com/bigquery-ml/docs

BigQueryのクエリで簡単にモデル作れる機能のことで、BigQuery MLとも言う。

### dbtについて

https://www.getdbt.com/

オンプレ版とクラウド版がある。

この辺にアナリティクス・エンジニアリングのワークフローという触れ込みがあり、
今後、アナリティクス・エンジニアのデファクトツールとなるのか気になるところ。

https://www.getdbt.com/what-is-analytics-engineering/

dbtのコンセプトは、"dbt is the T in ELT."
![1-BogoeTTK1OXFU1hPfUyCFw.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636318/5606c824-35bd-51cb-a4c1-30c63ba261e4.png)

こちらのリンクに背景とか色々書いてあります。

https://blog.getdbt.com/what-exactly-is-dbt/

チュートリアルはこちらの記事が参考になりました。

https://dev.classmethod.jp/articles/dbt-tutorial/

GCPの[dataform](https://dataform.co/)と似たコンセプトのOSSベースのツール。
いづれにも触れてみての所感を書いている方がいて、ふむふむと読ませて頂きました。

https://www.yasuhisay.info/entry/2021/07/25/011000

https://attsun1031.github.io/blog/dbt-dataform-comparison

----
ここからは、実際に手を動かした手順メモ的な内容

## dbtインストール

Cloud版では要らないのですが、CLI版で試しました。

https://docs.getdbt.com/dbt-cli/install/homebrew

この辺でmac版のhomebrewで試した。dbt-bigqueryのみでOK

インストールされたバージョンとプラグインの確認。こんな感じで出ればOK

<details><summary>dbt --version</summary><div>

```
% dbt --version                                                                                          (git)-[master]
installed version: 1.0.0
   latest version: 1.0.0

Up to date!

Plugins:
  - bigquery: 1.0.0
```
</div></details>

つまづき易いポイント

* バージョンが古いの入っていたり、プラグインがない場合、linkで新しい方に切り替える。アンインストールしてから入れ直す。

## dbt profileの設定

https://docs.getdbt.com/reference/warehouse-profiles/bigquery-profile

方法は4つあり、1.が手軽でおすすめ

1. oauth via gcloud
2. oauth token-based
3. service account file
4. service account json


## dbt_mlのサンプルコードの外観を掴む

https://github.com/balmasi/dbt_bigquery_ml_example

### 参考記事

https://showmethedata.blog/agile-machine-learning-with-dbt-and-bigquery-ml

### dbt_mlについて

https://hub.getdbt.com/kristeligt-dagblad/dbt_ml/latest/

dbt_mlではモデルの評価結果を新たにテーブルを作ることなく、auditというテーブルにトレーニングする毎に書き込んで、実験結果を並べて見ることを意識した出力にしている。

出力されるテーブルは、次のようなカラムがある。ml.evaluateはないっぽい。

<details><summary>macros/hooks/model_audit.sql</summary><div>

```
{% macro _audit_table_columns() %}

{% do return ({
    'model': 'string',
    'schema': 'string',
    'created_at': dbt_utils.type_timestamp(),
    'training_info': 'array<struct<training_run int64, iteration int64, loss float64, eval_loss float64, learning_rate float64, duration_ms int64, cluster_info array<struct<centroid_id int64, cluster_radius float64, cluster_size int64>>>>',
    'feature_info': 'array<struct<input string, min float64, max float64, mean float64, median float64, stddev float64, category_count int64, null_count int64>>',
    'weights': 'array<struct<processed_input string, weight float64, category_weights array<struct<category string, weight float64>>>>',
}) %}

{% endmacro %}
```
</div></details>

post_hookにて、次のようにマクロを設定すると、上記のテーブルにtraining_info, feature_infoなどが追加される。

<details><summary>features.sql</summary><div>

```
{{
    config(
        materialized='model',
        
        ml_config={
            'model_type': 'logistic_reg',
            'early_stop': true,
            'ls_init_learn_rate': 0.1,
        },
        
        post_hook="{{ dbt_ml.model_audit() }}" 
    )
}}

select * from {{ ref('features') }}
```
</div></details>

<details><summary>auditテーブル</summary><div>
![スクリーンショット 2021-12-23 22.15.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636318/aebc855b-f764-1cb0-748e-8b9d2baca0d4.png)
</div></details>

## dbt実行

### dbt_projectの設定

dbt_project.ymlで設定したprofileを指定する。

```
profile: my-bigquery-db
```

### 実行

depsで必要なdbt_utils, dbt_mlを読み込み、seedでデータソースをBigQueryにロードする。
最後にrunで実行する手順になります。
以下は、実行したそれぞれのコマンド出力。

<details><summary>det deps</summary><div>

```
% dbt deps                                                                                                 (git)-[main]
14:44:40  No profile "dbt_bqml_example" found, continuing with no target
14:44:40  [WARNING]: Deprecated functionality
The `source-paths` config has been renamed to `model-paths`. Please update your
`dbt_project.yml` configuration to reflect this change.
14:44:40  [WARNING]: Deprecated functionality
The `data-paths` config has been renamed to `seed-paths`. Please update your
`dbt_project.yml` configuration to reflect this change.
14:44:40  Running with dbt=1.0.0
14:44:42  Installing version 0.5.1
14:44:42    Installed from version 0.5.1
14:44:42    Up to date!
14:44:42  Installing version 0.8.0
14:44:43    Installed from version 0.8.0
14:44:43    Up to date!
```

</div></details>

<details><summary>dbt seed</summary><div>

```
% dbt seed                                                                                                 (git)-[main]
14:55:06  [WARNING]: Deprecated functionality
The `source-paths` config has been renamed to `model-paths`. Please update your
`dbt_project.yml` configuration to reflect this change.
14:55:06  [WARNING]: Deprecated functionality
The `data-paths` config has been renamed to `seed-paths`. Please update your
`dbt_project.yml` configuration to reflect this change.
14:55:06  Running with dbt=1.0.0
14:55:06  Found 5 models, 0 tests, 0 snapshots, 0 analyses, 391 macros, 2 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics
14:55:06  
14:55:08  
14:55:08  Running 2 on-run-start hooks
14:55:09  1 of 2 START hook: dbt_ml_example.on-run-start.0................................ [RUN]
14:55:09  1 of 2 OK hook: dbt_ml_example.on-run-start.0................................... [OK in 0.00s]
14:55:09  2 of 2 START hook: dbt_ml_example.on-run-start.1................................ [RUN]
14:55:09  2 of 2 OK hook: dbt_ml_example.on-run-start.1................................... [OK in 0.00s]
14:55:09  
14:55:09  Concurrency: 8 threads (target='dev')
14:55:09  
14:55:09  1 of 3 START seed file dbt_ml_example.predict_me................................ [RUN]
14:55:09  2 of 3 START seed file dbt_ml_example.raw_titanic............................... [RUN]
14:55:09  3 of 3 START seed file dbt_ml_example.test...................................... [RUN]
14:55:12  3 of 3 OK loaded seed file dbt_ml_example.test.................................. [INSERT 418 in 2.52s]
14:55:12  1 of 3 OK loaded seed file dbt_ml_example.predict_me............................ [INSERT 7 in 2.71s]
14:55:13  2 of 3 OK loaded seed file dbt_ml_example.raw_titanic........................... [INSERT 891 in 3.88s]
14:55:13  
14:55:13  Finished running 3 seeds, 2 hooks in 6.78s.
14:55:13  
14:55:13  Completed successfully
14:55:13  
14:55:13  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3
```
</div></details>

<details><summary>dbt run</summary><div>

```
% dbt run                                                                                                  (git)-[main]
02:58:22  [WARNING]: Deprecated functionality
The `source-paths` config has been renamed to `model-paths`. Please update your
`dbt_project.yml` configuration to reflect this change.
02:58:22  [WARNING]: Deprecated functionality
The `data-paths` config has been renamed to `seed-paths`. Please update your
`dbt_project.yml` configuration to reflect this change.
02:58:23  Running with dbt=1.0.0
02:58:23  Unable to do partial parsing because profile has changed
02:58:23  Unable to do partial parsing because a project config has changed
02:58:24  [WARNING]: Did not find matching node for patch with name 'ml_logreg' in the 'models' section of file 'models/schema.yml'
02:58:24  [WARNING]: Did not find matching node for patch with name 'hparam_tune' in the 'models' section of file 'models/schema.yml'
02:58:24  Found 3 models, 0 tests, 0 snapshots, 0 analyses, 391 macros, 2 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics
02:58:24  
02:58:26  
02:58:26  Running 2 on-run-start hooks
02:58:26  1 of 2 START hook: dbt_ml_example.on-run-start.0................................ [RUN]
02:58:26  1 of 2 OK hook: dbt_ml_example.on-run-start.0................................... [OK in 0.00s]
02:58:26  2 of 2 START hook: dbt_ml_example.on-run-start.1................................ [RUN]
02:58:26  2 of 2 OK hook: dbt_ml_example.on-run-start.1................................... [OK in 0.00s]
02:58:26  
02:58:26  Concurrency: 8 threads (target='dev')
02:58:26  
02:58:26  1 of 3 START view model dbt_ml_example.features................................. [RUN]
02:58:27  1 of 3 OK created view model dbt_ml_example.features............................ [OK in 1.03s]
02:58:27  2 of 3 START model model dbt_ml_example.model................................... [RUN]
02:59:00  2 of 3 OK created model model dbt_ml_example.model.............................. [OK in 32.70s]
02:59:00  3 of 3 START table model dbt_ml_example.ml_predicted............................ [RUN]
02:59:02  3 of 3 OK created table model dbt_ml_example.ml_predicted....................... [CREATE TABLE (418.0 rows, 23.8 KB processed) in 2.57s]
02:59:02  
02:59:02  Finished running 1 view model, 1 model model, 1 table model, 2 hooks in 38.76s.
02:59:02  
02:59:02  Completed successfully
02:59:02  
02:59:02  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3
```
</div></details>

モデルの評価結果はこちら

<details><summary>評価結果</summary><div>

![スクリーンショット 2021-12-23 22.11.22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636318/323d2b67-ce2e-c9f7-e901-527210e53141.png)
![スクリーンショット 2021-12-23 22.11.34.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636318/1f0a6023-383b-df36-3bea-eb37246a2e63.png)
</div></details>

<details><summary>auditテーブル</summary><div>
![スクリーンショット 2021-12-23 22.13.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636318/896d365f-fb63-9f61-a045-de52e7ada793.png)
![スクリーンショット 2021-12-23 22.14.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636318/0b6c877f-36ce-5a5f-e18f-787b95cc93f4.png)
![スクリーンショット 2021-12-23 22.14.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636318/8f8ffa27-8b46-e715-5f66-f00a96b8f590.png)
</div></details>

つまづき易いポイント

* dbt_mlとのバージョン、サンプルのdbt_mlのバージョンが古いんで最新のdbtに書き換える

<details><summary>packages.yml</summary><div>

```
packages:
  - package: kristeligt-dagblad/dbt_ml
    version: 0.5.1
```
</div></details>

* gcpのoauthログイン, profilesの設定
* profileのbqのデフォルトのロケーション、ロケーションをミスるとdatasetが見つからないとか出るあれ
* dbt_project.ymlでprofile切り替え忘れ

## ちょっといじって実行してみる

dataformでbqmlを使う記事があったので、試しにdbtでどうなるか試してみました。

https://dataform.co/blog/bq-ml-pipeline

題材は、パブリックデータである new_york_taxi_trips で、tip_amount チップ額を回帰問題でとくもの。

### 特徴量のSQL

features.sqlでselectのする内容変える。

<details><summary>models/features.sql</summary><div>

```
WITH final AS (
    SELECT
        tip_amount AS label,
        SUBSTR(CAST(pickup_datetime AS string), 12, 2) pickup_datetime,
        SUBSTR(CAST(dropoff_datetime AS string), 12, 2) dropoff_datetime,
        passenger_count,
        trip_distance,
        rate_code,
        fare_amount,
        pickup_location_id,
        dropoff_location_id
    FROM
        `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2018`
    TABLESAMPLE SYSTEM (20 PERCENT)
)
SELECT * FROM final
```
</div></details>

### MLモデルタイプを変更

<details><summary>models/ml_linear_reg.sql</summary><div>

```
{{
    config(
        materialized='model', 

        ml_config={ 
            'model_type': 'linear_reg',
            'early_stop': true,
            'ls_init_learn_rate': 0.1
        },
        
        post_hook="{{ dbt_ml.model_audit() }}" 
    )
}}
SELECT * FROM {{ ref('features') }}
```
</div></details>

この辺に詳しいCreate Modelのリファレンスがあるんで、これ見ていじってみると良い。
https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create

models以下にbqml trainingするために必要なSQLを追加するだけ。
featuresとの依存関係は、プレースホルダのrefキーワードで決めて実行順序を制御してくれてるっぽい。

* automl_regressionでは、ml.weightsがサポートされないとエラーがでた。あんまりテストしてないっぽい。試した[GoogleブログのAutoML TablesがBigQuery MLで一般提供されたときの記事](https://cloud.google.com/blog/ja/products/data-analytics/automl-tables-now-generally-available-bigquery-ml)

<details><summary>models/ml_automl_reg.sql</summary><div>

```
{{
    config(
        materialized='model', 

        ml_config={ 
            'model_type': 'AUTOML_REGRESSOR',
            'budget_hours': 1.0
        },
        
        post_hook="{{ dbt_ml.model_audit() }}" 
    )
}}
SELECT * FROM {{ ref('features') }}
```
</div></details>

一応、モデルの評価結果はこちら、

<details><summary>model info</summary><div>

![スクリーンショット 2021-12-23 22.34.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636318/6a520364-84da-69bc-19fd-f011ce8edb80.png)
</div></details>

### ハイパーパラメータチューニング
サポートされているとREADMEに書いてあるが、audit書き込みでエラーが出ました。

https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-hyperparameter-tuning

* ml.training_infoではなく代わりにml.trials_infoを利用せよとエラーが出る。これもバグっぽい
* trials回数が多すぎるとエラーが出る。これもバグっぽい

<details><summary>models/ml_dnn_class_hparam_tune.sql</summary><div>

```
{{
    config(
        materialized='model',
        ml_config={
            'model_type': 'dnn_classifier',
            'auto_class_weights': true,
            'learn_rate': dbt_ml.hparam_range(0.01, 0.1),
            'early_stop': true,
            'max_iterations': 50,
            'num_trials': 10,
            'max_parallel_trials': 2,
            'dropout': dbt_ml.hparam_candidates([0, 0.1, 0.25, 0.4]),
            'optimizer': dbt_ml.hparam_candidates(['adam', 'sgd'])
        }
    )
}}
SELECT * FROM {{ ref('features') }}
```
</div></details>

auditの書き込みと予測sqlが出来ずエラーになっている。一応、テーブルに結果は出ていた。

<details><summary>hparam model info</summary><div>

![スクリーンショット 2021-12-23 22.12.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636318/05064117-416a-dfb5-93c2-85a792e81cf9.png)
</div></details>

## 予測SQL

テストデータのテーブルを使う、ml_predictはこんな感じ

<details><summary>models/ml_predicted.sql</summary><div>

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

WITH predict_features AS (
    SELECT
        tip_amount,
        SUBSTR(CAST(pickup_datetime AS string), 12, 2) pickup_datetime,
        SUBSTR(CAST(dropoff_datetime AS string), 12, 2) dropoff_datetime,
        passenger_count,
        trip_distance,
        rate_code,
        fare_amount,
        pickup_location_id,
        dropoff_location_id
    FROM
        `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2018` LIMIT 100
)

SELECT * FROM {{ dbt_ml.predict(ref('ml_logreg'), 'predict_features') }}
```
</div></details>

予測結果の表はこちら、１レコードにつきクラス毎のスコアがrecord型で出力されている。

<details><summary>bigquery ml_predicted</summary><div>

![スクリーンショット 2021-12-23 22.20.46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636318/d440d9ea-4252-f23c-5773-042ba3c46cd9.png)

</div></details>


## まとめ
このdbtのパイプラインでモデル構築〜評価までdbt runコマンド一発で高速に回せるのは非常に魅力的です。
SQLで書ける簡単な特徴量エンジニアリングなら、features.sqlに書くだけで済む手軽さが素晴らしい。
最近のBigQueryのリリースで、pythonで実行する複雑なものはCloud Functionsで書いて外部関数として実行できるようなるため、このfeatures.sqlでそんなものを呼び出しても良さそうです。

https://medium.com/google-cloud-jp/bigquery-whats-new-2021-cc9866f90d5c

日々の運用を考えると学習や評価データの期間をパラメータで取って、スケジュール呼び出し可能なcloud版を利用すると手軽そうです。

## 今後やるかわからないけども

* 次はdataformとBQMLでも試してみたい
* もっと実用的な特徴量設計、モデリングの場合でも試したい
https://github.com/shimacos37/bqml-tutorial