Friday, May 25, 2018

Kudu partition example

create table kudu_partition
(  id string,
load_ts timestamp,
col1 string,
col2 string,
col3 string, 
primary key (id,load_ts))
partition by hash (id) partitions 20,
range (load_ts) 
( partition '2018-05-01' <= values < '2018-06-01',
    partition '2018-06-01' <= values < '2018-07-01',
    partition '2018-07-01' <= values < '2018-08-01',
    partition '2018-08-01' <= values < '2018-09-01',
partition '2018-09-01' <= values < '2018-10-01',
    partition '2018-10-01' <= values < '2018-11-01',
    partition '2018-11-01' <= values < '2018-12-01',
    partition '2018-12-01' <= values < '2019-01-01',
    partition '2019-01-01' <= values < '2019-02-01',
    partition '2019-02-01' <= values < '2019-03-01',
    partition '2019-03-01' <= values < '2019-04-01',
    partition '2019-04-01' <= values < '2019-05-01'
)stored as kudu;


insert into kudu_partition values('abc1',now(),'a','b','c');
insert into kudu_partition values('abc2','2018-05-01','a','b','c');
insert into kudu_partition values('abc3','2018-05-05 21:03:05','a','b','c');

alter table kudu_partition add range partition '2019-05-01' <= values < '2019-06-01';
alter table kudu_partition drop range partition '2018-05-01' <= values < '2018-06-01';


[cdh-vm.dbaglobe.com:21000] > show range partitions kudu_partition;
Query: show range partitions kudu_partition
+---------------------------------------------------------------------+
| RANGE (load_ts)                                                     |
+---------------------------------------------------------------------+
| 2018-06-01T00:00:00.000000Z <= VALUES < 2018-07-01T00:00:00.000000Z |
| 2018-07-01T00:00:00.000000Z <= VALUES < 2018-08-01T00:00:00.000000Z |
| 2018-08-01T00:00:00.000000Z <= VALUES < 2018-09-01T00:00:00.000000Z |
| 2018-09-01T00:00:00.000000Z <= VALUES < 2018-10-01T00:00:00.000000Z |
| 2018-10-01T00:00:00.000000Z <= VALUES < 2018-11-01T00:00:00.000000Z |
| 2018-11-01T00:00:00.000000Z <= VALUES < 2018-12-01T00:00:00.000000Z |
| 2018-12-01T00:00:00.000000Z <= VALUES < 2019-01-01T00:00:00.000000Z |
| 2019-01-01T00:00:00.000000Z <= VALUES < 2019-02-01T00:00:00.000000Z |
| 2019-02-01T00:00:00.000000Z <= VALUES < 2019-03-01T00:00:00.000000Z |
| 2019-03-01T00:00:00.000000Z <= VALUES < 2019-04-01T00:00:00.000000Z |
| 2019-04-01T00:00:00.000000Z <= VALUES < 2019-05-01T00:00:00.000000Z |
| 2019-05-01T00:00:00.000000Z <= VALUES < 2019-06-01T00:00:00.000000Z |
+---------------------------------------------------------------------+

select  *from kudu_partition;