如何将BigQuery表模式导出为DDL

Question

我需要创建一个与现有的BigQuery表具有相同模式的表。 在标准的MySQL中有SHOW CREATE TABLE，是否有类似于BigQuery的东西？

- Grigoryants Artem

---

我看到有两个被删除的答案，这是好事。这里发生了什么？被删除的答案才是好的。 - Pentium10

@Stéphane-Fréchette - 我明白为什么你的第一个答案被删除了 - 但是你为什么删除了你的第二个答案？在我看来，它看起来很好。 - Mikhail Berlyant

谢谢您的警惕。我已经恢复了我的回答，现在我们可以继续了！ - Stéphane Fréchette

Answer 1

SELECT
 table_name, ddl
FROM
 `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES;

https://cloud.google.com/blog/topics/developers-practitioners/spring-forward-bigquery-user-friendly-sql

- Petro Maslov

---

完美！这应该是被接受的答案。 - Kieran Benton

我本以为这会显示用于创建表的 sql，所以如果我使用 SELECT 语句生成表，它只会给我那个 SQL，但我想我错了。即使我是从 SELECT 语句生成的，一个漂亮的 DDL 语句也在等着我。 - Peter

比接受的答案好多了。 - CodeMonkey

只是添加一个澄清。@Petro Maslov的响应中的查询结构为===>SELECT table_name，ddl FROM [PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.TABLES，因此在上述查询中，“bigquery-public-data”。census_bureau_usa是[PROJECT_ID.]DATASET_ID..将其替换为您的项目和数据集ID。 - Yogesh Devi

不清楚应该替换什么（特定于您的示例），以及每次运行都是通用的。 - Alechan

请注意，有些情况下返回的DDL不能直接运行，因为在长描述字符串中间存在换行符。 - Lars Haugseth

Answer 2

虽然没有类似于MySQL中的SHOW CREATE TABLE语句，但是通过使用UDF函数可以生成数据集中表的DDL语句...

使用以下脚本，并确保将“mydataset”替换为您自己的数据集。您甚至可以添加一个WHERE谓词，以仅输出特定表的DDL

复制所需表的输出，并将其粘贴到新的Compose Query窗口中，并给它一个新的表名！

CREATE TEMP FUNCTION MakePartitionByExpression(
  column_name STRING, data_type STRING
) AS (
  IF(
    column_name = '_PARTITIONTIME',
    'DATE(_PARTITIONTIME)',
    IF(
      data_type = 'TIMESTAMP',
      CONCAT('DATE(', column_name, ')'),
      column_name
    )
  )
);

CREATE TEMP FUNCTION MakePartitionByClause(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      'PARTITION BY ',
      (SELECT MakePartitionByExpression(column_name, data_type)
       FROM UNNEST(columns) WHERE is_partitioning_column = 'YES'),
      '\n'),
    ''
  )
);

CREATE TEMP FUNCTION MakeClusterByClause(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      'CLUSTER BY ',
      (SELECT STRING_AGG(column_name, ', ' ORDER BY clustering_ordinal_position)
        FROM UNNEST(columns) WHERE clustering_ordinal_position IS NOT NULL),
      '\n'
    ),
    ''
  )
);

CREATE TEMP FUNCTION MakeNullable(data_type STRING, is_nullable STRING)
AS (
  IF(not STARTS_WITH(data_type, 'ARRAY<') and is_nullable = 'NO', ' NOT NULL', '')
);

CREATE TEMP FUNCTION MakeColumnList(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      '(\n',
      (SELECT STRING_AGG(CONCAT('  ', column_name, ' ', data_type,  MakeNullable(data_type, is_nullable)), ',\n')
       FROM UNNEST(columns)),
      '\n)\n'
    ),
    ''
  )
);

CREATE TEMP FUNCTION MakeOptionList(
  options ARRAY<STRUCT<option_name STRING, option_value STRING>>
) AS (
  IFNULL(
    CONCAT(
      'OPTIONS (\n',
      (SELECT STRING_AGG(CONCAT('  ', option_name, '=', option_value), ',\n') FROM UNNEST(options)),
      '\n)\n'),
    ''
  )
);

WITH Components AS (
  SELECT
    CONCAT('`', table_catalog, '.', table_schema, '.', table_name, '`') AS table_name,
    ARRAY_AGG(
      STRUCT(column_name, data_type, is_nullable, is_partitioning_column, clustering_ordinal_position)
      ORDER BY ordinal_position
    ) AS columns,
    (SELECT ARRAY_AGG(STRUCT(option_name, option_value))
     FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS AS t2
     WHERE t.table_name = t2.table_name) AS options
  FROM mydataset.INFORMATION_SCHEMA.TABLES AS t
  LEFT JOIN mydataset.INFORMATION_SCHEMA.COLUMNS
  USING (table_catalog, table_schema, table_name)
  WHERE table_type = 'BASE TABLE'
  GROUP BY table_catalog, table_schema, t.table_name
)
SELECT
  CONCAT(
    'CREATE OR REPLACE TABLE ',
    table_name,
    '\n',
    MakeColumnList(columns),
    MakePartitionByClause(columns),
    MakeClusterByClause(columns),
    MakeOptionList(options))
FROM Components

更多信息请查看 -> 使用INFORMATION_SCHEMA获取表元数据https://cloud.google.com/bigquery/docs/information-schema-tables

- Stéphane Fréchette

---

有一点需要提到的是，它还会计算“_PARTITIONTIME”列并将其添加到创建语句中，但是BQ不允许为用户创建这样的列。 - Grigoryants Artem

Answer 3

使用以下“技巧”可以创建具有与现有表相同模式的BigQuery表作为目标（这里的技巧在于使用WHERE FALSE，它使下面的查询在输出中具有0行的情况下免费，并保留模式）。

#standardSQL
SELECT * 
FROM `project.dataset.existing_table`
WHERE FALSE  

或者您可以在CTAS（CREATE TABLE AS SELECT）类型的DDL中使用上述语句。

- Mikhail Berlyant