Incompatible types while writing JSON to BigQuery table

Question

I have a function that recieves JSON data from API server and upload it to BigQuery table with specified schema. JSON data has some fields that should be STRING but contain only digits. Here is a sample of json data:

{
"realizationreport_id":5200242,
"suppliercontract_code":null,
"rrd_id":4681869162,
"gi_id":3246258,
"subject_name":"\xd0\x9a\xd0\xb0\xd0\xbb\xd1\x8c\xd1\x81\xd0\xbe\xd0\xbd\xd1\x8b",
"nm_id":44185221,          <--- serialized to INTEGER but should be STRING
"barcode":"2010739265043"  <--- serialized to INTEGER but should be STRING
........
}

Table Schema:

      bigquery.SchemaField("realizationreport_id", "INTEGER", mode="NULLABLE"),
      bigquery.SchemaField("suppliercontract_code", "STRING", mode="NULLABLE"),
      bigquery.SchemaField("rrd_id", "INTEGER", mode="REQUIRED"),
      bigquery.SchemaField("gi_id", "INTEGER", mode="NULLABLE"),
      bigquery.SchemaField("subject_name", "STRING", mode="NULLABLE"),
      bigquery.SchemaField("nm_id", "STRING", mode="NULLABLE"), #integer
      bigquery.SchemaField("barcode", "STRING", mode="NULLABLE"),#integer
      ...................

Uploading to Table

job_config = bigquery.LoadJobConfig(
                schema = wb_options.get_schema(),                
                autodetect = False
                )

loadJob = client.load_table_from_json(json_result, table, job_config=job_config)  
loadJob.result()

When JSON starts dump data it meets INTEGER value for the STRING table field

JSON parsing error in row starting at position 0: Could not convert value to string. Field: nm_id; Value: 44185221

How can I change json decode function for a particular field like nm_id to be STRING?

Should I do it somehow by adding to metadata map {default : CustomDecode} for json.dumps(metadata).encode("utf-8").

Or there is needed functionality in bigquery.LoadJobConfig?

user_16929894 · Answer

From the LoadJobConfig reference of the BigQuery Python API, I could not find any functionality that would do exactly what you needed. The decimal_target_types was interesting, however it could not be used while also providing a table schema in the request as I tested. It worked correctly as expected when manually setting the JSON integer to strings using the documentation guide, fitting the table schema. I think the way to go is to edit the JSON decode function to automatically convert the relevant properties to strings, as this other thread explores.

Incompatible types while writing JSON to BigQuery table

1 Answers