Configuring the query of the customer records of interest - 7.3

MySQL
Version
7.3
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-21

Procedure

  1. Double-click tMySQLInvalidRows 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 tMysqlConnection 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 accept the propagation prompted by the pop-up dialog box.
  8. In the Table name field, enter or browse to the database table to be checked, cust in this example.
  9. In the Where clause field, type in the where clause to be used, in addition to the given data quality rule, to query the customer data of interest. In this scenario, enter `cust`.`Email` like 's%' within the 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 scenario, select cust_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 Libraries > Rules > SQL of the DQ Repository. The cust_age rule can be imported from the TDQEEDEMOJAVA project.
    For further information about the DQ Repository and its items and about how to import the data quality demo project, see the Talend Studio User Guide
  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 allows you to select the customers whose ages count 20 or more.