Endpoint Post Dynamic Endpoint

Name

post_[dynamic_endpoint_name]

Description

No description available

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

Insert

There are no parameters to configure.

ODBC application

Use these SQL queries in your ODBC application data source:

INSERT Single Row

This is sample how you can insert into BigQuery using ZappySys query language. You can also use ProjectId='myproject-id' in WITH clause.

INSERT INTO MyBQTable1(SomeBQCol1, SomeBQCol2) Values(1,'AAA')
--WITH(DatasetId='TestDataset',Output='*')
--WITH(DatasetId='TestDataset',ProjectId='MyProjectId',Output='*')

INSERT Multiple Rows from SQL Server

This example shows how to bulk insert into Google BigQuery Table from microsoft SQL Server as external source. Notice that INSERT is missing column list. Its provided by source query so must produce valid column names found in target BQ Table (you can use SQL Alias in Column name to produce matching names)

INSERT INTO MyBQTable1 
SOURCE(
    'MSSQL'
  , 'Data Source=localhost;Initial Catalog=tempdb;Initial Catalog=tempdb;Integrated Security=true'
  , 'SELECT Col1 as SomeBQCol1,Col2 as SomeBQCol2 FROM SomeTable Where SomeCol=123'
)
--WITH(DatasetId='TestDataset',Output='*')
--WITH(DatasetId='TestDataset',ProjectId='MyProjectId',Output='*')

INSERT Multiple Rows from any ODBC Source (DSN)

This example shows how to bulk insert into Google BigQuery Table from any external ODBC Source (Assuming you have installed ODBC Driver and configured DSN). Notice that INSERT is missing column list. Its provided by source query so it must produce valid column names found in target BQ Table (you can use SQL Alias in Column name to produce matching names)

INSERT INTO MyBQTable1 
SOURCE(
    'ODBC'
  , 'DSN=MyDsn'
  , 'SELECT Col1 as SomeBQCol1,Col2 as SomeBQCol2 FROM SomeTable Where SomeCol=123'
) 
WITH(DatasetId='TestDataset')

INSERT Multiple Rows from any JSON Files / API (Using ZappySys ODBC JSON Driver)

This example shows how to bulk insert into Google BigQuery Table from any external ODBC JSON API / File Source (Assuming you have installed ZappySys ODBC Driver for JSON). Notice that INSERT is missing column list. Its provided by source query so it must produce valid column names found in target BQ Table (you can use SQL Alias in Column name to produce matching names). You can also use similar approach to read from CSV files or XML Files. Just use CSV / XML driver rather than JSON driver in connection string. Refer this for more examples of JSON Query https://zappysys.com/onlinehelp/odbc-powerpack/scr/json-odbc-driver-sql-query-examples.htm

INSERT INTO MyBQTable1 
SOURCE(
    'ODBC'
  , 'Driver={ZappySys JSON Driver};Src='https://some-url/get-data''
  , 'SELECT Col1 as SomeBQCol1,Col2 as SomeBQCol2 FROM _root_'
)
--WITH(DatasetId='TestDataset',Output='*')
--WITH(DatasetId='TestDataset',ProjectId='MyProjectId',Output='*')

SQL Server

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

INSERT Single Row

This is sample how you can insert into BigQuery using ZappySys query language. You can also use ProjectId='myproject-id' in WITH clause.

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO MyBQTable1(SomeBQCol1, SomeBQCol2) Values(1,''AAA'')
--WITH(DatasetId=''TestDataset'',Output=''*'')
--WITH(DatasetId=''TestDataset'',ProjectId=''MyProjectId'',Output=''*'')';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_BIGQUERY_IN_GATEWAY];

INSERT Multiple Rows from SQL Server

This example shows how to bulk insert into Google BigQuery Table from microsoft SQL Server as external source. Notice that INSERT is missing column list. Its provided by source query so must produce valid column names found in target BQ Table (you can use SQL Alias in Column name to produce matching names)

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO MyBQTable1 
SOURCE(
    ''MSSQL''
  , ''Data Source=localhost;Initial Catalog=tempdb;Initial Catalog=tempdb;Integrated Security=true''
  , ''SELECT Col1 as SomeBQCol1,Col2 as SomeBQCol2 FROM SomeTable Where SomeCol=123''
)
--WITH(DatasetId=''TestDataset'',Output=''*'')
--WITH(DatasetId=''TestDataset'',ProjectId=''MyProjectId'',Output=''*'')';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_BIGQUERY_IN_GATEWAY];

INSERT Multiple Rows from any ODBC Source (DSN)

This example shows how to bulk insert into Google BigQuery Table from any external ODBC Source (Assuming you have installed ODBC Driver and configured DSN). Notice that INSERT is missing column list. Its provided by source query so it must produce valid column names found in target BQ Table (you can use SQL Alias in Column name to produce matching names)

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO MyBQTable1 
SOURCE(
    ''ODBC''
  , ''DSN=MyDsn''
  , ''SELECT Col1 as SomeBQCol1,Col2 as SomeBQCol2 FROM SomeTable Where SomeCol=123''
) 
WITH(DatasetId=''TestDataset'')';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_BIGQUERY_IN_GATEWAY];

INSERT Multiple Rows from any JSON Files / API (Using ZappySys ODBC JSON Driver)

This example shows how to bulk insert into Google BigQuery Table from any external ODBC JSON API / File Source (Assuming you have installed ZappySys ODBC Driver for JSON). Notice that INSERT is missing column list. Its provided by source query so it must produce valid column names found in target BQ Table (you can use SQL Alias in Column name to produce matching names). You can also use similar approach to read from CSV files or XML Files. Just use CSV / XML driver rather than JSON driver in connection string. Refer this for more examples of JSON Query https://zappysys.com/onlinehelp/odbc-powerpack/scr/json-odbc-driver-sql-query-examples.htm

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO MyBQTable1 
SOURCE(
    ''ODBC''
  , ''Driver={ZappySys JSON Driver};Src=''https://some-url/get-data''''
  , ''SELECT Col1 as SomeBQCol1,Col2 as SomeBQCol2 FROM _root_''
)
--WITH(DatasetId=''TestDataset'',Output=''*'')
--WITH(DatasetId=''TestDataset'',ProjectId=''MyProjectId'',Output=''*'')';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_BIGQUERY_IN_GATEWAY];