You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Clay McDonald <st...@bateswhite.com> on 2014/06/19 15:17:29 UTC
simple insert query question
hi all,
how do I write the following query to insert a note with a current system timestamp?
I tried the following;
INSERT INTO TEST_LOG VALUES (unix_timestamp(),'THIS IS A TEST.');
thanks, Clay
RE: simple insert query question
Posted by Jamie Thomson - Contractor <Ja...@dunnhumby.com>.
“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 [mailto:nitinpawar432@gmail.com]
Sent: 19 June 2014 15:08
To: user@hive.apache.org
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 query
On Thu, Jun 19, 2014 at 7:26 PM, Clay McDonald <st...@bateswhite.com>> wrote:
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 [mailto:nishant.k02@gmail.com<ma...@gmail.com>]
Sent: Thursday, June 19, 2014 9:29 AM
To: user@hive.apache.org<ma...@hive.apache.org>; 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!
Best,
Nishant
On Jun 19, 2014 6:17 AM, "Clay McDonald" <st...@bateswhite.com>> wrote:
hi all,
how do I write the following query to insert a note with a current system timestamp?
I tried the following;
INSERT INTO TEST_LOG VALUES (unix_timestamp(),'THIS IS A TEST.');
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 privileged.
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.
RE: simple insert query question
Posted by Clay McDonald <st...@bateswhite.com>.
Got it. Thanks!
From: Nitin Pawar [mailto:nitinpawar432@gmail.com]
Sent: Thursday, June 19, 2014 10:08 AM
To: user@hive.apache.org
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 query
On Thu, Jun 19, 2014 at 7:26 PM, Clay McDonald <st...@bateswhite.com>> wrote:
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 [mailto:nishant.k02@gmail.com<ma...@gmail.com>]
Sent: Thursday, June 19, 2014 9:29 AM
To: user@hive.apache.org<ma...@hive.apache.org>; 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!
Best,
Nishant
On Jun 19, 2014 6:17 AM, "Clay McDonald" <st...@bateswhite.com>> wrote:
hi all,
how do I write the following query to insert a note with a current system timestamp?
I tried the following;
INSERT INTO TEST_LOG VALUES (unix_timestamp(),'THIS IS A TEST.');
thanks, Clay
--
Nitin Pawar
Re: simple insert query question
Posted by Nitin Pawar <ni...@gmail.com>.
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 query
On Thu, Jun 19, 2014 at 7:26 PM, Clay McDonald <
stuart.mcdonald@bateswhite.com> wrote:
> 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 [mailto:nishant.k02@gmail.com]
> *Sent:* Thursday, June 19, 2014 9:29 AM
> *To:* user@hive.apache.org; 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!
>
> Best,
> Nishant
>
> On Jun 19, 2014 6:17 AM, "Clay McDonald" <st...@bateswhite.com>
> wrote:
>
> hi all,
>
> how do I write the following query to insert a note with a current system
> timestamp?
>
> I tried the following;
>
>
> INSERT INTO TEST_LOG VALUES (unix_timestamp(),'THIS IS A TEST.');
>
> thanks, Clay
>
--
Nitin Pawar
RE: simple insert query question
Posted by Clay McDonald <st...@bateswhite.com>.
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 [mailto:nishant.k02@gmail.com]
Sent: Thursday, June 19, 2014 9:29 AM
To: user@hive.apache.org; 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!
Best,
Nishant
On Jun 19, 2014 6:17 AM, "Clay McDonald" <st...@bateswhite.com>> wrote:
hi all,
how do I write the following query to insert a note with a current system timestamp?
I tried the following;
INSERT INTO TEST_LOG VALUES (unix_timestamp(),'THIS IS A TEST.');
thanks, Clay
Re: simple insert query question
Posted by Nishant Kelkar <ni...@gmail.com>.
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!
Best,
Nishant
On Jun 19, 2014 6:17 AM, "Clay McDonald" <st...@bateswhite.com>
wrote:
> hi all,
>
> how do I write the following query to insert a note with a current system
> timestamp?
>
> I tried the following;
>
>
> INSERT INTO TEST_LOG VALUES (unix_timestamp(),'THIS IS A TEST.');
>
> thanks, Clay
>
RE: simple insert query question
Posted by Clay McDonald <st...@bateswhite.com>.
also, this does not work;
insert into table test_log values(now(),'THIS IS A TEST');
insert into table test_log values(unix_timestamp(),'THIS IS A TEST');
insert into table test_log values (from_unixtime(unix_timestamp()),'THIS IS A TEST');
-----Original Message-----
From: Clay McDonald
Sent: Thursday, June 19, 2014 9:17 AM
To: user
Subject: simple insert query question
hi all,
how do I write the following query to insert a note with a current system timestamp?
I tried the following;
INSERT INTO TEST_LOG VALUES (unix_timestamp(),'THIS IS A TEST.');
thanks, Clay