ENH: #8325 Add ability to create tables using the gbq module. 
State: closed

closes #8325 
closes #10652
- Added ability to automatically create a table using the gbq.to_gbq function if destination table does not exist
- Added `gbq.gbq_table_exists()` function to the gbq module 
- Added `gbq.create_gbq_table()` function to the gbq module 


Labels:
name: Enhancement
name: API Design

Comments:
- Renamed `gbq_table_exists` to `table_exists`
- Renamed `create_gbq_table` to `create_table`
- Made `project_id` a required parameter in `create_table`
- Reformatted descriptor text in `to_gbq` function into bullet points
- Removed redundant checks in test_gbq.py. eg.  `if PROJECT_ID and not missing_bq()`
- Modify `gbq_read` to support the `destination_table` argument, which allows users to redirect the query results directly to a BigQuery table instead of a pandas DataFrame
- Add `if_exists argument to`gbq_read`and`to_gbq`` to support appending, or replacing a table in GoogleBig Query based on the argument provided.
  - If destination table has a different schema and `if_exists` is set to `append`, raise `InvalidSchemaError`
  - If destination table has a different schema and `if_exists` is set to `replace`, delete and re-create destination table, wait 120 seconds before inserting data  (https://code.google.com/p/google-bigquery/issues/detail?id=191)
- Cleaned up code that does not adhere to PEP8
- Fixed issues with  `setUp` and `tearDown` in test_gbq.py

Ready for review. Ran unit tests in TestReadGBQIntegration & TestToGBQIntegration. All tests  passed.

@parthea1 did you mean to close this?

No, I didn't mean to close it. I think it closed automatically when I reverted my last commit. I thought it would be easier to revert my last commit, and submit a fresh one that takes into account all the feedback. I'm working on the new commit now. I think the PR will open again once I add a commit. 

Regarding the allowLargeResults feature (i.e. running a google big query without returning a dataframe) , do you think its better to add the following comment into #10474 and continue the discussion there?

> I have prepared 2 proposals in order to allow the user to choose to upload a Pandas Dataframe to a BigQuery table or run a SQL-Like query and have the results goto a BigQuery table. I will create a new PR once the decision is made.

My latest commit takes into account all the feedback in this PR, including: 
- Revert change to `read_gbq()`. (I will create a separate PR to support Google BigQuery allowLargeResult parameter)
- Update documentation in io.rst
- Add `delete_table()` function to the gbq module 
- Code refactoring

Ready for review. Ran unit tests in test_gbq. All tests passed. 

Let me know if there is any other work needed for this PR. I have a few new pull requests that I want to create after this one.
1. Remove dependency on bq command line tool in `test_gbq.py`
2. Add ability to create and delete datasets using `create_dataset()` and `delete_dataset()`
3. Add support for BigQuery allowLargeResults #10474 

cc @jacobschaer can you have a look?

I've updated the documentation with the required changes. Travis is green.

My latest commit takes into account all the feedback in this PR, including:
- Changes to the documentation for the gbq module in `io.rst`
- Additional error handling for http error as well as other errors that can occur while streaming data to BigQuery
- Create a sub section in `doc/source/whatsnew/v0.17.0.txt` for enhancements to the `gbq` module
- Ensure all links are formatted correctly
- Add a separate function in the `gbq module` for checking imports
- Add unit test `test_upload_data_dataset_not_found()`

Ready for review. Ran unit tests in test_gbq. All tests passed.

@parthea1 let's scale back a lot on the exception handling code. Its not tested and doesn't add value over simply re-raising. 

As I said above catching a http error in the middle of an insertion (and raising a nice miessage is fine).

Just use `ValueError` for everything (a couple of custom exceptions are ok, e.g. AuthorizationError) or whatever, but not too many. Its just confusing and not tested.

overall looks good though.

If you can make those changes we can put this in 0.17.0

@jorisvandenbossche can you have a look?

@parthea1 Looks like a nice enhancement! I put a bunch of comments, but it are just small things on the documentation formatting

I agree with @jreback that all the different custom exception classes are not needed. Just a clear error message is way more important

I've implemented all the changes. Waiting for Travis.

Travis is green.

@parthea1 couple of minor doc changes. if you could asap would be gr8. pls let me know when you push.

https://travis-ci.org/pydata/pandas/jobs/79827353

look at the skipped tests (one of the tabs at the very bottom). all of the gbq tests are being skipped because of an import issue.

can you pls fix this as well

but test at least import for me (e.g. `Successfully installed google-api-python-client-1.4.1 httplib2-0.9.1 oauth2client-1.5.0 pyasn1-0.1.8 pyasn1-modules-0.0.7 rsa-3.2 simplejson-3.8.0 uritemplate-0.6
`)

```
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_make_a_connector (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... SKIP: Cannot run integration tests without a project id
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
SKIP: Cannot run integration tests without a project id
pandas.io.tests.test_gbq.test_requirements ... ok
```

I've updated the docs with the minor changes.

I have only seen the following message when testing locally. After I enter the project Id, I no longer see the message.
`SKIP: Cannot run integration tests without a project id`

I haven't encountered the error `Could not import Google API Client` locally. 

I ran all units tests with a project Id and they all pass. 

I could look into it after 0.17.0 release

I notice that the  `apiclient` is not installed 

`apiclient: None`

```
INSTALLED VERSIONS
------------------
commit: 8174073eb24080bed4b3eb4b1d6f9283c3ba33d8
python: 2.7.10.final.0
python-bits: 64
OS: Linux
OS-release: 2.6.32-042stab108.8
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: en_US.UTF-8
LANG: en_US.UTF-8

pandas: 0.16.2+635.g8174073.dirty
nose: 1.3.7
pip: 7.1.2
setuptools: 18.1
Cython: 0.19.1
numpy: 1.7.0
scipy: 0.13.2
statsmodels: 0.5.0
IPython: 2.4.1
sphinx: 1.1.3
patsy: 0.2.1
dateutil: 1.5
pytz: 2013b
bottleneck: 0.8.0
tables: 3.0.0
numexpr: 2.2.2
matplotlib: 1.3.1
openpyxl: 1.6.2
xlrd: 0.9.2
xlwt: 0.7.5
xlsxwriter: 0.4.6
lxml: None
bs4: 4.2.1
html5lib: 1.0b2
httplib2: None
apiclient: None
sqlalchemy: 0.9.6
pymysql: 0.6.3.None
psycopg2: 2.5.2 (dt dec pq3 ext)
```

right I think we need to install it as 1.2.0

pls see what u can do

Is it ok to use the latest version of google-api-python-client `1.4.2`, instead of `1.2`?

All unit tests in `test_gbq.py` pass in my local environment with `1.4.2`.

The only change I would recommend after updating to `1.4.2` is to change `apiclient` to `googleapiclient`. `apiclient` is an alias for `googleapiclient`

see https://github.com/google/google-api-python-client/blob/master/apiclient/__init__.py

no see if u can make the original one work
not averse to adding the API stuff to snothrr build though 

I am going to merge this: https://github.com/pydata/pandas/pull/11081/files

shortly - you can just put the things (and exact versions) in the `*.pip` files

Great! I'll keep an eye out for the pip files.

While I was resolving the import error, I ran into the issue mentioned in #10656 where `LooseVersion` does not treat `'1.2'` and `'1.2.0'` as equal. I think the solution is to use `StrictVersion`. I've added the commit that fixes the version check.

Here is a comparison of `LooseVersion` and `StrictVersion`

```
>>> from distutils.version import LooseVersion
>>> a = LooseVersion('1.2')
>>> b = LooseVersion('1.2.0')
>>> a == b
False
>>> from distutils.version import StrictVersion
>>> c = StrictVersion('1.2')
>>> d = StrictVersion('1.2.0')
>>> c == d
True
```

ok gr8!

having tests that work on Travis will be a big help 

even better would be a test project id (iirc that is hard to get) - thiugh I can prob set some up

Getting all the tests working with a test project id could be done using the following steps:
- [ ] Create a google account
- [ ] Register for BigQuery, and create a ProjectId
- [ ] Set the project id in `test_gbq.py`. Run the unit test and go through the authentication process.
- [ ] Copy the file that is generated : `pandas/io/tests/bigquery_credentials.dat` 
- [ ] Place the `bigquery_credentials.dat` into `pandas/io/tests/` at the start of each build.

I have seen an advertisement from BigQuery with a 2 month(or $300) free trial.
https://cloud.google.com/free-trial/

We've tried several times to work something out with Google for this and haven't gotten anywhere. Ideally they would provide an account just for integration testing for this project - perhaps liming access to Travis IP, etc. @azbones - have we heard anything back from them?

@parthea - that's basically what we did.
1) Created BigQuery account
2) Ran gbq once to generate the credentials file
3) Put the credentials file on a server with Jenkins
4) Jenkins runs on commit, moving the credentials file to the correct place and doing a string substitution with the project ID (in the test files).

The catch was that Jenkins needed to run at least often enough to keep the token valid, but that turned out to not be too big of an issue.

@jacobschaer
That's great! So everything is in place except a free account for integration testing which may take some time. Is it possible to use a BigQuery trial in the meantime? That should buy us 2 months. At the same time, we can understand what the cost would be for a paid account (you can see how long the $300 lasts in the trial), and it will also give us a change to tell Google what we're using it for in hopes that we can get an account for integration testing. It may help open the communications with Google.

@parthea1 let's open a new issue about proper testing of this on Travis. I am working on getting some credentials.

@parthea1 update the whatsnew to show closing #10656 as well

This should close #10652