Check the master table in a database and output the matched data
This section describes how to check the master table in a database for the input data and output the matched data using the Replace by DB Table logic.
This tutorial uses the database and sample data created in Write data from Amazon S3 to a database (MySQL).
For details on how to use the sample data, refer to How to use sample data.
Do the above tutorial first, and then do this tutorial.
The sample project reads a CSV file and searches the product inventory master table using LocalProductCode as the key to retrieve the corresponding InventoryQty.
If an applicable LocalProductCode exists in the product inventory master table, the data is output to another CSV file.
- Processing flow
- Key functions
-
-
Replace by DB Table logic
This logic replaces the input character string according to a DB table and returns the value of the string.
You can set it from String > Conversion > Replace by DB Table of the tool palette in Mapper.
-
- Tips for this operation
-
If the value of LocalProductCode in the input data is found in the product inventory master table, the Replace by DB Table logic replaces that value with the corresponding InventoryQty in the product inventory master table.
By extracting only the replaced records, you can output only the data for the values that are found in the master data.
Take the following steps in order to check the master table in a database and output the matched data.
The sample data to execute the above processing is provided.
Download the sample data from Scenario list. The procedure for using the sample data is as follows:
Sample data file name: tutorial_DB_matched_data.zip
-
Extract the sample data
Extract the sample data file, tutorial_DB_matched_data.zip.
-
Prepare the sample data
The data extracted from tutorial_DB_matched_data.zip is the sample data.
-
Upload the sample data
Refer to Upload a CSV file to storage to upload the sample data to the storage.
Procedures
For the procedure from uploading a CSV file to launching Designer, refer to Create a script that processes a CSV file.
To configure the process flow and data flow, refer to the script images in Description.
(1) Place the Read CSV File operation and the Write CSV File operation
-
From the tool palette, drag File > CSV > Read CSV File onto the script canvas.
-
Configure the Read CSV File operation as shown below.
Required settings tab
= Remarks =-
Specify input.csv as the CSV file to be read.
-
After you specify input.csv, if you select Read column names from first row of file, the column list is read automatically.
-
-
From the tool palette, drag Conversion > Basic > Mapping onto the script canvas.
-
From the tool palette, drag File > CSV > Write CSV File onto the script canvas.
-
Configure the Write CSV File operation as shown below.
Required settings tab
= Remarks =-
Specify output.csv of the sample data as the CSV file to write to.
-
After you specify output.csv, if you select Read column names from first row of file, the column list is read automatically.
-
-
Link the component icons as follows:
(2) Create a mapping in the mapping canvas
-
Double-click the mapping icon to open the Mapper editor.
-
In the Mapper editor, create a process that retrieves the value of InventoryQty from the product inventory master table using LocalProductCode as the key to extract only matching records.
Configure the mapping canvas as shown below.
-
In the Replace by DB Table logic, select LocalProductCode in Key column as the key for matching and select InventoryQty in Value column as the replacement value.
-
If the input value matches the value of LocalProductCode in the InventoryMaster table, the value will be replaced with the corresponding value of InventoryQty.
|
No. |
Logic name |
Description |
Location on tool palette |
||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
(1) |
If Loop |
This logic repeatedly outputs only the data that matches the conditions. |
Loop > Conditional loop > If Loop |
||||||||||||
|
(2) |
Different |
This logic determines whether two input character strings are different. |
Condition > String > Different |
||||||||||||
|
(3) |
Replace by DB Table |
This logic replaces the input character string according to a DB table and returns the value of the string. Configure the Required settings tab as shown below.
|
String > Conversion > Replace by DB Table |
By extracting only the replaced records, you can output only the data for the values that are found in the master data.
-
Execute the script. If it's completed normally, the operation is successful.
For details about how to execute scripts , refer to Execute the script in Create a script that processes a CSV file.