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 的表，表结构如下：

查询示例

1. 获取前 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

2. 获取从第 5 行开始的 3 行数据：

   SELECT * FROM employees LIMIT 4，3;
   

   id	name	department	salary
   5	Eve	HR	5500
   6	Frank	Finance	7500
   7	Grace	HR	4800

3. 获取从第 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. 查看和描述系统自带的函数

1. 查看系统自带的函数：

   SHOW FUNCTIONS;
   

   输出示例（部分函数）：

   ...
   upper
   lower
   concat
   substr
   ...
   

2. 显示函数的用法：

   DESC FUNCTION upper;
   

   输出示例：

   u_upper(_FUNC_(str) - Returns str with all characters changed to uppercase
   

3. 详细显示函数的用法：

   DESC FUNCTION EXTENDED upper;
   

   输出示例：

   u_upper(_FUNC_(str) - Returns str with all characters changed to uppercase
   Example:
     > SELECT _FUNC_('Hive');
     'HIVE'
   

2.常用字符串函数

1. 拼接函数 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 字段，适用于字符串类型，每个参数的类型是字符串或者可以自动转换为字符串。

2. 无连接符限制的拼接函数 concat：

   SELECT concat(ename, " . ", sal, ":", deptno) FROM emp;
   

   假设 emp 表数据如下：

   ename	sal	deptno
   Alice	5000	10
   Bob	6000	20

   输出示例：

   Alice . 5000:10
   Bob . 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：

   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。