Snowflake Cloud Data Warehouse V2 Connector > Additional runtime configurations > Process SQL queries and stored procedures using an SQL transformation
  

Process SQL queries and stored procedures using an SQL transformation

You can configure an SQL transformation to process SQL queries and stored procedures midstream in a Snowflake Cloud Data Warehouse 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.
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 Snowflake. The stored procedure must exist in the Snowflake 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.
The SQl transformation calls a stored procedure in Snowflake.
SQL Query
You can configure an SQL transformation to process an entered query that you define in the SQL editor. 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 SQL tab, select the query type as Entered Query and define the query in the SQL editor.
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

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 Snowflake 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.
This example lists the tasks required to configure an SQL transformation that calls a stored procedure in Snowflake:
    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.
    You can specify the Snowflake database, schema, and procedure name in the advanced SQL properties. The agent considers the properties specified in the stored procedure first, followed by the advanced source properties, then the additional JDBC URL parameters in the connection, and finally the source object metadata.
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 transformations

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