BigQuery get table schema via query

4
votes

Is it possible to get BigQuery table schema info via query? (Something like TABLES but for schema). Not interested in cli approach.

What I'm trying to achieve is to perform an "incremental" update between 2 BQ tables (let`s say staging and production) with the same structure based on 1 "key" column, but with an automatic way to create the key and the rest of the hash based on all other columns.

In MySQL/mariadb would be something like. Get the columns from information_schema.columns for the specific table and string agg all columns based with COLUMN_TYPE = "PRI" and keep is as key, and then get all the others with a string agg also. This would auto generate a SQL query in order to do the rest stuff.

So the starting question is if there is something relevant to MySQL's information_schema.COLUMNS

3
google-bigquery
Short answer: it is not currently possible but you could submit a feature request on the issue tracker for information schema support. – Elliott Brossard
Fortunately it`s one of the times where your wish becomes a self fulfilling prophecy. cloud.google.com/bigquery/docs/information-schema-intro – Alex Aslanoglou

3 Answers

3
votes

Check out the Google documentation on Getting table metadata using INFORMATION_SCHEMA. INFORMATION_SCHEMA provides metadata on your BigQuery datasets.

I believe you want the following:

SELECT * 
FROM `project-id.dataset_name.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
1
votes

I can see a couple of approaches, though they differ from the MYSQL approach -

  1. If you have to use a query, just create a decorator to wrap a query with a limit 1 clause. While you will be charged the same amount as running a full table scan, it will give you the the schema in the response (see documentation of the query job). Not the best approach due the possibly charges it might incur.
  2. If you aren't tied to the query job, why not use the tables get method? It returns a table resource object which contains the schema
1
votes

UPDATE 2021

It is possible now:

SELECT 
 TO_JSON_STRING(
    ARRAY_AGG(STRUCT( 
      IF(is_nullable = 'YES', 'NULLABLE', 'REQUIRED') AS mode,
      column_name AS name,
      data_type AS type)
    ORDER BY ordinal_position), TRUE) AS schema
FROM
  <YOUR_DATASET>.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = <YOUR_TABLE>