BigQuery等同于"CREATE TABLE my_table(LIKE your_table)" [英] BigQuery Equivalent of "CREATE TABLE my_table (LIKE your_table)"

问题描述

我想创建一个表，该表的架构与另一个表完全相同.在其他SQL引擎中，我认为我可以使用"CREATE TABLE my_table( Like your_table)"或某些变体.

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.

我在BigQuery中找不到对应的内容.这有可能吗?

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

推荐答案

使用以下格式:

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

这将创建一个具有与旧表相同模式的新表，并且由于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.

但是请注意，这不会保留分区，表说明等.另一种选择是使用CLI(或API)，制作表的副本，然后覆盖其内容，例如:

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.

这篇关于BigQuery等同于"CREATE TABLE my_table(LIKE your_table)"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！