Hive笔记

ゞ浴缸里的玫瑰 2023-09-25 23:03 131阅读 0赞

**目录**

第3章 Hive数据类型

第 4 章 DDL 数据定义

第5章DML数据操作

第6章 查询（语法与MySQL一样）

第 7 章 分区表和分桶表

第 8 章 函数

--------------------

## **第3章 Hive数据类型** ##

![f44ffd048f734108b0a93ddf25d008ed.png][]

如array<map<string,int>>

集合数据类型工作中不是很常用，最常用的就是基本数据类型中的INT、BIGINT、BOOLEAN、DOUBLE、STRING这五种。其中日期类型也不常用，因为哪怕是字符串类型写成yyyy-mm-nn年月日的形式，都可以自动转换成日期形式。

**1****）集合案例实操 （了解即可）**

（1）假设某表有如下一行，我们用 JSON 格式来表示其数据结构。在 Hive 下访问的格

式为

> \{ 
> 
> "name": "songsong", 
> 
> "friends": \["bingbing" , "lili"\] , //列表 Array, 
> 
> "children": \{ //键值 Map, 
> 
> "xiao song": 18 , 
> 
> "xiaoxiao song": 19 
> 
> \} 
> 
> "address": \{ //结构 Struct, 
> 
> "street": "hui long guan", 
> 
> "city": "beijing" 
> 
> \} 
> 
> \}

（2）Hive 上创建测试表 test

> create table test( 
> 
> name string, 
> 
> friends array<string>,
> 
> children map<string, int>, 
> 
> address struct<street:string, city:string> 
> 
> ) 
> 
> row format delimited fields terminated by ',' 
> 
> collection items terminated by '\_' 
> 
> map keys terminated by ':' 
> 
> lines terminated by '\\n';

**字段解释：**

> row format delimited fields terminated by ',' -- 列分隔符 
> 
> collection items terminated by '\_'  \-- MAP、STRUCT 和 ARRAY 的元素分隔符(数据分割符号) 
> 
> map keys terminated by ':' -- MAP 中的 key 与 value 的分隔符 
> 
> lines terminated by '\\n'; -- 行分隔符（默认是\\n,所以这行可写可不写）
> 
> 访问方式：select friends\[0\],children\[“xiaosong”\],address.street from test;
> 
> 数组：通过下标访问 map：通过传入key访问value  struct：通过名字.属性访问

![d8a25389d7d04dd3b580ab7b784af40d.png][]![b01329e53f9f42049fa8ff808347185c.png][]

其中第（2）条，STRING类型可以隐式地转换为DOUBLE类型，要注意STRING类型的值要为“32.1”这种数字形式，如果是“aa.b”这种非数字类型就不能转换成DOUBLE类型。

## **第 4 章 DDL 数据定义** ##

**1.创建数据库**

> create database \[if not exits\] database2 \[location '/db\_hive2.db'\];

注：【】内为可选选项，location ‘位置’：指定数据库存放位置

> hive>drop databases like ’db\_hive2\*’;

查询以db\_hive2开头的数据库

> Desc database db\_hive2；

查看db\_hive2数据库详细信息

1.  **删除数据库**

1）删除空数据库

> hive>drop database db\_hive2;

3）如果数据库不为空，可以采用 cascade 命令，强制删除

> hive> drop database db\_hive cascade;

**2.创建表**

![c3c60f6002d741e69a4d125ea7960f14.png][]

如：create table test(id int,name string)

row format delimited fields terminated by ‘,’;

创建一个包含id、name字段的test表，字段以“，”分割。

![e4657af98c0d496784d1cc14921c5d68.png][]

![96ca0c5697b94870af5ffc9bc0712af8.png][]

![135b40a6ab5c47f9861e47172b7d0db8.png][]

![f891e8929eb24499926de4387b95dbfa.png][]

总结：默认情况下创建的是管理表（内部表），当创建表时加了EXTERNAL关键字时，创建的是外部表。当删除表时，如果是管理表，则会将元数据和数据全部删除，也就是完全删除；如果是外部表，则只会删除元数据，不会删除数据，元数据就是表名，当重新创建一个相同的表名时，再次查看数据还是可以查看到的。

生产环境中更多地用的是外部表。

**管理表与外部表的互相转换**

修改内部表 student2 为外部表

> alter table student2 set tblproperties('EXTERNAL'='TRUE');

修改外部表 student2 为内部表

> alter table student2 set tblproperties('EXTERNAL'='FALSE');

查询表的类型

> desc formatted student2;

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

## **第5章DML数据操作** ##

**5.1** **数据导入**

**\*****5.1.1** **向表中装载数据（****Load****） （常用）**

**1****）语法**

> hive> load data \[local\] inpath '数据的 path' \[overwrite\] into table 
> 
> student \[partition (partcol1=val1,…)\];

（1）load data:表示加载数据

（2）local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表

（3）inpath:表示加载数据的路径

（4）overwrite:表示覆盖表中已有数据，否则表示追加

（5）into table:表示加载到哪张表

（6）student:表示具体的表

（7）partition:表示上传到指定分区

如：

（1）加载本地文件student.txt到 hive 的student表中

> hive (default)> load data local inpath 
> 
> '/opt/module/hive/datas/student.txt' into table default.student;

