hive的运行流程
底层原理
hive的三种搭建方式
hive的数据库操作:
创建数据库:create database <dbname>;
删除数据库:drop database <dbname>;
修改数据库:alter database <dbname> set dbproperties
:alter database <dbname> set owner ROLE|USER <name>;
列出数据库:show databases;
查看数据库详细信息:desc database extended <dbname>
hive的表操作:
创建表:create table <tablename> (
fieldName int|string|date|timestamp|array|map|struct comment ‘’,
……
)
partitioned by (fieldName type comment ‘’, fileName type comment ‘’)
row format
delimited
fields terminated by ‘,’
collection items terminated by ‘-’
map keys terminated by ‘:’
lines terminated by ‘\n’
stored as textfile|orcfile|sequencefile
建表:
create table <tablename> like table <tablename1>;
create table <tablename> as select f1,f2… from tablename2;
表分区
为了查询方便,不用遍历整个数据集
设置分区列
添加分区:
alter table tablename add partition (key1=value1, …)
删除分区:
alter table tablename drop partition (key1=value1,…)
-
insert into values(),兼容性不好,不建议使用,同时该方式还需要事务支持。
-
from tablename|(select …)
insert overwrite table tabname
select …… statement where …
inesrt into table tbname2
select statement…
insert into talbe tbname3
select statement…
-
insert into table tbname
select … statement from tbname0 where…
-
load
-
load data [local] inpath ‘filepath’ [overwrite] into table tablename;
外部表和内部表
managed table
一旦删除内部表,hdfs上的数据文件要删除
external table
一旦删除外部表,hdfs上的数据文件不会删除,DBMS中存储的元数据要删除。
hive和beeline
beeline要使用hiveserver2
beeline -u jdbc:hive2://nodex:10000/dbname -n root
beeline> !connect jdbc:hive2://nodex:10000/dbname root abc
beeline> !close
beeline> !quit
hiveserver2的服务可以通过jdbc连接:JAVA通过JDBC操作hive。
自定义函数:
udf:
extends UDF
public String evaluate(String input) {
return “”"
}
-
HIVE案例
掉线率统计
需求:
统计出掉线率最高的前10基站
数据:
record_time:通话时间
imei:基站编号
cell:手机编号
drop_num:掉话的秒数
duration:通话持续总秒数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U0ofd6HS-1618233479102)(media/3eac9fb562d946514c0ab3f888762b73.png)]
1.建表
create table cell_monitor(
record_time string,
imei string,
cell string,
ph_num int,
call_num int,
drop_num int,
duration int,
drop_rate DOUBLE,
net_type string,
erl string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
STORED AS TEXTFILE;
结果表
create table cell_drop_monitor(
imei string,
total_drop_num int,
total_duration int,
d_rate DOUBLE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
STORED AS TEXTFILE;
2、load数据
LOAD DATA LOCAL INPATH ‘/opt/data/cdr_summ_imei_cell_info.csv’ OVERWRITE INTO
TABLE cell_monitor;
3、找出掉线率最高的基站
from cell_monitor cm
insert overwrite table cell_drop_monitor
select cm.imei
,sum(cm.drop_num),sum(cm.duration),sum(cm.drop_num)/sum(cm.duration) d_rate
group by cm.imei
sort by d_rate desc;
Hive案例Wordcount
1、建表
create table docs(line string);
create table wc(word string, totalword int);
2、加载数据
load data local inpath ‘/tmp/wc’ into table docs;
3、统计
from (select explode(split(line, ’ ')) as word from docs) w
insert into table wc
select word, count(1) as totalword
group by word
order by word;
4、查询结果
select * from wc;
explain sql; 查看sql的执行计划
HIVE参数
hive 参数、变量
hive当中的参数、变量,都是以命名空间开头
通过${}方式进行引用,其中system、env下的变量必须以前缀开头
select “${system:user.name}” from cell_drop_monitor limit 3;
select “${env:JAVA_HOME}” from cell_drop_monitor limit 3;
命名空间 | 读写权限 | 含义 |
---|---|---|
hiveconf | 可读写,范围小 | hive-site.xml当中的各配置变量 例:hive --hiveconf hive.cli.print.header=true |
system | 可读写,范围稍大 | 系统变量,包含JVM运行参数等,跟jre相关的属性信息 例:system:user.name=root |
env | 只读,范围大 | 环境变量,跟操作系统有关的属性信息 例:env:JAVA_HOME |
hivevar | 可读写,范围更小 | 例:hive -d val=key |
hive 参数设置方式
1、修改配置文件 ${HIVE_HOME}/conf/hive-site.xml
2、启动hive cli时,通过–hiveconf key=value的方式进行设置
例:hive --hiveconf hive.cli.print.header=true
3、进入cli之后,通过使用set命令设置
set system:hello=world; create table tb_test(id int); insert into tb_test values(1); select “${system:hello}” from tb_test limit 1; hive> select “${system:hello}” from tb_test limit 1; OK world Time taken: 0.082 seconds, Fetched: 1 row(s) bin/hive -d myid=3 hive> select * from tb_user1 where id=${myid}; bin/hive -d myname=‘小明1’ hive> select * from tb_user1 where name="${myname}"; hive> select “${env:PATH}” from tb_user1 limit 1; hive> select “${system:user.dir}” from tb_user1 limit 1; |
---|
hive set命令
在hive CLI控制台可以通过set对hive中的参数进行查询、设置
set设置:
set hive.cli.print.header=true;
set查看
set hive.cli.print.header;
hive参数初始化配置
当前用户家目录下的.hiverc文件
如: ~/.hiverc
如果没有,可直接创建该文件,将需要设置的参数写到该文件中,hive启动运行时,会加载改文件中的配置。
hive历史操作命令集
~/.hivehistory
HIVE动态分区
指定分区:手动指定?
mapreduce分区器动态指定?
让map端的分区器进行分区,就是动态分区。
开启支持动态分区
set hive.exec.dynamic.partition=true; 默认值
默认:true
set hive.exec.dynamic.partition.mode=nonstrict;
默认:strict(至少有一个分区列是静态分区)
相关参数
set hive.exec.max.dynamic.partitions.pernode;
每一个执行mr节点上,允许创建的动态分区的最大数量(100)
set hive.exec.max.dynamic.partitions;
所有执行mr节点上,允许创建的所有动态分区的最大数量(1000)
set hive.exec.max.created.files;
所有的mr job允许创建的文件的最大数量(100000)
建表
create table tb_user1(
id int,
name string,
likes array<string>,
addrs map<string, string>
)
partitioned by (age int, sex string);
create table tb_user2(
id int,
name string,
age int,
sex string,
likes array<string>,
addrs map<string, string>)
row format
delimited
fields terminated by “,”
collection items terminated by “-”
map keys terminated by “:”
lines terminated by “\n”;
数据
11,张三1,23,female,lol-book-movie,beijing:xisanqi-shanghai:pudong
12,张三2,24,male,lol-book-movie,beijing:xisanqi-shanghai:pudong
13,张三3,23,female,lol-book-movie,beijing:xisanqi-shanghai:pudong
14,张三4,24,male,lol-book-movie,beijing:xisanqi-shanghai:pudong
15,张三5,25,male,lol-movie,beijing:xisanqi-shanghai:pudong
16,张三6,24,female,lol-book-movie,beijing:xisanqi-shanghai:pudong
17,张三7,23,female,lol-book,beijing:xisanqi-shanghai:pudong
18,张三8,25,female,lol-book,beijing:xisanqi-shanghai:pudong
19,张三9,25,male,lol-book-movie,beijing:xisanqi-shanghai:Pudong
导数据
load data local inpath “users2.txt” into table tb_user2;
加载数据
from tb_user2
insert overwrite table tb_user1 partition (age, sex)
select id, name, likes, addrs, age, sex distribute by age, sex;
Select的字段顺序要和目标表字段顺序对应,尤其是age和sex。
如果不指定set hive.exec.dynamic.partition.mode=nostrict;
如下语句报错
hive> from tb_user1 > insert overwrite table tb_user partition(age,sex) > select id, name, likes, addrs, age, sex distribute by age, sex; |
---|
但是可以修改为:直接指定分区列age=20
hive> from tb_user1 > insert overwrite table tb_user partition(age=20,sex) > select id, name, likes, addrs, sex distribute by sex; |
---|
HIVE分桶
hive 分桶
注意随机性决定了抽样的准确度:随机
分桶表是对列值取哈希值的方式,将不同数据放到不同文件中存储。
对于hive中每一个表、分区都可以进一步进行分桶。
由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。
适用场景:
数据抽样( sampling )
分桶表抽样
抽样表: TABLESAMPLE (BUCKET x OUT OF y [ON colname])
TABLESAMPLE子句允许用户对表中数据抽样,而不是获取全表数据。
TABLESAMPLE子句可以跟在任何表的from子句之后。桶从1开始计数。colname表示通过哪列进行抽样。colname可以是表中非分区列或者rand()表示对一整行而非单个列进行抽样。表中的记录根据指定的列分桶到y个桶中(从1到y)。返回属于x桶的列。
下面SQL语句表示source表中分桶为32个桶之后返回第三个桶的数据。's’是表的别名:
SELECT * FROM source TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand()) s;
修剪输入
一般情况下,TABLESAMPLE会扫描整张表来获取抽样数据,效率不高。在创建表的时候可以通过clustered
by子句指定一列或几列将表的数据先使用hash分区。如果TABLESAMPLE子句指定的列和clustered
by子句中的列一样,TABLESAMPLE子句只扫描表中需要的hash分区并返回:
如果上面例子中,表source创建的时候使用了’clustered by id into 32 buckets’,则:
3 out of 32
TABLESAMPLE(BUCKET 3 OUT OF 16 ON id)
该语句只会抽取第三个和第十九个桶内数据。也就是每个桶由(32/16)=2个cluster组成。
另外,如果如下抽样,则
TABLESAMPLE(BUCKET 3 OUT OF 64 ON id)
会抽取第三个桶一半的数据,因为每个桶包含(32/64)=1/2个cluster.
开启支持分桶
set hive.enforce.bucketing=true;
默认:false;设置为true之后,mr运行时会根据bucket的个数自动分配reduce
task个数。(用户也可以通过mapred.reduce.tasks自己设置reduce任务个数,但分桶时不推荐使用)
注意:一次作业产生的桶(文件数量)和reduce task个数一致。
往分桶表中加载数据
insert into table bucket_table select columns from tbl;
insert overwrite table bucket_table select columns from tbl;
create table tb_user2 ( id int, name string, likes array<string>, addrs map<string, string> ) clustered by (id) into 4 buckets row format delimited fields terminated by ‘,’ collection items terminated by ‘-’ map keys terminated by ‘:’ lines terminated by ‘\n’; insert into table tb_user2 select id, name, likes, addrs from tb_user1; select * from tb_user2 tablesample(bucket 3 out of 4 on id); select * from tb_user2 tablesample(bucket 3 out of 8 on id); select * from tb_user2 tablesample(bucket 3 out of 2 on id); select * from tb_user2 tablesample(bucket 1 out of 2 on id); |
---|
分桶表抽样查询
select * from bucket_table tablesample(bucket 1 out of 4 on columns);
TABLESAMPLE语法:
TABLESAMPLE(BUCKET x OUT OF y)
x:表示从哪个bucket开始抽取数据 1/2 1/4 1/8
y:必须为该表总bucket数的倍数或因子
例:
当表总bucket数为32时
TABLESAMPLE(BUCKET 3 OUT OF 16),抽取哪些数据?
共抽取2(32/16)个bucket的数据,抽取第2、第18(16+2)个bucket的数据
TABLESAMPLE(BUCKET 3 OUT OF 256),抽取哪些数据?
?
例:
CREATE TABLE psn31( id INT, name STRING, age INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’;
测试数据:
1,tom,11
2,cat,22
3,dog,33
4,hive,44
5,hbase,55
6,mr,66
7,alice,77
8,scala,88
- 创建分桶表
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);
Hive Lateral View
hive Lateral View
Lateral View用于和UDTF函数(explode、split)结合来使用。
首先通过UDTF函数拆分成多行,再将多行结果组合成一个支持别名的虚拟表。
主要解决在select使用UDTF做查询过程中,查询只能包含单个UDTF,不能包含其他字段、以及多个UDTF的问题
语法:
LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias) …
例:
统计人员表中共有多少种爱好、多少个城市?
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ERFe48dr-1618233479105)(media/56cd4bc2546ef3925050e9fc8b03cd61.png)]
select count(distinct(myCol1)), count(distinct(myCol2)) from psn2
LATERAL VIEW explode(likes) myTable1 AS myCol1
LATERAL VIEW explode(address) myTable2 AS myCol2, myCol3;
HIVE视图
hive View视图
和关系型数据库中的普通视图一样,hive也支持视图
特点:
-
不支持物化视图
-
只能查询,不能做加载数据操作
-
视图的创建,只是保存一份元数据,查询视图时才执行对应的子查询
-
view定义中若包含了ORDER BY/LIMIT语句,当查询视图时也进行ORDER
BY/LIMIT语句操作,view当中定义的优先级更高-
view: order by age asc;
-
select order by age desc;
-
select * from view order by age desc;
-
-
view支持迭代视图
-
view1: select * from tb_user1;
-
view2: select * from view1;
-
view3: select * from view2;
-
View语法
创建视图:
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name
[(column_name [COMMENT column_comment], …) ]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, …)]
AS SELECT … ;
查询视图:
select colums from view;
删除视图:
DROP VIEW [IF EXISTS] [db_name.]view_name;
create view statistics (likenum, addrsnum) as
select count(distinct(myTable1.myCol1)), count(distinct(myTable2.myCol1)) from
tb_user1
LATERAL VIEW explode(likes) myTable1 AS myCol1
LATERAL VIEW explode(addrs) myTable2 AS myCol1, myCol3;
create view myv1 (name, age) as select name, age from tb_user2;
HIVE索引
Hive 索引
目的:优化查询以及检索性能
创建索引:
create index t1_index on table tb_user2(name)
as ‘org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’ with deferred
rebuild
in table t1_index_table;
as:指定索引器;
in table:指定索引表,若不指定默认生成在default__tb_user2_t1_index__表中
create index t2_index on table tb_user2(name)
as ‘org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’ with deferred
rebuild;
with deferred rebuild表示在执行alter index xxx_index on xxx
rebuild时将调用generateIndexBuildTaskList获取Index的MapReduce,并执行为索引填充数据。
select * from tb_user2 where likes not null
select * from tb_user2 where name=‘zhangsan’;
查询索引
show index on psn2;
重建索引
(建立索引之后必须重建索引才能生效)
ALTER INDEX t1_index ON tb_user2 REBUILD;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wcu7tuAc-1618233479108)(media/d4b3c8d7babb517525dc2983bebf3ab7.png)]
删除索引
DROP INDEX IF EXISTS t1_index ON psn2;
HIVE运行方式
Hive运行方式:
-
命令行方式cli:控制台模式
-
脚本运行方式(实际生产环境中用最多)
-
JDBC方式:hiveserver2
-
web GUI接口 (hwi、hue等)
-
Hive在CLI模式中可以:
-
与hdfs交互
执行执行dfs命令
例:dfs –ls /;
-
与Linux交互
!开头
例: !pwd
hive --service cli --help
Hive脚本运行方式:
-
hive -e “SQL“ 退出
-
hive -e “SQL">aaa
-
hive -S -e “”>aaa
-
hive -f file
-
hive -i /home/my/hive-init.sql
hive> source file (在hive cli中运行)
直接执行/root/myini2.sql文件中的sql,
hive> source /root/myini2.sql ;
指定hive的数据库并进入hive CLI直接操作 bin/hive --database mydb2 指定数据库,指定要执行的sql执行结束直接退出到shell bin/hive --database mydb2 -e “select * from tb_lines limit 5” bin/hive -e “select * from mydb2.tb_lines limit 5” [root@node4 ~]# cat hive.sql select * from mydb3.tb_user1; select * from mydb2.tb_lines limit 5; -f指定要执行哪个文件中的sql语句 [root@node4 ~]# hive -f hive.sql [root@node4 ~]# cat myini.sql use mydb3; create table tb_user111 ( id int, name string, likes array<string>, addrs map<string, string> ) row format delimited fields terminated by ‘,’ collection items terminated by ‘-’ map keys terminated by ‘:’ lines terminated by ‘\n’; load data local inpath ‘/root/users.txt’ into table tb_user111; 在进入hive的cli之前先执行指定文件内的SQL语句进行初始化操作 [root@node4 ~]# hive -i myini.sql 大S表示静默模式执行 [root@node4 ~]# hive -S -e “use mydb2; select * from tb_lines limit 5” hello bjsxt 1 hello bjsxt 2 hello bjsxt 3 hello bjsxt 4 hello bjsxt 5 [root@node4 ~]# -v表示将执行的sql打印到控制台 [root@node4 ~]# hive -v -e “use mydb2; select * from tb_lines limit 5” Logging initialized using configuration in jar:file:/root/hive-client/lib/hive-common-1.2.1.jar!/hive-log4j.properties use mydb2 OK Time taken: 1.479 seconds select * from tb_lines limit 5 OK hello bjsxt 1 hello bjsxt 2 hello bjsxt 3 hello bjsxt 4 hello bjsxt 5 Time taken: 0.879 seconds, Fetched: 5 row(s) |
---|
HIVE的GUI接口
Hive Web GUI接口
web界面安装:
下载源码包apache-hive-*-src.tar.gz
将hwi war包放在$HIVE_HOME/lib/
制作方法:将hwi/web/*里面所有的文件打成war包
cd apache-hive-1.2.1-src/hwi/web
jar -cvf hive-hwi.war *
复制tools.jar(在jdk的lib目录下)到$HIVE_HOME/lib下
修改hive-site.xml
修改hive配置文件hive-site.xml添加以下配置内容:
启动metastore服务的node3上配置该信息
<property>
<name>hive.hwi.listen.host</name>
<value>0.0.0.0</value>
</property>
<property>
<name>hive.hwi.listen.port</name>
<value>9999</value>
</property>
<property>
<name>hive.hwi.war.file</name>
<value>lib/hive-hwi.war</value>
</property>
启动hwi服务(端口号9999)
hive --service hwi
浏览器通过以下链接来访问
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NNIAjUfV-1618233479112)(media/79153e8f9467b3296ebfde33b1c00ef8.png)]
HIVE权限管理
三种授权模型:
1、Storage Based Authorization in the Metastore Server
基于存储的授权 -
可以对Metastore中的元数据进行保护,但是没有提供更加细粒度的访问控制(例如:列级别、行级别)。
2、SQL Standards Based Authorization in HiveServer2
基于SQL标准的Hive授权 - 完全兼容SQL的授权模型,推荐使用该模式。
3、Default Hive Authorization (Legacy Mode)
hive默认授权 -
设计目的仅仅只是为了防止用户产生误操作,而不是防止恶意用户访问未经授权的数据。
Hive - SQL Standards Based Authorization in HiveServer2
-
完全兼容SQL的授权模型
-
除支持对于用户的授权认证,还支持角色role的授权认证
-
role可理解为是一组权限的集合,通过role为用户授权
-
一个用户可以具有一个或多个角色
-
默认包含两种角色:public、admin
限制:
1、启用当前认证方式之后,dfs, add, delete, compile, and reset等命令被禁用。
2、通过set命令设置hive configuration的方式被限制某些用户使用。
(可通过修改配置文件hive-site.xml中hive.security.authorization.sqlstd.confwhitelist进行配置)
set hive.cli.print.header=true;
3、添加、删除函数以及宏的操作,仅为具有admin的用户开放。
4、用户自定义函数(开放支持永久的自定义函数),可通过具有admin角色的用户创建,其他用户都可以使用。
5、Transform功能被禁用。
配置方式:
在启动metastore服务的节点上修改
在hive服务端修改配置文件hive-site.xml添加以下配置内容:
<property>
<name>hive.security.authorization.enabled</name>
<value>true</value>
</property>
<property>
<name>hive.server2.enable.doAs</name>
<value>false</value>
</property>
<property>
<name>hive.users.in.admin.role</name>
<value>root</value>
</property>
<property>
<name>hive.security.authorization.manager</name>
<value>org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory</value>
</property>
<property>
<name>hive.security.authenticator.manager</name>
<value>org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator</value>
</property>
服务端启动hiveserver2;客户端通过beeline进行连接
Hive权限管理
角色的添加、删除、查看、设置:
-
CREATE ROLE role_name; – 创建角色
-
DROP ROLE role_name; – 删除角色
-
SET ROLE (role_name|ALL|NONE); – 设置角色
-
SHOW CURRENT ROLES; – 查看当前具有的角色
-
SHOW ROLES; – 查看所有存在的角色
[root@node4 hive-client]# bin/beeline -u jdbc:hive2://node3:10000/mydb3 -n root 0: jdbc:hive2://node3:10000/mydb3> select * from tb_user0; 0: jdbc:hive2://node3:10000/mydb3> show current roles; 0: jdbc:hive2://node3:10000/mydb3> show roles; 0: jdbc:hive2://node3:10000/mydb3> set role admin; 0: jdbc:hive2://node3:10000/mydb3> show roles; 0: jdbc:hive2://node3:10000/mydb3> show current roles; 0: jdbc:hive2://node3:10000/mydb3> select * from tb_user0; |
---|
将角色授予某个用户、角色: GRANT role_name [, role_name] … TO principal_specification [, principal_specification] … [ WITH ADMIN OPTION ]; principal_specification : USER user | ROLE role | 移除某个用户、角色的角色: REVOKE [ADMIN OPTION FOR] role_name [, role_name] … FROM principal_specification [, principal_specification] … ; principal_specification : USER user | ROLE role |
---|---|
查看授予某个用户、角色的角色列表 SHOW ROLE GRANT (USER|ROLE) principal_name; | 查看属于某种角色的用户、角色列表 SHOW PRINCIPALS role_name; |
Hive权限管理
权限:
-
SELECT privilege – 赋予对象读取的权限
-
INSERT privilege – 赋予向对象(表)添加数据的权限
-
UPDATE privilege – 赋予对对象(表)运行更新查询的权限
-
DELETE privilege – 赋予从对象(表)删除数据的权限
-
ALL PRIVILEGES – 赋予所有上述权限
将权限授予某个用户、角色: GRANT priv_type [, priv_type ] … ON table_or_view_name TO principal_specification [, principal_specification] … [WITH GRANT OPTION]; | 移除某个用户、角色的权限: REVOKE [GRANT OPTION FOR] priv_type [, priv_type ] … ON table_or_view_name FROM principal_specification [, principal_specification] … ; |
---|---|
principal_specification : USER user | ROLE role priv_type : INSERT | SELECT | UPDATE | DELETE | ALL | 查看某个用户、角色的权限: SHOW GRANT [principal_name] ON (ALL| ([TABLE] table_or_view_name) |
创建角色:
create role myrole;
给myrole赋权限:
grant select on tb_user1 to ROLE myrole;
将给角色赋值给用户:
grant myrole to USER abc; 不管abc用户是否存在
下次以abc登录就具有相关角色了。
剥夺abc的myrole角色
revoke myrole from USER abc;
hive中所有的权限要求
Y: 要求的权限
Y + G: 要求有"WITH GRANT OPTION"权限
Action | Select | Insert | Update | Delete | Owership | Admin | URL Privilege(RWX Permission + Ownership) |
---|---|---|---|---|---|---|---|
ALTER DATABASE | Y | ||||||
ALTER INDEX PROPERTIES | Y | ||||||
ALTER INDEX REBUILD | Y | ||||||
ALTER PARTITION LOCATION | Y | Y (for new partition location) | |||||
ALTER TABLE (all of them except the ones above) | Y | ||||||
ALTER TABLE ADD PARTITION | Y | Y (for partition location) | |||||
ALTER TABLE DROP PARTITION | Y | ||||||
ALTER TABLE LOCATION | Y | Y (for new location) | |||||
ALTER VIEW PROPERTIES | Y | ||||||
ALTER VIEW RENAME | Y | ||||||
ANALYZE TABLE | Y | Y | |||||
CREATE DATABASE | Y (if custom location specified) | ||||||
CREATE FUNCTION | Y | ||||||
CREATE INDEX | Y (of table) | ||||||
CREATE MACRO | Y | ||||||
CREATE TABLE | Y (of database) | Y (for create external table – the location) | |||||
CREATE TABLE AS SELECT | Y (of input) | Y (of database) | |||||
CREATE VIEW | Y + G | ||||||
DELETE | Y | ||||||
DESCRIBE TABLE | Y | ||||||
DROP DATABASE | Y | ||||||
DROP FUNCTION | Y | ||||||
DROP INDEX | Y | ||||||
DROP MACRO | Y | ||||||
DROP TABLE | Y | ||||||
DROP VIEW | Y | ||||||
DROP VIEW PROPERTIES | Y | ||||||
EXPLAIN | Y | ||||||
INSERT | Y | Y (for OVERWRITE) | |||||
LOAD | Y (output) | Y (output) | Y (input location) | ||||
MSCK (metastore check) | Y | ||||||
SELECT | Y | ||||||
SHOW COLUMNS | Y | ||||||
SHOW CREATE TABLE | Y+G | ||||||
SHOW PARTITIONS | Y | ||||||
SHOW TABLE PROPERTIES | Y | ||||||
SHOW TABLE STATUS | Y | ||||||
TRUNCATE TABLE | Y | ||||||
UPDATE | Y |
HIVE优化
核心思想:把Hive SQL 当做Mapreduce程序去优化
以下SQL不会转为Mapreduce来执行
-
select仅查询本表字段
-
where仅对本表字段做条件过滤
Explain 显示执行计划
- EXPLAIN [EXTENDED] query
Hive抓取策略:
Hive中对某些情况的查询不需要使用MapReduce计算,可以将MapReduce作业转换为fetch操作。
抓取策略
set hive.fetch.task.conversion=none/more;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b98AS387-1618233479115)(media/2c75d446d3a2393326047178a7e47980.png)]
将指定的操作由mapreduce作业的形式转换为hdfs fetch的形式。
Hive运行方式:
-
本地模式
-
集群模式
本地模式
开启本地模式:
set hive.exec.mode.local.auto=true;
注意:
hive.exec.mode.local.auto.inputbytes.max默认值为128M
表示加载文件的最大值,若大于该配置仍会以集群方式来运行!
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JPD2pF0d-1618233479116)(media/a901e5074b2d2f5f2161ec206a468bb8.png)]
并行计算
通过设置以下参数开启并行模式:
set hive.exec.parallel=true;
注意:hive.exec.parallel.thread.number
(一次SQL计算中允许并行执行的job个数的最大值)
select * from
(select count(*) from tb_user2) a,
(select count(*) from tb_user2) b;
严格模式
通过对执行的SQL的限制,防止用户执行可能会产生意想不到效果的语句。
通过设置以下参数开启严格模式:
set hive.mapred.mode=strict;
(默认为:nonstrict非严格模式)
查询限制:
1、对于分区表,必须添加where对于分区字段的条件过滤;
2、order by语句必须包含limit输出限制;
3、限制执行笛卡尔积的查询。
Hive排序
Order By - 对于查询结果做全排序,只允许有一个reduce处理
(当数据量较大时,应慎用。严格模式下,必须结合limit来使用)
Sort By -
对于单个reduce的数据进行排序,对于有多个reducer的查询,不能保证reducer之间数据有序,但可以保证单个reducer内部有序。
Distribute By - 分区排序,经常和Sort By结合使用
Cluster By - 相当于 Sort By + Distribute By
(Cluster By不能通过asc、desc的方式指定排序规则;
可通过 distribute by column sort by column asc|desc 的方式)
Hive Join
1、Join计算时,将小表(驱动表)放在join的左边
2、Map Join:在Map端完成Join
两种实现方式:
1、SQL方式,在SQL语句中添加MapJoin标记(mapjoin hint)
语法:
SELECT /*+ MAPJOIN(smallTable) */ smallTable.key, bigTable.value
FROM smallTable JOIN bigTable ON smallTable.key = bigTable.key;
2、开启自动的MapJoin
自动的mapjoin
通过修改以下配置启用自动的mapjoin:
set hive.auto.convert.join = true;
(该参数为true时,Hive自动对左边的表统计量,如果是小表就加入内存,即对小表使用Map
join)
相关配置参数:
hive.mapjoin.smalltable.filesize;
(大表小表判断的阈值,如果表的大小小于该值则会被加载到内存中运行)
hive.ignore.mapjoin.hint;
(默认值:true;是否忽略mapjoin hint 即mapjoin标记)
hive.auto.convert.join.noconditionaltask;
(默认值:true;将普通的join转化为普通的mapjoin时,是否将多个mapjoin转化为一个mapjoin)
hive.auto.convert.join.noconditionaltask.size;
(将多个mapjoin转化为一个mapjoin时,其表的最大值)
尽可能使用相同的连接键(会转化为一个MapReduce作业)
大表join大表
空key过滤:有时join超时是因为某些key对应的数据太多,而相同key对应的数据都会发送到相同的reducer上,从而导致内存不够。此时我们应该仔细分析这些异常的key,很多情况下,这些key对应的数据是异常数据,我们需要在SQL语句中进行过滤。
空key转换:有时虽然某个key为空对应的数据很多,但是相应的数据不是异常数据,必须要包含在join的结果中,此时我们可以表a中key为空的字段赋一个随机的值,使得数据随机均匀地分不到不同的reducer上
Map-Side聚合
通过设置以下参数开启在Map端的聚合:
set hive.map.aggr=true;
相关配置参数:
hive.groupby.mapaggr.checkinterval:
map端group by执行聚合时处理的多少行数据(默认:100000)
hive.map.aggr.hash.min.reduction:
进行聚合的最小比例(预先对100000条数据做聚合,若聚合之后的数据量/100000的值大于该配置0.5,则不会聚合)
hive.map.aggr.hash.percentmemory:
map端聚合使用的内存的最大值
hive.map.aggr.hash.force.flush.memory.threshold:
map端做聚合操作是hash表的最大可用内容,大于该值则会触发flush
hive.groupby.skewindata
是否对GroupBy产生的数据倾斜做优化,默认为false
合并小文件
文件数目小,容易在文件存储端造成压力,给hdfs造成压力,影响效率
设置合并属性
是否合并map输出文件:hive.merge.mapfiles=true
是否合并reduce输出文件:hive.merge.mapredfiles=true;
合并文件的大小:hive.merge.size.per.task=256*1000*1000
去重统计
数据量小的时候无所谓,数据量大的情况下,由于COUNT DISTINCT操作需要用一个Reduce
Task来完成,这一个Reduce需要处理的数据量太大,就会导致整个Job很难完成,一般COUNT
DISTINCT使用先GROUP BY再COUNT的方式替换
控制Hive中Map以及Reduce的数量
Map数量相关的参数
参数 | 说明 |
---|---|
mapred.max.split.size | 一个split的最大值,即每个map处理文件的最大值 |
mapred.min.split.size.per.node | 一个节点上split的最小值 |
mapred.min.split.size.per.rack | 一个机架上split的最小值 |
Reduce数量相关的参数
参数 | 说明 |
---|---|
mapred.reduce.tasks | 强制指定reduce任务的数量 |
hive.exec.reducers.bytes.per.reducer | 每个reduce任务处理的数据量 |
hive.exec.reducers.max | 每个任务最大的reduce数 |
Hive - JVM重用
适用场景:
1、小文件个数过多
2、task个数过多
通过 set mapred.job.reuse.jvm.num.tasks=n; 来设置
(n为task插槽个数)
缺点:设置开启之后,task插槽会一直占用资源,不论是否有task运行,直到所有的task即整个job全部执行完成时,才会释放所有的task插槽资源!
是否合并map输出文件:hive.merge.mapfiles=true
是否合并reduce输出文件:hive.merge.mapredfiles=true;
合并文件的大小:hive.merge.size.per.task=256*1000*1000
去重统计
数据量小的时候无所谓,数据量大的情况下,由于COUNT DISTINCT操作需要用一个Reduce
Task来完成,这一个Reduce需要处理的数据量太大,就会导致整个Job很难完成,一般COUNT
DISTINCT使用先GROUP BY再COUNT的方式替换
控制Hive中Map以及Reduce的数量
Map数量相关的参数
参数 | 说明 |
---|---|
mapred.max.split.size | 一个split的最大值,即每个map处理文件的最大值 |
mapred.min.split.size.per.node | 一个节点上split的最小值 |
mapred.min.split.size.per.rack | 一个机架上split的最小值 |
Reduce数量相关的参数
参数 | 说明 |
---|---|
mapred.reduce.tasks | 强制指定reduce任务的数量 |
hive.exec.reducers.bytes.per.reducer | 每个reduce任务处理的数据量 |
hive.exec.reducers.max | 每个任务最大的reduce数 |
Hive - JVM重用
适用场景:
1、小文件个数过多
2、task个数过多
通过 set mapred.job.reuse.jvm.num.tasks=n; 来设置
(n为task插槽个数)
缺点:设置开启之后,task插槽会一直占用资源,不论是否有task运行,直到所有的task即整个job全部执行完成时,才会释放所有的task插槽资源!