Hive学习笔记

一、简介

Hive 是一个构建在 Hadoop 之上的数据仓库，它可以将结构化的数据文件映射成表，并提供类 SQL 查询功能，用于查询的 SQL 语句会被转化为 MapReduce 作业，然后提交到 Hadoop 上运行。

特点：

1. 简单、容易上手 (提供了类似 sql 的查询语言 hql)，使得精通 sql 但是不了解 Java 编程的人也能很好地进行大数据分析；
2. 灵活性高，可以自定义用户函数 (UDF) 和存储格式；
3. 为超大的数据集设计的计算和存储能力，集群扩展容易;
4. 统一的元数据管理，可与 presto／impala／sparksql 等共享数据；
5. 执行延迟高，不适合做数据的实时处理，但适合做海量数据的离线处理；
6. 不是关系型数据库。

二、安装配置

1. 下载解压hive包（这里我是3的版本）

   Code

   $tar -zxvf apache-hive-3.1.2-bin.tar.gz
   $mv apache-hive-3.1.2-bin /soft/
   $ln -s apache-hive-3.1.2-bin hive

2. 配置环境变量

   Code

   # Hive
   export HIVE_HOME=/soft/hive
   export PATH=$PATH:$HIVE_HOME/bin
   # 配置生效
   $source /etc/profile

3. 复制mysql驱动程序到hive的lib目录下

   Code

   $cp mysql-connector-java-5.1.38.jar /soft/hive/lib/

4. 配置hive-env.sh

   在hive下的conf目录下，找到hive-env.sh.template，并复制一份重命名为hive-env.sh

   Code

   $cp hive-env.sh.template hive-env.sh

   编辑hive-env.sh，修改配置：

   Code

   # Hadoop安装目录
   HADOOP_HOME=/soft/hadoop
   # HIVE配置文件目录
   export HIVE_CONF_DIR=/soft/hive/conf
   # HIVE库文件目录
   export HIVE_AUX_JARS_PATH=/soft/hive/lib

5. 配置hive-site.xml

   在hive下的conf目录下，找到hive-default.xml.template，并复制一份重命名为hive-site.xml

   Code

   $cp hive-default.xml.template hive-site.xml

   然后对如下内容进行修改：

   xml

   <!-- 数据库的账户、密码 -->
   <property>
       <name>javax.jdo.option.ConnectionUserName</name>
       <value>root</value>
       <description>Username to use against metastore database</description>
   </property>
   <property>
       <name>javax.jdo.option.ConnectionPassword</name>
       <value>root</value>
       <description>password to use against metastore database</description>
   </property>
   <!-- 连接的MYSQL数据库，IP是我自己的本机WIN10。记得去创一个hive的数据库 -->
   <property>
       <name>javax.jdo.option.ConnectionURL</name>
       <value>jdbc:mysql://192.168.203.1:3306/hive?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=GMT</value>
   </property>
   <property>
       <name>javax.jdo.option.ConnectionDriverName</name>
       <value>com.mysql.jdbc.Driver</value>
       <description>Driver class name for a JDBC metastore</description>
   </property>
   <!-- 设置目录 -->
   <property>
       <name>hive.exec.local.scratchdir</name>
       <value>/home/wbw/hive</value>
       <description>Local scratch space for Hive jobs</description>
   </property>
   <!-- 设置下载资源目录 -->
   <property>
       <name>hive.downloaded.resources.dir</name>
       <value>/home/wbw/hive/downloads</value>
       <description>Temporary local directory for added resources in the remote file system.</description>
   </property>
   <!-- 设置查询日志目录 -->
   <property>
       <name>hive.querylog.location</name>
       <value>/home/wbw/hive/querylog</value>
       <description>Location of Hive run time structured log file</description>
   </property>
   <!-- 日志 -->
   <property>
       <name>hive.server2.logging.operation.log.location</name>
       <value>/home/wbw/hive/server2_log</value>
       <description>Top level directory where operation logs are stored if logging functionality is enabled</description>
   </property>
   <!-- 提示信息 -->
   <property>
       <name>hive.cli.print.header</name>
       <value>true</value>
       <description>Whether to print the names of the columns in query output.</description>
   </property>
   <property>
       <name>hive.cli.print.current.db</name>
       <value>true</value>
       <description>Whether to include the current database in the Hive prompt.</description>
   </property>

6. 创建hive数据库

   sql

   create database hive;

7. 初始化hive的元数据(表结构)到mysql中

   Code

   $schematool -dbType mysql -initSchema

   这时候报错：

   

   发现是hive-site.xml第3215行错误，定位到错误的地方：

   Code

   vi hive-site.xml +3215

   发现是注释 description 中的内容，有一个for&#8的东西，将后面的&…;删除即可。然后重新执行初始化命令。

   最后查看MYSQL发现多了很多表。

