Hive

KNOWU2024-11-132024-11-13

前言

数据仓库的基本介绍

数据仓库和数据库的区别:
- OLTP(联机事务处理): 数据库面向于事务处理存储业务数据数据库在设计的时候, 尽可能避免冗余出现捕获数据对数据进行CURD操作对交互性要求比较高
- OLAP(联机分析处理): 数据仓库面向于主题存储过去既定发生过数据 ,为了分析方便, 可以允许出现一定冗余情况数据分析更多做的都是查询操作 , 对交互性没有要求
- 注意: 数据仓库的出现, 绝不是要替代数据库的
什么样容器可以作为数据仓库呢? 只要能够存储数据, 并且可以对数据进行查询容器都可以
- 例如: mysql oracle SQL server Excel
数据仓库分层架构模式, 及其ETL的基本介绍:
- 数据仓库主要的目的:
- 对系统过去已经既定发生过的数据, 进行数据分析, 从而对未来提供决策支持
数据分析:
- 本质上来讲, 其实就是在数据查询操作, 对过去的数据进行查询, 已得到目的结论过程

如何进行数据分析工作: 常用的语言 SQL
- SQL是这个世界上最好的分析工具, 因为会SQL人肯定比会大数据多
什么是离线分析, 什么是实时分析:
- 离线分析: 对过去以及既定发生的过数据, 进行分析处理的工作, 对延迟性要求不强 批处理分析
- 实时分析: 数据刚刚产生, 就需要立即对数据进行分析处理操作, 而且对延迟性要求比较高(毫秒级) , 流式分析方案
数据仓库的最大特点:
- 数据仓库本身既不生产数据, 也不消耗数据, 数据来源于各个数据源
数据仓库的主要的特征:
- 面向于主题: 主题指的分析的问题, 分析的是什么, 主题就是什么
- 集成性: 数据是来源于各个数据源的
- 稳定性(非易失性): 存储的都是过去既定发生过数据 ,数据一般不允许进行修改操作
- 时变性: 随着时间的推移, 过去定义分析的流程并不能满足后续分析, 需要对分析方案进行改变

hive的基本介绍

hive就是一款数据仓库的工具, 基于hadoop, 说明如果要想启动hive, 必须先保证hadoop是启动良好的状态, hive最初是属于Facebook, 后期贡献给apache , 称为apche的顶级的开源项目(http://hive.apache.org)
hive本质上就是对HDFS中结构化文本数据, 进行映射成为一张表, 提供了,可以通过SQL的方式查询数据
hive其实就是一款MapReduce的翻译工具, 主要作用, 将编写SQL, 翻译为MapReduce

那么为什么要使用hive?

原因如下: 
   1) 如果直接编写MapReduce, 成本过高, 时间周期比较长
   2) MapReduce入门门槛比较高
如果使用hive: 
	1) 成本降低, 甚至于可以找一个普通开发人员即可完成 效率更高效
	2) 门槛更低, 基本上大多数程序员 都可以编写SQL

hive的架构;

client: 客户端  编写SQL命令
metastore:  存储元数据 , hive的元数据主要指的, 有那些库, 有那些表, 表中有那些字段, 字段是什么类型, 数据存储在hdfs什么位置,分割符号是什么.....  而这些数据, 称为hive的元数据
SQL的解析器: 将SQL转换抽象语法树, 目的是为了通过解析器, 检查SQL语法是否正确
编译器: 将抽象语法树转换为逻辑执行计划, 主要目的, 从语法树分析出, 要操作那个表, 操作什么数据, 规划MR如何编写, 先干什么然后干什么问题
优化器: 对整个逻辑计划进行优化处理工作 hive默认提供多种优化措施
执行器:  将逻辑计划, 转换为物理计划(MapReduce), 然后将MR提交到yarn平台进行执行

hive的元数据主要指的

1
2
3

    有那些库, 有那些表, 表中有那些字段, 字段是什么类型, 数据存储在hdfs什么位置,分割符号是什么.....  而这些数据, 称为hive的元数据 , 这些数据默认都是存储derby的数据库, 但是一般推荐将其存储在外部数据库中, 比如mysql

既然数据存储到mysql中, 必然hive就得提供一个查询mysql的线程, 此线程指的就是有一个metastore服务项

说明: hive具有sql数据库的外表，但应用场景完全不同， hive 只适合用来做批量数据统计分析( 离线分析 )

hive的安装

1. 上传资料中的 hive安装包到 node3的 /export/software下

1. 解压 hive的安装包到 /export/server下

1 2	cd /export/software tar -zxf apache-hive-2.1.0-bin.tar.gz -C /export/server

1. 修改 hive名称为 hive-2.1.0

1 2	cd /export/server mv apache-hive-2.1.0-bin hive-2.1.0

1. 修改hive的配置文件: hive-env.sh

cd /export/server/hive-2.1.0/conf
cp hive-env.sh.template hive-env.sh

vim hive.env.sh

修改以下内容:  注意前面的#号不要忘记删除
HADOOP_HOME=/export/server/hadoop-2.7.5 
export HIVE_CONF_DIR=/export/server/hive-2.1.0/conf

1. 修改 hive的 hive-site.xml

cd /export/server/hive-2.1.0/conf
vim hive-site.xml

# 添加一下内容:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>root</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>123456</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://node1:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>com.mysql.jdbc.Driver</value>
  </property>
  <property>
      <name>hive.metastore.schema.verification</name>
      <value>false</value>
  </property>
  <property>
    <name>datanucleus.schema.autoCreateAll</name>
    <value>true</value>
 </property>
 <property>
	<name>hive.server2.thrift.bind.host</name>
	<value>node3</value>
   </property>
</configuration>

1. 将资料中 mysql的驱动包, 导入到hive的lib目录下

1
2
3

cd /export/server/hive-2.1.0/lib/

将资料中的 mysql-connector-java-5.1.38.jar 上传到 此目录下:

1. 将 hive的 jdbc目录下的hive**-jdbc-2.1.0-standalone.**jar 复制到 hive的lib目录下

1	cp /export/server/hive-2.1.0/jdbc/hive-jdbc-2.1.0-standalone.jar /export/server/hive-2.1.0/lib/

1. 修改hadoop的core-site.xml

在node1: 
cd /export/server/hadoop-2.7.5/etc/hadoop
vim core.site.xml
添加以下内容:
<property> 
	<name>hadoop.proxyuser.root.hosts</name>
	<value>*</value> 
</property> 
<property> 
	<name>hadoop.proxyuser.root.groups</name> 
	<value>*</value> 
</property>

添加后保存退出, 然后同步到 noed2和 node3
cd /export/server/hadoop-2.7.5/etc/hadoop
scp -r core-site.xml  node2:$PWD
scp -r core-site.xml  node3:$PWD

1. 启动hadoop集群

1	node1节点: start-all.sh

1. 启动hive

node3: 

cd /export/server/hive-2.1.0/bin

nohup ./hive --service metastore &
nohup ./hive --service hiveserver2 &

通过jps 看到有两个RUNJAR 出现 , 1分钟测试多次

或者通过 ps -ef | grep hive

1. 初始化hive的元数据

cd /export/server/hive-2.1.0/bin
执行:
schematool -dbType mysql -initSchema
手动初始化元数据信息

1. 连接hive:

node3:
cd /export/server/hive-2.1.0/bin

./beeline

进入后输入
!connect jdbc:hive2://node3:10000

输入用户名 root  密码 任意  即可

hive的shell参数

如何向hive设置一个参数配置操作, 设置的方式主要有三种方案:

1. 全局设置 : 直接在hive-site.xml中进行参数配置操作
- 特点: 如果在hive-site.xml中进行配置信息, 此信息全局有效的
- 例如:
  1
  2
  3
  4
  <property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>123456</value>
  </property>

通过外部命令行设置: 在执行./hive的可以设置操作

特点: 只要连接的是这个服务端的客户端, 都具有这样的配置

例如

bin/hive -hiveconf hive.root.logger=INFO,console  
#或者:
nohup hive   -hiveconf hive.root.logger=INFO,console  --service hiveserver2 &  

