Geek Answers Handbook

BigQuery Command Line Tool - Add to a table using a query

Can I add query results to a table using the bq command line tool? I cannot see the flags available to indicate this, and when I run it, it fails and indicates that "the table already exists"

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

BigQuery error in query operation: Work with error: ': Already exists: Table project: DATASET.table

+4
source share
3 answers

BigQuery initially did not support standard SQL-idiom

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

and you had to do it with --append_table

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

Originally with bq it was

bq query --append_table ...

Help for bq query command

$ bq query --help

And the output shows the 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: '')
...

UNION ALL, sql- .

, , SELECT something from tableA, tableB, UNION ALL, NOT a JOIN, , , , .

+8

, - Google, BigQuery , .

DML- , - :

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

docs.

, --use_legacy_sql=False, :

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

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

You must add:

--noreplace or --replace=false

0
source

More articles:


All Articles