Scenario 4: Retrieving data from a MySQL database using the data source on Talend Runtime side to set up the database connection - 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

This scenario describes a two-component Job that retrieves data from a MySQL database table and displays the data on the console. The Job will be deployed in Talend Runtime and will use the data source created on Talend Runtime to connect to the database defined in a Runtime configuration file, so this scenario is relevant only if you are using one of Talend's ESB products.

Prerequisites:

A MySQL data source has been created in Talend Runtime Container. To do so, proceed as follows:

  1. Install the MySQL JDBC driver by running the following bundle:install command in Talend Runtime Container:

    karaf@trun()> bundle:install mvn:mysql/mysql-connector-java/5.1.18
  2. Install the database connection pool by running the following bundle:install command in Talend Runtime Container:

    karaf@trun()> bundle:install -s mvn:commons-dbcp/commons-dbcp/1.4
  3. Copy the data source configuration file datasource-mysql.xml under the folder <TalendRuntimePath>/add-ons/datasources/dataservice to the folder <TalendRuntimePath>/container/deploy and then in the file update the username and password property values required to connect to your database.

    <?xml version="1.0" encoding="UTF-8"?>
    <blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0"
        default-activation="lazy">
    
        <bean id="mysqlDataSource" class="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
            <property name="url" value="jdbc:mysql://localhost:3306/test"/>
            <property name="user" value="root"/>
            <property name="password" value="talend"/>
        </bean>
    
        <bean id="dataSource" class="org.apache.commons.dbcp.datasources.SharedPoolDataSource" destroy-method="close">
            <property name="connectionPoolDataSource" ref="mysqlDataSource"/>
            <property name="maxActive" value="20"/>
            <property name="maxIdle" value="5"/>
            <property name="maxWait" value="-1"/>
        </bean>
    
        <service ref="dataSource" interface="javax.sql.DataSource">
            <service-properties>
                 <entry key="osgi.jndi.service.name" value="jdbc/sample"/>
            </service-properties>
        </service>
    </blueprint>
    

    Note that the property osgi.jndi.service.name in the data source configuration file defines the data source alias, jdbc/sample, in this example. The alias will be used in the database component to connect to the database defined in the data source configuration file when the Job is deployed in Talend Runtime.

    For more information about how to use the data source in Talend Runtime Container, see Talend ESB Container Administration Guide and Talend ESB Studio User Guide.

Adding and linking the components

  1. Create a new Job and add a tMysqlInput component and a tLogRow component by typing their names in the design workspace or dropping them from the Palette.

  2. Link the tMysqlInput component to the tLogRow component using a Row > Main connection.

Configuring the components

  1. Double-click tMysqlInput to open its Basic Settings view in the Component tab.

  2. In the Table Name field, enter the name of the table from which the data will be retrieved. In this example, it is student.

  3. Click the [...] button next to Edit schema to open the [Schema] dialog box and define the schema of the table student by adding two columns: sid of Integer type and sname of String type.

  4. Click the Guess Query button to fill the Query field with the SQL query statement to be used to retrieve data from the specified table.

  5. Select the Specify a data source alias check box and in the Data source alias field displayed enter the data source alias specified in the data source configuration file datasource-mysql.xml on Talend Runtime side. In this example, it is jdbc/sample.

  6. Double-click the tLogRow component to open its Basic settings view.

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

Building the Job and deploying it in Talend Runtime Container

  1. Press Ctrl + S to save the Job.

  2. Start the Talend Runtime Container.

  3. In the Repository tree view of your Talend Studio, right-click the Job and from the contextual menu select Build Job to open the [Build Job] dialog box.

  4. Select OSGI Bundle For ESB from the Select the build type drop-down list.

  5. Click the Browse button next to the To archive file field to specify where you will store the Job archive file. In this example, we will export the Job directly to the deploy folder <TalendRuntimePath>/container/deploy so that the Job can be run immediately in Talend Runtime after being exported.

    For more information about how to build a Job, see Talend Studio User Guide.

  6. Click Finish to close the [Build Job] dialog box.

    As you can see on the Talend Runtime Container console, the Job starts to run immediately after being deployed and the data in the student table is retrieved and displayed on the console.