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

Question

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?

Answer 1

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.

Answer 2

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