CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
urbando
urbando

Reputation: 333

Is it possible to remove a column from a partitioned table in Google BigQuery?

I'm trying to remove a column from a partitioned table in BigQuery using this command

bq query --destination_table [DATASET].[TABLE_NAME] --replace --use_legacy_sql=false 'SELECT * EXCEPT(column) FROM [DATASET].[TABLE_NAME]'

As a result the unwanted column is removed, the schema is changed but the data is no more partitioned.

Any suggestion on how to keep the data partitioned after the column is removed? Docs are clear only for non partitioned tables.

Upvotes: 0

Views: 2080

Answers (2)

Patrick Atoon
Patrick Atoon

Reputation: 769

BigQuery now supports DROP COLUMN in partitioned tables:

ALTER TABLE mydataset.mytable
DROP COLUMN column

It's in beta at the time of writing, but it worked for me.

Upvotes: 2

Daria
Daria

Reputation: 606

There are two workarounds you can use:

  1. Use a column-partitioning table, which means it's partitioned on a value of a regular column in a table. You can create a new column-partitioned table and copy the data deleting the column:

    bq mk --time_partitioning_field=pt --schema=... [DATASET].[TABLE_NAME2]

    bq query --destination_table=[DATASET].[TABLE_NAME2] "SELECT _PARTITIONTIME as pt, * EXCEPT(column) from [DATASET].[TABLE_NAME]"

  2. You can also still use day-partitioned tables, but copy the data using DML. You can set or copy _PARTITIONTIME column inside the DML INSERT statement, which is not possible with regular SELECT. Here is an example:

    INSERT INTO dataset1.table1 (_partitiontime, a, b) SELECT TIMESTAMP(DATE "2008-12-25") AS _partitiontime, "a" AS a, "b" AS b

This requires DML over partitioned tables, which is currently in alpha: https://issuetracker.google.com/issues/36383555

Upvotes: 3

Related Questions