load_table_from_json interpolates string as int

[ben-marengo-msmg]

Issue

using load_table_from_json() to load data into a string column fails when using a value that can be interpolated as an int (eg. "123")

see code snippet, which shows inconsistency with the other data loading apis

Environment details

• OS type and version: macos 12.2.1
• Python version: 3.9.5 (also errors on 3.8)
• pip version: pip --version
• google-cloud-bigquery version: 2.31.0 & 3.0.1

Code example

import io
import json

from google.api_core.exceptions import BadRequest
from google.cloud.bigquery import Client, Table, SchemaField

data = [{"col_1": "123"}]

project = 'my-project'
bq = Client(project)

table = Table(f'{project}.aaa.data_load_test', schema=[SchemaField("col_1", "STRING"), ])
bq.delete_table(table, not_found_ok=True)
bq.create_table(table)


# WORKS: bq load from byte stream
with io.StringIO() as fp:
    json.dump(data, fp)
    fp.seek(0)
    bq.load_table_from_file(fp, table).result()


# WORKS: streaming api
bq.insert_rows_json(table, data)


try:
    # FAILS: bq load from python dict.
    # 123 is interpolated as an int, even though i supply it as a string
    bq.load_table_from_json(data, table).result()
except BadRequest as e:
    print("ERROR")
    print(e)


# WORKS: bq load from python dict, but 123d cannot be interpolated as an int
bq.load_table_from_json([{"col_1": "123d"}], table).result()

Stack trace

Traceback (most recent call last):
  File "/Users/ben.marengo/code/scratch/bq_load.py", line 28, in <module>
    bq.load_table_from_json(data, table).result()
  File "/Users/ben.marengo/code/pyenvs/scratch/lib/python3.9/site-packages/google/cloud/bigquery/job/base.py", line 728, in result
    return super(_AsyncJob, self).result(timeout=timeout, **kwargs)
  File "/Users/ben.marengo/code/pyenvs/scratch/lib/python3.9/site-packages/google/api_core/future/polling.py", line 137, in result
    raise self._exception
google.api_core.exceptions.BadRequest: 400 Provided Schema does not match Table my-project:aaa.data_load_test. Field col_1 has changed type from STRING to INTEGER

[tswast]

This is a tough one. One possibility would be to omit any local schema interpolation (if there is any, I forget) and use an autodetect schema, instead.

I suspect you should be able to work around this issue by manually supplying the correct schema in a job configuration object.

[tswast]

New theory: I see we don't have any client-side schema generation. Instead, the client adds autodetect = True if no schema is supplied.

python-bigquery/google/cloud/bigquery/client.py

Lines 2760 to 2761 in 66500b4

	if job_config.schema is None:
	job_config.autodetect = True

Perhaps omitting this flag when the table already exists will help?

I suspect that the backend first attempts to generate a schema from the provided data and then compares that generated schema with the existing table. If this is the case, we should also file a backend issue (possibly in addition to adding a workaround in the client if the table already exists).

[miguel-graindata]

I have been coming across the same issue when trying to load json files into BigQuery. I thought the problem was already fixed (#1248) but the issue is still around.

How can we request that it is solved? This is the code that I am using to load data.

job_config = LoadJobConfig(create_disposition='CREATE_IF_NEEDED',
                                        write_disposition='WRITE_APPEND',
                                        autodetect=True)

bq_table = f'{project_id}.{dataset_id}.{table_id}'

job = bqclient.load_table_from_json(json_data, bq_table, job_config=job_config)

[meredithslota]

#1248 was never merged, so perhaps that fix is still relevant.

Re: #1228 (comment) — if this turns out to be a backend issue we should absolutely file a bug with the correct team (and I'd propose not adding a client-side workaround since we'd have to undo it later and that might cause even more problems for folks).