Insert timestamp into Hive

Question

Hi i'm new to Hive and I want to insert the current timestamp into my table along with a row of data.

Here is an example of my team table :

team_id int
fname   string
lname   string
time    timestamp

I have looked at some other examples, How to insert timestamp into a Hive table?, How can I add a timestamp column in hive and can't seem to get it to work. This is what I am trying:

insert into team values('101','jim','joe',from_unixtime(unix_timestamp()));

The error I get is:

FAILED: SemanticException [Error 10293]: Unable to create temp file for insert values Expression of type TOK_FUNCTION not supported in insert/values

If anyone could help, that would be great, many thanks frostie

Solution 1

Can be achieved through current_timestamp() , but only via select clause. don't even require from clause in select statment.

insert into team select '101','jim','joe',current_timestamp();

or if your hive version doesn't support leaving from in select statment

insert into team select '101','jim','joe',current_timestamp() from team limit 1;

Solution 2

If you don't already have a table with at least one row, you can accomplish the desired result as such.

insert into team select '101','jim','joe',current_timestamp() from (select '123') x;

Comments

[Frostie_the_snowman]

I tried the above and got the following error: ParseException line 1:65 Failed to recognize predicate '<EOF>'. Failed rule: 'regularBody' in statement

[sumitya]

Added one more query to get result.

[Frostie_the_snowman]

Hi @syadav this works but doesn't appear in the table, but if I remove the limit 1 it works but inputs multiple. Any reason why this would be happening or how I could fix this?

[sumitya]

what's your hive version? I tested this on Hive 1.2.1.2.3.4.29-1 it works there.

[Frostie_the_snowman]

using Hive 1.2.1000.2.4.0.0-169

[Frostie_the_snowman]

After a while of debugging and testing, I have got the following to work correctly: insert into team select '101','jim','joe',current_timestamp() from team limit 2; Could you please explain how limit works in this command? is it the same as a normal limit on a select query?

[sumitya]

yes it is same as normal query on a table with limit .It is almost equivalent to select * from table if you don't specify any limit you will get all the records from the table. if you want only 1 record then limit is required.

[Frostie_the_snowman]

Perfect, Thanks for all your help

[Paul Lam]

Note that the last query will not insert any values if the table is empty, cause from ${table} won't match any records, and the selected values would be omitted.