Hive数仓操作（十）

一、Hive 分页查询

在大数据处理中，分页查询是非常常见的需求。Hive 提供了

LIMIT

和

OFFSET

关键字来方便地进行分页操作。本文将详细介绍它们的用法。

1. 基本用法

• LIMIT：用于限制查询结果的行数。
• OFFSET：用于指定从哪一行开始检索。

2. 基本语法：

LIMIT 使用：

SELECT*FROM 表名 LIMIT 起始行数, 连续取值的长度;SELECT*FROM 表名 LIMIT x;#返回前x行SELECT*FROM 表名 LIMIT0, x;#两者等价，返回前x行

• 起始行数：从 0 开始计数，表示要跳过的行数。
• 连续取值的长度：表示要返回的行数。

结合 OFFSET 使用：

SELECT*FROM 表名 LIMIT 连续取值的长度 OFFSET 起始行数;

这里，

LIMIT

后面只有一个参数，表示要提取的数量，而

OFFSET

则表示从第几行开始。

3. 示例

假设有一个名为

employees

的表，表结构如下：
idnamedepartmentsalary1AliceHR50002BobIT60003CharlieFinance70004DavidIT80005EveHR55006FrankFinance75007GraceHR48008HenryIT90009IvyFinance720010JackHR5300

查询示例

1. 获取前 5 行数据：SELECT*FROM employees LIMIT5;idnamedepartmentsalary1AliceHR50002BobIT60003CharlieFinance70004DavidIT80005EveHR5500
2. 获取从第 5 行开始的 3 行数据：SELECT*FROM employees LIMIT4，3;idnamedepartmentsalary5EveHR55006FrankFinance75007GraceHR4800
3. 获取从第 5 行开始的 3 行数据：SELECT*FROM employees LIMIT3OFFSET4;idnamedepartmentsalary5EveHR55006FrankFinance75007GraceHR4800

4. 注意事项

• 行数计数：在 Hive 中，LIMIT 和 OFFSET行数计数从 0 开始。
• LIMIT 和 OFFSET 的使用：- 当使用 LIMIT n OFFSET m 时，LIMIT 后面只能有一个参数，表示要提取的数量。- OFFSET 用于指定应该跳过的行数。
• 性能问题：在处理非常大的数据集时，分页查询可能会影响性能，特别是当 OFFSET 值较大时。应该尽量使用适当的过滤条件来减少查询的数据量。

二、Hive 常用函数

1. 查看和描述系统自带的函数

1. 查看系统自带的函数：SHOW FUNCTIONS;输出示例（部分函数）：...upperlowerconcatsubstr...
2. 显示函数的用法：DESCFUNCTION upper;输出示例：u_upper(_FUNC_(str) - Returns str with all characters changed to uppercase
3. 详细显示函数的用法：DESCFUNCTIONEXTENDED upper;输出示例：u_upper(_FUNC_(str) - Returns str with all characters changed to uppercaseExample: > SELECT _FUNC_('Hive'); 'HIVE'

2.常用字符串函数

1. **拼接函数 concat_ws*：SELECT concat_ws(':', ename, job, hiredate)FROM emp;假设 emp 表数据如下：enamejobhiredateAliceManager2021-05-01BobDeveloper2022-03-10输出示例*：Alice:Manager:2021-05-01Bob:Developer:2022-03-10解释：使用 : 作为连接符，拼接 ename、job 和 hiredate 字段，适用于字符串类型，每个参数的类型是字符串或者可以自动转换为字符串。
2. **无连接符限制的拼接函数 concat*：SELECT concat(ename," . ", sal,":", deptno)FROM emp;假设 emp 表数据如下：enamesaldeptnoAlice500010Bob600020输出示例*：Alice . 5000:10Bob . 6000:20解释：拼接多个字符串和字段，可以自动处理各种数据类型，将它们转换为字符串后进行拼接，更适合直接拼接不需要分隔符的场景。
3. **截取字符串 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。
4. 查看字符串长度、转换大小写：SELECT length('abc'), upper('abc'), lower('ABC');输出示例：3 ABC abc解释：- length('abc'): 获取字符串长度，结果为 3。- upper('abc'): 转换为大写，结果为 ABC。- lower('ABC'): 转换为小写，结果为 abc。
5. **首字母大写 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 。
6. **去空格函数 trim、ltrim 和 rtrim**：SELECT trim(' abcde '), ltrim(' ww'), rtrim('ww ');输出示例：abcde ww ww解释：- trim(' abcde '): 去掉首尾空格，结果为 abcde。- ltrim(' ww'): 去掉左侧空格，结果为 ww。- rtrim('ww '): 去掉右侧空格，结果为 ww。
7. **字符串替换函数 replace 和字符位置查找函数 instr**：SELECTreplace('baidu.com','baidu','bangci'), instr('abcdf','c');输出示例：bangci.com 3解释：- replace('baidu.com', 'baidu', 'bangci'): 将字符串 baidu 替换为 bangci，结果为 bangci.com。- instr('abcdf', 'c'): 查找字符 c 在字符串中的位置，结果为 3。