一:hive第一天
1.1概述
hive是基于Hadoop的一个数据仓库工具,用来进行数据提取、转化、加载,这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表,并提供SQL查询功能,能将SQL语句转变成MapReduce任务来执行。Hive的优点是学习成本低,可以通过类似SQL语句实现快速MapReduce统计,使MapReduce变得更加简单,而不必开发专门的MapReduce应用程序。hive是十分适合数据仓库的统计分析和Windows注册表文件。
hive不适合用于联机(online)事务处理,也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业。hive的特点包括:可伸缩(在Hadoop的集群上动态添加设备)、可扩展、容错、输入格式的松散耦合。
Hive提供了一个SQL命令的操作接口,允许用户可以使用类似SQL的Hive的Query Language执行一些离线的SQL分析。但是Hive和传统的数据库不同,Hive只是构建在Hadoop的MapReduce之上的SQL解析工具,并不参与数据的管理和存储,Hive中所有的数据都是在运行任务的时候才会真正的加载。
Hive配置
安装前确保已安装hadoop和mysql
1、解压hive安装包,解压到/usr目录下
[root@CentOS ~]# tar -zxf apache-hive-1.2.2-bin.tar.gz -C /usr/
[root@CentOS ~]# cd /usr/apache-hive-1.2.2-bin/
2、创建conf/hive-site.xml,配置以下内容
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://CentOS:3306/hive?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>root</value>
</property>
</configuration>
3、配置HIVE_HOME环境变量
HADOOP_HOME=/usr/hadoop-2.9.2
HIVE_HOME=/usr/apache-hive-1.2.2-bin
JAVA_HOME=/usr/java/latest
PATH= P A T H : PATH: PATH:JAVA_HOME/bin: H A D O O P H O M E / b i n : HADOOP_HOME/bin: HADOOPHOME/bin:HADOOP_HOME/sbin:$HIVE_HOME/bin
CLASSPATH=.
export JAVA_HOME
export PATH
export HADOOP_HOME
export CLASSPATH
export HIVE_HOME
4、启动hive
[root@CentOS ~]# hive 后台单用户启动服务
[root@CentOS ~]# hiveserver2 >/dev/null 2>&1 & 后台多用户启动服务
[1] 8772
[root@CentOS ~]# beeline -u jdbc:hive2://CentOS:10000 -n root
加载数据到表中:load data local inpath ‘xxx路径’ overwrite into table Xxx表
json格式文件:需要添加jar文件 add jar /usr/apache-hive-1.2.2-bin/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.2.jar删除的时候使用delete指令
5、Hive有以下几点特性:
Hive和关系数据库存储文件的系统不同,Hive使用的是Hadoop的HDFS,关系数据库则是服务器本地的文件系统。
Hive使用的计算模型是MapReduce,而关系数据库则是自己设计的计算模型。
关系数据库都是为OLTP进行设计的,而Hive则是为海量数据做数据挖掘设计的,实时性很差,实时性的区别导致Hive的应用场景和关系数据库有很大的不同。
Hive很容易扩展自己的存储能力和计算能力,这个是继承Hadoop的特性,而关系数据库在这个方面要比Hive差很多。
二:hive第二天
1、DDL操作
查看数据库show databases;
使用数据库use test;
新建数据库CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name;
查看数据库信息DESC DATABASE [EXTENDED] db_name;
删除数据库DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];默认行为是 RESTRICT,如果数据库中存在表则删除失败。要想删除库及其中的表,可以使用 CASCADE 级联删除。
查看当前数据库select current_database();
1.1、创建管理表、外部表、分区表、分桶表、临时表、复制表
- 管理表
内部表也称之为MANAGED_TABLE;默认存储在/user/hive/warehouse下,也可以通过location指定;删除表时,会删除表数据以及元数据;
- 外部表
外部表称之为EXTERNAL_TABLE;在创建表时可以自己指定目录位置(LOCATION);删除表时,只会删除元数据不会删除表数据;
create external table if not exists t_access(
…
)…
LOCATION ‘/hive/t_access’;
- 分区表
Hive中的表对应为HDFS上的指定目录,在查询数据时候,默认会对全表进行扫描,这样时间和性能的消耗都非常大。分区为HDFS上表目录的子目录,数据按照分区存储在子目录中。如果查询的where字句的中包含分区条件,则直接从该分区去查找,而不是扫描整个表目录,合理的分区设计可以极大提高查询速度和性能。
CREATE EXTERNAL TABLE t_employee(
…
) PARTITIONED BY (deptno INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY “\t”
LOCATION ‘/hive/t_employee’
加载:load data local inpath ‘/root/baizhi/t_employee’ overwrite into table t_employee partition(deptno=10);
分桶表
分区表是为了将文件按照分区文件夹进行粗粒度文件隔离,但是分桶表是将数据按照某个字段进行hash计算出所属的桶,然后在对桶内的数据进行排序 。
CREATE EXTERNAL TABLE t_employee_bucket(
…
deptno INT)
CLUSTERED BY(id) SORTED BY(salary ASC) INTO 6 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY “\t”
LOCATION ‘/hive/employee_bucket’;
必须设置set hive.enforce.bucketing = true;
加载:INSERT INTO TABLE t_employee_bucket(id,name,job,manager,hiredate,salary,deptno) SELECT id,name,job,manager,hiredate,salary,deptno FROM t_employee where deptno=10;
临时表 创建临时表
临时表仅对当前session(一个Connection有效)可见,临时表的数据将存储在用户的暂存目录中,并在会话结束后删除。如果临时表与永久表表名相同,则对该表名的任何引用都将解析为临时表,而不是永久表。临时表还具有以下两个限制:不支持分区列;不支持创建索引.
CREATE TEMPORARY TABLE if not exists …
创建:create TEMPORARY table t_tem as select * from t_employee where deptno=10;
select * from t_tem;
复制表结构
CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS t_employee_copy2 LIKE t_employee_bucket location ‘/hive/t_empolyee_copy2’
可以复制临时表的结构
create temporary external table newtabelname like tablename;
1.2、修改表
重命名:alter table oldneame rename to newname;
修改列名、属性:alter table t_user change id(修改的列名) id(修改后的列名) string(修改后的属性)修改顺序、alter table t_user change id id bigint after name;(把id放name后面,可以一起修改列名属性)
添加列名、属性:alter table t_user add columns (deptno int)(在末尾添加的列和属性);
清空表:truncate table t_employee_copy; -- 必须内部表
截断分区:truncate table t_employee partition(deptno=20);-- 要求被截断的表必须是管理表(内部表)
展示分区:show partitions t_employee;
查看建表语句:show create table t_employee;
如果删除的是管理表,系统在删除表的时候会删除表里数据,如果用户开启HDFS的回收站功能,数据实际上移动到垃圾回收站,所以如果用户想跳过垃圾hhhh回收站drop table 表名字 PURGE;
附加:
core-site.xml
<property>
<name>fs.trash.interval</name>
<value>5</value> //最多容忍数据在垃圾回收站保留5分钟
</property>
2、DML操作
2、1加载文件数据到表中
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
load data local inpath '/root/t_employee' overwrite into table t_employee partition(deptno='10')
2.2、查询结果插入表中
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select * FROM from_statement; --覆盖
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]
select * FROM from_statement;--追加
json加载:add jar /root/apache-hive-1.2.2-bin/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.2.jar
将查询结果插入多个表
FROM from_statement
INSERT OVERWRITE TABLE tablename1
[PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;
插入动态分区
0: jdbc:hive2://CentOS:10000> set hive.exec.dynamic.partition.mode=nonstrict; #开启动态分区
No rows affected (0.016 seconds)
0: jdbc:hive2://CentOS:10000> INSERT OVERWRITE TABLE t_employee PARTITION (deptno) SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM t_emp_json ;
结果写出文件系统
insert overwrite disectory '/2020年1月10日' row format delimited fields terminated by '--'STORED AS textfile SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM t_emp_json ;
2.1、order by 和sort by的区别
可以使用ORDER BY或者Sort BY对查询结果进行排序,排序字段可以是整型也可以是字符串:如果是整型,则按照大小排序;如果是字符串,则按照字典序排序。ORDER BY 和 SORT BY 的区别如下:使用ORDER BY时会有一个Reducer对全部查询结果进行排序,可以保证数据的全局有序性;使用SORT BY时只会在每个Reducer中进行排序,这可以保证每个Reducer的输出数据是有序的,但不能保证全局有序。由于ORDER BY的时间可能很长,如果你设置了严格模式(hive.mapred.mode = strict),则其后面必须再跟一个limit子句。
group by 分组查询
set hive.map.aggr=true;
hive.map.aggr控制程序如何进行聚合。默认值为false。如果设置为true,Hive会在map阶段就执行一次聚合。这可以提高聚合效率,但需要消耗更多内存。
2.2、DISTRIBUTE BY
默认情况下,MapReduce程序会对Map输出结果的Key值进行散列,并均匀分发到所有Reducer上。如果想要把具有相同Key值的数据分发到同一个Reducer进行处理,这就需要使用DISTRIBUTE BY字句。需要注意的是,DISTRIBUTE BY虽然能保证具有相同Key值的数据分发到同一个Reducer,但是不能保证数据在Reducer上是有序的。
需要根据key的种类设置1种类数量;
表join查询:
Hive支持内连接,外连接,左外连接,右外连接,笛卡尔连接,这和传统数据库中的概念是一致的。需要特别强调:JOIN语句的关联条件必须用ON指定,不能用WHERE指定,否则就会先做笛卡尔积,再过滤,这会导致你得不到预期的结果。
去重查询:distinct
select distinct(job) from t_employee;查询工作种类
2.3、CLUSTER BY
CLUSTER BY
如果SORT BY和DISTRIBUTE BY指定的是相同字段,且SORT BY排序规则是ASC,此时可以使用CLUSTER BY进行替换。
2.4、LEFT SEMI JOIN
EFT SEMI JOIN (左半连接)是 IN/EXISTS 子查询的一种更高效的实现。
- JOIN 子句中右边的表只能在 ON 子句中设置过滤条件;
- 查询结果只包含左边表的数据,所以只能SELECT左表中的列。
2.3、开窗函数
over()开窗函数,其括号内部主要有两种形式,固定搭配,不能更改:
over(distribute by…sort by…) 和 over(partition by…order by…)
两种开窗方式区别
patition by是按照一个一个reduce去处理数据的,所以要使用全局排序order by
distribute by是按照多个reduce去处理数据的,所以对应的排序是局部排序sort
案例:deptno是部门编号 按部门分组(reduce和组的个数设置一致)工资(sal)降序 排序
select empno,ename,sal,deptno,rank() over(partition by deptno order by sal desc) from t_employee;
select empno,ename,sal,deptno,rank() over(distribute by deptno sort by sal desc) as rank from t_employee;
cube 函数多个任意维度的查询
案例:select e.deptno,e.job,avg(e.sal) avg,max(e.sal) max,min(e.sal) min from t_employee e group by e.deptno,e.job with cube;
2.4、Hive数据倾斜
常见面试题型:原因和解决办法!!
数据倾斜是进行大数据计算时最经常遇到的问题之一。当我们在执行HiveQL或者运行MapReduce作业时候,如果遇到一直卡在map100%,reduce99%一般就是遇到了数据倾斜的问题。数据倾斜其实是进行分布式计算的时候,某些节点的计算能力比较强或者需要计算的数据比较少,早早执行完了,某些节点计算的能力较差或者由于此节点需要计算的数据比较多,导致出现其他节点的reduce阶段任务执行完成,但是这种节点的数据处理任务还没有执行完成。
group by,我使用Hive对数据做一些类型统计的时候遇到过某种类型的数据量特别多,而其他类型数据的数据量特别少。当按照类型进行group by的时候,会将相同的group by字段的reduce任务需要的数据拉取到同一个节点进行聚合,而当其中每一组的数据量过大时,会出现其他组的计算已经完成而这里还没计算完成,其他节点的一直等待这个节点的任务执行完成,所以会看到一直map 100% reduce 99%的情况。
解决方法:
set hive.map.aggr=true
set hive.groupby.skewindata=true
原理:
hive.map.aggr=true 这个配置项代表是否在map端进行聚合hive.groupby.skwindata=true 当选项设定为 true,生成的查询计划会有两个 MR Job。第一个 MR Job 中,Map 的输出结果集合会随机分布到 Reduce 中,每个 Reduce 做部分聚合操作,并输出结果,这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中,从而达到负载均衡的目的;第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中(这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中),最后完成最终的聚合操作。
Hive On Hbase
create external table t_employee_hbase(empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES("hbase.columns.mapping" = ":key,cf1:name,cf1:job,cf1:mgr,cf1:hiredate,cf1:sal,cf1:comm,cf1:deptno")
TBLPROPERTIES("hbase.table.name" = "baizhi:t_employee");
0: jdbc:hive2://CentOS:10000> select empno,ename,sal,deptno from t_employee_hbase;
+--------+-----------+-------+---------+--+
| empno | ename | sal | deptno |
+--------+-----------+-------+---------+--+
| 1 | zhangsan | NULL | 10 |
| 2 | lisi | 5000 | 20 |
+--------+-----------+-------+---------+--+
2 rows selected (0.447 seconds)
0: jdbc:hive2://CentOS:10000> select deptno,count(*) from t_employee_hbase group by deptno;
以上操作都是读操作,因此可以正常运行,当用户执行写操作的时候,将数据写入Hbase
0: jdbc:hive2://CentOS:10000> insert overwrite table t_employee_hbase select empno,ename,job,mgr,hiredate,sal,comm,deptno from t_employee;
系统会抛出一下错误。
ERROR : Ended Job = job_1578881006622_0016 with errors
Error: Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask (state=08S01,code=2)
0: jdbc:hive2://CentOS:10000>
需要替换hive-hbase-handler-1.2.2.jar,问题参考: https://blog.csdn.net/weixin_38231448/article/details/103935613
UDF&UDTF
UDF函数其实就是一个简单的函数,执行过程就是在Hive转换成MapReduce程序后,执行java方法,类似于像MapReduce执行过程中加入一个插件,方便扩展。目前Hive除去一些内置的函数以外,还提供了一些内置的函数的扩扎接口:
UDF:针对单行数据操作,需要继承UDF
UDTF:操作一个数据行,产生多个数据行或者是多个列,需要用户继承GenericUDTF
UDAF:操作多个数据行,产生一个数据行,主要用以聚合统计,需要继承AbstractGenericUDAFResolver
目前由于Hive中已经提供了强大的聚合函数,本篇文章就UDF和UDTF实现给出以下两个案例:
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.9.2</version>
</dependency>
UDF
package com.jiangzz;
import org.apache.hadoop.hive.ql.exec.UDF;
public class DeptUDF extends UDF {
public String evaluate(String value){
return value+"部门";
}
}
这里的evaluate方法的参数和返回值需要用户根据自己的需求定义
UDTF
package com.jiangzz;
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.Arrays;
import java.util.List;
public class UserUDTF extends GenericUDTF {
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
List<ObjectInspector> objectInspectors=new ArrayList<ObjectInspector>();
objectInspectors.add(PrimitiveObjectInspectorFactory.javaIntObjectInspector);
objectInspectors.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
objectInspectors.add(PrimitiveObjectInspectorFactory.javaBooleanObjectInspector);
//返回的类型参数
return ObjectInspectorFactory.getStandardStructObjectInspector(Arrays.asList("id","name","sex"),objectInspectors);
}
public void process(Object[] args) throws HiveException {
String value = args[0].toString();
String sep= args[1].toString();
String[] tokens = value.split(sep);
forward(Arrays.asList(Integer.parseInt(tokens[0]),tokens[1],Boolean.valueOf(tokens[2])));
}
public void close() throws HiveException {
}
}
代码打包
将代码进行package打包 hive-function-1.0-SNAPSHOT.jar创建以下function.sql文件进行测试
create temporary function dept_fun as 'com.jiangzz.DeptUDF';
create temporary function user_fun as 'com.jiangzz.UserUDTF';
select dept_fun(20);
select user_fun('1,zhangsan,true',',');
[root@CentOS ~]# hive -f function.sql --auxpath /root/hive-function-1.0-SNAPSHOT.jar
Hive安装
由于hive需要存储一些一些建表的元数据信息,因此在生产环境下安装Hive需要事先安装MySQL数据库服务(注意:MySQL数据库的编码必须是Latin1编码)。其次Hive是构建在Hadoop存储和计算之上的工具,因此还需要在配置和安装hive之前保证Hadoop的hdfs和MapReduce正常运行。最后Hive的安装主机上必须配置HADOOP_HOME环境变量,这样Hive可以通过该环境变量感知用户的Hadoop计算集群的位置和规模。
Hive配置
1、解压hive安装包,解压到/usr目录下
[root@CentOS ~]# tar -zxf apache-hive-1.2.2-bin.tar.gz -C /usr/
[root@CentOS ~]# cd /usr/apache-hive-1.2.2-bin/
2、创建conf/hive-site.xml,配置以下内容
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://CentOS:3306/hive?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>root</value>
</property>
3、将MySQL的驱动jar拷贝到lib目录下
MacBookPro:~ admin$ scp
.m2/repository/mysql/mysql-connector-java/5.1.48/mysql-connector-java-5.1.48.jar
root@CentOS:/usr/apache-hive-1.2.2-bin/lib root@centos’s password:
mysql-connector-java-5.1.48.jar 100% 983KB 103.5MB/s
00:00
4、配置HIVE_HOME环境变量
HADOOP_HOME=/usr/hadoop-2.9.2 HIVE_HOME=/usr/apache-hive-1.2.2-bin
JAVA_HOME=/usr/java/latest
PATH= P A T H : PATH: PATH:JAVA_HOME/bin: H A D O O P H O M E / b i n : HADOOP_HOME/bin: HADOOPHOME/bin:HADOOP_HOME/sbin:$HIVE_HOME/bin
CLASSPATH=. export JAVA_HOME export PATH export HADOOP_HOME export
CLASSPATH export HIVE_HOME
5、启动Hive
单用户模式
[root@CentOS ~]# hive
Logging initialized using configuration in
jar:file:/usr/apache-hive-1.2.2-bin/lib/hive-common-1.2.2.jar!/hive-log4j.properties
hive> show tables; OK Time taken: 0.726 seconds hive>
多用户模式
[root@CentOS apache-hive-1.2.2-bin]# ./bin/hiveserver2 >/dev/null
2>&1 & [3] 26279[root@CentOS apache-hive-1.2.2-bin]# ./bin/beeline -u
jdbc:hive2://CentOS:10000 -n root Connecting to
jdbc:hive2://CentOS:10000 Connected to: Apache Hive (version 1.2.2)
Driver: Hive JDBC (version 1.2.2) Transaction isolation:
TRANSACTION_REPEATABLE_READ Beeline version 1.2.2 by Apache Hive 0:
jdbc:hive2://CentOS:10000>