CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
heydy
heydy

Reputation: 303

Partitioned table BigQuery (with custom field)

I can't find any examples that show how to write a JSON for a partitioned table using a custom field. Below is an example of how to specify a table partitioned by the type "DAY", but if I, in addition, would like to partition by a specific field - how would the JSON look like? 

{
  "tableReference": {
    "projectId": "bookstore-1382",
    "datasetId": "exports",
    "tableId": "partition"
  },
  "timePartitioning": {
    "type": "DAY"
  }
}

Upvotes: 0

Views: 446

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

Take a look at the API reference. The timePartitioning object currently supports the following attributes:

  • expirationMs
  • field
  • requirePartitionFilter
  • type

I won't copy/paste all of the comments here, but this is what it says for field:

[Experimental] [Optional] If not set, the table is partitioned by pseudo column '_PARTITIONTIME'; if set, the table is partitioned by this field. The field must be a top-level TIMESTAMP or DATE field. Its mode must be NULLABLE or REQUIRED.

In your case, the payload would look like:

{
  "tableReference": {
    "projectId": "<your project>",
    "datasetId": "<your dataset>",
    "tableId": "partition"
  },
  "timePartitioning": {
    "type": "DAY",
    "field": "<date_or_timestamp_column_name>"
  }
}

Alternatively, you can issue a CREATE TABLE DDL statement using standard SQL. To give an example:

#standardSQL
CREATE TABLE `your-project.your-dataset.table`
(
  x INT64,
  event_date DATE
)
PARTITION BY event_date;

Upvotes: 2

Related Questions