Support INTERVAL data type

[tswast]

"BigQuery now supports the INTERVAL type, which represents a duration or an amount of time. This type is in Preview."

https://cloud.google.com/bigquery/docs/release-notes#July_27_2021

Name	Range
INTERVAL	-10000-0 -3660000 -87840000:0:0 to 10000-0 3660000 87840000:0:0

An INTERVAL object represents duration or amount of time. Interval is composed of three independent parts:

• [sign]Y-M: Years and Months
• [sign]D: Days
• [sign]H:M:S.F: Hours, Minutes, Seconds and Subseconds.

Canonical format

[sign]Y-M [sign]D [sign]H:M:S[.F]

• Y: Year
• M: Month
• D: Day
• H: Hour
• M: Minute
• S: Second
• [.F]: Up to six fractional digits (microsecond precision)

TODO:

• Add relevant enums for data types
• Row data is converted to relevant type in RowIterator (list_rows()) feat: add support for INTERVAL data type to list_rows #840
• Convert data type in query parameters
• Covert data type in insert_rows

Edit: Removed pandas, arrow, db-api support in favor of #836, as those implementations are currently blocked on Arrow and the BQ Storage API.

[tswast]

Full pandas / arrow support may have to wait. Getting an error from the BQ Storage API when I added

  {
    "mode": "NULLABLE",
    "name": "interval_col",
    "type": "INTERVAL"
  }

to the scalars_schema.json file, and the test value "interval_col": "P7Y11M9DT4H15M37.123456S" (ISO 8601, which is used as the canonical format for JSON)

[tswast]

Query parameter support doesn't appear to be implemented yet in the backend. I've filed internal issue 195050789.

[tswast]

insert_rows works, but only with "canonical" format. The "canonical" format is a pain to work with since I'll have to have a bunch of client-side logic to detect if years/months are different signs and recalculate. Likewise for hours/minutes/seconds/microseconds.

I've filed internal issue 195051077 to support ISO 8601 Duration.

[tswast]

Issue 195051077 is now resolved. Both insert_rows and query parameters are now supported (or will be once the changes roll out, hopefully in December)

[tboddyspargo]

Hi, @tswast - would you be able to summarize the status of this request? Are there outstanding blockers or is it just a question of prioritization? My team would love to be able to support INTERVAL with GBQ (using pandas-gbq or bigquery-sqlalchemy), but I'm hesitant to pursue a potentially complicated custom workaround if there might be a more official solution in the works. Thanks in advance!

[tswast]

@tboddyspargo Last I checked, the BQ Storage API now sends back data of this type https://arrow.apache.org/docs/python/generated/pyarrow.month_day_nano_interval.html which accounts for the calendar-based intervals that BigQuery supports.

Easiest thing for pandas-gbq and this package to do would be to wrap that type in a pandas ArrowDtype. Would love to see a PR for that at some point. Main tricky part is we need to transform the response from the REST API into that same arrow type. I believe that requires updating the type mappings here: https://github.com/googleapis/python-bigquery/blob/main/google/cloud/bigquery/_pyarrow_helpers.py and the types mappers here: https://github.com/googleapis/python-bigquery/blob/main/google/cloud/bigquery/_pandas_helpers.py

For context: pandas is making a lot of progress making the ArrowDtype type act like other timestamp/timedelta operations, but I'm not aware of any work in pandas to support month_day_nano_interval, which most closely resembles https://pandas.pydata.org/docs/reference/api/pandas.tseries.offsets.DateOffset.html

[tswast]

For the write side, we might need some backend changes. I'm not seeing INTERVAL listed in the parquet data types here: https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-parquet#parquet_conversions

[kiraksi]

The work to be done to get closer to interval support here:

• PR 1

• Delete _row_iterator_to_dataframe and download_dataframe_row_iterator, as these functions will be blocking interval support

• Instead use and refactor RowIterator.to_dataframe / to_dataframe_iterable to make it shared helper code to do the work of converting to dataframes

• PR 2

• Add type support by adding interval to pyarrow_helpers scalars

• Add data support by adding parsing logic to transform BigQuery Interval data to pyarrow.month_day_nano for use in our library. Possibly will need to do an additional transformation by transforming the arrow data to pandas.ArrowDType

I plan to complete this work in 3 weeks before the end of my internship, if I am unable to then this work can be taken up by anyone else interested in adding this support.