`to_dataframe` fails with `Decimal type with precision 77 does not fit` when REST API is used to download extreme BIGNUMERIC values

[tswast]

Discovered in #786, but I think it's unrelated to the dtype changes I'm making there.

Steps to reproduce

1. Create a table with max / min NUMERIC values.

{"bool_col": true, "bytes_col": "abcd", "date_col": "9999-12-31", "datetime_col": "9999-12-31 23:59:59.999999", "geography_col": "POINT(-122.0838511 37.3860517)", "int64_col": "9223372036854775807", "numeric_col": "9.9999999999999999999999999999999999999E+28", "bignumeric_col": "5.7896044618658097711785492504343953926634992332820282019728792003956564819967E+38", "float64_col": "+inf", "string_col": "Hello, World", "time_col": "23:59:59.99999", "timestamp_col": "9999-12-31T23:59:59.999999Z"}
{"bool_col": false, "bytes_col": "abcd", "date_col": "0001-01-01", "datetime_col": "0001-01-01 00:00:00", "geography_col": "POINT(-122.0838511 37.3860517)", "int64_col": "-9223372036854775808", "numeric_col": "-9.9999999999999999999999999999999999999E+28", "bignumeric_col": "-5.7896044618658097711785492504343953926634992332820282019728792003956564819968E+38", "float64_col": "-inf", "string_col": "Hello, World", "time_col": "00:00:00", "timestamp_col": "0001-01-01T00:00:00.000000Z"}
{"bool_col": false, "bytes_col": "", "date_col": "1970-01-01", "datetime_col": "1970-01-01 00:00:00", "geography_col": "POINT(0 0)", "int64_col": "0", "numeric_col": "0.0", "bignumeric_col": "0.0", "float64_col": 0.0, "string_col": "", "time_col": "12:00:00", "timestamp_col": "1970-01-01T00:00:00.000000Z"}
{"bool_col": null, "bytes_col": null, "date_col": null, "datetime_col": null, "geography_col": null, "int64_col": null, "numeric_col": null, "bignumeric_col": null, "float64_col": null, "string_col": null, "time_col": null, "timestamp_col": null}

2. Download table with list_rows, but set max_results=10 so that REST API is used.
3. Call to_dataframe

Stack trace

bigquery_client = <google.cloud.bigquery.client.Client object at 0x7fcdafac9bb0>
scalars_extreme_table = 'precise-truck-742.python_bigquery_tests_system_20210723213441_aa9f85.scalars_extreme'
max_results = 10

    @pytest.mark.parametrize(
        ("max_results",), ((None,), (10,),)  # Use BQ Storage API.  # Use REST API.
    )
    def test_list_rows_nullable_scalars_extreme_dtypes(
        bigquery_client, scalars_extreme_table, max_results
    ):
>       df = bigquery_client.list_rows(
            scalars_extreme_table, max_results=max_results
        ).to_dataframe()

tests/system/test_pandas.py:828:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
google/cloud/bigquery/table.py:1903: in to_dataframe
    record_batch = self.to_arrow(
google/cloud/bigquery/table.py:1718: in to_arrow
    for record_batch in self._to_arrow_iterable(
google/cloud/bigquery/table.py:1618: in _to_page_iterable
    yield from result_pages
google/cloud/bigquery/_pandas_helpers.py:570: in download_arrow_row_iterator
    yield _row_iterator_page_to_arrow(page, column_names, arrow_types)
google/cloud/bigquery/_pandas_helpers.py:543: in _row_iterator_page_to_arrow
    arrays.append(pyarrow.array(page._columns[column_index], type=arrow_type))
pyarrow/array.pxi:306: in pyarrow.lib.array
    ???
pyarrow/array.pxi:39: in pyarrow.lib._sequence_to_array
    ???
pyarrow/error.pxi:141: in pyarrow.lib.pyarrow_internal_check_status
    ???
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

>   ???
E   pyarrow.lib.ArrowInvalid: Decimal type with precision 77 does not fit into precision inferred from first array element: 76

[tswast]

I know we have "BQ to Arrow" schema conversion logic that should get the right precision, but maybe that isn't used in the REST download path?

Aside: how does parameterized types affect that schema conversion? I assume the max precision is a bit wasteful, but should still be OK?

[tswast]

Upon further investigation, I believe this is due to pyarrow not fully supporting the "partial 77 digit precision" that BigQuery does. https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#decimal_type

I'm able to successfully convert data in the range: -9.999999999999999999999999999999999999999999999999999999999999999999999999999E+37 to 9.999999999999999999999999999999999999999999999999999999999999999999999999999E+37 (actually -1.0E+38 and 1.0E+38 work too, but not anything more than that).

[tswast]

@kmjung Is this a known issue in pyarrow? It's able to handle the Arrow buffers from the BQ Storage API with values at the "partial 77th digit precision" extremes (e.g. 5.7896044618658097711785492504343953926634992332820282019728792003956564819968E+38), but not when trying to create the pyarrow Table from the REST API results.

This raises a possible fix: always use BQ Storage API in to_arrow or to_dataframe, even if max_results is set. This seems like a pretty high overhead though if someone only wants a single page of results.

[emkornfield]

I think this works in the Read API and Arrow conversion because we never leave the binary form and we don't have a great way of checking actual precision until it is converted to/from strings. Arrow is strict about string parsing even though the value could fit into 256 bits.