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.
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.
- 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).
- 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'
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: sales_ddl.txt
Create Table Sales (
Sale_id Number,
Product_name varchar2(30),
Price Number
);
Script Name: 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.
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.
