Skip to content

BigQuery: Cannot push pandas dataframe to bigquery table using load_table_from_dataframe() #8305

Description

@cormac-long

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

  1. Create python virtual environment as documented at

..* https://packaging.python.org/guides/installing-using-pip-and-virtual-environments/

  1. Using pip (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/)

  1. Create a dataset, then create a table in the dataset, using the Python API

  2. Create some test data, as a pandas dataframe, to emplace in the table

  3. Attempt to push the data to the table from the dataframe, observe the following error

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.
  1. Verify that the connection works: Write the data to a CSV file then try to push the CSV file to the table - this succeeds (and I can see the data in BigQuery Web UI)

Code

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

Stack Trace

>>> 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.
>>> 

Metadata

Metadata

Assignees

Labels

api: bigqueryIssues related to the BigQuery API.type: bugError or flaw in code with unintended results or allowing sub-optimal usage patterns.

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions