You can configure an SQL transformation in a Databricks Delta mapping to process SQL queries. The SQL transformation needs to be connected to the mapping pipeline.
When you add an SQL transformation to the mapping, on the
SQL
tab, you define the database connection and select the SQL query type for the transformation. The SQL transformation process the entered query that you define in the SQL editor. You can specify functions with a simple SELECT statement. Do not use more than one SQL query in an SQL transformation.
When you configure a SELECT query, specify the column names in the output ports for the functions. For example, when you specify the query
SELECT square(~AGE~), sqrt(~SNAME~)
, specify two output columns for the AGE and SNAME functions.
When you run the mapping, the SQL transformation processes the query and returns the rows. The SQL transformation also returns any errors that occur from the underlying database or from a user syntax that is not valid. When an SQL error occurs, the error is logged to the
SQLError
field, by default.
SQL transformation doesn't apply to Databricks cluster.
For more information about SQL queries, see
Transformations
in the Data Integration documentation.
Rules and guidelines for SQL transformation
You cannot configure a stored procedure in an SQL transformation.
The SQL query must be a simple SELECT statement without FROM and WHERE arguments.
You cannot configure a parameterized query in an SQL transformation.
You cannot include special characters in the query.
You need to manually specify the output ports, including the numrowsAffected and passthrough ports that are used downstream in the transformation.
ASK INFAPreview
