Support (non-legacy) SQL nested data types

[dhermes]

STRUCT<t> and ARRAY<t> seem to be the types.

See #2342. h/t to @c0b for filing.

---

From #2585: Sample app that reproduces this

Stacktrace:

Traceback (most recent call last):
  File "simple_app.py", line 57, in <module>
    query_shakespeare()
  File "simple_app.py", line 47, in query_shakespeare
    page_token=page_token)
  File "lib/python2.7/site-packages/google/cloud/bigquery/query.py", line 401, in fetch_data
    rows_data = _rows_from_json(response.get('rows', ()), self.schema)
  File "lib/python2.7/site-packages/google/cloud/bigquery/_helpers.py", line 98, in _rows_from_json
    for item in cell['v']])
  File "lib/python2.7/site-packages/google/cloud/bigquery/_helpers.py", line 61, in _record_from_json
    for subfield, cell in zip(field.fields, value['f']):
KeyError: 'f'

---

Stepping into the debugger:

ipdb> value
{u'v': {u'f': [{u'v': u'hamlet'}, {u'v': u'5318'}]}}
ipdb> field.__dict__
{'field_type': u'RECORD', 'description': None, 'name': u'title', 'fields': [<google.cloud.bigquery.schema.SchemaField object at 0x7f4ba9227550>, <google.cloud.bigquery.schema.SchemaField object at 0x7f4ba9227690>], 'mode': u'REPEATED'}

(Added by @dhermes)

[dhermes]

A simple statement works fine

>>> from google.cloud import bigquery
>>> client = bigquery.Client()
>>> query = client.run_sync_query('SELECT 1')
>>> query.use_legacy_sql = False
>>> query.run()
>>> query.rows
[(1,)]

Sending a query with nested types also works fine

In [1]: from google.cloud import bigquery

In [2]: client = bigquery.Client()

In [3]: sql_str = (
   ...:     'SELECT ARRAY['
   ...:     'STRUCT("zx83" AS id, 1 AS a, 2 AS b), '
   ...:     '("f8f7", 4, 7)'
   ...:     '] '
   ...:     'd')

In [4]: query = client.run_sync_query(sql_str)

In [5]: query.use_legacy_sql = False

In [6]: query.run()

In [7]: query.__dict__
Out[7]:
{'_client': <google.cloud.bigquery.client.Client at 0x7fb7cf39a290>,
 '_configuration': <google.cloud.bigquery.query._SyncQueryConfiguration at 0x7fb7cf3b0fd0>,
 '_job': None,
 '_properties': {u'cacheHit': False,
  u'jobComplete': True,
  u'jobReference': {u'jobId': u'job_ruoSHLwjeg_lSfYZq8dmrjQ0Eg4',
   u'projectId': u'precise-truck-742'},
  u'kind': u'bigquery#queryResponse',
  u'rows': [{u'f': [{u'v': [{u'v': {u'f': [{u'v': u'zx83'},
          {u'v': u'1'},
          {u'v': u'2'}]}},
       {u'v': {u'f': [{u'v': u'f8f7'}, {u'v': u'4'}, {u'v': u'7'}]}}]}]}],
  u'schema': {u'fields': [{u'fields': [{u'mode': u'NULLABLE',
       u'name': u'id',
       u'type': u'STRING'},
      {u'mode': u'NULLABLE', u'name': u'a', u'type': u'INTEGER'},
      {u'mode': u'NULLABLE', u'name': u'b', u'type': u'INTEGER'}],
     u'mode': u'REPEATED',
     u'name': u'd',
     u'type': u'RECORD'}]},
  u'totalBytesProcessed': u'0',
  u'totalRows': u'1'},
 '_udf_resources': (),
 'query': 'SELECT ARRAY[STRUCT("zx83" AS id, 1 AS a, 2 AS b), ("f8f7", 4, 7)] d'}

but the rows accessor fails

In [8]: query.rows
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-8-27b591eefa89> in <module>()
----> 1 query.rows

