google-bigquery - 如果没有可用于分区消除的过滤器，则无法查询表

Question

我有一个分区表，很想使用 MERGE 语句，但由于某种原因不起作用。

MERGE `wr_live.p_email_event`  t
using `wr_live.email_event` s
on t.user_id=s.user_id and t.event=s.event and t.timestamp=s.timestamp
WHEN NOT MATCHED THEN
INSERT (user_id,event,engagement_score,dest_email_domain,timestamp,tags,meta)
values (user_id,event,engagement_score,dest_email_domain,timestamp,tags,meta)

我得到

Cannot query over table 'wr_live.p_email_event' without a filter that can be used for partition elimination.

什么是正确的语法？还有一种方法可以表达更短的插入内容吗？不命名所有列？

Answer 1

What's the proper syntax?

正如您从错误消息中看到的 - 您的分区 wr_live.p_email_event表是用 require partition filter 创建的设置为真。这意味着对该表的任何查询都必须在各自的分区字段上有一些过滤器

假设 timestamp是分区字段 - 您可以执行以下操作

MERGE `wr_live.p_email_event`  t
USING `wr_live.email_event` s
ON t.user_id=s.user_id AND t.event=s.event AND t.timestamp=s.timestamp
AND DATE(t.timestamp) > CURRENT_DATE()  -- this is the filter you should tune 
WHEN NOT MATCHED THEN
INSERT (user_id,event,engagement_score,dest_email_domain,timestamp,tags,meta)
VALUES (user_id,event,engagement_score,dest_email_domain,timestamp,tags,meta)   

因此，您需要在以下行中进行设置，以便它实际上不会过滤掉您需要参与的任何内容

AND DATE(t.timestamp) <> CURRENT_DATE()  -- this is the filter you should tune 

例如，我发现，将来将其设置为时间戳 - 在许多情况下可以解决该问题，例如

AND DATE(t.timestamp) > DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY)   

当然，如果您的 wr_live.email_event表也​​用 require partition filter 分区设置为 true - 您需要为 s.timestamp 添加相同的过滤器

Also is there a way I can express shorter the insert stuff? without naming all columns?

BigQuery DML 的 INSERT 需要 column names to be specified - 没有办法(至少我知道)使用 INSERT 语句避免它
同时，您可以通过从查询结果中使用 DDL 的 CREATE TABLE 来避免这种情况。这不需要列出列

例如，像下面这样

CREATE OR REPLACE TABLE `wr_live.p_email_event`
PARTITION BY DATE(timestamp) AS
SELECT * FROM `wr_live.p_email_event` 
WHERE DATE(timestamp) <> DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY)
UNION ALL 
SELECT * FROM `wr_live.email_event` s
WHERE NOT EXISTS (
  SELECT 1 FROM `wr_live.p_email_event` t
  WHERE t.user_id=s.user_id AND t.event=s.event AND t.timestamp=s.timestamp
  AND DATE(t.timestamp) > DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY)
)

您可能还想通过 OPTIONS() 包含表选项列表 - 但看起来过滤器属性不受支持 - 所以如果你有/需要它 - 上面将“删除”这个属性:o(