Hive存储格式

hive的存储格式

hive的存储格式分为两大类：一类纯文本文件，一类是二进制文件存储。

第一类： 纯文本文件存储

textfile: 纯文本文件存储格式，不压缩，也是hive的默认存储格式，磁盘开销大，数据解析开销大
复制代码

第二类：二进制文件存储

- sequencefile:
    会压缩，不能使用load方式加载数据
- parquet:
    会压缩，不能使用load方式加载数据
- rcfile:
    会压缩，不能load。查询性能高，写操作慢，所需内存大，计算量大。此格式为行列混合存储，hive在该格式
    下，会尽量将附近的行和列的块存储到一起。
- orcfile:rcfile的升级版

复制代码

存储格式的配置项

<property>
    <name>hive.default.fileformat</name>
    <value>TextFile</value>
    <description>
    Expects one of [textfile, sequencefile, rcfile, orc].
    Default file format for CREATE TABLE statement. Users can explicitly override it by CREATE TABLE ... STORED AS [FORMAT]
    </description>
</property>
复制代码

案例测试：

案例1：textfile

create external table if not exists stocks_1 (
exchange1 string,
symbol string,
ymd string,
price_open float,
price_high float,
price_low float,
price_close float,
volume int,
price_adj_close float
)
row format delimited
fields terminated by ','
stored as textfile;

load data local inpath './data/stocks.csv' into table stocks_1;
在linux的命令行上使用hdfs dfs -put方法去上传到指定目录下。
复制代码

文末扫码有惊喜！

案例2： sequencefile

create external table if not exists stocks_seq_1 (
exchange1 string,
symbol string,
ymd string,
price_open float,
price_high float,
price_low float,
price_close float,
volume int,
price_adj_close float
)
row format delimited
fields terminated by ','
stored as sequencefile
;

使用insert into的方式加载数据
from stocks_1
insert into stocks_seq_1 select *
;

或者使用克隆的方式：
create table stocks_seq_2 stored as sequencefile as select * from stocks_1;
复制代码

案例3：parquet

create external table if not exists stocks_parquet (
exchange1 string,
symbol string,
ymd string,
price_open float,
price_high float,
price_low float,
price_close float,
volume int,
price_adj_close float
)
row format delimited
fields terminated by ','
stored as parquet
;

使用insert into的方式加载数据
from stocks_1
insert into stocks_parquet select *
;

或者使用克隆的方式：
create table stocks_parquet_1 stored as parquet as select * from stocks_1;
复制代码

案例4：rcfile

create external table if not exists stocks_rcfile (
exchange1 string,
symbol string,
ymd string,
price_open float,
price_high float,
price_low float,
price_close float,
volume int,
price_adj_close float
)
row format delimited
fields terminated by ','
stored as rcfile
;

使用insert into的方式加载数据
from stocks_1
insert into stocks_rcfile select *
;

或者使用克隆的方式：
create table stocks_rcfile_2 stored as rcfile as select * from stocks_1;
复制代码

案例5：orcfile

create external table if not exists stocks_orcfile (
exchange1 string,
symbol string,
ymd string,
price_open float,
price_high float,
price_low float,
price_close float,
volume int,
price_adj_close float
)
row format delimited
fields terminated by ','
stored as orcfile
;

使用insert into的方式加载数据
from stocks_1
insert into stocks_orcfile select *
;

或者使用克隆的方式：
create table stocks_orcfile_2 stored as orcfile as select * from stocks_1;
复制代码

测试性能：

select count(*) from stocks_1;
select count(*) from stocks_seq_1;
select count(*) from stocks_parquet;       
select count(*) from stocks_rcfile;
select count(*) from stocks_orcfile;
比较一下上述五个查询所需要的时间

更多大数据精彩内容欢迎B站搜索“千锋教育”或者扫码领取全套资料