.../google/cloud/bigquery/query.pyc in rows(self)
    223         :returns: fields describing the schema (None until set by the server).
    224         """
--> 225         return _rows_from_json(self._properties.get('rows', ()), self.schema)
    226
    227     @property

.../google/cloud/bigquery/_helpers.pyc in _rows_from_json(rows, schema)
     96             if field.mode == 'REPEATED':
     97                 row_data.append([converter(item, field)
---> 98                                  for item in cell['v']])
     99             else:
    100                 row_data.append(converter(cell['v'], field))

.../google/cloud/bigquery/_helpers.pyc in _record_from_json(value, field)
     59     if _not_null(value, field):
     60         record = {}
---> 61         for subfield, cell in zip(field.fields, value['f']):
     62             converter = _CELLDATA_FROM_JSON[subfield.field_type]
     63             if field.mode == 'REPEATED':

KeyError: 'f'

[dhermes]

Just for fun, here are those last properties as JSON:

{
  "cacheHit": false, 
  "jobComplete": true, 
  "jobReference": {
    "jobId": "job_ruoSHLwjeg_lSfYZq8dmrjQ0Eg4", 
    "projectId": "precise-truck-742"
  }, 
  "kind": "bigquery#queryResponse", 
  "rows": [
    {
      "f": [
        {
          "v": [
            {
              "v": {
                "f": [
                  {
                    "v": "zx83"
                  }, 
                  {
                    "v": "1"
                  }, 
                  {
                    "v": "2"
                  }
                ]
              }
            }, 
            {
              "v": {
                "f": [
                  {
                    "v": "f8f7"
                  }, 
                  {
                    "v": "4"
                  }, 
                  {
                    "v": "7"
                  }
                ]
              }
            }
          ]
        }
      ]
    }
  ], 
  "schema": {
    "fields": [
      {
        "fields": [
          {
            "mode": "NULLABLE", 
            "name": "id", 
            "type": "STRING"
          }, 
          {
            "mode": "NULLABLE", 
            "name": "a", 
            "type": "INTEGER"
          }, 
          {
            "mode": "NULLABLE", 
            "name": "b", 
            "type": "INTEGER"
          }
        ], 
        "mode": "REPEATED", 
        "name": "d", 
        "type": "RECORD"
      }
    ]
  }, 
  "totalBytesProcessed": "0", 
  "totalRows": "1"
}

[c0b]

and where can I get alias names? in a query like below I am expecting to access results in a dict way like result.a result.b or result["a"] result["b"] because in my actual app (in nodejs) I have more complicated query generated schema, there are more fields, to access all those fields in the tuple (1, 2) way is not convenient and error prone; please let me know should it be another issue by its own? or can be documentation request if you already have it (but I don't see an example in current doc)

https://googlecloudplatform.github.io/google-cloud-python/stable/bigquery-query.html

In [56]: query = client.run_sync_query('SELECT 1 AS a, 2 AS b'); query.use_legacy_sql = False; query.run()

In [57]: query.__dict__
Out[57]: 
{'_client': <gcloud.bigquery.client.Client at 0x7f89a047ad90>,
 '_configuration': <gcloud.bigquery.query._SyncQueryConfiguration at 0x7f89a0404710>,
 '_job': None,
 '_properties': {u'cacheHit': False,
  u'jobComplete': True,
  u'jobReference': {u'jobId': u'job_POdEXqw6MgHtL-WDZX21BA7_fII',
   u'projectId': u'hb-inspector'},
  u'kind': u'bigquery#queryResponse',
  u'rows': [{u'f': [{u'v': u'1'}, {u'v': u'2'}]}],
  u'schema': {u'fields': [{u'mode': u'NULLABLE',
     u'name': u'a',
     u'type': u'INTEGER'},
    {u'mode': u'NULLABLE', u'name': u'b', u'type': u'INTEGER'}]},
  u'totalBytesProcessed': u'0',
  u'totalRows': u'1'},
 '_udf_resources': (),
 'query': 'SELECT 1 AS a, 2 AS b'}

In [58]: query.rows
Out[58]: [(1, 2)]

[c0b]

I just found bq tool has a command switch --apilog can save the detail log, exposed the library used by bq tool's python code is google-api-python-client; that one is able to handle nested google-api-python-client; at least not crashing, but the data types handling are still not there googleapis/google-api-python-client#281

INFO:root:user-agent: google-api-python-client/1.3.1 (gzip)

[theacodes]

Bump - this is blocking a critical sample.

[dhermes]

@tseaver You still planning to handle?

[theacodes]

Another bump here.

[c0b]

blocked on this can't use google-cloud-python with the new BigQuery standard SQL

[theacodes]

Another bump, this is blocking samples and usage of this service.

/cc @omaray @jgeewax

[omaray]

/cc @stephenplusplus

[gaurangsinha]

Another bump, blocked on this.

Currently using a manual fix to:
google/cloud/bigquery/_helpers.py, line 61, in _record_from_json
Changed from

 for subfield, cell in zip(field.fields, value['f']):

to

record_iter = None
if 'f' in value:
    record_iter = zip(field.fields, value['f'])
elif 'v' in value and 'f' in value['v']:
    record_iter = zip(field.fields, value['v']['f'])
if record_iter:
    for subfield, cell in record_iter:

Also changed line 63, it's referencing the parent field mode instead of the subfield.
Changed from

if field.mode == 'REPEATED':

to

if subfield.mode == 'REPEATED':

[stephenplusplus]

@c0b since you helped us out with our schema parser over on google-cloud-node, does this issue affect us?

[tseaver]

@stephenplusplus Can you point me to the issue there?

[stephenplusplus]

Sure: googleapis/google-cloud-node#1593 -- and here is the parser we ended up with: https://github.com/GoogleCloudPlatform/google-cloud-node/blob/72192113f3259d94c565498c081cde1bc95d8085/packages/bigquery/src/table.js#L261

[tseaver]

@gaurangsinha thanks for the suggested fix. I will investigate ASAP.

[tseaver]

@gaurangsinha Can you check out #2787 for your purposes? It makes a more general change to how RECORD and REPEATED mode columns are parsed.

[theacodes]

@tseaver when can we expect a release of the bigquery client that includes this fix?

[tseaver]

@jonparrott We can make a release whenever. I'm expecting that we will be "declaring beta" this week on bigquery, which would be the natural point for the release.

[theacodes]

It'd be great to get a release before then so I can get the samples ready.

[greghines]

Hi - I am getting a strange error when trying the big query Python API. I think it is related to this discussion but not sure (and haven't found anything else relevant).

from google.cloud import bigquery 
client = bigquery.Client() 
query_results = client.run_sync_query("""
        SELECT
            fullVisitorId
        FROM dataset.table
        where hits.eventInfo.eventCategory is not Null
        limit 50;""") 

(I've removed the dataset and table names.) If I have the constraint "where hits is not Null" - this works just fine. But with the above code I get the error -

google.cloud.exceptions.BadRequest: 400 Cannot access field eventInfo on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [5:20] 

Is this error indeed related? Is so, when can we expect the fix to be released? (I installed the bigQuery client a couple of days ago so pretty sure I am using the most recent version.) If this error is not related, my apologies and any help pointing me in the right direction would be greatly appreciated.

Cheers, Greg

[tswast]

@greghines Your error does not appear to be caused by the client library. Are you able to run the same query in the BigQuery UI?

[greghines]

@tswast - thanks for the response. Yes the code does run in the BigQuery UI