Json schema file will not execute in BigQuery Python API

user_10027329 0
Viewed 476

I am having an issue with Bigquery Python API. Here is the stack trace when I execute my script:

Traceback (most recent call last):
  File "createTable.py", line 17, in <module>
    open_schema()
  File "createTable.py", line 12, in open_schema
    table = bigquery.Table(table_id, schema=schema)
    ...
    "Schema items must either be fields or compatible "
ValueError: Schema items must either be fields or compatible mapping representations.

The script is very simple, open a schema file and create the table:

   from google.cloud import bigquery
   # Construct a BigQuery client object.
   client = bigquery.Client()
   table_id = "project-py-290522:bq_dts.bq-test"
       
   def open_schema():
       with open("hcl-schema.json","r", encoding = "utf-8") as fName:
           schema = fName.readlines()
       
           table = bigquery.Table(table_id, schema=schema)
           print(repr(table))
           client.create_table(table)  # Make an API request.
       
   if __name__ == "__main__":
       open_schema()
       
   print("Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id))

When I execute the schema in the console and the CLI, the table is created perfectly as is. How can the console and CLI execute the create table but chokes in the API. I have searched and searched and have not found an answer. Can someone help?

Here is the schema stored in the hcl-schema.json file. I've shortened the attribut list for brevity, but otherwise intact:

    [  
      {
        "name":"user_id",
        "type":"STRING",
        "mode":"NULLABLE"
      },
      {
        "name":"msg_version",
        "type":"STRING",
        "mode":"REQUIRED"           
      },
      {
        "name":"APIStreamData",
        "type":"RECORD",
        "mode":"REQUIRED",
        "fields":
        [
          {
            "name":"msg_version",
            "type":"STRING",
            "mode":"REQUIRED"
          },
          {
            "name":"streams",
            "type":"RECORD",
            "mode":"REPEATED",
            "fields":
            [
                {
                  "name":"length",
                  "type":"STRING",
                  "mode":"REQUIRED"
                },                      
                {
                  "name":"cached",
                  "type":"STRING",
                  "mode":"NULLABLE"
                },  
              {
                "name":"track",
                "type":"RECORD",
                "mode":"REQUIRED",
                "fields":
                [
                  {
                    "name":"msg_version",
                    "type":"STRING",
                    "mode":"REQUIRED"
                  },
                  {
                    "name":"track_id",
                    "type":"STRING",
                    "mode":"REQUIRED"
                  }
                ]
              }    
    
            ]
          }
        ]
      }
    ]

Thanks

Dazed and confused

google-bigquery python json
2 Answers

You may use a dict representation of the json file, not a string as in the original question:

with open("schema.json") as json_file:
    schema_dict = json.load(json_file)
    table = bigquery.Table(table_id, schema=schema_dict)
    table = client.create_table(table)
user_28005190

The below documentation i am referring shows the schema to be specified like below in Python https://cloud.google.com/bigquery/docs/tables#creating_an_empty_table_with_a_schema_definition

schema = [bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),bigquery.SchemaField("age", "INTEGER", mode="REQUIRED")]

I tried with below JSON and Code, this works fine. I think the json you provided has RECORD inside RECORD so we need to handle accordingly

from google.cloud import bigquery
import json
# Construct a BigQuery client object.
client = bigquery.Client()
table_id = "my-project.mock_dataset.bq-test"

def open_schema():
            
    bigquerySchema = []
    bigqueryfieldSchema = []
    with open('test.json') as f:
        bigqueryColumns = json.load(f)
        print(bigqueryColumns)
        for col in bigqueryColumns:
            if col['type'] != 'RECORD':
                print(col['name'])
                bigquerySchema.append(bigquery.SchemaField(col['name'], col['type'],mode=col['mode']))
            else:
                for colfield in col['fields']:
                    bigqueryfieldSchema.append(bigquery.SchemaField(colfield['name'], colfield['type'],colfield['mode']))
                print(bigqueryfieldSchema)
                print(col['fields'])
                bigquerySchema.append(bigquery.SchemaField(col['name'], col['type'],col['mode'],'',bigqueryfieldSchema))
        print(bigquerySchema)

    table = bigquery.Table(table_id, schema=bigquerySchema)
    print(repr(table))
    client.create_table(table)  # Make an API request.
    print("Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)) 

if __name__ == "__main__":
    open_schema()

 [  
      {
        "name":"user_id",
        "type":"STRING",
        "mode":"NULLABLE"
      },
      {
        "name":"msg_version",
        "type":"STRING",
        "mode":"REQUIRED"           
      },
      {
        "name":"APIStreamData",
        "type":"RECORD",
        "mode":"REQUIRED",
        "fields":
        [
          {
            "name":"msg_version",
            "type":"STRING",
            "mode":"REQUIRED"
          },
          {
             "name":"track_id",
             "type":"STRING",
             "mode":"REQUIRED"
          }
       ]
      }
 ]
user_157812010
Related