（2）加载 HDFS 文件到 hive 的student表中

> hive (default)> load data inpath '/user/atguigu/hive/student.txt' into 
> 
> table default.student;

（3）加载数据并覆盖表中已有的数据

> hive (default)> load data inpath '/user/atguigu/hive/student.txt' 
> 
> overwrite into table default.student;

注意：从本地文件中上传student.txt到student表中是复制过去，**但从HDFS文件中上传student.txt到student表中是剪切过去，也就是把student.txt文件剪切到student表中。**

**\*****5.1.2** **通过查询语句向表中插入数据（****Insert****）（常用）**

**基本插入数据 （少用，因为一条条插入数据太慢了）**

> hive (default)> insert into table student\_par 
> 
> values(1,'wangwu'),(2,'zhaoliu');

**两种插入模式（常用，通过查询把数据导入）**

*  **覆盖模式插入（**insert overwrite**）（根据单张表查询结果）**

> hive (default)> insert overwrite table student 
> 
> select id, name from student where month='201709';

**二、追加模式插入（**insert into**）**

> hive (default)> insert into student
> 
> select id, name from student where month='201709';

insert into：以追加数据的方式插入到表或分区，原有数据不会删除

insert overwrite：会覆盖表中已存在的数据

**注意：****insert** **不支持插入部分字段**

**5.1.3****查询语句中创建表并加载数据（****As Select****） （不常用）**

根据查询结果创建表（查询的结果会添加到新创建的表中）

> create table if not exists student3 as select id, name from student;

注意：查询的字段为创建新表的字段，并将查询出的数据作为新表的数据。

**5.1.4** **创建表时通过** **Location** **指定加载数据路径（不常用）**

**创建表，并指定在** **hdfs** **上的位置**

> hive (default)> create external table if not exists student5( id int, name string) 
> 
> row format delimited fields terminated by '\\t' location '/student;

注意：这种方式相当于创建一个表，然后指定了一个hdfs已有的路径，路径里面有数据，创建的表直接拥有此路径的数据。

**5.1.****5****清除表中的数据（****Truncate****）**

注意：Truncate 只能删除管理表（内部表），不能删除外部表中数据

> hive (default)> truncate table student;

**6.2 分组（知识盲区，group by分组面试sql题中常考，需要好好掌握）**

**6.2.1 Group By** **语句**

GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然

后对每个组执行聚合操作。

**1****）案例实操：**

（1）计算 emp 表每个部门的平均工资

> hive (default)> select t.deptno, avg(t.sal) avg\_sal from emp t group by 
> 
> t.deptno;

（2）计算 emp 每个部门中每个岗位的最高薪水

> hive (default)> select t.deptno, t.job, max(t.sal) max\_sal from emp t 
> 
> group by 
> 
> t.deptno, t.job;

**6.2.2 Having** **语句**

**1****）****having** **与** **where** **不同点**

（1）where 后面不能写分组函数，而 having 后面可以使用分组函数。

（2）having 只用于 group by 分组统计语句。

**2****）案例实操**

（1）求每个部门的平均薪水大于 2000 的部门

求每个部门的平均工资

> hive (default)> select deptno, avg(sal) from emp group by deptno;

求每个部门的平均薪水大于 2000 的部门

> hive (default)> select deptno, avg(sal) avg\_sal from emp group by deptno 
> 
> having avg\_sal > 2000;

**6.3 Join** **语句**

**6.3.1** **等值** **Join**

Hive 支持通常的 SQL JOIN 语句。

**1****）案例实操**

（1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；

> hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e 
> 
> join dept d on e.deptno = d.deptno;

**6.3.2** **表的别名**

**1****）好处**

（1）使用别名可以简化查询。

（2）使用表名前缀可以提高执行效率。

**2****）案例实操**

合并员工表和部门表

> hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d 
> 
> on e.deptno = d.deptno;

**6.3.3** **内连接**

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

> hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d 
> 
> on e.deptno = d.deptno;

**6.3.4** **左外连接**

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

> hive (default)> select e.empno, e.ename, d.deptno from emp e left join 
> 
> dept d on e.deptno = d.deptno;

**6.3.5** **右外连接**

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

> hive (default)> select e.empno, e.ename, d.deptno from emp e right join 
> 
> dept d on e.deptno = d.deptno;

**6.3.6** **满外连接**

满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字

段没有符合条件的值的话，那么就使用 NULL 值替代。

> hive (default)> select e.empno, e.ename, d.deptno from emp e full join 
> 
> dept d on e.deptno = d.deptno;

**3****）多表连接查询**

> hive (default)>SELECT e.ename, d.dname, l.loc\_name 
> 
> FROM emp e 
> 
> JOIN dept d 
> 
> ON d.deptno = e.deptno 
> 
> JOIN location l 
> 
> ON d.loc = l.loc;

大多数情况下，Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。本例中会首先

启动一个 MapReduce job 对表 e 和表 d 进行连接操作，然后会再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 l;进行连接操作。

注意：为什么不是表 d 和表 l 先进行连接操作呢？这是因为 Hive 总是按照从左到右的

顺序执行的。

**优化：当对** **3** **个或者更多表进行** **join** **连接时，如果每个** **on** **子句都使用相同的连接键的**

**话，那么只会产生一个** **MapReduce job****。**

## **第6章 查询（语法与MySQL一样）** ##

**注意：Hive没有索引，Hive查询的效率源于数据的分区分类。**

**4** **个** **By** **区别：**

**（1）Order By：**对数据进行**全局排序**，并且**只有一个Reducer**，不管我们对Reducer的数量如何设置，也只会有一个Reducer执行，**因为全局排序只能在一个Reducer中执行。**（注意：Order By进行全局排序，所以效率低，在公司生产环境中一般运行不了，因为数据太大，然而只有一个Reducer）

**（2）Sort By：****分区内有序，**也就是每个Reducer内部进行排序，对全局结果集来说不是有序的。结果如下图类似：

![09af4b936b4f49919596793bb62776df.png][]

**（3）Distribute By：****它的功能类似于MR中的Partition，对数据进行分区，一般结合Sort By一起使用。**用法如下：（先将数据分区，再将各个分区内的数据进行排序）

![9628bdfefe8d467385c0e9eb1031f0ba.png][]

**（4）Cluster By：**当 Distribute by 和 Sorts by 字段相同时，可以使用 Cluster by 方式。Cluster by 除了具有 Distribute by 的功能外还兼具 Sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。（一般用的比较少，因为很少有字段相同的情况）

![0e2c5675a66b4f1982175effb85c9dc5.png][]

## **第** **7** **章 分区表和分桶表** ##

**7.1** **分区表**

分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所

有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。**在查询时通过** **WHERE** **子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多，避免全表扫描。** （效率高的原因是因为查询直接在指定的分区表中进行，而不会进行全表扫描）

**7.1.1** **分区表基本操作**

**（1）创建分区表语法**

> hive (default)> create table dept\_partition( deptno int, dname string, loc int) 
> 
> partitioned by (day string) 
> 
> row format delimited fields terminated by '\\t';

注意：分区字段不能是表中已经存在的字段，可以将分区字段看作表的伪列。另外partitioned by**注意partitioned，是ed结尾**，后面很多地方partition没有ed结尾。

**（2）加载数据到分区表中，把数据加载到表的指定分区中**

> hive (default)> load data local inpath 
> 
> '/opt/module/hive/datas/dept\_20200401.log' into table dept\_partition 
> 
> **partition(day='20200401');**

**注意：分区表加载数据时，必须指定分区（不指定分区的话系统会自动生成一个分区名，此分区名对于日后的查询比较麻烦），一般在加载数据的时候指定好分区，分区命名一般以日期的形式，因为公司存储数据大都是按日期来存储。**如：

![2360d26c422c4c148b83977f40f256e8.png][]

**（3）查询分区表中数据**

单分区查询

> hive (default)> select \* from dept\_partition where day='20200401';

多分区联合查询

> hive (default)> select \* from dept\_partition where day='20200401' 
> 
> union 
> 
> select \* from dept\_partition where day='20200402' 
> 
> union 
> 
> select \* from dept\_partition where day='20200403'; 
> 
> hive (default)> select \* from dept\_partition where day='20200401' or
> 
> day='20200402' or day='20200403';

注意：在查询语句中，在where条件中可以像使用正常的字段一样使用分区字段，如day='20200401'一样，直接使用字段即可。

1.  **增加分区**

**语法：alter table 表名 add partition（分区名字）；**

创建单个分区

> hive (default)> **alter table** dept\_partition **add** partition(day='20200404');

同时创建多个分区

> hive (default)> **alter table** dept\_partition **add** partition(day='20200405') 
> 
> partition(day='20200406');

**注意：同时创建多个分区时，分区与分区之间用空格隔开。**

**（5）****删除分区**

删除单个分区

> hive (default)> **alter table** dept\_partition **drop** partition (day='20200406');

同时删除多个分区

> hive (default)> **alter table** dept\_partition **drop** partition(day='20200404'), partition(day='20200405');

**注意：同时删除多个分区时，分区与分区之间用逗号‘，’隔开。**

**（6）****查看分区表有多少分区**

hive> show partitions dept\_partition;

**注意：这里partitions是以s结尾的。**

**（7）****查看分区表结构**

> hive> desc formatted dept\_partition;

**总结partition：创建分区表时，partitioned以ed结尾；增删查时，partition不变，查看分区表时，partitions以s结尾。**

**7.1.2 二级分区**

思考: 如果一天的日志数据量也很大，如何再将数据拆分?可以用二级分区，按天按小时进行分区，两层分区。

**1.创建二级分区表**

> hive (default)> create table dept\_partition2( deptno int, dname string, loc string) partitioned by (day string, hour string)
> 
> row format delimited fields terminated by '\\t';

**注意：二级分区的创建，就是在partitioned分区里写两个字段，中间用逗号隔开。**

**2.正常的加载数据**

（1）加载数据到二级分区表中

> hive (default)> load data local inpath 
> 
> '/opt/module/hive/datas/dept\_20200401.log' into table dept\_partition2 partition(day='20200401', hour='12');

**注意：加载数据要指定二级分区的位置，即传入两个分区字段的值。**

（2）查询分区数据

> hive (default)> select \* from dept\_partition2 where day='20200401' and 
> 
> hour='12';

**注意：这里where条件后面，可以不给条件，也可以只给一个条件，也可以给两个条件，但条件与条件之间用and连接。**

**3.把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式**

提示：这里的意思是，用hadoop命令将数据上传到分区目录下时，分区目录是用hadoop命令创建的，不是用HQL语句创建的分区，那么用Hive语句操作这个分区目录时，不会显示此目录的信息，因为不是用HQL创建的，那么如何让Hadoop命令创建的分区目录可以成为HQL语句的呢？接下来就是三种关联方式。

1.  **方式一：上传数据后修复**

**用hadoop命令创建分区目录并将数据放入分区中**

> hive (default)> dfs -mkdir -p 
> 
> /user/hive/warehouse/mydb.db/dept\_partition2/day=20200401/hour=13;

> hive (default)> dfs -put /opt/module/datas/dept\_20200401.log 
> 
> /user/hive/warehouse/mydb.db/dept\_partition2/day=20200401/hour=13;

查询数据（查询不到刚上传的数据，因为不是用HQL创建的分区）

> hive (default)> select \* from dept\_partition2 where day='20200401' and 
> 
> hour='13';

**执行修复命令**

> **hive> msck repair table dept\_partition2;**

再次查询数据 （执行了上面一条命令后分区目录会自动整理，Hadoop创建的分区目录有效）

> hive (default)> select \* from dept\_partition2 where day='20200401' and 
> 
> hour='13';

**（****2****）方式二：上传数据后添加分区**

**用hadoop命令创建分区目录并将数据放入分区中**

> hive (default)> dfs -mkdir -p 
> 
> /user/hive/warehouse/mydb.db/dept\_partition2/day=20200401/hour=14;

> hive (default)> dfs -put /opt/module/hive/datas/dept\_20200401.log 
> 
> /user/hive/warehouse/mydb.db/dept\_partition2/day=20200401/hour=14;

**执行添加分区 （相当于添加一个分区，分区名和hadoop创建的分区目录一样，将hadoop创建的分区目录覆盖掉，使这个分区名有效）**

> **hive (default)> alter table dept\_partition2 add** 
> 
> **partition(day='20200401',hour='14');**

查询数据

> hive (default)> select \* from dept\_partition2 where day='20200401' and 
> 
> hour='14';

**（3）方式三：创建文件夹后** **load** **数据到分区**

提示：这种方式就是正常的load加载数据，和上面（2）的方式差不多，用相同的分区名覆盖掉hadoop创建的分区目录，然后使分区名有效

用hadoop命令创建分区目录

> hive (default)> dfs -mkdir -p 
> 
> /user/hive/warehouse/mydb.db/dept\_partition2/day=20200401/hour=15;

**用load data加载数据的方式上传数据**

> **hive (default)> load data local inpath** 
> 
> **'/opt/module/hive/datas/dept\_20200401.log' into table** 
> 
> **dept\_partition2 partition(day='20200401',hour='15');**

查询数据

> hive (default)> select \* from dept\_partition2 where day='20200401' and 
> 
> hour='15';

**4.动态分区**

**一、（复杂方式，可以不用）**

动态分区指的是系统自动进行分区，我们给定一个分区字段，系统会按照这个字段来进行智能分区，不需要我们指定分区。而静态分区指的是我们给定一个分区，把数据insert进去。

1.  **开启动态分区功能（默认** **true****，开启）**

> hive.exec.dynamic.partition=true

**（****2****）设置为非严格模式**（动态分区的模式，**默认** **strict**，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）

![8114bb45ed034724b4c87d15e607c3ee.png][]

注意：默认是严格模式，不能使用动态分区，用动态分区的时候要设置为非严格模式。

**（3）****案例实操**

需求：将 dept 表中的数据按照地区（loc 字段），插入到目标表 dept\_partition 的相应

分区中。

**（****1****）创建目标分区表**

> hive (default)> create table dept\_partition\_dy(id int, name string) 
> 
> partitioned by (location int) 
> 
> row format delimited fields terminated by '\\t';

**注意：分区字段不能和普通字段的名字相同。**

**（****2****）设置动态分区 （先设置为非严格模式，再往分区表里插入数据）**

> hive (default)> set hive.exec.dynamic.partition.mode = nonstrict; 
> 
> hive (default)> insert into table dept\_partition\_dy partition(**loc****ation**) select 
> 
> deptno, dname, **loc****ation** from dept;

**注意：partion（）分区里只填分区字段名即可**，不能给定参数（给定参数属于静态分区），然后在select语句中的字段里，**最后一个字段要填写分区字段，与括号里的分区名相对于，这样就能告诉系统哪个是分区字段，这里分区字段即location**。

（3）查看目标分区表的分区情况

> hive (default)> show partitions dept\_partition\_dy;

说明：其实动态分区，就是给定一个分区字段，然后将另一个表的数据导入进来，但导入进来的数据会按照分区归类。

比如创建了一个dept\_1的表，然后要将dept表中的数据导入到dept\_1表中。dept\_1表中有id int, name string两个字段以及分区字段location int，dept表中有deptno, dname, **loc****ation****三个字段，其中dept表的location字段在插入数据的时候放在最后，那么系统将会按照dept表中的location字段里的数据进行分区。dept\_1和dept表中对应的location名字可以不一样。只要对应语法上的位置即可。**

**二、简单方式，3.0版本新特性（常用）**

**上面的方式一中，我们需要先指定为非严格模式，再将数据进行分区插入；而在3.0版本中，可以省略两个步骤。（1.不用开启非严格模式，系统自动转换。2.不需要指定分区字段，在select语句中最后一个字段为分区字段即可。）**

**如：**

**（****1****）创建目标分区表**

> hive (default)> create table dept\_partition\_dy(id int, name string) 
> 
> partitioned by (location int) 
> 
> row format delimited fields terminated by '\\t';

**（****2****）插入数据并且自动分区归类**

> hive (default)> insert into table dept\_partition\_dy
> 
> select deptno, dname, **loc****ation** from dept;

**说明：和上面的语句对比一下，省略了partion（location）这句，以及不用指定非严格模式。**

**分桶表**

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理

的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围

划分。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

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

**分桶表就是将一个表分成多个小文件，一般用于数据量极大的时候。（了解即可，不重点掌握）**

**创建分桶表**

> create table stu\_buck(id int, name string)
> 
> clustered by(id) 
> 
> **into 4 buckets**
> 
> row format delimited fields terminated by '\\t';

## **第 8 章 函数** ##

**8.1系统内置函数**

**1）查看系统自带的函数**

> **hive> show functions;**

**2）显示自带的函数的用法**

> **hive> desc function upper;**

**3）详细显示自带的函数的用法**

> **hive> desc function extended upper;**

![27d1c7adf7cc46b9a2d976e1a88c6d8b.png][]

注意：一、多指的是输入数据的行数，不是数据的个数，比如UDAF（聚合函数），多行数据聚合成一行数据；再比如UDTF：一行数据变成多行数据（wordcount案例就是这种）

**8.2** **常用内置函数**

**8.2.1 空字段赋值** NVL( value，default\_value)

**（1）****函数说明**

NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default\_value)。它的功能是如

果 value 为 NULL，则 NVL 函数返回 default\_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。

其中default\_value可以为字段，也可以为确定的值。

**（2）查询：如果员工的** **comm** **为** **NULL****，则用****-1** **代替**

> hive (default)> select comm,nvl(comm, -1) from emp;

**（3）查询：如果员工的** **comm** **为** **NULL****，则用领导****id列****代替**

> hive (default)> select comm,nvl(comm, id) from emp;

**8.2.2 CASE WHEN THEN ELSE END**

**按需求查询数据**

![7e7cc490e7c64baf8f48872aa2261e1d.png][]

sum(case sex when '男' then 1 else 0 end) male\_count这句表示：如果sex等于男返回1，否则返回0，然后将结果累加。

**多条件写法：**sum(case sex when ‘a’ then 1 when ‘b’ then 2...else 0 end)

多条件写法就是将when then 多次写即可。

**上面语句效果等同于下面，if（）函数也可以嵌套**

![905446b14e284be198ceccbac45046a1.png][]

**8.2.3** **行转列**

**（1）****CONCAT(string A/col, string B/col****…****)****：**

返回输入字符串连接后的结果，支持任意个输入字符串或字段;如：

**拼接字符：**![ecc129900a32412184ab3cc863b54291.png][]

结果显示：a-b-c

**拼接字段：**![0ce3a81253aa4eaa9e937b3ff6b176e3.png][]

**（****2****）****CONCAT\_WS(separator, str1, str2,...)****:**

第一个参数为一个分隔符，后面都是字符串或字段。如：

![a324fbf7da274d0999fb0192b9ffe15b.png][]

结果显示：a-b-c

注意: CONCAT\_WS must be "string or array<string>，参数必须为String或String数组

如：select concat\_ws(‘|’,collect\_set(name));将数组进行处理。Name1|name2|name3|....

**（****3****）****collect\_list( column ):**

传入字段，将该列返回成一行数据（数组形式），并且不去除重复数据；如：

![373a6a255f2e4bfe871d50b2ca776576.png][]

![6de82dfb0b7f4818909680ef871393db.png][]

**（****4****）****collect\_set( column ):**

传入字段，将该列返回成一行数据，并且去除重复数据；如：

![0a918650d230475995abed0937e68994.png][]

**注意：（3）和（4）两个函****数只接受基本数据类型，其他类型不支持！**

**列转行：**

1.  **EXPLODE(col)****：**将一列中复杂的 Array 或者 Map 结构拆分成多行。

LATERAL VIEW （测斜表，用于把炸裂出的列与原表中的列相关联）

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

**LATERAL VIEW** **EXPLODE(col)** **表别名 AS 被拆分的列的别名**

**说明：**当需求只涉及拆分当前字段时，可以只用explode（column），如下方式；如果需求涉及到拆分当前字段后并关联原表字段信息，那就需要lateral View 关键字，不然字段行数匹配不上，如5.2示例。

**如：**![18cfd5db30144e23bc149f3475dfb9c4.png][]

**将**![3b43a6980d5e494dbf2c34753ab12b4f.png][]**------>** ![92282619475746a1bff53fb4052cf850.png][]

注意：explode（）只支持array 或 map ，不支持其他类型，其他类型需要用

