Talendis an Open Source Data Integration tool, that can be used to send data to
Vizardfrom different data sources such as Enterprise Systems (such as SAP, Oracle etc), Relational Databases (such as MySQL, MSSQL, PostgreSQL etc) and Cloud Software (such as Salesforce etc).
This help section demonstrates an example on how to integrate data from
MySQLdatabase into Vizard using Talend. We will build a Talend job to send data to three different tables (salaries, dept_emp, departments) by extracting data from MySQL Database.
The sample database used in this example is as shown.
Create a Job in Talend by selecting
Create Job under
Job Designs as shown below.
Name the talend Job and click
Finish to create the Job. Double click the Job under
Job Designs to open the Job.
You can add talend components from the
Palette.To extract data from MySQL database, we need
tMysqlInputcomponent. Since we have three tables, i.e.
departments.we will add three
tMysqlInputcomponents in the Job.
For Edit Components Name : Change the name of the components in the
View subtab under
Component tab as shown below.
Create a Database Connection
To configure the schema to MySQL components you need to establish a connection to the database. This can be done in two ways, one is to create a connection in
Metadataso that you can use this connection across components and the other is to create a connection in the
Componenttab. It is advisable to create a connection in Metadata. Kindly follow this video for create a
Configure Schema to MySQL Components
To Configure schema of MySQL components, You can speify the
SQL Query,use the following queries to get the schema.
SELECT * FROM salaries LIMIT 2000
SELECT * FROM dept_emp LIMIT 2000
SELECT * FROM departments LIMIT 2000
Table Name and SQL Query can be specified under Component tab.
Use tMap Component
Double click on tMap to open
Add output fields by dragging and dropping from table 1.
Similarlly connect with others tables
Convert data to JSON format
Sending data to Vizard requires making a HTTP request to Vizard's REST API endpoint. Data will be sent in request body as a JSON string. Hence, you need to convert row based data to JSON data. Add
tWriteJSONField component to convert data to JSON format and connect to tMap to receive data from tMap.
Vizard expects request body to be in this format, with data rows in docs key. We will configure the JSON structure accordingly.
Double click on
tWriteJSONField component to configure the tree. Add
docsas Sub-element under
Set a fixed value
elementas Sub-element under
Set it as
Add fields to the element
Create as sub-element of target node.
tWriteJSONFieldcomponent output data schema.
You can send new fields that are required to send data to Vizard. You can add those fields in tMap component. Add variable
vizardCounter and tableName.
Set the value to
Mathematical.SDIV(Numeric.sequence("s1",1,1),1000),this will increment the value of
vizardCounter by 1 for every 1000 rows. And Use tableName (Defined Teable Name in Vizard) for tableName field.
vizardCounter and tableNameto output.
Remove root node and group by
vizardCounter,to group all the rows with same
vizardCounterand make a request. This will help you send data in batches.
tableName in root, as shown below.
Send Data to Vizard
To send data to Vizard, add a
tRESTClient component and Connect with
component tab, edit the
Basic Settings as shown. Note that
app_id values can be obtained under
API TOKENSsubtab under
DATAtab in Vizard. Click here to know how to get team_id and app_id.
Advanced Setting add HTTP header
"Authorization" and set value as shown. Note that the value of token can be obtained from
API TOKENS subtab under
DATA tab in Vizard. Click here to know more about generating token.
Drop JSON Request Root and Deselect
Convert Response to DOM Document