Help Center/ Data Lake Insight/ Spark SQL Syntax Reference/ SELECT/ Basic Statements
Updated on 2024-11-08 GMT+08:00
View PDF
Share

Basic Statements

Function

This statement is a basic query statement and is used to return the query results.

Syntax

1
2
3
4
5
6
7
 
SELECT [ALL | DISTINCT] attr_expr_list FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_name_list]
  [ORDER BY col_name_list][ASC | DESC]
  [CLUSTER BY col_name_list | DISTRIBUTE BY col_name_list]
  [SORT BY col_name_list]]
  [LIMIT number];

Keywords

Table 1 SELECT keywords

Parameter

Description

ALL

The keyword ALL is used to return all matching rows from a database, including duplicates. It can only be followed by an asterisk (*); otherwise, an error will occur.

This is the default behavior in SQL statements and is typically not written explicitly. If neither ALL nor DISTINCT is specified, the query result will include all rows, even if they are duplicates.

DISTINCT

When the DISTINCT keyword is used in a SELECT statement, the system removes duplicate data from the query results to ensure uniqueness.

WHERE

Specifies the filter criteria for a query. Arithmetic operators, relational operators, and logical operators are supported.

where_condition

Filter criteria.

GROUP BY

Specifies the grouping field. Single-field grouping and multi-field grouping are supported.

col_name_list

Field list

ORDER BY

Sort the query results.

ASC/DESC

ASC sorts from the lowest value to the highest value. DESC sorts from the highest value to the lowest value. ASC is the default sort order.

CLUSTER BY

CLUSTER BY is used to bucket the table according to the bucketing fields and then sort within the bucketed table. If the field of DISTRIBUTE BY is the same as the field of SORT BY and the sorting is in descending order, the combination of DISTRIBUTE BY and SORT BY achieves the same function as CLUSTER BY.

DISTRIBUTE BY

Specifies the bucketing fields without sorting the table.

SORT BY

The objects will be sorted in the bucket.

LIMIT

LIMIT is used to limit the query results. Only INT type is supported by the number parameter.

Precautions

  • The table to be queried must already exist, or an error message will be displayed.
  • When submitting SQL statements to display binary data on the DLI management console, the binary data is converted to Base64.

Example

To filter the record, in which the name is Mike, from the student table and sort the results in ascending order of score, run the following statement:

1
2
3
 
SELECT * FROM student
  WHERE name = 'Mike'
  ORDER BY score;
Parent topic: SELECT