hadoop - hive date & time stamp from unix_timestamp()


Keywords:hadoop 


Question: 

I need two columns to be inserted with current date(sysdate) and time stamp. I have created the table and inserting data using unix_timestamp. I am not able to convert into hive date and time stamp format.

############ Hive create table   #############
create table informatica_p2020.M23_MD_LOC_BKEY(
group_nm string,
loc string,
natural_key string,
loc_sk_id int,
**load_date date,
load_time timestamp)**
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/user/spanda20/informatica_p2020/infor_external/m23_md_loc/m23_md_loc_bkey/';

############### Insert into Table   ##########
insert overwrite table M23_MD_LOC_BKEY select 'M23' as group_nm,loc,concat('M23','|','LOC') as NATURAL_KEY,
ROW_NUMBER() OVER () as loc_sk_id,
from_unixtime(unix_timestamp(), 'YYYY-MM-DD'),
from_unixtime(unix_timestamp(), 'YYYY-MM-DD HH:MM:SS.SSS') from M23_MD_LOC LIMIT 2 ;

################output of the insert query ############
M23     SY_BP   M23|LOC 1       **2015-07-183**     2015-07-**183** 16:07:00.000
M23     SY_MX   M23|LOC 2       2015-07-183     2015-07-183 16:07:00.000

Regards Sanjeeb


1 Answer: 

instead of from_unixtime(unix_timestamp(), 'YYYY-MM-DD') try - from_unixtime(unix_timestamp(), 'yyyy-MM-dd')