8. 启动Hive(要先启动HDFS)

   Code

   $hive

9. 注意事项（别人写的，如果有问题可以试试）

   • hive-site.xml中 mysql的驱动名称为com.mysql.cj.jdbc.Driver

   • xml文档中javax.jdo.option.ConnectionURL中&要用&替代,一定要指定字符集,时区

     Code

     jdbc:mysql://127.0.0.1:3306/hive?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT

   • 我已经提前对数据库的root用户进行授权

三、HiveQL命令

在hive中的命令和MySQL基本上是一致的。有一点要注意， 数据是存在HDFS上的，HIVE只是用来做一个映射，他的元数据是放在MySQL上的（自带的不好用只能单线程不用）。

3.1 基本命令

查看版本

Code

$hive --version

查看帮助

Code

$hive --help

启动HIVE

Code

$hive

相当于开启了一个服务hive –service cli。但是不能并行。

退出HIVE

Code

hive >exit;

3.2 数据库相关

创建数据库

Code

hive (default)> create database mydb;

切换数据库

Code

hive (default)> use mydb;

查看数据库

Code

hive (mydb)> show databases;

删除数据库

Code

hive (default)> drop database mydb;

3.3 表相关

在hive中表分为两种：

• 托管表：删除表时，数据也删除了。（默认，即将数据也托管给Hive）
• 外部表：删除表时，数据不删除。(只是维护一个目录结构)

查看库中所有表

Code

$hive>show tables in mydb;

创建表

Code

$hive>CREATE [EXTERNAL] TABLE IF NOT EXISTS t1(id int,name string,age int) [COMMENT 'xx'] ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' lines terminated by '\n' STORED AS TEXTFILE;

EXTERNAL（可选）：外部表

COMMENT：注释

ROW FORMAT DELIMITED FIELDS TERMINATED BY：

lines terminated by：

STORED AS TEXTFILE：文本文件存储

通过hive (mydb)> dfs -lsr /; 即在hive中执行查看HDFS命令，执行速度会大大提高。同时，也可以看到在hive中创建的内容在hdfs上的情况。

删除表

Code

$hive>drop table t;

添加数据

1. 使用insert（不推荐）

   Code

   hive (mydb)> insert into t1 values(1,'tom',10);

   

   insert操作走MR程序，所以过程非常慢，实际开发中一般不会进行该操作。可能会促发内存不足异常，解决方法在下面。

2. 使用load

   • 从本地上传文件

     

     先在本地创建一个文本文件（这里不小心多了两行空行，记录有两条NULL）。

     Code

     hive (mydb)> load data local inpath '/home/wbw/tmp/data1.txt' into table t1;

   • 从HDFS移动文件

     先将刚创建的文本文件上传到hdfs。

     Code

     hive (mydb)> load data inpath '/user/wbw/data1.txt' [overwrite] into table t1;

     overwrite（可选）：覆盖

查看HDFS文件：

可以看到文件并不是放在一起的，而是没进行一次操作，都会新增一个文件。

查看表结构

Code

hive (mydb)> desc [formatted] t1;

不带formatted（简略版）：

带formatted（详细版）：

查看表数据

• select count(*) from t1; （走MR）
• select id,name from t1; （不走MR）
• select * from t1 order by id desc; （走MR）

说明只要有设计到聚合相关的，都会走MR程序。

复制表

• 携带数据和表结构

  Code

  hive (mydb)> create table t2 as select * from t1;

  

• 不带数据，仅表结构

  Code

  hive (mydb)> create table t3 like t1;

  

分区表

