0

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?

o-90
  • 17,045
  • 10
  • 39
  • 63
ishita shukla
  • 1
  • 2

2 Answers2

1

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

Community
  • 1
  • 1
Pranav Shukla
  • 2,206
  • 2
  • 17
  • 20
1

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

Shubhangi
  • 2,229
  • 2
  • 14
  • 14
  • your first line gave me the answer >INSERT INTO TABLE tbl_t SELECT t_id,from_unixtime(unix_timestamp(c_date ,'dd/MM/yyyy'), 'yyyy-MM-dd HH:mm:ss'),name FROM tbl_s; This worked fine .. Thank you – ishita shukla May 27 '16 at 16:45