BigQuery计划的数据传输会引发“不兼容的表分区规范".错误-错误消息被截断 [英] BigQuery Scheduled Data Transfer throws "Incompatible table partitioning specification." Error - but error message is truncated

问题描述

我正在使用新的BQ数据传输UI，并且在安排了数据传输后，传输失败.运行历史记录中的错误消息并不是很有用，因为错误消息似乎被截断了.

 不兼容的表分区规范.预期分区规范间隔(类型:小时)，但输入分区规范为;职位编号:xxxxxxxxxxxx 

请注意部分错误，内容为...但输入分区规范为...".分号前没有任何内容.似乎该错误已被截断.

有关运行的一些详细信息:该运行每晚从位于GCS桶中的CSV文件导入数据.成功提取后，该过程将删除该文件.BQ中的目标表是使用默认分区伪列(_PARTITIONTIME)的分区表

到目前为止我所做的:

1. 重新安排排定的数据传输-失败并引发了相同的错误
2. 删除BQ中的目标表，并使用不同的分区规范(天，小时，月)重新创建它-然后重新运行预定的传输-失败并引发相同的错误.
3. 使用BQ UI(创建表，附加特定表)手动导入数据(我从GCS下载了文件，并从我的计算机本地上传了文件)-效果很好.
4. 检查了一下这是否是Stack Overflow上的已知问题，并仅发现了此问题(现已关闭)-已关闭，但不完全是问题.(

   I'm using the new BQ Data Transfer UI and upon scheduling a Data Transfer, the transfer fails. The error message in Run History isn't terribly helpful as the error message seems truncated.

   Incompatible table partitioning specification. Expects partitioning specification interval(type:hour), but input partitioning specification is ; JobID: xxxxxxxxxxxx
   

   Note the part of the error that says..."but input partition specification is..." with nothing before the semicolon. Seems this error is truncated.

   Some details about the run: The run imports data from a CSV file located in a GCS Bucket on a nightly basis. Once successfully ingested the process will delete the file. The target table in BQ is a partitioned table using the default partition pseudo column (_PARTITIONTIME)

   What I have done so far:

   1. Reran the scheduled Data Transfer -- which failed and threw the same error
   2. Deleted the target table in BQ and recreated it with different partition specifications (day, hour, month) -- then Reran Scheduled Transfer -- failed and threw same error.
   3. Imported the data manually (I downloaded the file from GCS and uploaded it locally from my machine) using the BQ UI (Create Table, append the specific table) - Worked perfectly.
   4. Checked to see if this was a known issue here on Stack Overflow and only found this (which is now closed) -- close, but not exactly the issue. (BigQuery Data Transfer Service with BigQuery partitioned table)

   What I'm holding off doing since it would take a bit more work.

   1. Change schema of the target BQ table to include a specified column specific for partitioning
   2. Include a system-generated timestamp in the original file inside of GCS and ensure the process recognizes this as the partitioning field.

   Am I doing something wrong? Or is this a known issue?

   解决方案

   Alright, I believe I have solved this. It looks like you need to include runtime parameters into your target table if the destination table is being partitioned.

   https://cloud.google.com/bigquery-transfer/docs/gcs-transfer-parameters

   Specifically this section called "Runtime Parameter Examples" here: https://cloud.google.com/bigquery-transfer/docs/gcs-transfer-parameters#loading_a_snapshot_of_all_data_into_an_ingestion-time_partitioned_table

   They also advise that minutes cannot be specified in these parameters.

   You will need to append the parameters to your destination table details as shown below:

   这篇关于BigQuery计划的数据传输会引发“不兼容的表分区规范".错误-错误消息被截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！