🚀
Snowflake x dbtやってみた〜マクロ編〜
マクロとは？
Jinjaテンプレートを使った柔軟なSQLを書くことができます。
マクロを設定し、それをテストとして実行してみる
以下のSQLをmacrosディレクトリにて作成する。
macros/no_nulls_in_columns.sql
{% macro no_nulls_in_columns(model) %}
SELECT * FROM {{ model }} WHERE
{% for col in adapter.get_columns_in_relation(model) -%}
{{ col.column }} IS NULL OR
{% endfor %}
FALSE
{% endmacro %}
↑で設定したマクロを利用するためにtestを書きます。
tests/no_nulls_in_dim_listings.sql
{{ no_nulls_in_columns(ref('dim_listings_cleansed')) }}
実際にコンパイルされたクエリ
target/compiled/dbtlearn/tests/no_nulls_in_dim_listings.sql
SELECT * FROM AIRBNB.dev.dim_listings_cleansed WHERE
LISTING_ID IS NULL OR
LISTING_NAME IS NULL OR
ROOM_TYPE IS NULL OR
MINIMUM_NIGHTS IS NULL OR
HOST_ID IS NULL OR
PRICE IS NULL OR
CREATED_AT IS NULL OR
UPDATED_AT IS NULL OR
FALSE
サードパーティパッケージ
dbtへサードパーティパッケージをインストールすることで、機能を拡張することができます。
パッケージの定義
packages.yml
packages:
- package: dbt-labs/dbt_utils
version: 0.8.0
パッケージのインストール
ubuntu@dbt:~/dbtlearn$ dbt deps
02:59:48 Running with dbt=1.3.1
02:59:48 Installing dbt-labs/dbt_utils
02:59:49 Installed from version 0.8.0
02:59:49 Updated version available: 1.0.0
02:59:49
02:59:49 Updates available for packages: ['dbt-labs/dbt_utils']
Update your versions in packages.yml, then run dbt deps
ubuntu@dbt:~/dbtlearn$
generate_surrogate_key
今回はgenerate_surrogate_keyを利用してみます。
ドキュメントはこちらを確認ください。
このマクロは、指定されたフィールドを使用してハッシュ化されたサロゲートキーを生成するためのデータベース横断的な方法を実装しています。
複数のカラムを指定し、主キーを作成してみます。
models/fct_reviews.sqlの修正
以下のカラムをハッシュ化させてreview_idカラムを作成します。
- listing_id
- review_date
- reviewer_name
- review_text
{{
config(
materialized = 'incremental',
on_schema_change='fail'
)
}}
WITH src_reviews AS (
SELECT * FROM {{ ref('src_reviews') }}
)
SELECT
{{ dbt_utils.surrogate_key(['listing_id', 'review_date', 'reviewer_name', 'review_text']) }}
AS review_id,
*
FROM src_reviews
WHERE review_text is not null
{% if is_incremental() %}
AND review_date > (select max(review_date) from {{ this }})
{% endif %}
dbt runの実行
ubuntu@dbt:~/dbtlearn$ dbt run --full-refresh --select fct_reviews
snowflakeでテーブル確認
確かにREVIEW_IDが追加されています。
xxxx#COMPUTE_WH@(no database).(no schema)>SELECT * from airbnb.dev.fct_reviews LIMIT 10;
+----------------------------------+------------+-------------------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| REVIEW_ID | LISTING_ID | REVIEW_DATE | REVIEWER_NAME | REVIEW_TEXT | REVIEW_SENTIMENT |
|----------------------------------+------------+-------------------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------|
| 4b734c1aab5b219b453c1d3d6a87c5cf | 35175941 | 2019-08-07 00:00:00.000 | Reiner | Gute Kommunikation, gute Lage der Wohnung, Schlafzimmer zum Hinterhof ruhig,brschöne Einrichtung, komme gerne wieder. | neutral |
| f93e33495d0871e0c0d126c3ec762db6 | 35175941 | 2019-09-25 00:00:00.000 | Nader | Very nice apartment. All was clean and enjoyed staying there. Very wellequiped. Definitely recommendable | positive |
| 83519e510029e76c1d83bdc73b3a069e | 35175941 | 2019-09-30 00:00:00.000 | Mark | Die Wohnung ist toll. Grosse Küche( Wasserkocher mitnehmen :)), grosses Bad mit einer Waschmaschine und einem Trockner. Schlafzimmer mit dem Fenster zum Innenhof, also mann kann gut schlafen. Gegenüber gibt es ein Kiosk, der fast 24h geöffnet ist. | negative |
| 790f50e4bd01df17604ed6f5ca8f1a22 | 35175941 | 2019-10-08 00:00:00.000 | Irem | Kommunikation sowie checkincheckout haben gut geklappt. Die Wohnung hat eine riesen Küche und ist sehr gut ausgestattet. Eher in einem ruhigen Viertel, jedoch gute BusTramverbindung und Station nahe gelegen. Kann die Unterkunft weiterempfehlen. | negative |
| f7a8b7f894b6190c61de2c90b8a72edc | 35175941 | 2019-10-12 00:00:00.000 | Valerio | The apartment is very nice and comfy, and very near the underground station. Location is indeed very close to the centre, and easy to reach with public transportation.brThere is also a Grocery store at 3 mins walking, which is also very good to have nearby.brLast but not least, the host has been very nice and always available for any sort of request I had. Thanks | positive |
| 801138d4bc48982029b27b1ebb28d34a | 35175941 | 2019-10-24 00:00:00.000 | Nada | I really enjoyed staying at Ralfs. The apartment is even nicer and more spacious than the pictures. My questions were always quickly answered and the location is very convenient at it is 1minute away from the bus stop. Thanks for everything | positive |
| cb8593845d5b3ed8c1bb66db06513b5c | 35175941 | 2019-10-29 00:00:00.000 | Joel | Great place I really loved everything about it. Its a ten minute walk from some amazing restaurants. | positive |
| 1472250436a51e9a1ec7fa7b47b9b028 | 35179379 | 2019-06-01 00:00:00.000 | Jakub | Very nice apartment that gives a feeling of home. Calm area, parks but also shops and public transport nearby. I warmly recommend to everyone. | positive |
| c5cc352f69bbf355aa78132303987725 | 35179379 | 2019-11-09 00:00:00.000 | Verena | Super comfortable, Franzi makes you feel at home. Thank you | positive |
| 1da309859b1e9f4ff082bebe91494535 | 35179379 | 2020-03-01 00:00:00.000 | Karol | I really enjoyed staying at Franziskas place. Shes a great host and really makes you feel at home. | positive |
+----------------------------------+------------+-------------------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
10 Row(s) produced. Time Elapsed: 0.142s
xxxx#COMPUTE_WH@(no database).(no schema)>
Discussion