Feature Request: Requesting support for streaming data to bigquery

[rahul1990gupta]

to_gbq() method can only to be called at most 1000 times before it gets the quota exceeded error
https://cloud.google.com/bigquery/quotas

It would be nice to have a support for stream data to big-query where writing to big-query table is implemented by client.insert_rows method as described below in the link.

https://cloud.google.com/bigquery/streaming-data-into-bigquery#bigquery-stream-data-python

System details:
Ubuntu 14.04
pandas 0.22.0
pandas-gbq 0.7.0

[max-sixty]

Thanks for the issue @rahul1990gupta

While I think we'd take a PR for this, I don't think it's a good pattern - streaming is slower and more expensive than batch loads, and that we're only loading data from pandas means the data has already been aggregated to some extent.

I hear you re the 1000 limit, but can you find workarounds - e.g. can you aggregate your data and upload in fewer larger batches? Or if you're a heavy user of BQ, you may be able to have the limit increased if you ask Google support.

[tswast]

+1 to workarounds if at all possible. Are you creating >1000 tables per day or are these dataframes appending/replacing existing tables?

My biggest hesitation with allowing streaming as an option is that replacing a table using streaming is very flakey. We'd have to re-add logic to wait 2 minutes between deleting at table and writing rows to it when replacing a table, which I don't think is a very good user experience in the general case.

[tswast]

If you absolutely must do streaming, note that the pandas-gbq library builds on the google-cloud-bigquery library, which does have a streaming method insert_rows().

Get a list of rows from the dataframe and use the "grouper" example from itertools to collect into reasonable-sized batches. Call bqclient.insert_rows(tableref, batch) for each batch of rows.

[tswast]

Unlike to_gbq, bqclient.insert_rows does require a table to exist, so if you're creating 1000s of new tables per day, this is a little more complicated. There's an internal pandas_gbq.gbq.generate_bq_schema method, which may help with creating tables.

[tswast]

Closing this issue as out-of-scope for the pandas-gbq library.

[tswast]

@rahul1990gupta I've thought more about this feature request, and I believe it's an appropriate addition to the google-cloud-bigquery library but not pandas-gbq. I've filed googleapis/google-cloud-python#7553 to track adding a insert_rows_from_dataframe method there.