0

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?

mraguda
  • 35
  • 5

1 Answers1

0

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:

Note the Parameterized destination name (e.g. Parameters appended to the Destination Table Name

mraguda
  • 35
  • 5