Hive知识点总结

Hive简介：

Hive是由2007年8月由FaceBook开源用于解决海量结构化日志的数据统计工具，是基于Hadoop的一个数据仓库的管理工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询的功能。本质是将HQL语句转换为MR的任务进行运算，底层由HDFS来提供数据存储。Hive保存数据默认在  /user/hive/warehouse

Hive的优缺点：

优点：

1. 操作接口采用类SQL的语法
2. 避免了编写MR程序
3. Hive的执行延迟较高
4. Hive的优势是处理大数据，但是处理小数据没有优势
5. Hive支持用户的自定义函数，用户可以根据自己的需求实现自己的函数

缺点：

1. HQL语句的表达能力有限
2. 迭代算法无法表达
3. 数据挖掘方面不太擅长
4. Hive的本身效率较低
5. Hive自动生成的MR程序，通常情况下不够智能化
6. Hive的调优比较困难

Hive的执行过程：

首先来了一条HQL语句，提交到客户端上，然后会远程调用组件提交到driver端，driver端由解释器，解释时就会访问MetaStore服务，告诉我们表的位置等信息，解释之后，会使用编译器进行编译，然后进入优化器，优化后生成底层的MR任务，提交至Yarn上，然后在HDFS上读取数据。

Hive的beeline启动方式：

HiveServer2提供了一种新的命令行工具-Beeline，它基于SQLLine CLI 的JDBC客户端。Beeline的工作方式有两种：嵌入模式和远程模式。嵌入模式：在此模式下，可以返回一个嵌入式的hive（类似于hive CLI）。远程模式：会通过thrift协议与某个单独的hiveServer2进程连接通信。

10000:hive启动JDBC时产生的一个端口号

用户名：offcn

密码；hive到3.0版本密码都是弱密码，也就是说数仓只要用户名正确，权限正确，便会登录成功。

Hive的基础命令：

1. Hive   -e   SQL   （执行完SQL后进入命令行）
2. Hive   -f    SQL脚本文件
3. 查看HDFS文件系统 dfs   -ls

DDL语句：

一：库相关语句

1. 创建库

Create (database|schema) [if not exists] name [comment database_comment] [location hdfs_path] [with dbproperties (property_name=property_value,.....)];

注释：comment 为database增加一个描述信息

              Location  默认情况下，hive数据库中的表的数据存储在

/hive.metastore.warehouse.dir 指定的目录下

  With dbproperties  用于为数据库增加一些和其相关的键值对属性信息，例如创建的时间，作者等

E.g.

Create database db01 comment “测试库01” location ‘/test-hive/’ with dbproperties(‘author’=’LYD’,’date’=’2023-08-17’);

1. 查询库

Show database [like ‘identifier_with_wildcard’];

Describe|desc database [extended] database_name;

注释：like关键字可以进行模式匹配

              Extended会显示创建数据库时指定的dbproperties信息

e.g.  

Show databases;

Show databases like ‘db*’;

Describe database db01;

Describe database extended db01;

1. 修改库

Alter database  database_name set dbproperties(proName=proval,....);

注释：hive只支持对dbproperties和owner的更改，不支持对数据库其他元数据信 息的修改。

E.G. alter database db01 set dbproperties (‘user’=’hadoop’,’date’=’1997-11-01’);

1. 删除库

Drop database [if exists] database_name [restrict|cascade];

注释：drop只能删除空表

              Cascade：默认情况下，hive不允许删除一个含有表的数据库，用户在删除   数据库的时候，要么先删除数据库中所有的表，然后删除数据库；   要么在删除的命令后面加上cascade，这样hive会自行删除数据库 中的所有的表。

Restrict：和默认值一样，就是必须先手动删除库中的所有的表，然后才能删除数据库。

E.g. drop database db01 cascade;

二：表相关语句

1. 创建表（即在HDFS上创建一个目录）

1.1 内部表（管理表）

默认创建的表都是管理表/内部表，这种表，hive会控制数据的生命周期，hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir所定义的目录的子目录下。当我们删除一个管理表时，hive也会删除这个表中数据以及元数据，管理表不适合和其他工具共享数据。

E.g.  

 创建数据库，创建表，准备数据并上传至表中：

 Create database offcn;

 Use offcn;

 Create table if not exists manage_emp(

Id int ,name string

) row format delimited fields terminated by ‘\t’;

Vim emp.txt;

01 hadoop

02 hive

03 hbase

上传数据到表目录下：

Hdfs dfs -put emp.txt /user/hive/warehouse/offcn.db/managed_emp;

1.1.1使用As根据查询结果来创建表

        Create table emp01 as select * from managed_emp;

1.1.2like 根据已存在的表结构创建表（没有数据）

        Create table if not exists emp02 like managed_emp;

1.2 外部表 external

因为表是外部表，所以hive并非认为完全拥有这份数据。删除该表并不会删除这份数据，不过描述表的元数据信息会被删除掉。

E.g.

Create external table if not exists external_emp(

Id int ,name string )

Row format delimited fields terminated by ‘\t’;

Hdfs dfs -put emp.txt /user/hive/warehouse/offcn.db/external_emp;

注意：使用as语句无法创建外部表，但是可以使用like进行创建表结构，但是没有数据。

