安排在集群表BigQuery上的插入 [英] Scheduling insert on a clustered table BigQuery

问题描述

我有以下查询查询，我只是想每天追加数据，但这给了我一个错误:

I have the following sheduled querie, im just trying to append data every day but it is giving me the an error:

SELECT
  PARSE_DATE('%Y%m%d',event_date) AS fecha,
  indicador
FROM `TABLE_*`
WHERE _TABLE_SUFFIX BETWEEN 
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND 
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY
  event_date,indicador

错误:

不兼容的表分区规范.目标表存在分区规范间隔(类型:DAY，字段:fecha)集群(指示符)，但传输目标分区规范是interval(type:DAY，field:fecha).请在更新其中任何一个后重试目标表或传输分区规范.

Incompatible table partitioning specification. Destination table exists with partitioning specification interval(type:DAY,field:fecha) clustering(indicador), but transfer target partitioning specification is interval(type:DAY,field:fecha). Please retry after updating either the destination table or the transfer partitioning specification.

目的地表:

表类型:已分区分区依据:日期在字段上进行了分区: fecha 分区过滤器:不需要聚集者:指标

Table type: Partitioned Partitioned by: Day Partitioned on field: fecha Partition filter: Not required Clustered by: indicador

我认为聚类"存在问题，是否有办法在集群表上消除插入(追加)?

I believe there is a problem with "clustering" is there a way to shedule an insert (append) on a clustered table ?

推荐答案

我想您可能必须意识到一个事实，即Bigquery 计划查询机制尚不支持clustered-tables#clustering_partitioned_tables"rel =" nofollow noreferrer>集群表.com/bigquery/docs/scheduling-queries#write_preference"rel =" nofollow noreferrer>写入 操作，如本堆栈

I guess you might have to be aware of the fact, that Bigquery clustered tables are not yet supported by Schedule queries mechanism approaching write operations as was already discussed in this Stack thread.

作为解决方法，我建议两个选择:

As a workaround I would advice two options:

• 使用 INSERT SELECT DML 声明初始查询而不是在创建目标时指定目标表通过将写首选项字段保留为空白来安排查询，不会触发任何 WriteDisposition 函数./li>
• 我已将目标表分区字段用作以下内容中的空组成的计划查询与我类似的用例，即使定义了目标表，它也帮助我省略了与您报告的相同的问题.

• Using INSERT SELECT DML statement in the initial query instead of specifying the destination table when creating the schedule query by leaving Write preference field as a blank one, without triggering any of WriteDisposition functions.
• I've used Destination table partitioning field as empty composing scheduled query in my similar use case, even though the destination table was defined, it helped me to omit the same issue as you've reported as well.

请让我知道上述建议是否可以帮助您解决问题.

Please let me know whether above mentioned recommendations helped you to solve the issue.

这篇关于安排在集群表BigQuery上的插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！