Fails to generate schema on nested json

[tpcgold]

this example json is valid but cannot be parsed to a schema:

from bigquery_schema_generator.generate_schema import SchemaGenerator
import json

test = {
	"a": "a",
	"b": "20220101",
	"c": "c",
	"values": [{
		"percentage": 3,
		"values": [{
			"a": "20220101",
			"b": 100,
		}]
	}]
}

generator = SchemaGenerator(input_format='json', infer_mode='NULLABLE')

schema_map, error_logs = generator.deduce_schema(input_data=json.dumps(test))

[bxparks]

It might seem confusing at first, but you need to remember that the data file for BigQuery is a newline-delimited JSON, not simple JSON. So the input_data expects an array (more accurately, an iterable) of JSON objects, not a simple JSON object.

So you need to change:

schema_map, error_logs = generator.deduce_schema(input_data=json.dumps(test))

to

schema_map, error_logs = generator.deduce_schema(input_data=[json.dumps(test)])

---

Slightly off topic, but maybe relevant to you, you don't need to convert a python dict, into a JSON string, which gets converted back into a python dict by SchemaGenerator. You can feed a python dict directly into SchemaGenerator using input_format='dict'. In other words

from bigquery_schema_generator.generate_schema import SchemaGenerator
import sys
import json

test = {
    "a": "a",
    "b": "20220101",
    "c": "c",
    "values": [{
        "percentage": 3,
        "values": [{
            "a": "20220101",
            "b": 100,
        }]
    }]
}

generator = SchemaGenerator(input_format='dict', infer_mode='NULLABLE')

schema_map, error_logs = generator.deduce_schema(input_data=[test])
schema = generator.flatten_schema(schema_map)
json.dump(schema, sys.stdout, indent=2)
print()

which prints

[
  {
    "mode": "NULLABLE",
    "name": "a",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "b",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "c",
    "type": "STRING"
  },
  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "percentage",
        "type": "INTEGER"
      },
      {
        "fields": [
          {
            "mode": "NULLABLE",
            "name": "a",
            "type": "INTEGER"
          },
          {
            "mode": "NULLABLE",
            "name": "b",
            "type": "INTEGER"
          }
        ],
        "mode": "REPEATED",
        "name": "values",
        "type": "RECORD"
      }
    ],
    "mode": "REPEATED",
    "name": "values",
    "type": "RECORD"
  }
]

You can see some of the fields being interpreted as INTEGER instead of STRING because of the default type inference taking place inside quoted values. You can turn that off using --quoted_values_are_strings flag or option.

[uche-madu]

This was mighty helpful. Thank you!