Structs lack proper names as dicts and arrays get turned into array of dicts

[jasonqng]

Version 0.1.4

This query returns a improperly named dict:

q = """
select struct(a,b) col
from
(SELECT 1 a, 2 b)
"""
df = gbq.read_gbq(q, dialect='standard', verbose=False)

Compare with result from Big Query:

An array of items also get turned into a arrays of dicts sometimes. For example:

q = """
select array_agg(a)
from
(select "1" a UNION ALL select "2" a)
"""
gbq.read_gbq(q, dialect='standard', verbose=False, project_id='project')

outputs:

Compare to Big Query:

These issues may or may not be related?

[jreback]

@jasonqng if you wouldn't mind debugging this locally and see what the raw returns from bq are for these cases (and show here)?

IOW about here: https://github.com/pydata/pandas-gbq/blob/master/pandas_gbq/gbq.py#L607

[jasonqng]

This is what I get back when running from bq command line:

query --use_legacy_sql=False select struct(a,b) col from (SELECT 1 a, 2 b)
+-------------------+
|        col        |
+-------------------+
| {"a":"1","b":"2"} |
+-------------------+

[jasonqng]

What I get for the second case, I get a proper list/array:

query --use_legacy_sql=False select array_agg(a) from (select "1" a UNION ALL select "2" a)
+--------------+
|     f0_      |
+--------------+
| [u'1', u'2'] |
+--------------+

I'll tinker around a bit more tomorrow. But looks like this shouldn't be too hard to fix. 🤞

[parthea]

Including the result from the api log as well (using bq --apilog=-):

{
 "kind": "bigquery#getQueryResultsResponse",
 "etag": "",
 "schema": {
  "fields": [
   {
    "name": "col",
    "type": "RECORD",
    "mode": "NULLABLE",
    "fields": [
     {
      "name": "a",
      "type": "INTEGER",
      "mode": "NULLABLE"
     },
     {
      "name": "b",
      "type": "INTEGER",
      "mode": "NULLABLE"
     }
    ]
   }
  ]
 },
 "jobReference": {
  "projectId": "x",
  "jobId": "bqjob_x"
 },
 "totalRows": "1",
 "rows": [
  {
   "f": [
    {
     "v": {
      "f": [
       {
        "v": "1"
       },
       {
        "v": "2"
       }
      ]
     }
    }
   ]
  }
 ],
 "totalBytesProcessed": "0",
 "jobComplete": true,
 "cacheHit": true
}

Full bq command:

bq query --apilog=- --format=prettyjson --use_legacy_sql=False  "select struct(a,b) col from (SELECT 1 a, 2 b)"

[jreback]

So this is not the correct way to do this. You would actually construct directly the result, but an idea. You will have to figure out semantically what the result is and how to describe this most usefully while representing the structure. Probably a MultiIndex on the columns makes sense here.

In [27]: df = DataFrame({'col':[{"a":"1","b":"2"}]})

In [28]: df
Out[28]: 
                    col
0  {'b': '2', 'a': '1'}

In [29]: df.unstack().apply(Series)
Out[29]: 
       a  b
col 0  1  2

Keep in mind that you want to the most natural representation. If its not obvious, then don't do it.

[jasonqng]

@jreback Not sure what you mean that this is not the correct way to do this. Certainly, the output you produce is one way of outputting the results (in a flattened way) and it can definitely be produced once you have the result in proper dict form. But as seen in the bq command line output, a dict is the expected output of col as opposed to the current behavior where the field name is lost altogether. Poked around and it looks like the field type of a struct in the schema returned from bq is RECORD; if I'm reading this right, it's just a matter of adding logic to replace the v keys to the corresponding fields["name"] from the schema.

Output:

Similar debugging info for the latter query above where the returned column is an array of two strings:

schema {u'fields': [{u'type': u'STRING', u'name': u'f0_', u'mode': u'REPEATED'}]}
rows [{u'f': [{u'v': [{u'v': u'1'}, {u'v': u'2'}]}]}]
field_value [{u'v': u'1'}, {u'v': u'2'}]
field_type STRING
page_array [([{u'v': u'1'}, {u'v': u'2'}],)]

[jreback]

@jasonqng I mean the idea here is to preserve the structure, but at the same time make it useful.

As far as contstruction you also might be able to directly construct with DataFrame.from_records; this is how we do this from SQL (where each row is a record).

[jreback]

@jasonqng want to take a crack at this?

[jasonqng]

@jreback Sure, have a hack day on Friday, hopefully will have time to give it a shot. Thanks.

[jreback]

great!

[jasonqng]

Just curious, how wedded is this project to google-api-python-client? I started to code up something for converting the mangled struct into the correct schema, but recalled that the google-cloud-python package doesn't have this issue, they already do a nice clean up of the field names for structs and arrays. Try for example:

from google.cloud import bigquery
bigquery_client = bigquery.Client()
q = """
select ROW_NUMBER() over () row_num, struct(a,b) col
from
(select * from
    (SELECT 1 a, 2 b)
    UNION ALL
    (SELECT 5 a, 6 b)
)
"""
table = bigquery.query.QueryResults(query=q,client=bigquery_client)
table.use_legacy_sql = False
table.run()
data = table.fetch_data()[0]
print data
columns=[field.name for field in table.schema]
print columns
df = pd.DataFrame(data=data,columns=columns)
df

Output:

[(2, {u'a': 5, u'b': 6}), (1, {u'a': 1, u'b': 2})]
[u'row_num', u'col']

row_num	col
2	{u'a': 5, u'b': 6}
1	{u'a': 1, u'b': 2}