Data Sorting

You often need to sort data when processing it. This document describes how to use Spark SQL for data sorting in FineDataLink.

You can use the ORDER BY clause in Spark SQL to perform a global sorting on the query result set. For large datasets, this process will take a long time to execute.

DESC: It stands for descending order. 

ASC: It stands for ascending order.

Example 1: You need to sort the daily sales amount in the Daily_Sales_Summary table in ascending order.

select * FROM
DB Table Input
order by DB Table Input.`Daily_Sales`  ASC

Note:

The table name corresponds to the name of the upstream node, which should be input by clicking the button next to Input Source. Therefore, you cannot simply copy the statement.

You can click Data Preview to view the results sorted in ascending order, as shown in the following figure. 

Example 2: You need to sort the sales amount in the Daily_Sales_Summary dataset in descending order.

select * FROM
DB Table Input-3
order by DB Table Input-1.`Daily_Sales`  DESC

Note:

The table name corresponds to the name of the upstream node, which should be input by clicking the button next to Input Source. Therefore, you cannot simply copy the statement.

You can click Data Preview to view the results sorted in descending order, as shown in the following figure.

SORT BY and DISTRIBUTE BY

Typically, local sorting is implemented by the combination of SORT BY and DISTRIBUTE BY.

SORT BY performs local sorting, which ensures that data within each group is sorted in order, yet it cannot guarantee that the entire dataset is globally sorted unless the data belongs to only one group. Its advantage is that local sorting can greatly improve the efficiency of subsequent global sorting.

DISTRIBUTE BY sorts data in the form of groups. Its functionality is somewhat similar to that of GROUP BY.

Example 1: You can sort the daily sales amount in the Daily_Sales_Summary table in ascending order, grouped by store number.

select * FROM
DB Table Input-2
distribute by DB Table Input-2.`Store`  sort by  DB Table Input-2.`Store`, DB Table Input-2.`Daily_Sales`  ASC

The store numbers are sorted in ascending order, and the daily sales amount of the same store is sorted in ascending order, as shown in the following figure.

CLUSTER BY

CLUSTER BY combines the functionalities of DISTRIBUTE BY and SORT BY.

Example 1:

select * FROM
DB Table Input-3
distribute by DB Table Input-3.`Store`  sort by DB Table Input-3.`Store`   ASC

Example 2:

select * FROM
DB Table Input-3
cluster by DB Table Input-3.`Store`

Note:

The SQL statements in Example 1 and Example 2 are equivalent, and the query result sets they return are identical.

You can click Data Preview to view the results, where the store numbers are sorted in ascending order, as shown in the following figure.