New Standard SQL Data Types Not Supported in gcloud._helpers._rows_from_json() / _CELLDATA_FROM_JSON()

[KevinTydlacka]

Standard SQL dialect support was added in 0.18.0, but current release does not support queries that return results that include data types that are not supported by Legacy SQL.

E.g. executing the following query using Standard SQL:

SELECT PARSE_DATE('%Y%m%d', '20160810') AS table_date;

results in

rows = query.rows
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/gcloud/bigquery/query.py", line 204, in rows
    return _rows_from_json(self._properties.get('rows', ()), self.schema)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/gcloud/bigquery/_helpers.py", line 85, in _rows_
from_json
    converter = _CELLDATA_FROM_JSON[field.field_type]
KeyError: u'DATE'

Not a significant issue since the Legacy SQL Data types will work for my needs, but wanted to report it all the same.

[tseaver]

@toomtoom Thanks for the report! For future reference, here are the docs for the column types returned when querying with the standard SQL dialect.

[abovethewater]

Unfortunately there is no equivalent timezone functionality in the legacy SQL syntax, so this is a bit of an issue.

For anyone else blocked by this, the query actually works, and the raw data is available via

query._properties['rows']

If there is a preferable way to access this until fixed, then please recommend.

[daspecster]

@tseaver isn't 'DATE' a valid type?
https://cloud.google.com/bigquery/sql-reference/data-types#date-type

It's listed here https://github.com/GoogleCloudPlatform/google-cloud-python/blob/master/google/cloud/bigquery/_helpers.py#L81.

[tseaver]

@abovethewater, @toomtoom, @daspecster

• PR Support parsing DATE columns from Standard SQL tables #2265 added support for the DATE type.
• We should be able to add the INT64 and FLOAT64 standard types as trivial aliases for the legacy INT and FLOAT.
• The standard BYTES type should be straightforward, if we can figure out how the back-end is expecting the values to be tunneled via JSON (I expect base64, but can't find it in the docs).
• Support for both ARRAY and STRUCT are going to require reworking the current implementation, as the field types are no longer constant strings.

[tseaver]

• Correctly model JSON repr of complex nested records. #2787 fixed the marshalling of ARRAY and STRUCT types, as well as adding the BOOL alias.
• Coerce query parameter values from native types to JSON-appropriate types #2803 fixed handling of DATE, DATETIME, TIMESTAMP fields.

AFAICT, the remaining standard SQL types which need support are BYTES (I just verified that the back-end marshals them as base64) and TIME.