Google BigQuery Connectors

ZappySys Google BigQuery Connector provide read / write capability inside your app (see list below), using these drag and drop , high performance connectors you can perform many Google BigQuery operations without any coding. You can use this connector to integrate Google BigQuery data inside apps like SSIS, SQL Server or popular ETL Platforms / BI Tools/ Reporting Apps / Programming languages (i.e. Informatica, Power BI, SSRS, Excel, C#, JAVA, Python)

Download for SSIS Download for Other Apps Documentation

Video Tutorial - Google BigQuery - Connectors REST API Integration

Click on your App below to get started with Google BigQuery Integration

Choose your App / Scenario for which you like to integrate Google BigQuery

SSIS

SQL Server

Power BI

SSRS

Informatica

MS Access

MS Excel

SSAS

Python

JAVA

Tableau

SAP Crystal Reports

Azure Data Factory (ADF)

ODBC

Actions supported by Google BigQuery Connector

Google BigQuery Connector support following actions for REST API integration. If some actions are not listed below then you can easily edit Connector file and enhance out of the box functionality.

Read Data using SQL Query -OR- Execute Script (i.e. CREATE, SELECT, INSERT, UPDATE, DELETE)

Runs a BigQuery SQL query synchronously and returns query results if the query completes within a specified timeout

Parameter

Description

SQL Statement (i.e. SELECT / DROP / CREATE)

Option	Value
Example1	SELECT title,id,language,wp_namespace,reversion_id ,comment,num_characters FROM bigquery-public-data.samples.wikipedia LIMIT 1000
Example2	CREATE TABLE TestDataset.Table1 (ID INT64,Name STRING,BirthDate DATETIME, Active BOOL)
Example3	INSERT TestDataset.Table1 (ID, Name,BirthDate,Active) VALUES(1,'AA','2020-01-01',true),(2,'BB','2020-01-02',true),(3,'CC','2020-01-03',false)

Use Legacy SQL Syntax?

Option	Value
false	false
true	true

timeout (Milliseconds)

Wait until timeout is reached.

Option	Value
false	false
true	true

Job Location

The geographic location where the job should run. For Non-EU and Non-US datacenters we suggest you to supply this parameter to avoid any error.

Option	Value
System Default
Data centers in the United States	US
Data centers in the European Union	EU
Columbus, Ohio	us-east5
Iowa	us-central1
Las Vegas	us-west4
Los Angeles	us-west2
Montréal	northamerica-northeast1
Northern Virginia	us-east4
Oregon	us-west1
Salt Lake City	us-west3
São Paulo	southamerica-east1
Santiago	southamerica-west1
South Carolina	us-east1
Toronto	northamerica-northeast2
Delhi	asia-south2
Hong Kong	asia-east2
Jakarta	asia-southeast2
Melbourne	australia-southeast2
Mumbai	asia-south1
Osaka	asia-northeast2
Seoul	asia-northeast3
Singapore	asia-southeast1
Sydney	australia-southeast1
Taiwan	asia-east1
Tokyo	asia-northeast1
Belgium	europe-west1
Finland	europe-north1
Frankfurt	europe-west3
London	europe-west2
Madrid	europe-southwest1
Milan	europe-west8
Netherlands	europe-west4
Paris	europe-west9
Warsaw	europe-central2
Zürich	europe-west6
AWS - US East (N. Virginia)	aws-us-east-1
Azure - East US 2	azure-eastus2
Custom Name (Type your own)	type-region-id-here

Read Table Rows

Gets the specified table resource by table ID. This method does not return the data in the table, it only returns the table resource, which describes the structure of this table.

Parameter	Description
ProjectId	Leave this value blank to use ProjectId from connection settings
DatasetId	Leave this value blank to use DatasetId from connection settings
TableId

[$parent.tableReference.datasetId$].[$parent.tableReference.tableId$]

Read data from [$parent.tableReference.datasetId$].[$parent.tableReference.tableId$] for project .

Parameter	Description

List Projects

Lists Projects that the caller has permission on and satisfy the specified filter.

Parameter	Description
SearchFilter	An expression for filtering the results of the request. Filter rules are case insensitive. If multiple fields are included in a filter query, the query will return results that match any of the fields. Some eligible fields for filtering are: name, id, labels.{key} (where key is the name of a label), parent.type, parent.id, lifecycleState. Example: name:how*

List Datasets

Lists all BigQuery datasets in the specified project to which the user has been granted the READER dataset role.

Parameter

Description

ProjectId

SearchFilter

An expression for filtering the results of the request. Filter rules are case insensitive. If multiple fields are included in a filter query, the query will return results that match any of the fields. Some eligible fields for filtering are: name, id, labels.{key} (where key is the name of a label), parent.type, parent.id, lifecycleState. Example: name:how*

all

Whether to list all datasets, including hidden ones

Option	Value
True	True
False	False

Create Dataset

Creates a new empty dataset.

Parameter	Description
ProjectId
Dataset Name	Enter dataset name
Description

Delete Dataset

Deletes the dataset specified by the datasetId value. Before you can delete a dataset, you must delete all its tables, either manually or by specifying deleteContents. Immediately after deletion, you can create another dataset with the same name.

Parameter

Description

ProjectId

DatasetId

Delete All Tables

If True, delete all the tables in the dataset. If False and the dataset contains tables, the request will fail. Default is False

Option	Value
True	True
False	False

Delete Table

Parameter	Description
ProjectId
DatasetId
TableId

List Tables

Lists BigQuery Tables for the specified project / dataset to which the user has been granted the READER dataset role.

Parameter	Description
ProjectId
DatasetId

Get Query Schema (From SQL)

Runs a BigQuery SQL query synchronously and returns query schema

Parameter

Description

SQL Query

Use Legacy SQL Syntax?

Option	Value
false	false
true	true

timeout (Milliseconds)

Wait until timeout is reached.

Option	Value
false	false
true	true

Job Location

The geographic location where the job should run. For Non-EU and Non-US datacenters we suggest you to supply this parameter to avoid any error.

Option	Value
System Default
Data centers in the United States	US
Data centers in the European Union	EU
Columbus, Ohio	us-east5
Iowa	us-central1
Las Vegas	us-west4
Los Angeles	us-west2
Montréal	northamerica-northeast1
Northern Virginia	us-east4
Oregon	us-west1
Salt Lake City	us-west3
São Paulo	southamerica-east1
Santiago	southamerica-west1
South Carolina	us-east1
Toronto	northamerica-northeast2
Delhi	asia-south2
Hong Kong	asia-east2
Jakarta	asia-southeast2
Melbourne	australia-southeast2
Mumbai	asia-south1
Osaka	asia-northeast2
Seoul	asia-northeast3
Singapore	asia-southeast1
Sydney	australia-southeast1
Taiwan	asia-east1
Tokyo	asia-northeast1
Belgium	europe-west1
Finland	europe-north1
Frankfurt	europe-west3
London	europe-west2
Madrid	europe-southwest1
Milan	europe-west8
Netherlands	europe-west4
Paris	europe-west9
Warsaw	europe-central2
Zürich	europe-west6
AWS - US East (N. Virginia)	aws-us-east-1
Azure - East US 2	azure-eastus2
Custom Name (Type your own)	type-region-id-here

Get Table Schema

Gets the specified table resource by table ID. This method does not return the data in the table, it only returns the table resource, which describes the structure of this table.

Parameter	Description
DatasetId
TableId

insert_table_data

Parameter	Description
ProjectId
DatasetId
TableId

post_[$parent.tableReference.datasetId$]_[$parent.tableReference.tableId$]

Generic Request

This is generic endpoint. Use this endpoint when some actions are not implemented by connector. Just enter partial URL (Required), Body, Method, Header etc. Most parameters are optional except URL.

Parameter	Description
Url	API URL goes here. You can enter full URL or Partial URL relative to Base URL. If it is full URL then domain name must be part of ServiceURL or part of TrustedDomains
Body	Request Body content goes here
IsMultiPart	Check this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value) ==== Raw Upload (Content-Type: application/octet-stream) ===== To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g. @c:\data\myfile.zip ) ==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) ===== To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored. If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type. See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored). file1=@c:\data\Myfile1.txt file2=@c:\data\Myfile2.json file2.Content-Type=application/json SomeField1=aaaaaaa SomeField1.Content-Type=text/plain SomeField2=12345 SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab SomeFieldStartingWithAtSign=\@MyTwitterHandle
Filter	Enter filter to extract array from response. Example: $.rows[] --OR-- $.customers[].orders[*]. Check your response document and find out hierarchy you like to extract
Headers	Headers for Request. To enter multiple headers use double pipe or new line after each {header-name}:{value} pair