在DBT模型中使用run_query变量。

huangapple go评论57阅读模式
英文:

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

两件事:

  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:

  1. 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')")%}
  1. (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)
  1. (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 %}

huangapple
  • 本文由 发表于 2023年3月10日 01:16:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/75687956.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定