Insert query on Partitioned tables in Bigquery

0
votes

I have some Insert queries written in hive to be migrated in Bigquery. For example: 

insert into test.abc partition(yrmth) select * from test.xyz

In Bigquery, partition is only supported in YYYYMMDD format. I'm able to dump the data in partitioned table through BQ command line tool by loading test.abc$20171125.

How can I achieve the same using DML statements in Bigquery?

I have learnt that Legacy SQL doesn't support writing DML statements and Standard SQL doesn't support the table specifications like test.abc$20171125 that is required for loading the data in corresponding partition.

1
hivegoogle-bigquery
See issuetracker.google.com/issues/36383555 – Elliott Brossard

1 Answers

2
votes

You are correct - DML statements are not yet supported over partitioned tables.

Just do simple select select * from test.xyz with destination table test.abc$20171125. This is supported by Web UI, bq command line, API and any client of your choice

Check https://issuetracker.google.com/issues/36383555 if you want to try alpha release for column based partitioned tables - DML over partitioned tables is part of it