Advanced Use Case

The following pipeline is an example of how the MySQL Stored Procedure is used in a scenario where data from a MySQL instance is inserted in a SQL instance.

Download this pipeline

In this pipeline there are two stored procedures that will be invoked - "enron.SP_Emp_Delete" and "enron.Multiple_Statements".

The first stored procedure is as shown below:

CREATE PROCEDURE enron.SP_Emp_Delete(IN empno1 INT,IN empno2 INT)
      BEGIN
           DELETE FROM sp_emp WHERE empid=empno1;
           DELETE FROM sp_emp WHERE empid=empno2;
      END

There are no OUT parameters in this stored procedure. No outputs will be shown in the Output Parameter output view.

The second stored procedure is as shown below:

CREATE PROCEDURE enron.Multiple_Statements(IN empno1 INT,empno2 INT, OUT result1  INT,OUT result2 INT)
      BEGIN
           INSERT INTO sp_emp (empid,ename) VALUES (empno1,'snaplogic1');
           INSERT INTO sp_emp (empid,ename) VALUES (empno2,'snaplogic12');
           SELECT empid INTO result1 FROM sp_emp WHERE ename='snaplogic1';
           SELECT empid INTO result2 FROM sp_emp WHERE ename='snaplogic2';
      END

Pipeline ETL Operations

This pipeline does the following ETL operations:

• Load: The first Mapper maps the values "501", and "502" to the fields "empno1" and "empno2" in the target schema.
• Transform: The first MySQL Stored Procedure Snap invokes the stored procedure "enron.SP_Emp_Delete" that deletes the records in the table that match the values in the "empno1" and "empno2" fields.
• Load: The second Mapper Snap maps the values "501", "502", "snaplogic1", and "snaplogic12" to the "empno1", "empno2", "ename1", and "ename2" fields in the target schema.
• Load: The second MySQL Stored Procedure Snap invokes the stored procedure "enron.Multiple_Statements" that inserts the records into the target table.
• Extract & Load: The stored procedure "enron.Multiple_Statements" invoked by the second MySQL Stored Procedure Snap also extracts records from the table and writes the "empid" into the "result1" and "result2" fields where there is a matching record based on the "ename" field.
• Load: The data passed from the second MySQL Stored Procedure Snap is inserted into the specified SQL database by the SQL Server Insert Snap.
• Extract: The records in the specified SQL table are retrieved by the SQL Server Select Snap, demonstrating that the pipeline was executed successfully.