How to append data into a partitioned table in bulk, overriding ingestion time partitioning

The target table has ingestion time partitioning (PARTITION BY DATE(_PARTITIONTIME)). Logging information is flowing into this table.

I'd like to load historical logging data having the same schema and have it be partitioned based on the date portion of a specific TIMESTAMP column. I.e., I'd like to override the mechanism of the ingestion time partitioning while retaining the data type of the partition id.

When I try to load the data with bq load, bq complains that the partitioning is incompatible:

bq --project_id=myProj \
load \
        --time_partitioning_field=span.endTime \
        --time_partitioning_type=DAY \
        --source_format=PARQUET \
        myDataset.test_junk \
        gs://mybucket/test/export-*.parquet

The result:

E0419 17:06:29.304003 140704289732224 bq_utils.py:244] BigQuery error in load operation: Error processing job 'myProj:bqjob_r4bd44de633ecd32f_000001879b5714b9_1': Incompatible table partitioning specification. Expects partitioning specification interval(type:day), but input partitioning specification is
interval(type:day,field:span.endTime)

Is there any easier way to accomplish my goal than writing custom code per this stackoverflow post?

As mentioned in this document:

--time_partitioning_type: Enables time-based partitioning on a table and sets the partition type. Possible values are HOUR, DAY, MONTH, and YEAR. This flag is optional when you create a table partitioned on a DATE, DATETIME, or TIMESTAMP column. The default partition type for time-based partitioning is DAY. You cannot change the partitioning specification on an existing table.

--time_partitioning_field: The DATE or TIMESTAMP column used to create a partitioned table. If time-based partitioning is enabled without this value, an ingestion-time partitioned table is created.

For example:

The command that loads data from gs://mybucket/mydata.parquet into a new ingestion-time partitioned table named mytable in mydataset.

    bq load \
    --source_format=PARQUET \
    --time_partitioning_type=DAY \
    mydataset.mytable \
    gs://mybucket/mydata.parquet

The command that loads data from gs://mybucket/mydata.parquet into a partitioned table named mytable in mydataset. The table is partitioned on the mytimestamp column.

    bq load \
    --source_format=PARQUET \
    --time_partitioning_field mytimestamp \
    mydataset.mytable \
    gs://mybucket/mydata.parquet

For more information you can refer to this document.

You can refer to this document about the limitations of types of partitioned tables.

TL/DR: I had to use the {table}${partition} syntax to dump and load partitions individually.

I came to the conclusion that there was no way to do a single dump and a single load that would work given the constraints:

• Source and destination tables are both partitioned using ingestion time.
• Records loaded in the destination table must have the correct ingestion time, that is, the original ingestion time in the source table.
• Data must be appended to the destination table.

Additional constraints:

• Source and destination tables in different regions.
• Schema uses nested data structure that failed to be automatically loaded properly using anything other than JSON format.

I could have used a programmatic API, but I wound up writing a program to generate shell scripts to use bq, gcloud, and gsutil to dump and load all the partitions individually.

Commands wound up looking like:

# Set project for source
gcloud config set project {source_project}

# Extract partition 20220325
bq extract --location {source_region} \
--destination_format NEWLINE_DELIMITED_JSON \
--quiet=true \
--format=pretty \
--compression GZIP \
'{source_project}:{dataset}.{table}$20220325' \
gs://{source_bucket}/dump.20220325.json.gz

# ... and similar for the rest of the partitions ...

# Copy all the dumps from source to destination region
gsutil cp -r gs://{source_bucket} gs://{destination_bucket}

# Set project for destination
gcloud config set project {destination_project}

# Load partition (20220325) into destination table
bq load --location={destination_region} \
--source_format=NEWLINE_DELIMITED_JSON \
--quiet=true \
--format=pretty \
--autodetect=false \
'{destination_project}:{dataset}.{table}$20220325' \
gs://{destination_bucket}/dump.20220325.json.gz

# ... and similar for the rest of the partition dumps ...

I was surprised to run into problems using Parquet: the loads would fail with "Provided Schema does not match Table". It may have been possible to use some flag to get a perfect schema match, but since JSON worked as shown above, I wasn't motivated to dig too deeply.