JSON schema to BigQuery schema converter

Question

I am looking for a JSON schema to Google BigQuery schema converter. I have found multiple tools on the web that generate a Google BigQuery schema from a JSON object, but nothing from a JSON schema.

Answer 1

In case anyone comes across this in future, I found a website that can do it:

https://bigquery-json-schema-generator.com/

Answer 2

My conclusion is that there is no such tool in the market, as of beginning of 2017. I came to that conclusion after long research and finally decided to build my own converting tool (specific to my project; not generic enough to be released as open source; sorry).

Answer 3

We encountered a similar issue in migrating from an ancient postgresql tables to Google BigQuery & BigStorage. GCP requires specifying a json schema file to import data. dBeaver and most SQL editors will now allow you to export data to JSON. So I created a query to format the postgresql schema so that it exports to JSON correctly.

In dBeaver pull the column data you need, then format output as text (varchar) to ensure that the value in Excel has double quotes around it. Note, column names cannot contain quotation marks, so an easy to substitute token ('xy_') is used to make it easy to copy/paste with quotations.

select
  ordinal_position
  ,column_name as xz_namexz_
  ,case data_type
    when 'character varying' then 'string'
    when 'timestamp without time zone' then 'timestamp'     --note timezone assumed to be EDT or your region
    else data_type
    end as xz_typexz_
  ,case when column_name in ('my_id','my_old_id') then 'REQUIRED'
    else 'NULLABLE'
  end as xz_modexz_
  ,case when numeric_precision is null then null
    else cast(numeric_precision as varchar)
    end as xz_precisionxz_
  ,case when character_maximum_length is null then null
    else cast(character_maximum_length as varchar)
  end as "xz_maxLengthxz_"
from information_schema.columns where table_name='my_table'

With the copy/replace that will yield the layout you need for GCP BQ.

  [
    {
       "name" : "prty_key",
      "type" : "string",
      "mode" : "NULLABLE"
    },
    {
      "name" : "hsld_key",
      "type" : "string",
      "mode" : "NULLABLE"
    }
  ]