Create external table if not exists emp04 like managed_emp;

1.3 临时表（temporary），不支持分区字段和创建索引

  临时表只对当前的session有效，session退出后，表会自动删除。如果创建的临时表的表名已经存在，那么当前session引用到该表名时实际用的是临时表，只有drop或者rename临时表后，才能使用原始表；临时表可以存储在内存或SSD中，使用hive.exec.temporary.table.storage参数进行配置，该参数有三种取值：memory、ssd、default（memory）

E.g.

Create temporary table if not exists temporary_emp(

Id int,name string)

Row format delimited fields terminated by ’\t’;

内部表和外部表之间的转换

内部表->外部表

Alter table managed_emp set dbproperties(‘EXTERNAL’=’TRUE’);

内部表<-外部表

Alter table external_emp set dbproperties(‘EXTERNAL’=’FALSE’);

1.4 分区表(分区字段不能是表字段)

为了查询的快捷，将一个大的文件拆分成若干个小文件，分区表会按照某个字段进行拆分，查询时，只查询其中的一小部分。有效的避免了全表扫描。

创建单级分区表：

Create table partiiton_emp(id int ,name string ) partitioned by (age int ) row format delimited fields terminated by ‘\t’;

创建多级分区表：

Create table partition_emp2(id int ,name string ,addr string ) partitioned by (month string ,day string ) row format delimited fields terminated by ‘\t’;

1. 显示表信息

Desc emp01;

Desc formatted emp01;（显示详细信息）

1. 修改表

3.1 重命名表(外部表只修改元数据信息，在hdfs上的真实目录名称不会改变)

Alter table managed_emp rename to external_emp01;

Alter table external_emp rename to managed_emp01;

3.2 查看、修改、删除表分区

添加分区

Alter table partiiton_emp add partition(age=’20’);

添加多个分区

Alter table partition_emp add partition(age=‘10’) partition(age=‘6’);

添加多级分区

Alter table partition_emp add partition(month=2,day=20);

删除分区

Alter table partition_emp drop partition (age=’30’);

查看该表的全部分区

Show partitions partition_emp;

3.3 添加、修改、删除表

添加age列

Alter table managed_emp01 add columns(age int);

修改age字段为address

Alter table managed_emp01 change column age address string;

使用新字段替换原有表结构

Alter table managed_emp01 replace columns (id int ,name string);

3.4 删除表（drop）、截断表（truncate）

Drop table [if exists] table_name [purge];

purge参数为，直接删除不进垃圾回收站，永久删除！

删除内部表,此时元数据信息和hdfs上的真实信息都没有了

Drop table emp01;

Drop table emp01 purge;

删除外部表，此时元数据信息会被删除掉，但是hdfs上的数据仍然还在

Drop table external_emp01;

截断内部表

Truncate table emp01;

截断外部表(truncate 不能删除外部表数据)

Truncate table external_emp01;

Error: Error while compiling statement: FAILED: SemanticException [Error 10146]: Cannot truncate non-managed table external_emp01. (state=42000,code=10146)

Drop、delete、truncate三者的区别：

Drop：直接删除表、库，效率较高

Delete：不加where条件时会删除整张表，但是删除时是一条一条删除

Truncate：直接把表删除，重建表结构，无法删除外部表

1.5分桶表（本质是分数据，分桶字段必须是表字段）

每一个表或者分区，hive可以进一步组织分桶。也就是说，桶是为了更细粒度的数据划分。分桶的原理就是对分桶列取hash值，再用该hash值模桶数（hive中使用pmod函数）。分桶不会改变原有表/分区目录的组织方式，只是更改了数据在文件中的分布。

分桶表的优点：避免了全表扫描、优化了join查询、加快了分桶的抽样查询

E.g.

            Create table buck_emp(id int ,name string ,age int) clustered by (id) sorted by (age desc) into 3 buckets row format delimited fields terminated by ‘\t’;

DML语句：

一：数据导入

1. 直接上传数据

临时表-------------没有表文件目录，无法上传

内部表

Hdfs dfs -put emp.txt /user/hive/warehouse/offcn.db/managed_emp01

Select * from managed_emp01;

外部表

Hdfs dfs -put emp.txt /user/hive/warehouse/offcn.db/external_emp01

Select * from external_emp01;

分区表

Hdfs dfs -put emp.txt /user/hive/warehouse/offcn.db/partition_emp/age=20/

Select * from partiiton_emp;

Hdfs dfs -put emp.txt /user/hive/warehouser/offcn.db/partition_emp/month=6/day=2

0 select * from partition_emp;

分桶表（上传完毕后，发现HDFS中目录下上传的emp.txt并没有分成多个文件，也就是并没有按照指定字段进行分桶）

Hdfs dfs -put emp.txt /user/hive/warehouse/offcn.db/buck_emp

Select * from buck_emp;

1. 向表中加载数据

语法：load data [local] inpath ‘path/target.log’ [overwirte] into table tab [partition (partcol1=val1,...)];

Local:表示从本地服务器加载数据到hive表中，否则从HDFS上加载数据到hive表中

临时表

Load data local inpath ‘/home/offcn/tmp/emp.txt’ into table temporary_emp;

内部表

Load data local inpath ‘/home/offcn/tmp/emp.txt’ into table managed_emp01;(直接在表后追加)

Load data local inpath ‘/home/offcn/tmp/emp.txt’ overwrite into table

managed_emp01;（直接覆写）

外部表

Load data local inpath ‘/home/offcn/tmp/emp.txt’ into table external_emp01;

分区表

Load data local inpath ‘/home/offcn/tmp/emp.txt’ into table partition_emp partition(age=18);

Load data local inpath ‘/home/offcn/tmp/emp.txt’ into table partition_emp partition(month=8,day=12);

分桶表

Load data local inpath ‘/home/offcn/tmp/emp.txt’ into table buck_emp;

1. 通过查询语句向表中插入语句(insert)

3.1 insert values

Insert into table tab [partition ( partcol1[=val1],partcol2[=val2]...)] values (value[,value2 ...])

临时表

Insert into table temporary_emp(id ,name ) values (1,’hadoop’);

内部表

Insert into table managed_emp01 (id,name) values(2,’spark’);

外部表

Insert into table external_emp01 (id,name) values(3,’kafka’);

分区表

Insert into table partition_emp01 partition(age=20) values(2,’lyd’);

Insert into table partition_emp01 partition(month=1,day=11) values(2,’jxy’);

分桶表

Insert into table buck_emp01 (id,name) values(1,’hadoop’);

3.2 insert+select 将某张表查出的内容插入到目标表中

语法：

Insert overwrite table tablename [partition(partcol1=val1,partcol2=val2...)]

Select ***  from ***;(直接覆写)

Insert into table tablename [partition(partcol1=val1,partcol2=val2...)]

Select ***  from ***;(追加)

临时表

Insert overwrite table temporary_emp select * from emp02;

内部表

Insert into table managed_emp01 select * from emp02;

外部表

Insert into table external_emp02 select * from emp02;

分区表

Insert into table partition_emp partition(age=45) select * from emp02;

Insert into table partition_emp partition(month=2,day=29) select * from emp02;

注意：插入的表和查询的表的结构一一对应，才能插进去。

分桶表：

在hive 3.x之前的版本，需要开启以下参数：

Set hive.enforce.bucketing=true;  开启分桶操作

Set hive.enforce.sorting=true;    开启排序操作

Insert into table buck_emp select * from partition_emp01;

3.3 多重插入（使用一条sql语句，将一张表的查询结果分别插入不同的表中）

将partition_emp表中的部分数据分别替换，插入到managed_emp01,

external_emp02中

From partition_emp

Insert overwrite table managed_emp01

Select id,name

Insert into table external_emp01

Select id,name;

3.4动态分区

动态分区可以基于查询参数的位置去推断分区的名称，从而建立分区。

如果没有动态分区，在往hive分区表中插入数据时，如果需要创建的分区很多，比如以表中某个字段进行分区存储，则需要复制粘贴修改很多sql去执行。

示例：

设置非严格模式

Set hive.exec.dynamic.partition.mode=nonstrict;

创建动态分区表

Create table dynamic_emp(id int ) partitioned by (name string) row format delimited fields terminated by ‘\t’;

动态插入数据：

Insert overwrite table dynamic_emp partition(name)

Select id,name from managed_emp01;

3.5 CTE表达式

公用表表达式，是在单个语句的执行范围内定义的临时结果集，只在查询期间有效。也可以自引用，多次引用。

示例：

With t1 as (select name from emp01 where emp01.id=2)

Select * from t1;

from风格：

With t1 as (select name from emp01 where emp01.id=2)

From t1 select *;

串联：

With t1 as (select name from emp01 where emp01.id=2)

    t2 as (select name from t1)

Select * from (select name from t2) haha //这里必须起别名

Union案例：union去重，union all不去重

With t1 as (select * from emp01 where emp01.id=2)

T2 as (select * from emp01 where emp01.id=3)

Select * from t1 union all select * from t2;

插入数据：

Create table emp03 like emp01;

With t1 as (select * from emp01 where emp01.id = 2 )

From t1

Insert overwrite table emp03

Select *;

创建表：

Create table emp05 as

With t1 as (select * from emp01 where emp01.id=2)

Select * from t1;

1. 创建表时通过location指定加载数据路径

Create table if not exists emp04(

Id int ,name string)

Row format delimited fields terminated by ‘\t’

Location ‘/test’;

二：数据导出

1. insert导出

1.1将查询结果导出到本地

Insert overwrite local directory ‘/home/offcn/tmp/export/emp’

Select * from managed_emp01;

1.2 将查询的结果格式化导出到本地(将分隔符~A转化成Tab键进行导出)

Insert overwrite local directory ‘/home/offcn/tmp/export/emp2’

Row format delimited fields terminated by ‘\t’ 

Select * from managed_emp01;

1.3 将查询的结果导出到HDFS上（没有local）

Insert overwrite directory ‘/test/export/emp’

Select * from managed_emp01;

1. Hadoop命令导出到本地(下载到本地)

Dfs -get /user/hive/warehouse/offcn.db/emp02/emp.txt

/home/offcn/tmp/export/emp2/emp.txt;

1. export导出到HDFS上

Export table offcn.emp02 to ‘/tmp/export/emp’;

1. Import数据到指定的hive表中

