SELECT clause - Amazon OpenSearch Service

SELECT clause

Note

To see which AWS data source integrations support this SQL command, see Supported OpenSearch SQL commands and functions.

OpenSearch SQL supports a SELECT statement used for retrieving result sets from one or more tables. The following section describes the overall query syntax and the different constructs of a query.

Syntax 

select_statement 
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select_statement, ... ]
[ ORDER BY 
    { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] 
    [ , ... ] 
    } 
]
[ SORT BY 
    { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] 
    [ , ... ] 
    } 
]
[ WINDOW { named_window [ , WINDOW named_window, ... ] } ]
[ LIMIT { ALL | expression } ]

While select_statement is defined as:

SELECT [ ALL | DISTINCT ] { [ [ named_expression ] [ , ... ] ] }
FROM { from_item [ , ... ] }
[ PIVOT clause ]
[ UNPIVOT clause ]
[ LATERAL VIEW clause ] [ ... ]
[ WHERE boolean_expression ]
[ GROUP BY expression [ , ... ] ]
[ HAVING boolean_expression ]

Parameters

  • ALL

    Selects all matching rows from the relation and is enabled by default.

  • DISTINCT

    Selects all matching rows from the relation after removing duplicates in results.

  • named_expression

    An expression with an assigned name. In general, it denotes a column expression.

    Syntax: expression [[AS] alias]

  • from_item

    Table relation

    Join relation

    Pivot relation

    Unpivot relation

    Table-value function

    Inline table

    [ LATERAL ] ( Subquery )

  • PIVOT

    The PIVOT clause is used for data perspective. You can get the aggregated values based on specific column value.

  • UNPIVOT

    The UNPIVOT clause transforms columns into rows. It is the reverse of PIVOT, except for aggregation of values.

  • LATERAL VIEW

    The LATERAL VIEW clause is used in conjunction with generator functions such as EXPLODE, which will generate a virtual table containing one or more rows.

    LATERAL VIEW will apply the rows to each original output row.

  • WHERE

    Filters the result of the FROM clause based on the supplied predicates.

  • GROUP BY

    Specifies the expressions that are used to group the rows.

    This is used in conjunction with aggregate functions (MIN, MAX, COUNT, SUM, AVG, and so on) to group rows based on the grouping expressions and aggregate values in each group.

    When a FILTER clause is attached to an aggregate function, only the matching rows are passed to that function.

  • HAVING

    Specifies the predicates by which the rows produced by GROUP BY are filtered.

    The HAVING clause is used to filter rows after the grouping is performed.

    If HAVING is specified without GROUP BY, it indicates a GROUP BY without grouping expressions (global aggregate).

  • ORDER BY

    Specifies an ordering of the rows of the complete result set of the query.

    The output rows are ordered across the partitions.

    This parameter is mutually exclusive with SORT BY and DISTRIBUTE BY and can not be specified together.

  • SORT BY

    Specifies an ordering by which the rows are ordered within each partition.

    This parameter is mutually exclusive with ORDER BY and can not be specified together.

  • LIMIT

    Specifies the maximum number of rows that can be returned by a statement or subquery.

    This clause is mostly used in the conjunction with ORDER BY to produce a deterministic result.

  • boolean_expression

    Specifies any expression that evaluates to a result type boolean.

    Two or more expressions may be combined together using the logical operators ( AND, OR ).

  • expression

    Specifies a combination of one or more values, operators, and SQL functions that evaluates to a value.

  • named_window

    Specifies aliases for one or more source window specifications.

    The source window specifications can be referenced in the widow definitions in the query.