Error while trying to create the dataset table

[clorichel]

First thing first: thank you for this target guys!

Been struggling with this one for a bit, hoping someone could help:

Traceback (most recent call last):
  File "/root/.virtualenvs/target-bigquery/lib/python3.7/site-packages/target_bigquery-1.4.0-py3.7.egg/target_bigquery.py", line 243, in persist_lines_stream
  File "/root/.virtualenvs/target-bigquery/lib/python3.7/site-packages/google_cloud_bigquery-1.14.0-py3.7.egg/google/cloud/bigquery/client.py", line 408, in create_table
    api_response = self._call_api(retry, method="POST", path=path, data=data)
  File "/root/.virtualenvs/target-bigquery/lib/python3.7/site-packages/google_cloud_bigquery-1.14.0-py3.7.egg/google/cloud/bigquery/client.py", line 419, in _call_api
    return call()
  File "/root/.virtualenvs/target-bigquery/lib/python3.7/site-packages/google_api_core-1.11.1-py3.7.egg/google/api_core/retry.py", line 273, in retry_wrapped_func
    on_error=on_error,
  File "/root/.virtualenvs/target-bigquery/lib/python3.7/site-packages/google_api_core-1.11.1-py3.7.egg/google/api_core/retry.py", line 182, in retry_target
    return target()
  File "/root/.virtualenvs/target-bigquery/lib/python3.7/site-packages/google_cloud_core-1.0.2-py3.7.egg/google/cloud/_http.py", line 396, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.BadRequest: 400 POST https://www.googleapis.com/bigquery/v2/projects/redactedproject/datasets/redacteddataset/tables: Invalid value for type: NULL is not a valid value

The dataset is created just fine 👌 if it doesn't exist, but code always fails while trying to create the table. I've voluntarily added the BadRequest exception to intercept the table name with:

except exceptions.Conflict:
    pass
except exceptions.BadRequest:
    raise Exception("BadRequest, msg is {}".format(msg))

And here it is:

Exception: BadRequest, msg is {'type': 'SCHEMA', 'stream': 'reviews', 'schema': {'type': ['object'], 'properties': {'id': {'type': 'integer'}, 'created_at': {'type': 'string', 'format': 'date-time'}, 'updated_at': {'type': 'string', 'format': 'date-time'}, 'votes_up': {'type': 'number'}, 'votes_down': {'type': 'number'}, 'score': {'type': 'number'}, 'content': {'type': 'string'}, 'title': {'type': ['string', 'null']}, 'email': {'type': ['string', 'null']}, 'sentiment': {'type': ['number', 'null']}, 'sku': {'type': 'string'}, 'name': {'type': 'string'}, 'reviewer_type': {'type': 'string'}, 'deleted': {'type': 'boolean'}, 'user_reference': {'type': ['null', 'string']}}, 'additionalProperties': False}, 'key_properties': ['id']}

This msg seems valid to me: table = msg.stream and if I print it it's reviews as expected. What did I miss?

[balmasi]

This is also happening to me. What are we missing?

[nickleomartin]

Hey @clorichel ,

I think the problem here is that the tap has created a schema which has ordered the type list in a way that target-bigquery does not expect i.e.'title': {'type': ['string', 'null']} when it expects 'title': {'type': ['null', 'string']}.

See here:

target-bigquery/target_bigquery.py

Lines 68 to 73 in a30ec31

	if isinstance(field['type'], list):
	if field['type'][0] == "null":
	schema_mode = 'NULLABLE'
	else:
	schema_mode = 'required'
	schema_type = field['type'][-1]

The indexation for the last item in the list returns "null" resulting in the Invalid value for type: NULL is not a valid value error.

[0xinsanity]

Has there been any update on this? I tried modifying that if statement to also check the second value in the type array, but I still have the same error.

[hack-c]

also getting this problem when using tap-hellobaton. would something like this work?

 if isinstance(field['type'], list): 
     if "null" in field['type']: 
         schema_mode = 'NULLABLE' 
     else: 
         schema_mode = 'required' 
     schema_type = list(set(field['type']) - {"null"}).pop()