Configuring the query of the customer records - Cloud - 8.0

MySQL
Version
Cloud
8.0
Language
English
Product
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
Module
Talend Studio
Content
Data Governance > Third-party systems > Database components (Integration) > MySQL components
Data Quality and Preparation > Third-party systems > Database components (Integration) > MySQL components
Design and Development > Third-party systems > Database components (Integration) > MySQL components
Last publication date
2024-02-29

Procedure

  1. Double-click tDBInvalidRows to open its Component view.
  2. In the Validation type field, select DQ rule validation in order to use the given data quality rule.
  3. Select Use an existing connection to reuse the connection that tDBConnection creates.
  4. Click the [...] button next to Edit schema to open the schema editor.
  5. Click the [+] button three times to add throw rows and rename them as Name, DOB and Email, respectively.
  6. In the DOB row, select VARCHAR in the DB type column, enter 19 in the Length column and 0 in the Precision column.
  7. Click OK to validate these changes and propagate the changes.
  8. In the Table name field, browse to or enter the database table to be checked, customers in this example.
  9. In the Where clause field, enter the clause to be used, in addition to the given data quality rule, to query the customer data.
    In this example, enter `customers`.`Email` like 's%' in quotation marks to retrieve the email records beginning with the letter s or S.
  10. In the DQ rule list field, select the data quality rule to be used. In this example, select customers_age. This rule is a demo rule reading 18<=((TO_DAYS(NOW())-TO_DAYS(DOB))/365) .
    You can check the available data quality rules in the DQ Repository, Libraries > Rules > SQL.
    For further information, see Importing a data quality demo project.
  11. Click Guess Query to generate the corresponding query clause that uses the Not clause.
  12. In the Query field, change 18 to 20 to modify the clause as the following: 
    "SELECT `Name`, `DOB`, `Email` FROM `crm`.`cust` 
WHERE (NOT ( 20>((TO_DAYS(NOW())-TO_DAYS(DOB))/365) )
AND `cust`.`Email` like 's%')"
    This clause lets you select the customers whose age is 20 or more.