Bigquery query to find the column names of a table

Question

I need a query to find column names of a table (table metadata) in Bigquery, like the following query in SQL:

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

Answer 1

BigQuery now supports information schema.

Suppose you have a dataset named MY_PROJECT.MY_DATASET and a table named MY_TABLE, then you can run the following query:

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

Answer 2

Yes you can get table metadata using INFORMATION_SCHEMA.

One of the examples mentioned in the past link retrieves metadata from the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view for the commits table in the github_repos dataset, you just have to

1. Open the BigQuery web UI in the GCP Console.

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

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

Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

The results should look like the following

  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+   | 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        |   +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+