Matching Multiple Comma-Separated Fields Based on the Grouping Sequence

Application Scenario

After fetching data from the interface, the data is stored in the format: {{Main Data}, {Sub-data 1, Sub-data 2}, {Sub-data a, Sub-data b}}. Each sub-data group contains multiple fields, which are stored as comma-separated values, as shown in the following figure.

In actual business scenarios, each sub-data item acts as a group, and it is desired that all sub-data items are matched according to the data order within the group (with items marked in the same color in the previous figure displayed in the same row). The expected output data is shown in the following figure.

 When using two Field-to-Row Splitting operators to split the Title and Branch fields separately, a Cartesian product is generated, as shown in the following figure.

The fields marked in yellow are not the actual required data.

Implementation Method

1. Use SQL statements to split the Title field by comma (,) into multiple substrings; then unnest each substring, where each row of the unnested result contains the position of the substring in the original value (pos field), the substring value (col field), and the Employee_ID (the primary key of the data table) which is a shared grouping identifier field.

2. The splitting logic for the Branch field is the same as for the Title field. The result is shown in the following figure.

3. Use the pos and Employee_ID fields as join fields to associate the split results, as shown in the following figure.

Retrieve the col and col1 fields from the joined result.

Simulated Data Source

You can download the sample data: Employee Information Table.xls.

In the examples of this document, data in the format of {{Main Data}, {Sub-data 1, Sub-data 2}, {Sub-data a, Sub-data b}} is stored in the database table. 

1. Create a scheduled task, drag a Data Transformation node onto the page, and enter the Data Transformation editing page.

2. Drag in a DB Table Input operator and fetch the data to be processed, as shown in the following figure.

Processing Data

1. Drag in a Spark SQL operator and configure it to use SQL statements to process the data, as shown in the following figure.

with tbl_time as (SELECT posexplode(split(DB Table Input.`Title`, ',')) AS (pos, col),DB Table Input.`Employee_ID` FROM DB Table Input), tbl_note as (SELECT posexplode(split(DB Table Input.`Branch`, ',')) AS (pos, col),DB Table Input.`Employee_ID` FROM DB Table Input) select tbl_time.`Employee_ID`,tbl_time.col as `Title`,tbl_note.col as `Branch` from tbl_time left join tbl_note on tbl_time.pos = tbl_note.pos and tbl_time.`Employee_ID`=tbl_note.`Employee_ID`

Note:

In the above statements, data in the format DB Table Input.`Title` needs to be generated by clicking the button next to Input Source.

The explanation of the SQL statement is as follows.

• Use SQL statements to split the Title field by comma (,), retrieve the split field, intra-group sequence number, and the shared identifier field Employee_ID, then form a temporary table tbl_time.

• Use SQL statements to split the Branch field by comma (,), retrieve the split field, intra-group sequence number, and the shared identifier field Employee_ID, then form a temporary table tbl_note.

• Use SQL  statements to join the tbl_time and tbl_note tables based on the intra-group sequence number (pos) and the shared identifier field (Employee_ID) to obtain the required results.

2. Click Data Preview, as shown in the following figure.

If you require the final preview data the final preview data to include the Last_Name, First_Name, Title_of_Courtesy, Birth_Date, and Hire_Date fields, modify the SQL statement to:

with tbl_time as (SELECT posexplode(split(DB Table Input-Copy.`Title`, ',')) AS (pos, col),DB Table Input-Copy.`Employee_ID`,DB Table Input-Copy.`Last_Name`,DB Table Input-Copy.`First_Name`,DB Table Input-Copy.`Title_of_Courtesy`,DB Table Input-Copy.`Birth_Date`,DB Table Input-Copy.`Hire_Date`FROM DB Table Input-Copy), 
tbl_note as (SELECT posexplode(split(DB Table Input-Copy.`Branch`, ',')) AS (pos, col),DB Table Input-Copy.`Employee_ID` FROM DB Table Input-Copy)
select tbl_time.`Employee_ID`,tbl_time.`Last_Name`,tbl_time.`First_Name`,tbl_time.`Title_of_Courtesy`,tbl_time.`Birth_Date`,tbl_time.`Hire_Date`,tbl_time.col as `Title_of_Courtesy`,tbl_note.col as `Branch` from tbl_time left join tbl_note 
on tbl_time.pos = tbl_note.pos and tbl_time.`Employee_ID`=tbl_note.`Employee_ID`

Note:

In the above statements, data in the format DB Table Input.`Title` needs to be generated by clicking the button next to Input Source.

Click Data Preview, as shown in the following figure.

Saving the Data

You could further process the data. In the example of this document, the data is saved to a database table.

Drag in a DB Table Output operator and configure it to output the processed data into a database table, as shown in the following figure.

Set Write Method to Add/Modify/Delete Data Based on Identifier Field, and tick Add.

Publishing the Task to Production Mode

You can click the Publish button to publish the task to Production Mode, as shown in the following figure.