CODEWITHSUNDEEP
hiveodbcunixodbc
Sree Eedupuganti
Sree Eedupuganti

Reputation: 224

Hive Update and Delete

I am using Hive 1.0.0 Version and Hadoop 2.6.0 and Cloudera ODBC Driver. I am trying to Update and Delete the data in the hive database from Cloudera HiveOdbc Driver it throws an error. Here is my error.

What i have done ?

CREATE:

create database geometry;

create table odbctest (EmployeeID Int,FirstName String,Designation String, Salary Int,Department String) 
clustered by (department)
into 3 buckets
stored as orcfile
TBLPROPERTIES ('transactional'='true');

Table created.

INSERT:

insert into table geometry.odbctest values(10,'Hive','Hive',0,'B');

By passing the above query the data is inserting into database.

UPDATE:

When i am trying to Update the following error is getting

update geometry.odbctest set salary = 50000 where employeeid = 10;

SQL> update geometry.odbctest set salary = 50000 where employeeid = 10;

[S1000][Cloudera][HiveODBC] (55) Insert operation is not support for table: HIVE.geometry.odbctest

[ISQL]ERROR: Could not SQLPrepare

DELETE:

When i am trying to Delete the following error is getting

delete from geometry.odbctest where employeeid=10;

SQL> delete from geometry.odbctest where employeeid=10;

[S1000][Cloudera][HiveODBC] (55) Insert operation is not support for table: HIVE.geometry.odbctest

[ISQL]ERROR: Could not SQLPrepare

Can anyone help me out,

Upvotes: 1

Views: 1965

Answers (4)

Anoop Kumar
Anoop Kumar

Reputation: 1

You should not think about Hive as a regular RDBMS, Hive is better suited for batch processing over very large sets of immutable data.

Here is what you can findenter link description here

Hadoop is a batch processing system and Hadoop jobs tend to have high latency and incur substantial overheads in job submission and scheduling. As a result - latency for Hive queries is generally very high (minutes) even when data sets involved are very small (say a few hundred megabytes). As a result it cannot be compared with systems such as Oracle where analyses are conducted on a significantly smaller amount of data but the analyses proceed much more iteratively with the response times between iterations being less than a few minutes. Hive aims to provide acceptable (but not optimal) latency for interactive data browsing, queries over small data sets or test queries.

Hive is not designed for online transaction processing and does not offer real-time queries and row level updates. It is best used for batch jobs over large sets of immutable data (like web logs).

Upvotes: 0

Brody
Brody

Reputation: 86

I stumbled across the same issue when connecting to Hive 1.2 using the Simba ODBC driver distributed by Cloudera (v 2.5.12.1005 64-bit). After verifying everything in javadba's post, I did some additional digging and found the problem to be a bug in the ODBC driver.

I was able to resolve the issue by using the Progress DataDirect driver, and it looks like the version of the driver distributed by hortonworks works as well (links to both solutions below).

https://www.progress.com/data-sources/apache-hive-drivers-for-odbc-and-jdbc http://hortonworks.com/hdp/addons/

Hope that helps anyone who may still be struggling!

Upvotes: 1

Phani Kumar
Phani Kumar

Reputation: 261

As of now Hive does not support Update and Delete Operations on the data in HDFS. https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions

Upvotes: -1

WestCoastProjects
WestCoastProjects

Reputation: 63062

You have done a couple of required steps properly:

  • ORC format
  • Bucketed table

A likely cause would be: one or more of the following hive settings were not included:

These configuration parameters must be set appropriately to turn on transaction support in Hive:

hive.support.concurrency – true
hive.enforce.bucketing – true
hive.exec.dynamic.partition.mode – nonstrict
hive.txn.manager – org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
hive.compactor.initiator.on – true (for exactly one instance of the Thrift metastore service)
hive.compactor.worker.threads – a positive number on at least one instance of the Thrift metastore service

The full requirements for transaction support are here: https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions

If you have verified the above settings are in place then do a 

describe extended odbctest;

To evaluate its transaction related characteristics.

Upvotes: 3

Related Questions