Create table if not exists emp5(

Id int ,name string

)row format delimited fields terminated by ‘\t’;

Import table emp5 from ‘/test’;(注意：需要先将数据export导出，再进行导入)

DQL数据查询语言

一：基本查询(select ... from.....)

Select * from emp;

1. Hive shell命令导出

Hive -e ‘select * from offcn.emp02;’ >/home/offcn/tmp/export/emp.txt;

二：条件查询

1. Between、in、is null

Select * from emp where sal=2000;

Select * from emp where sal between 200 and 2000;

Select * from emp where comn is null;

Select * from emp where sal in (1000,2000)

1. like和Rlike

选择条件可以包含字符或数字：%表示零个或多个字符、_代表一个字符

Rlike子句是hive中这个功能的一个扩展，其可以通过java的正则表达式这个更加强大的语言指定匹配条件。

Select * from emp where ename like ‘S%’;

Select * from emp where ename like ‘_S%’;

Select * from emp where ename Rlike ‘[I]’

1. 逻辑运算符(and\or\not)

Select * from emp where sal>1000 and deptno=30;

Select * from emp where sal>1000 or deptno=30;

Select * from emp where deptno not in (19,21);

1. Limit 语句（限制返回的行数，只能跟一个参数）

Select * from emp limit 5;

三：分组查询

1. Group by 语句（select后的字段只能是分组字段或者聚合函数）

通常回合聚合函数一起使用(min\max\avg\sum\count)

Select deptno,avg(sal) avg_sal from emp group by deptno;计算每个部门的平均薪水

Select t.deptno,t.job,max(t.sal) from emp t group by t.deptno,t.job;计算每个部门每个岗位的最高薪水

1. Having语句

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

Select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal>2000;

四：连接查询

1. 内连接 （两种方式）

Select e.*,d.*

From emp e inner join dept d

On e.deptno=d.deptno;

Select e.*,d.*

From emp e left join dept d

On e.deptno=d.deptno

Intersect

Select e.*,d.*

From emp e right join dept d

On e.deptno=d.deptno;

1. 左右独有(使用差集实现)

Select e.*,d.*

From emp e full outer join dept d

On e.deptno=d.deptno

Where e.deptno is null or d.deptno is null;

Select e.*,d.*

From emp e full outer join dept d

On e.deptno=d.deptno

Except/minus

Select e.*,d.*

From emp e inner outer join dept d

On e.deptno=d.deptno;

1. 交叉连接（隐式和显式两种方式）

隐式：

Select e.*,d.*

From emp e,dept d;

显式：

Select e.*,d.*

From emp e cross join dept d;

1. 左半开连接 left semi join

当记录对于右边表满足on语句中的判定条件，只能返回左边表的记录，左半开连接是内连接的优化，当左边表的一条数据，在右边表中存在时，hive就停止扫描(去重)。

注意：左半开连接的select和where关键字后面只能出现左表的字段，不能出现右表的字段。

Select * from dept d left semi join emp e

On d.deptno=e.deptno;

错误示例：

Select d.*,e.*

From dept d left semi join emp e

On d.deptno=e.deptno;

1. 自连接

准备数据：

Create table area(

Id string not null,

Area_code string,

Area_name string,

Level string,

Parent_code string,

Target string)

Row format delimited fields terminated by ‘\t’;

Load data local inpath ‘/home/offcn/tmp/area.csv’ into table area;

需求：统计山西省下所有的市区

首先查询山西省的code：

Select area_code from area where area_name=’山西’；

之后查询parent_code为山西的所有地区信息：

Select * from area where parent_code=(Select area_code from area where area_name=’山西’);

或者使用自连接进行查询:

Select a.*,b.area_name

From area a join area b

On a.parent_code=b.area_code

Where b.area_name=’山西’；

1. 多表连接

Create table if not exists location (

Loc_code int,

Loc_name string)

Row format delimited fields terminated by ‘\t’;

Load data local inpath ‘/home/offcn/tmp/location.txt’ into table location;

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_code;

Hive会对每一个join连接对象启动一个MR程序。所以当有三个或更多的join连接的时候，如果每个on子句都使用相同的连接键的话，那么只会产生一个MR程序。

五：hive join 的注意事项

1. 允许复杂的连接表达式，支持非等值连接

等值连接：

Select a.* from a join b on a.id=b.id;

非等值连接：

Select a.* from a join b on a.id=b.id and a.name=b.name;

Select a.* from a join b on a.id<>b.id;

1. 同一查询中可以连接两个以上的表

Select a.*,b.*,c.* from a join b on a.id=b.id join c on b.id=c.id;

1. Join 时的最后一个表会通过reducer流式传输，并在其中缓冲之前的其他表，因此，将大表放置在最后有助于减少reducer阶段缓存数据所需要的内存

1. 在join的时候，可以使用STREAMTABLE指定要流式传输的表，如果省略，则hive将流式传输最右边的表。

Select /*+STREAMTABLE(a)*/ a.val,b.val,c.val from a join b on a.id=b.id join c on c.id=b.id;

1. join在where条件之前运行

1. 如果除了一个要连接的表之外的所有表都很小，则可以将其作为仅map作业执行，不走reduce join了。

Select /*+MAPJOIN(b)*/ a.key ,a.val from a join b on a.key=b.key;

