Google BigQuery Connectors

Back Next

Custom query source type

You can use a custom query as a source object when you use a

Google BigQuery V2

connection.

You might want to use a custom query as the source when a source object is large. You can use the custom query to reduce the number of fields that enter the data flow. You can configure a custom query to read data from one project while having the

Google BigQuery V2

connection set up in another project. You can also create a parameter for the source type when you design your mapping so that you can define the query in the

Mapping

Task wizard.

To use a custom query as a source, select

Query

as the source type when you configure the source transformation and then use valid and supported SQL to define the query.

You can use legacy SQL or standard SQL to define a custom query. To define a legacy SQL custom query, you must select the

Use Legacy SQL For Custom Query

option when you create a

Google BigQuery V2

connection. You can unselect the

Use Legacy SQL For Custom Query

option to define a standard SQL custom query. For more information about

Google BigQuery

Legacy SQL functions and operators, see Legacy SQL functions and operators.

Rules and guidelines for
Google BigQuery
custom queries

When you configure a custom query, consider the following guidelines:

You cannot use custom query as a source for the following configurations:

Key range partitions

Data filters

Sort

When you specify the SESSSTARTTIME variable in a custom query to return the current date and time, use any of the following formats in the SELECT query:

CAST('$$$SESSSTARTTIME' as TIMESTAMP(0))

SELECT PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%E6S', '$$$SESSSTARTTIME' ) as t1 --2022-10-06 18:53:28 UTC

SELECT cast(substr(cast('$$$SESSSTARTTIME' as string),0,19) as datetime FORMAT 'MM/DD/YYYY HH24:MI:SS') as t2;

If you provide the billing project ID in the source advanced properties, and you want to use the staging mode for the read operation and enable the

Use EXPORT DATA statement to stage

property, you must specify the project ID in the custom query.

When you configure a view, you can query only to a maximum of 14 nested levels.

When you use the query source type in a mapping to read from multiple tables, and you configure a join for one or more tables that have the same column names, the mapping fails.

For example, see the following SQL query that involves a full outer join between two tables EMPLOYEE and DEPARTMENT that are part of the SALES.PUBLIC schema, where two columns have the same name, CITY:

SELECT EMP_ID, NAME, CITY, DEPT_ID, DEPT_NAME, CITY FROM SALES.PUBLIC.EMPLOYEE FULL OUTER JOIN SALES.PUBLIC.DEPARTMENT ON EMP_ID = DEPT_ID

To distinguish the conflicting column names, add aliases that the database can refer to while joining the tables:

SELECT e.EMP_ID, e.NAME, e.CITY as ecity,d.DEPT_ID, d.DEPT_NAME, d.CITY as dcity FROM SALES.PUBLIC.EMPLOYEE e FULL OUTER JOIN SALES.PUBLIC.DEPARTMENT d ON e.EMP_ID = d.DEPT_ID

Rename Saved Search

Table of Contents

Google BigQuery Connectors

Google BigQuery Connectors

Custom query source type

Custom query source type

Rules and guidelines for Google BigQuery custom queries

Rules and guidelines for
Google BigQuery
custom queries