bug: BigQuery backend can't handle the empty result with json type

[goldmedal]

What happened?

When using the BigQuery backend, I found the Ibis can't handle the empty result if it's a JSON type.

The following code can reproduce it:

connection = ibis.bigquery.connect(
            project_id=info.project_id,
            dataset_id=info.dataset_id,
            credentials=credentials)
sql = "select json_object('a', 1, 'b', 2) limit 0"
df = connection.sql(sql).to_pandas()
print(df)

The output is

  File "pyarrow/table.pxi", line 4858, in pyarrow.lib.Table.from_batches
ValueError: Must pass schema, or at least one RecordBatch

The detailed log is provided below.

However, if the result isn't empty, it works well.

select json_object('a', 1, 'b', 2)

By the way, I also encountered a similar issue with the INTERVAL column. However, I have no idea to provide a simple SQL to reproduce it. 🤔

What version of ibis are you using?

9.5.0

What backend(s) are you using, if any?

BigQuery

Relevant log output

/../.venv/lib/python3.11/site-packages/google/cloud/bigquery/_pandas_helpers.py:207: UserWarning: Unable to determine Arrow type for field 'f0_'.
  warnings.warn(
Traceback (most recent call last):
  File "/../tools/ibis_test.py", line 22, in <module>
    df = connection.sql(sql).to_pandas()
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/../.venv/lib/python3.11/site-packages/ibis/expr/types/relations.py", line 3453, in to_pandas
    return self.execute(**kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/../.venv/lib/python3.11/site-packages/ibis/expr/types/core.py", line 396, in execute
    return self._find_backend(use_default=True).execute(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/../.venv/lib/python3.11/site-packages/ibis/backends/bigquery/__init__.py", line 842, in execute
    df = query.to_arrow(
         ^^^^^^^^^^^^^^^
  File "/../.venv/lib/python3.11/site-packages/google/cloud/bigquery/table.py", line 1974, in to_arrow
    return pyarrow.Table.from_batches(record_batches, schema=arrow_schema)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "pyarrow/table.pxi", line 4858, in pyarrow.lib.Table.from_batches
ValueError: Must pass schema, or at least one RecordBatch

Code of Conduct

• I agree to follow this project's Code of Conduct

[goldmedal]

Related issue in the bigquery side googleapis/python-bigquery#1580

[tswast]

Thanks for the reports @goldmedal ! I'm taking a closer look at JSON on the BQ-side today. I think I have a fix, but there are a few code paths for downloading results, so I want to double check with some additional tests.

[cpcloud]

@tswast Did you get a chance to wrap this up on the BigQuery side?

[cpcloud]

Looks like this is working in Ibis now, so I'll assume the answer is yes:

In [1]: from ibis.interactive import *

In [2]: con = ibis.bigquery.connect()

In [3]: expr = con.sql("select json_object('a', 1, 'b', 2)
      ⋮ limit 0")

In [4]: expr
Out[4]:
┏━━━━━━━━━━━━━━━━━━━━━━┓
┃ f0_                  ┃
┡━━━━━━━━━━━━━━━━━━━━━━┩
│ json                 │
└──────────────────────┘