8

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

Community
  • 1
  • 1
Frostie_the_snowman
  • 629
  • 3
  • 9
  • 17

2 Answers2

12

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;
sumitya
  • 2,631
  • 1
  • 19
  • 32
  • 2
    I tried the above and got the following error: `ParseException line 1:65 Failed to recognize predicate ''. Failed rule: 'regularBody' in statement` – Frostie_the_snowman Jun 17 '16 at 09:53
  • Added one more query to get result. – sumitya Jun 17 '16 at 12:00
  • 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? – Frostie_the_snowman Jun 17 '16 at 12:46
  • what's your hive version? I tested this on `Hive 1.2.1.2.3.4.29-1` it works there. – sumitya Jun 17 '16 at 12:57
  • using Hive 1.2.1000.2.4.0.0-169 – Frostie_the_snowman Jun 17 '16 at 13:09
  • 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? – Frostie_the_snowman Jun 20 '16 at 15:31
  • 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. – sumitya Jun 20 '16 at 17:56
  • Perfect, Thanks for all your help – Frostie_the_snowman Jun 21 '16 at 09:10
  • 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. – Paul Lam Sep 16 '20 at 03:41
3

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;
mck
  • 40,932
  • 13
  • 35
  • 50
Kevin
  • 61
  • 2