有没有一种方法可以将 BigQuery 表的模式导出为 JSON？

Question

BigQuery数据表具有模式，可以在Web UI中查看更新，也可以将其用作JSON文件使用bq工具加载数据。但是，我无法找到一种从现有表格中将此模式转储到JSON文件（最好是从命令行）的方法。这可能吗？

- Daniel Waechter

Answer 1

有没有一种方法可以从现有表格中将模式转储到JSON文件中（最好是从命令行）。这种可能吗？

请尝试以下方法：

bq show bigquery-public-data:samples.wikipedia  

您可以使用“--format”标志来美化输出。

--format：none | json | prettyjson | csv | sparse | pretty：

命令输出的格式。选项包括：

none:       ...
pretty:     formatted table output  
sparse:     simpler table output  
prettyjson: easy-to-read JSON format  
json:       maximally compact JSON  
csv:        csv format with header   

前三个格式是为了人类可读性，后三个格式是为了传递给其他程序。如果没有选择格式，则根据运行的命令选择一个格式。

我意识到我提供了部分答案 :o)

下面是PO想要的结果

bq show --format=prettyjson bigquery-public-data:samples.wikipedia | jq '.schema.fields' 

- Mikhail Berlyant

---

我建议直接在Google Cloud SDK Shell中探索bq命令。只需从bq --help开始... :o) - Mikhail Berlyant

为了后人，这个命令做了我想要的事情：bq show --format=prettyjson bigquery-public-data:samples.wikipedia | jq '.schema.fields' - Daniel Waechter

有没有办法将输出转发到文本文件中？我的模式定义对终端来说太大了。 - flowoo

请在结尾处加上"> yourfile.json"，不需要引号。 - fpopic

在Windows中，我发现需要使用引号"而不是撇号'，因此命令如下：bq show --format=prettyjson bigquery-public-data:samples.wikipedia | jq ".schema.fields"。 - philshem

Answer 2

您可以添加标志--schema[1]来避免表格数据信息。

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE] > [SCHEMA_FILE]

bq show --schema --format=prettyjson myprojectid:mydataset.mytable > /tmp/myschema.json

[1] 管理表模式的方法

- bsmarcosj

---

太好了！看起来这是在我提出这个问题几个月后添加的，云SDK版本为165。比依赖于jq要好得多。 - Daniel Waechter

Answer 3

1. 在bq用户界面中选择表。
2. 选择要导出模式的列。
3. 使用复制菜单将模式复制为JSON。

- Anthony Awuley

Answer 4

答案更新

自2020年10月起， 您还可以在INFORMATION_SCHEMA.COLUMNS上运行SQL查询，这是一种自我反思的功能。

SELECT *
FROM <YOUR_DATASET>.INFORMATION_SCHEMA.COLUMNS

并使用聚合函数嵌套数据，例如

SELECT table_name, ARRAY_AGG(STRUCT(column_name, data_type)) as columns
FROM <YOUR_DATASET>.INFORMATION_SCHEMA.COLUMNS
GROUP BY table_name

如果您需要从视图中获取源代码，INFORMATION_SCHEMA.VIEWS 中还有一些有趣的元数据。

然后在BigQuery界面上点击保存结果/JSON，或在您的情况下将其包装到bq query命令行中。

来源：BigQuery发布说明

- Michel Hua

---

我觉得这应该是2021年的最佳解决方案。 - ASomN

这很完美 - 只需要特别添加 SELECT ddl 列，并带有所需的 table_name WHERE 子句。 - MandisaW

Answer 5

您可以使用REST API调用以JSON格式获取BigQuery表模式。文档链接: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables/get

curl 'https://bigquery.googleapis.com/bigquery/v2/projects/project-name/datasets/dataset-name/tables/table-name' \
     --header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
     --header 'Accept: application/json' \
     --compressed

- Soumendra Mishra

---

谢谢您提供这个信息 - 我正在寻找API版本。有没有一种方法可以不使用HTTP调用来完成这个操作？像这样的函数是否就是API中所有更“本地”外观的函数在内部看起来都是这样的呢？（我的意思是普通函数不是HTTP调用）。我只是不希望它变得很慢，而且“调用”似乎很慢。 - makmak

通常情况下，当我加载数据等操作时，我不需要考虑身份验证等问题。@Soumendra Mishra - makmak

Answer 6

截至2022年5月15日，以下步骤是有效的：

1. 进入Google Cloud，进入Cloud Shell
2. 从Cloud Shell左侧的下拉菜单中选择项目
3. 使用以下命令： bq show --schema --format=prettyjson .

请注意保留HTML标签。

- Shagoon Sood

Answer 7

以下的 Bash 脚本和 SQL 语句总是帮助我从数据集中提取所有表模式到 JSON 文件中解决问题：

#!/bin/bash
#gen-default-schema.sh
input=$1
source_type=$2
result=tables_${source_type}.result

bq query --format=csv --use_legacy_sql=false --flagfile=$input | awk '{if(NR>1)print}' > $result

while IFS= read -r line
do
    tbl_name=`echo "$line" | awk -F. '{print $NF}'`
    schema_file=`echo "$tbl_name" | cut -d'_' -f2-`.schema
    echo $schema_file
    bq show --schema --format=prettyjson $line > ./temp/${source_type}/${schema_file}
    echo "done"
done < "$result"

输入文件 example.sql ($1)

SELECT
  table_catalog || ":" || table_schema || "." || table_name
FROM (
  SELECT
    table_catalog,
    table_schema,
    table_name
  FROM
    `project-id`.<dataset_id>.INFORMATION_SCHEMA.TABLES
  ORDER BY
    table_name ASC )

运行：

$bash gen-default-schema.sh example.sql example

这将把所有的JSON模式放在./temp文件夹下。

- Logan

Answer 8

如果您想从Google的cloud-console中进行此操作，那么一个简短的SQL查询就可以实现。

它将为您提供来自模式的所有信息，并且您可以根据需要使用https://cloud.google.com/bigquery/docs/information-schema-column-field-paths#schema更改STRUCT( ... )。

或者，使用INFORMATION_SCHEMA.<something>与其他视图一起获取不同的元信息到JSON。

正如@Michel Hua在他们的答案中所说，选择Query results -> JSON在bigquery中运行SQL查询后获取JSON。

SELECT table_name, ARRAY_AGG(STRUCT(column_name, data_type, description)) as columns
FROM `your-project-id`.your_dataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 
WHERE table_name = 'your_table_name' 
GROUP BY table_name

- eemilk