google-cloud-platform - 如何将 BigQuery 表架构导出为 DDL
问题描述
我需要创建与现有架构相同的 BigQuery 表。在标准的 MySql 中有SHOW CREATE TABLE
,BigQuery 有类似的东西吗?
解决方案
与 MySQL 中的 SHOW CREATE TABLE 没有什么相似之处,但可以使用 UDF 在数据集中生成表的 DDL 语句......
使用以下脚本并确保将“mydataset”替换为您的。您甚至可以添加 WHERE 谓词以仅输出特定表 DDL
复制所需表的输出并将其粘贴到新的 Compose 查询窗口中,并为其指定一个新的表名!
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获取表元数据
推荐阅读
- awk - 为什么 awk 中的 asort 会弄乱我数组中的一条记录?
- angular - 如何让 Creative Tim Angular Material Pro 在 JVectorMap 上渲染区域?
- python - 如果条件满足,分配标识符
- android - Mockito - RuntimeException:存根!模拟 RecyclerView.Adapter 时
- voltdb - 搜索 VoltDB ODBC 驱动程序
- html - 垂直对齐标签并选择左侧的文本
- microsoft-graph-api - Sharepoint Drive Delta 令牌不反映公共链接更改
- controller - TYPO3 Flash Message 有效但没有样式
- java - 在不同的 docker 容器上运行的 Spring Boot 和 Angular 应用程序访问相同的端口 443?
- regex - 从仅匹配正则表达式模式的字符串中提取字符