ENH: improve bigquery connector to optionally allow use of gcloud credentials
State: closed

closes #8489

The pandas google bigquery connector can be difficult to use from an ipython notebook: when no authentication token exists, it fails silently (and can hang the notebook).  This happens because the oauth2client library is trying to bring up a browser on the console, to request an auth token.  

This PR provides an improved mechanism: an optional `use_gcloud_auth` argument which specifies that the user wants to employ a pre-existing authentication token, generated using [gcloud auth login](https://cloud.google.com/sdk/gcloud/reference/auth/login).  An error is raised if the credentials file is missing.


Labels:

Comments:
this is meant to close #8489 ?

seems very hard coded to me

how about allowing passing of the path as the credentials

first try the regular method (storage)
else if the path exists try this method ?

yes, it would resolve #8489 

The gcloud auth credentials file is not exactly in the same format as what appears in `bigquery_credentials.dat`.  But it would be quite reasonable to allow passing in of a different credentials path.  I'll make that change.

gr8

u can just put a try except around them
and then raise if neither are accepted

Changes made -- now the credentials file path can also be specified.

oauth2 will not raise an exception when it fails; it just hangs, because it's waiting for terminal input.  But folks running this from a console with a browser should still be able to use that auth mechanism first, since the gcloud auth asks for broader permissions, and is in general less desirable.  

can u attempt to get the credential first from the file then fall back to browser based? (so only have a single credentials argument)?

The problem is that there are two different files, with two different levels of credentialed authorizations.

`bigquery_credentials.dat` is created with authorization only for BigQuery.

`~/.config/gcloud/credentials` is created with broader authorization, for all google cloud services. 

The two files also have slightly different formats.  

And it's nice to have a `use_gcloud_auth` argument, which causes the correct file path to be used by default, without the pandas user needing to know about that arcania.

ok how about

passing an argument named gcloud_credentials. if it's True then u can try the default file path; if it not None then use it as the path to the file

instead of the boolean arg u have now

Done.

Commits squashed.

ok I think u can test both of those cases
(both should raise MissingbCredentials)
eg gcloud_credentials not None

huh?  What two cases.  

gcloud_credentials =None is tested now k think
so True and a path (both should raise MissingCredentials) as nothing on travis

Sorry, I'm unfamiliar with your testing framework, and don't have bw to add anything more.

ok

well if I come back to it will be here

everything has to be tested (esp this but a bit trilcky as we don't have live credentials) - otherwise bugs creep in

it works for us in our tests with live credentials.  It's going into production use in our team.

cc @jacobschaer pls have a look

I'm out of town at the moment, but I can try to look at it and test this coming weekend. 
CC @sean-schaefer @azbones 

Did some initial testing on my environment w/ live creds and it looks good. Exception raised given invalid creds path, works fine with True and specific valid path (creds file was generated through Google Cloud SDK upon running gcloud auth login).

Would be nice to see some tests for this also. E.g. mock the creds file and test get_credentials with gcloud_credentials = True or str or None, verify returned creds object. @jacobschaer may have thoughts on that as well when he returns.

@sean-schaefer if guys want to do a mock credentials object would be awesome! then we could add a few more validation tests!

@sean-schaefer I can look at this more tomorrow evening, but I am up for working on this too.

@azbones Let's touch base tomorrow to discuss.

I'm having some trouble testing this. If I install `bigquery` which, as far as I recall, was required for our automated test suite (to create/remove test tables), running the automated test suite yields:

`__main__.test_requirements ... SKIP: pandas requires google-api-python-client >= 1.2.0 for Google BigQuery support, current version 1.2`

Upgrading 'google-api-python-client' seems to improve things slightly but it breaks `bq` command line tool. For some reason I'm having permissions issues, so I can't get further.

Installed modules (with bq working):

```
Cython==0.21.1
bigquery==2.0.17
google-api-python-client==1.2
google-apputils==0.4.1
httplib2==0.9
nose==1.3.4
numpy==1.9.0
oauth2client==1.2
-e git+https://github.com/ichuang/pandas.git@36f38764db4e3d9d1a70d264122013cc680195d5#egg=pandas-master
pyasn1==0.1.7
pyasn1-modules==0.0.5
python-dateutil==2.2
python-gflags==2.0
pytz==2014.7
rsa==3.1.4
simplejson==3.6.5
six==1.8.0
uritemplate==0.6
wsgiref==0.1.2
```

I will double check my permissions and try to get automated test working.

@jreback -- Myself and @azbones added automated tests for the gcloud credentials code. To mock the credentials we took an actual creds file and put mock data in. In the tests we flip the "invalid" key in that creds file between true or false. It's up to the Google Cloud SDK to put the correct value in there.

@ichuang For the tests we added a return of the gcloud creds before checking their validity. That way the invalid tests won't fall back to the non-headless method.

seems that >1.2 google-python-api-client is now required?

Technically no, 1.2 will work. In #8587 @jacobschaer had identified that there's an issue where the import test fails with the client == 1.2, and that changing the LooseVersion check from 1.2.0 to 1.2 fixes it. I see no problem with changing it, but he had some concerns.

1.2.0/1.2 is fine. The problem was that with LooseVersions, 1.2.0 is greater than 1.2 which was preventing our automated tests from being useful. Basically they were all being skipped due to the dependency check. I don't know when that became an issue, but it would be nice to slip the fix into one of these PR's and get that off the table.

Done.

@jacobschaer looks like these tests were mostly skipped: https://travis-ci.org/pydata/pandas/jobs/41446098
maybe something in the package config not setup correctly?

I get the following:

```
[jreback-~/pandas] nosetests  pandas/io/tests/test_gbq.py -v
test_should_be_able_to_get_a_bigquery_service (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_be_able_to_get_results_from_query (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_be_able_to_get_schema_from_query (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_be_able_to_get_valid_credentials (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_be_able_to_get_valid_gcloud_credentials (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_be_able_to_make_a_connector (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_fail_if_gcloud_credentials_incorrectly_formatted (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_fail_with_invalid_gcloud_credentials (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_raise_exception_with_invalid_gcloud_creds_path (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... SKIP: Cannot run integration tests without a project id
test_bad_project_id (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_bad_table_name (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_column_order (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_column_order_plus_index (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_download_dataset_larger_than_200k_rows (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_index_column (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_malformed_query (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_arbitrary_timestamp (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_empty_strings (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_false_boolean (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_null_boolean (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_null_floats (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_null_integers (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_null_strings (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_null_timestamp (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_timestamp_unix_epoch (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_true_boolean (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_valid_floats (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_valid_integers (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_should_properly_handle_valid_strings (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_unicode_string_conversion_and_normalization (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_read_gbq_with_no_project_id_given_should_fail (pandas.io.tests.test_gbq.TestReadGBQUnitTests) ... ok
test_should_return_bigquery_booleans_as_python_booleans (pandas.io.tests.test_gbq.TestReadGBQUnitTests) ... ok
test_should_return_bigquery_floats_as_python_floats (pandas.io.tests.test_gbq.TestReadGBQUnitTests) ... ok
test_should_return_bigquery_integers_as_python_floats (pandas.io.tests.test_gbq.TestReadGBQUnitTests) ... ok
test_should_return_bigquery_strings_as_python_strings (pandas.io.tests.test_gbq.TestReadGBQUnitTests) ... ok
test_should_return_bigquery_timestamps_as_numpy_datetime (pandas.io.tests.test_gbq.TestReadGBQUnitTests) ... ok
test_that_parse_data_works_properly (pandas.io.tests.test_gbq.TestReadGBQUnitTests) ... ok
test_to_gbq_should_fail_if_invalid_table_name_passed (pandas.io.tests.test_gbq.TestReadGBQUnitTests) ... ok
test_to_gbq_with_no_project_id_given_should_fail (pandas.io.tests.test_gbq.TestReadGBQUnitTests) ... ok
test_google_upload_errors_should_raise_exception (pandas.io.tests.test_gbq.TestToGBQIntegration) ... SKIP: Cannot run integration tests without a project id
test_upload_data (pandas.io.tests.test_gbq.TestToGBQIntegration) ... SKIP: Cannot run integration tests without a project id
pandas.io.tests.test_gbq.test_requirements ... ok

----------------------------------------------------------------------
Ran 42 tests in 0.072s

OK (SKIP=32)
```