ZappySys Google BigQuery Connector can be used to query large amounts of Google BigQuery Data and also perform DML and DDL operations like CREATE, ALTER / Update / Delete / INSERT and more. In this post, we will see how to query complex types such as RECORD (i.e. ARRAY type) and JSON, GEOGRAPHY types in ZappySys ODBC Driver for BigQuery.
Check this article on how to read/write Google BigQuery data in different apps using ZappySys Driver
Here is an example of setting up ODBC DSN for Google BigQuery Driver for any app.
Query BigQuery data for RECORD type (nested fields)
If you already installed ZappySys ODBC PowerPack and followed the steps listed here then check below steps for query nested fields (RECORD datatype)
- Open DSN UI and edit DSN created by ZappySys API Driver for BigQuery
- Configure DSN as explained in previous link
- Go to Preview window and enter your like below (change as per your field). Main this is use #DirectSQL prefix in query. You can alias field as needed
Example SELECT SQL for BigQuery with ARRAY (i.e RECORD) / GEGRAPHY / JSON Type
#DirectSQL
SELECT ColInteger,ColString,ColFloat,ColNumeric,ColBool,ColTimestamp,ColDatetime,ColDate,ColBytes,ColBigNumeric,ColTime,ColGeography
,ColRecord.Id
,ColRecord.Name
,ColJson
FROM TestDataset.DataTypeTest
Here is how the table looks like in Google BigQuery Console
Response in Fiddler
Here is how schema is reported in Fiddler when you call query api
Update Google BigQuery for Array / RECORD, Geography, JSON Datatype
Example UPDATE SQL for BigQuery
#DirectSQL
Update TestDataset.DataTypeTest
Set ColTime='23:59:59.123456',
ColGeography=ST_GEOGPOINT(34.150480, -84.233870),
ColRecord=(1,"AA"),
ColBigNumeric=1222222222222222222.123456789123456789123456789123456789,
ColJson= JSON_ARRAY('{"doc":1, "values":[{"id":1},{"id":2}]}')
Where ColInteger=1