本博文的主要内容如下:
Hive文件存储格式
Hive 操作之表操作:创建外、内部表
Hive操作之表操作:表查询
Hive操作之表操作:数据加载
Hive操作之表操作:插入单表、插入多表
Hive语法结构:where 查询、all 和 distinct 选项、基于 Partition 的查询、基于 HAVING 的查询、 LIMIT 限制查询、 GROUP BY 分组查询、 ORDER BY 排序查询、SORT BY 查询、DISTRIBUTE BY 排序查询、CLUSTER BY 查询
Hive操作之视图操作
Hive操作之索引操作
Hive操作之分区操作:创建分区
Hive操作之分区操作:插入数据
Hive操作之分区操作:动态分区
Hive操作之桶操作
Hive操作之符合类型:Struct使用
Hive操作之符合类型:Array使用
Hive操作之符合类型:Map使用
Hive操作之join用法:等连接
Hive操作之join用法:多表连接
Hive操作之join用法:join的缓存和任务转换
Hive操作之join用法:join的结果
Hive操作之join用法:join的过滤
Hive操作之join用法:join的顺序
Hive操作之join用法:map 端 join
Hive操作之Hive 内置操作符与函数:字符串函数
Hive操作之Hive 内置操作符与函数:集合统计函数
Hive操作之复合类型操作:Map类型构建
Hive操作之复合类型操作:Struct 类型构建
Hive操作之复合类型操作: Array 类型构建
Hive操作之用户自定义函数 UDF
Hive操作之Hive 查询优化:join优化
Hive操作之Hive 查询优化:group by 优化
Hive操作之Hive 查询优化:合并小文件
Hive操作之Hive 查询优化:Hive实现(not) in
Hive操作之Hive 查询优化:排序优化
Hive操作之Hive 查询优化:使用分区
Hive操作之Hive 查询优化:Distinct 使用
Hive操作之Hive 查询优化:Hql使用自定义的mapred脚本
Hive操作之Hive 查询优化:UDTF
Hive操作之Hive 查询优化: 聚合函数count和sum
Hive操作之Hive的权限控制
Hive操作之角色的创建和删除
Hive操作之角色的授权和撤销
Hive操作之Hive支持的权限控制
Hive操作之超级管理权限
Hive操作之Hive与JDBC示例
Hive操作之Hive案例分析
Hive文件存储格式包括以下几类:
1、TEXTFILE
2、SEQUENCEFILE
3、RCFILE
4、ORCFILE(0.11以后出现)
其中TEXTFILE为默认格式,建表时不指定默认为这个格式,导入数据时会直接把数据文件拷贝到hdfs上不进行处理。
SEQUENCEFILE,RCFILE,ORCFILE格式的表不能直接从本地文件导入数据,数据要先导入到textfile格式的表中, 然后再从TextFile表中用insert导入SequenceFile,RCFile,ORCFile表中。
当用户的数据文件格式不能被当前Hive所识别的时候,可以自定义文件格式。用户可以通过实现InputFormat和OutputFormat来自定义输入、输出格式。
《其实,这些,都可以看Hadoop应用开发技术详解 刘刚》
更多用法,一定要去看官网啊!!!
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
一、TEXTFILE 格式
默认格式,数据不做压缩,磁盘开销大,数据解析开销大。 可结合Gzip、Bzip2使用(系统自动检查,执行查询时自动解压),但使用这种方式,Hive不会对数据进行切分, 从而无法对数据进行并行操作。
示例:
create table if not exists textfile_table( site string, url string, pv bigint, label string) row format delimited fields terminated by ' ' stored as textfile;
插入数据操作:
Hive> set Hive.exec.compress.output=true; Hive> set mapred.output.compress=true; Hive> set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; Hive> set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec; Hive> insert overwrite table textfile_table select * from textfile_table;
二、SEQUENCEFILE 格式
SequenceFile是Hadoop API提供的一种二进制文件支持,其具有使用方便、可分割、可压缩的特点。 SequenceFile支持三种压缩选择:NONE,RECORD,BLOCK。Record压缩率低,一般建议使用BLOCK压缩。
示例:
create table if not exists seqfile_table( site string, url string, pv bigint, label string) row format delimited fields terminated by ' ' stored as sequencefile;
插入数据操作:
Hive> set Hive.exec.compress.output=true; Hive> set mapred.output.compress=true; Hive> set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; Hive> set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec; Hive> SET mapred.output.compression.type=BLOCK; Hive> insert overwrite table seqfile_table select * from textfile_table;
三、RCFILE 文件格式
RCFILE是一种行列存储相结合的存储方式。首先,其将数据按行分块,保证同一个record在一个块上,避免读一个记录需要读取多个block。其次,块数据列式存储,有利于数据压缩和快速的列存取。
示例:
create table if not exists rcfile_table( site string, url string, pv bigint, label string) row format delimited fields terminated by ' ' stored as rcfile;
插入数据操作:
Hive> set Hive.exec.compress.output=true; Hive> set mapred.output.compress=true; Hive> set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; Hive> set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec; Hive> insert overwrite table rcfile_table select * from textfile_table;
四、ORCFILE()
省略
五、再看TEXTFILE、SEQUENCEFILE、RCFILE三种文件的存储情况:
[hadoop@hadoop1 ~]$ hadoop dfs -dus /user/Hive/warehouse/* hdfs://hadoop@hadoop1:19000/user/Hive/warehouse/hbase_table_1 0 hdfs://hadoop@hadoop1:19000/user/Hive/warehouse/hbase_table_2 0 hdfs://hadoop@hadoop1:19000/user/Hive/warehouse/orcfile_table 0 hdfs://hadoop@hadoop1:19000/user/Hive/warehouse/rcfile_table 102638073 hdfs://hadoop@hadoop1:9000/user/Hive/warehouse/seqfile_table 112497695 hdfs://hadoop@hadoop1:19000/user/Hive/warehouse/testfile_table 536799616 hdfs://hadoop@hadoop1:19000/user/Hive/warehouse/textfile_table 107308067 [hadoop@hadoop1 ~]$ hadoop dfs -ls /user/Hive/warehouse/*/ -rw-r--r-- 2 hadoop supergroup 51328177 2014-03-20 00:42 /user/Hive/warehouse/rcfile_table/000000_0 -rw-r--r-- 2 hadoop supergroup 51309896 2014-03-20 00:43 /user/Hive/warehouse/rcfile_table/000001_0 -rw-r--r-- 2 hadoop supergroup 56263711 2014-03-20 01:20 /user/Hive/warehouse/seqfile_table/000000_0 -rw-r--r-- 2 hadoop supergroup 56233984 2014-03-20 01:21 /user/Hive/warehouse/seqfile_table/000001_0 -rw-r--r-- 2 hadoop supergroup 536799616 2014-03-19 23:15 /user/Hive/warehouse/testfile_table/weibo.txt -rw-r--r-- 2 hadoop supergroup 53659758 2014-03-19 23:24 /user/Hive/warehouse/textfile_table/000000_0.gz -rw-r--r-- 2 hadoop supergroup 53648309 2014-03-19 23:26 /user/Hive/warehouse/textfile_table/000001_1.gz
总结: 相比TEXTFILE和SEQUENCEFILE,RCFILE由于列式存储方式,数据加载时性能消耗较大,但是具有较好的压缩比和查询响应。数据仓库的特点是一次写入、多次读取,因此,整体来看,RCFILE相比其余两种格式具有较明显的优势。
Hive 操作
1、表操作
Hive 和 Mysql 的表操作语句类似,如果熟悉 Mysql,学习Hive 的表操作就非常容易了,下面对 Hive 的表操作进行深入讲解。
1.1、创建表
Hive 的数据表分为两种,内部表和外部表。
内部表:Hive 创建并通过 LOAD DATA INPATH 进数据库的表,这种表可以理解为数据和表结构都保存在一起的数据表。当通过 DROP TABLE table_name 删除元数据中表结构的同时,表中的数据也同样会从 HDFS 中被删除。
外部表:在表结构创建以前,数据已经保存在 HDFS 中,通过创建表结构,将数据格式化到表的结果里。当进行 DROP TABLE table_name 操作的时候,Hive 仅仅删除元数据的表结构,而不删除 HDFS 上的文件,所以,相比内部表,外部表可以更放心大胆地使用。
下面详细介绍对表操作的命令及使用方法:
1) 创建内部表使用 CREATE TABLE 命令。与Mysql 创建表的命令一样,COMMENT 是对字段的注释。例如
Hive> CREATE TABLE IF NOT EXISTS table1(id INT COMMENT 'comment1',name STRING COMMENT 'comment2',no INT COMMENT 'comment3')
2) 创建外部表使用 EXTERNAL 关键字。IF NOT EXISTS 表示如果 table2 表不存在就创建,存在就不创建。例如
Hive> CREATE EXTERNAL TABLE IF NOT EXISTS table2(id INT COMMENT 'comment1',name STRING COMMENT 'comment2',no INT COMMENT 'comment3');
3) 删除表。数据表在删除的时候,内部表会连数据一起删除,而外部表只删除表结构,数据还是保留的。删除表的命令如下。
Hive> DROP TABLE table1;
Hive> DROP TABLE table1 cascade; //当table里有数据的时候上面的命令行是无法删除table的。加上cascade强制删除
4) 改表结构。例如对 table2 表添加两个字段 data_time 和 password,操作命令如下。
Hive> ALTER TABLE table2 ADD COLUMNS(data_time STRING COMMENT 'comment1',password STRING COMMENT 'comment2');
COMMENT是注释作用
5) 修改表名。例如把 table2 表重命名为 table3 ,操作命令如下。
Hive> ALTER TABLE table2 RENAME TO table3;
这个命令可以让用户为表更名,数据所在的位置和分区名并不改变。换而言之,旧的表名并未“释放” ,对旧表的更改会改变新表的数据。
6) 创建与已知表相同结构的表。例如创建一个与 table2 表结构相同的表,表名为
copy_table2,这里要用到 LIKE 关键字,操作命令如下。
Hive> CREATE TABLE copy_table2 LIKE table2;
其实啊,如果是对旧表的复制,将旧表的结构和数据一起复制,来得到新表,则
CREATE TABLE djt_user_copy
as
SELECT * FROM djt_user
其实啊,如果是对旧表的复制,只将旧表的结构复制,来得到新表,则
CREATE TABLE djt_user_copy as SELECT * FROM djt_user WHERE 1=2;
djt_user是旧表,djt_user_copy是新表。跟like的用法相同
1.2、表查询
Hive 的查询语句与标准 SQL 语句类似,具体的语法如下。
SELECT [ALL | DISTINCT] select_expr,select_expr,... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [ CLUSTER BY col_list|[DISTRIBUTE BY col_list] [SORT BY col_list] ] [LIMIT number]
一个 SELECT 语句可以是一个 union 查询或一个子查询的一部分;table_reference 是查询的输入,可以是一个普通表、视图、join或子查询。
首先创建一个 TextFile 格式的表 table1,并指定数据分隔符。
Hive> create table table1(id INT,name STRING,no INT) row format delimited fields terminated by ' ' STORED AS TEXTFILE;
先将 /home/hadoop/zhouls/data.txt 插入表 table1 中。
Hive> LOAD DATA LOCAL INPATH '/home/hadoop/zhouls/data.txt' INTO TABLE table1;
1) 查询 table1 表的所有内容,查询语句如下:
Hive> select * from table1;
OK
1 liugang 1000
2 lisi 1001
3 wangwu 1002
Time taken: 0.075 seconds, Fetched: 3 row(s)
SELECT * 查询没有开启 MapReduce 任务,这是 Hive 查询语句中唯一没有把 Hive 查询语句解释为 MapReduce 任务执行。
2) 查询 table1 表的 name 属性,查询语句如下
Hive> SELECT name FROM table1;
OK
liugang
lisi
wangwu
Time taken: 0.095 seconds, Fetched: 3 row(s)
"SELECT name FROM table1;" 被解释成了一个 MapReduce 任务执行。
1.3、 数据加载
首先创建一个表 table2,table4,必须声明文件格式STORED AS TEXTFILE,否则数据无法加载。
Hive> create table table2(uid STRING,gender STRING,ip STRING) row format delimited fields terminated by ' ' STORED AS TEXTFILE; Hive> create table table4(uid STRING,gender STRING,ip STRING) row format delimited fields terminated by ' ' STORED AS TEXTFILE;
(1) 加载本地数据
加载本地数据使用 LOCAL 关键字,操作如下。
Hive> LOAD DATA LOCAL INPATH '/home/hadoop/djt/user.txt' INTO TABLE table2;
1.4、 插入表
(1) 单表插入
创建一个表 insert_table,表结构和 table2 的结构相同,把 table2 表中的数据插入到新建的表 insert_table 中,代码如下。
Hive> create table insert_table like table2;----复制表结构(不包括旧表的数据) Hive> insert overwrite table insert_table select * from table2;
overwrite 关键字表示如果 insert_table 表中有数据就删除。(覆盖的意思)
(2) 多表插入
在table2中,查询字段 uid 并插入 test_insert1 表,查询字段 uid 并插入 test_insert2 表。操作命令如下。
Hive> create table test_insert1(num INT); Hive> create table test_insert2(num INT);
from table2 insert overwrite table test_insert1 select uid insert overwrite table test_insert2 select uid;
insert 操作的时候,from 子句既可以放在 select 子句后面,也可以放在 insert 子句前面。
Hive 不支持用 insert 语句一条一条地进行插入操作,也不支持 update 操作。数据是以 load 的方式加载到建立好的表中。数据一旦导入就不可以修改。
通过查询将数据保存到 HDFS ,directory 为 HDFS 文件系统的目录。操作命令如下。
Hive> insert overwrite directory '/advance/Hive' select * from table2;
导入数据到本地目录,操作命令如下。
Hive> insert overwrite local directory '/home/hadoop/djt/test2' select * from table2;
产生的文件会覆盖指定目录中的其它文件,即将目录中已经存在的文件进行删除。
同一个查询结果可以同时插入到多个表或者多个目录中,命令如下。
from table2 insert overwrite local directory '/home/hadoop/djt/test2' select * insert overwrite directory '/advance/Hive' select ip;
select * 表示把 table2 表中的所有数据复制到本地 /home/hadoop/djt/test2 目录下面,select ip 表示把 table2 的 ip 字段内容复制到 HDFS 文件系统的 /advance/Hive 目录下。
下面我们逐一学习 Hive 的语法结构,很easy的哦。
2、Hive 的语法结构
(1) where 查询
where 查询是一个布尔表达式。例如,下面的查询语句只返回销售记录大于10,且归属地属于北京的销售代表。Hive 不支持在 where 子句中的 in,exist或子查询。
Hive> select * from sales where amount >10 and region = "beijing";
(2) all 和 distinct 选项
使用 all 和 distinct 选项区分对重复记录的处理。默认是 all,表示查询所有记录,distinct 表示去掉重复的记录。
Hive> select * from sales where amount >10 and region = "beijing";
1) 查询 table1 表中的所有 age、grade 的内容,等同于如下操作。
Hive> select all age,grade from table1;
2) 查询去掉 age 和 grade 重复的记录,操作如下。
Hive> select distinct age ,grade from table1;
3) 查询去掉 age 重复的记录,操作如下。
Hive> select distinct age from table1;
(3) 基于 Partition 的查询
一般 SELECT 查询会扫描整个表(除非是为了抽样查询)。但是如果一个表使用 PARTITIONED BY 子句建表,查询就可以利用分区剪枝(input pruning)的特性,只扫描一个表中它关心的那一部分。Hive 当前的实现是,只有分区断言出现在离 FROM 子句最近的那个 WHERE 子句中,才会启用分区剪枝。例如,page_views 表使用 date 列分区,以下语句只会读取分区为 ‘2013-08-01’ 的数据。
select page_views.* from page_views where page_views.date between '2013-08-01' and '2013-08-31';
(4) 基于 HAVING 的查询
Hive 不支持 HAVING 子句,可以将 HAVING 子句转化为一个子查询。例如以下这条语句 Hive 不支持。
select col1 from table1 group by coll having sum(col2) > 10;
可以用以下查询来表达。
select col1 from (select col1,sum(col2) as col2sum from table1 group by col1) table2 where table2.col2sum > 10;
(5) LIMIT 限制查询
limit 可以限制查询的记录数,查询的结果是随机选择的。下面的语句用来从 table1 表中随机查询 5 条记录。
select * from table1 limit 5;
(6) GROUP BY 分组查询
group by 分组查询在数据统计时比较常用,接下来讲解 group by 的使用。
1) 创建一个表 group_test,表的内容如下。
Hive> create table group_test(uid STRING,gender STRING,ip STRING) row format delimited fields terminated by ' ' STORED AS TEXTFILE;
向 group_test 表中导入数据。
Hive> LOAD DATA LOCAL INPATH '/home/hadoop/djt/user.txt' INTO TABLE group_test;
2) 计算表的行数命令如下。
Hive> select count(*) from group_test;
3) 根据性别计算去重用户数。
首先创建一个表 group_gender_sum
Hive> create table group_gender_sum(gender STRING,sum INT);
将表 group_test 去重后的数据导入表 group_gender_sum。
Hive> insert overwrite table group_gender_sum select group_test.gender,count(distinct group_test.uid) from group_test group by group_test.gender;
同时可以做多个聚合操作,但是不能有两个聚合操作有不同的 distinct 列。下面正确合法的聚合操作语句。
首先创建一个表 group_gender_agg
Hive> create table group_gender_agg(gender STRING,sum1 INT,sum2 INT,sum3 INT);
将表 group_test 聚合后的数据插入表 group_gender_agg。
Hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),count(*),sum(distinct group_test.uid) from group_test group by group_test.gender;
但是,不允许在同一个查询内有多个 distinct 表达式。下面的查询是不允许的。
Hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),count(distinct group_test.ip) from group_test group by group_test.gender;
这条查询语句是不合法的,因为 distinct group_test.uid 和 distinct group_test.ip 操作了uid 和 ip 两个不同的列。
(7) ORDER BY 排序查询
ORDER BY 会对输入做全局排序,因此只有一个 Reduce(多个 Reduce 无法保证全局有序)会导致当输入规模较大时,需要较长的计算时间。使用 ORDER BY 查询的时候,为了优化查询的速度,使用 Hive.mapred.mode 属性。
Hive.mapred.mode = nonstrict;(default value/默认值) Hive.mapred.mode=strict;
与数据库中 ORDER BY 的区别在于,在 Hive.mapred.mode=strict 模式下必须指定limit ,否则执行会报错。
Hive> set Hive.mapred.mode=strict; Hive> select * from group_test order by uid limit 5;
Total jobs = 1
..............
Total MapReduce CPU Time Spent: 4 seconds 340 msec
OK
01 male 192.168.1.2
01 male 192.168.1.32
01 male 192.168.1.26
01 male 192.168.1.22
02 female 192.168.1.3
Time taken: 58.04 seconds, Fetched: 5 row(s)
(8) SORT BY 查询
sort by 不受 Hive.mapred.mode 的值是否为 strict 和 nostrict 的影响。sort by 的数据只能保证在同一个 Reduce 中的数据可以按指定字段排序。
使用 sort by 可以指定执行的 Reduce 个数(set mapred.reduce.tasks=< number>)这样可以输出更多的数据。对输出的数据再执行归并排序,即可以得到全部结果。
Hive> set Hive.mapred.mode=strict; Hive> select * from group_test sort by uid ;
Total MapReduce CPU Time Spent: 4 seconds 450 msec
OK
01 male 192.168.1.2
01 male 192.168.1.32
01 male 192.168.1.26
01 male 192.168.1.22
02 female 192.168.1.3
03 male 192.168.1.23
03 male 192.168.1.5
04 male 192.168.1.9
05 male 192.168.1.8
05 male 192.168.1.29
06 female 192.168.1.201
06 female 192.168.1.52
06 female 192.168.1.7
07 female 192.168.1.11
08 female 192.168.1.21
08 female 192.168.1.62
08 female 192.168.1.88
08 female 192.168.1.42
Time taken: 77.875 seconds, Fetched: 18 row(s)
(9) DISTRIBUTE BY 排序查询
按照指定的字段对数据划分到不同的输出 Reduce 文件中,操作如下。
Hive> insert overwrite local directory '/home/hadoop/djt/test' select * from group_test distribute by length(gender);
此方法根据 gender 的长度划分到不同的 Reduce 中,最终输出到不同的文件中。length 是内建函数,也可以指定其它的函数或者使用自定义函数。
Hive> insert overwrite local directory '/home/hadoop/djt/test' select * from group_test order by gender distribute by length(gender);
order by gender 与 distribute by length(gender) 不能共用。
(10) CLUSTER BY 查询
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。
3、视图操作
1) 创建一个测试表。
Hive> create table test(id int,name string);
OK
Time taken: 0.385 seconds
Hive> desc test;
OK
id int
name string
Time taken: 0.261 seconds, Fetched: 2 row(s)
2) 基于表 test 创建一个 test_view 视图。
Hive> create view test_view(id,name_length) as select id,length(name) from test;
3) 查看 test_view 视图属性。
Hive> desc test_view;
4) 查看视图结果。
视图是指计算机数据库中的视图,是一个虚拟表,即不是实实在在的,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。
一般情况,是多表关联查询的时候,才用视图
索引是为了提高查询速度的,视图是在查询sql的基础上的。
4、索引操作
1) Hive 创建索引。
2) 更新数据。
Hive> alter index user_index on user rebuild;
3) 删除索引
Hive> drop index user_index on user;
4) 查看索引
Hive> show index on user;
5) 创建表和索引案例
Hive> create table index_test(id INT,name STRING) PARTITIONED BY (dt STRING) ROW FORMAT DELIMITED FILEDS TERMINATED BY ',';
创建一个索引测试表 index_test,dt作为分区属性,“ROW FORMAT DELIMITED FILEDS TERMINATED BY ','” 表示用逗号分割字符串,默认为‘