Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 03E9A1046D for ; Wed, 3 Dec 2014 04:20:34 +0000 (UTC) Received: (qmail 20991 invoked by uid 500); 3 Dec 2014 04:20:32 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 20928 invoked by uid 500); 3 Dec 2014 04:20:32 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 20917 invoked by uid 99); 3 Dec 2014 04:20:32 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 03 Dec 2014 04:20:32 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of gates@hortonworks.com designates 209.85.192.179 as permitted sender) Received: from [209.85.192.179] (HELO mail-pd0-f179.google.com) (209.85.192.179) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 03 Dec 2014 04:20:27 +0000 Received: by mail-pd0-f179.google.com with SMTP id w10so14686532pde.10 for ; Tue, 02 Dec 2014 20:20:07 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:message-id:date:from:user-agent:mime-version:to :subject:references:in-reply-to:content-type; bh=nEPMSAZ+6m8axdWxQ8bX9NDEKP/+2Q0vNd58GWhrsa4=; b=EVusfDUKhktqZ9AlcwOvCfhXDjRU8A64NgJU0Xc9d52NZEWGRXJAFiJdZ4IJyhdt1A KHSVMHvkpjGZGMjZn3lVhnkcjpQlkGrz+7wPs5o1mRBNREKCvZGI9rve/tXQ2g5mMT7I E9xdhNwtCBEdU5t6Uy+f9re25dKiR54L+V3P9MupNrQytRTO29Lom5BDQGnXDH0Jh/TM ItTkFArZTAhOHMwYRYa+J8gCKzOd/cDqfwAcw8scVWWOiiLeaPCgvKRNCGI6dSl47OiT YnQaRu6hs6cnMHkWvWn9M9l3IfPzaKtp3XOfqvsrKkUxG+fofJcjlw0a/SJo2gpzDJu5 adqw== X-Gm-Message-State: ALoCoQlms+zR7aIKhCb3WOYyWn04wtYGRzqgDUPrG2raELRDq0YkTFRXB4sPVdFM1YzEybRYlsP3lxwhxmMlpGOa0xS6MBMJoILtQKZVhggzXXe93M37A1s= X-Received: by 10.68.68.206 with SMTP id y14mr11643644pbt.165.1417580407411; Tue, 02 Dec 2014 20:20:07 -0800 (PST) Received: from Alan-Gatess-MacBook-Pro.local (c-76-103-170-145.hsd1.ca.comcast.net. [76.103.170.145]) by mx.google.com with ESMTPSA id tr8sm8905719pbc.30.2014.12.02.20.20.04 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Tue, 02 Dec 2014 20:20:06 -0800 (PST) Message-ID: <547E7159.2030206@hortonworks.com> Date: Tue, 02 Dec 2014 18:11:37 -0800 From: Alan Gates User-Agent: Postbox 3.0.11 (Macintosh/20140602) MIME-Version: 1.0 To: user@hive.apache.org Subject: Re: UPDATE in Hive -0.14.0 References: In-Reply-To: Content-Type: multipart/related; boundary="------------080303040909050207080402" X-Virus-Checked: Checked by ClamAV on apache.org --------------080303040909050207080402 Content-Type: multipart/alternative; boundary="------------010906030305090700090201" This is a multi-part message in MIME format. --------------010906030305090700090201 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable You have to use ORC as the table format (it's the only format that=20 supports update and delete at this point), and you have to explicitly=20 mark the table as transactional. You must also bucket the table. For=20 example: create table HiveTest (...) clustered by (_col_) into _num_ buckets=20 stored as orc tblproperties('transactional' =3D 'true'); Alan. > unmesha sreeveni > November 24, 2014 at 1:39 > Created a Table in Hive > > create external table HiveTest (EmployeeID Int,FirstName=20 > String,Designation String,Salary Int,Department String) row format=20 > delimited fields terminated by "," location '/user/aibladmin/Hive'; > And set all the properties in hive-site.xml > hive.support.concurrency =E2=80=93 true > hive.enforce.bucketing =E2=80=93 true > hive.exec.dynamic.partition.mode =E2=80=93 nonstrict > hive.txn.manager =E2=80=93org.apache.hadoop.hive.ql.lockmgr.DbTxnManager > hive.compactor.initiator.on =E2=80=93 true > hive.compactor.worker.threads =E2=80=93 1 > > The when I tried > hive> > > > > UPDATE HiveTest SET salary =3D 50000 WHERE employeeid =3D 19; > FAILED: SemanticException [Error 10297]: Attempt to do update or=20 > delete on table default.HiveTest that does not use an AcidOutputFormat=20 > or is not bucketed > > Is it beacause of hive.enforce.bucketing =E2=80=93 true set in hive-site= .xml > Is bucketing like partition? > > > > > > > > > --=20 > */Thanks & Regards/ * > /* > */ > *Unmesha Sreeveni U.B/ > /* > *Hadoop, Bigdata Developer* > *Centre for Cyber Security | Amrita Vishwa Vidyapeetham* > http://www.unmeshasreeveni.blogspot.in/ > > / > / > Mahesh Kumar > November 24, 2014 at 1:25 > Hi unmesha sreevani, > > *Create metastore in mysql and create the tables as per the below link.* > https://github.com/apache/hive/blob/trunk/metastore/scripts/upCreate=20 > metastore in mysql and create the tables as per the below=20 > link.grade/mysql/hive-schema-0.14.0.mysql.sql=20 > . > > *And add these properties in hive-site.xml.* > > > 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 > > > > hive.compactor.worker.threads > 1 > > > *Make sure your table creation supports ACID ouput format.Create like=20 > following*. > > create table test(id int, name varchar(128)) clustered by (id) into 2=20 > buckets stored as orc TBLPROPERTIES ('transactional'=3D'true') > > > Regards, > > Mahesh.S > @Sanjiv Singh > November 24, 2014 at 0:39 > Make sure you have configured these properties hive-site.xml > > hive.support.concurrency > hive.enforce.bucketing > hive.exec.dynamic.partition.mode > hive.txn.manager > hive.compactor.initiator.on > hive.compactor.worker.threads > > > also following property has been set at table level > TBLPROPERTIES('transactional'=3D'true') > > Regards > Sanjiv Singh > Mob : +091 9990-447-339 > > > Nitin Pawar > November 24, 2014 at 0:28 > > whats your create table DDL? > > unmesha sreeveni > November 24, 2014 at 0:10 > Hi > > I am using hive -0.14.0 which support UPDATE statement > > but I am getting an error once I did this Command > UPDATE Emp SET salary =3D 50000 WHERE employeeid =3D 19; > > FAILED: SemanticException [Error 10294]: Attempt to do update or=20 > delete using transaction manager that does not support these operations. > hive> > > > Am I doing anything wrong? > > --=20 > */Thanks & Regards/ * > /* > */ > *Unmesha Sreeveni U.B/ > /* > *Hadoop, Bigdata Developer* > *Centre for Cyber Security | Amrita Vishwa Vidyapeetham* > http://www.unmeshasreeveni.blogspot.in/ > > / > / --=20 Sent with Postbox --=20 CONFIDENTIALITY NOTICE NOTICE: This message is intended for the use of the individual or entity to= =20 which it is addressed and may contain information that is confidential,=20 privileged and exempt from disclosure under applicable law. If the reader= =20 of this message is not the intended recipient, you are hereby notified that= =20 any printing, copying, dissemination, distribution, disclosure or=20 forwarding of this communication is strictly prohibited. If you have=20 received this communication in error, please contact the sender immediately= =20 and delete it from your system. Thank You. --------------010906030305090700090201 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable You have to use ORC as th= e table format (it's the only format that supports update and delete at=20 this point), and you have to explicitly mark the table as=20 transactional.=C2=A0 You must also bucket the table. For example:

