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 and write Google BigQuery data effortlessly. Query, integrate, and manage datasets, tables, and jobs — almost no coding required.

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 and write Google BigQuery data effortlessly. Query, integrate, and manage datasets, tables, and jobs — almost no coding required.

Google BigQuery

[Dynamic Table]

Select

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

ODBC application

Use these SQL queries in your ODBC application data source:

Query using simple SQL (Native SQL)

<p><strong>Native SQL</strong> — the SQL runs on the BigQuery side (server-side). Prefix the SQL with <code>#DirectSQL</code> to use this mode. Example queries a public dataset table (<code>bigquery-public-data.samples.wikipedia</code>); you can use your own or other free datasets (e.g. <code>nyc-tlc.yellow.trips</code>). Table name has three parts: <code>[Project.]Dataset.Table</code>.</p>

#DirectSQL
SELECT *
FROM bigquery-public-data.samples.wikipedia
LIMIT 1000

Query using complex SQL (Native SQL)

<p><strong>Native SQL</strong> — the SQL runs on the BigQuery side (server-side). Prefix with <code>#DirectSQL</code> to use this mode. Example uses <code>GROUP BY</code> and <code>ORDER BY</code> with the public dataset <code>bigquery-public-data.usa_names.usa_1910_2013</code>.</p>

#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

Query with CAST unix TIMESTAMP as datetime (Native SQL)

<p><strong>Native SQL</strong> — the SQL runs on the BigQuery side (server-side). Prefix with <code>#DirectSQL</code> to use this mode. Shows how to return a timestamp column as date or datetime using <code>CAST(col_timestamp AS DATE)</code> or <code>CAST(col_timestamp AS DATETIME)</code>.</p>

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

Query using simple SQL (Native SQL)

<p><strong>Native SQL</strong> — the SQL runs on the BigQuery side (server-side). Prefix the SQL with <code>#DirectSQL</code> to use this mode. Example queries a public dataset table (<code>bigquery-public-data.samples.wikipedia</code>); you can use your own or other free datasets (e.g. <code>nyc-tlc.yellow.trips</code>). Table name has three parts: <code>[Project.]Dataset.Table</code>.</p>

DECLARE @MyQuery NVARCHAR(MAX) = '#DirectSQL
SELECT *
FROM bigquery-public-data.samples.wikipedia
LIMIT 1000';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_BIGQUERY_IN_GATEWAY];

Query using complex SQL (Native SQL)

<p><strong>Native SQL</strong> — the SQL runs on the BigQuery side (server-side). Prefix with <code>#DirectSQL</code> to use this mode. Example uses <code>GROUP BY</code> and <code>ORDER BY</code> with the public dataset <code>bigquery-public-data.usa_names.usa_1910_2013</code>.</p>

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];

Query with CAST unix TIMESTAMP as datetime (Native SQL)

<p><strong>Native SQL</strong> — the SQL runs on the BigQuery side (server-side). Prefix with <code>#DirectSQL</code> to use this mode. Shows how to return a timestamp column as date or datetime using <code>CAST(col_timestamp AS DATE)</code> or <code>CAST(col_timestamp AS DATETIME)</code>.</p>

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];