How to Query Google BigQuery data using ODBC / SSIS (Datatypes such as RECORD, JSON, Array, GEOGRAPHY)

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)

  1. Open DSN UI and edit DSN created by ZappySys API Driver for BigQuery
  2. Configure DSN as explained in previous link
  3. 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

SELECT ColInteger,ColString,ColFloat,ColNumeric,ColBool,ColTimestamp,ColDatetime,ColDate,ColBytes,ColBigNumeric,ColTime,ColGeography
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

Update TestDataset.DataTypeTest 
Set ColTime='23:59:59.123456',
 ColGeography=ST_GEOGPOINT(34.150480, -84.233870),
 ColJson= JSON_ARRAY('{"doc":1, "values":[{"id":1},{"id":2}]}') 
Where ColInteger=1