使用DBT更新BigQuery的表格

发布于 2025-01-23 11:14:42 字数 407 浏览 2 评论 0原文

我正在尝试使用DBT更新BigQuery的表。以下命令在BigQuery执行；

Update {{ ref('my_table') }}
SET variable = 'value'
WHERE lower(variable) LIKE '%XX%' or lower(variable) like '%YY%'

但是，当我在DBT中运行它时，我会收到以下错误，

Server error: Database Error in rpc request (from remote system)
Syntax error: Expected end of input but got keyword LIMIT at [4:1]

有人知道为什么会发生这种情况以及如何解决吗？

原文

I am trying to update a table in bigquery using DBT. The below command executes in bigquery;

Update {{ ref('my_table') }}
SET variable = 'value'
WHERE lower(variable) LIKE '%XX%' or lower(variable) like '%YY%'

However when I run it in DBT I get the following error

Server error: Database Error in rpc request (from remote system)
Syntax error: Expected end of input but got keyword LIMIT at [4:1]

Does anyone know why this is happening and how to resolve?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

未蓝澄海的烟 2025-01-30 11:14:42

起初我知道这有点不直觉是一个选择语句。

相反，您应该考虑做类似：

with cte as (
     select * from {{ ref('my_table') }}
     where <criteria>
)
 select col1,
        col2,
        'value' as col3
 from cte

或更简单的事情：

SELECT 
  'value' as variable
FROM {{ ref('my_table') }}
WHERE lower(variable) LIKE '%XX%' or lower(variable) like '%YY%'

仅仅是因为在dbt Run循环中，新值将被实现为新模型。

但是，如果您正在寻找以干燥方式清洁基础桌子的方法，我强烈推荐此线程建模SQL Update语句来自DBT话语的一些模式，以管理处理特定价值清洁的语句。凯尔·里斯（Kyle Ries）的例子：

{% set mappings = {'something': 'boo', 'something-else': 'boo-else'} %}

with source as (
        select * from {{ ref(‘stg_foobar’) }}
),

final as ( 

        select
            case
              {% for old, new in mappings %}
                when other_column like ‘{{old}}’ then ‘{{new}}’
              {% endfor %}
            end as column_name
        from
            source

)

select * from final

It's a little unintuitive at first I know but with dbt, every model is a select statement.

You should instead think of doing something like:

with cte as (
     select * from {{ ref('my_table') }}
     where <criteria>
)
 select col1,
        col2,
        'value' as col3
 from cte

Or possibly even simpler:

SELECT 
  'value' as variable
FROM {{ ref('my_table') }}
WHERE lower(variable) LIKE '%XX%' or lower(variable) like '%YY%'

Simply because during the dbt run cycle, the new values will be materialized into the new model.

However, if you are looking for ways to clean underlying tables in a DRY way, I'd highly recommend this thread Modeling SQL Update Statements from the dbt discourse for some patterns on managing statements which handle specific value cleaning. Example from Kyle Ries:

{% set mappings = {'something': 'boo', 'something-else': 'boo-else'} %}

with source as (
        select * from {{ ref(‘stg_foobar’) }}
),

final as ( 

        select
            case
              {% for old, new in mappings %}
                when other_column like ‘{{old}}’ then ‘{{new}}’
              {% endfor %}
            end as column_name
        from
            source

)

select * from final

回复收藏 0 原文

~没有更多了~