hive

发表于 | 分类于

数据分析引擎

一、Hadoop中

（1）Hive：支持SQL
（2）Pig：支持PigLation

二、Spark中__

（*）Spark SQL：类似Hive，支持SQL、DSL

三、另一个：Impala

什么是Hive

一、Hive是基于HDFS之上的一个数据仓库，有助于使用SQL

  • Hive —-> HDFS
  • 表 ——> 目录
  • 数据 —-> 文件
  • 分区 —-> 目录
  • 桶 ——> 文件

    二、Hive是基于Hadoop之上的一个数据分析引擎

  • Hive 2.X 以前：SQL —-> Hive —-> MapReduce
  • Hive 2.X 以后：推荐使用Spark作为SQL的执行引擎（只针对Hadoop 3.X以前）（《Hive on Spark文档》）

Hive的体系架构

  • 一、CLI（命令行）：直接由Hive Dirver翻译
  • 二、JDBC（标准接口）：1.X由Thrift Server，2.X由Hive Server翻译为SQL语句交由Hive Dirver，端口号都为10000
  • 三、HWI（Hive Web Interface）：只在Hive 2.2前提供HWI网页工具，推荐使用HUE，由Hive Dirver翻译
  • *、在Hive的体系架构中还需要有关系型数据库用来存储Hive元信息（推荐使用MySQL）

Hive优缺点

  1. 优点
  • 1）操作接口采用了sql，简化开发，减少学习成本
  • 2）避免手写mapreduce程序
  • 3）hive执行延迟较高，适用场景大多用在实时性要求不强的场景
  • 4）处理大数据有优势
  • 5）支持自定义函数
  1. 缺点
  • 1）hive的sql表达能力有限（hql），并不能解决所有大数据场景
  • 2）hive效率低（自动生成mapreduce作业，但力度比较粗，调优困难）
  • 3）

安装和配置Hive

准备工作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
1、解压  tar -zxvf apache-hive-2.3.0-bin.tar.gz -C ~/training/
2、设置环境变量 vi  ~/.bash_profile
  HIVE_HOME=/root/training/hive
  export HIVE_HOME
 
  PATH=$HIVE_HOME/bin:$PATH
  export PATH
3.安装配置MySQL数据库
  在虚拟机上安装MySQL：
    yum remove mysql-libs 
    rpm -ivh mysql-community-common-5.7.19-1.el7.x86_64.rpm
    rpm -ivh mysql-community-libs-5.7.19-1.el7.x86_64.rpm
    rpm -ivh mysql-community-client-5.7.19-1.el7.x86_64.rpm
    rpm -ivh mysql-community-server-5.7.19-1.el7.x86_64.rpm
    rpm -ivh mysql-community-devel-5.7.19-1.el7.x86_64.rpm  （可选，但还是装上，后面装HUE的时候会用到。）
                   
  启动MySQL：service mysqld start
  或者：systemctl start mysqld.service
 
  查看root用户的密码：cat /var/log/mysqld.log | grep password
  登录后修改密码：alter user 'root'@'localhost' identified by 'Welcome_1';
 
  MySQL数据库的配置：
    创建一个新的数据库：create database hive;
    创建一个新的用户：
      create user 'hiveowner'@'%' identified by 'Welcome_1';

      给该用户授权
        grant all on hive.* TO 'hiveowner'@'%'; 
        grant all on hive.* TO 'hiveowner'@'localhost' identified by 'Welcome_1';          
         
        免费工具：http://www.mysqlfront.de/

嵌入模式：不需要MySQL，使用Hive自带的Derby数据库存储Hive的元信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
特点：
  （1）使用自带的Derby
  （2）只支持一个连接
  （3）用于开发和测试

修改配置文件hive-env.sh：
  HADOOP_HOME=path
  export HIVE_CONF_DIR=/root/hd/hive/conf

在HDFS集群上创建文件夹：
  hdfs dfs -mkdir /tmp
  hdfs dfs -mkdir /user/hive/warehouse/

