Hive-安装部署Hive-配置元数据库metaStore
配置metaStore到MySQL使用元数据服务的方式访问Hive使用JDBC方式访问Hive后台启动 Hive-常用SHELL命令
Hive常用交互命令Hive常用命令Hive参数配置方式 Hive-数据类型
基本数据类型集合数据类型集合数据操作 Hive-DDL数据定义
数据库操作内部表外部表管理表与外部表的互相转换数据表操作 Hive-DML数据操作
load-向表中装载数据insert-通过查询语句向表中插入数据as select-查询语句中创建表并加载数据import-导入数据到hive表insert-导出数据truncate-清除表中数据 Hive-查询操作Hive-分区表
二级分区数据直接上传到分区目录三种方式动态分区 Hive-分桶表Hive-函数
Hive官网地址：http://hive.apache.org/
文档查看地址https://cwiki.apache.org/confluence/display/Hive/GettingStarted
下载地址https://github.com/apache/hive
Hive-安装部署解压hive到指安装目录下
tar -zxvf apache-hive-3.1.2-bin.tar.gz -C /opt/module/
配置hive环境变量
# HIVE_HOMEexport HIVE_HOME=/opt/module/hiveexport PATH=$PATH:$HIVE_HOME/bin
解决日志jar包冲突（一般不用）
mv $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.jar $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.bak
初始化元数据库
bin/schematool -dbType derby -initSchema
报错解决如下
原因：hadoop和hive的两个guava.jar版本不一致，删除低版本的那个，将高版本的复制到低版本目录下
hive所在位置：/opt/module/hive/lib/
hadoop所在位置：/opt/module/hadoop-3.1.3/share/hadoop/common/lib
再次执行初始化命令bin/schematool -dbType derby -initSchema
Hive启动
在hive目录下执行
bin/hive
Hive默认使用的元数据库为derby，开启Hive之后就会占用元数据库，且不与其他客户端共享数据，所以需要将Hive的元数据地址改为MySQL
报错解决如下
需要先启动hadoop
Hive-配置元数据库metaStoreHive默认使用的元数据库为derby，开启Hive之后就会占用元数据库，且不与其他客户端共享数据，所以需要将Hive的元数据地址配置到外部系统
配置metaStore到MySQLhive/conf创建配置文件hive-site.xml
<?xml version="1.0"?><?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
登录MySQL创建Hive元数据库
mysql -uroot -proot;mysql> create database metastore;mysql> quit;
初始化Hive元数据库
schematool -initSchema -dbType mysql -verbose
启动Hive
bin/hive
使用元数据服务的方式访问Hive在hive-site.xml文件中添加如下配置信息
启动metastore，启动后窗口不能再操作，需打开一个新的shell窗口做别的操作
bin/hive --service metastore
启动hive
bin/hive
使用JDBC方式访问Hive在 hive-site.xml 文件中添加如下配置信息
查看端口是否被占用
netstat -anp|grep 10000
启动hiveserver2
bin/hive --service hiveserver2
等待hiveserver2启动完成
通过netstat -anp|grep 10000查看hiveserver2是否启动，在启动的时候保证端口没被占用，启动之后10000端口会有显示，在启动查端口前，没有查到占用就说明hiveserver2没有启动，hiveserver2会显示七八个Hive Session ID之后，才会真正启动，这要等个几分钟
或者查看hive的运行日志，在/tmp/用户名/hive.log
启动beeline客户端
bin/beeline -u jdbc:hive2://hadoop102:10000 -n root
看到如下界面表示成功
Connecting to jdbc:hive2://hadoop102:10000Connected to: Apache Hive (version 3.1.2)Driver: Hive JDBC (version 3.1.2)Transaction isolation: TRANSACTION_REPEATABLE_READBeeline version 3.1.2 by Apache Hive0: jdbc:hive2://hadoop102:10000>
启动报错解决如下
解决方式：在hadoop的配置文件core-site.xml增加如下配置，重启hdfs，其中“xxx”是连接beeline的用户，将“xxx”替换成自己的用户名即可。一定要重启hadoop，否则不会生效的还是报错！
*表示可通过超级代理xxx操作hadoop的用户、用户组和主机
前台启动的方式导致需要打开多个shell窗口，可以使用如下方式后台方式启动
nohup: 放在命令开头，表示不挂起,也就是关闭终端进程也继续保持运行状态/dev/null：是Linux文件系统中的一个文件，被称为黑洞，所有写入改文件的内容都会被自动丢弃2>&1 : 表示将错误重定向到标准输出上&: 放在命令结尾,表示后台运行一般会组合使用nohup [xxx 命令操作]> file 2>&1 &，表示将xxx命令运行的结果输出到file中，并保持命令启动的进程在后台运行
nohup hive --service metastore 2>&1 &nohup hive --service hiveserver2 2>&1 &
编写脚本启动hive
vim $HIVE_HOME/bin/hiveservices.sh
#!/bin/bashHIVE_LOG_DIR=$HIVE_HOME/logsif [ ! -d $HIVE_LOG_DIR ]thenmkdir -p $HIVE_LOG_DIRfi#检查进程是否运行正常，参数 1 为进程名，参数 2 为进程端口function check_process(){ pid=$(ps -ef 2>/dev/null | grep -v grep | grep -i $1 | awk '{print $2}') ppid=$(netstat -nltp 2>/dev/null | grep $2 | awk '{print $7}' | cut -d '/' -f 1) echo $pid [[ "$pid" =~ "$ppid" ]] && [ "$ppid" ] && return 0 || return 1}function hive_start(){ metapid=$(check_process Hivemetastore 9083) cmd="nohup hive --service metastore >$HIVE_LOG_DIR/metastore.log 2>&1 &" [ -z "$metapid" ] && eval $cmd || echo "metastroe 服务已启动" server2pid=$(check_process HiveServer2 10000) cmd="nohup hiveserver2 >$HIVE_LOG_DIR/hiveServer2.log 2>&1 &" [ -z "$server2pid" ] && eval $cmd || echo "HiveServer2 服务已启动" }function hive_stop(){metapid=$(check_process Hivemetastore 9083) [ "$metapid" ] && kill $metapid || echo "metastore 服务未启动" server2pid=$(check_process HiveServer2 10000) [ "$server2pid" ] && kill $server2pid || echo "HiveServer2 服务未启动" }case $1 in"start") hive_start ;;"stop") hive_stop ;;"restart") hive_stop sleep 2 hive_start ;;"status") check_process Hivemetastore 9083 >/dev/null && echo "metastore 服务运行正常" || echo "metastore 服务运行异常" check_process HiveServer2 10000 >/dev/null && echo "HiveServer2 服务运行正常" || echo "HiveServer2 服务运行异常" ;;*) echo Invalid Args! echo 'Usage: '$(basename $0)' start|stop|restart|status' ;;esac
chmod +x $HIVE_HOME/bin/hiveservices.sh
hiveservices.sh start
Hive-常用SHELL命令 Hive常用交互命令-e：不进入hive的交互窗口执行sql语句
bin/hive -e "select id from test;"
-f：执行脚本中sql语句
创建sql脚本hive.sql
select * from test;
执行脚本
bin/hive -f data/hive.sql > data/result.txt
Hive常用命令退出hive窗口
hive> exit;hive> quit;
在hive客户端命令窗口查看hdfs文件系统
hive> dfs -ls /;
查看在hive中输入的所有历史命令
进入到当前用户的根目录cd ~，查看.hivehistory文件
cat .hivehistory
Hive运行日志信息配置
Hive的log 默认存放在/tmp/当前用户名/hive.log 目录下
修改hive的log存放日志存放路径
第一步：修改hive/conf/hive-log4j2.properties.template文件名称为hive-log4j2.properties
第二步：在hive-log4j2.properties文件中修改log存放位置
打印当前库和表头
在hive-site.xml中加入如下两个配置
查看当前所有的配置信息
hive> set
配置文件声明方式
默认配置文件：hive-default.xml
用户自定义配置文件：hive-site.xml
用户自定义配置会覆盖默认配置，另外Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。配置文件的设定对本机启动的所有Hive进程都有效
命令行参数方式
启动Hive时，可以在命令行添加-hiveconf param=value来设定参数，仅对本次hive启动有效
bin/hive -hiveconf mapred.reduce.tasks=10;
查看参数设置
hive> set mapred.reduce.tasks;
参数声明方式
可以在HQL中使用SET关键字设定参数
hive> set mapred.reduce.tasks=100;
上述三种设定方式的优先级依次递增。即配置文件<命令行参数<参数声明。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在会话建立以前已经完成了
Hive-数据类型 基本数据类型对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数
集合数据类型Hive有三种复杂数据类型ARRAY、MAP和STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套
集合数据操作{ "name": "czs", "friends": ["qhh" , "xyx"] , //列表 Array, "children": { //键值 Map, "czs age": 18 , "qhh age": 19 } "address": { //结构 Struct, "street": "tkk", "city": "xujc" }}
创建数据库
create table stu(name string,friends array
row format delimited fields terminated by ','：列分隔符
collection items terminated by '_'：MAP STRUCT和ARRAY的分隔符(数据分割符号)
map keys terminated by ':'：MAP中的key与value的分隔符
lines terminated by 'n';：行分隔符
创建数据
数据繁多，可以通过外部文件方式导入数据
czs,qhh_xyx,czs age:18_qhh age:19,tkk_xujc
导入文本数据到测试表
load data local inpath '/hive/datas/test.txt' into table test;
访问数据
select friends[1],children['czs age'],address.city from test where name="czs";
Hive-DDL数据定义 数据库操作显示数据库
show databases
过滤显示查询的数据库
show databases like 'db_hive*'
显示数据库信息
desc database db_hive
显示数据库详细信息
desc database extended db_hive
切换当前数据库
use db_hive
描述数据库属性信息
alter database db_hive set dbproperties('createauth'='czs')
if exists避免要删除的空数据库不存在错误
drop database if exists db_hive
cascade强制删除数据不为空的数据库
drop database db_hive cascade
if not exists避免要创建的数据库已存在错误
create database db_hive if not exists db_hive
指定数据库存放位置创建数据库
create database db_hive location '/db_hive.db'
内部表默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表Hive会或多或少地控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir所定义的目录的子目录下
创建普通表
create table if not exists student(id int, name string)row format delimited fields terminated by 't'stored as textfilelocation '/user/hive/warehouse/student';
根据查询结果创建表
create table if not exists student2 as select id, name from student;
根据已存在的表结构创建表
create table if not exists student3 like student;
外部表因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉，外部表删除后hdfs中的数据还在，但是metadata中的元数据已被删除
创建外部表
create external table if not exists dept(deptno int,dname string,loc int)row format delimited fields terminated by 't';
管理表与外部表的互相转换修改内部表为外部表
alter table student set tblproperties('EXTERNAL'='TRUE');
修改外部表为内部表
alter table student set tblproperties('EXTERNAL'='FALSE');
数据表操作查看表信息
desc formatted score;
查看表字段信息
desc student;
修改表
alter table student rename to stu
删除表
drop table dept;
增加列信息
alter table dept add columns(deptdesc string);
修改列信息
alter table dept change column deptdesc desc string;
替换列信息
alter table dept replace columns(deptno string, dnamestring, loc string);
Hive-DML数据操作 load-向表中装载数据load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];
load data:表示加载数据
local:表示从本地加载数据到hive表；否则HDFS加载数据到 hive 表
inpath:表示加载数据的路径
overwrite:表示覆盖表中已有数据，否则表示追加
into table:表示加载到哪张表
student:表示具体的表
partition:表示上传到指定分区
加载本地文件到hive
load data local inpath '/hive/datas/student.txt' into table default.student;
加载hdfs数据到hive
load data inpath '/user/hive/student.txt' into table default.student;
insert-通过查询语句向表中插入数据insert into：以追加数据的方式插入到表或分区，原有数据不会删除insert overwrite：会覆盖表中已存在的数据
insert into table student values(1,'xyx'),(2,'czs');insert overwrite table student select id, name from student where name='czs';
as select-查询语句中创建表并加载数据create table if not exists student as select id, name from student;
import-导入数据到hive表import table student from '/hive/export/student';
insert-导出数据insert overwrite local directory '/hive/data/export/student' select * from student;
truncate-清除表中数据truncate table student;
Hive-查询操作内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
左外连接：JOIN操作符左边表中符合WHERe子句的所有记录将会被返回
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
右外连接：JOIN操作符右边表中符合WHERe子句的所有记录将会被返回
select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
满外连接：将会返回所有表中符合WHERe语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代
select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
Like,Rlike：Like运算选择类似的值%代表任意字符,_代表一个字符，Rlike可以通过正则表达式的方式指定匹配条件
# 查找名字以 A 开头的员工信息select * from emp where ename LIKE 'A%';# 查找名字中第二个字母为 A 的员工信息select * from emp where ename LIKE '_A%';# 查找名字中带有 A 的员工信息select * from emp where ename RLIKE '[A]';
GroupBy：和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作
# 计算 emp 表每个部门的平均工资select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
Having：只用于group by分组统计语句
# 求每个部门的平均薪水大于 2000 的部门select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
Order By：全局排序，只有一个Reducer，ASC默认升序，DESC降序
select ename, deptno, sal from emp order by deptno, sal;
Sort By：局部排序，为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序
# 设置reducer个数set mapreduce.job.reduces=3;# 查看reducer个数set mapreduce.job.reduces# 将查询结果导入到文件中insert overwrite local directory '/opt/module/data/sortby-result' select * from emp sort by deptno desc;
Distribute By：分区，控制某个特定行应该到哪个reducer， 类似MR中partition自定义分区，结合Sort By使用，分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区
set mapreduce.job.reduces=3;# 先按照部门编号分区，再按照员工编号降序排序。insert overwrite local directory '/opt/module/data/distribute-result' select * from emp distribute by deptno sort by empno desc;
Cluster By：当distribute by和sorts by字段相同时，可以使用cluster by方式，但是排序只能是升序排序的方式
# 等价select * from emp cluster by deptno;select * from emp distribute by deptno sort by deptno;
Hive-分区表分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERe子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');load data local inpath '/opt/module/hive/datas/dept_20200402.log' into table dept_partition partition(day='20200402');load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition partition(day='20200403');
查询分区中的数据
# 单分区查询select * from dept_partition where day='20200401';# 多分区查询select * from dept_partition where day='20200401'unionselect * from dept_partition where day='20200402'unionselect * from dept_partition where day='20200403';
增加分区
# 创建单分区alter table dept_partition add partition(day='20200404');# 创建多分区alter table dept_partition add partition(day='20200405') partition(day='20200406');
删除分区
# 删除单分区alter table dept_partition drop partition (day='20200406');# 删除多分区alter table dept_partition drop partition (day='20200404'), partition(day='20200405');
查看分区
# 查看有多少分区show partitions dept_partition;# 查看分区表结构desc formatted dept_partition;
二级分区创建二级分区
create table dept_partition2(deptno int, dname string, loc string)partitioned by (day string, hour string)row format delimited fields terminated by ' ';
导入分区数据
load data local inpath '/root/dept.log' into table dept_partition2 partition(day='20200401', hour='12');
查询分区数据
select * from dept_partition2 where day='20200401' and hour='12';
数据直接上传到分区目录三种方式上传数据后修复
# 上传数据hive (default)> dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;hive (default)> dfs -put /opt/module/datas/dept_20200401.log /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;# 查询数据（查询不到刚上传的数据）hive (default)> select * from dept_partition2 where day='20200401' and hour='13';# 执行修复命令hive> msck repair table dept_partition2;# 再次查询hive (default)> select * from dept_partition2 where day='20200401' and hour='13';
上传数据后添加分区
# 上传数据hive (default)> dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;hive (default)> dfs -put /opt/module/datas/dept_20200401.log /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;# 添加分区hive (default)> alter table dept_partition2 add partition(day='20200401',hour='14');# 查询数据hive (default)> select * from dept_partition2 where day='20200401' and hour='14';
创建文件夹后load数据到分区
# 创建目录hive (default)> dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=15;# 上传数据hive (default)> load data local inpath '/opt/module/hive/datas/dept.log' into table dept_partition2 partition(day='20200401',hour='15');# 查询数据hive (default)> select * from dept_partition2 where day='20200401' and hour='15';
动态分区关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(DynamicPartition)，只不过，使用Hive的动态分区，需要进行相应的配置。
开启动态分区
# 默认开启hive.exec.dynamic.partition=true
设置非严格模式
设置为非严格模式，动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区
hive.exec.dynamic.partition.mode=nonstrict
设置最大分区数
在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000
hive.exec.max.dynamic.partitions=1000
设置单个节点最大分区数
在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错
hive.exec.max.dynamic.partitions.pernode=100
设置最大创建文件数
整个MR Job中，最大可以创建多少个HDFS文件。默认100000
hive.exec.max.created.files=100000
设置空分区不抛出异常
当有空分区生成时，是否抛出异常。一般不需要设置。默认 false
hive.error.on.empty.partition=false
动态分区
# 创建目标动态分区表create table dept_partition_dy(id int, name string) partitioned by (loc int) row format delimited fields terminated by 't';# 设置动态分区set hive.exec.dynamic.partition.mode = nonstrict;hive (default)> insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;# 查看目标分区表的分区情况hive (default)> show partitions dept_partition;
Hive-分桶表分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive可以进一步组织成桶，也就是更为细粒度的数据范围划分。分桶是将数据集分解成更容易管理的若干部分的另一个技术。分区针对的是数据的存储路径；分桶针对的是数据文件
分桶规则：Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中
# 创建分桶表create table stu_buck(id int, name string)clustered by(id) into 4 bucketsrow format delimited fields terminated by 't';# 查看表结构hive (default)> desc formatted stu_buck;Num Buckets: 4# 导入数据到分桶表中，load 的方式hive (default)> load data inpath '/student.txt' into table stu_buck;# 查看分桶的数据hive(default)> select * from stu_buck;
注意事项
reduce的个数设置为-1,让Job自行决定需要用多少个reduce或者将reduce的个数设置为大于等于分桶表的桶数从hdfs中load数据到分桶表中，避免本地文件找不到问题不要使用本地模式 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 number]
常用函数
select count(*) cnt from emp;select max(sal) max_sal from emp;select min(sal) min_sal from emp;select sum(sal) sum_sal from emp;select avg(sal) avg_sal from emp;
行转列
CONCAT：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS：按指定分隔符连接
COLLECT_SET：将某字段的值进行去重汇总，产生Array类型字段
SELECtt1.c_b,CONCAT_WS("|",collect_set(t1.name))FROM (SELECtNAME,CONCAT_WS(',',constellation,blood_type) c_bFROM person_info)t1GROUP BY t1.c_b
列转行
EXPLODE：将hive一列中复杂的Array或者Map结构拆分成多行
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
用于和split，explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合
SELECtmovie,category_nameFROMmovie_infolateral VIEW explode(split(category,",")) movie_info_tmp AS category_name;
窗口函数
详情请看这
over() 此时每一行的窗口都是所有的行
SELECt id,name,num,sum(num) over() sum1 from aa
over(order by id) id=1的窗口只有一行，id=2的窗口包括id=1,id=2
SELECt id,name,num,sum(num) over(order by id) sum1 from aa
over(partition by name) 每一行根据name来区分窗口
SELECt id,name,num,sum(num) over(partition by name) sum1 from aa
over(partition by name order by id) 每一行根据name来区分窗口,再根据order by取具体的范围
SELECt id,name,num,sum(num) over(partition by name order by id) sum1 from aa
over( order by id range between 1 preceding and 1 following ) range 代表范围 preceding 向前 following 向后 窗口范围当前行前后一行
SELECt id,name,num,sum(num) over( order by id range between 1 preceding and 1 following ) sum1 from aa
over( order by id range between CURRENT ROW AND 1 following ) 窗口范围当前行和后面一行
SELECt id,name,num,sum(num) over( order by id range between CURRENT ROW AND 1 following ) sum1 from aa
自定义UDF
源码地址
将自定义函数打包之后，将jar包添加到hive的classpath路径下
hive (default)> add jar /opt/module/hive/data/myudtf.jar;
创建临时函数与开发好的java class关联
hive (default)> create temporary function myudtf as "czs.study.hive.MyUDTF";
使用自定义的函数
select myudtf("hello,world,hadoop,hive",",");