Hive partition with wildcard

Question

I am very new to partition.

Suppose I have the following table

table mytable(mytime timestamp, myname string)

where the column mytime is like this: year-month-day hour:min:sec.msec (for example,2014-12-05 08:55:59.3131)

I want to partition mytable based on year-month-day of mytime

For example,I want to make a partition for 2014-12-05

The record which has mytime like 2014-12-05 08:55:59,3131 will be in this partition.

So the query like select * from mytable where mytime='2014-12-05%' will search the

partition.

How can I do that in hive?

I already have data in mytable, do I need to recreate mytable and reload all the data?

Thank you

Answer 1

input

1997-12-31 23:59:59.999,kishore
2014-12-31 23:59:59.999999,manish

create table mytable_tmp(mytime string,myname string)
row format delimited
fields terminated by ',';

load data local inpath 'input.txt'
overwrite into table mytable_tmp;



create table mytable(myname string,mytimestamp string)
PARTITIONED BY (mydate string)
row format delimited
fields terminated by ',';


SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;

INSERT OVERWRITE TABLE mytable PARTITION(mydate) 
SELECT myname,mytime,to_date(mytime) from  mytable_tmp;


select * from mytable where mydate='2014-12-31';



manish  2014-12-31 23:59:59.999999  2014-12-31

there is partition mydate which include myname and mytime according to your problem;