tMDMSP - 6.3

Talend Open Studio for Big Data Components Reference Guide

EnrichVersion
6.3
EnrichProdName
Talend Open Studio for Big Data
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

Function

tMDMSP calls the MDM Hub stored procedure.

Purpose

tMDMSP offers a convenient way to centralize multiple or complex queries in a MDM Hub and call them easily.

tMDMSP Properties

Component family

Talend MDM

 

Basic settings

Schema and Edit Schema

In SP principle, the schema is an input parameter.

A schema is a row description, it defines the number of fields to be processed and passed on to the next component.

The schema of this component is read-only.

 Use an existing connectionSelect this check box if you want to use a configured tMDMConnection component.
 

MDM version

By default, Server 6.0 is selected. Although it is recommended to migrate existing Jobs for this new version, the Server 5.6 option is available to ease the process of the migration of your Jobs so as to keep them working without modification with a 6.0 server. To do so, an option on the server must be enabled to accept and translate requests from such Jobs.

 

URL

Type in the URL of the MDM server.

 

Username and Password

Type in the user authentication data for the MDM server.

To enter the password, click the [...] button next to the password field, and then in the pop-up dialog box enter the password between double quotes and click OK to save the settings.

 

Data Container

Type in the name of the data container that stores the procedure you want to call.

Type

Select Master or Staging to specify the database on which the action should be performed.

 

Procedure Name

Type in the exact name of the Stored Procedure.

 

Parameters (in order)

Click the Plus button and select the various Input Columns that will be required by the procedures.

Note

The SP schema can hold more columns than there are parameters used in the procedure.

Advanced settings

tStatCatcher Statistics

Select this check box to gather the processing metadata at the Job level as well as at each component level.

Connections

Outgoing links (from this component to another):

Row: Main

Trigger: Run if; On Component Ok; On Component Error, On Subjob Ok, On Subjob Error.

Incoming links (from one component to this one):

Row: Main, Iterate;

Trigger: Run if, On Component Ok, On Component Error, On Subjob Ok, On Subjob Error

For further information regarding connections, see Talend Studio User Guide.

Global Variables

ERROR_MESSAGE: the error message generated by the component when an error occurs. This is an After variable and it returns a string. This variable functions only if the Die on error check box is cleared, if the component has this check box.

A Flow variable functions during the execution of a component while an After variable functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl + Space to access the variable list and choose the variable to use from it.

For further information about variables, see Talend Studio User Guide.

Usage

This component is used as intermediary component. It can be used as start component but only no input parameters are thus needed for the procedure to be called. An output link is required.

Limitation

N/A

Executing a stored procedure using tMDMSP

In this scenario, the Job first generates parameters and sends them to tMDMSP, which executes a predefined stored procedure, and then extracts data from the returned execution result and presents the extracted data in the console.

Prerequisites:

  • Make sure the MDM server is up and running.

  • You have imported the MDM demo project and loaded the sample data into the data container Product by running the Job MDM_LoadAll.

  • You have created a store procedure called ProductSelection and deployed this stored procedure to the MDM server.

In this example, the stored procedure ProductSelection is designed to query two fields Name and Price of Product data records within a price range:

For more information on working with stored procedures, see Talend Studio User Guide.

Creating a Job to execute the stored procedure

  1. Drag and drop the following components onto the design workspace: tFixedFlowInput, tMDMSP, tExtractXMLField, and tLogRow.

  2. Link the components using the Row > Main connections.

Configuring tFixedFlowInput to generate a price range

  1. Double-click tFixedFlowInput to open its Basic settings view.

  2. Click the [...] button next to Edit schema to open the schema editor.

  3. Click the [+] button to add two parameters min and max that are used to define the price range.

  4. Click OK to close the schema editor.

  5. In the Mode area, keep the default option Use Single Table, and enter 10 between quotes for the min parameter and 15 between quotes for the max parameter.

Configuring tMDMSP to execute the stored procedure

  1. Double-click tMDMSP to open its Basic settings view.

  2. Enter the user name and password for accessing the MDM server.

  3. In the Data Container field, enter Product between quotes.

  4. In the Procedure Name field, enter ProductSelection between quotes.

  5. In the Parameters table, click the [+] button to add two rows, and select min and max respectively.

Configuring tExtractXMLField to extract data from the returned execution result of the stored procedure

  1. Double-click tExtractXMLField to open its Basic settings view.

  2. Click the [...] button next to Edit schema to open the schema editor.

  3. Add two columns to define the structure of the output data: name and price.

  4. Click OK to close the schema editor, and then click Yes in the [Propagate] dialog box.

  5. In the Loop XPath query field, enter result between quotes on which the loop is based.

    Each result of the stored procedure has the following syntax: <result><col0>val1</col1>...<colN>valn</colN></result> (where colN is the nth column in the SELECT clause).

    Warning

    If a null value exists in a data record to be returned by the execution result of the stored procedure, some inconsistencies will occur between the column number and the value for this column in the returned data record.

  6. In XPath query column of the Mapping table, enter the exact node name on which the loop is applied: col0 and col1, respectively, between quotes.

Configuring the data display mode and executing the Job

  1. Double-click the tLogRow component to display its Basic settings view.

  2. In the Mode area, select Table (print values in cells of a table) for better readability of the result.

  3. Save the Job and press F6 to run it.

    According to the stored procedure and the price range, the Product data records within the price range of 10 to 15 are displayed with the values of the Name and Price fields as expected.