How to copy a non partitioned table into an ingestion time partitioned table in bigquery using python?

user_8282569 0
Viewed 212

The use case is as follow: We have a table foo that has its data replaced every day. We want to start keeping old data in a history ingestion-time partitioned based table called foo_HIST.

I have the following code for google-cloud bigquery: 1.6.1

bq_client = bigquery.Client(project=env_conf.gcp_project_id)
dataset = bigquery.dataset.DatasetReference(
    env_conf.gcp_project_id, env_conf.bq_dataset
)

full_table_src = table_conf.table_name()
table_src = dataset.table(full_table_src)
table_dst_name = f"{full_table_src}_HIST"
table_dst = dataset.table(table_dst_name)
table_dst.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.HOUR,
)

# Truncate per partition.
job_config = bigquery.CopyJobConfig(
    create_disposition="CREATE_IF_NEEDED",
    write_disposition="WRITE_TRUNCATE",
)

job = bq_client.copy_table(table_src, table_dst, job_config=job_config)

The new table is indeed created, but when I check it with bq cli, it does not seem to be a partition based table. Here is the output.

bq show --format=prettyjson dataset_id.foo_HIST

{
  "creationTime": "1616418131814",
  "etag": "iqfdDzv2ifdsfERfwTiFjQ==",
  "id": "project_id:dataset_id.foo_HIST",
  "kind": "bigquery#table",
  "lastModifiedTime": "1616418131814",
  "location": "EU",
  "numBytes": "32333",
  "numLongTermBytes": "0",
  "numRows": "406",
  "schema": {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "MPG",
        "type": "FLOAT"
      },
    ]
  },
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/project_id/datasets/dataset_id/tables/foo_HIST",
  "tableReference": {
    "datasetId": "dataset_id",
    "projectId": "project_id",
    "tableId": "foo_HIST"
  },
  "type": "TABLE"
}
google-bigquery partitioning python
1 Answers

To anyone wondering how to copy a non partitioned table into a partitioned table (creating it if needed) in python:

It seems like CopyJob does not support this out of the box on the contrary of a QueryJob. Here is the final snippet using QueryJob:

    bq_client = bigquery.Client(project=gcp_project_id)
    dataset = bigquery.dataset.DatasetReference(
        gcp_project_id, dataset_id
    )

    table_src = dataset.table(table_name)
    table_dst_name = f"{table_name}_HIST"
    table_dst = dataset.table(table_dst_name)
    query = f"""
    SELECT *
    FROM `{project_id}`.dataset_id:table_name
    """

    job_config = bigquery.QueryJobConfig(
        create_disposition="CREATE_IF_NEEDED",
        write_disposition="WRITE_APPEND",
        time_partitioning=bigquery.TimePartitioning(
            type_=bigquery.TimePartitioningType.HOUR,
        ),
        use_legacy_sql=False,
        allow_large_results=True,
        destination=table_dst,
    )
    job = bq_client.query(query, job_config=job_config)
    job.result()  # Wait for job to finish
user_82825690
Related