google.api_core.exceptions.BadRequest: 400 Error while reading data, error message: Provided schema is not compatible with the file 'prod-scotty-c502e249-4dea-4ac0-a243-464db814bfda'. Field 'full_name' is specified as REQUIRED in provided schema which does not match NULLABLE as specified in the file.
from google.cloud import bigquery
import random
import names
import pandas
import csv
# PROJECT = <YOUR PROJECT HERE>
DATASET = "py_api_test001"
DATASET_LOCATION = "europe-west2"
DATA_TABLE = "poc_table"
# connect
client = bigquery.Client(project=PROJECT)
# dataset creation
dataset_id = "{}.{}".format(client.project, DATASET)
dataset = bigquery.Dataset(dataset_id)
dataset.location = DATASET_LOCATION
dataset = client.create_dataset(dataset) # API request
# table creation
schema = [
bigquery.SchemaField(name="full_name", field_type="STRING", mode="REQUIRED", description="Full name of individual"),
bigquery.SchemaField(name="age", field_type="INTEGER", mode="REQUIRED", description="Age of individual")
]
table_id = "{}.{}.{}".format(PROJECT, DATASET, DATA_TABLE)
table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table) # API request
# Broken bit: push data
# Create data:
random.seed(5)
ages_list = [18 + (x % 47) for x in random.sample(range(1000000),60)]
names_list = [names.get_full_name() for x in ages]
d1 = {"full_name": names_list, "age":ages_list}
df1 = pandas.DataFrame(data=d1)
# Broken:
dataset_ref = client.dataset(DATASET)
table_ref = dataset_ref.table(DATA_TABLE)
job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.PARQUET
job_config.autodetect = False
job_config.schema = schema
job = client.load_table_from_dataframe(dataframe=df1, destination=table_ref, location=DATASET_LOCATION, job_config=job_config)
job.result()
# Produces error:
# >>> job.result()
# Traceback (most recent call last):
# File "<stdin>", line 1, in <module>
# File "/home/cormac/bigpay/python/env/lib/python3.6/site-packages/google/cloud/bigquery/job.py", line 732, in result
# return super(_AsyncJob, self).result(timeout=timeout)
# File "/home/cormac/bigpay/python/env/lib/python3.6/site-packages/google/api_core/future/polling.py", line 127, in result
# raise self._exception
# google.api_core.exceptions.BadRequest: 400 Error while reading data, error message: Provided schema is not compatible with the file 'prod-scotty-c502e249-4dea-4ac0-a243-464db814bfda'. Field 'full_name' is specified as REQUIRED in provided schema which does not match NULLABLE as specified in the file.
# >>>
# ...
# But the same data works if I use a CSV file:
dataset_ref = client.dataset(DATASET)
table_ref = dataset_ref.table(DATA_TABLE)
job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1
job_config.autodetect = False
job_config.schema = schema
src_file = "test.csv"
df1.to_csv(src_file, index=False, quoting=csv.QUOTE_NONNUMERIC)
with open(src_file, "rb") as source_file:
job = client.load_table_from_file(
source_file,
table_ref,
location=DATASET_LOCATION, # Must match the destination dataset location.
job_config=job_config,
) # API request
>>> job.result()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/cormac/bigpay/python/env/lib/python3.6/site-packages/google/cloud/bigquery/job.py", line 732, in result
return super(_AsyncJob, self).result(timeout=timeout)
File "/home/cormac/bigpay/python/env/lib/python3.6/site-packages/google/api_core/future/polling.py", line 127, in result
raise self._exception
google.api_core.exceptions.BadRequest: 400 Error while reading data, error message: Provided schema is not compatible with the file 'prod-scotty-c502e249-4dea-4ac0-a243-464db814bfda'. Field 'full_name' is specified as REQUIRED in provided schema which does not match NULLABLE as specified in the file.
>>>
Environment details:
Platform: Ubuntu 18.04 (4.15.0-50-generic)
Python: 3.6.8
BigQuery version: 1.14.0
pandas version: 0.24.2
pyarrow version: 0.13.0
Steps to reproduce
..* https://packaging.python.org/guides/installing-using-pip-and-virtual-environments/
pip install <PKG>) install the following packages..* pandas (https://pypi.org/project/pandas/)
..* pyarrow (https://pypi.org/project/pyarrow/)
..* pandas-gbq (https://pypi.org/project/pandas-gbq/)
..* names (https://pypi.org/project/names/)
..* google-cloud-bigquery (https://pypi.org/project/google-cloud-bigquery/)
Create a dataset, then create a table in the dataset, using the Python API
Create some test data, as a pandas dataframe, to emplace in the table
Attempt to push the data to the table from the dataframe, observe the following error
Code
Stack Trace