BigQuery command line tool - append to table using query

4
votes

Is it possible to append the results of running a query to a table using the bq command line tool? I can't see flags available to specify this, and when I run it it fails and states "table already exists"

bq query --allow_large_results --destination_table=project:DATASET.table "SELECT * FROM [project:DATASET.another_table]"

BigQuery error in query operation: Error processing job '': Already Exists: Table project:DATASET.table

3
google-bigquery

3 Answers

9
votes

Originally BigQuery did not support the standard SQL idiom 

 INSERT foo SELECT a,b,c from bar where d>0;

and you had to do it their way with --append_table

But according to @Will's answer, it works now.

Originally with bq, there was

bq query --append_table ...

The help for the bq query command is 

$ bq query --help

And the output shows an append_table option in the top 25% of the output.

Python script for interacting with BigQuery.


USAGE: bq.py [--global_flags] <command> [--command_flags] [args]


query    Execute a query.

         Examples:
         bq query 'select count(*) from publicdata:samples.shakespeare'

         Usage:
         query <sql_query>

         Flags for query:

/home/paul/google-cloud-sdk/platform/bq/bq.py:
  --[no]allow_large_results: Enables larger destination table sizes.
  --[no]append_table: When a destination table is specified, whether or not to
    append.
    (default: 'false')
  --[no]batch: Whether to run the query in batch mode.
    (default: 'false')
  --destination_table: Name of destination table for query results.
    (default: '')
...

Instead of appending two tables together, you might be better off with a UNION ALL which is sql's version of concatenation.

In big query the comma or , operation between two tables as in SELECT something from tableA, tableB is a UNION ALL, NOT a JOIN, or at least it was the last time I looked.

6
votes

Just in case someone ends up finding this question in Google, BigQuery has evolved a lot since this post and now it does support Standard.

If you want to append the results of a query to a table using the DML syntax feature of the Standard version, you could do something like:

INSERT dataset.Warehouse (warehouse, state)
SELECT *
FROM UNNEST([('warehouse #1', 'WA'),
      ('warehouse #2', 'CA'),
      ('warehouse #3', 'WA')])

As presented in the docs.

For the command line tool it follows the same idea, you just need to add the flag --use_legacy_sql=False, like so:

bq query --use_legacy_sql=False "insert into dataset.table (field1, field2) select field1, field2 from table"
1
votes

According to the current documentation (March 2018): https://cloud.google.com/bigquery/docs/loading-data-local#appending_to_or_overwriting_a_table_using_a_local_file

You should add:

--noreplace or --replace=false