Google BigQuery Connectors > Part II: Data Integration with Google BigQuery V2 Connector > Mappings and mapping tasks with Google BigQuery V2 Connector > 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 can configure an SQL transformation to process one or more entered query that you define in the SQL editor. 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.
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.

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:
    1. Enter a name and description for the SQL transformation.
    2. In the Incoming Fields tab, define field rules that determine the data to include in the transformation.
    3. In the Properties panel of the SQL transformation, click the SQL tab.
    4. In the SQL tab, perform the following tasks:
    1. a. Select the connection to the database.
    2. You can select the connection or use a parameter.
    3. b. Set the SQL type to Stored Procedure.
    4. c. Click Select to select the stored procedure from the database, or enter the exact name of the stored procedure to call.
    5. 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.
    5. In the Field Mapping tab, specify how to map incoming fields to the input fields of the selected stored procedure.
    6. Define 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.

Rules and guidelines for SQL transformation

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