Current timestamp in Hive with examples
Contents
Timestamp values in Hive
Timestamp values are required to capture the time along with date values. Hive provides few date functions to get the current timestamp values. The functions are current_timestamp() and unix_timestamp(). The format of the timestamp value is ‘YYYY-MM-DD HH:MM:SS.sss’.
Current_timestamp in Hive
The current_timestamp() returns the current time with the date value in Hive. Here the brackets() are optional. Both current_timestamp and current_timestamp() returns the same values.
|
1 |
Select current_timestamp; |
Unix_timestamp in Hive
Unix_timestamp returns the current Unix timestamp in seconds. Here the brackets () are mandatory.
|
1 |
select unix_timestamp(); |
If we want to convert the seconds to readable timestamp format, we can use from_unixtime() function to do that. The function from_unixtime() is convert the seconds from unix epoch (1970-01-01 00:00:00 UTC) to a timestamp string.
|
1 |
select from_unixtime(unix_timestamp()); |
Insert timestamp value in Hive table
Timestamp is one of the data type in Hive that we can define for the columns as below.
|
1 |
Create table test_table (column1 timestamp); |
If we try to insert the current_timestamp value as below, it will throw an error in Hive.
|
1 |
Insert into test_table values (current_timestamp); |
FAILED: SemanticException [Error 10293]: Unable to create temp file for insert values Expression of type TOK_FUNCTION not supported in insert/values
How to resolve this error? Only the select query returns the current timestamp value in Hive. So we need to use insert into select current_timestamp syntax that will resolve this error. In this case, we need one target table and one dummy table from which we will select the values with current_timestamp.
|
1 2 3 4 |
-- target table hive> create table test_server_log(activity string,event_timestamp timestamp); -- dummy table hive> create table test_server_actions(activity string); |
The dummy table test_server_actions contains the list of actions related to server such as login,logout,restart and so on.
Lets write the insert query to add server actions with timestamp into the target table test_server_log.
|
1 2 |
insert into test_server_log select *,current_timestamp from test_server_actions; |
Output
The insert query got completed successfully and the current timestamp value is added in the target Hive table. Similarly we can use this method for other timestamp function from_unixtime(unix_timestamp()).
Recommended Articles
