Fork me on GitHub

Hive 文件存储格式对比实验

存储文件的压缩比测试

  存储文件的压缩比总结:ORC > Parquet > textFile

1)TextFile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
(1)创建表,存储数据格式为TEXTFILE
create table log_text (
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 ;
(2)向表中加载数据
hive (default)> load data local inpath '/opt/module/datas/log.data' into table log_text ;
(3)查看表中数据大小
dfs -du -h /user/hive/warehouse/log_text;
18.1 M /user/hive/warehouse/log_text/log.data

2)ORC

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
(1)创建表,存储数据格式为ORC
create table log_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 ;
(2)向表中加载数据
insert into table log_orc select * from log_text ;
(3)查看表中数据大小
dfs -du -h /user/hive/warehouse/log_orc/ ;
2.8 M /user/hive/warehouse/log_orc/000000_0

3)Parquet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
(1)创建表,存储数据格式为parquet
create table log_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 ;
(2)向表中加载数据
insert into table log_parquet select * from log_text ;
(3)查看表中数据大小
dfs -du -h /user/hive/warehouse/log_parquet/ ;
13.1 M /user/hive/warehouse/log_parquet/000000_0

存储文件的查询速度测试

  存储文件的查询速度总结:ORC > TextFile > Parquet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1)TextFile
hive (default)> select count(*) from log_text;
_c0
100000
Time taken: 21.54 seconds, Fetched: 1 row(s)
2)ORC
hive (default)> select count(*) from log_orc;
_c0
100000
Time taken: 20.867 seconds, Fetched: 1 row(s)
3)Parquet
hive (default)> select count(*) from log_parquet;
_c0
100000
Time taken: 22.922 seconds, Fetched: 1 row(s)

-----------------本文结束,感谢您的阅读-----------------