六：排序

1. 全局排序——order by

Asc 升序

Desc  降序

由于是全局排序，所以只会启动一个reducer

Select emp.* from emp order by emp.sal desc;

1. 每个MR程序内部排序（sort by）

Sort by；对于大规模的数据集order by的效率较低，很多情况下，并不需要全局排序，此时可以使用sort by进行内部排序。

Sort by 为每个reducer产生一个排序文件，每个reducer内部进行排序，对全局结果集来说不是排序。

e.g

设置reduce个数

Set mapreduce.job.reduces=3;

查看设置的reduces个数

Set mapred.reduces.tasks;

根据部门编号降序查看员工信息

Select * from emp sort by deptno desc;

1. 分区排序——distribute by

在某些情况下，需要控制某个特定行应该到哪个reducer，通常是为了后续的聚合操作，类似MR中的partition分区，结合sort by使用。

E.g.   

Insert overwrite local directory ‘/home/offcn/tmp/distribute_result’

Select * from emp distribute by deptno sort by empno desc;

Distribute by 的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区（hash partitioner）

1. Cluster by

当distrubute by和sort by 字段相同时，可以使用cluster by

Cluster by 除了具有distribute by的功能外还兼具sort by的功能，但是排序只能时升序排序

七：hive的函数

1. 系统内置函数

关系运算、逻辑运算（and\or\not）

数学运算（+、—、*、/、%、&、|、^、~）

数值运算

Round（取整函数）、floor（向下取整函数）、ceil（向上取整函数）、rand（取随机数函数）、exp（自然指数函数）、pow（幂运算函数）、sqrt（开平方函数）、bin（二进制函数）、hex（十六进制函数）、unhex（反转十六进制函数）、conv（进制转换函数）、pmod（正取余函数）

日期函数

UNIX时间戳转日期函数: from_unixtime

获取当前UNIX时间戳函数: unix_timestamp

日期转UNIX时间戳函数: unix_timestamp

指定格式日期转UNIX时间戳函数: unix_timestamp

日期时间转日期函数: to_date

日期转年函数: year

日期转月函数: month

日期转天函数: day

日期转小时函数: hour

日期转分钟函数: minute

日期转秒函数: second

日期转周函数: weekofyear

日期比较函数: datediff

日期增加函数: date_add

日期减少函数: date_sub

字符串函数

字符串长度函数：length

字符串反转函数：reverse

字符串连接函数：concat

带分隔符字符串连接函数：concat_ws

字符串截取函数：substr,substring

字符串转大写函数：upper,ucase

字符串转小写函数：lower,lcase

去空格函数：trim

左边去空格函数：ltrim

右边去空格函数：rtrim

正则表达式替换函数：regexp_replace

正则表达式解析函数：regexp_extract

URL解析函数：parse_url

json解析函数：get_json_object

空格字符串函数：space

重复字符串函数：repeat

首字符ascii函数：ascii

左补足函数：lpad

右补足函数：rpad

分割字符串函数: split

集合查找函数: find_in_set

E.g.

Get_json_object(string json_string,string path)

select  get_json_object('

{"store":

{"fruit":[

{"weight":8,"type":"apple"},

{"weight":9,"type":"pear"}

],

"bicycle":{"price":19.95,"color":"red"}

},

"email":"amy@only_for_json_udf_test.net",

"owner":"amy"

}'

,'$.store.fruit.type[0]');

1. 常用的内置函数

2.1 空字段赋值

NVL( value，default_value)，如果value的值为null，则nvl函数返回default_value，否则返回value的值。

如果员工的comn为null，则用-1代替：

Select comn,nvl(comn,-1) from emp;

2.2 条件函数

2.2.1 if函数

if(boolean testCondition, T valueTrue, T valueFalseOrNull)

当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull

Select if(1=2,100,200) from dual;  \\ 200

2.2.2 非空查找函数coalesce

coalesce(T v1, T v2, …)

返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

Select coalesce(null,100,200) from dual; \\ 100

2.2.3 条件判断函数 case

case a when b then c [when d then e] …… [else f] end

如果 a 等于 b ，那么返回 c ；如果 a 等于 d ，那么返回 e ；

否则返回 f

Select case 100

When 20 then ‘tom’

When 100 then ‘jerry’

Else ‘tim’

End;  \\  jerry

2.2.4 行转列（多行转单列）

concat(string a/col, string b/col…)

返回输入字符串连接后的结果，支持任意个输入字符串.

Select concat(---,>)  //  --->

concat_ws(separator, str1, str2,...)

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

Select concat_ws(_,~,~)  \\ ~_~

collect_set(col)

函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总， 产生array类型字段.

需求：统计每个部门薪资相同的员工

预计结果如下：

------------+--------------+

|  t1.base   |     name     |

+------------+--------------+

| NULL       | SCOTT        |

| 10,1300.0  | MILLER       |

| 10,2450.0  | CLARK        |

| 20,1100.0  | ADAMS        |

| 20,2975.0  | JONES        |

| 30,1500.0  | TURNER       |

| 30,2850.0  | BLAKE        |

| 20,800.0   | SMITH        |

| 30,1250.0  | WARD|MARTIN  |

| 30,1600.0  | ALLEN        |

