SQL Transformation

By Unknown - Saturday, May 21, 2016 - No Comments

SQL Transformation in Informatica with examples

SQL Transformation is a connected transformation used to process SQL queries in the midstream of a pipeline. We can insert, update, delete and retrieve rows from the database at run time using the SQL transformation.

The SQL transformation processes external SQL scripts or SQL queries created in the SQL editor. You can also pass the database connection information to the SQL transformation as an input data at run time.

The following SQL statements can be used in the SQL transformation.

Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME)
DATA MANIPULATION statements (INSERT, UPDATE, DELETE, MERGE)
DATA Retrieval Statement (SELECT)
DATA Control Language Statements (GRANT, REVOKE)
Transaction Control Statements (COMMIT, ROLLBACK)

Configuring SQL Transformation

The following options can be used to configure an SQL transformation

Mode: SQL transformation runs either in script mode or query mode.
Active/Passive: By default, SQL transformation is an active transformation. You can configure it as passive transformation.
Database Type: The type of database that the SQL transformation connects to.
Connection type: You can pass database connection information or you can use a connection object.

We will see how to create an SQL transformation in script mode, query mode and passing the dynamic database connection with examples.

Creating SQL Transformation in Query Mode

Query Mode: The SQL transformation executes a query that defined in the query editor. You can pass parameters to the query to define dynamic queries. The SQL transformation can output multiple rows when the query has a select statement. In query mode, the SQL transformation acts as an active transformation.

Rules and Guidelines for Query Mode

Use the following rules and guidelines when you configure the SQL transformation to run in query mode:

The number and the order of the output ports must match the number and order of the fields in the query SELECT clause.
The native data type of an output port in the transformation must match the data type of the corresponding column in the database. The Integration Service generates a row error when the data types do not match.
When the SQL query contains an INSERT, UPDATE, or DELETE clause, the transformation returns data to the SQL Error port, the pass-through ports, and the Num Rows Affected port when it is enabled. If you add output ports the ports receive NULL data values.
When the SQL query contains a SELECT statement and the transformation has a pass-through port, the transformation returns data to the pass-through port whether or not the query returns database data. The SQL transformation returns a row with NULL data in the output ports.
You cannot add the "_output" suffix to output port names that you create.
You cannot use the pass-through port to return data from a SELECT query.
When the number of output ports is more than the number of columns in the SELECT clause, the extra ports receive a NULL value.
When the number of output ports is less than the number of columns in the SELECT clause, the Integration Service generates a row error.
You can use string substitution instead of parameter binding in a query. However, the input ports must be string data types.

You can create the following types of SQL queries

Static SQL query: The SQL query statement does not change, however you can pass parameters to the sql query. The integration service runs the query once and runs the same query for all the input rows.
Dynamic SQL query: The SQL query statement and the data can change. The integration service prepares the query for each input row and then runs the query.

SQL Transformation Example Using Static SQL query

Q1) Let’s say we have the products and Sales table with the below data.

Table Name: Products
PRODUCT 
-------
SAMSUNG
LG
IPhone

Table Name: Sales
PRODUCT QUANTITY PRICE
----------------------
SAMSUNG 2        100
LG      3        80
IPhone  5        200
SAMSUNG 5        500

Create a mapping to join the products and sales table on product column using the SQL Transformation? The output will be

PRODUCT QUANTITY PRICE
----------------------
SAMSUNG 2        100
SAMSUNG 5        500
IPhone  5        200
LG      3        80

Solution:

Just follow the below steps for creating the SQL transformation to solve the example

Create a new mapping, drag the products source definition to the mapping.
Go to the toolbar -> Transformation -> Create -> Select the SQL transformation. Enter a name and then click create.
Select the execution mode as query mode, DB type as Oracle, connection type as static. This is shown in the below image.Then click OK.

informatica sql transformation in query mode

Edit the sql transformation, go to the "SQL Ports" tab and add the input and output ports as shown in the below image. Here for all the ports, you have to define Data Type (informatica specific data types) and Native Type (Database specific data types).

sql transformation sql ports tab in informatica

In the same "SQL Ports" Tab, go to the SQL query and enter the below sql in the SQL editor.

select product, quantity, price from sales where product = ?product?

