In this section, we will learn how to centralize a MySQL database connection through Metadata in Talend Studio for the Data Integration platform.
MySQL Database connection completed in the following two steps in the Talend studio:
- Set Up a Database connection
- Retrieve the table schemas
Step1: Set up a database connection
To set up a relationship between a Database and Talend, follow the below process:
- First, open the Talend studio, go to the Repository panel, and expand the Metadata
- Right-click on the DB Connections, and select Create connection in the popup menu, as shown in the below image:
Repository → Metadata → DB Connections
The Database connection will be completed in two steps:
- After, click on the Create Connection option in the popup menu, the [Database Connection] window will appear on the screen where we will fill the mandatory fields like Name, and other areas like Purpose, Description, and so on.
- After filling all the information, click on the Next button, as you can see in the below screenshot:
In this step, we will be defining the connection properties of the Database.
For this, we are selecting MYSQL in the DB type field as we can see in the below screenshot:
- After choosing the Db type, we fill all the necessary information about MySQL database like login, password, server, Database,
Note: Create your Database while connecting to the Talend studio's DB Connection.
- After completing all the connection details, click on the Test Connection button to check whether the filled Database details are correct or not.
- If the given details are correct, it will show the connection successfully message in the message box: Otherwise, it will show an error as we can see in the below screenshot:
- Click on the Finish button to complete the connection properties.
Step2: Retrieving table schemas
In this step, we are retrieving the table schemas from the database connection, which we have created above.
- For this, go to the Repository panel, right-click on the MySQL_test_job in the DB connection node.
- Then, select the Retrieve schema option in the popup menu as you can see in the below image:
- A new [Schema] window will appear on the screen where we can filter and show the various objects like Table, Views, and Synonyms in our database connection.
- We can also select the tables, and define the table schemas as we can notice in the below screenshot:
Note: If there are no tables available for retrieving from the selected database, or we do not have the right to access the database, one error message will appear.
Filtering database objects
We can filter the database objects using two options in the Select filter conditions, which are as follows:
- Use the name filter
- Use the SQL filter
For filtering the database according to their name filter, follow the below process:
- If we select the Use the name filter option from the Select Filter Conditions
- Select the multiple checkboxes of the database objects as (Table, view, and synonyms) in the Select type section for filtering and displaying the objects.
Note: The available options can differ based on the selected database.
- In the Set the Name Filter section, click on the Edit button to open an Edit Filter Name dialog box. We can enter the filter name in the particular dialog box, which we want to use.
- If we want to recover the database objects, whose name starts with "S", then enter the Filter Name as "S%".
- Or, if we want to recover all the database objects, whose name ends with "type", then enter "%type" as our Filter Name.
- Click on the OK button to close the dialog box as we can see in the below screenshot:
- Then, click on the Next button to open the new view on the window that lists the filtered database objects.
For filtering the database objects with the help of SQL query, follow the below process:
- Select the Use SQL Filter option in the Select Filter Conditions section, enter the SQL query in the Set SQL Filter
- And, click on the Next button to open a new view which has the listed filter database objects as we can see in the below image:
Note: Click on the Check connection button, which is presented on the schema window to verify the database connection status if no schema is visible on the list.
Selecting tables and defining table schemas
In the above image, we have filtered the list of database objects.
Follow the below process to load the schemas of the desired objectives in our Repository.
- We are selecting the one or more database objects on the list and click on the Next button to open a new view on the window where we can see the schema of the selected object as shown in the below image:
- We can change schemas if we needed, and also check the data type in the Type column whether it is correct or not.
Some of the commonly used Talend data types are as follows:
- List: It is a space-separated list of a primitive type element in an XML schema definition, and it is defined using the XSD: list
- Object: It is a generic Talend data type that allows processing the data without considering its content.
- Document: A document is a data type, which allows the processing of an entire XML document even without considering its content.
- But here, the schema displayed on the Schema panel based on the first table, which is selected in the list of the schemas loaded (left panel) by default.
- We can change the name of the schema based on our needs, and we can also customize the schema structure in the schema panel.
- The toolbar allows us to add, remove, or move columns in our schema, and we can also load an XML schema from a file or export the current schema as XML.
For retrieving a schema based on one of the loaded table schemas, follow the below process:
- Select the DB table schema name in the dropdown list, in our case we are selecting the emp_hr as DB table schema, and click on the Retrieve schema.
- Click on the OK button to confirm the changes.
- Then, click on the Finish button to complete the database schema creation.
To verify that the data is retrieved or not:
- Go to the Metadata section, in the Repository panel, and expand the Db connections where we can see that the retrieved schema is displayed in the Table schemas sub-folder as shown in the below screenshot:
We can drag and drop any table schema of the database connection from the Repository panel onto the Design workspace window as a new database component, or any existing component to reuse the Metadata.