Hive数仓操作（十）
一、Hive 分页查询
在大数据处理中，分页查询是非常常见的需求。Hive 提供了 LIMIT 和 OFFSET 关键字来方便地进行分页操作。本文将详细介绍它们的用法。
1. 基本用法
- LIMIT：用于限制查询结果的行数。
- OFFSET：用于指定从哪一行开始检索。
2. 基本语法：
LIMIT 使用：
SELECT * FROM 表名 LIMIT 起始行数, 连续取值的长度;
SELECT * FROM 表名 LIMIT x; #返回前x行
SELECT * FROM 表名 LIMIT 0, x; #两者等价，返回前x行
- 起始行数：从 0 开始计数，表示要跳过的行数。
- 连续取值的长度：表示要返回的行数。
结合 OFFSET 使用：
SELECT * FROM 表名 LIMIT 连续取值的长度 OFFSET 起始行数;
这里，LIMIT 后面只有一个参数，表示要提取的数量，而 OFFSET 则表示从第几行开始。
3. 示例
假设有一个名为 employees 的表，表结构如下：
| id | name | department | salary |
|---|---|---|---|
| 1 | Alice | HR | 5000 |
| 2 | Bob | IT | 6000 |
| 3 | Charlie | Finance | 7000 |
| 4 | David | IT | 8000 |
| 5 | Eve | HR | 5500 |
| 6 | Frank | Finance | 7500 |
| 7 | Grace | HR | 4800 |
| 8 | Henry | IT | 9000 |
| 9 | Ivy | Finance | 7200 |
| 10 | Jack | HR | 5300 |
查询示例
-
获取前 5 行数据：
SELECT * FROM employees LIMIT 5;id name department salary 1 Alice HR 5000 2 Bob IT 6000 3 Charlie Finance 7000 4 David IT 8000 5 Eve HR 5500 -
获取从第 5 行开始的 3 行数据：
SELECT * FROM employees LIMIT 4，3;id name department salary 5 Eve HR 5500 6 Frank Finance 7500 7 Grace HR 4800 -
获取从第 5 行开始的 3 行数据：
SELECT * FROM employees LIMIT 3 OFFSET 4;id name department salary 5 Eve HR 5500 6 Frank Finance 7500 7 Grace HR 4800
4. 注意事项
-
行数计数：在 Hive 中，LIMIT 和 OFFSET行数计数从 0 开始。
-
LIMIT 和 OFFSET 的使用：
- 当使用
LIMIT n OFFSET m时，LIMIT后面只能有一个参数，表示要提取的数量。 OFFSET用于指定应该跳过的行数。
- 当使用
-
性能问题：在处理非常大的数据集时，分页查询可能会影响性能，特别是当
OFFSET值较大时。应该尽量使用适当的过滤条件来减少查询的数据量。
二、Hive 常用函数
1. 查看和描述系统自带的函数
-
查看系统自带的函数：
SHOW FUNCTIONS;输出示例（部分函数）：
... upper lower concat substr ... -
显示函数的用法：
DESC FUNCTION upper;输出示例：
u_upper(_FUNC_(str) - Returns str with all characters changed to uppercase -
详细显示函数的用法：
DESC FUNCTION EXTENDED upper;输出示例：
u_upper(_FUNC_(str) - Returns str with all characters changed to uppercase Example: > SELECT _FUNC_('Hive'); 'HIVE'
2.常用字符串函数
-
拼接函数
concat_ws：SELECT concat_ws(':', ename, job, hiredate) FROM emp;假设
emp表数据如下：ename job hiredate Alice Manager 2021-05-01 Bob Developer 2022-03-10 输出示例：
Alice:Manager:2021-05-01 Bob:Developer:2022-03-10解释：使用
:作为连接符，拼接ename、job和hiredate字段，适用于字符串类型，每个参数的类型是字符串或者可以自动转换为字符串。 -
无连接符限制的拼接函数
concat：SELECT concat(ename, " . ", sal, ":", deptno) FROM emp;假设
emp表数据如下：ename sal deptno Alice 5000 10 Bob 6000 20 输出示例：
Alice . 5000:10 Bob . 6000:20解释：拼接多个字符串和字段，可以自动处理各种数据类型，将它们转换为字符串后进行拼接，更适合直接拼接不需要分隔符的场景。
-
截取字符串
substr：SELECT substr('abcde', 2), substr('abcde', 2, 1), substr('abcde', -4);输出示例：
bcde b bcde解释：
substr('abcde', 2): 从第二个字符开始截取，结果为bcde。substr('abcde', 2, 1): 从第二个字符开始截取1个字符，结果为b。substr('abcde', -4): 从第四个字符开始向后截取，结果为bcde。
-
查看字符串长度、转换大小写：
SELECT length('abc'), upper('abc'), lower('ABC');输出示例：
3 ABC abc解释：
length('abc'): 获取字符串长度，结果为3。upper('abc'): 转换为大写，结果为ABC。lower('ABC'): 转换为小写，结果为abc。
-
首字母大写
initcap、填充函数lpad和rpad：SELECT initcap('abcde'), lpad('ww', 5, " "), rpad('ww', 5, " ");输出示例：
Abcde ww ww解释：
initcap('abcde'): 首字母大写，结果为Abcde。lpad('ww', 5, " "): 左侧填充空格至长度为5，结果为ww。rpad('ww', 5, " "): 右侧填充空格至长度为5，结果为ww。
-
去空格函数
trim、ltrim和rtrim：SELECT trim(' abcde '), ltrim(' ww'), rtrim('ww ');输出示例：
abcde ww ww解释：
trim(' abcde '): 去掉首尾空格，结果为abcde。ltrim(' ww'): 去掉左侧空格，结果为ww。rtrim('ww '): 去掉右侧空格，结果为ww。
-
字符串替换函数
replace和字符位置查找函数instr：SELECT replace('baidu.com', 'baidu', 'bangci'), instr('abcdf', 'c');输出示例：
bangci.com 3解释：
replace('baidu.com', 'baidu', 'bangci'): 将字符串baidu替换为bangci，结果为bangci.com。instr('abcdf', 'c'): 查找字符c在字符串中的位置，结果为3。