Reference

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

Name

get_query

Description

Runs a BigQuery SQL query synchronously and returns query results if the query completes within a specified timeout [API reference]

Related Tables

[Dynamic Table]

Parameters

Parameter Required Options
Name: Sql

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

 YES
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)
Name: useLegacySql

Label: Use Legacy SQL Syntax?
Option Value
false false
true true
Name: timeoutMs

Label: timeout (Milliseconds)

Wait until timeout is reached.
Option Value
false false
true true
Name: Location

Label: 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

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
-Dynamic- DT_WSTR nvarchar(4000) 4000
[Dynamic Column]_DT DT_DBTIMESTAMP datetime
If the column you are looking for is missing, consider customizing Google BigQuery Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use Google BigQuery Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - Google BigQuery
Read / write Google BigQuery data inside your app without coding using easy to use high performance API Connector
Google BigQuery
[Dynamic Table]
Required Parameters
SQL Statement (i.e. SELECT / DROP / CREATE) Fill-in the parameter...
Optional Parameters
Use Legacy SQL Syntax? false
timeout (Milliseconds) 90000
Job Location
SSIS API Source - Read from table or endpoint

API Destination

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to read data using sql query -or- execute script:

API Destination - Google BigQuery
Read / write Google BigQuery data inside your app without coding using easy to use high performance API Connector
Google BigQuery
[Dynamic Table]
Lookup
Required Parameters
SQL Statement (i.e. SELECT / DROP / CREATE) Fill-in the parameter...
Optional Parameters
Use Legacy SQL Syntax? false
timeout (Milliseconds) 90000
Job Location
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read data using sql query -or- execute script 

SELECT * FROM [dynamic_table_name]
WITH
(
    "Sql" = 'SELECT title,id,language,wp_namespace,reversion_id ,comment,num_characters FROM bigquery-public-data.samples.wikipedia LIMIT 1000'
)

get_query endpoint belongs to [Dynamic Table] table(s), and can therefore be used via those table(s).

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Read data using sql query -or- execute script 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM [dynamic_table_name]
WITH
(
    "Sql" = ''SELECT title,id,language,wp_namespace,reversion_id ,comment,num_characters FROM bigquery-public-data.samples.wikipedia LIMIT 1000''
)';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_BIGQUERY_IN_GATEWAY];

get_query endpoint belongs to [Dynamic Table] table(s), and can therefore be used via those table(s).