Performance

[max-sixty]

We're starting to use BigQuery heavily but becoming increasingly 'bottlenecked' with the performance of moving moderate amounts of data from BigQuery to python.

Here's a few stats:

• 29.1s: Pulling 500k rows with 3 columns of data (with cached data) using pandas-gbq
• 36.5s: Pulling the same query with google-cloud-bigquery - i.e. client.query(query)..to_dataframe()
• 2.4s: Pulling very similar data - same types, same size, from our existing MSSQL box hosted in AWS (using pd.read_sql). That's on standard drivers, nothing like turbodbc involved

...so using BigQuery with python is at least an order of magnitude slower than traditional DBs.

We've tried exporting tables to CSV on GCS and reading those in, which works fairly well for data processes, though not for exploration.

A few questions - feel free to jump in with partial replies:

• Are these results expected, or are we doing something very wrong?
• My prior is that a lot of this slowdown is caused by pulling in HTTP pages, converting to python objects, and then writing those into arrays. Is this approach really scalable? Should pandas-gbq invest resources into getting a format that's query-able in exploratory workflows that can deal with more reasonable datasets? (or at least encourage Google to)

[tswast]

For some more stats, we can try the running the code in the "benchmark" folder for google-cloud-python. https://github.com/GoogleCloudPlatform/google-cloud-python/tree/6391eeef863d0c1b71cb0c6fa86f4d9831b814fa/bigquery/benchmark That should give us a baseline.

[max-sixty]

Need to look at this more, but TensorFlow looks like they've built something that may be fast, in C: tensorflow/tensorflow@1b881b7

[tswast]

Duplicate of #66?

[max-sixty]

Yes. I've done some work here and can improve time by about 2x and stop the bloating of memory. I'll post something soon-ish. Let's leave this open for the stats above

[max-sixty]

I haven't got around to getting this is a perfect state, but here's a dump for the moment:

Requirements

In order to go from BigQuery to Pandas we, at a minimum, need to:

• Get some JSON over HTTP
• Decode the JSON into Python objects
• Create an array from the Python objects

Dos & Don'ts

Given the nature of python - where objects are expensive in both construction time and memory usage - there are some things we want to avoid:

• Create a python object for each value
• Create an additional python class instance for each row
• Construct all python objects before starting to pack to arrays

Instead we want to:

• Construct as few objects as possible
• Convert objects to arrays and deref the objects as soon as possible

Current state

But currently neither pandas-gbq nor google.cloud.bigquery follows those principles (sometimes, though not always, understandably):

• Each Row is a new python object instance (though using slots reduces the memory burden)
• All rows need to be loaded into memory before we start packing into arrays
• Without spending enough time in the google.cloud.bigquery code, at first glance each item requires a lot of python calls

Benchmark

On an approximate benchmark, both libraries take around a minute:

query="""
SELECT year, value, date FROM `bigquery-public-data.bls.unemployment_cps` 
LIMIT 1000000
"""

# I ran the query to ensure it was cached

pd.read_gbq(query, project_id='x', dialect='standard')

#Requesting query... ok.
#Job ID: c7d73162-afe2-4ebb-9f7a-b9503fd553fe
#Query running...
#Query done.
#Cache hit.

#Retrieving results...
#Got 1000000 rows.

"Total time taken 58.01 s."
# Finished at 2018-03-23 02:34:45.

c = Client()
q=c.query(query)
i=q.result()


%timeit -n 1 -r 1 i.to_dataframe()
"1 loop, best of 1: 1min 12s per loop"

Prototype

I did a quick prototype to see if reducing the python overhead would help. (these are notes from a notebook from a while ago, so may not be fully repro)

TBC, this is v hacky, and makes a bunch of assumptions that are not generalizable.

def get_lists(page):
    for d in page:
        dicts, = d.values()
        yield (x for y in dicts for x in y.values())

def dfs(i):
    i.item_to_value = lambda x,y: y
    for page in i.pages:
        df= pd.DataFrame(get_lists(page))
        # the foreknowledge is obv a cheat
        df[[0,1]] = df[[0,1]].astype(float)
        df[2] = pd.to_datetime(df[2])
        yield df

%timeit -n 1 -r 1 pd.concat(dfs(i))
"1 loop, best of 1: 35.8 s per loop"

The biggest two changes are:

