CODEWITHSUNDEEP
hadoophive
ishita shukla
ishita shukla

Reputation: 1

inserting data in hive from one table to another so as to convert mm/dd/yyyy to hive timestamp

I have to populate data into a Hive table having timestamp as data-type. Referring to other answers here I understand the method to create a new table with data type as string and then do the conversion to yyyy-mm-dd as I have the format of mm/dd/yyyy.

CREATE TABLE tbl_t (
    t_id    varchar(50), 
    c_date  timestamp,
    name    varchar(25)
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t';

CREATE TABLE tbl_s (
    t_id    varchar(50), 
    c_date  string,
    name    varchar(25)
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t';

I populated tbl_s with

aa  11/08/1994  hhh
ss  11/09/2011  bbb
mm  09/07/2000  qqq

select t_id
  , from_unixtime(unix_timestamp(c_date ,'MM/dd/yyyy'),'yyyy-MM-dd')
  , name
from tbl_s;

This works fine. Now I need to populate my orignal table. Using the query:

insert into table tbl_t
select t_id
  , from_unixtime(unix_timestamp(c_date ,'MM/dd/yyyy'),'yyyy-MM-dd')
  , name
from tbl_s;

and then

select * from tbl_t;

which returns

aa  NULL    hhh
ss  NULL    bbb
mm  NULL    qqq

Why are the timestamp columns null?

Upvotes: 0

Views: 1749

Answers (2)

Shubhangi
Shubhangi

Reputation: 2254

Hive contains yyyy-mm-dd hh:mm:ss and optional nanosecond field. Below sql will insert timestamp into table

insert into table tbl_t select t_id, from_unixtime(unix_timestamp(c_date, 'dd/MM/yyyy')),name from tbl_s;

e.g. of contents of table after insert statement.

hive> select * from tbl_t; OK aa 2011-01-11 00:00:00 AAA bb 2012-02-22 00:00:00 BBB cc 2013-03-01 00:00:00 CCC

If you need to insert only YYYY-MM-DD then you may use date type instead of timestamp

Example:

CREATE TABLE tbl_r( t_id varchar(50), c_date date, name varchar(25)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

insert into table tbl_r select t_id, to_date(from_unixtime(unix_timestamp(c_date, 'dd/MM/yyyy'),'yyyy-mm-dd')),name from tbl_s;

hive> select * from tbl_r; OK aa 2010-12-11 AAA bb 2011-12-22 BBB cc 2012-12-01 CCC

Upvotes: 1

Pranav Shukla
Pranav Shukla

Reputation: 2226

You don't need the extra argument 'yyyy-MM-dd' in the from_unixtime function.

insert into table tbl_t select t_id,from_unixtime(unix_timestamp(c_date ,'MM/dd/yyyy')),name from tbl_s;

Please refer to this answer and Hive Date Functions

Upvotes: 1

Related Questions