Pendahuluan
dbt memungkinkan Anda untuk menulis kode SQL yang fleksibel menggunakan Jinja dan makro. Artikel ini menjelaskan notasi dan penggunaan Jinja dan makro.
Jinja
Jinja adalah sejenis mesin template yang dapat digunakan untuk menulis tidak hanya SQL, tetapi juga HTML, CSS, dan banyak file teks lainnya.
Jinja menyediakan tiga notasi berikut.
{% ... %}: sintaks kontrol
Mengontrol alur pemrosesan, sepertiforatauif.{{ ... }}: Ekspresi
Mengeluarkan hasil dari ekspresi sebagai string.{# ... #}: komentar
Anda dapat menulis komentar untuk membuat program lebih mudah dibaca orang. Komentar-komentar tersebut tidak akan menjadi output pada SQL final.
Sintaks if
Jinja menggunakan sintaks if sebagai berikut.
{% if kenny.sick %}
Kenny is sick.
{% elif kenny.dead %}
You killed Kenny! You bastard!!!
{% else %}
Kenny looks okay --- so far
{% endif %}
Jika sintaks if ditulis pada satu baris, maka akan terlihat seperti ini.
{{ env if env is defined else 'dev' }}
Sintaks for
Di Jinja, sintaks for dijelaskan sebagai berikut.
{% for col in columuns %}
{{ col }} ,
{% endfor %}
{% for key, val in dict %}
{{ key }}, {{ val }}
{% endfor %}
Variabel khusus bisa digunakan dalam sintaks for.
| Variabel | Deskripsi |
|---|---|
| loop.index | The current iteration of the loop. (1 indexed) |
| loop.index0 | The current iteration of the loop. (0 indexed) |
| loop.revindex | The number of iterations from the end of the loop (1 indexed) |
| loop.revindex0 | The number of iterations from the end of the loop (0 indexed) |
| loop.first | True if first iteration. |
| loop.last | True if last iteration. |
| loop.length | The number of items in the sequence. |
| loop.cycle | A helper function to cycle between a list of sequences. See the explanation below. |
| loop.depth | Indicates how deep in a recursive loop the rendering currently is. Starts at level 1 |
| loop.depth0 | Indicates how deep in a recursive loop the rendering currently is. Starts at level 0 |
| loop.previtem | The item from the previous iteration of the loop. Undefined during the first iteration. |
| loop.nextitem | The item from the following iteration of the loop. Undefined during the last iteration. |
| loop.changed(*val) | True if previously called with a different value (or not called at all). |
Fungsi-fungsi Jinja dbt sendiri
dbt mendefinisikan fungsi-fungsi Jinja-nya sendiri. Selain config(), ref(), dan source() yang umum digunakan, fungsi-fungsi berikut ini tersedia.
| dbt Jinja functions | ||
|---|---|---|
| adapter | execute | run_query |
| as_bool | flags | run_started_at |
| as_native | fromjson | schema |
| as_number | fromyaml | schemas |
| as_text | graph | selected_resources |
| builtins | invocation_id | set |
| config | log | source |
| cross-database macros | model | statement blocks |
| dbt_project.yml Context | modules | target |
| dbt_version | on-run-end Context | this |
| debug | tojson | |
| dispatch | profiles.yml Context | toyaml |
| doc | project_name | var |
| env_var | ref | zip |
| exceptions | return |
Contoh notasi Jinja
Misalkan Anda memiliki SQL berikut.
select
order_id,
sum(case when payment_method = 'bank_transfer' then amount end) as bank_transfer_amount,
sum(case when payment_method = 'credit_card' then amount end) as credit_card_amount,
sum(case when payment_method = 'gift_card' then amount end) as gift_card_amount,
sum(amount) as total_amount
from {{ ref('raw_payments') }}
group by 1
SQL di atas dapat ditulis menggunakan loop for Jinja dan variabel sebagai berikut.
{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}
select
order_id,
{% for payment_method in payment_methods %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount,
{% endfor %}
sum(amount) as total_amount
from {{ ref('raw_payments') }}
group by 1
Gunakan loop.last untuk menghindari meletakkan koma di akhir perulangan for.
{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}
select
order_id,
{% for payment_method in payment_methods %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount
{% if not loop.last %},{% endif %}
{% endfor %}
from {{ ref('raw_payments') }}
group by 1
Makro
Makro adalah fungsi-fungsi yang membuat kode yang dapat digunakan kembali. Tempatkan file SQL di direktori yang ditentukan oleh macro-paths di dbt_project.yml (macros secara default) dan tulis makro di dalamnya.
Misalnya, jika Anda ingin membuat daftar metode pembayaran sebagai variabel umum dalam makro, tulis sebagai berikut.
{% macro get_payment_methods() %}
{{ return(["bank_transfer", "credit_card", "gift_card"]) }}
{% endmacro %}
{% set payment_methods = get_payment_methods() %}
select
order_id,
{%- for payment_method in payment_methods %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount
{%- if not loop.last %},{% endif %}
{% endfor %}
from {{ ref('raw_payments') }}
group by 1
Jika Anda ingin menjadikan fungsi untuk mengkonversi mata uang sebagai fungsi umum dalam makro, tuliskan sebagai berikut.
{% macro usd_to_jpy(col_name, rate=100) %}
{{col_name}} * rate
{% endmacro %}
select
id,
usd_to_jpy('price', 90)
from {{ ref('raw_payments') }}
group by 1
Referensi
Ryusei Kakujo
Focusing on data science for mobility
Bench Press 100kg!