BigQuery: TypeError: b'v\xa9\' is not JSON serializable

[WillianFuks]

Os: Ubuntu 16.04
pipenv python 3.5
google-cloud-bigquery: 1.6.0

I'm basically trying to get a few rows from the public bitcoin dataset and save it in a personal destination table. Here's the code I used:

from google.cloud import bigquery as bq
client = bq.Client.from_service_account_json('service_account.json')

bc_ds_ref = client.dataset('bitcoin_blockchain', 'bigquery-public-data')
bc_table_ref = bq.TableReference(bc_ds_ref, 'blocks')
bc_table = client.get_table(bc_table_ref)

bc_data = list(client.list_rows(bc_table, max_results=10))

# saving the result to new destination table
dest_ds_ref = client.dataset('dest_dataset', 'dest_project')
dest_table_ref = bq.TableReference(dest_ds_ref, 'sampled_bitcoin_blocks')
dest_table = bq.Table(dest_table_ref, schema=bc_table.schema)

job = job = client.insert_rows(dest_table, bc_data)

At this point an exception is thrown; the full stacktrace:

TypeError                                 Traceback (most recent call last)
<ipython-input-38-5efecba8d0ed> in <module>
----> 1 job = client.insert_rows(dest_table, bc_data)

~/.local/share/virtualenvs/gcloud-4d5VpL3n/lib/python3.5/site-packages/google/cloud/bigquery/client.py in insert_rows(self, table, rows, selected_fields, **kwargs)
   1322             json_rows.append(json_row)
   1323 
-> 1324         return self.insert_rows_json(table, json_rows, **kwargs)
   1325 
   1326     def insert_rows_json(self, table, json_rows, row_ids=None,

~/.local/share/virtualenvs/gcloud-4d5VpL3n/lib/python3.5/site-packages/google/cloud/bigquery/client.py in insert_rows_json(self, table, json_rows, row_ids, skip_invalid_rows, ignore_unknown_values, template_suffix, retry)
   1402             method='POST',
   1403             path='%s/insertAll' % table.path,
-> 1404             data=data)
   1405         errors = []
   1406 

~/.local/share/virtualenvs/gcloud-4d5VpL3n/lib/python3.5/site-packages/google/cloud/bigquery/client.py in _call_api(self, retry, **kwargs)
    334         if retry:
    335             call = retry(call)
--> 336         return call()
    337 
    338     def get_dataset(self, dataset_ref, retry=DEFAULT_RETRY):

~/.local/share/virtualenvs/gcloud-4d5VpL3n/lib/python3.5/site-packages/google/api_core/retry.py in retry_wrapped_func(*args, **kwargs)
    258                 sleep_generator,
    259                 self._deadline,
--> 260                 on_error=on_error,
    261             )
    262 

~/.local/share/virtualenvs/gcloud-4d5VpL3n/lib/python3.5/site-packages/google/api_core/retry.py in retry_target(target, predicate, sleep_generator, deadline, on_error)
    175     for sleep in sleep_generator:
    176         try:
--> 177             return target()
    178 
    179         # pylint: disable=broad-except

~/.local/share/virtualenvs/gcloud-4d5VpL3n/lib/python3.5/site-packages/google/cloud/_http.py in api_request(self, method, path, query_params, data, content_type, headers, api_base_url, api_version, expect_json, _target_object)
    283         # data will be sent properly as JSON.
    284         if data and isinstance(data, dict):
--> 285             data = json.dumps(data)
    286             content_type = 'application/json'
    287 

/usr/lib/python3.5/json/__init__.py in dumps(obj, skipkeys, ensure_ascii, check_circular, allow_nan, cls, indent, separators, default, sort_keys, **kw)
    228         cls is None and indent is None and separators is None and
    229         default is None and not sort_keys and not kw):
--> 230         return _default_encoder.encode(obj)
    231     if cls is None:
    232         cls = JSONEncoder

/usr/lib/python3.5/json/encoder.py in encode(self, o)
    196         # exceptions aren't as detailed.  The list call should be roughly
    197         # equivalent to the PySequence_Fast that ''.join() would do.
--> 198         chunks = self.iterencode(o, _one_shot=True)
    199         if not isinstance(chunks, (list, tuple)):
    200             chunks = list(chunks)

/usr/lib/python3.5/json/encoder.py in iterencode(self, o, _one_shot)
    254                 self.key_separator, self.item_separator, self.sort_keys,
    255                 self.skipkeys, _one_shot)
--> 256         return _iterencode(o, 0)
    257 
    258 def _make_iterencode(markers, _default, _encoder, _indent, _floatstr,

/usr/lib/python3.5/json/encoder.py in default(self, o)
    177 
    178         """
--> 179         raise TypeError(repr(o) + " is not JSON serializable")
    180 
    181     def encode(self, o):

TypeError: b'v\xa9\x14x\xceH\xf8\x8c\x94\xdf7b\xda\x89\xdc\x84\x98 Ss\xa8\xceo\x88\xac' is not JSON serializable

But given that the data came directly from a BigQuery table, shouldn't this process be straightforward? I don't quite understand why this exception is thrown as bytes fields seem to be already analyzed in the code.

Is there anything I should be making before using insert_rows method?

Thanks in advance!

[tseaver]

The code in Client.insert_rows which converts the rows into JSON-compatible format does not expect to deal with nested records, which means that the bytes-type columns in the nested record field aren't going through the JSON-compat conversion.

@tswast Do you have a sense how this is supposed to go?

[tswast]

@tseaver is correct. Currently insert_rows does not convert record types to the correct JSON. It really should, though.

google-cloud-python/bigquery/google/cloud/bigquery/client.py

Line 1506 in 169bc22

converter = _SCALAR_VALUE_TO_JSON_ROW.get(field.field_type)

Looking at that line I also see a bug when mode='REPEATED' for the column. If a value is repeated, it is expected to be a list of values. The converter function will get called on the list instead of the values inside the list.

I think we need a helper method to convert a row/record that takes a schema (list of SchemaField) and a dictionary/tuple. This can then be called recursively when a column is of type RECORD.

[adamf]

I have a pull request (#7022) out for this, but would love a maintainer to give guidance on change location and other details. Thanks!