1. 创建分区表

   Code

   hive (mydb)> create table t4(id int,name string,age int) PARTITIONED BY (Year INT,Month INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

   PARTITIONED BY (Year INT,Month INT)：分区字段

   ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’：分隔符

2. 添加分区，创建目录

   Code

   hive (mydb)> alter table t4 add partition(year=2020,month=12);

   

   可以看到，分区的目录结构。其实就是在HDFS上多了几层条件目录。

3. 显示表的分区信息

   Code

   hive (mydb)> show partitions t4;

4. 加载数据到分区

   Code

   hive (mydb)> load data local inpath '/home/wbw/tmp/data1.txt' into table t4 partition(year=2020,month=11);

   

   

   这时候查看表内容，发现多了两个字段？其实是目录，没有字段。。。但是可以用where条件分区查看。

5. 删除分区

   Code

   hive (mydb)> ALTER TABLE t4 DROP IF EXISTS PARTITION (year=2014, month=11);

   

   可以看到只剩下一个month=12的分区了

6. 动态分区

   动态分区模式:

   • strict：严格模式，插入时至少指定一个静态分区

   • nonstrict：非严格模式，可以不指定静态分区。

   Code

   // 1.设置非严格模式
   $hive>set hive.exec.dynamic.partition.mode=nonstrict;
   // 2.向分区表添加数据（因为Hive中一般不进行insert操作，如果要添加都是Load）
   $hive>INSERT [OVERWRITE] TABLE <employees> PARTITION (<country>, <state>) SELECT ..., se.cnty, se.st FROM staged_employees se WHERE se.cnty = 'US';
   解析：
   前提是有一个分区表，里面有country和state为分区字段。其次，select查询结果里面有该分区字段。
   将后面select查询结果，添加到employees表中，以country和state为分区字段。

桶表

即hash算法，可以获得更快的查询速度。

1. 创建桶表

   Code

   hive (mydb)> create table t5(id int,name string,age int) CLUSTERED BY (id) INTO 3 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

   CLUSTERED BY (id) INTO 3 BUCKETS：根据id分3个桶

Q:如何确定桶的数量？

A:评估数据量，保证每个桶的数据量block的2倍大小。

2. 添加数据

   • 查询插入

     Code

     hive (mydb)> insert into t5 select id,name,age from t4;

     

     

   • 加载数据（为什么我是走MR的？）

     Code

     hive (mydb)> load data inpath '/user/wbw/data1.txt' into table t5;

     

     

     可以看到每次添加数据都会产生一个新文件。

导出表

Code

hive (mydb)> EXPORT TABLE t5 TO '/user/wbw/export';

3.4 查询操作

order全排序

Code

hive (mydb)> select * from t5 order by age;

运行结果：

Code

OK
t5.id	t5.name	t5.age
1	tom	11
1	tom	11
2	tom	12
2	tom	12
3	tom	13
3	tom	13
4	tom	14
4	tom	14
5	tom	15
5	tom	15

sort排序

map端排序，本地有序。

Code

hive (mydb)> select * from t5 sort by age;

结果同上order排序

DISTRIBUTE BY分区

类似于mysql的group by,进行分区操作。注意顺序.（先分区再排序）

Code

hive (mydb)> select id,age from t5 distribute by age sort by age desc;
备注：
cluster by ===>  distribute by cid sort by age
上面的语句可以等价的

运行结果：

Code

OK
id	age
5	15
5	15
4	14
4	14
3	13
3	13
2	12
2	12
1	11
1	11

3.5 函数

Tab函数列表

Code

$hive>(Tab键)

concat连接

Code

hive (mydb)> select concat('tom',100);
OK
_c0
tom100

数据库、用户信息

Code

hive (mydb)> select current_database(),current_user() ;
OK
_c0	_c1
mydb	wbw

聚合函数

Code

$hive>select cid,count(*) c ,max(price)  from orders group by cid having c > 1 ;

3.6 UDF用户自定义函数

1. 导入pom依赖

   xml

   <dependency>
       <groupId>org.apache.hive</groupId>
       <artifactId>hive-exec</artifactId>
       <version>3.1.2</version>
   </dependency>

2. 创建类，继承UDF

   java

   import org.apache.hadoop.hive.ql.exec.Description;
   import org.apache.hadoop.hive.ql.exec.UDF;
   
   /**
    * 自定义hive函数
    */
   @Description(name = "myadd",
                value = "myadd(int a , int b) ==> return a + b ",
                extended = "Example:\n"
                + " myadd(1,1) ==> 2 \n"
                + " myadd(1,2,3) ==> 6;")
   public class AddUDF extends UDF {
   
       public int evaluate(int a ,int b) {
           return a + b ;
       }
   
       public int evaluate(int a ,int b , int c) {
           return a + b + c;
       }
   }

3. 打成jar包

4. 添加jar包到hive的类路径

   Code

   $>cp /xxx/HiveDemo.jar /soft/hive/lib

5. 重进入hive

6. 创建临时函数

   Code

   CREATE TEMPORARY FUNCTION myadd AS 'XXX.XXX.XXX.XXX.AddUDF';

7. 在查询中使用自定义函数

   Code

   $hive>select myadd(1,2);

四、JDBC访问Hive

4.1 远程jdbc方式连接

1. 启动hiveserver2服务器，监听端口10000

   Code

   $>hive --service hiveserver2 &

2. 通过beeline命令行连接到hiveserver2

   Code

   # 进入beeline命令行(于hive --service beeline)
   $>beeline		
   # 查看帮助
   $beeline>!help	
   # 退出
   $beeline>!quit			
   # 连接到hive数据
   $beeline>!connect jdbc:hive2://localhost:10000/mydb2
   $beeline>show databases ;
   $beeline>use mydb2 ;
   # 显式表
   $beeline>show tables;

4.2 Hive-jdbc驱动程序

1. 添加依赖

   xml

   <dependency>
       <groupId>org.apache.hive</groupId>
       <artifactId>hive-jdbc</artifactId>
       <version>3.1.2</version>
   </dependency>

2. 编写JAVA

   java

   /**
    * App class
    * Hive-JDBC连接操作
    *
    * @author BoWenWang
    * @date 2020/3/3 15:02
    */
   public class App {
       public static void main(String[] args) throws ClassNotFoundException, SQLException {
           Class.forName("org.apache.hive.jdbc.HiveDriver");
           Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.174.201:10000/mydb", "wbw", "Bow1024");
           Statement statement = conn.createStatement();
           ResultSet rs = statement.executeQuery("select id,name,age from t5");
           while (rs.next()) {
               System.out.println(rs.getInt(1) + "," + rs.getString(2));;
           }
           rs.close();
           statement.close();
           conn.close();
       }
   }

   打印结果：

   Code

   3,tom
   3,tom
   4,tom
   1,tom
   4,tom
   1,tom
   5,tom
   2,tom
   5,tom
   2,tom

五、事务

使用事务要求：

1. 所有事务自动提交。
2. 只支持orc格式。
3. 使用bucket表。
   4. 配置hive参数，使其支持事务。

5.1 配置属性

Code

$hive>SET hive.support.concurrency = true;				
$hive>SET hive.enforce.bucketing = true;					
$hive>SET hive.exec.dynamic.partition.mode = nonstrict;	
$hive>SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
$hive>SET hive.compactor.initiator.on = true;
$hive>SET hive.compactor.worker.threads = 1;

5.2 使用事务性操作

Code

$>CREATE TABLE tx(id int,name string,age int) CLUSTERED BY (id) INTO 3 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as orc TBLPROPERTIES ('transactional'='true');

解析:
之前都一样，后面要加上下面的内容！
stored as orc 和 TBLPROPERTIES ('transactional'='true');

六、视图

和MySQL一样，视图只是表的关系映射，实际上是不存在的表。

6.1 创建视图

Code

$hive>create view v1 as select a.id aid,a.name ,b.id bid , b.order from customers a left outer join default.tt b on a.id = b.cid ;

6.2 查看视图

Code

$hive>select * from v1 ;

七、调优

7.1 explain

使用explain查看查询计划

Code

hive>explain [extended] select count(*) from customers ;
hive>explain select t.name , count(*) from (select a.name ,b.id,b.orderno from customers a ,orders b where a.id = b.cid) t group by t.name ;

extended：更详细

7.2 limit

设置limit优化测，避免全部查询

Code

hive>set hive.limit.optimize.enable=true

7.3 严格模式

Code

$hive>set hive.mapred.mode=strict

开启后的限制：

1. 分区表必须指定分区进行查询
2. order by时必须使用limit子句。
3. 不允许笛卡尔积.

7.4 设置MR的数量

Code

// 设置reducetask的字节数。
$hive>set hive.exec.reducers.bytes.per.reducer=750000000	
// 设置reduce task的最大任务数
$hive>set hive.exec.reducers.max=0					
// 设置reducetask个数。
$hive>set mapreduce.job.reduces=0

7.5 JVM重用

Code

$hive>set mapreduce.job.jvm.numtasks=1		//-1没有限制，使用大量小文件。

7.6 数据倾斜

Code

$hive>SET hive.optimize.skewjoin=true;
$hive>SET hive.skewjoin.key=100000;
$hive>SET hive.groupby.skewindata=true;


CREATE TABLE mydb.doc(line string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

select t.word,count(*) from (select explode(split(line,' ')) word from doc ) t group by t.word ;

八、问题汇总

8.1 虚拟内存不足

https://blog.csdn.net/weixin_33725722/article/details/92442061

当进行计算的操作时候，可能发生内存不足的情况，如上面的表插入insert时候。

8.2 JDBC-Hive连接不上

Error: Could not open client transport with JDBC Uri: jdbc:hive2://localhost:10000/mydb;: Failed to open new session: java.lang.RuntimeException: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.authorize.AuthorizationException): User: wbw is not allowed to impersonate root (state=08S01,code=0)

解决方法：

https://blog.csdn.net/finejade1027/article/details/91637283

User: wbw is not allowed to impersonate root .

hadoop.proxyuser.root.hosts配置项名称中root部分为报错User:* 中的用户名部分，根据需要修改。如这里的wbw，那就要修改成wbw。所有节点都要改！然后重新连接后，用户名输入wbw

xml

<!--在core-site.xml添加如下内容后，重启Hadoop  -->
<property>
  <name>hadoop.proxyuser.wbw.hosts</name>
  <value>*</value>
 </property>
 <property>
  <name>hadoop.proxyuser.wbw.groups</name>
  <value>*</value>
</property>

九、注意事项

如果配置hadoop HA之前，搭建了Hive的话，再HA之后，需要调整路径信息。

主要是修改mysql中的dbs,tbls等相关表。