Can you create a temporary table as the destination table in BigQuery? - Stack Overflow

admin管理员组

文章数量:1122832

In BigQuery, you can use the bq command line tool to run queries from the command line. However, large queries must have a destination table, and can't directly dump results.

Is there a way to specify a temporary table to be created as the destination table? Attempting to use a non-prefixed table name, which is how temporary tables are normally specified, doesn't work:

bq query  --nouse_legacy_sql --session_id=$sid --format=prettyjson --destination_table tempout  'select 1'

BigQuery error in query operation: Invalid value tempout for destination_table: Cannot determine table described by tempout

The official docs have an example using a regular table but not one with a temporary table. It seems temporary tables (like in sessions) cannot be used this way, but it isn't explicit.

In BigQuery, you can use the bq command line tool to run queries from the command line. However, large queries must have a destination table, and can't directly dump results.

Is there a way to specify a temporary table to be created as the destination table? Attempting to use a non-prefixed table name, which is how temporary tables are normally specified, doesn't work:

bq query  --nouse_legacy_sql --session_id=$sid --format=prettyjson --destination_table tempout  'select 1'

BigQuery error in query operation: Invalid value tempout for destination_table: Cannot determine table described by tempout

The official docs have an example using a regular table but not one with a temporary table. It seems temporary tables (like in sessions) cannot be used this way, but it isn't explicit.

• google-bigquery

Share Improve this question Follow asked Nov 21, 2024 at 11:22 polm23polm23 15.6k88 gold badges3737 silver badges6363 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

You can use a dataset with a default table expiration, so all tables will act as temporary tables:

bq mk --location=US --dataset --default_table_expiration=3600 project:temp_dataset

Another option is update the expiration of the created table:

bq query  --nouse_legacy_sql --session_id=$sid --format=prettyjson --destination_table project:dataset.tempout 'select 1'
bq update --expiration 3600 project:dataset.tempout

have you tried running the command by calling the table with the full name? your_project_id.temp_dataset.tempout. It would look like this:

bq query --nouse_legacy_sql --session_id=$sid --format=prettyjson \
  --destination_table your_project_id.temp_dataset.tempout ‘SELECT 1

Hope this helps!

本文标签： Can you create a temporary table as the destination table in BigQueryStack Overflow