Google BigQuery Connectors > Part II: Data Integration with Google BigQuery V2 Connector > Mappings for Google BigQuery V2 > Process SQL queries using an SQL transformation
  

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.
The SQl transformation calls a stored procedure in Google BigQuery.
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.
On the tab, select the query type as and define the query in the SQL editor.SQLEntered Query
Define multiple entered queries in an SQL transformation separated by a semicolon (;).
Note: 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.

Configuring an SQL transformation

You can configure a SQL transformation to process a stored procedure on the SQL tab of the SQL transformation.
This example lists the tasks required to configure an SQL transformation that calls a stored procedure in Google BigQuery.
Your mapping includes user IDs in the data flow. You want to include user names in addition to user IDs. You have a stored procedure that matches user IDs with user names in the database. You add an SQL transformation to your mapping, select the stored procedure, and map the userId incoming field with the userId input field in the stored procedure. Add a SQL transformation in a Google BigQuery mapping.
You check the Output Fields tab for the SQL transformation to confirm that it includes the username field. When you run the mapping, the username value is returned with the user ID.
Configure a mapping with the Source, Target, and SQL transformations.
Perform the following tasks in the SQL transformation:
    1Enter a name and description for the SQL transformation.
    2In the Incoming Fields tab, define field rules that determine the data to include in the transformation.
    3In the Properties panel of the SQL transformation, click the SQL tab.
    4In the SQL tab, perform the following tasks:
    1. aSelect the connection to the database.
      2. You can select the connection or use a parameter.
    2. bSet the SQL type to Stored Procedure.
    3. cClick Select to select the stored procedure from the database, or enter the exact name of the stored procedure to call.
      4. The stored procedure name is case-sensitive.
      Note: If you add a new stored procedure to the database while you have the mapping open, the new stored procedure does not appear in the list of available stored procedures. To refresh the list, close and reopen the mapping.
    The following image shows the configured SQL transformation properties:
    In this configuration, the SQL type selected is Stored Procedure.
    5In the Field Mapping tab, specify how to map incoming fields to the input fields of the selected stored procedure.
    6Define advanced properties for the transformation according to your requirement.
To configure an SQL transformation using the SQL type as SQL entered query , see Transformations in the Data Integration help.

Using a parameterized connection in an SQL transformation

You can parameterize a Google BigQuery V2 connection in an SQL transformation.
To parameterize an entered SQL query or a stored procedure in an SQL transformation midstream in a Google BigQuery V2 mapping, perform the following steps:
  1. 1On the SQL tab, select a Google BigQuery V2 connection without a parameter.
  2. 2Select Stored Procedure or SQL Query as the SQL Type.
  3. 3Select a stored procedure in Google BigQuery or define an entered query in the SQL editor.
  4. 4Change the connection for the SQL transformation to a parameterized Google BigQuery V2 connection.

Rules and guidelines for SQL transformation

Consider the following rules and guidelines when you configure an SQL transformation in mappings: