Chapter 4

第 4 章

Hive Operators

Hive 运算符

Operators in Hive are special symbols or keywords used in expressions to perform operations on data values (columns, literals, etc.).

Hive 中的运算符是用于在表达式中对数据值（列、字面量等）执行操作的特殊符号或关键字。

They are categorized by their purpose — arithmetic, comparison, logical, relational, etc.

它们根据用途进行分类——算术、比较、逻辑、关系等。

Arithmetic Operators

算术运算符

Used for Mathematical calculation.

用于数学计算。

1
2

select 1+9 from table_name limit 5;
select 40*5 from table_name limit 5;

Find the Total Revenu.

计算总收入。

1
2
3

select productname, price* quantity as  totalsales
from products  p ,orders  o
where  p .productid = o .productid 

Give a 10 % discount on expensive goods.

给昂贵的商品打 10% 的折扣。

1
2
3
4
5

SELECT productid, productname, price,
       price - (price * 0.10) AS price_after_10pct_discount
FROM products
WHERE price > 1000
LIMIT 20;

Find orders with odd quantity (modulus) and fraction price per item 查找数量为奇数（取模）的订单以及每件商品的单价（分数形式）

1
2
3
4
5
6

SELECT orderid, quantity,
       (quantity % 2) AS quantity_mod_2,
       CAST(p.price AS DOUBLE) / o.quantity AS price_per_item
FROM orders o
JOIN products p ON o.productid = p.productid
WHERE o.quantity > 0;

Relational Operators

关系运算符

Like many programming languages Hive also supports many relational operators. Relational operators are used to compare two operands and generate a TRUE or FALSE value.

像许多编程语言一样，Hive 也支持许多关系运算符。关系运算符用于比较两个操作数并生成 TRUE（真）或 FALSE（假）值。

Used to Compare two values.

用于比较两个值。

Orders placed by a specific customer (custid = 1010)

特定客户 (custid = 1010) 下的订单

1
2
3

SELECT orderid, custid, productid, quantity, orderdate
FROM orders
WHERE custid = 1010;

Products cheaper than or equal to 500

价格低于或等于 500 的产品

1
2
3

SELECT productid, productname, price
FROM products
WHERE price <= 500;

Customers not from ‘India’

不是来自“India”的客户

1
2
3

SELECT custid, cust_name, country
FROM customer
WHERE country <> 'India';

Customer name starting with

以…开头的客户名称

1
2
3

SELECT custid, cust_name, country
FROM customer
WHERE cust_name  Like 'Customer_10%';

Logical Operators

逻辑运算符

Logical operators in Hive provide support for creating logical expressions. All returns Boolean TRUE, FALSE, or NULL depending upon the Boolean values of the operands. In this NULL behaves as an “unknown” flag.

Hive 中的逻辑运算符支持创建逻辑表达式。根据操作数的布尔值，所有运算符都返回布尔值 TRUE、FALSE 或 NULL。在这里，NULL 表现为一个“未知”标记。

They allow us to combine multiple conditions.

它们允许我们组合多个条件。

Orders in 2023 for expensive products (price > 1000) 2023 年昂贵产品 (price > 1000) 的订单

1
2
3
4

SELECT o.orderid, o.custsid, o.productid, o.quantity, o.orderdate
FROM orders o
JOIN products p ON o.productid = p.productid
WHERE year(o.orderdate) = 2023 AND p.price > 1000;

Customers located in either UK or Germany 位于英国 (UK) 或德国 (Germany) 的客户

1
2
3

SELECT custid, cust_name, country
FROM customer
WHERE country = 'UK' OR country = 'Germany';

Products that are not very expensive (NOT price > 1500) 不是非常昂贵的产品 (NOT price > 1500)

1
2
3

SELECT productid, productname, price
FROM products
WHERE NOT (price > 1500);

Hive String Operators

Hive 字符串运算符

Add a “_VIP” tag to customers from India