| 10,5000.0  | KING         |

| 20,3000.0  | FORD         |

| 30,950.0   | JAMES        |

原表样子：

Deptname     emp_name    sal

大数据         Tom   1000

思路：1. 我们可以将数据进行归一，将姓名单独取出，再将部门名和   薪资放在一起（base）。

                        2. 之后，由于我们需要将每个部门薪资相同的员工姓名取到，            所以需要根据base进行分组即可。

select t1.base,concat_ws(‘|’,collect_set(t1.base)) name from (

Select ename,concat(deptname,’,’,sal) base from emp) t1

Group by t1.base;

2.2.5 列传行

explode(爆炸函数)

explode函数可以将一个array或者map类型的字段展开，其中 explode(array)使得结果中将array列表里的每个元素生成一行； explode(map)使得结果中将map里的每一对元素作为一行，key为一列， value为一列。

一般情况下，直接使用explode即可，也可以根据需要结合lateral view

Create table t1 (

name string ,

children array<string>,

address Map<string,string>

)row format delimited fields terminated by ‘|’

Collection items terminated by ‘,’

Map keys terminated by ‘:’;

Vim mapArray.txt

Zhangsan|child1,child2,child3...|k1:v1,k2:v2....

Lisi|child3,child4,child5....|k3:v3,k4:v4.....

Select explode(children) as myChild from t1;

myChild

Child1

Child2

.....

myMapKey	myMapValue
K1	V1
K2	V2
.....	......

Select explode(address) as (myMapKey,myMapValue) from t1;

select t1.*,childrenView.*,addressView.* from t1

lateral view explode(children) childrenView as children  //有join的意思

lateral view explode(address) addressView;

myChild	myMapKey	myMapValue
Child1	K1	V1
Child2	K2	V2
.....	.....	......

需求：窗口聚合函数的使用

统计每个ip，每个时间段数据的条数和明细

Select ip,createtime,url,pvs,count(pvs) over (partition by ip,createtime) from t2;

统计每个ip，每个时间段的总pv数以及明细

Select ip,createtime,url,pvs,sum(pvs) over (partition by ip,createtime) from t2;

统计每个ip，每个时间段平均pv次数以及明细

Select ip,create,url,pvs,avg(pvs) over (partition by ip,createtime) from t2;

窗口控制：窗口控制函数的使用

Select * ,

Sum(pvs) over (partition by ip ) as c_1,

Sum(pvs) over (order by createtime) as c_2,

Sum(pvs) over (partition by ip order by createtime desc) as c_3,

Sum(pvs) over (partition by ip order by createtime desc rows between unbounded preceding and current row) as c_4,

Sum(pvs) over (partition by ip order by createtime desc rows between 3 preceding and current row) as c_5,  

Sum(pvs) over (partition by ip order by createtime desc rows between 3 preceding and 1 following) as c_6,  

Sum(pvs) over (partition by ip order by createtime desc rows between current now and unbounded following) as c_7

From t2;

窗口分析函数的使用

2.2.6 窗口函数

语法：窗口函数 over (partition by 列名, 列名……order by 列名 rows between 开始位置 and 结束位置)

Over()函数：over()函数中包括三个函数：包括分区partition by 列名、排序order by 列名、指定窗口范围rows between 开始位置 and 结束位置。它决定了聚合函数的聚合范围，默认对整个窗口中的数据进行聚合，聚合函数对每一条数据调用一次。

使用Partition by子句对数据进行分区，可以用paritition by对区内的进行聚合， 可以跟多个字段，（partition by .. order by）可替换为（distribute by .. sort by ..）。

order by是排序的意思，是在该窗口中根据指定字段进行的排序，只能跟一个字段。

rows between开始位置 and 结束位置，窗口大小限定：

1. 1. 1. 1. following：往后
         2. current row：当前行
         3. unbounded：起点(一般结合preceding，following使用)
         4. unbounded preceding：表示该窗口最前面的行(起点)
         5. unbounded following：表示该窗口最后面的行(终点)

创建表：

Create table t2 (

ip string,

createtime string,

url string ,

pvs int

)row format delimited fields terminated by ‘\t’;

First_value开窗函数

统计每个ip和本ip第一次进入网站的pv差以及明细数据

With aaa as (

Select * ,first_val(pvs) haha over (partition by ip order by createtime)

From t2)

Select *,aaa.haha from aaa;

Last_value 开窗函数

统计每个ip,每个时间段和本ip，本时间最后一次进入网站的pv差以及明细数据

With a as (select * ,last_value(pvs) over (partition by ip order by createtime) ha

from t2 )

Select *,a.pvs-a.ha from a;

Lag() \ lead()开窗函数

Lag\lead函数可以在一次查询中取出同一个字段的前N行的数据（lag）和后N行的数据（lead）作为独立的列。

Lag\lead(field,num,defaultValue)

Field:需要查找的字段

Num:往前或往后查找的num行数据

DefaultValue：没有符合条件的默认值

示例：

需求：查询顾客上次购买的时间，以及明细

Select name,orderdate,cost,

lag(orderdate,1,’1900-01-01’) over (partition by name order by orderdate) as last_date

From business;

窗口排序函数

rank()   占坑，排名相同的为同一个排序号

