You can configure an SQL transformation in a Snowflake Data Cloud mapping to process SQL queries and stored procedures in Snowflake.
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.
You can choose to use a parameterized connection in a SQL transformation. You can also override the values defined in a parameter file at runtime. To use a parameterized connection in an SQL transformation, first create an SQL transformation in a mapping that uses a valid connection. Then, parameterize the connection in the SQL transformation. You can also use an SQL transformation to read from Java or SQL user-defined functions (UDF) in Snowflake.
You can use an SQL transformation to process the following types of SQL statements:
Stored procedure
You can configure an SQL transformation to call stored procedures and overloaded stored procedures in Snowflake. The stored procedure name is case-sensitive. You can select the stored procedure from the database, or enter the exact name of the stored procedure to call in the SQL transformation. Before you create the SQL transformation, ensure that stored procedure exists in the Snowflake database.
When you 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.
If you add a new stored procedure to the database when the mapping is open, the new stored procedure does not appear in the list of available stored procedures. To refresh the list, close and reopen the mapping.
SQL Query
You can configure an SQL transformation to process an entered query that you define in the SQL editor. Do not use more than one SQL query in an SQL transformation.
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.
For more information about SQL queries and stored procedures, see Transformations in the Data Integration documentation.
Rules and guidelines
You can use a SQL transformation with the following restrictions:
•You cannot use the saved query type in an SQL transformation.
•Mappings fail when you specify schema override results in multiple stored procedures that contain the same name and number of arguments.
•Mappings fail when the stored procedure contains Unicode characters.
•If NULL is returned from a stored procedure, a warning appears in the user interface and the session log. However, the mapping continues to process the rows.
•The runtime processing ignores the following properties in an SQL transformation:
- In-out and input parameters
- Advanced properties
- Auto commit
- Transformation scope
- Stop on error
•You cannot use key range partitioning when the mapping contains an SQL transformation.