Hive 分区--静态分区、动态分区

Hive静态分区

1、Hive 分区 partition:必须在表定义的时候指定对应的partition字段

     单分区建表语句:create table day_table (id int, content string) partitioned by (dt string);

                                  单分区表,按天分区,在表结构中存在idcontentdt三列

                                   以dt为文件夹区分

      双分区建表语句:

      create table day_hour_table (id int, content string) partitioned by (dt string, hour string);

      双分区表,按天和小时分区,在表结构中新增加了dthour两列

      先以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 
  • 1
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值