hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jamie Thomson - Contractor <>
Subject RE: simple insert query question
Date Fri, 20 Jun 2014 07:02:51 GMT
“create a dummy table and then write insert into table select from table2 kind of dummy query”
I’ve not been using Hadoop for too long but I was kinda surprised that there was no equivalent
of Oracle’s [DUAL] table in Hive. It would be useful for testing expressions in the SELECT
clause or achieving what Clay wants to achieve below. Wouldn’t be hard to achieve either,
just a simple 1-row file in HDFS.
Right now I create a DUAL table whenever I stand up a new Hadoop cluster and that’s no big
deal, but if it were in the box it would save me a small job each time.

Just a thought.

From: Nitin Pawar []
Sent: 19 June 2014 15:08
Cc: Nishant Kelkar
Subject: Re: simple insert query question

remember in hive, insert operation is

1) from a file
2) from another table

hive's underlying storage is hdfs which is not meant for having just single record kind of
stuff (as of now, this will change once hive starts supporting ACID actions in coming releases)

1) either create a sample file and load data in table using file
2) or create a dummy table and then write insert into table select from table2 kind of dummy

On Thu, Jun 19, 2014 at 7:26 PM, Clay McDonald <<>>
What about if I wanted to run this in hive,

create table test_log (test_time timestamp, test_notes varchar(60));

insert into table test_log values(now(),'THIS IS A TEST');

From: Nishant Kelkar [<>]
Sent: Thursday, June 19, 2014 9:29 AM
To:<>; Clay McDonald
Subject: Re: simple insert query question

Hey Stuart,

As far as I know, files in HDFS are immutable. So I would think that your query below would
not have a direct Hive conversion.

What you can do though, is create a local text file and then create an EXTERNAL TABLE on top
of that. Then, instead of your INSERT query, just use some linux command to append a line
to text file. It will automatically reflect in your external Hive table! :)

To understand what Hive external tables are and how to create them, I'd just go on the Hive
wiki page.

Good luck!

On Jun 19, 2014 6:17 AM, "Clay McDonald" <<>>
 hi all,

how do I write the following query to insert a note with a current system timestamp?

I tried the following;


thanks, Clay

Nitin Pawar
dunnhumby limited is a limited company registered in England and Wales with registered number
02388853 and VAT registered number 927 5871 83.
Our registered office is at Aurora House, 71-75 Uxbridge Road, London W5 5SL.
The contents of this message and any attachments to it are confidential and may be legally
If you have received this message in error you should delete it from your system immediately
and advise the sender.
dunnhumby may monitor and record all emails. The views expressed in this email are those of
the sender and not those of dunnhumby.
View raw message