BigQuery - Standard SQL - DATETIME cast issue

[VelizarVESSELINOV]

1. OS type and version

Latest Mac, not relevant

2. Python version and virtual environment information python --version

Latest Python, not relevant

3. google-cloud-python version pip show google-cloud, pip show google-<service> or pip freeze

Latest google-cloud package

4. Stacktrace if available

/usr/local/lib/python2.7/dist-packages/google/cloud/bigquery/query.pyc in fetch_data(self, max_results, page_token, start_index, timeout_ms, client)
    402             total_rows = int(total_rows)
    403         page_token = response.get('pageToken')
--> 404         rows_data = _rows_from_json(response.get('rows', ()), self.schema)
    405 
    406         return rows_data, total_rows, page_token

/usr/local/lib/python2.7/dist-packages/google/cloud/bigquery/_helpers.pyc in _rows_from_json(rows, schema)
    114 def _rows_from_json(rows, schema):
    115     """Convert JSON row data to rows with appropriate types."""
--> 116     return [_row_from_json(row, schema) for row in rows]
    117 
    118 

/usr/local/lib/python2.7/dist-packages/google/cloud/bigquery/_helpers.pyc in _row_from_json(row, schema)
    102     row_data = []
    103     for field, cell in zip(schema, row['f']):
--> 104         converter = _CELLDATA_FROM_JSON[field.field_type]
    105         if field.mode == 'REPEATED':
    106             row_data.append([converter(item, field)

KeyError: u'DATETIME'

5. Steps to reproduce
   Standard SQL query that it is working well on web UI:

SELECT CAST(STR AS DATETIME) AS DT FROM UNNEST (['2016-1-1']) AS STR;

6. Code example

    qry = bqr.run_sync_query("SELECT CAST(STR AS DATETIME) AS DT FROM UNNEST (['2016-1-1']) AS STR")
    qry.use_legacy_sql = False
    qry.run()

Very easy to fix just add DATETIME in

_CELLDATA_FROM_JSON = {
    'INTEGER': _int_from_json,
    'INT64': _int_from_json,
    'FLOAT': _float_from_json,
    'FLOAT64': _float_from_json,
    'BOOLEAN': _bool_from_json,
    'TIMESTAMP': _datetime_from_json,
    'DATE': _date_from_json,
    'RECORD': _record_from_json,
    'STRING': _string_from_json,
}

[tseaver]

A slightly easier pair of examples:

>>> from google.cloud.bigquery import Client
>>> client = Client()
>>> legacy = client.run_sync_query("SELECT USEC_TO_TIMESTAMP(NOW())")
>>> legacy.use_legacy_sql = True
>>> legacy.run()
>>> legacy.rows
[(datetime.datetime(2016, 11, 29, 4, 47, 8, 165819, tzinfo=<UTC>),)]
>>> standard = client.run_sync_query("SELECT DATETIME(2016, 11, 28, 23, 51, 22)")
>>> standard.use_legacy_sql = False
>>> standard.run()
>>> standard.rows
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File ".../google/cloud/bigquery/query.py", line 227, in rows
    return _rows_from_json(self._properties.get('rows', ()), self.schema)
  File ".../google/cloud/bigquery/_helpers.py", line 116, in _rows_from_json
    return [_row_from_json(row, schema) for row in rows]
  File ".../google/cloud/bigquery/_helpers.py", line 104, in _row_from_json
    converter = _CELLDATA_FROM_JSON[field.field_type]
KeyError: u'DATETIME'

Note that the DATETIME type is marshalled as an ISO string, not as a float at microsecond precision (which is how TIMESTAMP is marshalled):

>>> import pdb; pdb.pm()
> .../google/cloud/bigquery/_helpers.py(104)_row_from_json()
-> converter = _CELLDATA_FROM_JSON[field.field_type]
(Pdb) pp cell
{u'v': u'2016-11-28T23:51:22'}

[tseaver]

PR #2803 added support for the DATETIME field type. #2806 actually adds a system test ensuring that the values are marshalled as expected.