Talend in Big Data

Pre-requisites:

• Access to a MySQL database.
• Download the 'tpbd_gettingstarted_source_files.zip' from the Talend_Downloads link and store the source file 'gettingstarted.sql' locally.

Step-by-Step Guide:

• Start by launching an instance of the MySQL Workbench.
• Click the Server > Data Import from the menu bar which will open the import wizard.
• Now select the Import option from the Self-Contained File option to find the location of the 'gettingstarted.sql' file.
• You can either define a new schema from the Click New option or select the schema according to which the data should be imported.
• Select the Start Import option at the lower right corner.

Output

You shall see that the 'gettingstarted' database is finally imported into the MySQL database.

2. Identifying the Inconsistencies/Anomalies in Data

To critically analyze the customer phone numbers and email addresses, we can learn how to utilize the Profiling perspective of the Studio. Here, we shall make use of some out-of-box indicators as well as patterns to showcase the consistent and inconsistent address data.

To clean the customer data, the Profiling Jobs are generated over the analysis of the result which can then monitor the evolution too. To browse over the inconsistent data, the user can utilize the Data Explorer perspective.

Step-by-Step Guide:

• Start by creating a column analysis of the customer's phone numbers and email addresses.
• From the Analysis editor, you can connect to the MySQL database where the data resides.
• With the help of the indicators, users can see statistics of the data like blanks, row count, or duplicate count.
• For matching phone numbers and email addresses, users can implement the standard patterns.
• To showcase the results in charts or tables, you can execute the analysis.
• For identifying invalid records, users can view the analyzed data.
• Users can remove the duplicated values from the data or any data records that are not following the standardized formats for email and phone numbers, by generating any out-of-box Jobs from analysis results.

3. Defining a Column Analysis

To validate the phone number and email in a MySQL database, users can create a column analysis from the Profiling perspective of the Studio and collect the statistics for it. The analysis runs on several columns but each column is analyzed separately and independently.

Pre-requisite:

Open the Profiling perspective in the Studio.

Step-by-Step guide:

• When you enter the Data Quality Repository view, you can go through the Analyses and click on New Analysis.
• Once the New Analysis is opened, users can type Basic Column Analysis in the search field.
• Once done, users can click Basic Column Analysis from the list.
• Users can enter a name in the Name field (mandatory field) for the analysis. Avoid using any space or special characters.
• To open the Analysis editor, the users need to set the purpose and description for that analysis.

Output

Below is how the output shall be seen. Users can see that the new analysis is given in the Data Quality Repository tree view under the Analysis folder.

4. Creating the Database Connection

To analyze the customer data, we first need to establish a connection to the MySQL database.

Pre-requisites:

• Open the Profiling perspective in the Studio.
• Create a column analysis and open the Analysis editor.
• Import the 'gettingstarted.sql' file.

Step-by-Step guide:

• In the Analysis editor, select the New Connection tab and open the Create New Connection window.
• Users can opt for the database connections via the Connection Type list.
• To create the database connection, select the Finish button. Then users need to list as part of the Metadata node.
• Once done, users can open a new step and expand the DB connection.
• Select the defined table name and select all the required check boxes for the columns where users need to create the analysis as seen below.
• Select the OK button for closing the wizard. Similarly, you can list other columns and Refresh the data for displaying the actual data as seen below.

5. Setting System Indicators

To offer easier statistics like blanks, row count, and duplicate count, users can implement the column analysis using out-of-box indicators over the phone number as well as the email columns.

Step-by-Step Guide:

• Select the indicators from the Data Preview section to open the Indicator Selection window in the Analysis editor.
• Users can expand Simple Statistics and select the Blank Count, Row Count, and Duplicate Count to verify the consistency of the data. You want to see the row, blank and duplicate counts in the Email and Phone columns to see how consistent the data is.
• Users can select the settings icon and set thresholds on the Email and Phone columns. The threshold can be set as 0 in the Upper threshold field. This writes the values in red color for the count of the duplicate and blank values in the analysis results.

6. Setting Patterns