创建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.ConnectionURL</name>
      <value>jdbc:derby:;databaseName=metastore_db;create=true</value>
    </property>
              
    <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>org.apache.derby.jdbc.EmbeddedDriver</value>
    </property>   
             
    <property>
      <name>hive.metastore.local</name>
      <value>true</value>
    </property>   
 
    <property>
      <name>hive.metastore.warehouse.dir</name>
      <value>file:///root/training/apache-hive-2.3.0-bin/warehouse</value>
    </property>           
  </configuration>
 
初始化MetaStore：
    schematool -dbType derby -initSchema
           
日志：
    Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.

本地模式、远程模式：都需要MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
修改配置文件hive-env.sh：
  HADOOP_HOME=path
  export HIVE_CONF_DIR=/root/hd/hive/conf

在HDFS集群上创建文件夹：
  hdfs dfs -mkdir /tmp
  hdfs dfs -mkdir /user/hive/warehouse/

创建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.ConnectionURL</name>
      <value>jdbc:mysql://localhost:3306/hive?useSSL=false</value>
    </property>               
 
    <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>com.mysql.jdbc.Driver</value>
    </property>   
 
    <property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>hiveowner</value>
    </property>   
 
    <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>tiger</value>
    </property>                   
           
  </configuration>

初始化MetaStore：
  schematool -dbType mysql -initSchema
  
* 启动hive前先启动hadoop集群和yarn

Hive的架构

  1. 提供了一系列接口：hive shell、jdbc/odbc、webui
  2. hive架构：hive（Meta元数据（默认derby数据库，可以自定义）、Client（cli、idbc）、SQL Parser解析器、Physica编译器、Query优化器、Execution执行器、MR程序）
  3. 客户端输入sql后，首先调用到元数据
  4. 通过SQL Parser解析器找到对应的MR程序
  5. 经过编译器编译代码
  6. 通过优化其选择SQL需不需要经过计算
  7. 通过执行器执行MR程序

*Hive的表类型和数据类型

表类型

  1. 一、内部表（管理表）：类似MySQL、Oracle中的表
  • 删除管理表时，hive会自动删除管理表的数据，不擅长做数据共享
  1. 二、外部表
  • 删除外部表时，hive不认为这张表拥有这份数据，并不会删除数据，适合做数据共享
  • 外部表已被删除时，重新创建一个表明、表结构都相同的表，hive将自动关联到外部表留下的数据
  1. 三、分区表：提高性能
  • （*）补充：如何提高性能？（SQL执行计划）
  1. 四、桶表：类似Hash分区
  2. 五、视图：View

    数据类型

    | Java数据类型 | hive数据类型 | 数据长度 |
    | byte | TINYINT | 1byte |
    | short | SMALINT | 2byte |
    | int | INT | 4byte |
    | long | BIGINT | 8byte |
    | float | FLOAT | 单精度浮点数 |
    | double | DOUBLE | 双精度浮点数 |
    | String | STRING | 字符 |
    | | TIMESTAMP | 时间类型 |
    | | BINARY | 字节数组 |

基础HQL

