BigQuery Equivalent of "CREATE TABLE my_table (LIKE your_table)"

user_1549741 0
Viewed 359

I want to create a table which schema is exactly the same as another table. In other SQL engines, I think I was able to use "CREATE TABLE my_table (LIKE your_table)" or some variations.

I couldn't find the equivalent in BigQuery yet. Is this possible in some fashion?

google-bigquery
2 Answers

Use this form:

CREATE TABLE dataset.new_table AS
SELECT *
FROM dataset.existing_table
LIMIT 0

This creates a new table with the same schema as the old one, and there is no cost due to the LIMIT 0.

Note that this does not preserve partitioning, table description, etc., however. Another option is to use the CLI (or API), making a copy of the table and then overwriting its contents, e.g.:

$ bq cp dataset.existing_table dataset.new_table
$ bq query --use_legacy_sql --replace --destination_table=dataset.new_table \
    "SELECT * FROM dataset.new_table LIMIT 0;"

Now the new table has the same structure and attributes as the original did.

user_62533470

To create a partitioned and/or clustered table the syntax would be:

CREATE TABLE project.dataset.clustered_table PARTITION BY DATE(created_time) CLUSTER BY account_id AS SELECT * FROM project.dataset.example_table LIMIT 0

user_134945900
Related