BigQuery command line tool - append to table using query

Question

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

Answer 1

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.