ENH: Google BigQuery IO Module
State: closed

This module adds Google BigQuery functionality to the pandas project. The work is based on the existing sql.py and ga.py, providing methods to query a BigQuery database and read the results into a pandas DataFrame.


Labels:

Comments:
Also, is there any way to test this?

needs docs too (release, whatsnew, io)

@cpcloud FYI ...need ga docs too 

since I don't use these, what exactly does this do?

me neither, i created an issue a while ago to make ga docs...i guess low-prio for now.

bigquery is a relational db engine used for querying gigantic datasets. this looks like it puts a pandas wrapper around [the google bigquery query language](https://developers.google.com/bigquery/docs/query-reference)

i would say that based on the experience with the historical finance APIs that we should be very careful about network-ish APIs, insofar as they are difficult to test reliably. just my 2c

We have test cases, but they're rather specific to our own usage. The problem is that the tests require our credentials, as well as having known datasets to compare results with. There are sample datasets provided by Google, though you'll still need an account.

Thanks for the other suggestions, we'll work on these and update when we finish.

We updated the script with some of your error handling suggestions. Could you be more specific about what you need for documentation? 

A usage example in here: http://pandas.pydata.org/pandas-docs/dev/io.html#data-reader (or prob create another section to include ga as well). can you code-block to show how to do it (which doesn't actually execute)

also a blurb (in enhancements) for release notes (doc/source/release.rst)

Unfortunately, we did not include use cases for ga as well because we are not familiar with it, but we did write documentation for gbq in the io.rst and release.rst files. We also refactored the original script to improve ease of testing and committed a test suite that we've been using. There are several test cases that do require BigQuery credentials, so they will be skipped unless you hardcode those values into the script. The other tests use CSVs that we've included in the appropriate data directory. 

Please let us know any other suggestions / requirements you have. 

Does anyone have more thoughts on this? During testing, we've noticed there are occasionally problems with OSX using Google's Python API (in particular the OpenSSL/PyCrypto modules) - we're investigating, since this will also affect the GA module, but it seems this may be out of our control. Otherwise, it's been fairly stable.

When using our gbq module internally, we found that there are OpenSSL issues across platforms using the authentication method we employed. Although it works well on my local Ubuntu 12.04 system, we've had difficulty getting it to work on Snow Leopard and Windows 7. 

There is another form of authentication through oauth2client, but we rejected that option because it cannot run headless as we plan on running this on an EC2 instance (users are required to grant access to the Google API through a browser window that pops-up during execution). However, for use through this library, do you feel it would be better to authenticate in that manner and make it more suitable cross-platform? This is a question we are currently considering for internal use as well.  

Although not spelled out in our various dev docs, the established norm in pandas is to
avoid placing author names in source code. This applies to everyone from wesm on down. 

Contributors large and small so far have accepted  `git log/blame` as suitable credit, and
this can be hereby elevated to the level of a project dictum IMO.

I (and I believe other pandai) feel very strongly about religiously maintaining attribution and giving 
proper credit in OSS projects. The accepted form for this is an acknowledgment in the release notes
by name or GH handle. We often do this unbidden when new contributors are involved to show 
our gratitude for their contribution to the project. 

So, please remove the Authors line from the code and feel free to add a "thanks to @sean-schaefer, Jacob Schaer"
note to the appropriate item in RELEASE.rst file.

As far as whether to use OpenSSL or oauth2, I would vote for oauth2.  The ga.py submodule uses this currently, many users are using Pandas in iPython, and it is significantly easier to install given it doesn't have all the platform specific OpenSSL dependencies.  I spent quite a bit of time trying to make this work during testing and it was quite the hassle when using OpenSSL.

Also, I will volunteer to put the documentation together.  I'll just need to research how to do that as I've never contributed documentation before... 

The test file you added is pretty large. Can you trim it down in size? 

Docs on the other python libraries needed to use this would be great!

We made a few changes as suggested by @cpcloud; thank you for the feedback. We're not sure where you want documentation on library dependencies, but we did add dependencies as a note in io.rst.  It should be noted that there is presently a bug in BigQuery that is preventing our module from being 100% reliable, see:

http://stackoverflow.com/questions/17751944/google-bigquery-incomplete-query-replies-on-odd-attempts/

We are planning to implement pagination for the results so that datasets can be much larger and responses more reliable.

is `httplib2` kind of like the `request` library?

these deps need go in: http://pandas.pydata.org/pandas-docs/dev/install.html#optional-dependencies

and you should also have tests that skip if these are not installed

Yes, `httplib2` is Google's extension of the original `httplib` library. I imagine `request` could do the same thing, but the examples and recommendations with the BigQuery API was to use `httplib2`. 

We added the third party libraries to the `install.rst`, and added the import skip tests.

you might already have this, but on the call to the main api, `read_gbq`? need to raise with the deps if you don't find them. generally try the imports at the top of the module and then set a variable to indicate if they are successful, but don't raise (as the api files import your main module), only raise when the user tries to use

see `core/expressions.py` for an example of doing this (this actually falls back to using diffferent features, but its the same idea)

basically a user will try out your function and be like, hey I need these deps....(rather than having pandas auto install them or failing when pandas is imported, after all they are not required)

this needs to be hooked up to travis, see contributing.md
and add your deps to the ci/requirements-2.7 and 3.2 builds (e.g. the `httplib2` and `google-python-api`)

this way it will run your tests (and skip on the other builds), to at least let us know that things are working

and need to rebase on current master as well, see here: https://github.com/pydata/pandas/wiki/Tips-&-Tricks

thanks

nice i didn't know about that `nosetests -x` thing that is cool

@sean-schaefer BigQuery also provides a data to upload a csv/json I believe? worth including something like that here? (pandas is not directly involved, though I suspect that `to_csv` might need a couple of specific options to conform, e.g. must be `UTF-8` and so on. so might be nice to just have the functionaility here)

@jreback BigQuery does allow you to upload a CSV or JSON, and we've already been using that functionality for some ETL processes. I will say their CSV requirements are very strict, as are their table loading limitations. Agreed that it would be helpful to have that in our module as well; next week I'll evaluate how to modify what we're currently using for pandas general use. 

that sounds great!
BQ does look interesting!

@azbones I believe we've added all of the necessary documentation, but would certainly appreciate a review of <code>io.rst</code>, <code>release.rst</code> and <code>install.rst</code>. I know you're quite familiar with the usage of <code>gbq.py</code> now, so we welcome your doc critique as an end user.  

Does anyone have advice on how to approach testing the load CSV into BigQuery? I can't use a local database, obviously. We could create a BigQuery mock, which provides an appropriate response back to any given input, but I question how effective that would be in proving our loading works on the real thing. 

can you create a real google account (for say pandas-dev) somehow that can have actual credentials and then can upload/download stuff?

is there a BigQuery dev/test environment? Testing like this must be pretty common? (couuld also try asking google for a dev account?)

@jreback Prior to allowing API access to BQ, you are required to provide legitimate billing info so if you go over the free tier quotas Google can charge you. We've been in discussion with a BQ engineer at Google, we'll run the dev account request by him. Thanks for the suggestion. 

great....

what about `select count(*) from table`?

We could use <code>select count(*) from table</code> to get the total rows of the table being queried, but I'm not sure how helpful that would be for queries other than <code>select \* from table</code>, and even in that case the results could be cached and return almost instantly. Querying all 4 million rows in a table may take 60 seconds, but if a limit was specified in the query it could only take a couple. 

It's worth noting though that if your queries are this big, you run the risk of hitting one of the many other limitations within BigQuery: in particular, intermediate tables (required for groupings and joins) may not exceed 7MB; result sets may not exceed 128MB, and you may even get one of Google's lovely generic "reply too large" errors. For the datasets we've worked with, query time hasn't been a problem.