With the help of pre-specified patterns, users can identify any records that are not following the standardized format for the phone number or email by matching the content. This helps to identify the content, structure, and quality of phone numbers and emails. The percentage of data that matched or did not match the standard formats can be identified.

Step-by-Step Guide:

• In the Data Preview section, select the add_pattern icon for opening the Pattern Selector window.
• You can now expand the Regex > internet, where the users can select the check box for the email address and continue with the OK button. This way the pattern gets added in the Analyzed Columns section.
• Similar steps could be followed for the Phone number column and it too gets added in the Analyzed Columns section.
• In the Setting section, the user can set 98.0 in the Lower threshold (%) field. This ensures that the records with match pattern less than 98% eventually gets written in red.

7. Showing Analysis Results

We are now ready for executing and displaying the analysis results as charts and tables, once the column analysis and setting of the indicators and patterns are finalized.

Step-by-Step guide:

• To start analyzing the results, users need to select Java from the Execution engine list in the Analysis Parameters.
• You can either press the F6 button or simply the Run button to execute the analysis.
• You shall see that the editor switched to the Analysis Results view. You can see the details related to the statistic and pattern-matching results in the generated charts.

The chart generated for the Email column:

The chart generated for the Phone column:

Output:

We see that the count of the records is obtained for the phone number and email columns for both the matched and not-matched records. You will also see the results for blank and duplicate counts telling that the data is not consistent and must be corrected before processing.

Browsing Non-Match Data

As we saw from the column analysis, the data is inconsistent and now we need to scan for all the data records which don't follow the standardized format. This helps to understand what can be done to correct the inconsistent data and start analyzing the need for validating and cleansing the data.

Step-by-step guide:

• Users can view all the blank rows in the Email column, by selecting Blank Count in the statistic results from the Analysis Results view.
• Users can view the invalid values in the Email column, by selecting Pattern Matching of the Email column in the Analysis Results.

Cleansing Customer Contact Information

Once the customer data is profiled and the issue is identified, users can take some actions to properly clean the data. This can be easily done by generating out-of-the-box Talend Jobs. With these jobs, duplicate values from the columns could be removed along with the values that do not satisfy the pattern. This tells the users what needs to be resolved and identification of the issues that are present in the address data.

Removing Duplicate Values

Once the duplicated values are identified from the profiling results in the Email and Phone columns, out-of-box jobs can be created to separate distinct values from duplicate records. These jobs show all the duplicates in a rejected delimited file. Also, it then writes the unique values in the database for further analysis.

Step-by-step guide:

• From the Profiling perspective, open the column analysis and validate the Analysis Results in the editor.
• Users can identify duplicates from the Duplicate Count option present in the Simple Statistics.
• You shall see that the Integration perspective shows the created Job under the Repository tree view.
• According to the user's requirement, the tMysqlInput, tUniqueRow, and tMysqlOutputBulkExec components are automatically configured for analysis. Here, tMysqlOutputBulkExec is used to write unique records to a new table while with tFileOutputDelimited, the duplicate records are written in an output delimited file.
• You can click F6 for running the Job.

Output:

All the duplicated records are written back to the output file while the distinct unique records are written to a new table.

Removing Non-Matching Values

We saw that some of the data records didn't follow the standard email and phone formats from the pattern-matching results. You can generate out-of-box jobs to recover all the non-matching rows from their columns.

Step-by-step guide:

• From the Profiling perspective, open the column analysis and select the Analysis Results.
• Then in the Pattern Matching tables, you can easily Generate a Job from the option present.
• Once you press the Finish icon, the job is created.
• Now, the Integration perspective shows the generated Job in the Repository view.
• Users can make use of this job for performing the ETL process. This would be helpful for writing the matched and unmatched data into two separate output files.
• tMysqlInput is automatically configured as per your connection while tPatternCheck is configured as per the column analysis.
• You can change either the default name or path of the output files as per the requirement, and select the F6 button to run the Job.
• With the tFileOutputDelimited component, the Data Viewer option opens a view of the data where the matched and unmatched data is seen.

Output:

Similarly, an automatic job can be created to standardize the formats related to phone numbers or emails. Users can design a Job that adheres to the international format via the tStandardizePhoneNumber component.