Endpoint [Dynamic Endpoint]

Name

get_[dynamic_endpoint_name]

Description

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

Related Tables

[Dynamic Table]

Parameters

Output Columns

Input Columns

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]

Optional Parameters
ArrayTransformType	TransformComplexTwoDimensionalArray
ArrayTransRowValueFilter	$.f[*].v

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 [dynamic endpoint]:

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]

Select

Optional Parameters
ArrayTransformType	TransformComplexTwoDimensionalArray
ArrayTransRowValueFilter	$.f[*].v

ODBC application

Use these SQL queries in your ODBC application data source:

Native Query (ServerSide): Query using Simple SQL

Server side BigQuery SQL query example. Prefix SQL with word #DirectSQL to invoke server side engine (Pass-through SQL). Query free dataset table (bigquery-public-data.samples.wikipedia)

#DirectSQL SELECT * FROM bigquery-public-data.samples.wikipedia LIMIT 1000 /* try your own dataset or Some FREE dataset like nyc-tlc.yellow.trips -- 3 parts ([Project.]Dataset.Table) */

Native Query (ServerSide): Query using Complex SQL

Server side SQL query example of BigQuery. Prefix SQL with word #DirectSQL to invoke server side engine (Pass-through SQL). Query free dataset table (bigquery-public-data.usa_names.usa_1910_2013)

#DirectSQL 
SELECT name, gender, SUM(number) AS total
FROM bigquery-public-data.usa_names.usa_1910_2013
GROUP BY name, gender
ORDER BY total DESC
LIMIT 10

Native Query (ServerSide): Query with CAST unix TIMESTAMP datatype column as datetime

This example shows how to query timestamp column as DateTime. E.g. 73833719.524272 should be displayed as 1972-05-04 or with milliseconds 1972-05-04 1:21:59.524 PM then use CAST function (you must use #DirectSQL prefix)

#DirectSQL 
SELECT id, col_timestamp, CAST(col_timestamp as DATE) AS timestamp_as_date, CAST(col_timestamp as DATETIME) AS timestamp_as_datetime
FROM MyProject.MyDataset.MyTable
LIMIT 10

SQL Server

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

Native Query (ServerSide): Query using Simple SQL

Server side BigQuery SQL query example. Prefix SQL with word #DirectSQL to invoke server side engine (Pass-through SQL). Query free dataset table (bigquery-public-data.samples.wikipedia)

DECLARE @MyQuery NVARCHAR(MAX) = '#DirectSQL SELECT * FROM bigquery-public-data.samples.wikipedia LIMIT 1000 /* try your own dataset or Some FREE dataset like nyc-tlc.yellow.trips -- 3 parts ([Project.]Dataset.Table) */';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_BIGQUERY_IN_GATEWAY];

Native Query (ServerSide): Query using Complex SQL

Server side SQL query example of BigQuery. Prefix SQL with word #DirectSQL to invoke server side engine (Pass-through SQL). Query free dataset table (bigquery-public-data.usa_names.usa_1910_2013)

DECLARE @MyQuery NVARCHAR(MAX) = '#DirectSQL 
SELECT name, gender, SUM(number) AS total
FROM bigquery-public-data.usa_names.usa_1910_2013
GROUP BY name, gender
ORDER BY total DESC
LIMIT 10';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_BIGQUERY_IN_GATEWAY];

Native Query (ServerSide): Query with CAST unix TIMESTAMP datatype column as datetime

This example shows how to query timestamp column as DateTime. E.g. 73833719.524272 should be displayed as 1972-05-04 or with milliseconds 1972-05-04 1:21:59.524 PM then use CAST function (you must use #DirectSQL prefix)

DECLARE @MyQuery NVARCHAR(MAX) = '#DirectSQL 
SELECT id, col_timestamp, CAST(col_timestamp as DATE) AS timestamp_as_date, CAST(col_timestamp as DATETIME) AS timestamp_as_datetime
FROM MyProject.MyDataset.MyTable
LIMIT 10';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_BIGQUERY_IN_GATEWAY];