load_table_from_dataframe does not error out when `nan` in a required column - Million dollar bug

[gbmarc1]

When loading a pandas dataframe into Bigquery that contains a nan in a required column, the upload succeeds but the resulting table is not representative of the dataframe. The values in the column containing the nan are unordered and the user is not aware of it.

Environment details

• OS type and version:
• Python version: Python 3.11.4
• pip version: pip 23.2.1
• google-cloud-bigquery version:
• Name: google-cloud-bigquery

Version: 3.12.0
Summary: Google BigQuery API client library
Home-page: https://github.com/googleapis/python-bigquery
Author: Google LLC
Author-email: googleapis-packages@google.com
License: Apache 2.0
Requires: google-api-core, google-cloud-core, google-resumable-media, grpcio, grpcio, packaging, proto-plus, protobuf, python-dateutil, requests
Required-by: google-cloud-aiplatform, pandas-gbq

Steps to reproduce

1. Run the code below

Code example

from google.cloud import bigquery 
import pandas as pd
import numpy as np

df = pd.DataFrame([["hello", "string"], ["hello2", np.nan], ["hello3", "valid"], ["hello4", "valid2"]], columns=["image_uri", "phash"])

client = bigquery.Client(project="project")
job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("image_uri", "STRING", mode="REQUIRED"),
        bigquery.SchemaField("phash", "STRING", mode="REQUIRED"),
    ]
)
job = client.load_table_from_dataframe(
    df, "foo.foo_bar", job_config=job_config
)
job.result()

df_read = pd.read_gbq("foo.foo_bar", project_id="project")

[Gaurang033]

seems like there is an issue with pyarrow library. Following code show the reproduction of the same bug.

    import pyarrow as pa
    import pyarrow.parquet
    import pandas as pd

    fields = [pa.field('id', pa.string(), nullable=False),
              pa.field('name', pa.string(), nullable=False)]
    array = [pa.array(['10', '11', '12', '13']),
             pa.array(['AAA', None, 'BBB', 'CCC'])]
    table = pa.Table.from_arrays(array, schema=pa.schema(fields))
    pyarrow.parquet.write_table(table, 'test_arrow.parquet', compression='SNAPPY', use_compliant_nested_type=True)
    df = pd.read_parquet("/Users/fki/Documents/git/Demo/bq_api/test_arrow.parquet", engine='pyarrow')
    print("\n\n\n")
    print(df)

Possible solution

until issue is fixed in Pyarrow. Disable the nullability for schema in pyarrow table. This will throw unhandled exception though.

_pandas_helper.py

def bq_to_arrow_field(bq_field, array_type=None):
    """Return the Arrow field, corresponding to a given BigQuery column.

    Returns:
        None: if the Arrow type cannot be determined.
    """
    arrow_type = bq_to_arrow_data_type(bq_field)
    if arrow_type is not None:
        if array_type is not None:
            arrow_type = array_type  # For GEOGRAPHY, at least initially
        # is_nullable = bq_field.mode.upper() == "NULLABLE"
        is_nullable = True

Exception if remove nullability

        if self._exception is not None:
            # pylint: disable=raising-bad-type
            # Pylint doesn't recognize that this is valid in this case.
>           raise self._exception
E           google.api_core.exceptions.BadRequest: 400 Required field phash cannot be null

[Gaurang033]

other way to fix this is to verify if data has null when column has data type as required. created the pull request for that.

[Linchin]

@Gaurang033 Thank you so much for your investigation, and proposing a solution! It looks like pyarrow did not check whether the arrays are consistent with schema when Table.from_arrays() is called, because table = pa.Table.from_arrays(array, schema=pa.schema(fields))(in your reproduction example) yields the following without an error:

   id  name
0  10   AAA
1  11  None
2  12   BBB
3  13   CCC

It feels like a major neligence on the pyarrow side. I wonder if it's a bug, or it's done by design.

At the same time, we have an umbrella issue #1646, which covers a bunch of issues related to the load_table_from_*() methods. We may need a more comprehensive look into them all, in case this patch would break other user paths.

[Gaurang033]

@Linchin totally agree. Seems like a bug from pyarrow library as I can reproduce it without using bigquery. if you think we need this patch until pyarrow fix this, let's merge this. I fixed the failing test case. when we provided unknown data type arrow field was coming as null. and so method I added was throwing exception.

[chalmerlowe]

I added a similar comment on the PR #1698:

I worry about spending too much time and energy trying to create a work around for what we all agree is a problem in pyarrow. This feels like it creates greater complexity in our code, increased fragility, and a higher maintenance burden in the long run.

Am I missing something?

I am inclined to close this issue as out of scope for us.

[tswast]

Given that arrow is intended for fast conversions, I think the correct fix for us is to stop using REQUIRED/NULLABLE from BigQuery to determine our local arrow schema. Instead, always set nullable=True locally.