Here ?product? is the parameter binding variable which takes its values from the input port. Now connect the source qualifier transformation ports to the input ports of SQL transformation and target input ports to the SQL transformation output ports. The complete mapping flow is shown below.

Create the workflow, session and enter the connections for source, target. For SQL transformation also enter the source connection.

After you run the workflow, the integration service generates the following queries for sql transformation

select product, quantity, price from sales where product ='SAMSUNG'
select product, quantity, price from sales where product ='LG'
select product, quantity, price from sales where product ='IPhone'

Dynamic SQL query: A dynamic SQL query can execute different query statements for each input row. You can pass a full query or a partial query to the sql transformation input ports to execute the dynamic sql queries.

SQL Transformation Example Using Full Dynamic query

Q2) I have the below source table which contains the below data.

Table Name: Del_Tab
Del_statement
------------------------------------------
Delete FROM Sales WHERE Product = 'LG'
Delete FROM products WHERE Product = 'LG'

Solution:

Just follow the same steps for creating the sql transformation in the example 1.

Now go to the "SQL Ports" tab of SQL transformation and create the input port as "Query_Port". Connect this input port to the Source Qualifier Transformation.
In the "SQL Ports" tab, enter the sql query as ~Query_Port~. The tilt indicates a variable substitution for the queries.
As we don’t need any output, just connect the SQLError port to the target.
Now create workflow and run the workflow.

SQL Transformation Example Using Partial Dynamic query

Q3) In the example 2, you can see the delete statements are similar except Athe table name. Now we will pass only the table name to the sql transformation. The source table contains the below data.

Table Name: Del_Tab
Tab_Names
----------
sales
products

Solution:

Create the input port in the sql transformation as Table_Name and enter the below query in the SQL Query window.

Delete FROM ~Table_Name WHERE Product = 'LG'

SQL Transformation in Script Mode Examples - Informatica

This is continuation to my previous post on SQL Transformation in Query Mode. Here we will see how to use SQL transformation in script mode.

Script Mode

In a script mode, you have to create the sql scripts in a text file. The SQL transformation runs your sql scripts from these text files. You have to pass each script file name from the source to the SQL transformation ScriptName port. The script file name should contain a complete path to the script file. The SQL transformation acts as passive transformation in script mode and returns one row for each input row. The output row contains results of the query and any database error.

SQL Transformation default ports in script mode

In script mode, By default three ports will be created in SQL transformation. They are

ScriptName (Input port) : Receives the name of the script to execute for the current row.
ScriptResult (output port) : Returns PASSED if the script execution succeeds for the row. Otherwise FAILED.
ScriptError (Output port) : Returns errors that occur when a script fails for a row.

Rules and Guidelines for script mode

You have to follow the below rules and guidelines when using the sql transformation in script mode:

You can run only static sql queries and cannot run dynamic sql queries in script mode.
You can include multiple sql queries in a script. You need to separate each query with a semicolon.
The integration service ignores the output of select statements in the SQL scripts.
You cannot use procedural languages such as oracle plsql or Microsoft/Sybase T-SQL in the script.
You cannot call a script from another script. Avoid using nested scripts.
The script must be accessible to the integration service.
You cannot pass arguments to the script.
You can use mapping variables or parameters in the script file name.
You can use static or dynamic database connection in the script mode.

Note: Use SQL transformation in script mode to run DDL (data definition language) statements like creating or dropping the tables.

Create SQL Transformation in Script Mode

We will see how to create sql transformation in script mode with an example. We will create the following sales table in oracle database and insert records into the table using the SQL transformation.

Script Name: $PMSourceFileDir/sales_ddl.txt

Create Table Sales (
  Sale_id Number,
  Product_name varchar2(30),
  Price Number
);

Script Name: $PMSourceFileDir/sales_dml.txt

Insert into sales values(1,'Samsung',2000);
Insert into sales values(2,'LG',1000);
Insert into sales values(3,'Nokia',5000);

I created two script files in the $PMSourceFileDir directory. The sales_ddl.txt contains the sales table creation statement and the sales_dml.txt contains the insert statements. These are the script files to be executed by SQL transformation.

We need a source which contains the above script file names. So, I created another file in the $PMSourceFileDir directory to store these script file names.

