1、Hive 分区 partition:必须在表定义的时候指定对应的partition字段
单分区建表语句:create table day_table (id int, content string) partitioned by (dt string);
单分区表,按天分区,在表结构中存在id,content,dt三列
以dt为文件夹区分
双分区建表语句:
create table day_hour_table (id int, content string) partitioned by (dt string, hour string);
双分区表,按天和小时分区,在表结构中新增加了dt和hour两列
先以dt为文件夹,再以hour子文件夹区分
2、Hive查询执行分区语法
SELECT day_table.* FROM day_table WHERE day_table.dt>= '2008-08-08';
分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段,就会全部扫描
3、预先导入分区数据,但是无法识别怎么办
msck repair table tablename
直接添加分区
1、创建静态单分区表:
hive>
> create table student_static_partion1
> (
> id int,
> name String,
> likes array<String>,
> address map<String, String>
> )
> partitioned by (age int)
> row format delimited
> fields terminated by ','
> collection items terminated by '-'
> map keys terminated by ':';
OK
Time taken: 1.963 seconds
2、导入本地数据:当数据被加载至表中时,不会对数据进行任何转换。
Load操作只是将数据复制至Hive表对应的位置。数据加载时在表下自动创建一个目录
hive> load data local inpath '/opt/software/data/student' into table student_static_partion1 partition(age=16);
Loading data to table default.student_static_partion1 partition (age=16)
OK
Time taken: 2.432 seconds
hive>
3、查询导入数据:
hive> select * from student_static_partion1;
OK
1 小红1 ["王者","book","movie"] {"modu":"renminglu","shenzheng":"futian"} 16
2 小红2 ["王者","book","movie"] {"modu":"renminglu","xizhang":"lasha"} 16
3 小红3 ["吃鸡","book","movie"] {"chongqing":"renminglu","shenzheng":"futian"} 16
4 小红4 ["王者","book","movie"] {"modu":"renminglu","dongguang":"changan"} 16
5 小红5 ["walking","book","movie"] {"modu":"renminglu","shenzheng":"futian"} 16
6 小红6 ["王者","book","movie"] {"nanchang":"renminglu","shenzheng":"futian"} 16
7 小红7 ["王者","book","movie"] {"modu":"renminglu","shenzheng":"futian"} 16
8 小红8 ["walking","book","movie"] {"nanchang":"renminglu","guangzhou":"niwan"} 16
9 小红9 ["王者","book","movie"] {"modu":"renminglu","shenzheng":"futian"} 16
10 小红10 ["王者","book","movie"] {"shanghai":"renminglu","shenzheng":"futian"} 16
Time taken: 1.858 seconds, Fetched: 10 row(s)
hive>
=========================================================
创建静态双分区:
hive> create table student_static_partition2
> (
> id int,
> name String,
> likes array<String>,
> address map<String, String>
> )
> partitioned by (age int,sex String)
> row format delimited
> fields terminated by ','
> collection items terminated by '-'
> map keys terminated by ':';
OK
Time taken: 0.159 seconds
查看分区信息:
hive> desc formatted student_static_partition2;
OK
# col_name data_type comment
id