介绍
#表的分类:
内部表:
内部常规表
内部分区表
内部分桶表
外部表:
外部常规表
外部分区表
外部分桶表
分区表
介绍
#分区就是分文件夹
1、分区表实际是就是对要进行分析的文件进行分类管理
2、本质是将相同特征的文件存放在同一个文件夹下,通过文件夹对数据进行分类
3、分区之后在查询时,可以通过添加条件,避免进行全表扫描,提高查询效率
4、分区表又分为静态分区和动态分区
5、分区表是一种优化手段,是锦上添花的东西,一张表可以没有分区,但是查询效率可能会比较低
静态分区
#静态分区就是手动来操作分区的文件夹
#----------------------单级分区-一级文件夹------------------------------
-- 1、数据样例
/*
2021-01-28,Autauga,Alabama,01001,5554,69
2021-01-28,Baldwin,Alabama,01003,17779,225
2021-01-28,Barbour,Alabama,01005,1920,40
2021-01-28,Bibb,Alabama,01007,2271,51
2021-01-28,Blount,Alabama,01009,5612,98
2021-01-29,Bullock,Alabama,01011,1079,29
2021-01-29,Butler,Alabama,01013,1788,60
2021-01-29,Calhoun,Alabama,01015,11833,231
2021-01-29,Chambers,Alabama,01017,3159,76
2021-01-29,Cherokee,Alabama,01019,1682,35
2021-01-30,Chilton,Alabama,01021,3523,79
2021-01-30,Choctaw,Alabama,01023,525,24
2021-01-30,Clarke,Alabama,01025,3150,38
2021-01-30,Clay,Alabama,01027,1319,50
*/
-- 2、创建分区表
drop table t_covid;
create external table t_covid(
dt_value string ,
country string ,
state string ,
country_code string ,
cases int ,
deaths int
)
partitioned by (dt string) -- 这里用来指定分区的字段,字段名字可以随便写
row format delimited fields terminated by ',' -- 自定字段之间的分隔符
;
-- 3、给分区表加载数据
load data local inpath '/root/hive_data/covid-28.dat' into table t_covid partition (dt='2021-01-28');
load data local inpath '/root/hive_data/covid-29.dat' into table t_covid partition (dt='2021-01-29');
load data local inpath '/root/hive_data/covid-30.dat' into table t_covid partition (dt='2021-01-30');
-- 4、查看分区表数据
select * from t_covid; --查看所有分区数据
select * from t_covid where dt='2021-01-28'; -- 查询指定单个分区的数据
select * from t_covid where dt='2021-01-28' or dt='2021-01-29' ; -- 查询指定多个分区的数据
#----------------------单级分区-多级文件夹------------------------------
-- 1、数据样例
/*
2021-02-28,Cleburne,Alabama,01029,1258,28
2021-02-28,Coffee,Alabama,01031,4795,72
2021-02-28,Colbert,Alabama,01033,5686,104
2021-02-28,Conecuh,Alabama,01035,999,23
2021-02-28,Coosa,Alabama,01037,670,19
2021-02-28,Covington,Alabama,01039,3504,87
2021-02-28,Crenshaw,Alabama,01041,1279,47
2021-02-28,Cullman,Alabama,01043,8466,145、
2021-02-29,Dale,Alabama,01045,4235,92
2021-02-29,Dallas,Alabama,01047,3181,108
2021-02-29,DeKalb,Alabama,01049,8052,130
2021-02-29,Elmore,Alabama,01051,8449,131
2021-02-30,Escambia,Alabama,01053,3478,47
2021-02-30,Etowah,Alabama,01055,12359,228
2021-02-30,Fayette,Alabama,01057,1841,37
2021-02-30,Franklin,Alabama,01059,3829,55
2021-02-30,Geneva,Alabama,01061,2205,51
*/
-- 2、创建分区表
drop table t_covid2;
create table t_covid2(
dt_value string ,
country string ,
state string ,
country_code string ,
cases int ,
deaths int
)
partitioned by (month string,dt string) -- 这里用来指定分区的字段,字段名字可以随便写
row format delimited fields terminated by ',' -- 自定字段之间的分隔符
;
-- 3、给分区表加载数据
-- 1月份数据
load data local inpath '/root/hive_data/covid-28.dat' into table t_covid2
partition (month='2021-01',dt='2021-01-28');
load data local inpath '/root/hive_data/covid-29.dat' into table t_covid2
partition (month='2021-01',dt='2021-01-29');
load data local inpath '/root/hive_data/covid-30.dat' into table t_covid2
partition (month='2021-01',dt='2021-01-30');
-- 2月份数据
load data local inpath '/root/hive_data/2_month/covid-28.dat' into table t_covid2
partition (month='2021-02', dt='2021-02-28');
load data local inpath '/root/hive_data/2_month/covid-29.dat' into table t_covid2
partition (month='2021-02', dt='2021-02-29');
load data local inpath '/root/hive_data/2_month/covid-30.dat' into table t_covid2
partition (month='2021-02', dt='2021-02-30');
-- 4、查询数据
select * from t_covid2; -- 查询所有分区
select * from t_covid2 where month = '2021-02'; -- 查询2月份数据
select * from t_covid2 where month = '2021-02' and dt = '2021-02-28'; -- 查询2月28号份数据
-- 手动添加分区
alter table t_covid2 add partition(month='2021-03',dt='2021-03-28');
-- 查看分区文件夹信息
show partitions t_covid2;
分桶表
介绍
1、分桶表和分区表没什么关系
2、分桶表是将表数据分到多个文件,分区表是将数据分到多个文件夹
3、分桶表底层就是MapReduce中分区
4、分桶和分区的区别
1)MapReduce的分区就是Hive的分桶
2)Hive的分桶就是MapReduce的分区
3)Hive的分区和MapReduce分区没什么关系
5、结论:分桶就是分文件
6、分桶的本质就是将大表进行拆分编程小表,小表好join
7、一张表既可以是分区表也可以是分桶表
作用
1、提高Join的效率
2、用于数据的抽样
分区+分桶
-- 内部表
create table A(
dt_value string ,
country string ,
state string ,
country_code string ,
cases int ,
deaths int
)
partitioned by (dt string) -- 分文件夹
clustered by (country_code) into 3 buckets ; -- 文件夹内部再分文件
-- 外部表
create external table A(
dt_value string ,
country string ,
state string ,
country_code string ,
cases int ,
deaths int
)
partitioned by (dt string) -- 分文件夹
clustered by (country_code) into 3 buckets ; -- 文件夹内部再分文件
Hive表数据加载的方式
-- 如何给一张表加载数据
-- 1、创建表
drop table if exists myhive.score2;
create table if not exists myhive.score2
(
sid string,
cid string,
sscore int
)
row format delimited fields terminated by '\t';
-- 2、表加载数据
-- 方式1-insert into命令 #0颗星
insert into score2 values ('08','02',80),('09','02',80),('10','02',80);
-- 方式2-直接通过put命令将文件上传到表目录 #1颗星,测试用
hadoop fs -put score.txt /user/hive/warehouse/myhive.db/score
select * from score2;
-- 方式3-使用load命令加载数据 #4颗星,测试和生成都可以用
load data local inpath '/root/hive_data/test/score.txt' overwrite into table myhive.score2;
-- 方式4-使用insert into select .... #5颗星、保存结果
insert into score2
select * from score where sscore > 80;
-- 方式5-使用create table score5 as select * from score; #1颗星 测试用
-- 先创建表,表的字段和score字段相同,同时score表的数据插入到score3表
create table score3 as select * from score;
-- 方式6-使用第三方框架 #5颗星,生产环境
sqoop框架: MySQL/Oracle ===========> Hive表
Kettle框架: MySQL/Oracle ===========> Hive表
-- 方式7-HDFS先有数据,后有表 ,建表时使用location关键字
create external table t_covid2(
dt string comment '日期' ,
country string comment '县',
state string comment '州',
country_code string comment '县编码',
cases int comment '确诊人数',
deaths int comment '死亡任务'
)comment '美国新冠数据'
row format delimited fields terminated by ',' -- 自定字段之间的分隔符
location '/input/data';
Hive查询的数据如何导出
-- 方式1-使用命令导出到Linux本地目录中
-- 使用默认分隔符 '\001'
insert overwrite local directory '/root/hive_data/export_hive' select * from score where sscore > 85;
-- 手动指定分隔符 ','
insert overwrite local directory '/root/hive_data/export_hive'
row format delimited fields terminated by ','
select * from score where sscore > 85;
-- 方式2-使用命令导出到HDFS目录中(去掉local)
insert overwrite directory '/output/hive_data'
row format delimited fields terminated by ','
select * from score where sscore > 85;
-- 方式3-使用SQL导出到其他表 !!!!!!!!!!!!!!!!!!
insert overwrite into table 目标表
select 字段 from 原表 where 条件;
-- 方式4-使用第三方框架导出其他的存储平台(HBase、Spark、MySQL) !!!!!!!!!!!!
sqoop
Kettle
Datax
Presto