split（字段，分隔符）函数进行处理，将其转化为array类型；如：

![c9900f738ea04e949371165b7ce2c9bf.png][]

**（5.2）案例需求，将如下格式变为后面格式：**

![d3fbf2b929f444ec81cd0a20db3d10d0.png][]

**----->**![a6dc6637faa24f8c94e4608a88029327.png][]

**建表语句：**

![7c8b91cad0954f6a99b699433c1c5b8d.png][]

![a3ea75717bab4427b539603de7c7661d.png][]

**8.****3****窗口函数（开窗函数）**

**1****）相关函数说明**

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

说白了，就是像Lateral View一样，匹配行，不然的话数据不对称。有了OVER（）函数可以更方便地进行数据的处理，如行与行之间数据的相加等操作。

也跟group by差不多，对数据集进行分组，但比group by更加灵活，可以指定分组以及排序，其实就是开窗口，不同的数据在不同的区域进行数据处理。

CURRENT ROW：当前行

n PRECEDING：往前 n 行数据

n FOLLOWING：往后 n 行数据

UNBOUNDED：起点，

UNBOUNDED PRECEDING 表示从前面的起点，

UNBOUNDED FOLLOWING 表示到后面的终点

**LAG(col,n,default\_val)****：往前第** **n** **行数据，default\_val可填可不填，不填默认用null代替，也可以填个固定默认值，也可以填一个字段，表示用当前这行数据。（default\_val用于补充没有匹配到数据的行。）**

**LEAD(col,n, default\_val)****：往后第** **n** **行数据，default\_val 和LAG函数中的一样。**

**NTILE(n)****：**把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对

于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

即：把数据分成n等份，如有10条数据，n为5，则把数据等分为5份，每份2条数据。

**说明：蓝色字体是用在OVER（）函数里面的，用于控制分组的行数，并且不常用；**

**红色字体是用在OVER（）函数前面的，用于数据的处理，并且常用；**

**特别说明：上述的这些函数必须跟着over（）一起使用，不能单独使用。**

**窗口函数一般跟聚合函数一起使用，进行数据的加减计算等，来完成需求：**

**聚合函数：count、sum、avg、max、min**

**2****）数据准备：****name****，****orderdate****，****cost**

> jack,2017-01-01,10
> 
> tony,2017-01-02,15
> 
> jack,2017-02-03,23
> 
> tony,2017-01-04,29
> 
> jack,2017-01-05,46
> 
> jack,2017-04-06,42
> 
> tony,2017-01-07,50
> 
> jack,2017-01-08,55
> 
> mart,2017-04-08,62
> 
> mart,2017-04-09,68
> 
> neil,2017-05-10,12
> 
> mart,2017-04-11,75
> 
> neil,2017-06-12,80
> 
> mart,2017-04-13,94

**3）创建** **hive** **表并导入数据**

