Hive的使用
Hive 中数据库Database基本操作
创建数据库:
(1)create database db_hive_01;
(2)create database if not exists db_hive_02;//标准的写法
(3)create database if not exists db_hive_03 location '/user/beifeng/hive/warehouse/db_hive_03.db';
显示数据库:
show databases;
show databases like 'db_hive*' //模糊显示数据库
查看数据库的信息:
desc database db_hive_03;
desc database extended db_hive_03;
创建表的三种方式:
(1)直接创建
create table IF NOT EXISTS default.bf_log_20150913
(
ip string COMMENT 'remote ip address',
user string,
req_url string COMMENT 'user request url'
)
COMMENT 'BeiFeng Web Access Logs'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
STORED AS TEXTFILE
LOCATION '/user/beifeng/hive/warehouse/bf_log_20150913'
(2)基于某些表查询出结果来建表
create table IF NOT EXISTS default.bf_log_20150913_sa
AS select ip,user from default.bf_log_20150913;
(3)基于已经存在的表和视图来构建新的表
create table IF NOT EXISTS default.bf_log_20150912
like default.bf_log_20150913;
查看表结构信息
desc student;
desc extended student;//查看详细信息
desc formatted student;//开发常用
load data local inpath '/opt/data/student.txt' into table db_hive.student;
查看Hive的函数
show function;
查看函数的使用
desc function upper;
查看函数的详细使用:
desc function extended upper;
select id,upper(name) uname from db_hive.student;
删除数据库:
drop database db_hive_03;
drop database db_hive_03 cascade;
drop database if exists db_hive_03;
以【雇员表和部门表】为例创建讲解Hive中表的操作
员工表
create table IF NOT EXISTS default.emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
部门表
create table IF NOT EXISTS default.dept(
deptno int,
dname string,
loc string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
加载数据
load data local inpath '/opt/data/emp.txt' overwrite into table emp;
load data local inpath '/opt/data/dept.txt' overwrite into table dept;
overwrite关键字 表示覆盖表中的数据
创建子表
create table if not exists default.dept_cats
as
select * from dept;
清除表中的数据
truncate table dept_cats;
create table if not exists default.dept_like
like default.dept;
修改表的名称
alter table dept_like rename to dept_like_rename;
删除表
drop table if exists dept_like_rename;
Hive中外部表的讲解(对比管理表)
在hive中表的类型有两种
(1)管理表
(2)托管表(外部表) 加external关键字
create external table IF NOT EXISTS default.emp_ext(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
/hdfs/weblogs/
20150910.txt
20150911.txt
20150912.txt
外部表的区别在于删除表是不删除表所对应的数据文件,而管理表会删除对应的数据文件
企业中80%会使用外部表。
总结:
内部表也称为managed_table
默认存储在/user/hive/warehouse下,也可以在创建表的时候通过location来指定
删除表时,会删除表数据以及元数据
外部表称为external_table
在创建表时可以自己指定目录位置(location) 通常必须指定
删除表时,只会删除元数据不会删除表数据
create external table IF NOT EXISTS default.emp_ext2(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
location '/user/beifeng/hive/warehouse/emp_ext2';
load data [LOCAL] INPATH 'filepath'
[OVERWRITE] into table tablename
[PARTITION (partcol1=val1,...)]
原始文件存储的位置
(1)本地 local
(2)hdfs
对表中的数据是否覆盖
(1)覆盖 OVERWRITE
(2)追加
分区表加载,特殊性
partition (partcol1=val1,…)
(1)加载本地文件到hive表
load data local inpath '/opt/datas/emp.txt' into table default.emp;
(2)加载hdfs文件到hive中
load data inpath '/user/beifeng/hive/datas/emp.txt' into table default.emp;
(3)加载数据覆盖表中已有的数据
load data inpath '/user/beifeng/hive/datas/emp.txt' overwrite into table default.emp;
(4)创建表的时候通过insert加载
create table default.emp_ci like emp;
insert into table default.emp_ci select * from default.emp;
(5)创建表的时候通过location指定加载
导出Hive表数据的几种方式讲解
(1)insert overwrite local directory '/opt/datas/hive_exp_emp'
select * from default.emp;
(2)insert overwrite local directory '/opt/datas/hive_exp_emp'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY '\n'
select * from default.emp;
(3)bin/hive -e "select * from default.emp;" > /opt/data/exp_res.txt
(4)insert overwrite directory '/user/beifeng/hive/hive_exp_emp'
select * from default.emp;
(5)sqoop方式
Hive中常见的查询讲解
select * from emp;
select t.empno,t.ename, t.deptno from emp t;
select * from emp limit 5;
select t.empno,t.ename, t.deptno from emp t where t.sal between 800 and 1500;
select t.empno,t.ename, t.deptno from emp t where comm is null;
show functions;
查看函数的使用:
desc function extended min;
select count(*)cnt from emp ;
select max(sal) max_sal from emp ;
select sum(sal) sum_sal from emp ;
select avg(sal) avg_sal from emp ;
Hive中数据导入导出Import和Export使用讲解
export 将hive表中的数据导出到外部
import 将外部数据导入到hive表中
export table tablename to 'export_target_path'
注意:这里的export_target_path指的是HDFS上的路劲
export table emp to '/user/beifeng/hive/export/emp_ext';
通过已有的表创建表结构类似的空表(不导入数据)
create table db_hive.emp like default.emp
import table db_hive.emp from '/user/beifeng/hive/export/emp_ext';
其他
order by 全局排序 一个Reduce
sort by 每个Reduce内部进行排序,全局不是排序
distribute by 类似MR中partition,进行分区,结合sort by使用
cluster by 当distribute和sort字段相同时,使用方式
(1)order by是对全局数据进行排序,仅仅只有一个reduce,当数据量非常大的时候,很容易会出现问题
select * from emp order by empno desc;
(2)sort by是对每一个reduce内部进行排序,全局结果集来说不是排序的
设置job的reduce数量
set mapreduce.job.reduces=3;每个reduce会生成一个文件
select * from emp sort by empno asc;
insert overwrite local directory ‘/opt/datas/sortby-res’ select * from emp sort by empno asc;
(3)distribute by
分区partition 类似于MapReduce中分区partition,
对数据进行分区,结合sort by进行使用。
insert overwrite local directory ‘/opt/datas/disby-res’
select * from emp distribute by deptno sort by empno asc;
注意:distribute by必须在sort by前面,必须先分区才可以排序
(4)cluster by 当distribute和sort字段相同时,使用cluster by
insert overwrite local directory ‘/opt/datas/disby-res’
select * from emp cluster by empno asc;
操作
group by /having
每个部门的平均工资
select
t.deptno,
avg(t.sal) avg_sal
from emp t
group by t.deptno;
每个部门中最高岗位的薪水
select
t.deptno,
t.job,
max(t.sal) max_sal
from emp t
group by t.deptno,t.job
having 是针对分组结果进行筛选
where是针对单挑记录进行筛选
求每个部门的平均薪水大于2000的部门
select
t.deptno,
avg(t.sal) avg_sal
from emp t
group by t.deptno
having avg_sal>2000;
join
等值join … on
select
e.empno,e.ename, d.deptno, d.dname
from emp e join dept d
on e.deptno=d.deptno
左右接连
select
e.empno,e.ename, d.deptno, d.dname
from emp e left join dept d
on e.deptno=d.deptno
Hive高级
HiveServer2、Beeline、JDBC使用
启动HiveServer2
bin/hiveserver2
启动Beeline
bin/beeline
通过Beeline连接
!connect jdbc:hive2://Master:10000 beifeng beifeng org.apache.hive.jdbc.HiveDriver
进入之后和在bin/hive是一样的,在beeline中操作只看到结果,看不到MapReduce的打印信息。
还有一种连接方式
bin/beeline -u jdbc:hive2://Master:10000/default
HiveServer2 JDBC
将分析的结果存储在hive表(result),前段通过DAO代码,进行数据的查询
Hive中常见的数据压缩讲解
(1)安装sanppy
(2)编译hadoop 2.x源码
mvn package -Pdist,native -DskipTests -Dtar -Drequire.snappy
/opt/modules/hadoop-2.5.0-src/target/hadoop-2.5.0/lib/native
bin/yarn jar share/hadoop/mapreduce/hadoop-mapreduce-examples-2.5.0.jar
wordcount -Dmapreduce.map.output.compress=true
-Dmapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec
/user/beifeng/mapreduce/wordcount/input
/user/beifeng/mapreduce/wordcount/output2
在hive中配置
set mapreduce.map.output.compress=true;
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
配置演示讲解mapreduce和hive中使用snappy压缩
input -> map -> shuffle -> reduce -> output
数据压缩的好处
数据量小,减少本地磁盘IO和网络IO
压缩格式:bzip2,gzip,lzo,snappy
压缩比:bzip2>gzip>lzo bzip2最节省存储空间
解压速度:lzo>gzip>bzip2 lzo解压速度是最快的
通常情况下
block -> map
10G ,10 block
压缩
5G ,5 block
Hive Storage Format讲解
文件的存储格式
file_format:
| sequencefile
| textfile (default)
configuration
| RCFILE
| ORC
| PARQUET
| AVRO
| INPUTFORMAT
数据存储
按行存储
按列存储
ORC PARQUET用的最多
create table page_views(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
load data local inpath '/opt/datas/page_views.data' into table page_views;
create table page_views_orc(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc;
insert into table page_views_orc select * from page_views;
create table page_views_parquet(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS parquet;
insert into table page_views_parquet select * from page_views;
hdfs中查看文件的大小
hdfs dfs -du -h path
10000条日志数据文件大小
textfile 18.1M
orc 2.6M
parquet 13.1M
查询测试
select session_id,count(*) cnt from page_views group by session_id order by cnt desc limit 30;
62s
select session_id,count(*) cnt from page_views_orc group by session_id order by cnt desc limit 30;
61s
同时指定数据存储格式和压缩方式:
create table page_views_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="SNAPPY");
create table page_views_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="NONE");
加载数据之后 NONE为7.6M SNAPPY方式压缩后3.8M
总结:在实际项目开发中,hive表的数据
存储格式:orcfile/parquet
数据压缩:snappy
Hive 企业使用优化
select * from emp;这里的select * 不走MapReduce
这是Hive本身的优化
select id from emp就会执行MapReduce,
其实这样也不应该去走MapReduce
这是FetchTask机制,配置如下:
hive.fetch.task.conversion
minimal
minimal是默认的配置 也可以配置more
minimal情况下:(1)select * (2)分区字段的where (3)limit 不执行MapReduce
more情况下:select 某个字段 不执行MapReduce
Hive的高级优化:
(1)大表拆分为子表
create [temporary] [external] table [if not exists] [db_name.]table_name
[as select_statement]
(2)外部表,分区表
结合使用
多级分区
create [temporary] [external] table [if not exists] [db_name.]table_name
[(col_name data_type [comment col_name],…)]
[partition by (col_name data_type [comment col_comment],…)]
[row format row_format]
(3)数据
存储格式(textfile,orcfile,parquet)
数据压缩(snappy)
(4)SQL
优化SQL语句
join/filter
(5)MapReduce
Reduce Number
JVM重用
推测执行
Join优化:
Commmon/Shuffle/Reduce Join
连接发生的阶段,发生在Reduce Task
大表对大表
每条数据都是从文件中读取的
Map Join
连接发生的阶段,发生在Map Task
用于大表对小表
*大表的数据放从文件中读取 cid
*小表的数据内存中 id
DistributedCache
可以设置 hive.auto.convert.join=true
SMB Join
Sort-Merge-Bucklet Join
通常用于用大表对大表的Join
通常需要设置:
set hive.auto.convert.sortmerge.join=true
set hive.optimize.bucketmap.join=true
set hive.optimize.bucketmap.sortedmerge=true
customer
3 bucket
lst 1001-1101
2nd 1201-1401
3rd 1501-1901
order
3 bucket
lst 1001-1101
2nd 1201-1401
3rd 1501-1901
customer的1st桶join order的1st桶
分桶Join
查看hive的执行计划
explain select * from emp;
explain select deptno,avg(sal) avg_sal from emp group by deptno;
并行执行:
hive.exec.parallel.thread.number=8 最好不要超过20
hive.exec.parallel=false
JVM重用
mapreduce.job.jvm.numtasks=1
Reduce数目
mapreduce.job.reduce=1
推测执行
mapreduce.map.speculative=true
hive.mapred.reduce.tasks.speculative.execution=true
mapreduce.reduce.speculative=true
Map数目
hive.merge.size.per.task=256000000
group by /count(distinct)会导致数据倾斜
动态分区调整
动态分区属性:设置为true表示开启动态分区功能(默认为false)
hive.exec.dynamic.partition=true
动态分区属性:设置为nonstrict,表示允许所有分区都是动态的(默认为strict)
设置为strict,表示必须保证至少有一个分区是静态的
hive.exec.dynamic.partition.mode=strict
动态分区属性:每个mapper或reducer可以创建的最大动态分区个数
hive.exec.max.dynamic.partition.pernode=100
动态分区属性:一个动态分区创建语句可以创建的最大动态分区个数
hive.exec.max.dynamic.partition=1000
动态分区属性:全局可以创建的最大文件个数
hive.exec.max.create.files=100000
strict mode
对分区表进行查询,在where子句中没有加分区过滤的话,讲禁止提交任务(默认:nostrict)
set hive.mapred.mode=strict
注意:使用严格模式可以禁止3种类型的查询
(1)对于分区表,不加分区字段过滤条件,不能执行
(2)对于order by 语句,必须使用limit语句
(3)限制笛卡尔积德查询(join的时候不使用on,而使用where的)
Hive项目实战
Hive 项目实战一创建表并导入日志数据,引出问题
create table IF NOT EXISTS default.bf_log_src(
remote_addr string,
remote_user string,
time_local string,
request string,
status string,
body_bytes_sent string,
request_body string,
http_referer string,
http_user_agent string,
http_x_forward_for string,
host string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
store as textfile;
load data local inpath '/opt/data/moodle.ibeifeng.access.log' into table bf_log_src;
思路
*原表
*针对不同的业务创建不同的子表
*数据存储格式 orcfile/parquet
*数据压缩 snappy
*map output 数据压缩 snappy
*外部表
*分区表(演示)
Hive 项目实战二使用RegexSerDe处理Apache或者Ngnix日志文件
在创建表的时候可以采用hive提供的正则校验数据格式
drop table if not exists default.bf_log_src;
create table IF NOT EXISTS default.bf_log_src(
remote_addr string,
remote_user string,
time_local string,
request string,
status string,
body_bytes_sent string,
request_body string,
http_referer string,
http_user_agent string,
http_x_forward_for string,
host string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex"=".............."
)
store as textfile;
Hive 项目实战三依据原表创建子表及设置orcfile存储和snappy压缩数据
drop table if not exists default.bf_log_comm;
create table IF NOT EXISTS default.bf_log_comm(
remote_addr string,
time_local string,
request string,
http_referer string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="SNAPPY");
加载数据
insert into table default.bf_log_comm
select remote_addr,time_local,request,http_referer from default.bf_log_src;
Hive 项目实战四数据清洗之自定义UDF去除数据双引号
去除字段的” e.g. “xxxx”
定义UDF对原表数据进行ETL清洗
第一个UDF去除引号
public class RemoveQuotesUDF extends UDF {
public Text evaluate(Text str) {
//validate
if (null == str) {
return null;
}
if (null == str.toString()) {
return new Text();
}
//remove
return new Text(str.toString().replaceALL("\"", ""));
}
}
add jar /opt/datas/hiveud2.jar
create temporary function my_removequotes
as "com.beifeng.senior.hive.udf.RemoveQuotesUDF";
list jar
再次覆盖数据
insert overwrite into table default.bf_log_comm
select
my_removequotes(remote_addr),
my_removequotes(time_local),
my_removequotes(request),
my_removequotes(http_referer)
from default.bf_log_src;
=======3.19、 Hive 项目实战五数据清洗之自定义UDF转换日期时间数据
重点:第二个UDF处理日期时期字段
31/Aug/2015:00:04:37 +0000 => 20150831000437
public class DateTransformUDF extends UDF {
private final SimpleDateFormat inputFormat = new SimpleDateFormat("dd/MMM/yyyy:HH:mm:ss", Locale.ENGLISH);
private final SimpleDateFormat outputFormat = new SimpleDateFormat("yyyyMMddHHmmss");
public Text evaluate(Text input) {
Text pouput = new Text();
//validate
if (null == input) {
return null;
}
if (null == input.toString()) {
return null;
}
String inputDate = input.toString().trim();
if (null == inputDate) {
return null;
}
try {
//parse
Date parseDate = inputFormat.parse(inputDate);
//transform
String outputDate = outputFormat.format(parseDate);
//set
output.set(outputDate);
} catch(Exception e) {
e.printStackTrace();
return output;
}
return output;
}
}
将程序打成jar包
add jar /opt/datas/hiveud2.jar
create temporary function my_datetransform
as "com.beifeng.senior.hive.udf.DateTransformUDF";
insert overwrite into table default.bf_log_comm
select
my_removequotes(remote_addr),
my_removequotes(my_datetransform(time_local)),
my_removequotes(request),
my_removequotes(http_referer)
from default.bf_log_src;
select * from default.bf_log_comm limit 5;
Hive 项目实战六依据业务编写HiveQL分析数据
查看内置函数的s使用
desc function extended substring;
substring('Facebook', 5, 1) => 'b' 下标从1开始截取
select
t.hour,
count(*) cnt
from
(
select
substring(time_local,9,2) hour
from default.bf_log_comm
) t
group by t.hour
order by cnt desc;
-----
select
t.prex_ip,
count(*) cnt
from
(
select
substring(remote_addr,1,7) prex_ip
from default.bf_log_comm
) t
group by t.prex_ip
order by cnt desc;