Hive查询

怼烎@ 2022-04-25 01:46 250阅读 0赞

Hive查询 分桶表 常用函数 自定义函数：

## 查询语法 ##

[参考文档][Link 1]

## 基本查询 ##

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 </pre></td> 
 <td><pre>select … from </pre></td> 
 </tr> 
 </tbody> 
</table>

### 全表查询 ###

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 </pre></td> 
 <td><pre>select * from emp; </pre></td> 
 </tr> 
 </tbody> 
</table>

### 条件查询 ###

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 </pre></td> 
 <td><pre>select empno, ename from emp; </pre></td> 
 </tr> 
 </tbody> 
</table>

注意:

1.  SQL 语言大小写不敏感。
2.  SQL 可以写在一行或者多行
3.  关键字不能被缩写也不能分行
4.  各子句一般要分行写。
5.  使用缩进提高语句的可读性。

### 列别名 ###

1.  重命名一个列
2.  便于计算
3.  紧跟列名，也可以在列名和别名之间加入关键字‘AS’

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 </pre></td> 
 <td><pre>--询名称和部门 hive &gt; select ename AS name, deptno dn from emp; </pre></td> 
 </tr> 
 </tbody> 
</table>

### 算术运算符 ###

<table> 
 <thead> 
 <tr> 
 <th>运算符</th> 
 <th>描述</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>A+B</td> 
 <td>A和B 相加</td> 
 </tr> 
 <tr> 
 <td>A-B</td> 
 <td>A减去B</td> 
 </tr> 
 <tr> 
 <td>A*B</td> 
 <td>A和B 相乘</td> 
 </tr> 
 <tr> 
 <td>A/B</td> 
 <td>A除以B</td> 
 </tr> 
 <tr> 
 <td>A%B</td> 
 <td>A对B取余</td> 
 </tr> 
 <tr> 
 <td>A&amp;B</td> 
 <td>A和B按位取与</td> 
 </tr> 
 <tr> 
 <td>A\</td> 
 <td>B</td> 
 <td>A和B按位取或</td> 
 </tr> 
 <tr> 
 <td>A^B</td> 
 <td>A和B按位取异或</td> 
 </tr> 
 <tr> 
 <td>~A</td> 
 <td>A按位取反</td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 </pre></td> 
 <td><pre>--薪水加100后的信息 select ename,sal +100 from emp; </pre></td> 
 </tr> 
 </tbody> 
</table>

### 常用函数 ###

### Where语句 ###

1．使用WHERE子句，将不满足条件的行过滤掉

2．WHERE子句紧随FROM子句

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 </pre></td> 
 <td><pre>--查询出薪水大于1000的所有员工 select * from emp where sal &gt;1000; </pre></td> 
 </tr> 
 </tbody> 
</table>

### 比较运算符 ###

<table> 
 <thead> 
 <tr> 
 <th>操作符</th> 
 <th>支持的数据类型</th> 
 <th>描述</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>A=B</td> 
 <td>基本数据类型</td> 
 <td>如果A等于B则返回TRUE，反之返回FALSE</td> 
 </tr> 
 <tr> 
 <td>A&lt;=&gt;B</td> 
 <td>基本数据类型</td> 
 <td>如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL</td> 
 </tr> 
 <tr> 
 <td>A&lt;&gt;B, A!=B</td> 
 <td>基本数据类型</td> 
 <td>A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE</td> 
 </tr> 
 <tr> 
 <td>A&lt;B</td> 
 <td>基本数据类型</td> 
 <td>A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE</td> 
 </tr> 
 <tr> 
 <td>A&lt;=B</td> 
 <td>基本数据类型</td> 
 <td>A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE</td> 
 </tr> 
 <tr> 
 <td>A&gt;B</td> 
 <td>基本数据类型</td> 
 <td>A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE</td> 
 </tr> 
 <tr> 
 <td>A&gt;=B</td> 
 <td>基本数据类型</td> 
 <td>A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE</td> 
 </tr> 
 <tr> 
 <td>A [NOT] BETWEEN B AND C</td> 
 <td>基本数据类型</td> 
 <td>如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。</td> 
 </tr> 
 <tr> 
 <td>A IS NULL</td> 
 <td>所有数据类型</td> 
 <td>如果A等于NULL，则返回TRUE，反之返回FALSE</td> 
 </tr> 
 <tr> 
 <td>A IS NOT NULL</td> 
 <td>所有数据类型</td> 
 <td>如果A不等于NULL，则返回TRUE，反之返回FALSE</td> 
 </tr> 
 <tr> 
 <td>IN(数值1, 数值2)</td> 
 <td>所有数据类型</td> 
 <td>使用 IN运算显示列表中的值</td> 
 </tr> 
 <tr> 
 <td>A [NOT] LIKE B</td> 
 <td>STRING 类型</td> 
 <td>B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。</td> 
 </tr> 
 <tr> 
 <td>A RLIKE B, A REGEXP B</td> 
 <td>STRING 类型</td> 
 <td>B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。</td> 
 </tr> 
 </tbody> 