Dense_rank()   不占坑，排名相同的为同一个排序号

Row_number()   不考虑排名相同的情况

Ntile(num)   将分组数据按照顺序均匀切为n片

Select *,

Rank() over (partition by ip order by pvs) rank,

Dense_rank() over(partition by ip order by pvs) dense_rank,

Row_number() over(partition by ip order by pvs) row_number,

From t2;

Ntile(n) 函数：

1. 不指定排序将数据分成3份

Select * , ntile(3) over () as rn from emp;

 从上图可以看出，由于数据时13条，不能被均分为3份，所以在第一份中多了一份数据，这就是ntile的一个规则，多出来的数据均匀的分到前几份中。

1. 指定顺序将数据分为3份

Select *,ntile(3) over(order by id asc) as rn

From emp;

1. 指定partition by 将数据分为3份

注意：partition by 是指定义窗口的意思，我们定义了窗口之后，会在每个窗口内部进行拆分。

Select *,ntile(3) over (partition by dept) as rn from emp;

ntile函数的使用场景:

求全局半分比，例如按照工资从高到低的顺序取出前20%的人员信息

select *from (Select *,ntile(5) over (order by sal desc) as rn from emp

)tmp where tmp.rn=1;

求子窗口的百分比，求每个部门的前20%工资的人员信息

select *from (Select *,ntile(5) over (partition by deptno order by sal desc) as rn from emp

)tmp where tmp.rn=1;

Cume_dist开窗函数（求占比）

如果是升序，则统计：小于等于当前值的行数/总行数

如果是降序，则统计：大于等于当前值的行数/总行数

示例：统计小于等于当前工资的人数占比的人员信息

Select name,deptno,sal,

Cume_dist() over (order by sal) as cume_dist

From data;

         Precent_rank开窗函数、计算百分比排名

百分比排名是指给出数据在整个数据集中的相对位置的百分比。例如一个数据的百分比排名是0.8，表示该数据在整个数据集中的位置处于80%的位置上。

示例：计算每个学生在score中的百分比排名

Select id,score,precent_rank() over (order by score desc) as rank

From students;

        窗口分组函数

1. Grouping

当我们根据不同维度进行分组后聚合时，我们之前的思想是使用union all对结果进行聚合。

示例1：使用一条sql分别求出每个ip，每个时间内数据的数量

Select ip,null as createtime,count(*) as num,

1 as GROUPING_ID from t2

Group by ip

Union all

Select null as ip ,createtime ,count(*) as num2,

2 as GROUPING_ID from t2

Group by createtime;

在不适用窗口分组函数的时候，会很麻烦，接下来我们使用窗口分组函数：

Select ip,createtime,count(1) as num ,GROUPING__ID

From t2 group by ip,createtime

GROUPING SETS(ip,createtime)

ORDER BY GROUPING__ID;

示例2：使用一条SQL分别求出，每个ip，每个时间，相同ip，时间段内数据的条数

Select

Ip,createtime,count(1) as num

GROUPING__ID

From t2

Group by

Ip,createtime

GROUPING SETS(ip,createtime,(ip,createtime))

ORDER BY

GROUPING_ID;

1. Cube 根据group by 的维度的所有组合进行聚合

需求：使用一条sql分别求出每个ip、每个时间、以及相同时间和ip内的数据条数

Select

Ip,createtime,count(*) as num ,

GROUPING__ID

From t2

Group by

Ip,createtime

WITH CUBE

ORDER BY

GROUPING__ID;

1. Roll up

Roll up 为CUBE的子集，以聚合字段的最左侧的维度为主，从该维度进行层级聚合。Rollup(a,b,c)则其组合为（a,b,c）（a,b）（a,c）（）

Select

Ip,createtime,count(*) as num ,

GROUPING__ID

From t2

Group by

Ip,createtime

WITH ROLLUP

ORDER BY

GROUPING__ID;

Hive的压缩和存储

压缩:

Hive的压缩不是必须开启的，在计算密集时，不进行压缩。存储密集时，需要开启压缩。

优点：

1. 节约磁盘存储空间，降低单节点的磁盘IO
2. 由于压缩后的数据占用的带宽更少，因此可以加快数据在Hadoop集群流动的速度，减少网络传输带宽。

缺点：

1. 需要花费额外的时间/CPU做压缩和解压缩计算

Hadoop压缩：

Haodop对文件压缩均实现org.apache.hadoop.io.compress.CompressionCodec接口，所有的实现类都在org.apache.hadoop.io.compress包下。

压缩算法的对比：

压缩格式	工具	算法	文件扩展名	是否可切分 （切片）	对应的编码/解码器
DEFAULT （default）	无	DEFAULT	.deflate	否	org.apache.hadoop.io.compress.DefaultCodec
Gzip	gzip	DEFAULT	.gz	否	org.apache.hadoop.io.compress.GzipCodec
bzip2	bzip2	bzip2	.bz2	是	org.apache.hadoop.io.compress.BZip2Codec
LZO	lzop	LZO	.lzo	是（索引）	com.hadoop.compression.lzo.LzopCodec
LZ4	无	LZ4	.lz4	否	org.apache.hadoop.io.compress.Lz4Codec
Snappy	无	Snappy	.snappy	否	org.apache.hadoop.io.compress.SnappyCodec