#这样启动hiveserver2只有, 所有连接这个hiveserver2的服务的客户端 都具有这个参数配置

1. 通过会话设置 : 在客户端内部, 通过set的方式设置
- 特点: 仅在当前会话是生效的, 退出之后就恢复原状
- 例如:
  1
  2
  3
  0: jdbc:hive2://node3:10000> set mapreduce.job.reduces=3;
  
  # 仅在当前这个会话有效的, 退出重新进, 就恢复原状

作用范围:

默认配置文件 >基于配置文件设置 >外部命令行方式 > 会话设置

优先级:

会话设置 > 外部命令行方式 > 基于配置文件方式 > 默认配置文件

注意:

整个hive是有一个默认的配置项, 如果你设置, 都是由默认值的, 而默认项配置在什么位置: hive-default.xml

hive的基本操作

库

1. 创建数据库:
- 语法: create database 数据库名称 [location hdfs路径];
- 说明: 当创建一个数据库, 如果没有指定数据库放置的位置, 默认情况下, 会在hdfs的/user/hive/warehouse/建立一个目录
1. 查看数据库:
- 语法1: show databases; 查看所有的数据库
- 语法2: desc database 数据名称; 查看某一个数据库
1. 如何删除数据库:
- 格式: drop database 数据库名称;

表

创建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]

说明: 
	EXTERNAL(external): 如果在建表的添加此关键词, 说明此表为外部表, 如果不加就是内部表(管理表)
	PARTITIONED BY : 如果在建表时候添加了此关键词, 说明此表为分区表
	CLUSTERED BY ,SORTED BY, INTO num_buckets BUCKETS 组合使用:
		如果在建表的指定 CLUSTERED BY INTO num_buckets BUCKETS 表示当前这个表为 分桶表
		SORTED BY 指定按照那个字段进行排序操作
	ROW FORMAT : 指定数据的分割符号
	STORED AS : 指定表的数据的文件格式, 支持四种: textfile(普通文件) sequenceFile parquet ORC
	LOCATION : 指定表数据在什么位置
	
在整个hive共计有几种类型表?  四种表
	内部表  外部表  分区表 分桶表
注意: 有一些程序员会认为是两种表 , 我们一般不这么认为
	内部表
		内部的分区表
		内部分桶表
	外部表
		外部的分区表
		外部的分桶表

修改表的操作

修改表的名称

--基本语法：
alter  table  old_table_name  rename  to  new_table_name;

-- 把表score4修改成score5
alter table score4 rename to score5;

添加/修改列:

--添加列操作: 
alter table score5 add columns (mycol string, mysco string);

--修改列操作: 更名查找
alter table score5 change column mysco mysconew int;

清空表:

格式:
    truncate table 表名;

注意: 清空表, 只针对内部表, 不能对外部表进行操作, 因为外部表对数据没有修改权限

如何向表中加载数据

通过 insert into/overwrite + select 加载数据 : （☆常用）

1 2	--格式: insert into/overwrite [table] 表名 [partition(分区字段1=值1...)] + select查询语句

通过 load data方式加载数据: （☆常用）

1 2	--格式: load data [local] inpath '路径' into table 表名 [partition(分区字段1=值1....)]

使用export和import完成数据的导入导出的操作

create table teacher2 like teacher;
export table teacher to  '/export/teacher';
import table teacher2 from '/export/teacher';
--这些路径都是HDFS的路径, 一般针对内部表进行操作 (使用较少)

如何将表中数据导出

insert 方式导出数据 : （☆常用）

--操作一:  默认分割符号导出本地磁盘
     insert overwrite local directory '/root/hivedatas/exporthive' select * from score;
--操作二: 采用指定分割符号导出本地磁盘
     insert overwrite local directory '/root/hivedatas/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from student
     
     --注意: 以上两种操作, 如果不加 local 表示导出到HDFS中

通过shell的方式进行数据导出操作 : 大致了解

1	bin/hive -e "select * from myhive.score;" > /export/server/exporthive/score.txt

export导出到HDFS上: 大致了解

1 2	export table score to '/export/exporthive/score'; # 路径为HDFS的路径

常见数据类型:

1 2	原始类型: int double string varchar timestamp date 复杂类型: array map struct (理解为集合)

VARCHAR和STRING都用于表示文本数据，但它们有些不同。STRING是没有长度限制的文本类型，而VARCHAR允许你指定最大长度，如果超出指定长度会被截断。VARCHAR通常用于节省存储空间，适用于知道数据长度限制的场景。

复杂类型(array,map,struct)的操作

array类型操作

-- 建表语句:
create table hive_array(name string, work_locations array<string>)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ',';

思考一行数据应该如何定义呢? 
张三	北京,上海,深圳,天津

-- 在Linux的创建一个文件, 添加内容: 文件放置在 /root/hivedatas/  
vim hive_array.txt
-- 内容如下:
zhangsan        beijing,shanghai,tianjin,hangzhou
wangwu  changchun,chengdu,wuhan,beijin

-- 加载数据
load data local inpath '/root/hivedatas/hive_array.txt' into table hive_array;
--相关的操作:

map类型:

-- 建表语句: 
create table hive_map(
id int, name string, members map<string,string>, age int
)
row format delimited fields terminated by ','
COLLECTION ITEMS TERMINATED BY '#' 
MAP KEYS TERMINATED BY ':'; 

-- 思考一行数据应该如何定义呢? 
1,zhangsan,address:beijing#sex:nan,28

-- 在Linux的创建一个文件, 添加内容: 文件放置在 /root/hivedatas/  
vim hive_map.txt
-- 内容如下: 
1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26

--加载数据: 
load data local inpath '/root/hivedatas/hive_map.txt' into table hive_map;
-- 相关的操作:

struct类型:

-- 建表语句:
create table hive_struct(
ip string, 
info struct<name:string, address:string,age:int,birthday:string>,
sex string,
info2 map<string,string>
)
row format delimited fields terminated by ','
COLLECTION ITEMS TERMINATED BY ':'
MAP KEYS TERMINATED BY '-';


-- 思考一行数据应该如何定义呢? 
张三:北京:20:2020-10-15,男,father-'laozhang':monther-'laoli':sister-'xiaozhang'
李四:上海:18:2019-10-15,女,father-'laozhang':monther-'laoli':sister-'xiaozhang'

-- 在Linux的创建一个文件, 添加内容: 文件放置在 /root/hivedatas/  
vim hive_struct.txt


-- 加载数据:
load data local inpath '/export/server/hivedatas/hive_struct.txt' into table hive_struct;
--相关的操作

内部表

建表的初体验:

create database myhive;
use myhive;
create table stu(id int, name string);
insert into stu (id,name) values(1,'zhangsan');
select * from stu;

说明: 当创建一个表, 没有指定数据的位置, 此时表会自动在 库目录下创建一个子目录, 目录的名称与表名一致,

后续添加的数据都会在这个目录下

hive中默认数据的分割符号为: \001  (不可见符号)

hive默认的文件类型 就是 textfile

建表时, 指定数据的分割符号:

1 2	案例: create table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t' location '/benchmarks';

根据查询结果创建表

create table stu3 as select * from stu2;

说明:
   根据将一条SQL语句执行结果, 创建表, 此表与SQL的结果结构保持一致, 数据也会存储在新的表中

根据已经存在的表结构创建表

1
2
3

create table stu4 like stu2;

说明: 读取一个表的结构,创建一个与这个表相同结构的表  不含数据

查看表的基本信息
格式: desc formatted 表名;
删除表的操作:
1
格式: drop table 表名;

1	说明: 如果是管理表(内部表), 描述对表有绝对的控制权, 所以在删除表的时候, 对应hdfs中数据也会被一次性全部都删除的(元数据 + 数据)

查看所有的表
格式: show tables;

外部表

创建一张老师表:

1	create external table teacher (t_id string,t_name string) row format delimited fields terminated by '\t';

创建一张学生表:

1	create external table student (s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by '\t';

导入数据: load

--格式: 
	load data [local] inpath '路径' into table 表名  ;
	
	--说明:
		--local: 如果带上此关键词, 表示从本地(Linux)加载数据, 如果不带表示从HDFS上加载数据
		
	--注意:
		--load命令不会执行MR, 底层本质上执行了HDFS的命令
			--如果使用local: hdfs dfs -put 操作
			--如果没有local: hdfs dfs -mv 操作
--案例:
	load data local inpath '/root/hivedatas/techer.csv' into table teacher;
	load data local inpath '/root/hivedatas/student.csv' into table student;

删除表:

--删除学生表:
	drop table student;

--注意: 如果表是一个外部表, 在删除表的时候, 仅会将表的元数据给删除, 而不会删除表对应数据

在什么样场景中使用内部表, 什么样场景中使用外部表:

区别:
   内部表: hive会认为对表有绝对的控制权, 所以在删除表的时候, 会直接将表的元数据和数据本身全部删除
   外部表: hive会认为对此表没有控制权,  所以在删除表的时候, 仅会将自己的元数据删除, 并不会将数据给删除

如果对数据有控制权, 建议采用的内部表, 如果对数据没有控制权, 建议采用的外部表构建
如果数据需要进行共享, 也就说多个人都需要使用到这份数据, 此时需要构建的也是外部表

一般情况下, 数据是你自己产生的, 往往构建的是内部表, 数据如果使用别人的, 往往构建的都是外部表

内外表之间的转换处理工作

内部表转换为外部表:

--操作: 
   alter table student set tblproperties('EXTERNAL'='TRUE');
  
   --注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

外部表转换为内部表:

--操作: 
   alter table student set tblproperties('EXTERNAL'='FALSE');
   
   --注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

如何查看自己的表是内部表呢, 还是外部表呢?

--操作:
    desc  formatted 表名;
--在结果中查看:
	Table Type: 
		MANAGED_TABLE: 内部表
		EXTERNAL_TABLE: 外部表

分区表

分区表:

将一份数据按照某些条件, 将其划分为多个文件, 这些文件会被放置在以不同的文件夹下面, 当进行分析的时候, 可以只对某个文件夹下的数据进行分析, 从而没有必要对整体数据进行操作, 以此来提升效率.

例如:
比如说, 目前有一个文件, 里面存储了一年的订单销售信息, 目前向让分析一下, 去年4月份的订单销售总额

做法: 
1) 创建一个订单表, 然后将这一年的数据加载到这个表中
2) 编写SQL:
select  sum(money) from order  where date = '2020-04';
问题:
在上述的SQL, 需要对order表进行整体的扫描 从中获取到4月份的数据, 然后求和操作
当使用分区表:
1) 创建一个分区表, 分区字段按照月份操作
2) 将这个一年订单数据, 按照各个月份, 分别放置不同月份的文件夹下面
3) 将所有的这些数据加载到分区表, 通过月份指向不同的文件夹
4) 查询数据:
select  sum(money)  from order where 分区月份字段 = '2019-04'

由于筛选条件是分区字段, 底层在过滤数据的时候, 根据分区字段, 找到对应文件夹, 然后直接将文件夹中数据获取到, 求和即可, 此时就不需要在扫描整个表的数据,  减少数据的扫描量, 从而提升效率

目的: 分区表的出现主要的目的就是为了提升查询的效率, 在查询的时候, 帮助减少扫描的数据量

创建分区表的操作:

-- 创建只有一个分区的字段:
create table score(s_id string,c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by '\t';

-- 创建带有多个分区的字段:
create table score2 (s_id string,c_id string, s_score int) partitioned by (year string,month string,day string) row format delimited fields terminated by '\t';

如何加载数据

load data local inpath '/root/hivedatas/score.csv' into table score partition(month='2020-06');


load data local inpath '/root/hivedatas/score.csv' into table score2 partition(year='2020',month='06',day='06');


注意: 一般在离线分析中, 分区的粒度一般最小以天为单位, 或者以月 或者 季度 或者 年

查看分区:
1
show partitions score;

添加一个分区

1	alter table score add partition(month='202005') LOCATION '/user/hive/warehouse/score/2020/202005';

同时添加多个分区

1
2
3

alter table score add 
partition(month='202004') LOCATION '/user/hive/warehouse/score/2020/202004'
partition(month = '202003') LOCATION '/user/hive/warehouse/score/2020/202003';

删除分区

1	alter table score drop partition(month = '202006');

分区表案例:需求描述：现在有一个文件score.txt文件，存放在集群的这个目录下/scoredatas/month=202006，这个文件每天都会生成，存放到对应的日期文件夹下面去，文件别人也需要公用，不能移动。需求，创建hive对应的表，并将数据加载到表中，进行数据统计分析，且删除表之后，数据不能删除。

数据准备

hadoop fs -mkdir -p /scoredatas/month=202006
hadoop fs -mkdir -p /scoredatas/month=202007
hadoop fs -mkdir -p /scoredatas/month=202008

hadoop fs -put score.csv /scoredatas/month=202006/
hadoop fs -put score.csv /scoredatas/month=202007/
hadoop fs -put score.csv /scoredatas/month=202008/

创建表:

create external table score3 (
 s_id string,
 c_id string,
 s_score int
) 
partitioned by (month string)
row format delimited fields terminated by '\t'
location '/scoredatas';

添加分区, 让分区表识别到目录下的分区文件夹

alter table score3 add 
partition(month='202009') LOCATION '/user/hive/warehouse/score3/2020/202009'
partition(month= '202010') LOCATION '/user/hive/warehouse/score3/2020/202010'
partition(month= '202011') LOCATION '/user/hive/warehouse/score3/2020/202011;  
--此操作, 相当于在构建元数据

动态插入多个分区
如果你的数据源很大，或者你想要通过插入数据的方式来动态添加多个分区，可以使用 INSERT 语句：
1
2
3
4
5
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

INSERT INTO TABLE score3 PARTITION (month)
SELECT product_id, amount, year, month FROM staging_sales;
这样，Hive 会根据数据中 year 和 month 字段的值自动创建分区，并插入相应数据。

分桶表

分桶表其实指的就是MR中分区操作, 将一个文件拆分为多个文件过程, 这些多个文件是放置在一个文件夹下的.

有什么作用?

1) 随机采样
2) 提升执行效率
	例如:
	   查询数据过程中, 根据分桶字段查询, 可以按照分桶字段进行计算分区编号, 直接获取对应分区编号中数据(不是特别明显)
	   很多提升效率的地方 在 多表join中优化

首先开启hive的桶表的支持

1
2
3

set hive.enforce.bucketing=true;

默认情况下, hive不支持桶表, 如果需要使用, 必须先开启

1. 设置reduce的个数
1
set mapreduce.job.reduces=3;

创建桶表

1	create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t';

加载数据:

注意:  
	如果目标是桶表, 不能使用load data方式来加载数据, 因为load data方式不会执行MR, 那么也就不会进行分桶操作

正确做法:
   先创建一个和桶表一致的表, 只不过这个是一个普通表:
   create table course_tmp (c_id string,c_name string,t_id string)  row format delimited fields terminated by '\t';
   接着通过 load data方式 将数据加载到这个临时表: 
   load data local inpath '/root/hivedatas/course.csv' into table course_tmp;
   最后, 从临时表将数据查询出来, 然后加载到桶表中: 
      格式:
  		insert into/overwrite  table  表名 + select 语句
  	  操作:
  	     insert into table course select * from course_tmp;

hive函数

查询的语法

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


参数说明:
CLUSTER BY  : 指定按照某个字段分桶  
SORT BY : 按照那个字段来执行排序操作(局部排序)
DISTRIBUTE BY : 当分桶字段和排序字段是同一个字段, 并且执行升序, 可以使用此关键词缩写

分组操作:
- 注意事项:
  - 在执行的分组操作的时候, select后面跟着的字段, 一定是group by中出现过的, 否则就会报错
  - having是对分组之后的结果进行过滤操作, 而where是对from后面表进行过滤操作

多表查询:

满外连接(全外连接) : FULL JOIN

案例:
    SELECT * FROM teacher t FULL JOIN course c ON t.t_id = c.t_id ;