• Pack each page into a df before moving onto the next page (and python will throw away the temporary objects, so you're limited by memory of the array-packed objects, rather than memory in python objects)
• Don't do excess work on each row - don't construct an object
  ...and then we're obv not doing work around type checking, so this is a rosy picture

Stepping back

I think there's some case for working through whether this is generalizable. But, for larger datasets, we'd still be fairly slow relative to downloading CSVs:

• Still constructing python objects before putting into arrays
• The specific JSON format is very inefficient - a dictionary for each row, and another for each value

And even CSVs aren't that great - everything is a string, no native formats

Options

• Stay where we are
• Roll out some of these changes (e.g. closest change is to construct an array-per-page)
• Allow larger datasets through a different path - probably exporting to a GCS file (CSV / parquet?), downloading that file, and loading that

[tswast]

Foreknowledge isn't actually as big of a cheat as you might think. Every query creates a temporary table, from which we could get the total_rows (on RowIterator, only populated after the first page) / num_rows (on the Table) and the schema. From these we can construct the dataframe ahead of time and then populate.

I'm still not sure how to get around all the JSON though. Export to GCS and download is really the only option right now. Unfortunately Parquet is supported for import to BigQuery but not export yet (I think they are working on it).

[tswast]

I think an array-per-page would be a reasonable step. We could try blasting up the page_size to see if that helps, though I think the default might give the largest possible page already.

[max-sixty]

From https://www.tidyverse.org/articles/2018/04/bigrquery-1-0-0/

Improved download speeds
The system for downloading data from BigQuery into R has been rewritten from the ground up to considerably improve performance:

By default, data is downloaded from BigQuery in pages of 10,000 rows. Previously, bigrquery downloaded then parsed each page. Now, bigrquery downloads all pages, then parses all pages. This means that you’ll now see two progress bars: one for downloading JSON from BigQuery and one for parsing that JSON into a data frame.

Because all pages are downloaded in a single pass, we can now download in parallel, using up to 6 simultaneous connections by default. This generally doesn’t result in a six-fold speed up, but should at least double download speed.

The parsing code has been rewritten in C++. This eliminates several expensive intermediate computations, and means that bigrquery no longer requires readr.

All up, I can now download the first million rows of publicdata.samples.natality in about a minute, about 8x faster than the previous version. This data frame takes up 170 MB of space in BigQuery and 140 MB of memory in R, so a minute to download doesn’t seem unreasonable. The bottleneck for loading BigQuery data is now parsing BigQuery’s JSON format, which is difficult to optimise further because I’m already using the fastest C++ JSON parser, RapidJson. If this is still too slow (because you download a lot of data), see ?bq_table_download for an alternative approach.

[max-sixty]

We just moved internally from using a CSV loader to the google.cloud.bigquery loader, and it got 3x faster:

df=pd.DataFrame(np.random.rand(1000000,2), columns=list('ab')).assign(date=pd.Timestamp('2017'), s='abc')
 %timeit -n 1 -r 1 write_gbq(df, dataset_id='ishares', table_name='test', if_exists='replace')

# new: 1 loop, best of 1: 11 s per loop
# old: 1 loop, best of 1: 32.3 s per loop

Would be good if we can move this library over to delegating down to that, too

[tswast]

Great to hear! The one difference with the google.cloud.bigquery loader that I know of is that it uploads the index column and pandas-gbq doesn't. Should be doable to "drop indexes" in pandas-gbq or add a feature to google.cloud.bigquery to do that for us.

[max-sixty]

Do you know whether it'll upload a default index? (I can look if you don't know off hand)

That would mean we need to make a change in that library if we want to maintain back-compat (and I also think forcing an index upload is bad). We could surmount this by setting a random column as the index, but I think that's going to result in a bunch of small bugs around index vs column differences.

[tswast]

Yes, I believe it does upload the default index. I couldn't figure out a good way to avoid it when using Parquet.

We probably can't always remove the index because I think we do want to upload the index when an explicit index is specified, as we do in the code sample:

https://github.com/GoogleCloudPlatform/google-cloud-python/blob/9d64d3094bea8002fce329c96c70cb6f48684633/docs/bigquery/snippets.py#L3098-L3129

[max-sixty]

Yes - I had a look at the parquet libraries and it doesn't look like excluding the indexes is an option.

I'd probably favor having parquet over optional indexes, though I'm not sure if it's worth the change. What do you think?