Google BigQuery Connectors

Back Next

Process SQL queries using an SQL transformation

You can configure an SQL transformation to process SQL queries and stored procedures midstream in a Google BigQuery V2 mapping.

When you add an SQL transformation to the mapping, on the SQL tab, you define the database connection and the type of SQL that the transformation processes.

When you call a stored procedure in an SQL transformation, you can use a connected SQL transformation. The transformation is connected to the mapping pipeline.

The SQL transformation can process the following types of SQL statements:

Stored procedure: You can configure an SQL transformation to call a stored procedure in Google BigQuery. The stored procedure must exist in the Google BigQuery database before you create the SQL transformation. When the SQL transformation processes a stored procedure, it passes input parameters to the stored procedure. The stored procedure passes the return value to the output fields of the transformation. You can also configure the SQL transformation to generate one output row for each input row.
SQL Query: You must use a standard SQL to define the entered SQL query. The SQL transformation processes the query and returns the rows. The SQL transformation also returns any errors that occur from the underlying database or if there is an error in the user syntax to the SQLError output field.

Define multiple entered queries in an SQL transformation separated by a semicolon (;).

Saved query type is not applicable.

For more information about SQL queries and stored procedures, see

Transformations

in the Data Integration help.

When you specify an SQL query in a mapping, you can set the billing project ID for the Google Cloud project that is linked to an active Google Cloud Billing account where the Secure Agent runs the query jobs. If you omit the project ID here, the Secure Agent runs the query jobs in the Google Cloud project corresponding to the

Project ID

value specified in the Google BigQuery V2 connection.

When you configure an SQL query in a mapping, you can assign a label to the SQL transformation. Use the label to organize and filter the associated jobs in the Google Cloud Platform Log Explorer.

For more information about labels and their usage requirements, see Assign a label to the transformations.

Mappings for Google BigQuery V2

Configuring an SQL transformation

Using a parameterized connection in an SQL transformation

Rules and guidelines for SQL transformation

Download Guide

Watch

Comments

Cloud Connectivity Homepage

Communities

Knowledge Base

Success Portal