hive学习笔记_hive是基于hadoop的一个数据仓库工具,用来进行数据提取、转化、加载,这是一种可以-CSDN博客

本文链接：https://blog.csdn.net/weixin_45764675/article/details/103967598

一：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 :$ JAVA_HOME/bin: $HADOOP_HOME/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 :$ JAVA_HOME/bin: $HADOOP_HOME/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>