英文:
Using variable of run_query in a DBT model
问题
I'm using Dbt - Snowflake to insert values in a table. The model I create must behave differently depending on whether I'm on the weekend or not. During the week we do Append and during the weekend we do Truncate. To do this I check the current date with a "run_query" and I test the return value in an if tag. However I have a behavior that does not correspond to what I am looking for.
{% set boolWeek = run_query(select IFF(DAYNAME(CURRENT_DATE()) in ('Sat', 'Sun'), 'True','False'))%}
{% if boolWeek == 'True' %}
{{ config({
"query_tag":"Test",
"alias":"Test",
"materialized":"table",
"schema":"HUB"
})
}}
{% else %}
{{ config({
"query_tag":"Test",
"alias":"Test",
"materialized":"incremental",
"schema":"HUB"
})
}}
{% endif %}
I've tried several forms of settings for the variable and several forms of its reference ("{{boolWeek}}"
, boolWeek.columns[0].values()
etc.) no one works.
I'm quite lost and would like some examples to figure it out properly.
英文:
I'm using Dbt - Snowflake to insert values in a table. The model I create must behave differently depending on whether I'm on the weekend or not. During the week we do Append and during the weekend we do Truncate. To do this I check the current date with a "run_query" and I test the return value in an if tag. However I have a behavior that does not correspond to what I am looking for.
{% set boolWeek = run_query(select IFF(DAYNAME(CURRENT_DATE()) in ('Sat', 'Sun'), 'True','False'))%}
{% if boolWeek == 'True' %}
{{ config({
"query_tag":"Test",
"alias":"Test",
"materialized":"table",
"schema":"HUB"
})
}}
{% else %}
{{ config({
"query_tag":"Test",
"alias":"Test",
"materialized":"incremental",
"schema":"HUB"
})
}}
{% endif %}
I've tried several form of settings for the variable and several form of its reference ("{{boolWeek}}"
, boolWeek.columns[0].values()
etc.) no one works.
I'm quite lost and would like some examples to figure it properly out.
答案1
得分: 1
两件事:
- 你需要将你的 SQL 包裹在
run_query()
内,加上引号,就像这样:
{% set boolWeek = run_query("select IFF(DAYNAME(CURRENT_DATE()) in ('Sat', 'Sun'), 'True','False')") %}
2.(奖励,不需要运行此操作)如果你在处理布尔值,为什么不使用布尔值而不是字符串呢?你可以将你的 SQL 修改为以下形式来实现:
select coalesce(dayname(current_date()) in ('Sat', 'Sun'), false)
3.(奖励,与前一个项目相关)如果你使用布尔值,你需要调整你的 Jinja if
语句:
{% if boolWeek == true %}
所以,整个代码看起来是这样的:
{% set boolWeek = run_query("select coalesce(dayname(current_date()) in ('Sat', 'Sun'), false)") %}
{% if boolWeek == true %}
{{ config({
"query_tag":"Test",
"alias":"Test",
"materialized":"table",
"schema":"HUB"
})
}}
{% else %}
{{ config({
"query_tag":"Test",
"alias":"Test",
"materialized":"incremental",
"schema":"HUB"
})
}}
{% endif %}
英文:
Two things:
- You would need to wrap your SQL within
run_query()
in quotes, such as it looks like this:
{% set boolWeek = run_query("select IFF(DAYNAME(CURRENT_DATE()) in ('Sat', 'Sun'), 'True','False')")%}
- (bonus, not needed to run this) If you are dealing with booleans, why not use them instead of strings? You could change your SQL to look like this to accomplish so:
select coalesce(dayname(current_date()) in ('Sat', 'Sun'), false)
- (bonus, related to the latter bullet) If you then use booleans, you would need to adjust your Jinja
if
statement:
{% if boolWeek == true %}
So, the whole thing would look like this:
{% set boolWeek = run_query("select coalesce(dayname(current_date()) in ('Sat', 'Sun'), false)") %}
{% if boolWeek == true %}
{{ config({
"query_tag":"Test",
"alias":"Test",
"materialized":"table",
"schema":"HUB"
})
}}
{% else %}
{{ config({
"query_tag":"Test",
"alias":"Test",
"materialized":"incremental",
"schema":"HUB"
})
}}
{% endif %}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论