How to create hive table with date format 'dd-MMM-yyyy'?

0
votes

I,m trying to create a hive table for importing csv data into table where the date format in the csv file is 'dd-MMM-yyyy' (for example 20-Mar-2018). When i created table in hive it turns out the entire column of date into null values. Can anyone suggest me how to figure out this?

My Query:

create external table new_stock (Symbol String,Series String,Dat date,Prev_Close float,Open_Price float,High_Price float,Low_Price float,Last_Price float,Close_Price float,Avg_Price float,Volume int,Turn_Over float,Trades int,Del_Qty int,DQPQ_Per float) row format delimited fields terminated by ',' stored as textfile LOCATION '/stock_details/'
1
stringdatehivehiveql
Create table with STRING column and then convert date during select like in this answer: stackoverflow.com/a/47301792/2700344 – leftjoin
Use yyyy-MM-dd to convert to Hive compatible date – leftjoin
@leftjoin, i tried and it worked. thanks – Arunachalam

1 Answers

0
votes

Finally some help from @leftjoin, i solved the problem of converting string date with format (dd-MMM-yyyy) to (dd-MM-yyyy) by using select query. It would work fine.

select from_unixtime(unix_timestamp(columnname ,'dd-MMM-yyyy'), 'dd-MM-yyyy') from tablename;