create table HiveTest (...) clustered by (_col_) into _num_ buckets=20 stored as orc tblproperties('transactional' =3D 'true');

Alan.

= = =20 November 24, 201= 4 at 1:39
Created a Table in Hive

create=20 external table HiveTest (EmployeeID Int,FirstName String,Designation=20 String,Salary Int,Department String) row format delimited fields=20 terminated by "," location '/user/aibladmin/Hive';=C2=A0
=C2=A0
And set al= l the=20 properties in hive-site.xml
hive.support.concurrency =E2=80=93 true
hive.enforce.bucketing =E2=80=93 truehive.exec.dynamic.partition.mode =E2=80=93 nonstric= t
hive.txn.manager=20 =E2=80=93org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
hive.compactor.initiator.on =E2=80=93 truehive.compactor.worker.threads =E2=80=93 1
The when I= =20 tried=C2=A0
hive>=C2=A0
=C2=A0 =C2=A0 >=C2=A0
=C2=A0 =C2=A0 > UPDATE HiveTest SET salary =3D 50000 WHERE employeeid =3D 19;
FAILED: SemanticException [Error 10297]: Attempt=20 to do update or delete on table default.HiveTest that does not use an=20 AcidOutputFormat or is not bucketed

<= /div>
=C2=A0Is it beacause of hive.enforce.bucketing =E2= =80=93 true=20 set in hive-site.xml
Is bucketing like=20 partition?








--
Thanks & Regards

Unmesha Sreeveni U.B
= Hadoop, Bigdata Developer
Centre for Cyber Security | Amrita Vishwa Vidyapeetham


= = =20 November 24, 201= 4 at 1:25
Hi unmesha=20 sreevani,

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 Create metastore in=20 mysql and create the tables as per the below link.
https://github.com/apache/hive/blob/tr= unk/metastore/scripts/upCreate metastore in mysql and create the tables as per the below=20 link.grade/mysql/hive-schema-0.14.0.mysql.sql.

=
And add these properties in hive-site.xml.

&= lt;property>
=C2=A0<name>hive.support.concurrency</na= me>
=C2=A0<value>true</value>
</prope= rty>

<property>
=C2=A0<name&= gt;hive.enforce.bucketing</name>
=C2=A0<value>true<= ;/value>
</property>

