Hive
1. hive简介
- hive是一个数据仓库,非Java编程者对HDFS的数据做mapreduce操作。hive是基于hadoop的一个数据仓库工具。数据仓库是用来做查询分析的数据库,基本不用来做修改和删除.
- hive:数据仓库。
- hive:解释器,编译器,优化器等。
- hive运行时,元数据储存再关系型数据库中。(mysql)
2. hive的搭建的模式
- 内嵌模式(元数据保存再内嵌的derby中,允许一个会话连接,尝试多个会话连接会报错)
2. 单用户模式
- 安装mysql代替derby储存数据
3. 远程模式/多用户模式
- 在服务器端启动MetaStoreServer,客户端利用Thrift协议通过MetaStoreServer访问元数据库
3. HQL详解
- 具体见官方文档
-
DDL语句
- 创建数据库
create databse [database_name]
- 删除数据库
drop database [database_name]
- 修改数据库
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
- 使用和查看数据库
use/show database_name
- 创建表
- 建表语句
create table person(
id int,
name string,
age int,
likes array,
address map<string,string>
)
row format delimited fields terminated by ‘,’
collection items terminated by ‘-’
map keys terminated by ‘:’
lines terminated by ‘\n’
*删除表
drop table table_name //注意:只能删除内部表的元数据和源数据,删除不了外部表的源数据
- 建表语句
- 创建数据库
-
DML语句
-
导入数据
- LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]
- INSERT OVERWRITE TABLE person2 [PARTITION(dt=‘2008-06-08’, country)]
SELECT id,name, age From ppt; - FROM person t1
INSERT OVERWRITE TABLE person1 [PARTITION(dt=‘2008-06-08’, country)]
SELECT t1.id, t1.name, t1.age ;
【from放前面好处就是后面可以插入多条语句 】
-
查询数据并保存
- A、保存数据到本地:
例: insert overwrite local directory ‘/opt/datas/hive_exp_emp2’
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
select * from db_1128.emp ;
//查看数据
!cat /opt/datas/hive_exp_emp2/000000_0; - B、保存数据到HDFS上:
例: insert overwrite directory ‘/user/beifeng/hive/hive_exp_emp’
select * from db_1128.emp ;
- A、保存数据到本地:
-
hive的group by \join \having等操作和mysql没多大的区别。
-
-
4.Hive分区
-
创建单分区
create table day_table(id int,content string) partitioned by (dt string) row format delimited fields terminated by ','//单分区
-
创建多分区
create table day_table(id int,content string) partitioned by(day string,hour string) row format delimitede fields terminated by ',' //多分区
-
添加分区
//已经创建好分区表,需要添加分区 alter table day_table add partition(dt='2018-08-05')
-
删除分区
alter table day_table drop partition(dt='2018-08-05')
-
数据加载进分区表
load data [local] inpath '/usr/**' into table day_table partition (dt='2018-08-05')
-
查看分区
show partitions day_table
-
重命名分区
alter table day_table partition(dr='2018-08-05') rename to partition(dt='2018-08-08')
-
动态分区
-
建立非分区表并加载数据
-
建立外部分区表并动态加载数据 (注意删除外部表的相关事项)
-
加载数据到动态分区
-
设置hive.exec.dynamic.partition.mode=nonstrict
from psn21 insert overwrite table psn22 partition(age, sex) select id, name, age, sex, likes, address distribute by age, sex;
-
-
hiveSerDe
- Serializer and Deserializer SerDe 用于做序列化和反序列化。
Hive正则匹配 CREATE TABLE logtbl ( host STRING, identity STRING, t_user STRING, time STRING, request STRING, referer STRING, agent STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ( "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) (-|[0-9]*)" ) STORED AS TEXTFILE;
5.Beeline,JDBC,hiveserver2
- beeline和hiveserver2
- 服务端 Hiveserver2直接启动
- 1、beeline -u jdbc:hive2://node5:10000/default -n root
- 2、先启动 beeline,然后在交互式界面上使用命令:!connect jdbc:hive2://node5:10000 root 123
- JDBC
- 一般是平台使用 展示或接口,服务端启动hiveserver2后,在java代码中通过调用hive的jdbc访问默认端口10000进行连接、访问
- 服务端 Hiveserver2直接启动
6.自定义函数UDF,UDAF,UDTF
- UDF:一进一出
- 自定义 UDF 需要继承 org.apache.hadoop.hive.ql.UDF。
- 需要实现 evaluate 函数,evaluate 函数支持重载
- 把程序打包放到目标机器上去;
- 进入 hive 客户端,添加 jar 包:hive>add jar /jar/udf_test.jar;(清除缓存时记得删除jar包delete jar /*)
- 创建临时函数:hive>CREATE TEMPORARY FUNCTION add_example AS ‘hive.udf.Add’;
- UDAF:多进一出
- UDTF:一进多出
7.hive中的参数和变量的设置方式
- 修改配置文件hive-site.xml
- hive set命令
set hive.cli.print.header=true;
- 启动hive cli
hive --hiveconf hive.cli.print.header=true
- hive参数初始化配置 初始化再/root/下设置.hiver隐藏文件
8.分桶
-
分桶的意义:数据抽样( sampling )、map-join
-
分桶的方式:分桶表是对列值取哈希值的方式,将不同数据放到不同文件中存储。
-
开启分桶设置:set hive.enforce.bucketing=true;
-
分桶代码流程
- 创建分桶表
CREATE TABLE psnbucket( id INT, name STRING, age INT) CLUSTERED BY (age) INTO 4 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
- 加载数据
insert into table psnbucket select id, name, age from psn31;
- 抽样
select id, name, age from psnbucket tablesample(bucket 2 out of 4 on age);
9.视图和索引
- 视图实际上是一句sql,视图并不保存源数据,只是保存了一份元数据
- 索引的存在可以很好的优化查询,是一种以空间换时间的行为
10.hive的运行方式
- 命令行cli运行:控制台模式
- 与hdfs交互
dfs -ls /
- 与linux交互
!pwd
- 与hdfs交互
- 脚本运行方式(!!!)
- 注意如果使用crontab -e定时则需要再sh脚本中写上hive的完整路径
- 注意如果使用crontab -e定时则需要再sh脚本中写上hive的完整路径
- JDBC方式:hiveserver2
- web GUI接口:(hwi,hue等)
11.权限管理
- Storage Based Authorization in the Metastore Server基于存储的授权
- SQL Standards Based Authorization in HiveServer2基于SQL标准的Hive授权
- Default Hive Authorization (Legacy Mode) hive默认授权
12.hive的优化(!!!)
-
核心思想:将优化当做一个mapreduce去优化
- hive本地模式:当数据量小于hive.exec.mode.local.auto.inputbytes.max的时候可以使用本地模式,如果超出仍然会是集群模式
- 并行计算:
通过设置以下参数开启并行模式: set hive.exec.parallel=true; Select t1.cf1,t2.cf2 from (select count(id) as cf1 from table1) t1, (select count(id) as cf2 from table1) t2, 注意:hive.exec.parallel.thread.number (一次SQL计算中允许并行执行的job个数的最大值)
- 严格模式:set hive.mapred.mode=strict;
查询限制 1、对于分区表,必须添加where对于分区字段的条件过滤; 2、order by语句必须包含limit输出限制; 3、限制执行笛卡尔积的查询。
- hive优化
Order By - 对于查询结果做全排序,只允许有一个reduce处理 (当数据量较大时,应慎用。严格模式下,必须结合limit来使用) Sort By - 对于单个reduce的数据进行排序 Distribute By - 分区排序,经常和Sort By结合使用 Cluster By - 相当于 Sort By + Distribute By (Cluster By不能通过asc、desc的方式指定排序规则; 可通过 distribute by column sort by column asc|desc 的方式)
- 控制Hive中Map以及Reduce的数量
- JVM重用:适用场景:小文件过多,task个数过多