</table>

### Like和RLike ###

1.  使用LIKE运算选择类似的值
2.  选择条件可以包含字符或数字:% 代表零个或多个字符(任意个字符)。\_ 代表一个字符。
3.  RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 4 5 6 </pre></td> 
 <td><pre>-- 查找以2开头薪水的员工信息 select * from emp where sal LIKE '2%'; -- 查找第二个数值为2的薪水的员工信息 select * from emp where sal LIKE '_2%'; -- 查找薪水中含有2的员工信息 select * from emp where sal RLIKE '[2]'; </pre></td> 
 </tr> 
 </tbody> 
</table>

### 逻辑运算符 ###

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 4 </pre></td> 
 <td><pre>--求每个部门的平均工资 select deptno, avg(sal) from emp group by deptno; --求每个部门的平均薪水大于2000的部门 select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal &gt; 2000; </pre></td> 
 </tr> 
 </tbody> 
</table>

### Join语句 ###

#### 等值Join ####

注意:Hive支持通常的SQL JOIN语句，但 **只支持等值连接，不支持非等值连接**。

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 4 </pre></td> 
 <td><pre>--根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称； select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno; --合并员工表和部门表 select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno; </pre></td> 
 </tr> 
 </tbody> 
</table>

表的别名:（1）使用别名可以简化查询。（2）使用表名前缀可以提高执行效率。

#### 左外连接 ####

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 </pre></td> 
 <td><pre>select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno; </pre></td> 
 </tr> 
 </tbody> 
</table>

#### 右外连接 ####

右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 </pre></td> 
 <td><pre>select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno; </pre></td> 
 </tr> 
 </tbody> 
</table>

#### 满外连接 ####

满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 </pre></td> 
 <td><pre>select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno; </pre></td> 
 </tr> 
 </tbody> 
</table>

#### 多表连接 ####

数据准备

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 4 </pre></td> 
 <td><pre>[hadoop@datanode1 datas]$ vim location.txt 1700 Beijing 1800 London 1900 Tokyo </pre></td> 
 </tr> 
 </tbody> 
</table>

创建位置表

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 4 5 </pre></td> 
 <td><pre>create table if not exists default.location( loc int, loc_name string ) row format delimited fields terminated by '\t'; </pre></td> 
 </tr> 
 </tbody> 
</table>

导入数据

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 </pre></td> 
 <td><pre>load data local inpath '/opt/module/datas/location.txt' into table default.location; </pre></td> 
 </tr> 
 </tbody> 
</table>

多表连接查询

注意:大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。Hive总是按照从左到右的顺序执行的。因此不是Hive总是按照从左到右的顺序执行的。

#### 笛卡尔积 ####

笛卡尔集会在下面条件下产生:

1.  省略连接条件
2.  连接条件无效
3.  所有表中的所有行互相连接

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 4 5 </pre></td> 
 <td><pre>--案例实操 select empno, dname from emp, dept; --连接谓词中不支持or select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno= d.deptno or e.ename=d.ename; ## 错误的 </pre></td> 
 </tr> 
 </tbody> 
</table>

### 排序 ###

#### 全局排序 ####

Order By：全局排序，一个Reducer

1．使用 ORDER BY 子句排序  
ASC（ascend）: 升序（默认）  
DESC（descend）: 降序

ORDER BY 子句在SELECT语句的结尾

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 4 </pre></td> 
 <td><pre>--查询员工信息按工资升序排列 hive (default)&gt; select * from emp order by sal; --查询员工信息按工资降序排列 select * from emp order by sal desc; </pre></td> 
 </tr> 
 </tbody> 
</table>

#### 按照别名排序 ####

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 4 </pre></td> 
 <td><pre>--按照员工薪水的2倍排序 select ename, sal*2 twosal from emp order by twosal; --按照部门和工资升序排序 select ename, deptno, sal from emp order by deptno, sal; </pre></td> 
 </tr> 
 </tbody> 
</table>

#### MR内部排序（Sort By） ####

Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。

#### 分区排序 （Distribute By） ####

Distribute By：类似MR中partition，进行分区，结合sort by使用。

注意：Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

案例实操：

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 </pre></td> 
 <td><pre>--需求：先按照部门编号分区，再按照员工编号降序排序。 set mapreduce.job.reduces=3; select * from emp distribute by deptno sort by empno desc; </pre></td> 
 </tr> 
 </tbody> 
</table>

#### Cluster By ####

当distribute by和sorts by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 </pre></td> 
 <td><pre>select * from emp cluster by deptno; select * from emp distribute by deptno sort by deptno; </pre></td> 
 </tr> 
 </tbody> 
</table>

### 桶表 ###

**分区针对的是数据的存储路径；分桶针对的是数据文件。**

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。 分桶是将数据集分解成更容易管理的若干部分的另一个技术。

查看创建的分桶表

![kpDZqO.png][]

![kpD5S1.png][]

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 </pre></td> 
 <td><pre>--为什么没有分桶呢 这里需要我们开启一个属性 insert into table stu_buck select id, name from stu; </pre></td> 
 </tr> 
 </tbody> 
</table>

![kpDxSI.png][]

![kps4r6.png][]

#### 分桶抽样查询 ####

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

查询表stu\_buck中的数据 select \* from 表名 tablesample(bucket x out of y on id);

y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。

x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第4(x+y)个bucket的数据。

### 其他常用查询函数 ###

#### 空字段赋值 ####

NVL：给值为NULL的数据赋值，它的格式是NVL( string1,replace\_with)。它的功能是如果string1为NULL，则NVL函数返回replace\_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

#### CASE WHEN ####

#### 行转列 ####

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT\_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

COLLECT\_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

#### 列转行 ####

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

#### 窗口函数 ####

