Append data by insert command

[jaydeep vishwakarma <ja...@mkhoj.com>]

Hi,

Not able to append data in hive tables  .I know only one syntax of
"INSERT" command which have keyword called "OVERWRITE" , It overwrites
data in whole partition. Is there any other way by that I can append
records in hive tables by HQL?

Regards,
Jaydeep

The information contained in this communication is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. It may contain confidential or legally privileged information. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. The firm is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt.

RE: Append data by insert command

[Bennie Schut <bs...@ebuddy.com>]

Hi Jaydeep,

Currently the only thing you can do is overwrite.
This sounds worse then it is since there is a workaround which is probably used by most people.
You can partition by some kind of dummy key and simple overwrite into a new partition leaving the rest of the data in other partitions in tact.

something like this:

create table test (
  test_id     bigint
, testline        string
) PARTITIONED BY (appendkey_id INT)


INSERT OVERWRITE TABLE test partition(appendkey_id=1)
SELECT test_id, testline
FROM test_load

Just make sure the "appendkey_id=1" will have a unique value each time. We for instance use a timestamp for it.
If the table is really small you can do a union with the original table to combine old+new table and still use the overwrite.

Having all of this hidden from the user would be nice though :)

Bennie.

-----Original Message-----
From: jaydeep vishwakarma [mailto:jaydeep.vishwakarma@mkhoj.com] 
Sent: Friday, July 23, 2010 12:24 PM
To: hive-dev@hadoop.apache.org
Subject: Append data by insert command


Hi,

Not able to append data in hive tables  .I know only one syntax of
"INSERT" command which have keyword called "OVERWRITE" , It overwrites
data in whole partition. Is there any other way by that I can append
records in hive tables by HQL?

Regards,
Jaydeep

The information contained in this communication is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. It may contain confidential or legally privileged information. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. The firm is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt.