Bigquery查询以查找表的列名

Question

我需要一个查询来在Bigquery中查找表（表元数据）的列名，就像在SQL中的以下查询：

SELECT column_name,data_type,data_length,data_precision,nullable FROM all_tab_cols where table_name ='EMP';

- user1487985

Answer 1

BigQuery现在支持信息模式。

假设您有一个名为MY_PROJECT.MY_DATASET的数据集和一个名为MY_TABLE的表格，那么您可以运行以下查询：

SELECT column_name
FROM MY_PROJECT.MY_DATASET.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'MY_TABLE'

- Lak

---

你知道为什么这个查询在我的一个数据集上需要运行3分钟以上吗？数据集中的表并不是很多，而且表也不大。但是3分钟以上的时间使得它在我的应用程序中无法使用。感谢任何帮助！ - kilgoretrout

Answer 2

是的，您可以使用INFORMATION_SCHEMA获取表的元数据。

在过去的链接中提到的示例之一是从github_repos数据集的commits表中检索INFORMATION_SCHEMA.COLUMN_FIELD_PATHS视图的元数据，您只需要：

1. 在GCP控制台中打开BigQuery Web UI。

2. 在查询编辑器框中输入以下标准SQL查询。INFORMATION_SCHEMA需要标准SQL语法。标准SQL是GCP控制台的默认语法。

 SELECT
  *
 FROM
  `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
 WHERE
  table_name="commits"
  AND column_name="author"
  OR column_name="difference"

注意：INFORMATION_SCHEMA视图名称区分大小写。

3. 点击运行。

结果应该如下所示：

  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | table_name | column_name |     field_path      |                                                                      data_type                                                                      | description |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | commits    | author      | author              | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>                                                                  | NULL        |
  | commits    | author      | author.name         | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.email        | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.time_sec     | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.tz_offset    | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.date         | TIMESTAMP                                                                                                                                           | NULL        |
  | commits    | difference  | difference          | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL        |
  | commits    | difference  | difference.old_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.new_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.old_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_repo | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_repo | STRING                                                                                                                                              | NULL        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+

- Tiago Martins Peres

---

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS正是我所需要的。谢谢你！ - timle

Answer 3

更新：现在已经可以通过使用INFORMATION SCHEMA文档以及下面的答案来检索表格元数据。

回答，大约于2012年：

目前还不能通过查询来检索表格元数据（即列名和类型），尽管这不是第一次被请求。

您需要通过查询来执行此操作吗？通过tables API可获取表格元数据。

- Craig Citro

---

谢谢Craig，tables API对我来说足以获取表格元数据。 - user1487985

我个人希望它可以作为一个查询完成的原因是因为这是我的项目之前一直在使用的方式，这样在移动所有内容的过程中就少了一个需要学习和转换的函数。 - Altimus Prime

有一个新的答案，可以通过标准SQL查询来实现，可能会给你一些想法。 - Jofre

这个答案是错误的，至少目前是这样的。请看下面正确的答案。 - Alexandru R

Answer 4

对于像我这样的新手，以上内容的语法如下：

select * from project_name.dataset_name.INFORMATION_SCHEMA.COLUMNS where table_catalog=project_name and table_schema=dataset_name and table_name=table_name

- SSS

---

我强烈建议您在问题中加上“SQL”标签。同时，看一下查询格式，使其更易于阅读。 - Marcel Wilson

Answer 5

实际上，可以使用SQL来完成此操作。要做到这一点，您需要查询记录表以获取创建此特定表的最后一个日志。

例如，假设该表每天加载/创建：

    CREATE TEMP FUNCTION jsonSchemaStringToArray(jsonSchema String)
          RETURNS ARRAY<STRING> AS ((
            SELECT
              SPLIT(
                REGEXP_REPLACE(REPLACE(LTRIM(jsonSchema,'{ '),'"fields": [',''), r'{[^{]+"name": "([^\"]+)"[^}]+}[, ]*', '\\1,')
              ,',')
          ));
    WITH valid_schema_columns AS (
      WITH array_output aS (SELECT
        jsonSchemaStringToArray(jsonSchema) AS column_names
      FROM (
        SELECT
          protoPayload.serviceData.jobInsertRequest.resource.jobConfiguration.load.schemaJson AS jsonSchema
          , ROW_NUMBER() OVER (ORDER BY metadata.timestamp DESC) AS record_count
        FROM `realself-main.bigquery_logging.cloudaudit_googleapis_com_data_access_20170101`
        WHERE
          protoPayload.serviceData.jobInsertRequest.resource.jobConfiguration.load.destinationTable.tableId = '<table_name>'
          AND
          protoPayload.serviceData.jobInsertRequest.resource.jobConfiguration.load.destinationTable.datasetId = '<schema_name>'
          AND
          protoPayload.serviceData.jobInsertRequest.resource.jobConfiguration.load.createDisposition = 'CREATE_IF_NEEDED'
      ) AS t
      WHERE
        t.record_count = 1 -- grab the latest entry
      )
      -- this is actually what UNNESTS the array into standard rows
      SELECT
        valid_column_name
      FROM array_output
      LEFT JOIN UNNEST(column_names) AS valid_column_name

    )

- mr.meer

---

我们在哪里插入我们的表名？显然，根据上面的注释，这里有一个更简单的版本，它提供了一个更短的SQL示例。 - Praxiteles

Answer 6

检查列，您可以通过CLI轻松访问您的表格，易于查找。

bq query --use_legacy_sql=false 'select Hour, sum(column 1) as column from `project_id.dataset.table_name` where Date(Hour) = '2020-06-10';'

- Kumar Pankaj Dubey