<table> 
 <thead> 
 <tr> 
 <th>函数</th> 
 <th>功能</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>OVER()</td> 
 <td>指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化</td> 
 </tr> 
 <tr> 
 <td>CURRENT ROW</td> 
 <td>当前行</td> 
 </tr> 
 <tr> 
 <td>n PRECEDING</td> 
 <td>往前n行数据</td> 
 </tr> 
 <tr> 
 <td>n FOLLOWING</td> 
 <td>往后n行数据</td> 
 </tr> 
 <tr> 
 <td>UNBOUNDED</td> 
 <td>起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点</td> 
 </tr> 
 <tr> 
 <td>LAG(col,n)</td> 
 <td>往前第n行数据</td> 
 </tr> 
 <tr> 
 <td>LEAD(col,n)</td> 
 <td>往后第n行数据</td> 
 </tr> 
 <tr> 
 <td>NTILE(n)</td> 
 <td>把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始：n必须为int类型。</td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 </pre></td> 
 <td><pre>--准备数据 [hadoop@datanode1 datas]$ vim business.txt jack,2018-01-01,10 tony,2018-01-02,15 jack,2018-02-03,23 tony,2018-01-04,29 jack,2018-01-05,46 jack,2018-04-06,42 tony,2018-01-07,50 jack,2018-01-08,55 mart,2018-04-08,62 mart,2018-04-09,68 neil,2018-05-10,12 mart,2018-04-11,75 neil,2018-06-12,80 mart,2018-04-13,94 --创建表导入数据 create table business( name string, orderdate string, cost int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; load data local inpath "/opt/module/datas/business.txt" into table business; --查询在2018年4月份购买过的顾客及总人数 select name,count(*) over () from business where substring(orderdate,1,7) = '2018-04' group by name; name count_window_0 mart 2 jack 2 --查询顾客的购买明细及月购买总额 select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business; --查询顾客的购买明细及月购买总额 select *,sum(cost) over(partition by month(orderdate)) from business; --查询顾客上次的购买时间 select *, lag(orderdate,1) over(distribute by name sort by orderdate), lead(orderdate,1) over(distribute by name sort by orderdate) from business; select *, lag(orderdate,1,"2016-12-31") over(distribute by name sort by orderdate) from business; --查询前20%时间的订单信息 select *,ntile(5) over(sort by orderdate) gid from business where gid=1;X select *,ntile(5) over(sort by orderdate) gid from business having gid=1;X 	select * 	from( 	select *,ntile(5) over(sort by orderdate) gid from business 	) t 	where 	gid=1; select * from ( select name,orderdate,cost, ntile(5) over(order by orderdate) sorted from business ) t where sorted = 1; </pre></td> 
 </tr> 
 </tbody> 
</table>

#### Rank ####

<table> 
 <thead> 
 <tr> 
 <th>函数</th> 
 <th>功能</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>RANK()</td> 
 <td>排序相同时会重复，总数不会变</td> 
 </tr> 
 <tr> 
 <td>DENSE_RANK()</td> 
 <td>排序相同时会重复，总数会减少</td> 
 </tr> 
 <tr> 
 <td>ROW_NUMBER()</td> 
 <td>会根据顺序计算</td> 
 </tr> 
 </tbody> 
</table>

#### 自定义函数 ####

Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。

当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

根据用户自定义函数类别分为以下三种：

（1）UDF（User-Defined-Function） 一进一出

（2）UDAF（User-Defined Aggregation Function） 聚集函数，多进一出 类似于：count/max/min

（3）UDTF（User-Defined Table-Generating Functions） 一进多出 如lateral view explore()

[官方文档地址][Link 2]

编程步骤:

（1）继承org.apache.hadoop.hive.ql.UDF

（2）需要实现evaluate函数；evaluate函数支持重载；

（3）在hive的命令行窗口创建函数

自定义UDF函数

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 4 5 6 7 8 </pre></td> 
 <td><pre>&lt;dependencies&gt; 		&lt;!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec --&gt; 		&lt;dependency&gt; 			&lt;groupId&gt;org.apache.hive&lt;/groupId&gt; 			&lt;artifactId&gt;hive-exec&lt;/artifactId&gt; 			&lt;version&gt;1.2.1&lt;/version&gt; 		&lt;/dependency&gt; &lt;/dependencies&gt; </pre></td> 
 </tr> 
 </tbody> 
</table>

java类

上传上服务器

![kpopw9.png][]

添加jar

[Link 1]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
[kpDZqO.png]: /images/20220210/2aa4ce05c7714dd8ab13568c53654a27.png
[kpD5S1.png]: /images/20220210/49cc654429b54859be35ec1d48f589e1.png
[kpDxSI.png]: /images/20220210/b377c709cfac4e99baede30313192ba0.png
[kps4r6.png]: /images/20220210/49cc718adb8c4317864d99697755fee7.png
[Link 2]: https://cwiki.apache.org/confluence/display/Hive/HivePlugins
[kpopw9.png]: /images/20220210/dd23d90ff20448388463d21d6a5be17f.png