计划查询和集群 [英] Scheduled queries and clustering

问题描述

在BigQuery中似乎无法安排写入时间分区的和群集的目标表(使用WRITE_TRUNCATE和分区装饰器)的查询:我们正在收到错误消息:

It does not seem to be possible to schedule Queries in BigQuery that write to time partitioned and clustered target tables (using WRITE_TRUNCATE and a partition decorator): we are getting the error message:

Invalid value: Incompatible table partitioning specification. Expects partitioning specification interval(type:day) clustering(siteId,channelId), but input partitioning specification is interval(type:day)

我不明白为什么会这样，集群规范不是表定义的一部分吗?当在已经群集的表中执行dml插入数据时，我们也不需要指定其他任何内容.还是与我们有关在计划的查询中不使用DML ?

I don't understand why this is happening, isn't the clustering specification just a part of the table definition? We also don't need to specify anything extra when performing dml inserts data in an already clustered table. Or is this related to us not using DML in the scheduled query?

编辑:预定查询结构如下:

SELECT
  reportDate,
  channelId,
  siteId,
  pageType,
  [MORE_COLUMNS_SELECT),
  SUM(timeOnPage) AS timeOnPage_agg,
  ARRAY_AGG(STRUCT( sessionId,
      [MORE_COLUMNS_NESTED)
 ) AS Details
  ----
FROM `project.dataset.viewname` 
WHERE    reportDate >= TIMESTAMP_TRUNC(TIMESTAMP_ADD(@run_time, INTERVAL -1 DAY), DAY)
     AND reportDate < TIMESTAMP_TRUNC(@run_time, DAY)
GROUP BY
    reportDate,
  channelId,
  siteId,
  pageType,
  [MORE_COLUMNS_SELECT)

我正在将查询结果写入目标表，如下所示: TARGET_TABLE_NAME $ {run_time-24h |％Y％m％d"}

I am writing the results of this query to the target table like this: TARGET_TABLE_NAME${run_time-24h|"%Y%m%d"}

该表在_PARTITIONTIME(=报告日期)上按时间分区，并在siteId，channelId

That table is time partitioned on _PARTITIONTIME (= Reportdate) and clustered on siteId, channelId

推荐答案

截至2018年10月23日，BigQuery计划的查询功能似乎不支持WRITE_TRUNCATE加载模式结合群集.

As of 23-10-2018, it seems that the BigQuery scheduled query functionality does NOT support the WRITE_TRUNCATE loading pattern in combination with clustering.

确实起作用的工作是使用DML语句写入集群目标表.

What DOES work however, is writing to a clustered target table using a DML statement.

这篇关于计划查询和集群的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！