DBL数据定义

  1. 库操作
  • alter database hive_db set dbproperties(‘dataname’=’zfhzxg的数据库’);：添加数据库描述信息
  • desc database hive_db;：查看数据库结构
  • desc database extended hive_db;：查看数据库拓展性
  • drop database if exists hive_db;：检测该库存在时，删除该库
  1. 管理表操作
  • show databases [link ‘db*’];：显示数据库，可选通配符筛选
  • create database hive_db location ‘path’;：在指定路径下创建数据库
  • create database if not exists hive_db;：检测该表不存在时，创建该表
  • desc formatted emp;：查看表类型
  1. 外部表操作
  • create table emp(id int,name string) row format[按行格式化] delimited fields[根据字段] terminated by “\t”[按空格切分];：创建表（默认创建管理表）
  • create external table if not exists emp2(id int,name string) row format delimited fields terminated by “\t”;：创建外部表
  • create table if not exists emp2 as select * from emp where name=”zfhzxg”;：emp2存在时，将emp中name为zfhzxg的字段传输到emp2中
  1. 分区表操作
  • create table if not exists emp_partition(id int,name string) partitioned by (day string) row format delimited fields terminated by “\t”;：创建分区表，以day为分区
  • select * from emp_partition where day=’1112’;：查询day分区为1112的数据
  • alter table emp_partition add partition(day=’1113’);：向emp_partition表中添加1113分区
  • alter table emp_partition drop partition(day=’1112’);：删除1112分区
  1. 修改表
  • alter table emptable rename to empt;：修改表名
  • alter table emp_partition add clumns(desc string);：向表中添加desc字段
  • alter table emp_partition change column desc descs int;：修改表中的字段
  • alter table emp_partition replace colums(name string,descs int);：替换表中的字段（重新定义，全部替换）

DML数据操作

  1. 加载/导入、插入
  • load data local inpath ‘path’ into table hive_db;：导入本地数据（追加）
  • load data inpath ‘path’ into table default.emp;：向default库中的emp表导入hdfs中的数据（追加，如果加载hdfs中的数据，源文件将会被剪切）
  • load data inpath ‘path’ overwrite into table default.emp;：导入hdfs中的数据（覆盖）
  • load data local inpath ‘path’ into table emp_partition partition(day=’1112’);：向1113分区中导入本地数据（追加）
  • insert into table emp_partition partition(day=’1112’) values(1,’zfhzxg’);：向分区表中的1112分区插入数据
  • create table if not exists emptable as select * from emp_partition where day=’1112’;：新创建一张表，并导入emp_partition表中1112分区中的数据
  • create table if not exists empta(id int,name string) row format delimited fields terminated by ‘\t’ location ‘path’;：创建表是关联数据，并不会剪切元数据
  • insert overwrite local directory ‘path’ select * from emp_partition where day=’1113’;：向本地路径（文件夹）导出empt_partition表中1113分区中的数据
  • dfs _get ‘path1’ ‘path2’;：在hive终端可以输入hdfs命令，可以用hadoop命令进行导出
  • [root@RedHat112 hive]# bin/hive -e “select from emp_partition where day=’1113’” > /root/1113.txt ：将hive中表的数据导出成文件，用于结果导出，如果sql语句中包含单引号，需要用双引号包含sql语句
  • truncate table emp;：清空表中的数据

查询

  1. 基础查询
  • seltct * from emp;：全表查询
  • select emp.id,emp.name from emp;：查询指定字段
  • select emp.id [as] id,emp.name [as] name from emp;：自定义字段名
  1. 算数运算符
  • +（相加）
  • -（相减）
  • *（相乘）
  • /（相除）
  • %（取余）
  • &（按位取与）
  • |（按位取或）
  • ^（异或）
  • ~（按位取反，只能用于四种整数类型）
  1. 函数
  • count（求行数）
  • max（最大）
  • min（最小）
  • sum（求和）
  • avg（平均值）
  • limit（查看前几条数据）：select * from emp limit 2;
  1. where
  • select * from emp where id between 1 and 3;：查询id在1到3之间的人
  • select * from emp where id in(1,3);：查询id为1和3的人
  1. like：选择类似的值，选择条件可以包含字母和数字
  • select * from emp where name like “_f%”;：查找name第二个字符为f的人
  • select * from emp where name rlike “[7]”;：rlike中可以使用正则表达式
  1. group by：分组
  • select avg(empt.sal) avg_sal,deptno from empt group by deptno;：查询每个部门的工资平均值（按deptno分组）
  • select max(empt.sak) max_sal,deptno from empt group by deptno;：查询每个部门的最高薪水
  • select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal>1700;：查询每个部门的平均薪水大于1700的部门（在分组后添加条件时，用having）

