CODEWITHSUNDEEP
sqlhadoophivetimestamphiveddl
Aditya Verma
Aditya Verma

Reputation: 241

I want to add an extra column in my existing hive table so that I can have a current time stamp for that day

Need to add extra date column in my existing hive table so that it picks up the current date from the system for that day

hive (hivejobs)>
select * from my_current_Table;
OK
name   age
Aditya  2
Aditya  7

I want to add a date column here so that i can get the current system date for that day as soon as I add a column. I think the work around would be to do a join of two tables with other table with the current system date.

below is my code and thought process.

alter table my_current_Table add columns( todays_date current_date());

this gives me an error , I am unable to find the actual way to do it

please help.

expected output

hive (hivejobs)> 
select * from my_current_Table;

OK
name   age  todays_date
Aditya  2   2019-02-08 13:21:50
Aditya  7   2019-02-08 13:21:50

Upvotes: 2

Views: 6594

Answers (2)

Ajay Kharade
Ajay Kharade

Reputation: 1525

Below are the steps for same,

  1. Create a temporary table

create table my_current_Table_temp(name string, age int);

  1. Insert data using a file or existing table into the my_current_Table_temp table:

name|age

Aditya | 2

Aditya | 7

  1. Create a table which will contain your final data:

create table my_current_Table(name string, age tinyint, todays_date string);

  1. Insert data from the temporary table and with that also add the columns with default value as current date and time:

insert overwrite table my_current_Table select name, age, FROM_UNIXTIME( UNIX_TIMESTAMP(), 'dd/MM/YYYY HH:mm') from my_current_Table_temp;

Upvotes: 1

leftjoin
leftjoin

Reputation: 38335

You cannot define calculated column in Hive table.

If you want always query today's date, use current_timestamp function in the select:

select t.*, 
       date_format(current_timestamp,'yyyyMMdd HH:mm:ss') as todays_date 
  from my_current_Table t;

If you want to add column and store the date when the record was inserted, add column to the table and reload from itself, for example like this:

alter table my_current_Table  add columns(load_date string);

insert overwrite table my_current_Table 
select t.*, 
           date_format(current_timestamp,'yyyyMMdd HH:mm:ss') as load_date
      from my_current_Table t;

Upvotes: 3

Related Questions