大数据-Hive

Hive简介

Hive是基于 Hadoop 的一个【数据仓库工具】，可以将结构化和半结构化的数据文件映射为一张数据库表，并提供简单的 sql 查询功能 。因为比直接用MapReduce开发效率更高，Hive的主要作用就是用来做离线数据分析。

本质是：将HQL转化成MapReduce程序

特点

可扩展性
Hive可以自由的扩展集群的规模，一般情况下不需要重启服务

延伸性
Hive支持自定义函数，用户可以根据自己的需要来实现自己的函数

容错性
即使节点出现错误，SQL仍然可以完成执行

优点

1. 操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）。
2. 避免了去写MapReduce，减少开发人员的学习成本。
3. Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合。
4. Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高。
5. Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

缺点

1. Hive的HQL表达能力有限

   （1）迭代式算法无法表达

   （2）数据挖掘方面不擅长

   2．Hive的效率比较低

   （1）Hive自动生成的MapReduce作业，通常情况下不够智能化

   （2）Hive调优比较困难，粒度较粗

Hive架构

Client 客户端

Hive允许client连接的方式有三个CLI（hive shell）、JDBC/ODBC(java访问hive)、WEBUI（浏览器访问hive）。

Metastore 元数据

元数据包括表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等。

Driver 驱动器

• 解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如ANTLR；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
• 编译器（Physical Plan）：将AST编译生成逻辑执行计划。
• 优化器（Query Optimizer）：对逻辑执行计划进行优化。
• 执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark

数据处理

Hive的数据存储在HDFS中，计算由MapReduce完成。HDFS和MapReduce是源码级别上的整合，两者
结合最佳。解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计
划的生成。

Hive 启动

启动集群

• 启动Zookeeper
  【123】zkServer.sh start
• 启动Hdfs+Yarn
  [root@node01 ~]# start-all.sh

Hive的三种交互方式

1. 第一种

   • shell交互Hive，用命令hive启动一个hive的shell命令行，在命令行中输入sql或者命令来和Hive交互。

   • 服务端启动metastore服务：nohup hive --service metastore > /dev/null 2>&1 &
     进入命令:hive
     退出命令行：quit;
     

2. 第二种

   • Hive启动为一个服务器，对外提供服务，其他机器可以通过客户端通过协议连接到服务器，来完成访问操作，这是生产环境用法最多的

•    hadoop fs -chmod -R 777 /yjx
     服务端启动hiveserver2服务：
     nohup hive --service metastore > /dev/null 2>&1 &
     nohup hiveserver2 > /dev/null 2>&1 &
     需要稍等一下，启动服务需要时间：
     进入命令:1)先执行beeline，在执行! connect jdbc:hive2://node01:10000
     2)或者直接执行 beeline -u jdbc:hive2://node01:10000 -n root
     退出命令行：！exit
  

3. 第三种

   • 使用 –e 参数来直接执行hql的语句

     • bin/hive -e "show databases;"
       

   • 使用 –f 参数通过指定文本文件来执行hql的语句

     • vim hive.sql
       use myhive;
       select * from test
       保存退出
       hive -f hive.sql
       

   • 特点：执行完sql后，回到linux命令行。

• 在hive cli和beeline cli的区别

• Beeline CLI相比于Hive CLI更现代和全面。它提供了更好的兼容性、更多的特性和更灵活的配置，同时支持多种客户端语言和显示格式。因此，在Hive的新版本中，推荐使用Beeline CLI作为与Hive交互的命令行界面。

• metastore服务实际上就是一种thrift服务，通过它我们可以获取到hive元数据，并且通过thrift获
  取原数据的方式，屏蔽了数据库访问需要驱动，url，用户名，密码等等细节。
  HiveServer2（HS2）是一个服务端接口，使远程客户端可以执行对Hive的查询并返回结果。一般
  来讲，我们认为HiveServer2是用来提交查询的，也就是用来访问数据的。 而MetaStore才是用来
  访问元数据的。

• beeline cli优化了命令行界面

Hive的基本操作

创建数据库

