Queries

Description

Hive dialect supports a commonly-used subset of Hive’s DQL. The following lists some parts of HiveQL supported by the Hive dialect.

Syntax

The following section describes the overall query syntax. The SELECT clause can be part of a query which also includes common table expressions (CTE), set operations, and various other clauses.

  1. [WITH CommonTableExpression [ , ... ]]
  2. SELECT [ALL | DISTINCT] select_expr [ , ... ]
  3.   FROM table_reference
  4.   [WHERE where_condition]
  5.   [GROUP BY col_list]
  6.   [ORDER BY col_list]
  7.   [CLUSTER BY col_list
  8.     | [DISTRIBUTE BY col_list] [SORT BY col_list]
  9.   ]
  10.  [LIMIT [offset,] rows]
  • The SELECT statement can be part of a set query or a sub-query of another query
  • CommonTableExpression is a temporary result set derived from a query specified in a WITH clause
  • table_reference indicates the input to the query. It can be a regular table, a view, a join or a sub-query.
  • Table names and column names are case-insensitive

WHERE Clause

The WHERE condition is a boolean expression. Hive dialect supports a number of operators and UDFs in the WHERE clause. Some types of sub-queries are supported in WHERE clause.

GROUP BY Clause

Please refer to GROUP BY for more details.

ORDER BY Clause

The ORDER BY clause is used to return the result rows in a sorted manner in the user specified order. Different from SORT BY, ORDER BY clause guarantees a global order in the output.

Note: To guarantee global order, there has to be single one task to sort the final output. So if the number of rows in the output is too large, it could take a very long time to finish.

CLUSTER/DISTRIBUTE/SORT BY

Please refer to Sort/Cluster/Distributed BY for more details.

ALL and DISTINCT Clauses

The ALL and DISTINCT options specify whether duplicate rows should be returned or not. If none of these two options are given, the default is ALL (all matching rows are returned). DISTINCT specifies removal of duplicate rows from the result set.

LIMIT Clause

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.

LIMIT takes one or two numeric arguments, which must both be non-negative integer constants. The first argument specifies the offset of the first row to return and the second specifies the maximum number of rows to return. When a single argument is given, it stands for the maximum number of rows and the offset defaults to 0.

Examples

Following is an example of using hive dialect to run some queries.

Note: Hive dialect no longer supports Flink SQL queries. Please switch to default dialect if you’d like to write in Flink syntax.

  1. Flink SQL> create catalog myhive with ('type' = 'hive', 'hive-conf-dir' = '/opt/hive-conf');
  2. [INFO] Execute statement succeed.
  4. Flink SQL> use catalog myhive;
  5. [INFO] Execute statement succeed.
  7. Flink SQL> load module hive;
  8. [INFO] Execute statement succeed.
  10. Flink SQL> use modules hive,core;
  11. [INFO] Execute statement succeed.
  13. Flink SQL> set table.sql-dialect=hive;
  14. [INFO] Session property has been set.
  16. FLINK SQL> set sql-client.execution.result-mode=tableau;
  18. Flink SQL> select explode(array(1,2,3)); -- call hive udtf
  19. +----+-------------+
  20. | op |         col |
  21. +----+-------------+
  22. | +I |           1 |
  23. | +I |           2 |
  24. | +I |           3 |
  25. +----+-------------+
  26. Received a total of 3 rows
  28. Flink SQL> create table tbl (key int,value string);
  29. [INFO] Execute statement succeed.
  31. Flink SQL> insert into table tbl values (5,'e'),(1,'a'),(1,'a'),(3,'c'),(2,'b'),(3,'c'),(3,'c'),(4,'d');
  32. [INFO] Submitting SQL update statement to the cluster...
  33. [INFO] SQL update statement has been successfully submitted to the cluster:
  35. FLINK SQL> set execution.runtime-mode=batch; -- change to batch mode
  37. Flink SQL> select * from tbl cluster by key; -- run cluster by
  38. 2021-04-22 16:13:57,005 INFO  org.apache.hadoop.mapred.FileInputFormat                     [] - Total input paths to process : 1
  39. +-----+-------+
  40. | key | value |
  41. +-----+-------+
  42. |   1 |     a |
  43. |   1 |     a |
  44. |   5 |     e |
  45. |   2 |     b |
  46. |   3 |     c |
  47. |   3 |     c |
  48. |   3 |     c |
  49. |   4 |     d |
  50. +-----+-------+
  51. Received a total of 8 rows