特点: 将左关联 和 右关联 合并在一起的结果集
     左表数据和右表进行匹配, 如果匹配不上, 使用Null替代, 同时右边表也和左表进行匹配, 如果匹配不上也是用Null来替代
     左表和右表的数据全部都是展示, 互相匹配不上的 都用NULL

注意: 在进行多表关联的时候, 有N个表关联至少需要 N-1个关联条件

排序操作:

ORDER BY全局排序:
- 注意: order by 全局排序, 这种排序操作, 只能有一个reduce, 不能出现多个reduce
sort by部分排序:

需要将数据导出到文件里面才能看到效果，每个文件内部是有序的
注意: 可以对每个reduce内部进行排序的操作, 前提reduce数量有多个, 如果只有一个, 和order by是一样的
distribute by分区排序:

如果你只写sort by的话,那么就是随机分区.如果你希望自己定义使用哪个字段来分区,那么你就需要使用Distribute By字段,这个Distribute By是指定你按哪个字段分区的. Distribute By在全排序是没有意义的,因为全排序是只有一个ReduceTask,那么不管你key是什么,全都是0号分区,所以说Distribute By只有结合部分排序才有意义的.所以一般情况下Distribute By 是结合sort by 来使用的
- 注意: 指定按照那个字段作为分区的字段, 执行分区
cluster by:
- 注意: 当发现需要进行分区的字段和排序的字段都是同一个, 并且执行的升序排序, 可以将其缩写为 cluster by 即可

例如:
	select * from score where month ='2020-05' distribute by s_id  order by s_score;
	说明: 按照 s_id作为分区字段, 按照 s_score执行排序操作
	select * from score where month ='2020-05' sort by s_score;
	说明: 根据s_score在每个reduce中进行排序操作


SQL:
   select * from score where month ='2020-05' distribute by s_score  sort by s_score;
    和
   select * from score where month ='2020-05' cluster by s_score;  等价关系
   
   说明:
      当发现需要进行分区的字段和排序的字段都是同一个, 并且执行的升序排序, 可以将其缩写为 cluster by 即可

hive的查询案例

数据准备工作

create database day09_hive;
use day09_hive;

create external table teacher (
t_id string,
t_name string
) row format delimited fields terminated by '\t';

create external table student (
s_id string,
s_name string,
s_birth string ,
s_sex string 
) row format delimited fields terminated by '\t';

create table score(
s_id string,
c_id string,
s_score int
) row format delimited fields terminated by '\t';

create table course (
c_id string,
c_name string,
t_id string
) row format delimited fields terminated by '\t';

load data local inpath '/root/hivedatas/techer.csv' into table teacher;
load data local inpath '/root/hivedatas/student.csv' into table student;
load data local inpath '/root/hivedatas/score.csv' into table score;
load data local inpath '/root/hivedatas/course.csv' into table course;

hive的内置函数

要求掌握程度: 知道在hive中有哪些函数, 名称可以不记得, 但是得知道有这样的函数

条件函数:

if函数:
- 格式: if( 条件, true return , false return )
- 注意: if函数支持无限嵌套

1 2	测试: select if(name ='lisi','lisi', if(name='zhangsan',100,200) ) from (select 'zhangsan' as name) tmp;

CASE函数

格式1: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
- 如果 a=b 返回c 如果 a等于d 返回e 否则就返回f
格式2: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
- 如果a的条件成立返回b 如果 c条件成立返回d 如果都不成立返回 e

案例一: 
select  case num when 50 then '等于50' when 100 then '等于100' else '不等于' end  as c1 from  (select  200 as num) tmp;


案例二 : 
   select  case when num=50 then '等于50' when num=100 then '等于100' when num=200 then '等于200' else '啥也不等于'  end as c1 from  (select  200 as num) tmp;
   
注意: 如果在判断过程中吗前面判断成功后, 即使后面依然有条件成立项 也不再进行判断了

转换函数: 对数据进行类型转换操作
- cast()函数:
  - 格式: cast(表达式 as 数据类型)
  1
  2
  3
  例如:
  select cast ('100' as int);
  select cast ('2020-10-10 12:10:20' as date);
Hive自带的json解析函数

get_json_object
- 语法：get_json_object(json_string, '$.key')
- 说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。这个函数每次只能返回一个数据项。
- 示例：
1
select get_json_object('{"name":"zhangsan","age":18}','$.name');
如果既要解析name字段，也解析age字段，则可以这样写：
1
2
3
select
get_json_object('{"name":"zhangsan","age":18}','$.name'),
get_json_object('{"name":"zhangsan","age":18}','$.age');
但是如果要解析的字段有很多，再这样写就太麻烦了，所以就有了 json_tuple 这个函数。

json_tuple
- 语法： json_tuple(json_string, k1, k2 ...)
- 说明：解析json的字符串json_string,可指定多个json数据中的key，返回对应的value。如果输入的json字符串无效，那么返回NULL。
- 示例：
1
2
3
4
5
select
b.name
,b.age
from tableName a lateral view
json_tuple('{"name":"zhangsan","age":18}','name','age') b as name,age;
注意：上面的json_tuple函数中没有$.

如果在使用json_tuple函数时加上$.就会解析失败,字段全是NULL，所以json_tuple函数不需要加$.了，否则会解析不到。

总结：json_tuple相当于get_json_object的优势就是一次可以解析多个json字段。

hive行转列的函数:

行转列: 将多行数据放置在一列中显示
涉及到相关的函数:
- concat(str1,str2,…)
- concat_ws(sep, str1,str2)
- collect_set(col) |collect_list(col)
  - set操作在收集过程中, 会去掉重复的数据, 而list不会
相关的案例演示

-- 第一步: 创建表
create table emp(
deptno int,
ename string
) row format delimited fields terminated by '\t';

-- 第二步:  准备数据:  在 /root/hivedata/
vim emp.txt
内容如下: 
20      SMITH
30      ALLEN
30      WARD
20      JONES
30      MARTIN
30      BLAKE
10      CLARK
20      SCOTT
10      KING
30      TURNER
20      ADAMS
30      JAMES
20      FORD
10      MILLER

--第三步: 加载数据
load data local inpath '/root/hivedatas/emp.txt' into table emp;

第四步: 需求: 最终结果要求如下
select deptno,  concat_ws("|", collect_set(ename))  from  emp group by deptno;

hive的列转行的操作:

描述: 将一列中数据, 变为多行数据
相关函数: explode (爆炸炸裂函数)
- 格式: explode(复杂类型)

相关的案例: 
-- 建表操作:
create table emp2(
deptno int,
names array<string>
)
row format delimited fields terminated by '\t'
collection items terminated by '|';

-- 准备数据:  在 /root/hivedatas/
vim emp2.txt

内容如下: (Array复杂类型)
10      CLARK|KING|MILLER
20      SMITH|JONES|SCOTT|ADAMS|FORD
30      ALLEN|WARD|MARTIN|BLAKE|TURNER|JAMES

-- 导入数据:
load data local inpath '/root/hivedatas/emp2.txt' into table emp2;

-- 进行列 转行的操作:
select  explode(names) from emp2;

1 2	-- 要求携带上部门: select deptno , explode(names) from emp2;

注意事项: 
    explode函数称为UDTF函数, 这种函数也被称为表生成函数, 这种函数有一个特点,一进多出函数
    
    这种UDTF函数在使用过程中有注意事项的
    	1) UDTF函数如果使用select语句之后, 只能有我这一个函数, 不允许出现其他字段
    	2) UDTF函数不允许嵌套在其他的函数中, 但是可以将其他其他函数嵌套在UDTF中

通过错误可以看出无法执行上述SQL, 需要要求展示这个内容, 如何解决呢? LATERAL VIEW 侧视图
侧视图使用格式: 
	LATERAL VIEW udtf(expression) tableAlias AS columnAlias  (类似于一张临时表)
	放置位置: 整个SQL语句最后面
	
最终SQL:
   select deptno,name  from emp2 LATERAL VIEW explode(names) tmp as name;

当数据中是普通类型的(String类型) , 如何使用explode函数进行处理: split函数

1	select deptno, concat_ws(',',collect_list(ename)) as c1 from emp group by deptno;

1 2	select explode(split(c1,",")) from (select deptno, concat_ws(',',collect_list(ename)) as c1 from emp group by deptno) tmp;