File name: $PMSourceFileDir/Script_names.txt

> cat $PMSourceFileDir/Script_names.txt
$PMSourceFileDir/sales_ddl.txt
$PMSourceFileDir/sales_dml.txt

Now we will create a mapping to execute the script files using the SQL transformation. Follow the below steps to create the mapping.

Go to the mapping designer tool, source analyzer and create the source file definition with the structure as the $PMSourceFileDir/Script_names.txt file. The flat file structure is shown in the below image.

Go to the warehouse designer or target designer and create a target flat file with result and error ports. This is shown in the below image.

Go to the mapping designer and create a new mapping.
Drag the flat file into the mapping designer.
Go to the Transformation in the toolbar, Create, select the SQL transformation, enter a name and click on create.
Now select the SQL transformation options as script mode and DB type as Oracle and click ok.

The SQL transformation is created with the default ports.
Now connect the source qualifier transformation ports to the SQL transformation input port.
Drag the target flat file into the mapping and connect the SQL transformation output ports to the target.
Save the mappping. The mapping flow image is shown in the below picture.

Go to the workflow manager, create a new mapping and session.
Edit the session. For source, enter the source file directory, source file name options as $PMSourceFileDir\ and Script_names.txt respectively. For the SQL transformation, enter the oracle database relational connection.
Save the workflow and run it.

This will create the sales table in the oracle database and inserts the records.

SQL Transformation Properties

After you create the SQL transformation, you can define ports and set attributes in the following transformation tabs:

Ports. Displays the transformation ports and attributes that you create on the SQL Ports tab.
Properties. SQL transformation general properties.
SQL Settings. Attributes unique to the SQL transformation.
SQL Ports. SQL transformation ports and attributes.

Note: You cannot update the columns on the Ports tab. When you define ports on the SQL Ports tab, they display on the Ports tab.

Properties Tab

Configure the SQL transformation general properties on the Properties tab. Some transformation properties do not apply to the SQL transformation or are not configurable.

The following table describes the SQL transformation properties:

Property	Description
Run Time Location	Enter a path relative to the Integration Service node that runs the SQL transformation session. If this property is blank, the Integration Service uses the environment variable defined on the Integration Service node to locate the DLL or shared library. You must copy all DLLs or shared libraries to the run-time location or to the environment variable defined on the Integration Service node. The Integration Service fails to load the procedure when it cannot locate the DLL, shared library, or a referenced file.
Tracing Level	Sets the amount of detail included in the session log when you run a session containing this transformation. When you configure the SQL transformation tracing level to Verbose Data, the Integration Service writes each SQL query it prepares to the session log.
Is Partition able	Multiple partitions in a pipeline can use this transformation. Use the following options: - No. The transformation cannot be partitioned. The transformation and other transformations in the same pipeline are limited to one partition. You might choose No if the transformation processes all the input data together, such as data cleansing. - Locally. The transformation can be partitioned, but the Integration Service must run all partitions in the pipeline on the same node. Choose Locally when different partitions of the transformation must share objects in memory. - Across Grid. The transformation can be partitioned, and the Integration Service can distribute each partition to different nodes. Default is No.
Update Strategy	The transformation defines the update strategy for output rows. You can enable this property for query mode SQL transformations. Default is disabled.
Transformation Scope	The method in which the Integration Service applies the transformation logic to incoming data. Use the following options: - Row - Transaction - All Input Set transaction scope to transaction when you use transaction control in static query mode. Default is Row for script mode transformations.Default is All Input for query mode transformations.
Output is Repeatable	Indicates if the order of the output data is consistent between session runs. - Never. The order of the output data is inconsistent between session runs. - Based On Input Order. The output order is consistent between session runs when the input data order is consistent between session runs. - Always. The order of the output data is consistent between session runs even if the order of the input data is inconsistent between session runs. Default is Never.
Generate Transaction	The transformation generates transaction rows. Enable this property for query mode SQL transformations that commit data in an SQL query. Default is disabled.
Requires Single Thread Per Partition	Indicates if the Integration Service processes each partition of a procedure with one thread.
Output is Deterministic	The transformation generate consistent output data between session runs. Enable this property to perform recovery on sessions that use this transformation. Default is enabled.

News Ticker