<proper= ty>
=C2=A0<name>hive.exec.dynamic.partition.mode</nam= e>
=C2=A0<value>nonstrict</value>
</p= roperty>

<property>
=C2=A0<n= ame>hive.txn.manager</name>
=C2=A0<value>org.apach= e.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property= >

<property>
=C2=A0<name>= hive.compactor.initiator.on</name>
=C2=A0<value>true&= lt;/value>
</property>

<property>
=C2=A0<name>hive.compactor.wor= ker.threads</name>
=C2=A0<value>1</value>
=
</property>

Make sure your table creation supports ACID ouput format.Create like=20 following.

create table test(id int, name varchar(128)) clustered by (id) into 2 buckets stored as orc=20 TBLPROPERTIES ('transactional'=3D'true')


Regards,
<= div class=3D"gmail_extra">
Mahesh.S
= = =20 November 24, 201= 4 at 0:39
Make sure you= =20 have configured these properties hive-site.xml

hive.support.concurr= ency
hive.enforce.bucketing
hive.exec.dynamic.partition.mode
hive.= txn.manager
hive.compactor.initiator.on
hive.compactor.worker.threads=


also following property has been set at table level
TBLPROPERTIES('transact= ional'=3D'true')

Regards
Sanjiv Singh
Mob :=C2=A0 +091 9990-447-339


= = =20 November 24, 201= 4 at 0:28

whats your crea= te=20 table DDL?

= = =20 November 24, 201= 4 at 0:10
Hi

I am usin= g hive -0.14.0 which support UPDATE statement

=
but I am= =20 getting an error once I did this Command
UPDATE Emp SET= =20 salary =3D 50000 WHERE employeeid =3D 19;

FAILED: SemanticException= =20 [Error 10294]: Attempt to do update or delete using transaction manager=20 that does not support these operations.
hive>=C2=A0
=


=
Am I doin= g anything wrong?

--
=
Thanks &am= p;=20 Regards

Unmesha Sreeveni U.B
= Hadoop, Bigdata Developer
Centre for Cyber Security | Amrita Vishwa Vidyapeetham



--
Sent with Postbox

CONFIDENTIALITY NOTICE
NOTICE: This message is = intended for the use of the individual or entity to which it is addressed a= nd may contain information that is confidential, privileged and exempt from= disclosure under applicable law. If the reader of this message is not the = intended recipient, you are hereby notified that any printing, copying, dis= semination, distribution, disclosure or forwarding of this communication is= strictly prohibited. If you have received this communication in error, ple= ase contact the sender immediately and delete it from your system. Thank Yo= u. --------------010906030305090700090201-- --------------080303040909050207080402 Content-Type: image/jpeg; x-apple-mail-type=stationery; name="compose-unknown-contact.jpg" Content-Transfer-Encoding: base64 Content-ID: Content-Disposition: inline; filename="compose-unknown-contact.jpg" /9j/4AAQSkZJRgABAQEARwBHAAD/2wBDAAEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEC AQEBAQEBAgICAgICAgICAgICAgICAgICAgICAgICAgICAgL/2wBDAQEBAQEBAQICAgICAgIC AgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgL/wAAR CAAZABkDAREAAhEBAxEB/8QAGAAAAwEBAAAAAAAAAAAAAAAABgcICQr/xAA0EAABAwMCAgUK BwAAAAAAAAACAQMEBQYRABITIQcUMUF2CBUXIjI2N0JRtVRWkZOV0dL/xAAYAQEAAwEAAAAA AAAAAAAAAAADAAEEAv/EACQRAAICAAQGAwAAAAAAAAAAAAABAhEDMrHREyExM0FxgfDx/9oA DAMBAAIRAxEAPwDuEt+gW/ULet6oVC3rfqNQqFv0OfPn1GhUqfOmzZtKZlS5UqZMaNwzNwiJ VIl7eXLCaZIGwBl3TY8epPx2+jy2ZNPjvkwc9uhW8j7nCPhvOsQliYIeS7cvCpp8o50qwrC4 v3lsNSDbdmTEhvs2tahxpfV3WnmbbozJEw/gwdadbYExVRXKEKoSdvJcaOSqxE7/AAiX0gXx +a69/JSf9alIlste0VzaNpeFrcT9KKymotyiaZ0KRCnzacoE7Kjzn4gi2KqUh3jqDHDHv4mR UfruTWlMzlVUKIVNp9GguEJnAh0+IZjyAiisgyRDnu5azS8miKqjOTVkKqS/psG37fo1Fbab eg25b8eZPeFJBBJSjMG5HjMeyihnaauZwe4OGiju13GAcpOwBeN+U8/IkGbsiS8b7ryogmbz hbyc9REROfZhERO5ETShjPtvpGqTUyLErytS4siSwx5x2tRH4hPOI0DkjZtaJtFxuVEbIUUi yeNujlBUJGbJN6nM/Cyf2Hf60YgjvKA+NPSP4gT7axpcPtr51YWJnYn9dnAQWl722p4ot37y zqnlfp6FrqbwawG8/9k= --------------080303040909050207080402--