Reflect函数 :
- 说明:reflect函数可以支持在sql中调用java中的自带函数
- 格式:
  - reflect(‘java类权限类名’,’方法名’,参数….);
- 注意: 调用java中类中方法, 此方法应该是静态的工具类方法
1
2
3
4
--案例:
select reflect('java.lang.Math','min',10,5);
select reflect('java.lang.Math','max',10,5);
select reflect('java.lang.Math','abs',-10);

hive的窗口(分析)函数

窗口函数格式:
- 格式: 分析函数 over(partition by xxx order by xxx [asc|desc])
- 放置位置: select之后, from之前的位置

分析函数一: row_numver() rank() dense_rank() ntile()

分析函数的特点:

对于 row_number , rank ,dense_rank 和 ntile, 都是对数据进行打标记的操作, 每组的标记都是从1开始
row_number(): 在执行打标记的过程中, 不关心数据是否有重复的问题, 组中有多少行, 序号就会打到多少
rank(): 在执行打标记的过程中, 关心数据重复的问题, 对相同数据会打上相同标记, 但是会导致占用后续的序号
dense_rank(): 在执行打标记的过程中, 关心数据重复的问题, 对相同数据会打上相同标记,但是不会占用后续的序号
ntile(N): 在执行打标记的过程中, 按照指定N , 将数据平均划分为多份, 相同份会打上相同标记

row_numver() rank() dense_rank()应用场景: 求分组 topn的问题

分组topn:  指的是 对数据需要进行分组 求每个组内的前N个数据

例如: 
    求每个学生的平均值, 将将平均值最高的前3个获取出来? 这不是一个分组topn , 分组后求出每个学生平均值, 根据这个结果求前三位
    
    求每个电影分类下,每个分类中评论数最多的前三部电影?   分组topn问题

ntile(N) 应用场景: 求几分之几的问题

1 2	例如: 求咱们班级男生女生各身高的前2分之1的人是那些

执行相关的操作:

-- 第一步:  创建表
CREATE TABLE hive_t2 (
cookieid string,
createtime string,   --day 
pv INT
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
stored as textfile;

--第二步: 准备数据  /root/hivedatas
vim hive_t2.txt
-- 内容如下:
cookie1,2018-04-10,1
cookie1,2018-04-11,5
cookie1,2018-04-12,7
cookie1,2018-04-13,3
cookie1,2018-04-14,2
cookie1,2018-04-15,4
cookie1,2018-04-16,4
cookie2,2018-04-10,2
cookie2,2018-04-11,3
cookie2,2018-04-12,5
cookie2,2018-04-13,6
cookie2,2018-04-14,3
cookie2,2018-04-15,9
cookie2,2018-04-16,7

-- 第三步: 加载数据操作
load  data local inpath '/root/hivedatas/hive_t2.txt' into table hive_t2;

-- 加载到表结果:
+---------------------+-----------------------+---------------+--+
| hive_t2.cookieid  | hive_t2.createtime  | hive_t2.pv  |
+---------------------+-----------------------+---------------+--+
| cookie1             | 2018-04-10            | 1             |
| cookie1             | 2018-04-11            | 5             |
| cookie1             | 2018-04-12            | 7             |
| cookie1             | 2018-04-13            | 3             |
| cookie1             | 2018-04-14            | 2             |
| cookie1             | 2018-04-15            | 4             |
| cookie1             | 2018-04-16            | 4             |
| cookie2             | 2018-04-10            | 2             |
| cookie2             | 2018-04-11            | 3             |
| cookie2             | 2018-04-12            | 5             |
| cookie2             | 2018-04-13            | 6             |
| cookie2             | 2018-04-14            | 3             |
| cookie2             | 2018-04-15            | 9             |
| cookie2             | 2018-04-16            | 7             |
+---------------------+-----------------------+---------------+--+

-- 需求: 请求出每个cookieid中pv数量最多的前3个信息 ? 

select 
cookieid,
createtime,
pv,
row_number() over(partition by cookieid order by pv desc) as rank1,
rank() over(partition by cookieid order by pv desc) as rank2,
dense_rank() over(partition by cookieid order by pv desc) as rank3,
ntile(3) over(partition by cookieid order by pv desc) as rank4
from hive_t2;

+-----------+-------------+-----+--------+--------+--------+--------+--+
| cookieid  | createtime  | pv  | rank1  | rank2  | rank3  | rank4  |
+-----------+-------------+-----+--------+--------+--------+--------+--+
| cookie1   | 2018-04-12  | 7   | 1      | 1      | 1      | 1      |
| cookie1   | 2018-04-11  | 5   | 2      | 2      | 2      | 1      |
| cookie1   | 2018-04-16  | 4   | 3      | 3      | 3      | 1      |
| cookie1   | 2018-04-15  | 4   | 4      | 3      | 3      | 2      |
| cookie1   | 2018-04-13  | 3   | 5      | 5      | 4      | 2      |
| cookie1   | 2018-04-14  | 2   | 6      | 6      | 5      | 3      |
| cookie1   | 2018-04-10  | 1   | 7      | 7      | 6      | 3      |
| cookie2   | 2018-04-15  | 9   | 1      | 1      | 1      | 1      |
| cookie2   | 2018-04-16  | 7   | 2      | 2      | 2      | 1      |
| cookie2   | 2018-04-13  | 6   | 3      | 3      | 3      | 1      |
| cookie2   | 2018-04-12  | 5   | 4      | 4      | 4      | 2      |
| cookie2   | 2018-04-11  | 3   | 5      | 5      | 5      | 2      |
| cookie2   | 2018-04-14  | 3   | 6      | 5      | 5      | 3      |
| cookie2   | 2018-04-10  | 2   | 7      | 7      | 6      | 3      |
+-----------+-------------+-----+--------+--------+--------+--------+--+

最终SQL实现:
select
*
from
(select 
cookieid,
createtime,
pv,
row_number() over(partition by cookieid order by pv desc) as rank1
from hive_t2) tmp where rank1 <=3;

+---------------+-----------------+---------+------------+--+
| tmp.cookieid  | tmp.createtime  | tmp.pv  | tmp.rank1  |
+---------------+-----------------+---------+------------+--+
| cookie1       | 2018-04-12      | 7       | 1          |
| cookie1       | 2018-04-11      | 5       | 2          |
| cookie1       | 2018-04-16      | 4       | 3          |
| cookie2       | 2018-04-15      | 9       | 1          |
| cookie2       | 2018-04-16      | 7       | 2          |
| cookie2       | 2018-04-13      | 6       | 3          |
+---------------+-----------------+---------+------------+--+

分析函数二: 与聚合函数组合使用

常见聚合函数: max min sum avg count

应用场景: 级联求各种值的问题

1 2	实际场景中: 需要将当前行和之前或者之后行进行级联求何种值的时候, 应该要想到使用聚合函数和窗口函数进行组合使用

分析函数三: LAG,LEAD,FIRST_VALUE,LAST_VALUE

分析函数三(LAG,LEAD,FIRST_VALUE,LAST_VALUE)的应用场景:

让当前行和上面某一行或者下面某一行进行对比计算的时候, 可以使用此分析函数 

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

第一个参数为列名，
第二个参数为往上第n行（可选，默认为1），
第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

第一个参数为列名，
第二个参数为往下第n行（可选，默认为1），
第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

使用操作:

-- 建表:
CREATE TABLE hive_t4 (
cookieid string,
createtime string,  --页面访问时间
url STRING       --被访问页面
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
stored as textfile;

-- 准备数据  /root/hivedatas
vim hive_t4.txt

cookie1,2018-04-10 10:00:02,url2
cookie1,2018-04-10 10:00:00,url1
cookie1,2018-04-10 10:03:04,1url3
cookie1,2018-04-10 10:50:05,url6
cookie1,2018-04-10 11:00:00,url7
cookie1,2018-04-10 10:10:00,url4
cookie1,2018-04-10 10:50:01,url5
cookie2,2018-04-10 10:00:02,url22
cookie2,2018-04-10 10:00:00,url11
cookie2,2018-04-10 10:03:04,1url33
cookie2,2018-04-10 10:50:05,url66
cookie2,2018-04-10 11:00:00,url77
cookie2,2018-04-10 10:10:00,url44
cookie2,2018-04-10 10:50:01,url55

-- 加载数据
load data local inpath '/root/hivedatas/hive_t4.txt' into table hive_t4;

+---------------------+-----------------------+----------------+--+
| hive_t4.cookieid  | hive_t4.createtime  | hive_t4.url  |
+---------------------+-----------------------+----------------+--+
| cookie1             | 2018-04-10 10:00:02   | url2           |
| cookie1             | 2018-04-10 10:00:00   | url1           |
| cookie1             | 2018-04-10 10:03:04   | 1url3          |
| cookie1             | 2018-04-10 10:50:05   | url6           |
| cookie1             | 2018-04-10 11:00:00   | url7           |
| cookie1             | 2018-04-10 10:10:00   | url4           |
| cookie1             | 2018-04-10 10:50:01   | url5           |
| cookie2             | 2018-04-10 10:00:02   | url22          |
| cookie2             | 2018-04-10 10:00:00   | url11          |
| cookie2             | 2018-04-10 10:03:04   | 1url33         |
| cookie2             | 2018-04-10 10:50:05   | url66          |
| cookie2             | 2018-04-10 11:00:00   | url77          |
| cookie2             | 2018-04-10 10:10:00   | url44          |
| cookie2             | 2018-04-10 10:50:01   | url55          |
+---------------------+-----------------------+----------------+--+

--相关的操作:
SELECT cookieid,
  createtime,
  url,
  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
  LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
  LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time 
  FROM hive_t4;

+-----------+----------------------+---------+-----+----------------------+----------------------+--+
| cookieid  |      createtime      |   url   | rn  |     last_1_time      |     last_2_time      |
+-----------+----------------------+---------+-----+----------------------+----------------------+--+
| cookie1   | 2018-04-10 10:00:00  | url1    | 1   | 1970-01-01 00:00:00  | NULL                 |
| cookie1   | 2018-04-10 10:00:02  | url2    | 2   | 2018-04-10 10:00:00  | NULL                 |
| cookie1   | 2018-04-10 10:03:04  | 1url3   | 3   | 2018-04-10 10:00:02  | 2018-04-10 10:00:00  |
| cookie1   | 2018-04-10 10:10:00  | url4    | 4   | 2018-04-10 10:03:04  | 2018-04-10 10:00:02  |
| cookie1   | 2018-04-10 10:50:01  | url5    | 5   | 2018-04-10 10:10:00  | 2018-04-10 10:03:04  |
| cookie1   | 2018-04-10 10:50:05  | url6    | 6   | 2018-04-10 10:50:01  | 2018-04-10 10:10:00  |
| cookie1   | 2018-04-10 11:00:00  | url7    | 7   | 2018-04-10 10:50:05  | 2018-04-10 10:50:01  |
| cookie2   | 2018-04-10 10:00:00  | url11   | 1   | 1970-01-01 00:00:00  | NULL                 |
| cookie2   | 2018-04-10 10:00:02  | url22   | 2   | 2018-04-10 10:00:00  | NULL                 |
| cookie2   | 2018-04-10 10:03:04  | 1url33  | 3   | 2018-04-10 10:00:02  | 2018-04-10 10:00:00  |
| cookie2   | 2018-04-10 10:10:00  | url44   | 4   | 2018-04-10 10:03:04  | 2018-04-10 10:00:02  |
| cookie2   | 2018-04-10 10:50:01  | url55   | 5   | 2018-04-10 10:10:00  | 2018-04-10 10:03:04  |
| cookie2   | 2018-04-10 10:50:05  | url66   | 6   | 2018-04-10 10:50:01  | 2018-04-10 10:10:00  |
| cookie2   | 2018-04-10 11:00:00  | url77   | 7   | 2018-04-10 10:50:05  | 2018-04-10 10:50:01  |
+-----------+----------------------+---------+-----+----------------------+----------------------+--+

SELECT cookieid,
  createtime,
  url,
  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
  LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
  LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time 
  FROM hive_t4;

+-----------+----------------------+---------+-----+----------------------+----------------------+--+
| cookieid  |      createtime      |   url   | rn  |     last_1_time      |     last_2_time      |
+-----------+----------------------+---------+-----+----------------------+----------------------+--+
| cookie1   | 2018-04-10 10:00:00  | url1    | 1   | 2018-04-10 10:00:02  | 2018-04-10 10:03:04  |
| cookie1   | 2018-04-10 10:00:02  | url2    | 2   | 2018-04-10 10:03:04  | 2018-04-10 10:10:00  |
| cookie1   | 2018-04-10 10:03:04  | 1url3   | 3   | 2018-04-10 10:10:00  | 2018-04-10 10:50:01  |
| cookie1   | 2018-04-10 10:10:00  | url4    | 4   | 2018-04-10 10:50:01  | 2018-04-10 10:50:05  |
| cookie1   | 2018-04-10 10:50:01  | url5    | 5   | 2018-04-10 10:50:05  | 2018-04-10 11:00:00  |
| cookie1   | 2018-04-10 10:50:05  | url6    | 6   | 2018-04-10 11:00:00  | NULL                 |
| cookie1   | 2018-04-10 11:00:00  | url7    | 7   | 1970-01-01 00:00:00  | NULL                 |
| cookie2   | 2018-04-10 10:00:00  | url11   | 1   | 2018-04-10 10:00:02  | 2018-04-10 10:03:04  |
| cookie2   | 2018-04-10 10:00:02  | url22   | 2   | 2018-04-10 10:03:04  | 2018-04-10 10:10:00  |
| cookie2   | 2018-04-10 10:03:04  | 1url33  | 3   | 2018-04-10 10:10:00  | 2018-04-10 10:50:01  |
| cookie2   | 2018-04-10 10:10:00  | url44   | 4   | 2018-04-10 10:50:01  | 2018-04-10 10:50:05  |
| cookie2   | 2018-04-10 10:50:01  | url55   | 5   | 2018-04-10 10:50:05  | 2018-04-10 11:00:00  |
| cookie2   | 2018-04-10 10:50:05  | url66   | 6   | 2018-04-10 11:00:00  | NULL                 |
| cookie2   | 2018-04-10 11:00:00  | url77   | 7   | 1970-01-01 00:00:00  | NULL                 |
+-----------+----------------------+---------+-----+----------------------+----------------------+--+

hive的自定义函数

在hive中主要有三种类型的函数:
- UDF函数: 大部分的函数都是UDF函数
  - 特点: 一进一出
  - 例子: substr cast ….
- UDAF函数:
  - 特点: 多进一出
  - 例子: 聚合函数 sum count avg…
- UDTF函数:
  - 特点: 一进多出
  - 例子: explode
自定义 UDF函数
操作步骤

1) 创建项目, 导入相关的依赖

2) 创建包结构, 创建类, 继承 UDF类

3) 重写UDF函数的方法: evaluate   (此方法名称, 千万不能写错了)

4) 在此方法中, 定义函数的执行逻辑

5) 对项目进行打包: jar包

6) 将jar包上传到 hive的 lib的目录下

7) 在会话中, 加载jar包, 开始创建一个新的函数使用这个jar包即可

8) 测试使用

举例: 传入一个小写字母字符串, 将其转换为大写
操作流程:

创建项目, 导入相关的依赖

<dependencies>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>1.2.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>2.7.5</version>
    </dependency>
</dependencies>
  
<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.0</version>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
                <encoding>UTF-8</encoding>
            </configuration>
        </plugin>
    </plugins>
</build>

1. 创建包结构, 创建类, UDF类
1. 重写UDF函数的方法: evaluate

在此方法中, 定义函数的执行逻辑

package com.hive.udf;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class MyUDF extends UDF {


    public Text evaluate(Text value){

        //1. 获取传递进来的参数数据
        String parmas = value.toString();


        //2. 判断
        if(parmas == null || "".equals(parmas)){
            return null;
        }

        //3. 执行转换处理工作

        String upperCase = parmas.toUpperCase();

        return new Text(upperCase);
    }

}

