Hive 简明教程
Hive - Built-in Operators
本章解释了 Hive 的内置运算符。Hive 中有四种类型的运算符：
This chapter explains the built-in operators of Hive. There are four types of operators in Hive:
-
Relational Operators
-
Arithmetic Operators
-
Logical Operators
-
Complex Operators
Relational Operators
这些运算符用于比较两个操作数。下表描述了 Hive 中可用的关系运算符：
These operators are used to compare two operands. The following table describes the relational operators available in Hive:
Operator |
Operand |
Description |
A = B |
all primitive types |
TRUE if expression A is equivalent to expression B otherwise FALSE. |
A != B |
all primitive types |
TRUE if expression A is not equivalent to expression B otherwise FALSE. |
A < B |
all primitive types |
TRUE if expression A is less than expression B otherwise FALSE. |
A ⇐ B |
all primitive types |
TRUE if expression A is less than or equal to expression B otherwise FALSE. |
A > B |
all primitive types |
TRUE if expression A is greater than expression B otherwise FALSE. |
A >= B |
all primitive types |
TRUE if expression A is greater than or equal to expression B otherwise FALSE. |
A IS NULL |
all types |
TRUE if expression A evaluates to NULL otherwise FALSE. |
A IS NOT NULL |
all types |
FALSE if expression A evaluates to NULL otherwise TRUE. |
A LIKE B |
Strings |
TRUE if string pattern A matches to B otherwise FALSE. |
A RLIKE B |
Strings |
NULL if A or B is NULL, TRUE if any substring of A matches the Java regular expression B , otherwise FALSE. |
A REGEXP B |
Strings |
Same as RLIKE. |
Example
让我们假设 employee 表由名为 Id、Name、Salary、Designation 和 Dept 的字段组成，如下所示。生成一个查询来检索 Id 为 1205 的员工详细信息。
Let us assume the employee table is composed of fields named Id, Name, Salary, Designation, and Dept as shown below. Generate a query to retrieve the employee details whose Id is 1205.
+-----+--------------+--------+---------------------------+------+
| Id | Name | Salary | Designation | Dept |
+-----+--------------+------------------------------------+------+
|1201 | Gopal | 45000 | Technical manager | TP |
|1202 | Manisha | 45000 | Proofreader | PR |
|1203 | Masthanvali | 40000 | Technical writer | TP |
|1204 | Krian | 40000 | Hr Admin | HR |
|1205 | Kranthi | 30000 | Op Admin | Admin|
+-----+--------------+--------+---------------------------+------+执行以下查询以使用上述表格检索员工详细信息：
The following query is executed to retrieve the employee details using the above table:
hive> SELECT * FROM employee WHERE Id=1205;在成功执行查询后，您可以看到以下响应：
On successful execution of query, you get to see the following response:
+-----+-----------+-----------+----------------------------------+
| ID | Name | Salary | Designation | Dept |
+-----+---------------+-------+----------------------------------+
|1205 | Kranthi | 30000 | Op Admin | Admin |
+-----+-----------+-----------+----------------------------------+执行以下查询以检索工资大于或等于 40000 卢比的员工详细信息。
The following query is executed to retrieve the employee details whose salary is more than or equal to Rs 40000.
hive> SELECT * FROM employee WHERE Salary>=40000;在成功执行查询后，您可以看到以下响应：
On successful execution of query, you get to see the following response:
+-----+------------+--------+----------------------------+------+
| ID | Name | Salary | Designation | Dept |
+-----+------------+--------+----------------------------+------+
|1201 | Gopal | 45000 | Technical manager | TP |
|1202 | Manisha | 45000 | Proofreader | PR |
|1203 | Masthanvali| 40000 | Technical writer | TP |
|1204 | Krian | 40000 | Hr Admin | HR |
+-----+------------+--------+----------------------------+------+Arithmetic Operators
这些运算符支持操作数上的各种常见算术运算。它们都返回数字类型。下表描述了 Hive 中可用的算术运算符：
These operators support various common arithmetic operations on the operands. All of them return number types. The following table describes the arithmetic operators available in Hive:
Operators |
Operand |
Description |
A + B |
all number types |
Gives the result of adding A and B. |
A - B |
all number types |
Gives the result of subtracting B from A. |
A * B |
all number types |
Gives the result of multiplying A and B. |
A / B |
all number types |
Gives the result of dividing B from A. |
A % B |
all number types |
Gives the reminder resulting from dividing A by B. |
A & B |
all number types |
Gives the result of bitwise AND of A and B. |
A |
B |
all number types |
Gives the result of bitwise OR of A and B. |
A ^ B |
all number types |
Gives the result of bitwise XOR of A and B. |
~A |
all number types |
Logical Operators
运算符是逻辑表达式。它们都返回 TRUE 或 FALSE。
The operators are logical expressions. All of them return either TRUE or FALSE.
Operators |
Operands |
Description |
A AND B |
boolean |
TRUE if both A and B are TRUE, otherwise FALSE. |
A && B |
boolean |
Same as A AND B. |
A OR B |
boolean |
TRUE if either A or B or both are TRUE, otherwise FALSE. |
A |
B |
|
boolean |
Same as A OR B. |
NOT A |
boolean |
TRUE if A is FALSE, otherwise FALSE. |
!A |
Example
以下查询用于检索部门为 TP 且薪水大于 40000 卢比的员工详细信息。
The following query is used to retrieve employee details whose Department is TP and Salary is more than Rs 40000.
hive> SELECT * FROM employee WHERE Salary>40000 && Dept=TP;在成功执行查询后，您可以看到以下响应：
On successful execution of the query, you get to see the following response:
+------+--------------+-------------+-------------------+--------+
| ID | Name | Salary | Designation | Dept |
+------+--------------+-------------+-------------------+--------+
|1201 | Gopal | 45000 | Technical manager | TP |
+------+--------------+-------------+-------------------+--------+Complex Operators
这些运算符提供一个表达式来访问复杂类型元素。
These operators provide an expression to access the elements of Complex Types.
Operator |
Operand |
Description |
A[n] |
A is an Array and n is an int |
It returns the nth element in the array A. The first element has index 0. |
M[key] |
M is a Map<K, V> and key has type K |
It returns the value corresponding to the key in the map. |
S.x |
S is a struct |
It returns the x field of S. |