bigquery_create_sql_statement_from_json.py

"""
Python script to generate CREATE SQL statement, based on json data
Author: 4sushi
Creation date: 2021-06-30
"""

from google.cloud import bigquery
import json

# Load json data from string or file...
json_data_str = r"""
[
  {
    "glossary":{
      "title":"example glossary",
      "GlossDiv":{
        "title":"S",
        "GlossList":{
          "GlossEntry":{
            "ID":"SGML",
            "SortAs":"SGML",
            "GlossTerm":"Standard Generalized Markup Language",
            "Acronym":"SGML",
            "Abbrev":"ISO 8879:1986",
            "GlossDef":{
              "para":"A meta-markup language, used to create markup languages such as DocBook.",
              "GlossSeeAlso":[
                "GML",
                "XML"
              ]
            },
            "GlossSee":"markup"
          }
        }
      }
    }
  }
]
"""
json_data = json.loads(json_data_str)

# Create tmp big query table from json data, delete it after
project = 'project'
dataset = 'test'
table = 'tmp_table_to_remove'
client = bigquery.Client(project)
new_name_table_id = 'glossary'
table_id = f'{project}.{dataset}.{table}'
job_config = bigquery.LoadJobConfig(
    autodetect=True, source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
)
job = client.load_table_from_json(json_data, table_id, job_config=job_config)  
job.result()
job = client.query(f'select ddl from `{project}.{dataset}.INFORMATION_SCHEMA.TABLES` WHERE table_name = "{table}"')
req_create = [row.values() for row in job.result()][0][0]
req_create = req_create.replace(table_id, new_name_table_id)
# Remove tmp table
client.delete_table(table_id)

print(req_create)

# OUTPUT:
# CREATE TABLE `glossary`
# (
#  glossary STRUCT<GlossDiv STRUCT<GlossList STRUCT<GlossEntry STRUCT<GlossSee STRING, Acronym STRING, GlossTerm STRING, Abbrev STRING, GlossDef STRUCT<GlossSeeAlso ARRAY<STRING>, para STRING>, SortAs STRING, ID STRING>>, title STRING>, title STRING>
# );