自定义函数

  1. UDF（user-defined-function）：一进一出
  • 导入hive依赖包（hive/lib下）
  • 上传自定义函数包
  • add jar /root/tools/lower.jar;：添加到hive中
  • create temporary function my_lower as “com.zfhzxg.com.Lower”;：关联
  • 正常语法即可使用自定义函数
  1. UDAF：多进一出（count、max、min）
  2. UDTF：一进多出

join

  1. 等值join
  • select e.empno,e.ename,d.dept from empt e join dept d on e.deptno = d.deptno;
  1. 左外连接 left join（默认）
  • select e.empno,e.ename,d.dept from empt e left join dept d on e.deptno = d.deptno;
  1. 右外连接 right join（右边中符合where条件的所有记录都会被返回）
  • select e.empno,e.ename,d.dept form dept e right join empt e on e.deptno = d.deptno;
  1. 多表连接查询
  • select e.ename,d.dept,l.loc_name from empt e join dept d on e.deptno = d.deptno join location l on d.loc = l.loc_no;：查询员工的姓名，所在部门和地址

笛卡尔积（省略连接条件时）设置严格模式

  • set hive.mapred.mode;：查看模式
  • set hive.mapred.mode=strict;：设置严格模式（仅在本次设置终端内）

排序

  1. 全局排序 order by
  • select * from empt order by sal;：按照工资查询，升序（默认为升序）
  • select * from empt order by sal asc;：按照工资查询，升序
  • select * from empt order by sal desc;：按照工资查询，降序
  • select empt.empno,empt.sal2 two2sal from empt order by two2sal;：查询员工编号，按照双倍工资显示
  1. 分区排序
  • select * from empt distribute by month sort by empno desc;：查询月份分区表按照员工编号做降序排序

分桶

  1. 分区表分的时数据的存储路径
  2. 分桶是针对数据文件，将数据集整理成了若干个便于管理的部分（抽样测试）
  3. 分桶操作
  • create table emp_buck(id int,name string) clustered by (id) into 4 buckets row format delimited fields terminated by “\t”;：创建一个拥有四个分桶的分桶表
  • insert into table emp_buck select * from emp_b;：使用子查询的方法向分桶表中导入数据（使用元数据导入将不会产生分桶）
  • set hive.enforce.bucketing=true;：设置不让reduce的个数影响子查询导入的结果
  • select * from emp_buck tablesample(bucket x out of y on id);
  • y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。

Hive优化

压缩

  1. 开启Map阶段输出压缩
  • set hive.exec.compress.intermediate=true;：开启输出压缩功能
  • set mapreduce.map.output.compress=true;：开启Map输出压缩功能
  • set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;：设置压缩方式为SnappyCodec
  1. 开启Reduce阶段输出压缩
  • set hive.exec.compress.output=true;：开启最终输出压缩功能
  • set mapreduce.output.fileoutputformat.compress=true;：开启Reduce最终数据压缩功能
  • set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;：设置Reduce最终输出压缩方式为SnappyCodec
  • set mapreduce.output.fileoutputformat.compress.type=BLOCK;：开启块压缩

    存储

  1. hive存储格式：TextFile、SequenceFile、orc（列存储）、Parquet（列存储）
  • orc：Index Data，轻量级索引，默认每一万行做一个索引，rew Data，存储具体数据，stripe Footer，存储流的类型
  1. create table emp_stored(in int,name string) row format delimited fieleds terminated by “\t” stored as orc;：创建存储格式为orc的表
  2. 压缩比：orc > parquet > textFile

    数据倾斜

  3. set hive.map.aggr=true;：在Map端进行聚合
  4. set hive.groupby.skewindata=true;：负载均衡
  5. jvm 重用：
    1
    2
    3
    4
    5
    hadoop:mapred-site.xml:
    <property>
      <name>mapreduce.job.jvm.numtasks</name>
      <value>10~20</value>
    </property>