1. 对项目进行打包: jar包
1. 将jar包上传到 hive的 lib的目录下

在会话中, 加载jar包, 开始创建一个新的函数使用这个jar包即可

add jar /export/server/hive-2.1.0/lib/MyUDF.jar;   -- 加载jar包

-- 创建函数: 
create temporary function my_upper as 'com.hive.udf.MyUDF';

测试使用

select my_upper('abcdefg');

+----------+--+
|   _c0    |
+----------+--+
| ABCDEFG  |
+----------+--+

自定义 UDTF函数

操作步骤

1) 创建项目, 导入相关的依赖

2) 创建包结构, 创建类, 继承 GenericUDTF  类

3) 重写UDTF函数的方法: process  

4) 在此方法中, 定义函数的执行逻辑

5) 对项目进行打包: jar包

6) 将jar包上传到 hive的 lib的目录下

7) 在会话中, 加载jar包, 开始创建一个新的函数使用这个jar包即可

8) 测试使用

需求: 自定义一个UDTF，实现将一个任意分隔符的字符串切割成独立的单词,例如:

源数据：
"zookeeper,hadoop,hdfs,hive,MapReduce"
目标数据:
zookeeper
hadoop
hdfs
hive
MapReduce

函数需要传递参数: 
	参数1: 原始数据
	参数2: 分割符号

操作代码

1. 创建项目, 导入相关的依赖
1. 创建包结构, 创建类, 继承 GenericUDTF 类
1. 重写UDTF函数的方法: process

在此方法中, 定义函数的执行逻辑

package com.hive.udtf;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

import java.util.ArrayList;
import java.util.List;

public class MyUDTF  extends GenericUDTF{
    private final transient Object[] forwardListObj = new Object[1];


    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        //设置列名的类型
        List<String> fieldNames = new ArrayList<>();
        //设置列名
        fieldNames.add("column_01");

        List<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>()  ;//检查器列表

        //设置输出的列的值类型
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);

    }

    @Override
    public void process(Object[] objects) throws HiveException {
        //1:获取原始数据
        String args = objects[0].toString();
        //2:获取数据传入的第二个参数，此处为分隔符
        String splitKey = objects[1].toString();
        //3.将原始数据按照传入的分隔符进行切分
        String[] fields = args.split(splitKey);
        //4:遍历切分后的结果，并写出
        for (String field : fields) {
            //将每一个单词添加值对象数组
            forwardListObj[0] = field;
            //将对象数组内容写出
            forward(forwardListObj); // 调用一次forward方法, 相当于输出一行数据
        }
    }

    @Override
    public void close() throws HiveException {

    }
}

1. 后续与UDF函数一致的

☆ hive的调优

hive的压缩方案

压缩有什么用呢?
- 能够在有限的空间下, 存储更多的数据
- 在hive中一般推荐使用压缩方式: snappy LZO GZIP , 其中snappy压缩方式在整个占比比较高
在MapReduce中, 可以对那些阶段设置压缩呢?
- map输出端可以进行压缩
- reduce的输出端可以进行压缩
如何在MapReduce中配置压缩方案呢?

hadoop checknative: 此命令式用来查看hadoop本地库是否支持各种压缩方案

io.compression.codecs  默认值为 org.apache.hadoop.io.compress.DefaultCodec 表示不压缩 输入端压缩方案
	可选值: 
		org.apache.hadoop.io.compress.SnappyCodec
		org.apache.hadoop.io.compress.GzipCodec
		com.hadoop.compression.lzo.LzopCodec

mapreduce.map.output.compress 是否开启map端压缩  默认为 false   如果开启设置为true
mapreduce.map.output.compress.codec   默认: org.apache.hadoop.io.compress.DefaultCodec
	表示: 设置map端压缩方案
	可选值: 
		org.apache.hadoop.io.compress.SnappyCodec
		org.apache.hadoop.io.compress.GzipCodec
		com.hadoop.compression.lzo.LzopCodec
		
mapreduce.output.fileoutputformat.compress  是否开启reduce端压缩 默认为 false   如果开启设置为true
mapreduce.output.fileoutputformat.compress.codec 默认: org.apache.hadoop.io.compress.DefaultCodec
	表示: 设置reduce端压缩方案
    可选值: 
    	org.apache.hadoop.io.compress.SnappyCodec
		org.apache.hadoop.io.compress.GzipCodec
		com.hadoop.compression.lzo.LzopCodec

mapreduce.output.fileoutputformat.compress.type 设置压缩类型 默认 RECORD(行压缩)
	可选值: 
	   none  不采用任何格式
	   BLOCK 块压缩  (推荐使用)

如何在hive中设置map端压缩方案:

1
2
3

set hive.exec.compress.intermediate=true;  是否开启hive中间结果压缩操作 默认为false
set mapreduce.map.output.compress=true; 是否开始map端的压缩操作 默认为false 
set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec; 设置map端压缩方案

如何在hive设置reduce端压缩方案:

set hive.exec.compress.output=true;  是否开启hive最终结果压缩方案  默认为false

set mapreduce.output.fileoutputformat.compress=true; 是否开启MR的之中压缩方式 默认为false

set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;  设置reduce端压缩方案

set mapreduce.output.fileoutputformat.compress.type=BLOCK; 设置压缩的格式为块压缩 默认为RECORD

hive的数据存储格式

在hive中主要支持有四种存储格式: textFile sequenceFile ORC parquet
一般将以上四种存储格式, 分为两种类型: 一种为行式存储另一种列式存储方案
textFile和sequence是行式存储方案, ORC 和parquet是列式存储方案
在hive中, 一般构建表, 都是ORC类型

生产中建表方式:

create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
partitioned by (分区子段 ....)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="SNAPPY");

数据倾斜

group by优化:

count(distinct ):

思考: 在对整个表进行count统计的时候, 请问, 会执行MR中的reduce是否会运行多个呢? 只能有一个
注意: 对整个表进行聚合操作的时候, 翻译后的MR只能跑一个reduce
既然这样, 在只有一个reduce的情况下, 有执行了distinct(去重)的操作,而去重操作必须在reduce端才可以完成,如果数据量比较大, reduce端会承受大量的数据, 导致执行效率变慢

思考如何解决呢?

原来的SQL: 
    select count(distinct ip) from 表 ; --- 引发上面描述的问题
优化后SQL: 
	select count(ip)  from (select ip from 表 group by ip) tmp ;
	
	
	以上两种SQL的实现, 第二个SQL会执行两个MR, 第一个SQL会执行一个MR, 当数据量足够庞大的时候, 第二种方法执行效率优于第一种方法, 当数据量比较少的时候, 第一种方案比第二种效率高

注意: group by 也是容易产生数据倾斜问题的(两段聚合)

假设: 分别统计男生 女生人数各是多少, 并且将结果分在两个不同文件中

map:  k2(性别) 男60   v2(1) 女 3次

map:  男 40     女  5次

map:  男 55     女  10

reduce : 接收男   接收到 155  个 kv 键值对


reduce : 接收女   接收到 18 个 kv键值对

此时发生了数据倾斜问题

如何解决呢? 

采用 combiner的操作: 让每个map都进行局部聚合操作:

第一个map输出:  男  60   女 3  仅输出两对数据

第二个map输出: 男  40  女 5   仅输出两对数据

第三个 map 输出:  男 55   女  10 仅输出两对数据


接着到达reduce端: 

reduce: 接收男    接收到 3对

reduce:  接收女   接收到 3对



请在思考一种方式: 通过二个MR实现大的combiner

第一个MR: 将 数据不论男女 平均分发给两个reduce即可, 两个reduce各自会计算出结果:
   
第一个reduce: 
     男 80 
     女 10
第一个reduce: 
	 男 75 
	 女 8
 
接着运行第二个MR: 将一个MR的结果 当做第二个MR的输入, 此时按照  男  女 分发各个reduce即可

reduce : 接收男    接收到二对

reduce : 接收女    接收到二对

1 2	set hive.groupby.skewindata=true; #思想：就是先随机分发并处理，再按照key group by来分发处理。

使用map join

mapjoin,bucket mapjoin,SMB join测试

