BigQuery: use union categorical to concatenate pages in to_dataframe when categorical dtype is requested

[dkapitan]

I am working on an enhancement for pandas-gbq, where we would like to reduce the memory footprint in pandas through downcasting. See googleapis/python-bigquery-pandas#275 for the full details.

I have made a first working version and getting erratic behaviour in the conversion of STRING to pandas category. It seems like it has to do with the size of the query results.

See attached notebook for the code example.
pandas-gbq-bugtracing.ipynb.zip

[tseaver]

@tswast I know you have some stuff in work related to the dtypes conversions.

[tswast]

If the size of the query results is large, then there are multiple pages to parse. Since we construct a dataframe for each page and then concatenate them, the results may not be as expected. Per: http://pandas.pydata.org/pandas-docs/stable/user_guide/categorical.html#concatenation

When two categorical series are concatenated with different types, they get converted to the "object" dtype. It seems this might be avoided with https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.api.types.union_categoricals.html

I'd love if there was a way to support this feature without having to add special logic for different dtypes.

Related: per googleapis/python-bigquery-pandas#275 (comment) there are more immediate ways that we can decrease peak memory usage. I've filed #8107 to track that issue separately.

[tswast]

I updated the issue title to reflect that this issue is specifically for the categorical dtype.

#10027 which refactors to_dataframe to use to_arrow will affect this issue. Instead of converting the dtype at each page, that PR changes it to change the dtype when converting from arrow table to pandas dataframe. This may actually fix the issue with using the categorical dtype (but not decrease peak memory usage).

I suggest we at least add tests with categorical dtypes before closing this issue.

[plamut]

FWIW, the peak memory usage can be cut down by a lot using the bqstorage API (benchmark from today).

I will have a look at the remaining scope of this issue (the dtypes).