• 创建一个数据库，数据库在HDFS上的默认存储路径是/hive/warehouse/*.db。

create database shop;

• 避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）

create database if not exists shop;

创建数据库和位置

create database if not exists school location '/school.db';

修改数据库

alter database school set dbproperties('createtime'='20201213');  日期

数据库详细信息

1)显示数据库（show）
	show databases;
2）可以通过like进行过滤
	show databases like 's*';
3) 查看详情（desc）
	desc database school;
4）切换数据库（use）
	use school;

删除数据库

1）最简写法
	drop database school;
2）如果删除的数据库不存在，最好使用if exists判断数据库是否存在。否则会报错
	drop database if exists school;
3)如果数据库不为空，使用cascade命令进行强制删除。
	drop database if exists school cascade;

Hive数据类型

基本数据类型

复杂数据类型

Hive表操作

创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
字段解释说明:
- CREATE TABLE
创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项
来忽略这个异常。
- EXTERNAL
关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
创建内部表时，会将数据移动到数据仓库指向的路径（默认位置）；
创建外部表时，仅记录数据所在的路径，不对数据的位置做任何改变。在
删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
- COMMENT：
为表和列添加注释。
- PARTITIONED BY
创建分区表
- CLUSTERED BY
创建分桶表
- SORTED BY
不常用
- ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH
SERDEPROPERTIES (property_name=property_value, property_name=property_value,
...)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。
如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。
在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过
SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。
- STORED AS指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储
格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。
如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
- LOCATION ：
指定表在HDFS上的存储位置。
- LIKE
允许用户复制现有的表结构，但是不复制数据。

• 根据数据创建表结构

• 案例1：简单用户信息

  • 1,admin,123456,男,18
    2,zhangsan,abc123,男,23
    3,lisi,654321,女,16
    

  • create external table t_user(
    id int,
    uname string,
    pwd string,
    gender string,
    age int
    )
    row format delimited fields terminated by ','
    lines terminated by '\n';
    
    
    --载入数据代码
    load data inpath '/yjx/user.txt' into table t_user;
    

• 案例2：复杂人员信息

  • songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long
    guan_beijing
    yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing
    

  • create table IF NOT EXISTS t_person(
    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';
    
    
    --载入数据代码
    load data inpath '/yjx/person.txt' into table t_person;
    

显示表

show tables;
show tables like 'u';
desc t_person;
desc formatted t_person;

重命名

内部表（同时修改文件目录）外部表（因为目录是共享的，所以不会修改目录名称）

• 基本用法

  alter table old_table_name rename to new_table_name;
  

• 把t_old改成t_new

alter table t_old rename to t_new;

修改列

查询表结构
	desc test_new;
添加列
	alter table test_new add columns (education string);
查询表结构
	desc test_new;
更新列
	alter table test_new change education educationnew string;
删除表
	drop table test_new;

Hive内外部表

默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/opt/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

外部表

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

外部表的关键词：EXTERNAL

Hive载入数据

• 基本语法

  • load data [local] inpath 'datapath' [overwrite] into table student
    [partition (partcol1=val1,…)];
    --load data
    加载数据
    --[local]
    本地，不加Local就是从HDFS,如果是HDFS，将会删除掉原来的数据
    --inpath
    数据的路径
    --'datapath'
    具体的路径，要参考本地还是HDFS
    --[overwrite]
    覆盖
    --into table
    加入到表
    --student
    表的名字
    --[partition (partcol1=val1,…)]
    分区
    

• 加载linux本地数据

  --切记必须和hiveserver2在同一个节点才可以上传否则
  --SemanticException Line 1:23 Invalid path ''/root/d3.txt'': No files matching
  path file
  load data local inpath '/root/user.txt' into table t_user;
  

• 加载HDFS数据

load data inpath '/yjx/user.txt' into table t_user;

• 加载并覆盖已有数据

load data inpath '/yjx/user.txt' overwrite into table t_user;

• 通过查询插入数据

Hive载入数据

• 基本语法

load data [local] inpath 'datapath' [overwrite] into table student
[partition (partcol1=val1,…)];
--load data
加载数据
--[local]
本地，不加Local就是从HDFS,如果是HDFS，将会删除掉原来的数据
--inpath
数据的路径
--'datapath'
具体的路径，要参考本地还是HDFS
--[overwrite]
覆盖
--into table
加入到表
--student
表的名字
--[partition (partcol1=val1,…)] 分区

• 加载linux本地数据

--切记必须和hiveserver2在同一个节点才可以上传否则
--SemanticException Line 1:23 Invalid path ''/root/d3.txt'': No files matching
path file
load data local inpath '/root/user.txt' into table t_u

• 加载HDFS数据

load data inpath '/yjx/user.txt' into table t_user;

• 加载并覆盖已有数据

load data inpath '/yjx/user.txt' overwrite into table t_user;

• 通过查询插入数据
  • 创建表

create table t_user1(
id int,
uname string
)
row format delimited fields terminated by ','
lines terminated by '\n';
create table t_user2(
id int,
pwd string
)
row format delimited fields terminated by ','
lines terminated by '\n';

​ 插入查询结果

--将查询结果插入一张表
insert overwrite table t_user1 select id,uname from t_user;
insert overwrite table t_user2 select id,pwd from t_user;

--将查询结果一次性存放到多张表
from t_user
insert overwrite table t_user1 select id,uname
insert overwrite table t_user2 select id,pwd;

Hive导出数据

将表中的数据备份

• 将查询结果存放到本地

//创建存放数据的目录
mkdir -p /root/yjx
//导出查询结果的数据(导出到Node01上)
insert overwrite local directory '/root/person_data' select * from t_person;

• 按照指定的方式将数据输出到本地

//创建存放数据的目录
mkdir -p /root/yjx
//导出查询结果的数据
insert overwrite local directory '/root/yjx/person'
ROW FORMAT DELIMITED fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n'
select * from t_person;

• 将查询结果输出到HDFS

//创建存放数据的目录
hdfs dfs -mkdir -p /yjx/copy
//导出查询结果的数据
insert overwrite directory '/yjx/copy/user'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
select * from t_user;

直接使用HDFS命令保存表对应的文件夹

//创建存放数据的目录
hdfs dfs -mkdir -p /yjx/person
//使用HDFS命令拷贝文件到其他目录
hdfs dfs -cp /hive/warehouse/t_person/* /yjx/person

将表结构和数据同时备份

• 将数据导出到HDFS

//创建存放数据的目录
hdfs dfs -mkdir -p /yjx/copy
//导出查询结果的数据
export table t_person to '/yjx/copy';

• 删除表结构

drop table t_person;

• 恢复表结构和数据

import from '/yjx/copy';

Hive分区表

在大数据中，最常见的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这

样每次操作一个个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我

们可以把大的数据，按照每天或者每小时切分成一个个小的文件，这样去操作小的文件就会容易很

多了。

静态分区(SP)

• 单分区

--载入数据
load data inpath '/yjx/student.txt' into table t_student partition(grade=1);

• 多分区

--载入数据
load data inpath '/yjx/teacher11.txt' into table t_teacher
partition(grade=1,class=1);

• 分区表查询

select * from t_student where grade = 1 ;

• 查看分区

show partitions t_student;

• 添加分区

alter table t_student add partition (day='99990102');
alter table t_student add partition (day='99990103') location '99990103';

• 删除分区

alter table salgrade2 drop partition (day='99990102');

动态分区(DP)

• 静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。

• 详细来说，静态分区的列是在编译时期通过用户传递来决定的；动态分区只有在SQL执行时才能决

定。

• 开启动态分区首先要在hive会话中设置如下的参数

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

• 其余的参数详细配置如下

设置为true表示开启动态分区的功能（默认为false）
--hive.exec.dynamic.partition=true;
设置为nonstrict，表示允许所有分区都是动态的（默认为strict）
-- hive.exec.dynamic.partition.mode=nonstrict;
每个mapper或reducer可以创建的最大动态分区个数(默认为100)
比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认
值100，则会报错
--hive.exec.max.dynamic.partition.pernode=100;
一个动态分区创建可以创建的最大动态分区个数（默认值1000）
--hive.exec.max.dynamic.partitions=1000;
全局可以创建的最大文件个数（默认值100000）
--hive.exec.max.created.files=100000;
当有空分区产生时，是否抛出异常（默认false）
-- hive.error.on.empty.partition=false;

如果静态分区的话，我们插入数据必须指定分区的值。
如果想要插入多个班级的数据，我要写很多SQL并且执行24次很麻烦。
而且静态分区有可能会产生数据错误问题

如果使用动态分区，动态分区会根据select的结果自动判断数据应该load到哪儿分区去。

insert overwrite table t_student_d partition (grade,clazz) select * from t_student_e ;

分桶表

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

业务场景

数据分桶的适用场景：

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

• 尤其是需要确定合适大小的分区划分方式

• 不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬情况

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

• 分桶就是将数据按照字段进行划分，可以将数据按照字段划分到多个文件当中去。

数据分桶原理

Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

• bucket num = hash_function(bucketing_column) mod num_buckets

• 列的值做哈希取余 决定数据应该存储到哪个桶

分桶优势

• 方便抽样

  使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在

  数据集的一小部分数据上试运行查询，会带来很多方便

• 提高join查询效率

  获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结

  构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接

  （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果

  对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少

  JOIN的数据量。

数据分桶实战

使用data.txt；需要在将其上传到yjx/ciziten

-- 开启分桶功能
set hive.enforce.bucketing=true;
-- 设置Reduce个数
-- 我们需要确保reduce 的数量与表中的bucket 数量一致
-- bucket个数会决定在该表或者该表的分区对应的hdfs目录下生成对应个数的文件,而mapreduce的
个数是根据文件块的个数据确定的map个数。
set mapreduce.job.reduce=3;

-- 创建表
CREATE TABLE t_citizen_bucket(
idcard int,
pname string,
province int
)clustered by(idcard) sorted by (pname desc) into 16 buckets
row format delimited fields terminated by ','
lines terminated by '\n';

create EXTERNAL table t_citizen(
idcard int,
pname string,
province int
)row format delimited fields terminated by ','
lines terminated by '\n'
location '/yjx/citizen';
-- 数据导入
for (int i = 1000; i < 10000; i++) {
System.out.println(i + "," + "admin" + (new Random().nextInt(89999) +
10000) + "," + i % 34);
}
-- 将外部表的数据导入到分桶表
insert overwrite table t_citizen_bucket select * from t_citizen ;

数据抽样算法

对于非常大的数据集，用户不需要全部查询的结果，只需要一个代表性的查询结果时，可以通过对表进行分桶抽样。

数据块抽样:

-- 该方式允许Hive随机抽取N行数据，数据总量的百分比（n百分比）或N字节的数据。
SELECT * FROM <Table_Name> TABLESAMPLE(N PERCENT|ByteLengthLiteral|N ROWS) s;
1） tablesample(n percent) 根据hive表数据的大小按比例抽取数据，并保存到新的hive表中。如：
抽取原hive表中10%的数据
注意：测试过程中发现，select语句不能带where条件且不支持子查询，可通过新建中间表或使用随机
抽样解决
create table xxx_new as select * from xxx tablesample(10 percent)
2）tablesample(n M) 指定抽样数据的大小，单位为M。
3）tablesample(n rows) 指定抽样数据的行数，其中n代表每个map任务均取n行数据
-- hive另外一种按照抽样百分比进行抽样的方式，该种方式基于行数，按照输入路径下的数据块的百分比进行抽样。
-- 这种抽样的最小单元是一个hdfs数据块，如果表的数据大小小于普通块大小128M,将返回所有行。
-- 基于百分比的抽样方式提供了一个变量，用于控制基于数据块的调优种子信息：

桶表抽样：

-- tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)
-- 分桶语句中的分母表示的是数据将会被散列的桶的个数，分子表示将会选择的桶的个数。
-- x表示从哪个bucket开始抽取。
-- 例如，table总bucket数为32，tablesample(bucket 3 out of 16)
-- 表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据
-- y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。
-- 例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取
(64/128=)1/2个bucket的数据
select * from t_citizen_bucket tablesample(bucket 1 out of 16 on idcard);
select * from t_citizen_bucket tablesample(bucket 2 out of 4 on idcard);

随机抽样：

-- 使用RAND()函数和LIMIT关键字来获取样例数据，使用DISTRIBUTE和SORT关键字来保证数据是随机分散到mapper和reducer的。
-- ORDER BY RAND()语句可以获得同样的效果，但是性能没这么高。
SELECT * FROM <Table_Name> DISTRIBUTE BY RAND() SORT BY RAND() LIMIT <N rows to
sample>;
select * from t_citizen_bucket DISTRIBUTE BY RAND() SORT BY RAND() LIMIT 10;

Hive查询语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] rows]

Hive独特的排序

全局排序（order by）

Order By：全局排序，只有一个Reducer，当输入规模比较大时，需要较长的计算时间

使用 order by子句排序 :ASC（ascend）升序（默认）| DESC（descend）降序

• order by放在select语句的结尾

  select * from t_student_d order by sno;
  

• 按照字段别名排序

  select grade,count(sno) cs from t_student_d group by grade order by cs;
  

• 多个列排序

  select grade,count(sno) cs from t_student_d group by grade order by cs,grade;
  

局部排序 （sort by）

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

如果用sort by进行排序，并且设置mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出有序，不保证全局有序。

设置reduce个数		set mapreduce.job.reduce=3;
查看reduce个数		set mapreduce.job.reduce;
排序				  select * from t_student_d sort by sname;

将查询结果导入到文件中 
insert overwrite local directory '/root/student' select * from t_student_dsort by clazz asc, grade desc;

分区排序 （distribute by）

distribute by（字段）根据指定的字段将数据分到不同的reducer，类似MR中的分区，且分发算法是hash散列，结合sort by 使用。

对于distrbute by 进行测试，一定要多分配reduce进行处理，否则无法看到distribute by的效果。

注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

设置reduce个数		set mapreduce.job.reduce=7;

排序         		 
insert overwrite local directory '/data/student' select * from t_student_d
distribute by sname;

分区并排序 （cluster by）

cluster by（字段）除了具有Distribute by的功能外，还会对该字段进行排序
cluster by = distribute by + sort by 只能默认升序，不能使用倒序

select * from t_student_d sort cluster by sname;
select * from t_student_d distribute by sname sort by sname;

Hive内置函数

内置函数

1.查看系统自带函数
	show functions;
2.显示自带的函数的用法
	desc function upper;
3.详细显示自带的函数的用法
	desc function extended upper;

内置函数分类

​ 1. 关系操作符：包括 = 、 <> 、 <= 、>=等
2. 算数操作符：包括 + 、 - 、 *、／等
3. 逻辑操作符：包括AND 、 && 、 OR 、 || 等
4. 复杂类型构造函数：包括map、struct、create_union等
5. 复杂类型操作符：包括A[n]、Map[key]、S.x
6. 数学操作符：包括ln(double a)、sqrt(double a)等
7. 集合操作符：包括size(Array)、sort_array(Array)等
8. 类型转换函数： binary(string|binary)、cast(expr as )
日期函数：包括from_unixtime(bigint unixtime[, string format])、unix_timestamp()等
条件函数：包括if(boolean testCondition, T valueTrue, T valueFalseOrNull)等
11. 字符串函数：包括acat(string|binary A, string|binary B…)等
12. 其他：xpath、get_json_objectscii(string str)、con

Hive窗口函数

窗口函数是什么？

1. 窗口函数指定了函数工作的数据窗口大小(当前行的上下多少行)，这个数据窗口大小可能会随着行的变化而变化。
2. 开窗函数一般就是说的是over（）函数，其窗口是由一个 OVER 子句 定义的多行记录
3. 开窗函数一般分为两类,聚合开窗函数和排序开窗函数。

窗口函数和聚合函数区别？

1. 窗口函数对于每个组返回多行，组内每一行对应返回一行值。
2. 聚合函数对于每个组只返回一行。

聚合开窗函数

sum(求和)min(最小)max(最大)avg(平均值)count(计数)

• rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量。
  • OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
  • CURRENT ROW：当前行
  • n PRECEDING：往前n行数据
  • n FOLLOWING：往后n行数据
  • UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED
  • FOLLOWING表示到后面的终点

sum(col) over() :  分组对col累计求和，over() 中的语法如下
count(col) over() : 分组对col累计，over() 中的语法如下
min(col) over() : 分组对col求最小
max(col) over() : 分组求col的最大值
avg(col) over() : 分组求col列的平均值
first_value(col) over() : 某分区排序后的第一个col值
last_value(col) over() : 某分区排序后的最后一个col值
lag(col,n,DEFAULT) : 统计往前n行的col值，n可选，默认为1，DEFAULT当往上第n行为NULL时候，取默认值，如不指定，则为NULL
lead(col,n,DEFAULT) : 统计往后n行的col值，n可选，默认为1，DEFAULT当往下第n行为NULL时候，取默认值，如不指定，则为NULL
ntile(n) : 用于将分组数据按照顺序切分成n片，返回当前切片值。注意：n必须为int类型。

排名函数：
row_number() over() : 排名函数，不会重复，适合于生成主键或者不并列排名
rank() over() :  排名函数，有并列名次，名次不连续。如:1,1,3
dense_rank() over() : 排名函数，有并列名次，名次连续。如：1，1，2

排序开窗函数

row_number() over() : 排名函数，不会重复，适合于生成主键或者不并列排名

rank() over() : 排名函数，有并列名次，名次不连续。如:1,1,3

dense_rank() over() : 排名函数，有并列名次，名次连续。如：1，1，2

percent_rank() 计算给定行的百分比排名。可以用来计算超过了百分之多少的人

自定义函数

UDF(User-Defined-Function) 单行函数，一进一出 size/sqrt

UDAF(User- Defined Aggregation Funcation) 聚集函数，多进一出。 count/max/min/sum/avg

UDTF(User-Defined Table-Generating Functions) 一进多出 lateral view explode()

Hive参数

设置参数的三种方式

hive当中的参数、变量，都是以命名空间开头

1.修改配置文件 ${HIVE_HOME}/conf/hive-site.xml 永久有效！

<property>
       <name>hive.default.database</name>
       <value>mydb</value>
     </property>

2、启动hive cli时，通过–hiveconf key=value的方式进行设置 当前会话有效，退出无效！例：hive --hiveconf hive.cli.print.header=true

hive> SET hive.exec.dynamic.partition.mode=nonstrict;

3、进入cli之后，通过使用set命令设置：set hive.cli.print.header=true; 当前会话有效，退出无效！

需要注意的是:以上三种方式设置的参数优先级依次递增，即命令行 > 会话 > 配置文件。

hive (default)> SET hive.exec.dynamic.partition.mode=nonstrict;

常用的Hive设置

0-hive.fetch.task.conversion=more;将hive拉取的模式设置为more模式
1-hive.exec.mode.local.auto 决定 Hive 是否应该自动地根据输入文件大小，在本地运行（在GateWay运行） ;
2-hive.auto.convert.join ：是否根据输入小表的大小，自动将 Reduce 端的 Common Join 转化为
	Map Join，从而加快大表关联小表的 Join 速度。 默认：false。
3-mapred.reduce.tasks ：所提交 Job 的 reduer 的个数，使用 Hadoop Client 的配置。
	默认是-1，表示Job执行的个数交由Hive来分配；
	mapred.map.tasks:设置提交Job的map端个数；
4-hive.map.aggr=true 开启map端聚合；
	hive.groupby.skewindata=true ：决定 group by 操作是否支持倾斜的数据。
	原理是，在Group by中，对一些比较小的分区进行合并，默认是false；
5-hive.merge.mapredfiles ：是否开启合并 Map/Reduce 小文件，
	对于 Hadoop 0.20 以前的版本，起一首新的 Map/Reduce Job
	对于 0.20 以后的版本，则是起使用 CombineInputFormat 的MapOnly Job。 默认是：false；
6-hive.mapred.mode ：Map/Redure 模式，如果设置为 strict，将不允许笛卡尔积。 默认是：'nonstrict'；
7-hive.exec.parallel ：是否开启 map/reduce job的并发提交。
	默认Map/Reduce job是顺序执行的，默认并发数量是8，可以配置。默认是：false；
8-hive.exec.dynamic.partition =true：是否打开动态分区。 需要打开，默认：false；
	set hive.exec.dynamic.partition.mode=nonstirct

数据倾斜

数据分布不均，造成大量数据集中到一点，造成数据热点

从本质来说，导致数据倾斜有两种原因：

• 是任务读取大文件，最常见的就是读取压缩的不可分割的大文件。
• 是任务需要处理大量相同键的数据。

原因

• 业务无关的数据引发的数据倾斜

  • 场景

    实际业务中有些大量的NUL值或者一些无意义的数据参与到计算作业中，这些数据可能来自业务为上报或因数据规范将某类数据进行归一化变成空值或空字符等形式，这些与业务无关的数据导致在进行分组聚合或者在执行表连接时发生数据倾斜

  • 解决

    • 在计算过程中排除含有这类“异常”据即可

• 多维聚合计算数据膨胀引起的数据倾斜

  • 场景

  • 不同数据类型进行分组聚合，如果T表的数据量很大，并且Map端的聚合不能很好地起到数据压缩的情况下，会导致Map端产出的数
    据急速膨胀，这种情况容易导致作业内存溢出的异常。如果T表含有数据倾斜键，会加剧 Shuffle过程的数据倾斜。

  • 解决

    • 拆解上面的SQL语句，将 rollup拆解成如下多个普通类型分组聚合的组合。

• 无法削减中间结果的数据量引发的数据倾斜

  • 场景

    如student tb txt表中，s_age有数据倾斜，但如果数据量大到一定的数量，会导致处理倾斜的 Reduce任务产生内存溢出的异常

  • 解决

    • 启用该配置参数会将作业拆解成两个作业，第一个作业会尽可
      能将Map的数据平均分配到Reduce阶段，并在这个阶段实现预聚合，以减少第二个作业处理的数据量；第二个作业在第一个作业处理的数据基础上进行结果的预聚合。

• 两个Hive数据表连接时引发的数据倾斜

  • 场景

    两表进行普通的 repartition join时，如果表连接的键存在倾斜，那么在 Shufe阶段必然会引起数据倾斜。

  • 解决

    • Hve的通常做法还是启用两个作业，第一个作业处理没有便斜的数据，第二个作业将倾斜的数据存到分布式缓存中，分发到各个M即任务所在节点在Mip价段完成join操作，即 Mapjoin，这避免了 Shuffle，从而避免了数据倾斜。

Hive优化

Hive优化的核心思想：把Hive SQL 当做Mapreduce程序去优化

Fetch

我们之前是不是说过select * from 表；这样的sql语句是不会产生MR任务的，这涉及到一个fetch（抓取）的概念。

• hive-default.xml.template hive.fetch.task.conversion–>
  • 默认more 如果 none那么每次执行select * from 都是执行MapReduce

本地模式

hive.exec.mode.local.auto=true

并行执行

set hive.exec.parallel=true; //打开任务并行执行
set hive.exec.parallel.thread.number=16; //同一个sql允许最大并行度

严格模式

开启严格模式需要修改hive.mapred.mode值为strict，开启严格模式可以禁止3种类型的查询。

<property>
<name>hive.mapred.mode</name>
<value>strict</value>
</property>

• 防止用户执行低效率的SQL查询
• 对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行
• 对于使用了order by语句的查询，要求必须使用limit语句
• 限制笛卡尔积的查询。

JVM重用

JVM重用可以使得JVM实例在同一个job中重新使用N次。

N的值可以在Hadoop的mapred-site.xml文件中进行配置。

通常在10-20之间，具体多少需要根据具体业务场景测试得出。

很难避免小文件的场景或task特别多的场景，这类场景大多数执行时间都很短

<property>
<name>mapreduce.job.jvm.numtasks</name>
<value>10</value>
</property>

表的优化（小表与大表）

Hive默认第一个(左面的)表是小表，然后将其存放到内存中，然后去与第二张表进行比较
现在优化后小表前后无所谓

表的优化（大表与大表）

针对于空值，可以将空值随机设置一个不影响结果的值
将来reduce的时候可以分区到不同的reduce，减少压力

mapside聚合

默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。

并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，
最后在Reduce端得出最终结果。

• 开启Map端聚合参数设置
  是否在Map端进行聚合，默认为True
  hive.map.aggr = true
  在Map端进行聚合操作的条目数目
  hive.groupby.mapaggr.checkinterval = 100000
  有数据倾斜的时候进行负载均衡（默认是false）
  hive.groupby.skewindata = true
  当选项设定为 true，生成的查询计划会有两个MR Job。
  分两次进行mapredue,第一次随机分获取中间结果,第二次正常分，获取最终结果

Count(Distinct)

防止所有的数据都分到一个Reduce上面
首先使用Group By对数据进行分组，然后在统计

防止笛卡尔积

• 行列过滤(列裁剪)
  • 当表关联的时候，优先使用子查询对表的数据进行过滤,这样前面表关联数据就是少的，减少关联的次数

Hive文件存储格式

Hive支持行式存储格式和列式存储格式

行式存储

行式存储格式：相关数据保存在一起，数据读取的时候以行为单位读取的 非常便于根据ID进行查询

优点：这种存储格式简单、方便写入数据 。
缺点：不支持压缩、并且不支持列裁剪，数据分析开销较大
文件格式：TextFile、SequenceFile

TextFile

1、存储方式：行存储。默认格式，如果建表时不指定默认为此格式。

2、每一行都是一条记录，每行都以换行符"\n"结尾。默认是不压缩，但可以采用多种压缩方式，但是部分压缩算法压缩数据后生成的文件是不支持split。

3、可结合Gzip、Bzip2等压缩方式一起使用（系统会自动检查，查询时会自动解压）,推荐选用可切分的压缩算法。

4、该类型的格式可以识别在hdfs上的普通文件格式（如txt、csv），因此该模式常用语仓库数据接入和导出层；

5、无法区分数据类型，各个字段都被认为是文本，但需要制定列分隔符和行分隔符。

Sequence File

1、SequenceFile是一种二进制文件，以<key,value>的形式序列化到文件中。存储方式：行存储；

2、支持三种压缩选择：NONE、RECORD、BLOCK。RECORD压缩率低，一般建议使用BLOCK压缩。

3、优势是文件和Hadoop API的MapFile是相互兼容的

4、缺点是由于该种模式是在textfile基础上加了些其他信息，故该类格式的大小要大于textfile，现阶段基本上用。

列式存储格式：

将不同的列存放在不同的块中。只是针对数据的某些字段进行查询

优点：支持列裁剪、减少数据查询范围，数据支持压缩，节省空间。
缺点：写入数据相对困难，并且查询整行数据时，开销相对较大。
文件格式：ORC、PARQUET、RCFILE

ORC File

1、存储方式：数据按行分块，每块按照列存储

2、Hive提供的新格式，属于RCFile的升级版，性能有大幅度提升，而且数据可以压缩存储，压缩快，快速列存取。

3、ORC File会基于列创建索引，当查询的时候会很快，现阶段主要使用的文件格式。

Parquet File

1、存储方式：列式存储。

2、Parquet对于大型查询的类型是高效的。对于扫描特定表格中的特定列查询，Parquet特别有用。

RC File

1、存储方式：数据按行分块，每块按照列存储 。

A、首先，将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。

B、其次，块数据列式存储，有利于数据压缩和快速的列存取。

2、相对来说，RCFile对于提升任务执行性能提升不大，但是能节省一些存储空间。可以使用升级版的ORC格式。