0

I want to move data from table t1 to another table t2 to perform renaming of columns, using option 2 from this document - https://cloud.google.com/bigquery/docs/manually-changing-schemas. Both tables are partitioned by day.

The query I used:

bq query --destination_table=mydataset.t2 --replace --use_legacy_sql=false 'SELECT * EXCEPT(column_one, column_two),
 column_one AS newcolumn_one, column_two AS newcolumn_two
FROM
 mydataset.t1'

This doesn't preserve the _PARTITIONTIME and results in all data being partitioned on one day.

Is there any way I can move _PARTITIONTIME details as well ?

dgebert
  • 1,235
  • 1
  • 13
  • 30
Deepika Juneja
  • 11
  • 2
  • Didn't try myself yet, but did you try the following flags in your bq query call? --time_partitioning_field=COLUMN_NAME --time_partitioning_type=INTERVAL – Cylldby Jun 14 '21 at 15:39
  • The destination table is partitioned by Day and not by any explicit column in the schema. I tried bq query --time_partitoning_type=DAY --time_partitioning_field=date --destination_table=t2 --replace --use_legacy_sql=false 'Select _PARTITIONTIME as date, * From t1' but this errored out : Expects partitioning specification interval(type:day) but input partitioning specification is interval(type:day,field:date) – Deepika Juneja Jun 14 '21 at 17:13
  • Can you check this and try https://stackoverflow.com/questions/47775030/big-query-specify-partitiontime-when-inserting-from-one-table-into-another – radhika sharma Jun 15 '21 at 15:19

0 Answers0