Use Cloud Storage component to load json file into BigQuery table

Matillion ETL
1

I have json data that I am trying to load into a BigQuery table.

The json data looks like this

{"add:resultMessages": {"add-types:message": {"add-types:description": "The Tachograph details are not available for this driver number", "add-types:code": 405}}, "add:driver": {"add-types:givenName": "A PERSON", "add-types:dateOfBirth": "1900-10-15", "add-types:surname": "PERSON", "add-types:gender": "F"}, "add:enquiryGuid": "61f270cb-b01d-face-cafe-accededf100d", "add:address": {"add-types:addressLines": {"add-types:addressLine": ["Address 1", "TOWN"]}, "add-types:postCode": "ZZ19AA"}, "add:licence": {"add:validTo": "2029-01-22", "add:directiveIdentifier": 4, "add:entitlements": {"add:entitlement": [{"add-types:restrictions": {"add-types:restriction": [{"add-types:type": "01"}, {"add-types:type": 793}]}, "add-types:type": "F", "add-types:validFrom": "2013-01-19", "add-types:validTo": "2047-10-14", "add-types:code": "A"}, {"add-types:restrictions": {"add-types:restriction": {"add-types:type": "01"}}, "add-types:type": "P", "add-types:validFrom": "2009-01-25", "add-types:validTo": "2047-10-14", "add-types:code": "A"}, {"add-types:restrictions": {"add-types:restriction": [{"add-types:type": "01"}, {"add-types:type": 122}]}, "add-types:type": "F", "add-types:validFrom": "2013-08-06", "add-types:validTo": "2047-10-14", "add-types:code": "AM"}, {"add-types:restrictions": {"add-types:restriction": {"add-types:type": "01"}}, "add-types:type": "F", "add-types:validFrom": "2009-06-23", "add-types:validTo": "2047-10-14", "add-types:code": "B"}, {"add-types:restrictions": {"add-types:restriction": {"add-types:type": "01"}}, "add-types:type": "F", "add-types:validFrom": "2009-06-23", "add-types:validTo": "2047-10-14", "add-types:code": "B1"}, {"add-types:restrictions": {"add-types:restriction": {"add-types:type": "01"}}, "add-types:type": "P", "add-types:validFrom": "2009-07-01", "add-types:validTo": "2047-10-14", "add-types:code": "BE"}, {"add-types:restrictions": {"add-types:restriction": {"add-types:type": "01"}}, "add-types:type": "F", "add-types:validFrom": "2009-06-23", "add-types:validTo": "2047-10-14", "add-types:code": "F"}, {"add-types:restrictions": {"add-types:restriction": {"add-types:type": "01"}}, "add-types:type": "P", "add-types:validFrom": "1994-11-29", "add-types:validTo": "2047-10-14", "add-types:code": "G"}, {"add-types:restrictions": {"add-types:restriction": {"add-types:type": "01"}}, "add-types:type": "P", "add-types:validFrom": "1994-11-29", "add-types:validTo": "2047-10-14", "add-types:code": "H"}, {"add-types:restrictions": {"add-types:restriction": {"add-types:type": "01"}}, "add-types:type": "F", "add-types:validFrom": "2009-06-23", "add-types:validTo": "2047-10-14", "add-types:code": "K"}, {"add-types:restrictions": {"add-types:restriction": {"add-types:type": "01"}}, "add-types:type": "F", "add-types:validFrom": "2009-06-23", "add-types:validTo": "2047-10-14", "add-types:code": "L"}, {"add-types:restrictions": {"add-types:restriction": {"add-types:type": "01"}}, "add-types:type": "F", "add-types:validFrom": "2009-06-23", "add-types:validTo": "2047-10-14", "add-types:code": "N"}, {"add-types:restrictions": {"add-types:restriction": [{"add-types:type": "01"}, {"add-types:type": 122}]}, "add-types:type": "F", "add-types:validFrom": "2009-06-23", "add-types:validTo": "2047-10-14", "add-types:code": "P"}, {"add-types:restrictions": {"add-types:restriction": [{"add-types:type": "01"}, {"add-types:type": 122}]}, "add-types:type": "F", "add-types:validFrom": "2013-08-06", "add-types:validTo": "2047-10-14", "add-types:code": "Q"}]}, "add:status": "FC", "add:issueNumber": 30, "add:validFrom": "2019-01-23"}}

 

I am using the Cloud Storage load component but its failing with "No such field: add:resultMessages" since I cant use : in the field name what should I be doing - sorry if this is a) a long query and b) something really simple but I have not tried to do this before

2

Hi @SDIMB​,

I will preface my answer as I do not have experience with Google BigQuery but the JSON in your message is very valid. So, are you saying that you are trying to map the "add:resultMessages" to something like a column with a name of "resultMessages"? Or are you trying to land the entire payload in a column of type RECORD which would be the equivalent of variant for us Snowflake people?

The best way I have found to troubleshoot these loading type issues to look at the logs on the Data Warehouse side (in your case BigQuery). Typically the messages on the Data Warehouse side that you are loading into will have a bit better error message. Not to mention, if you see the same or similar error in the BigQuery logs, it likely means that it's not a Matillion issue as much as it is BigQueries expectation of that data. The important thing to remember here is that Matillion is doing any magic. It's simply defining a SQL statement to be executed. If you can't run a SQL statement manually to load the data then it's likely Matillion cannot either.

If you find that the needs to have some minor manipulation like removing the "add:" from the beginning of attributes before it will load flawlessly, then you could do that using Python. That would be pretty straightforward. Hopefully this give you some ideas. If not, please post back and hopefully someone can help further.