> create table business( 
> 
> name string, 
> 
> orderdate string,
> 
> cost int 
> 
> ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; 
> 
> load data local inpath "/opt/module/data/business.txt" into table 
> 
> business;

1.  **按需求查询数据**

**（4.1）****查询顾客及总人数**

> select 
> 
> name,
> 
> count(\*) over () 
> 
> from business;

**结果显示：**

![be3751ba48284f808400b74ee9a19c29.png][]

**说明：**如果没有over（）函数的话，那么查询出来的count（\*）只有一行数据，name字段与count（\*）字段匹配不上，所以会出错。而用了over（）函数之后，count（\*）字段的数据会与每一个name字段的数据相匹配，因为over（）函数将整张表作为了一个窗口。

**（4.2）****查询在** **2017** **年** **4** **月份购买过的顾客及总人数**

> select 
> 
> name,
> 
> count(\*) over () 
> 
> from business 
> 
> where substring(orderdate,1,7) = '2017-04' 
> 
> group by name;

**结果显示：**

![68e4463ac19a4621a47f8b3d36953871.png][]

注明：这里用over（），over（）中没有写其他功能，那它会将整个表作为一个整体计算并对应每行数据显示出来。

**substring（字符串，起始位置，截取多少位）：**substring（）函数用于截取字符串，字符串位置从0开始计算，注意第三个参数代表截取多少位，，而不是截取到字符串哪个位置，可以不填写表示截取到字符串最后一位。

**（4.3）****查询顾客的购买明细及月购买总额**

> select 
> 
> name,
> 
> orderdate,
> 
> cost,
> 
> sum(cost) over(partition by name, month(orderdate)) 
> 
> from business;

**结果显示：**

![ae7fbd25dab54761af9caca4b4ce19e7.png][]

**说明：**sum(cost) over(partition by name, month(orderdate)) 表示，按照相同人名、同一月份进行开窗，数据在各自的窗口进行处理，sum（cost）计算的是各个区内的数据，不是整个表的数据。也就是对相同人名、同一月份进行开窗。

**（4.4）****将每个顾客的** **cost** **按照日期进行累加**

> select name,orderdate,cost, 
> 
> sum(cost) over() as sample1,--所有行相加 
> 
> sum(cost) over(partition by name) as sample2,--按 name 分组，组内数据相加 
> 
> sum(cost) over(partition by name order by orderdate) as sample3,--按 name 
> 
> 分组，组内数据累加 
> 
> sum(cost) over(partition by name order by orderdate rows between 
> 
> UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一样,由起点到 
> 
> 当前行的聚合 
> 
> sum(cost) over(partition by name order by orderdate rows between 1 
> 
> PRECEDING and current row) as sample5, --当前行和前面一行做聚合 
> 
> sum(cost) over(partition by name order by orderdate rows between 1 
> 
> PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 
> 
> sum(cost) over(partition by name order by orderdate rows between current 
> 
> row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 
> 
> from business;

注意：rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

**（4.5）****查看顾客上次的购买时间**

> select 
> 
> name,
> 
> orderdate,
> 
> cost, 
> 
> lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) 
> 
> as time1, 
> 
> lag(orderdate,2) over (partition by name order by orderdate) as 
> 
> time2 
> 
> from business;

**结果显示：**

![c191ebaee7cf4fd58cc45f3b40b7cab7.png][]

**说明：**lag（col，n，default\_val）函数要用在over（）前面。表示往前n行取数据，其中前面没有行的，则用default\_val代替。LEAD（col，n，default\_val）函数用法和LAG（col，n，default\_val）函数一样，表示往后n行取数据。

**（4.6）****查询前** **20%****时间的订单信息**

select name,

orderdate,

cost,

ntile(5) over(order by orderdate) sorted

from business;

**结果显示：**

![cab7400c6e534d118bb8277ea3ca0b90.png][]

**说明：**ntile（n）函数会将数据等分成n份。

> select \* from ( 
> 
> select name,
> 
> orderdate,
> 
> cost, 
> 
> ntile(5) over(order by orderdate) sorted 
> 
> from business ) t 
> 
> where sorted = 1;

**结果显示：**

![9b295e042e914cab97a1265a6e6bc282.png][]

**8.****4** **Rank****（窗口函数）**

**1****）函数说明：就是对数据进行排序，****注意要跟over（）一起用才行,并且Over（）函数必须跟着分区或排序条件。**

RANK() ：排序相同时会重复，总数不会变，如：1-2-3-3-5-6

DENSE\_RANK() ：排序相同时会重复，总数会减少,如：1-2-3-3-4-5

ROW\_NUMBER() ：会根据顺序计算,如：1-2-3-4-5

**例:结果显示的是rank（）函数**

> select 
> 
> name, 
> 
> subject, 
> 
> score, 
> 
> **rank() over(partition by subject order by score desc) rp,** 
> 
> dense\_rank() over(partition by subject order by score desc) drp, 
> 
> row\_number() over(partition by subject order by score desc) rmp 
> 
> from score
> 
> Where rp < 4;

![f6d2c75755594e94abc3516cae9cd5c5.png][]

**8.5自定义函数**

**1****）**Hive 自带了一些函数，比如：max/min 等，但是数量有限，自己可以通过自定义 UDF 来

方便的扩展。

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

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

（1）UDF（User-Defined-Function）

一进一出

（2）UDAF（User-Defined Aggregation Function）

聚集函数，多进一出

类似于：count/max/min/sum/avg

（3）UDTF（User-Defined Table-Generating Functions）

一进多出

如 lateral view explode()

**4）**编程步骤：

（1）继承 Hive 提供的类

> org.apache.hadoop.hive.ql.udf.generic.GenericUDF 
> 
> org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

（2）实现类中的抽象方法

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

添加 jar

> add jar linux\_jar\_path

创建 function

> create \[temporary\] function \[dbname.\]function\_name AS class\_name;

（4）在 hive 的命令行窗口删除函数

> drop \[temporary\] function \[if exists\] \[dbname.\]function\_name;

**注明：\[temporary\]为临时的意思，可写可不写，写了temporary则当窗口关闭之后函数失效，不写temporary则函数时永久性的关闭窗口也不会自动删除函数。**

**Function\_name :函数名**

**Class\_name :函数的全类名**

**（5）自定义函数的步骤：**

（1）自定义 UDF：继承 GenericUDF，重写 evaluate 方法，然后打包加载到 hive 中，在 hive 中创建函数导入自定义 UDF 的全类名即可。

（2）自定义 UDTF：继承 GenericUDTF，重写 3 个方法：initialize（自定义输出的列名和类型）、process（将结果返回 forward（数据集））、close，然后打包加载到 hive 中，在 hive 中创建函数导入自定义 UDTF 的全类名即可。

[f44ffd048f734108b0a93ddf25d008ed.png]: https://img-blog.csdnimg.cn/f44ffd048f734108b0a93ddf25d008ed.png
[d8a25389d7d04dd3b580ab7b784af40d.png]: https://img-blog.csdnimg.cn/d8a25389d7d04dd3b580ab7b784af40d.png
[b01329e53f9f42049fa8ff808347185c.png]: https://img-blog.csdnimg.cn/b01329e53f9f42049fa8ff808347185c.png
[c3c60f6002d741e69a4d125ea7960f14.png]: https://img-blog.csdnimg.cn/c3c60f6002d741e69a4d125ea7960f14.png
[e4657af98c0d496784d1cc14921c5d68.png]: https://img-blog.csdnimg.cn/e4657af98c0d496784d1cc14921c5d68.png
[96ca0c5697b94870af5ffc9bc0712af8.png]: https://img-blog.csdnimg.cn/96ca0c5697b94870af5ffc9bc0712af8.png
[135b40a6ab5c47f9861e47172b7d0db8.png]: https://img-blog.csdnimg.cn/135b40a6ab5c47f9861e47172b7d0db8.png
[f891e8929eb24499926de4387b95dbfa.png]: https://img-blog.csdnimg.cn/f891e8929eb24499926de4387b95dbfa.png
[09af4b936b4f49919596793bb62776df.png]: https://img-blog.csdnimg.cn/09af4b936b4f49919596793bb62776df.png
[9628bdfefe8d467385c0e9eb1031f0ba.png]: https://img-blog.csdnimg.cn/9628bdfefe8d467385c0e9eb1031f0ba.png
[0e2c5675a66b4f1982175effb85c9dc5.png]: https://img-blog.csdnimg.cn/0e2c5675a66b4f1982175effb85c9dc5.png
[2360d26c422c4c148b83977f40f256e8.png]: https://img-blog.csdnimg.cn/2360d26c422c4c148b83977f40f256e8.png
[8114bb45ed034724b4c87d15e607c3ee.png]: https://img-blog.csdnimg.cn/8114bb45ed034724b4c87d15e607c3ee.png
[27d1c7adf7cc46b9a2d976e1a88c6d8b.png]: https://img-blog.csdnimg.cn/27d1c7adf7cc46b9a2d976e1a88c6d8b.png
[7e7cc490e7c64baf8f48872aa2261e1d.png]: https://img-blog.csdnimg.cn/7e7cc490e7c64baf8f48872aa2261e1d.png
[905446b14e284be198ceccbac45046a1.png]: https://img-blog.csdnimg.cn/905446b14e284be198ceccbac45046a1.png
[ecc129900a32412184ab3cc863b54291.png]: https://img-blog.csdnimg.cn/ecc129900a32412184ab3cc863b54291.png
[0ce3a81253aa4eaa9e937b3ff6b176e3.png]: https://img-blog.csdnimg.cn/0ce3a81253aa4eaa9e937b3ff6b176e3.png
[a324fbf7da274d0999fb0192b9ffe15b.png]: https://img-blog.csdnimg.cn/a324fbf7da274d0999fb0192b9ffe15b.png
[373a6a255f2e4bfe871d50b2ca776576.png]: https://img-blog.csdnimg.cn/373a6a255f2e4bfe871d50b2ca776576.png
[6de82dfb0b7f4818909680ef871393db.png]: https://img-blog.csdnimg.cn/6de82dfb0b7f4818909680ef871393db.png
[0a918650d230475995abed0937e68994.png]: https://img-blog.csdnimg.cn/0a918650d230475995abed0937e68994.png
[18cfd5db30144e23bc149f3475dfb9c4.png]: https://img-blog.csdnimg.cn/18cfd5db30144e23bc149f3475dfb9c4.png
[3b43a6980d5e494dbf2c34753ab12b4f.png]: https://img-blog.csdnimg.cn/3b43a6980d5e494dbf2c34753ab12b4f.png
[92282619475746a1bff53fb4052cf850.png]: https://img-blog.csdnimg.cn/92282619475746a1bff53fb4052cf850.png
[c9900f738ea04e949371165b7ce2c9bf.png]: https://img-blog.csdnimg.cn/c9900f738ea04e949371165b7ce2c9bf.png
[d3fbf2b929f444ec81cd0a20db3d10d0.png]: https://img-blog.csdnimg.cn/d3fbf2b929f444ec81cd0a20db3d10d0.png
[a6dc6637faa24f8c94e4608a88029327.png]: https://img-blog.csdnimg.cn/a6dc6637faa24f8c94e4608a88029327.png
[7c8b91cad0954f6a99b699433c1c5b8d.png]: https://img-blog.csdnimg.cn/7c8b91cad0954f6a99b699433c1c5b8d.png
[a3ea75717bab4427b539603de7c7661d.png]: https://img-blog.csdnimg.cn/a3ea75717bab4427b539603de7c7661d.png
[be3751ba48284f808400b74ee9a19c29.png]: https://img-blog.csdnimg.cn/be3751ba48284f808400b74ee9a19c29.png
[68e4463ac19a4621a47f8b3d36953871.png]: https://img-blog.csdnimg.cn/68e4463ac19a4621a47f8b3d36953871.png
[ae7fbd25dab54761af9caca4b4ce19e7.png]: https://img-blog.csdnimg.cn/ae7fbd25dab54761af9caca4b4ce19e7.png
[c191ebaee7cf4fd58cc45f3b40b7cab7.png]: https://img-blog.csdnimg.cn/c191ebaee7cf4fd58cc45f3b40b7cab7.png
[cab7400c6e534d118bb8277ea3ca0b90.png]: https://img-blog.csdnimg.cn/cab7400c6e534d118bb8277ea3ca0b90.png
[9b295e042e914cab97a1265a6e6bc282.png]: https://img-blog.csdnimg.cn/9b295e042e914cab97a1265a6e6bc282.png
[f6d2c75755594e94abc3516cae9cd5c5.png]: https://img-blog.csdnimg.cn/f6d2c75755594e94abc3516cae9cd5c5.png