Scheduling insert on a clustered table BigQuery

Question

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

ERROR:

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.

DESTINATION TABLE:

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 ?

Nick_Kh Nick_Kh · Accepted Answer · 2020-05-20T11:59:31

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:

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.

Scheduling insert on a clustered table BigQuery

1 Answers