hive
简介
hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据映射为一张表,它提供了一系列的工具,可以用来进行数据提取、转化和加载。hive定义了简单的类SQL查询语言,称为HiveSQL。hive在执行过程中会将HQL转换为MapReduce执行,所以它本质上还是一种离线的大数据分析工具,由于hadoop通常会在作业提交和调度时有很大的开销,有较高延迟,因此hive也不能再大规模数据集上实现低延迟的快速查询。hive的最佳适用场景是大数据集的批处理作业,例如网络日志的分析。
数据仓库的特征:
- 由多种异构数据组成;
- 一般是历史数据,用来读和分析,所以是弱事物;
- 数据库是为捕获数据,数据仓库是为分析数据的;
- 数据是时变的,即数据包含时间元素;
数据库属于OLTP,即联机事物处理系统,一般用来各种具体的业务处理;数据仓库属于OLAP系统,即联机分析处理系统,一般用来分析数据。
数据仓库与数据库的对比:
数据仓库 | 数据库 |
---|---|
为离线分析存储历史数据 | 为线上提供实时数据 |
只支持一写多读,不支持行级改 | 具有完善的crud功能 |
不强调事物 | 具有完整的事物能力 |
制造冗余,提高查询效率 | 尽量避免冗余 |
数据来源多样 | 数据来源单一 |
OLTP | OLAP |
安装:需要jdk环境、hadoop环境、解压即可使用;
运行:运行hive之前需要启动HDFS和MapReduce,启动命令:./bin hive;
注意:hive会将使用中产生的元数据会放在derby数据库,而derby是文件式数据库,而且是在哪里启动的hive,derby文件就在那个目录,更换目录元数据就丢失,而且同时只能连接一个客户端,这很不方便,所以需要借助mysql来保存hive的元数据。
基本语句
hive常用命令如下:
show databases
:显示数据库;create database test01
:创建数据库;(其实是一个目录)use test01
:切换到test01数据库;create table stu(id int, name string) row format delimited fields terminated by ‘ ’
:创建stu表,包含id、name两个字段,且分隔符为空格;(其实就是一个目录)desc stu
:查看表结构;insert into stu values(1, "张三")
:插入数据(一般不用);select * from stu (where id=1)
:查询,可以跟条件;load data local inpath '/data/1.txt' into table stu
:加载本地文件保存到数据库(HDFS);create table stu2 like stu
:创建一个结构和stu一样的表;insert overwrite table stu2 select * from stu
:将查询结果覆插入到stu2中;from stu insert overwrite table stu2 select * insert overwrite table stu2 select *
:将stu的查询结果覆盖插入到stu2和stu3中insert overwrite local directory ‘/data/1.txt' row format delimited fields terminated by ' ' select * from stu
:将查询结果保存到本地文件(去掉local关键字则是保存到HDFS中);alter table stu2 rename to stu3
:将stu2表重命名为stu3;drop table stu3
:删除stu3表;- hql中连表有:内连接(inner join,不能简写)、左连接(left join)、右连接(right join)、全连接(full join);
表概念
内部表
在上面的基本指令中,默认创建的表就是内部表,由hive先建一张表,然后向这个表里插入数据,这种表不常用;
外部表
外部表是在HDFS中已经有了固定格式的数据,然后在hive中创建外部表处理这部分数据,外部表可以管理指定目录下的所有文件,要求是格式统一。删除外部表不会删除真实的文件。创建命令:create external table stu1(id int, name string, score int) row format delimited fields terminated by ' ' location '/hdfs/dir'
。
分区表
即对数据进行分区,每个分区一个目录,这样可以避免整表查询从而提高查询效率,外部表和内部表都可以是分区表。分区表在导入数据时需要在hql语句末尾添加**partition(type=‘xx’)**指定分区;实际效果在HDFS中就是一个分区对应一个目录,分区字段就对应目录名,为字段名=字段值(type=xx),相关命令如下。
- 创建内部分区表:
create table stu(id int, name string) partitioned by (type string) row format delimited fields terminated by ' '
; - 创建外部分区表:
create external table stu(id int, name string) partitioned by (type string) row format delimited fields terminated by ' ' location '/hdfs/dir'
; - 添加分区:
alter table stu add partition(type='xx1') location '/hdfs/type=xx1'
;或者msck repair table book
; - 查看分区:
show partitions stu
; - 删除分区:
alter table stu drop partition(type='xx1')
; - 修改分区名:
alter table stu partition(type='xx1') rename to partition(type='xx2')
;
分桶表
分桶表概念:
- 分桶表是一种更细粒度的数据分配方式;
- 一个表可以分区也可以分桶;
- 分桶的主要作用是用于实现数据抽样;
- 分桶通过hash算法将数据分布在不同的桶中;
- 默认不开启,需要手动开启;
- 分桶表只能从另一张表导入数据(经测试,并不是);
- 涉及到Join操作时,可以在桶与桶间关联即可,大大减小Join的数据量,提高执行效率。
- 物理上每个桶就是目录里的一个文件,一个作业产生的桶(输出文件)数量和reduce任务个数相同。
使用分桶表:
- 开启分桶机制:set hive.enforce.bucketing=true;
- 创建分桶表:
create table student(name string, age int) clustered by (name) into 3 buckets row format delimited fields terminated by ' '
; - 进行抽样:
select * from student tablesample(bucket 1 out of 6 on name)
;(1表示从第1个桶开始抽样;6表示抽样步长和比例,即抽取桶数/6个桶的数据,每隔6个桶抽取一次,这个值只能是桶数的因数或倍数);
事务表
hive0.14之后开始支持事务和行级更新,但缺省不支持,需要额外的属性配置,还需要是分桶表且为orc格式;使用步骤如下:
- hive-site.xml中进行如下配置:
<property>
<name>hive.support.concurrency</name>
<value>true</value>
</property>
<property>
<name>hive.exec.dynamic.partition.mode</name>
<value>nonstrict</value>
</property>
<property>
<name>hive.txn.manager</name>
<value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
<property>
<name>hive.compactor.initiator.on</name>
<value>true</value>
</property>
<property>
<name>hive.compactor.worker.threads</name>
<value>1</value>
</property>
<property>
<name>hive.enforce.bucketing</name>
<value>true</value>
</property>
- 创建事务表:
create table t1(id int,name string) clustered by (id) into 3 buckets row format delimited fields terminated by ',' stored as orc
;
事务表一般不用
数据类型
array
# 数组类型可以通过size(info)获取数组长度
create external table t1(info array<int>) row format delimited fields terminated by ' ' collection items terminated by ',' location '/hdfs/1.txt'
map
# 列分隔符只能是\t
create external table t1(info map<string,int>) row format delimited fields terminated by '\t' map keys terminated by ',' location '/hdfs/1.txt'
# 一个map的查询示例
select distinct(info['k']) from t1 where info['k'] is not null;
struct
类似于Javabean
# 没有列分割符了
create external table t1(info struct<id:int,name:string,age:int>) row format delimited collection items terminated by ' ' location '/hdfs/1.txt'
# 使用struct示例
select info.age from t1 where info.naem='xx'
常用操作函数
- length(str):返回字符串长度;
- reverse(str):反转字符串;
- concat(str1, str2, ···)、concat_ws(ws, str, str, ···):拼接字符串、带拼接符的字符串拼接;
- substr(str, start, end):截取字符串,位置可以为负数,表示倒数;
- upper(str)、lower(str):转大/小写;
- trim(str)、ltrim(str)、rtrim(str):去掉空格,去掉左边空格,去掉右边空格;
- regexp_replace(str, pattern, str):正则替换;
- regexp_extract(str, pattern, int):正则搜索;
- repeat(str, int):重复字符串n此;
- split(str, str):切分为数组,参数为:字符串、切割符;
- explode(array):将数组切分为多行;
UDF
UDF即用户自定义函数,使用方法如下:
- 将hive的lib中的jar包加到工程中;
- 创建类继承UDF类, 编写**evaluate()**方法;
- 编写完成后打成jar包,拷贝到hive的lib目录下;
- 执行hive命令:add jar /data/hive/xxx.jar;
- 执行hive命令:create temporary function myfuc as ‘com.xxx’;
调优
group by的数据倾斜问题:
- set hive.map.aggr=true:相当于添加Combiner;
- set hive.groupby.skewindata=true;该参数本质是生成两个MRJob查询计划。第一次MR会在key上拼接随机字符串,从而使相同的key可能输出到不同的reduce中,第二次才会真正的实现将相同的key输出到相同的reduce中,从而达到最终的分区效果;
join的数据倾斜问题:
- 大小表的join:使用map join代替join,本质是将小表缓存到map端,并在map端就完成join;
- 在0.11之前是显示在select后面跟
/*+mapjoin(t1)*/
- 在0.11之后由两个参数来控制:①hive.auto.convert.join=true(默认开启);②hive.mapjoin.smalltable.filesize=25000000(默认25M)。
- 0.11之后也可以通过指定两个参数后使用第一种方式进行mapjoin:①hive.auto.convert.join=false;②hive.ignore.mapjoin.hint=false;
- 在0.11之前是显示在select后面跟
- 大表join大表:将造成数据倾斜的字段单独处理,然后在union其他结果;
连表查询的优化,在hive中,如果连表查询有查询条件,最好改为子查询(原因是count计数操作在hive中只会用一个reduce来实现,所以要先过滤出数据再计数),例如:
# 优化前:
select * from t1 a join t2 b on a.cid=b.id where a.dt='2019-10' and b.price>100;
# 优化后:
select * from (select * from t1 where dt='2019-10') a join (select * from t2 where price>100) b on a.cid=b.id;
参数调优
- 调整切片大小:set mapred.max.split.size=134217728(默认128MB);
- JVM重用:set mapred.job.reuse.jvm.num.tasks=1(默认1个);
- 关闭推测执行机制:set mapred.map.tasks.speculative.execution=false和set mapred.reduce.tasks.speculative.execution=false;
- 启用严格模式:set hive.mapred.mode=strict,启用严格模式后在以下情况会报错:
- 查询分区表没有指定分区字段;
- 使用了order by但是没有使用limit;
- 产生了笛卡尔积;
- 切换执行引擎:set hive.execution.engine=spark;(默认为mr)
- 设置reduce数量:set mapreduce.job.reduces=2;
- hive.exec.reducers.bytes.per.reducer=1000000000 ;(hive默认每1G文件分配一个reduce)
- 不要使用count (distinct cloumn) ,使用子查询
sqoop
sqoop:用于HDFS和关系型数据库的数据的相互导入,还可以将数据从关系数据库导出到HBASE,安装sqoop需要jdk和hadoop(导出到Hbase还需要hbase的)的环境变量,并且需要将相关的驱动包(例如mysql驱动包)拷贝到sqoop的lib下,最后在./conf/sqoop-env.sh文件中修改相关的环境变量即可;
基础指令:
- 查看mysql数据库:
./sqoop list-databases --connect jdbc:mysql://hdp01:3306/ -username root -password 1234
; - 查看表:
./sqoop list-tables --connect jdbc:mysql://hdp01:3306/db1 -username root -password 1234
; - mysql->hdfs:
./sqoop import --connect jdbc:mysql://hdp01:3306/db1 --username root --password 1234 --table t1 --target-dir '/data/t1' --fields-terminated-by '|' -m 1
;(还可以通过–query参数指定SQL查询,如:--query 'select * from db1.t1 where $CONDITIONS and id > 10'
) - hdfs->mysql:
- 先在mysql建表,注意字段类型要一致;
./sqoop export --connect jdbc:mysql://hdp01:3306/db1 --username root --password root --export-dir '/data/t1/' --table t1 -m 1 --fields-terminated-by '|'
;
- mysql->hbase:
./sqoop import connect jdbc:mysql://hdp01:3306/db1 --username root --password 1234 --table t1 --hbase-table t1 --column-family info --hbase-row-key sid --hbase-create-table
; - 查看帮助:./sqoop help,也可以:./sqoop import help
处理json
- 拷贝jar包hive-hcatalog-core-1.2.0.jar到lib目录下;
- 在hive客户端中执行命令:add jar /…/lib/xxx.jar;
- 创建与json格式对应的表:
create table if not exists t1(id int,name String) row format serde 'org.apache.hive.hcatalog.data.JsonSerDe' stored as textfile
; - 加载数据:
load data local inpath '/data/xxx.json' into table t1
;