MR段压缩：

代码中：

//开启map输出压缩
conf.set("mapreduce.map.output.compress","true");
conf.set("mapreduce.map.output.compress.codec","org.apache.hadoop.io.compress.BZip2Codec");
//开启reduce输出压缩
conf.set("mapreduce.output.fileoutputformat.compress","true");
conf.set("mapreduce.output.fileoutputformat.compress.codec","org.apache.hadoop.io.compress.GzipCodec");
//压缩类型
conf.set("mapreduce.output.fileoutputformat.compress.type","BLOCK");

Map输出阶段压缩：

1）开启hive中间传输数据压缩功能

set hive.exec.compress.intermediate=true;

2）开启mapreduce中map输出压缩功能

set mapreduce.map.output.compress=true;

3）设置mapreduce中map输出数据的压缩方式

set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;

4）执行查询语句

select count(1) from t2;

Reduce输出阶段压缩

当Hive将输出写入到表中时，输出内容同样可以进行压缩。属性hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为true，来开启输出结果压缩功能。

案例实操：

1）开启hive最终输出数据压缩功能

set hive.exec.compress.output=true;

2）开启mapreduce最终输出数据压缩

set mapreduce.output.fileoutputformat.compress=true;

3）设置mapreduce最终数据输出压缩方式

set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

4）设置mapreduce最终数据输出压缩为块压缩

set mapreduce.output.fileoutputformat.compress.type=BLOCK;

5)测试一下输出结果是否是压缩文件

insert overwrite local directory '/home/offcn/tmp/hive-export/' select * from t2 distribute by ip sort by pvs desc;

Hive的拉链表

数据同步问题

Hive在实际工作中主要用于构建离线数据仓库，定期的从各种数据源中同步采集数据到Hive中，经过分层转换提供数据应用。例如，每天需要从MySQL中同步最新的订单信息、用户信息、店铺信息等到数据仓库中，进行订单分析、用户分析。

例如：MySQL中有一张用户表：tb_user，每个用户注册完成以后，就会在用户表中新增该用户的信息，记录该用户的id、手机号码、用户名、性别、地址等信息。

 每天都会有用户注册，产生新的用户信息，我们每天都需要将MySQL中的用户数据同步到Hive数据仓库中，在做用户分析时，需要对用户的信息做统计分析，例如统计新增用户的个数、总用户个数、用户性别分布、地区分布、运营商分布等指标。

在实现数据仓库数据同步的过程中，我们必须保证Hive中的数据与MySQL中的数据是一致的，这样才能确保我们最终分析出来的结果是准确的，没有问题的，但是在实现同步的过程中，这里会面临一个问题：如果MySQL中的数据发生了修改，Hive中如何存储被修改的数据？

假设user表中目前由10条数据，在第二天MySQL中增加了两名新的用户，同时原来的用户也有一名修改的情况。那么，新增的数据会直接加载到hive表中，而更新的数据如何存储在hive表中。

对此，有以下三种解决方案：

1. 将更新的数据直接覆盖原数据，这样会没由历史数据。
2. 每次数据改变，就根据日期构建一份全量的快照表，这样会导致大量无用数据的冗余。
3. 构建拉链表，通过时间标记发生变化的数据的每种状态的时间周期。

拉链表的设计

拉链表的设计是将更新的数据进行状态记录，没有发生更新的数据不进行状态存储，用于存储所有数据在不同时间上的所有状态，通过时间进行标记每个状态的生命周期，查询时，根据需求可以获取指定时间范围状态的数据，默认用9999-12-31等最大值来表示最新状态。

实现过程：

Step1：增量采集所有的新增数据（增加的数据和变化的数据）放入到一张增量表中。

Step2：创建一张临时表，用于将老的拉链表与增量表进行合并。

Step3：将临时表的数据覆写至拉链表中。

拉链表的实现示例

Step1：创建拉链表并加载数据

Create table zipper (

userId string,

Phone string,

Nick string,

Gender string,

Addr string,

startTime string,

endTime string

)row format delimited fields terminated by ‘\t’;

Load data local inpath ‘/home/offcn/tmp/zipper.txt’ into table zipper;

Step2：创建增量表

Create table zipper_update(

userId string,

Phone string,

Nick string,

Gender string,

Addr string,

startTime string,

EndTime string

)row format delimited fields terminated by ‘\t’;

加载更新数据

Load data local inpath ‘/home/offcn/tmp/update.txt’ into table zipper_update;

Step3: 创建临时表合并数据

Create temporary table tmp_zipper(

userId string,

Phone string,

Nick string,

Gender string,

Addr string,

startTime string,

EndTime string

)row format delimited fields terminated by ‘\t’;

合并拉链表与增量表

Insert overwrite table tmp_zipper

Select userId,phone,nick,gender,addr,startTime,endTime

From zipper_update

Union all

Select a.userId,a.phone,a.nick,a.gender,a.addr,a.startTime,

If (b.userId is null or a.endTime<’9999-12-31’,a.endTime,date_sub(b.startTime,1))

As endTime

From zipper a left join zipper_update b

On a.userId=b.userId;

Step4: 生成最新的拉链表

Insert overwrite table zipper

Select * from tmp_zipper;