中表和大表join核心思想就是通过各种方法，使用map join

小表 join 大表:
- 解决方案: 采用 map Join来解决
- 如何设置呢?
1
2
set hive.auto.convert.join = true; 是否开启自动检测mapJoin
set hive.mapjoin.smalltable.filesize= 25000000; 大小表阈值默认大于 25M左右
- 在编写SQL的时候, 是将小表放置在join前面呢, 还是大表放置在前面?
  - hive的1.x版本之前 : 小表在join的前面
  - hive的1.x版本之后: 没有任何区别, 放前放后, 无所谓了都可以
中大型表 join 大表
- 解决方案:
  - 1. 能先对数据进行过滤, 就先过滤在join, 这样有可能会满足小表阈值
  - 1. bucket Join : 分桶的mapJoin方案
    1
    2
    #表须是分桶表
    set hive.optimize.bucketmapjoin = true;
大表 join 大表:
- 解决方案:
  - 1. 能先过滤, 先过滤, 然后在join的操作, 减少map到reduce之间数据传输量, 从而提升性能
  - 1. 如果大表中join字段的数据有很多的空值,这个时候, 也需要解决, 因为这些空值容易让reduce出现数据倾斜问题
    - 方案一: 提前将那些null值过滤掉
    - 方案二: 将Null值使用随机数替换即可
  - 1. SMB join: 分桶表排序 mapJoin方案
    1
    2
    3
    4
    set hive.auto.convert.sortmerge.join=true;
    set hive.optimize.bucketmapjoin = true;
    set hive.optimize.bucketmapjoin.sortedmerge = true;
    set hive.auto.convert.sortmerge.join.noconditionaltask=true;
控制空值分布

将为空的key转变为字符串加随机数或纯随机数，将因空值而造成倾斜的数据分不到多个Reducer。

注：对于异常值如果不需要的话，最好是提前在where条件里过滤掉，这样可以使计算量大大减少

实践中，可以使用case when对空值赋上随机值。此方法比直接写is not null更好，因为前者job数为1，后者为2.

使用case when实例1：
1
2
3
4
5
6
select userid, name from user_info a

join (
select case when userid is null then cast (rand(47)* 100000 as int )
else userid end from user_read_log
) b on a.userid = b.userid

hive的SQL的优化措施

列裁剪:
- 在执行SQL的时候, 如果发现操作的表, 只操作其中某几个字段, 其他字段不涉及, 那么可以选择只读取跟我们相关的列即可
- 如何设置呢?
  1
  hive.optimize.cp=true; 默认值就是true
分区裁剪:
- 如果操作的是一个分区表, 建议携带上分区字段, 从而减少扫描量, 提升效率, 同时如果表能先对数据进行过滤, 那么一定要先过滤再join
- 如何设置分区裁剪:
  1
  hive.optimize.pruner=true; 默认就是true

并行执行

说明: 在执行一个hive的SQL的时候, 这个SQL有可能会被翻译为多个MR , 而且多个MR之间没有任何的关联, 那么此时可以运行这多个MR 并行执行, 从而提升效率

set hive.exec.parallel=true;              --打开任务并行执行
set hive.exec.parallel.thread.number=16;  --同一个sql允许最大并行度，默认为8。

举个例子:
   select * from A 
   union all
   select * from B;

注意: 不管是否可以并行跑, 只要想并行, 前提是服务器有资源, 如果没有, 即使SQL可以并行, 依然不会并行

笛卡尔积:
- 什么时候会发生: 多表join的时候, 缺少on的关联条件
- 所以说, 在编写的多表join的时候, 一定不要少了on关联条件, on条件数量至少是表数量-1 个条件
- 注意:
  - 关联条件, 必须使用 on来书写, 不能使用where, 因为在hive中如果使用where来编写关联条件, 是先产生笛卡尔积的现象, 然后通过where进行过滤, 而 on是在join过程中将不符合的直接删除

hive的其他的调优措施

小文件过多优化

小文件如果过多，对 hive 来说，在进行查询时，每个小文件都会当成一个块，启动一个Map任务来完成，而一个Map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的Map数量是受限的。

所以我们有必要对小文件过多进行优化。

1 2	//执行Map前进行小文件合并 set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

hive 的fetch本地抓取策略

描述: 能不走MR 尽量不走MR

如何设置本地抓取策略:

hive.fetch.task.conversion :
	可选值:  
		none : 所有的查询都会执行MR
		minimal : 可以保证在执行全表查询,  根据字段查询 以及limit 可以不走MR的  (老版本默认值: 0.x版本)
		more   :  可以保证在 执行全表查询,  根据字段查询 , 简单过滤操作, 以及limit 可以不走MR的 (1.x后默认值)

hive的本地运行

描述: 能走本地MR, 尽量走本地MR, 不走集群MR

如何配置本地运行方案:

set hive.exec.mode.local.auto=true;  是否开启本地MR  默认值为 false

set hive.exec.mode.local.auto.inputbytes.max=51234560; 设置多大的数据量走集群MR  默认值 128M
set hive.exec.mode.local.auto.input.files.max=10;  设置文件数量的限制   默认值4

什么情况下会执行本地MR呢? 
    默认当查询数据量小于等于128M 并且读取的文件数量小于 4的时候, 才会执行本地MR , 否则只要有一个条件不满足, 就会执行集群MR的操作

一般在测试环境中使用, 生产中环境设不设置, 基本没啥大用

hive的动态分区
动态分区主要目的: 帮助快速在另一个表形成多个分区的操作
注意：在SELECT子句的最后几个字段，必须对应前面PARTITION (p_time)中指定的分区字段，包括顺序。
相关参数设置:

1) 开启动态分区的支持:
    set hive.exec.dynamic.partition=true;  默认为true
2) 设置hive语法模式为 非严格模式:
	set hive.exec.dynamic.partition.mode=nonstrict; 默认值为  strict 
	
3) 设置所有执行MR的节点, 最大可以创建多少个分区:
	set  hive.exec.max.dynamic.partitions= ; 默认 1000个
4) 设置每个MR的节点. 最大可以创建多少个分区
	set hive.exec.max.dynamic.partitions.pernode=100; 默认为100个
5) 整个mr任务中, 最大一次性可以操作多少个HDFS的文件:
	set hive.exec.max.created.files=100000;  默认为 10w

如何调整map数量和reduce数量

map数量的调整:

如何调少数量:

set mapred.max.split.size=112345600;
set mapred.min.split.size.per.node=112345600;
set mapred.min.split.size.per.rack=112345600;
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

以上四个参数配置, 描述 在map读取之前, 进行文件合并操作

如何增加数量: 对文件进行切分为多个文件即可

reduce数量调整:

hive默认会自动调整reduce的数量:

1
2
3

公式:  N=min（参数2，总输入数据量/参数1）
    参数2: 为 hive.exec.reducers.max设置的值 默认为 999
    参数1: hive.exec.reducers.bytes.per.reducer 默认为 1GB

调整的方式:

set hive.exec.reducers.bytes.per.reducer=524288000;  调整每个reduce能够处理数据量
	当调小后, reduce数量增加 , 调大后, reduce数量就会减少

set mapred.reduce.tasks=15; 强制调整reduce的数量

在什么情况下, 不管如何调整, reduce都只会有一个?
- 1. 执行 ORDER BY 全局排序
- 1. 没有 group by的聚合操作
- 1. 执行笛卡尔积

严格模式:

在hive中,一旦开启了严格模式, 会限制你某种不合规(效率极差)SQL的请求:

1) 对分区表进行查询的时候 不携带分区字段
2) 使用order by 没有使用 limit
3) SQL中出现笛卡尔积的现象
前提: 数据量足够大
set hive.mapred.mode = strict;  --开启严格模式
set hive.mapred.mode = nostrict; --开启非严格模式

jvm重用

在hive2.x版本无需做任何, 内置处理掉

说明: 让MR可以重复使用资源容器

推测执行

一般不建议开启

set mapred.map.tasks.speculative.execution=true
set mapred.reduce.tasks.speculative.execution=true
set hive.mapred.reduce.tasks.speculative.execution=true;