BigQuery GENERATE_UUID() 和 CTE [英] BigQuery GENERATE_UUID() and CTE's

问题描述

这种行为让我有点惊讶.

This behavior surprised me a little bit.

当您在 CTE 中生成 uuid(用于制作行 ID 等)并在将来引用它时，您会发现它发生了变化.似乎 generate_uuid() 被调用了两次而不是一次.有谁知道为什么 BigQuery 会出现这种情况以及这叫什么?

When you generate a uuid in a CTE (to make a row id, etc) and reference it in the future you'll find that it changes. It seems that generate_uuid() is being called twice instead of once. Anyone know why this is the case w/ BigQuery and what this is called?

我使用 generate_uuid() 来创建一个 row_id 并发现在我最终的连接中没有匹配发生.我发现解决它的最佳方法是从第一个 CTE 创建一个表，将 uuid 固定到位以备将来使用.

I was using generate_uuid() to create a row_id and was finding that in my eventual joins that no matches were occurring because of this. Best way to get around it I've found is by just creating a table from the first CTE which cements the uuid in place for future use.

仍然很想知道更多关于这背后的原因和内容.

Still curious to know more about the why and what behind this.

with _first as (

  select generate_uuid() as row_id
  
)
,_second as (

  select * from _first
  
)

select row_id from _first
union all
select row_id from _second

推荐答案

想知道更多关于这背后的原因和内容

curious to know more about the why and what behind this

这是设计使然:

WITH 子句未具体化.将所有查询放在 WITH 子句中然后运行 ​​UNION ALL 是对 WITH 子句的误用.
如果查询出现在多个 WITH 子句中，它会在每个子句中执行.

WITH clauses are not materialized. Placing all your queries in WITH clauses and then running UNION ALL is a misuse of the WITH clause.
If a query appears in more than one WITH clause, it executes in each clause.

您可以在文档中看到 - 不要对待 WITH 子句作为准备好的语句

You can see in documentation - Do not treat WITH clauses as prepared statements

这篇关于BigQuery GENERATE_UUID() 和 CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！