给来自印度的客户添加“_VIP”标签

1
2
3
4
5

SELECT custid,
       CONCAT(cust_name, '_VIP') AS vip_name,
       country
FROM customer
WHERE country = 'India';

Country and Customer Name in 1 column

将国家和客户名称放在一列中

1
2
3

SELECT  custid,
       cust_name ||  ' - '  || country  AS   full_info
FROM  customer;

Complex Type Operators

复杂类型运算符

Used for array, Maps and Structs.

用于数组 (array)、映射 (Maps) 和结构体 (Structs)。

1
2
3
4
5
6
7

CREATE TABLE customer_orders (
  custid INT,
  orders ARRAY<INT>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ':';

Data copy in text file customer_orders.txt and upload in HDFS. 将数据复制到文本文件 customer_orders.txt 中并上传到 HDFS。

1
2
3
4
5
6
7

1001,1:2:3
1002,4:5
1003,6:7:8:9
1004,10:11
1005,12:13:14
LOAD DATA INPATH '/Data/customer_orders.txt'
INTO TABLE customer_orders;

Filter by array contents: 按数组内容过滤：

1
2

SELECT  custid ,  orders [0] AS  first_order
FROM  customer_orders ;

Find customers who have order ID 7 查找拥有订单 ID 7 的客户

1
2
3

SELECT custid
FROM customer_orders
WHERE array_contains(orders, 7);

Hive Functions

Hive 函数

Functions in Hive are predefined (or user-defined) routines that process data and return results. Hive 中的函数是预定义（或用户定义）的例程，用于处理数据并返回结果。

They are broadly categorized into Built-in and User-Defined Functions (UDFs). 它们大致分为内置函数和用户定义函数 (UDF)。

Mathematical Functions

数学函数

1
2
3

select productname, price*3.14  from  products  p ;
round()
select productname, round(price*3.14,1)  from  products  p ;	

Bround()

Bround() 函数

Rounds a number using Banker’s rounding (HALF_EVEN method).

使用银行家舍入法（HALF_EVEN 方法）对数字进行舍入。

If the fractional part is exactly 0.5, it rounds to the nearest even integer.

如果小数部分正好是 0.5，它会舍入到最近的偶数整数。

1

select  productname, price*3.14, bround(price*3.14)  from  products  p ;

random discount for lucky customers.

为幸运客户提供随机折扣。

1

select  price, rand()  as   discount    from  products  p  ;

Date Function

日期函数

Convert a date string to UNIX time

将日期字符串转换为 UNIX 时间戳

1

SELECT unix_timestamp('2024-11-05', 'yyyy-MM-dd') AS timestamp_value;

Convert timestamp to date 将时间戳转换为日期

1

SELECT to_date('2024-11-05 10:30:45') AS only_date;

year(string date) year(string date)

1

SELECT   year (orderdate)  AS   order_year   from  orders  o  ;

Returns the quarter of the year (1–4). 返回一年的季度 (1–4)。

1

SELECT quarter(orderdate) AS quarter_value from orders o;

Subtracts a given number of days from a date. 从日期中减去指定的天数。

1

SELECT orderdate ,date_sub(orderdate, 7) AS one_week_before from orders o;

Returns the current system date. 返回当前系统日期。

1

SELECT current_date();

Returns the last day of the month for a given date. 返回给定日期所在月份的最后一天。

1

SELECT last_day(orderdate) AS last_day_feb from orders o;

Conditional operator examples (IF, CASE) 条件运算符示例（IF, CASE）

Simple IF: label each product ‘Expensive’ or ‘Cheap’ 简单的 IF：将每个产品标记为 ‘Expensive’（昂贵）或 ‘Cheap’（便宜）

1
2
3

SELECT productid, productname, price,
       IF(price > 1200, 'Expensive', 'Cheap') AS price_label
FROM products;

CASE: multi-level discount category based on price CASE：基于价格的多级折扣类别

1
2
3
4
5
6
7

SELECT productid, productname, price,
  CASE
    WHEN price >= 1500 THEN 'Tier A - 20% off'
    WHEN price >= 800  THEN 'Tier B - 10% off'
    ELSE 'Tier C - no discount'
  END AS discount_tier
FROM products;

String Function

字符串函数

reverse(string X) reverse(string X)

1

select  reverse(cust_name)  from  customer  c  ;

rpad(string str, int length, string pad) rpad(string str, int length, string pad)

Pads the right side of the string up to given length 将字符串的右侧填充到给定的长度

1

SELECT  rpad(cust_name, 5,  '*' )  AS   result   from  customer;

Split product name by _ 通过 _ 分割产品名称

1
2
3

SELECT productid, productname, 
       split(productname, '_') AS name_parts
FROM products;

Aggregation Function

聚合函数

1
2
3
4
5

Select count(*) from customer;
Select sum(price) from products;
Select avg(price) from products;
Select min(price) from price;
SELECT Max(price ) from price;

UDF

用户自定义函数 (UDF)

A User-Defined Function (UDF) lets you extend Hive with custom logic not available in built-in functions. 用户自定义函数 (UDF) 允许你使用内置函数中不可用的自定义逻辑来扩展 Hive。

UDFs run inside Hive’s JVM and are typically written in Java (any JVM language). UDF 在 Hive 的 JVM 内部运行，通常用 Java（或任何 JVM 语言）编写。

Hive has several extension points: Hive 有几个扩展点：

Simple UDF — row-wise scalar function. Extend org.apache.hadoop.hive.ql.exec.UDF. 简单 UDF — 逐行标量函数。继承 org.apache.hadoop.hive.ql.exec.UDF。

Input – Single row 输入 – 单行

Ouput – Single row 输出 – 单行

UDAF (User-Defined Aggregate Function) — aggregate functions (SUM, AVG style). UDAF (用户自定义聚合函数) — 聚合函数（SUM、AVG 风格）。

Input – Multiple row 输入 – 多行

Ouput – Single row 输出 – 单行

Example: - calculate total, find average 示例：- 计算总和，查找平均值

UDTF (User-Defined Table-Generating Function) — emits 0..N rows per input row. Extend org.apache.hadoop.hive.ql.udf.generic.GenericUDTF or the older UDTF. UDTF (用户自定义表生成函数) — 每行输入生成 0 到 N 行。继承 org.apache.hadoop.hive.ql.udf.generic.GenericUDTF 或旧版 UDTF。

Input – one row 输入 – 一行

Output – Multiple row 输出 – 多行

Example: - Split string into multiple rows 示例：- 将字符串分割成多行

When to use which 何时使用哪一种

Use Simple UDF: trivial scalar ops with fixed types (e.g., string -> string). 使用 简单 UDF：具有固定类型的简单标量操作（例如，字符串 -> 字符串）。

Use UDAF: computing aggregates across rows (custom aggregator). 使用 UDAF：跨行计算聚合（自定义聚合器）。

Use UDTF: explode/expand input into multiple rows/columns. 使用 UDTF：将输入分解/扩展为多行/多列。

Variables in HIVE

HIVE 中的变量

Variables in Hive behave same as in other programming languages.

Hive 中的变量与其他编程语言中的变量表现相同。

Variables are values that are changeable and not fixed.

变量是可变的而不是固定的值。

We can pass a value to a variable during the execution of program.

我们可以在程序执行期间向变量传递值。

Hiveconf – Configuration-level variable

Hiveconf – 配置级变量

Hivevar – user-level variable for script

Hivevar – 脚本的用户级变量

Set variable 设置变量

1

set emp_id = 90;

To check 查看变量

1
2
3
4
5

set emp_id;
SET limit=5;
Select * from emp LIMIT${hiveconf:limit}
SET hivevar:tablename= employee;
Select * from ${hivevar:tablename};