Extract Google Analytics Data using BigQuery and load in SQL Server

Question

I have to extract data from Google Analytics using BigQuery in JSON and load the JSON to SQL Server.

I know we can call BigQuery API in C# and stream the data in JSON and upload it in Google Storage and then copy the JSON file from GS to the ETL server using GSUTIL for processing. Read the JSON and load it in SQL server.

My Question is -

Is there a way we can directly stream JSON to ETL server instead of uploading to Google Storage?

Is there a way while streaming data, parse each row and load it in the table directly?

Is there a way to directly connect BigQuery API using SSIS(without 3rd party connector like ZappySys, CData)?

Appreciate your help.

Answer 1

You can use C# to connect to a BigQuery dataset and parse it row by row referencing Google.Apis.Bigquery.v2 and load it accordingly into a table here's a basic exaple

Alternatively, you can switch on Big Query access from within Google App Script (Resources -> Advanced Google Services -> BigQuery API) and then JavaScript and schedule your ETL logic Google Script example

If you can avoid parsing the data row by row from within code, you will find your solution scales better, but this will depend on your ETL requirements and dataset size