Can you partition on unix time (INT) in BigQuery? If so, How?

user_19576815 0
Viewed 123

Currently I'm working on a task in Airflow that loads CSV files to BigQuery where the time column is unix time (e.g., 1658371030).

The Airflow operator I'm using is GCSToBigQueryOperator where one of the params passed is schema_fields. If I define the time field in schema_fields value to be:

schema_fields = [
{"name": "UTCTimestamp", "type": "TIMESTAMP", "mode": "NULLABLE"},
....,
{"name": "OtherValue", "type": "STRING", "mode": "NULLABLE"}
]

Will BigQuery automatically detect that the unix time is INT and cast it to utc timestamp?

If it can't, how can we partition on a unix time (INT) in BigQuery?

airflow google-bigquery
1 Answers

I have tried making a table with partitioned tables using airflow, Can you try adding this parameter to your code(looking at your post UTCTimestamp is the only field applicable for partitioning):

time_partitioning={'type': 'MONTH', 'field': 'UTCTimestamp'}

For your reference type Specifies the type of time partitioning to perform and a required parameter for time portioning and field is the field name that is going to be partitioned.

Below is the dag file I have used for testing creating partitioned table.

My full code:

import os
from airflow import models
from airflow.providers.google.cloud.transfers.gcs_to_bigquery import GCSToBigQueryOperator
from airflow.utils.dates import days_ago
from datetime import datetime
dag_id = "TimeStampTry"

DATASET_NAME = os.environ.get("GCP_DATASET_NAME", '<yourDataSetName>')
TABLE_NAME = os.environ.get("GCP_TABLE_NAME", '<yourTableNameHere>')


with models.DAG(
    dag_id,
    schedule_interval=None,
    start_date=days_ago(1),
    tags=["SampleReplicate"],
) as dag:
   load_csv = GCSToBigQueryOperator(
    task_id='gcs_to_bigquery_example2',
    bucket='<yourBucketNameHere>',
    source_objects=['timestampsamp.csv'],
    destination_project_dataset_table=f"{DATASET_NAME}.{TABLE_NAME}",
    schema_fields=[
        {'name': 'Name', 'type': 'STRING', 'mode': 'NULLABLE'},
        {'name': 'date', 'type': 'TIMESTAMP', 'mode': 'NULLABLE'},
        {'name': 'Device', 'type': 'STRING', 'mode': 'NULLABLE'},
    ],
    time_partitioning={'type': 'MONTH', 'field': 'date'}
    ,
    write_disposition='WRITE_TRUNCATE',
    dag=dag,
)

timestampsamp.csv content: enter image description here

Screenshot of the table created in BQ: enter image description here enter image description here

As you can see the table type is set to partitioned.

Also please visit this article about BigQuery Rest reference for more details about the parameters and its descriptions.

user_193788260
Related