modified schema field list
State: closed

- [x] closes #13086
- [x] tests added / passed
- [x] passes `git diff upstream/master | flake8 --diff`
- [x] whatsnew entry


Labels:
name: Compat
description: pandas objects compatability with Numpy or Python functions

Comments:
you need tests for this and an example of why you think this is broken

## [Current coverage](https://codecov.io/gh/pydata/pandas/pull/13087?src=pr) is **84.14%**

> Merging [#13087](https://codecov.io/gh/pydata/pandas/pull/13087?src=pr) into [master](https://codecov.io/gh/pydata/pandas/branch/master?src=pr) will decrease coverage by **<.01%**

``` diff
@@             master     #13087   diff @@
==========================================
  Files           138        137     -1   
  Lines         50724      50289   -435   
  Methods           0          0          
  Messages          0          0          
  Branches          0          0          
==========================================
- Hits          42726      42312   -414   
+ Misses         7998       7977    -21   
  Partials          0          0          
```

> Powered by [Codecov](https://codecov.io?src=pr). Last updated by [103f7d3...4a30557](https://codecov.io/gh/pydata/pandas/compare/103f7d31e1b850e532ed85a4b53ef222d1271c54...4a305573241f25dddbd56cd570b350b448e08e5d)

I hope [this comment](https://github.com/pydata/pandas/issues/13086#issuecomment-217319009) provides a clearer example.

no please show a run of this and the error you get with master

Thanks for your patience! When I run this...

```
df_to_append = pd.DataFrame({'date': {pd.Timestamp('2016-05-04 00:00:00', offset='D'): pd.Timestamp('2016-05-04 00:00:00')},
 'new_nih_users': {pd.Timestamp('2016-05-04 00:00:00', offset='D'): 0.0},
 'new_web_users': {pd.Timestamp('2016-05-04 00:00:00', offset='D'): 6.0},
 'returning_nih_users': {pd.Timestamp('2016-05-04 00:00:00', offset='D'): 2.0},
 'returning_web_users': {pd.Timestamp('2016-05-04 00:00:00', offset='D'): 5.0}})
df_to_append.to_gbq(dataset + '.' + table, project, if_exists='append')
```

... I get an InvalidSchema error. Traceback:

```
---------------------------------------------------------------------------
InvalidSchema                             Traceback (most recent call last)
<ipython-input-57-704709b67558> in <module>()
      8  'returning_nih_users': {pd.Timestamp('2016-05-04 00:00:00', offset='D'): 2.0},
      9  'returning_web_users': {pd.Timestamp('2016-05-04 00:00:00', offset='D'): 5.0}})
---> 10 df_to_append.to_gbq(dataset + '.' + table, project, if_exists='append')

/Library/Python/2.7/site-packages/pandas/core/frame.pyc in to_gbq(self, destination_table, project_id, chunksize, verbose, reauth, if_exists, private_key)
    896         return gbq.to_gbq(self, destination_table, project_id=project_id,
    897                           chunksize=chunksize, verbose=verbose, reauth=reauth,
--> 898                           if_exists=if_exists, private_key=private_key)
    899 
    900     @classmethod

/Library/Python/2.7/site-packages/pandas/io/gbq.pyc in to_gbq(dataframe, destination_table, project_id, chunksize, verbose, reauth, if_exists, private_key)
    715         elif if_exists == 'append':
    716             if not connector.verify_schema(dataset_id, table_id, table_schema):
--> 717                 raise InvalidSchema("Please verify that the column order, "
    718                                     "structure and data types in the "
    719                                     "DataFrame match the schema of the "

InvalidSchema: Please verify that the column order, structure and data types in the DataFrame match the schema of the destination table.
```

The generated schema of `df_to_append` is

```
{'fields': [{'name': 'date', 'type': 'TIMESTAMP'},
            {'name': 'new_nih_users', 'type': 'FLOAT'},
            {'name': 'new_web_users', 'type': 'FLOAT'},
            {'name': 'returning_nih_users', 'type': 'FLOAT'},
            {'name': 'returning_web_users', 'type': 'FLOAT'}]}
```

and the schema of the BigQuery table I want to append to is

``````
{u'fields': [{u'mode': u'NULLABLE', u'name': u'date', u'type': u'TIMESTAMP'},
             {u'mode': u'NULLABLE',
              u'name': u'new_web_users',
              u'type': u'FLOAT'},
             {u'mode': u'NULLABLE',
              u'name': u'returning_web_users',
              u'type': u'FLOAT'},
             {u'mode': u'NULLABLE',
              u'name': u'new_nih_users',
              u'type': u'FLOAT'},
             {u'mode': u'NULLABLE',
              u'name': u'returning_nih_users',
              u'type': u'FLOAT'}]}```
``````

therefore the generated schema of the dataframe does not equal the schema of the BigQuery table I want to append the dataframe to.

how is that a bug? looks like user error to me
you have to match the schema of the existing table
how did you create the original table?

> how is that a bug? looks like user error to me

The reason I think it is a bug is because the generated schema for dataframes comes from [this function](https://github.com/pydata/pandas/blob/master/pandas/io/gbq.py#L735). It only adds `name` and `type` keys for each field.

> you have to match the schema of the existing table

Yes, but currently in the `verify_schema` method, it reads the schema of the BigQuery table using a apiclient.discovery.Resource object called `service` like this:

```
self.service.tables().get(
                projectId=self.project_id,
                datasetId=dataset_id,
                tableId=table_id
            ).execute()['schema']
```

which will yield a schema just like the generated schema from my dataframe but with the extra keys `mode` and `description` for each field. Documentation for Google's BigQuery python API [here](https://google-api-client-libraries.appspot.com/documentation/bigquery/v2/python/latest/bigquery_v2.tables.html#get) shows that the schema returned has `description` and `mode` in addition to `name` and `type`.

> how did you create the original table?

From an ipython notebook -- generating it using the dictionary I provided above gives the same result.

The [documentation](http://pandas.pydata.org/pandas-docs/stable/io.html#io-bigquery-writer) says 

> The dataframe must match the destination table in column order, structure, and data types.

but it does not specify that the dataframe's columns must have the same "description" or "mode" as the BigQuery table's columns. It is possible to generate "name" and "type" keys for each column in a dataframe but not "description" or "mode".

That's why I think `verify_schema` should get the destination table's schema using the BigQuery service API and then remove "description" and "mode" from that BigQuery table schema.

Thanks for the clear explanation of the issue. I can understand how the schema verification could fail. I see that `mode` and `description` are optional fields according to the BigQuery [docs](https://cloud.google.com/bigquery/docs/reference/v2/tables). I agree with ignoring the `description` field in the schema verification. 

I don't think the `mode` field should be ignored, especially when it is set to `REPEATED`. `REPEATED` fields can be thought of as arrays of values. See [here](http://stackoverflow.com/questions/32020714/what-does-repeated-field-in-google-bigquery-mean) for more information about the `REPEATED` option. The default value for mode is `NULLABLE`. Perhaps, we only support the  `NULLABLE` and `REQUIRED` option ?

![screenshot from 2016-05-05 21 30 36](https://cloud.githubusercontent.com/assets/5184014/15061932/f6c3f65a-1308-11e6-8d04-8fe32d14132f.png)

Looks like I forgot to include `fields` as another field key to be ignored. My mistake.

I agree that the `mode` field has important information.

Instead of stripping `description`, `mode` and `fields` from the `verify_bq_schema` method, do you think it would be better to **include** `mode` and `fields` in `_generate_bq_schema` (in addition to `name` and `type`)?

I see that `generate_bq_schema` will be removed in a future version, so I don't know what the best option is.

Based on the BigQuery docs and my understanding of the implementation, pandas currently doesn't support a BigQuery schema with any of the following options:
- `mode` is set to `REPEATED` (field is an arrays of values)
- `mode` is set to `REQUIRED` (pandas doesn't check for nulls, so exception would be raised by BigQuery API if null is found)
- `type` is set to `RECORD` (nested schema defined in `fields`)

Based on the above, I would prefer the following:
- Update the pandas documentation (or schema mismatch error message) to indicate that only `mode` with default value of `NULLABLE` is supported.
- Ignore the `description` field when verifying schema
- In `generate_bq_schema`, set `mode` to the default which is `NULLABLE`
- Any other schema differences should be raised to the user until we can add support in pandas for the more complicated options.

if some of the other contributors can comment.

cc @jacobschaer
cc @sean-schaefer
cc @aaront

this would need some tests of where it should suceed (append is ok given various states of the existing table), and fail (where it should raise)

@parthea 
- [x] _Update the pandas documentation (or schema mismatch error message) to indicate that only mode with default value of NULLABLE is supported._
  I updated both the [documentation](https://github.com/pydata/pandas/pull/13087/files#diff-bb262b5e18f651a4357476b250d4b255R4332) and the schema mismatch [error message](https://github.com/pydata/pandas/compare/master...medullaskyline:fix-to_gbq#diff-c2df6f86b4e0f1aef55f5ca8fb7fc17cR723).
- [x] _Ignore the description field when verifying schema_
  I took out the description field and used a list comprehension (code [here](https://github.com/pydata/pandas/pull/13087/files#diff-c2df6f86b4e0f1aef55f5ca8fb7fc17cR480)) as @jreback asked.
- [x] _In generate_bq_schema, set mode to the default which is NULLABLE_
  Code [here](https://github.com/pydata/pandas/compare/master...medullaskyline:fix-to_gbq#diff-c2df6f86b4e0f1aef55f5ca8fb7fc17cR745) and I also updated [one of the tests](https://github.com/pydata/pandas/compare/master...medullaskyline:fix-to_gbq#diff-d9c256c51de08508e10bf5b7e5bcba75R700) to include the `mode` field.
- [x] _Any other schema differences should be raised to the user until we can add support in pandas for the more complicated options._ (No changes necessary.)

@jreback 
- [x] _this would need some tests of where it should succeed (append is ok given various states of the existing table)_  This should be covered in the preexisting test [test_upload_data_if_table_exists_append](https://github.com/medullaskyline/pandas/blob/fix-to_gbq/pandas/io/tests/test_gbq.py#L638)
- [x] _and fail (where it should raise)_ I added new tests [test_table_with_required_columns_shouldnt_append](https://github.com/pydata/pandas/compare/master...medullaskyline:fix-to_gbq#diff-d9c256c51de08508e10bf5b7e5bcba75R745) and [test_append_to_table_w_required_cols_should_raise_InvalidSchema](https://github.com/pydata/pandas/compare/master...medullaskyline:fix-to_gbq#diff-d9c256c51de08508e10bf5b7e5bcba75R368). I can't actually run the former, since I don't have the project id of the Pandas GBQ project, but the latter test passed.

I also added a couple more tests to [GBQUnitTests](https://github.com/pydata/pandas/compare/master...medullaskyline:fix-to_gbq#diff-d9c256c51de08508e10bf5b7e5bcba75R276) using the bigquery-public-data project id. They're meant to test the changes to `generate_bq_schema`. They are probably redundant since [test_generate_schema](https://github.com/medullaskyline/pandas/blob/fix-to_gbq/pandas/io/tests/test_gbq.py#L696) already exists.

Done reviewing. A few minor comments.

All gbq tests pass locally

```
tony@tonypc:~/pandas-fix-to_gbq/pandas/io/tests$ nosetests test_gbq.py -v
test_append_to_table_w_required_cols_should_raise_InvalidSchema (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_bq_table_with_required_fields_should_fail_verify_schema (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_df_with_matching_schema_should_verify_schema (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_read_gbq_with_corrupted_private_key_json_should_fail (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_read_gbq_with_empty_private_key_file_should_fail (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_read_gbq_with_empty_private_key_json_should_fail (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_read_gbq_with_invalid_private_key_json_should_fail (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_read_gbq_with_no_project_id_given_should_fail (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_read_gbq_with_private_key_json_wrong_types_should_fail (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_should_return_bigquery_booleans_as_python_booleans (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_should_return_bigquery_floats_as_python_floats (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_should_return_bigquery_integers_as_python_floats (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_should_return_bigquery_strings_as_python_strings (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_should_return_bigquery_timestamps_as_numpy_datetime (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_that_parse_data_works_properly (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_to_gbq_should_fail_if_invalid_table_name_passed (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_to_gbq_with_no_project_id_given_should_fail (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_verify_schema_should_ignore_column_description (pandas.io.tests.test_gbq.GBQUnitTests) ... ok
test_should_be_able_to_get_a_bigquery_service (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... ok
test_should_be_able_to_get_results_from_query (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... ok
test_should_be_able_to_get_schema_from_query (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... ok
test_should_be_able_to_get_valid_credentials (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... ok
test_should_be_able_to_make_a_connector (pandas.io.tests.test_gbq.TestGBQConnectorIntegration) ... ok
test_should_be_able_to_get_a_bigquery_service (pandas.io.tests.test_gbq.TestGBQConnectorServiceAccountKeyContentsIntegration) ... ok
test_should_be_able_to_get_results_from_query (pandas.io.tests.test_gbq.TestGBQConnectorServiceAccountKeyContentsIntegration) ... ok
test_should_be_able_to_get_schema_from_query (pandas.io.tests.test_gbq.TestGBQConnectorServiceAccountKeyContentsIntegration) ... ok
test_should_be_able_to_get_valid_credentials (pandas.io.tests.test_gbq.TestGBQConnectorServiceAccountKeyContentsIntegration) ... ok
test_should_be_able_to_make_a_connector (pandas.io.tests.test_gbq.TestGBQConnectorServiceAccountKeyContentsIntegration) ... ok
test_should_be_able_to_get_a_bigquery_service (pandas.io.tests.test_gbq.TestGBQConnectorServiceAccountKeyPathIntegration) ... ok
test_should_be_able_to_get_results_from_query (pandas.io.tests.test_gbq.TestGBQConnectorServiceAccountKeyPathIntegration) ... ok
test_should_be_able_to_get_schema_from_query (pandas.io.tests.test_gbq.TestGBQConnectorServiceAccountKeyPathIntegration) ... ok
test_should_be_able_to_get_valid_credentials (pandas.io.tests.test_gbq.TestGBQConnectorServiceAccountKeyPathIntegration) ... ok
test_should_be_able_to_make_a_connector (pandas.io.tests.test_gbq.TestGBQConnectorServiceAccountKeyPathIntegration) ... ok
test_bad_project_id (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_bad_table_name (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_column_order (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_column_order_plus_index (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_download_dataset_larger_than_200k_rows (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_index_column (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_malformed_query (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_arbitrary_timestamp (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_empty_strings (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_false_boolean (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_null_boolean (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_null_floats (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_null_integers (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_null_strings (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_null_timestamp (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_timestamp_unix_epoch (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_true_boolean (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_valid_floats (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_valid_integers (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_properly_handle_valid_strings (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_read_as_service_account_with_key_contents (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_should_read_as_service_account_with_key_path (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_unicode_string_conversion_and_normalization (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_zero_rows (pandas.io.tests.test_gbq.TestReadGBQIntegration) ... ok
test_create_dataset (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_create_table (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_dataset_does_not_exist (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_dataset_exists (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_delete_dataset (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_delete_table (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_generate_schema (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_google_upload_errors_should_raise_exception (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_list_dataset (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_list_table (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_list_table_zero_results (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_table_does_not_exist (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_table_with_required_columns_shouldnt_append (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_upload_data (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_upload_data_if_table_exists_append (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_upload_data_if_table_exists_fail (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_upload_data_if_table_exists_replace (pandas.io.tests.test_gbq.TestToGBQIntegration) ... ok
test_upload_data_as_service_account_with_key_contents (pandas.io.tests.test_gbq.TestToGBQIntegrationServiceAccountKeyContents) ... ok
test_upload_data_as_service_account_with_key_path (pandas.io.tests.test_gbq.TestToGBQIntegrationServiceAccountKeyPath) ... ok
pandas.io.tests.test_gbq.test_requirements ... ok
pandas.io.tests.test_gbq.test_generate_bq_schema_deprecated ... ok

----------------------------------------------------------------------
Ran 78 tests in 450.072s

OK
```

needs a squash

@jreback @parthea Thanks for all your comments! I tried to do a squash with `git rebase -i HEAD~20`. Not sure what I'm supposed to see changed in the pull request.

Should I write more tests to address the issue with codecov/patch?

@medullaskyline you need to `push -f` to force the update

you can squash via `git rebase -i origin/master` as well

@jreback Just checking to see if there's anything else I need to do.

pls squash to a single commit (an rebase on master) / some codecov things changed.

needs a whatsnew entry. pls squash.

can you rebase and show a test run

can you rebase

@medullaskyline ping

can you rebase / update?

we also have new testing for gbq, pls look at the dev docs and see how to.

@medullaskyline can you rebase / update

this was looking good, but needs updating. pls resubmit a rebased version if you can.