Data Deduplication

Expected Effect

When building a data warehouse, you may need to deduplicate data, as duplicate records constitute dirty data. In this case, you can use the GROUP BY clause in the Spark SQL operator for processing.

For example, dirty data will appear if an order is accidentally triggered twice, resulting in two duplicate records for the same order. You can retain only one row by removing the duplicates, as shown in the following figure.

You can perform deduplication by using the GROUP BY clause in Spark SQL.

You can download the example data: Orderlist.xlsx

1. Click Data Development and create a scheduled task.

Add a Data Transformation node on the scheduled task editing page, as shown in the following figure.

Enter the Data Transformation node, add a DB Table Input operator, and configure it to extract the data table that needs to be deduplicated, as shown in the following figure.

2. Add a Spark SQL operator and use the GROUP BY clause to deduplicate the data, as shown in the following figure.

select 
 `customer`,`area` ,`date` , `sales` 
from 
DB Table Input
group by  `customer`,`area` ,`date` , `sales` 

Note:

Specify the data table and the field to be queried in the SQL statement by selecting them instead of entering their names.

2. Click Data Preview to view the deduplicated data, as shown in the following figure.

Then use the DB Table Output operator to output the deduplicated data to a specified data table, as shown in the following figure.

Note:

If you need to write data back to the original table or check duplicate entries, you can see Function Description of Data Comparison.