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 3399E10E0C for ; Tue, 8 Sep 2015 09:47:39 +0000 (UTC) Received: (qmail 383 invoked by uid 500); 8 Sep 2015 09:47:24 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 310 invoked by uid 500); 8 Sep 2015 09:47:24 -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 300 invoked by uid 99); 8 Sep 2015 09:47:24 -0000 Received: from Unknown (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 08 Sep 2015 09:47:24 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd2-us-west.apache.org (ASF Mail Server at spamd2-us-west.apache.org) with ESMTP id 20EAD1A08E4 for ; Tue, 8 Sep 2015 09:47:24 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 4.1 X-Spam-Level: **** X-Spam-Status: No, score=4.1 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=3, KAM_LINEPADDING=1.2, SPF_PASS=-0.001, URIBL_BLOCKED=0.001] autolearn=disabled Authentication-Results: spamd2-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-us-west.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id pskYGagqh8QD for ; Tue, 8 Sep 2015 09:47:16 +0000 (UTC) Received: from mail-yk0-f182.google.com (mail-yk0-f182.google.com [209.85.160.182]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with ESMTPS id E674723043 for ; Tue, 8 Sep 2015 09:47:15 +0000 (UTC) Received: by ykdg206 with SMTP id g206so110052113ykd.1 for ; Tue, 08 Sep 2015 02:47:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:date:message-id:subject:from:to:content-type; bh=K7NPjcdZRTnSgASZfiBrSf4EHNO8Liis28r+gyTCnOo=; b=zRj+cUNajvkTYf//J+6OMR0utsmvXTRMig//rxostJLunuIxijJnGu03PII0Z96cjU fcTSVTdMDDMZKVOCg+bxCQB9tgZZUVkx6+BXFzMgqKnT9t+lA6IFN+xOeXAkpo/ojbtr lmSxj3xDjH8DA5HV4KKeN7n1ekOzVGp/W7THww5HkrCaI+X2i7ffBTT1l4Jl1Y49In7F G8dpFoo9XjVEwqqTX/Isoxah9/wnQNelI88Ik3VAOIsEw00RdMk2HROmuQn85ejz/CDW N7d9N8xhdgMp0jDyvoaFMTqB01h26+0RCTmABRDmUFk4j6uKj4hjA510WqsHIxjqXMJT 4AYA== MIME-Version: 1.0 X-Received: by 10.170.128.2 with SMTP id u2mr26226607ykb.72.1441705635204; Tue, 08 Sep 2015 02:47:15 -0700 (PDT) Received: by 10.37.73.6 with HTTP; Tue, 8 Sep 2015 02:47:15 -0700 (PDT) Date: Tue, 8 Sep 2015 02:47:15 -0700 Message-ID: Subject: Unsubscribe From: Mohit Garg To: user@hive.apache.org Content-Type: multipart/alternative; boundary=001a1139558641d73a051f3940ed --001a1139558641d73a051f3940ed Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Mon, Sep 7, 2015 at 11:58 PM, Zhu, Jian-bing wrote: > Hi, > > > > I tried to test UPDATE and DELETE on Hive, but it always failed because o= f > =E2=80=9C[Error 10122]: Bucketized tables do not support INSERT INTO=E2= =80=9D. > > > > I am using Hortonworks Sandbox HDP 2.3 which contains Hive 1.2.1. > > > > I already configured all the required parameters according to Hive doc: > > 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=93 org.apache.hadoop.hive.ql.lockmgr.DbTxnManager > > hive.compactor.initiator.on =E2=80=93 true (for exactly one instance of t= he Thrift > metastore service) > > hive.compactor.worker.threads =E2=80=93 a positive number on at least one= instance > of the Thrift metastore service > > > > Hive log is attached and the followings are the output from Hive console. > > > > Can anyone help to take a look and provide some suggestion to resolve it? > Thanks in advance! > > > > > > > > hive> CREATE TABLE mytable (col1 int, col2 string) > > > CLUSTERED BY (col1) INTO 2 BUCKETS > > > STORED AS ORC TBLPROPERTIES('TRANSACTIONAL'=3D'TRUE'); > > OK > > Time taken: 1.324 seconds > > > > *hive> insert into mytable values (1, 'AAA');* > > Query ID =3D root_20150908014932_e21e8fb1-f93b-472a-a527-1adc29cd3359 > > Total jobs =3D 1 > > Launching Job 1 out of 1 > > Tez session was closed. Reopening... > > Session re-established. > > > > > > Status: Running (Executing on YARN cluster with App id > application_1440988430185 > _0013) > > > > > -------------------------------------------------------------------------= ------- > > VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED > KILLED > > > -------------------------------------------------------------------------= ------- > > Map 1 .......... SUCCEEDED 1 1 0 0 > 0 0 > > Reducer 2 ...... SUCCEEDED 2 2 0 0 > 0 0 > > > -------------------------------------------------------------------------= ------- > > VERTICES: 02/02 [=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D>>] 100% ELAPSED TIME: 5.70 s > > > -------------------------------------------------------------------------= ------- > > Loading data to table default.mytable > > Table default.mytable stats: [numFiles=3D2, numRows=3D1, totalSize=3D336, > rawDataSize=3D > = 91] > > OK > > Time taken: 16.585 seconds > > > > *hive> insert into mytable values (2, 'BBB');* > > Query ID =3D root_20150908014955_2647ae71-0687-43dd-84c5-8263e65622f9 > > Total jobs =3D 1 > > Launching Job 1 out of 1 > > > > > > Status: Running (Executing on YARN cluster with App id > application_1440988430185 > _0013) > > > > > -------------------------------------------------------------------------= ------- > > VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED > KILLED > > > -------------------------------------------------------------------------= ------- > > Map 1 .......... SUCCEEDED 1 1 0 0 > 0 0 > > Reducer 2 ...... SUCCEEDED 2 2 0 0 > 0 0 > > > -------------------------------------------------------------------------= ------- > > VERTICES: 02/02 [=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D>>] 100% ELAPSED TIME: 7.97 s > > > -------------------------------------------------------------------------= ------- > > Loading data to table default.mytable > > Table default.mytable stats: [numFiles=3D4, numRows=3D2, totalSize=3D672, > rawDataSize=3D182] > > OK > > Time taken: 13.812 seconds > > > > *hive> delete from mytable;* > > FAILED: SemanticException [Error 10122]: Bucketized tables do not support > INSERT INTO: Table: default.mytable > > hive> > > > > *hive> select * from mytable;* > > OK > > 2 BBB > > 1 AAA > > Time taken: 0.803 seconds, Fetched: 2 row(s) > > > > > > Best Regards, > > Jianbing > > > --=20 Mohit Garg 9717040694 --001a1139558641d73a051f3940ed Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


On Mon, Sep 7, 2015 at 11:58 PM, Zhu, Jian-bing <<= a href=3D"mailto:jian-bin.zhu@sap.com" target=3D"_blank">jian-bin.zhu@sap.c= om> wrote:

Hi,

=C2=A0

I tried to test UPDATE= and DELETE on Hive, but it always failed because of =E2=80=9C[Error 10122]= : Bucketized tables do not support INSERT INTO=E2=80=9D.

=C2=A0

I am using Hortonworks= Sandbox HDP 2.3 which contains Hive 1.2.1.

=C2=A0

I already configured a= ll the required parameters according to Hive doc:

hive.support.concurrency =E2=80=93 true

hive.enforce.bucketing =E2=80=93 true<= /p>

hive.exec.dynamic.partition.mode =E2=80=93 nonstrict=

hive.txn.manager =E2=80=93 org.apache.hadoop.hive.ql= .lockmgr.DbTxnManager

hive.compactor.initiator.on =E2=80=93 true (for exac= tly one instance of the Thrift metastore service)

hive.compactor.worker.threads =E2=80=93 a positive n= umber on at least one instance of the Thrift metastore service

=C2=A0

Hive log is attached a= nd the followings are the output from Hive console.

=C2=A0

Can anyone help to tak= e a look and provide some suggestion to resolve it? Thanks in advance!

=C2=A0

=C2=A0

=C2=A0

hive> CREATE TABLE myta= ble (col1 int, col2 string)

=C2=A0=C2=A0=C2=A0 > CL= USTERED BY (col1) INTO 2 BUCKETS

=C2=A0=C2=A0=C2=A0 > ST= ORED AS ORC TBLPROPERTIES('TRANSACTIONAL'=3D'TRUE');=

OK

Time taken: 1.324 seco= nds

=C2=A0

hive> insert into mytable values (1, 'AAA');

Query ID =3D root_2015= 0908014932_e21e8fb1-f93b-472a-a527-1adc29cd3359

Total jobs =3D 1

Launching Job 1 out of= 1

Tez session was closed= . Reopening...

Session re-established= .

=C2=A0

=C2=A0

Status: Running (Execu= ting on YARN cluster with App id application_1440988430185=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 _0013)

=C2=A0

----------------------= ----------------------------------------------------------

=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 VERTICES=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 STATUS=C2=A0 T= OTAL=C2=A0 COMPLETED=C2=A0 RUNNING=C2=A0 PENDING=C2=A0 FAILED=C2=A0 KILLED<= u>

----------------------= ----------------------------------------------------------

Map 1 ..........=C2=A0= =C2=A0 SUCCEEDED=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 1=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0

Reducer 2 ......=C2=A0= =C2=A0 SUCCEEDED=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 2=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0

----------------------= ----------------------------------------------------------

VERTICES: 02/02=C2=A0 = [=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D>>] 100%=C2=A0 ELAPSED TIME: 5.70 s

----------------------= ----------------------------------------------------------

Loading data to table = default.mytable

Table default.mytable = stats: [numFiles=3D2, numRows=3D1, totalSize=3D336, rawDataSize=3D=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A091]

OK

Time taken: 16.585 sec= onds

=C2=A0

hive> insert into my= table values (2, 'BBB');

Query ID =3D root_2015= 0908014955_2647ae71-0687-43dd-84c5-8263e65622f9

Total jobs =3D 1

Launching Job 1 out of= 1

=C2=A0

=C2=A0

Status: Running (Execu= ting on YARN cluster with App id application_1440988430185=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 _0013)

=C2=A0

----------------------= ----------------------------------------------------------

=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 VERTICES=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 STATUS=C2=A0 T= OTAL=C2=A0 COMPLETED=C2=A0 RUNNING=C2=A0 PENDING=C2=A0 FAILED=C2=A0 KILLED<= u>

----------------------= ----------------------------------------------------------

Map 1 ..........=C2=A0= =C2=A0 SUCCEEDED=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 1=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0

Reducer 2 ......=C2=A0= =C2=A0 SUCCEEDED=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 2=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0

----------------------= ----------------------------------------------------------

VERTICES: 02/02=C2=A0 = [=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D>>] 100%=C2=A0 ELAPSED TIME: 7.97 s

----------------------= ----------------------------------------------------------

Loading data to table = default.mytable

Table default.mytable = stats: [numFiles=3D4, numRows=3D2, totalSize=3D672, rawDataSize=3D182]

OK

Time taken: 13.812 sec= onds

=C2=A0

hive> delete from my= table;

FAILED: SemanticExcept= ion [Error 10122]: Bucketized tables do not support INSERT INTO: Table: def= ault.mytable

hive>=

=C2=A0

hive> select * from = mytable;

OK

2=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 BBB

1=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 AAA

Time taken: 0.803 seco= nds, Fetched: 2 row(s)

=C2=A0

=C2=A0

Best Regards,

Jianbing

=C2=A0




--
Mohit Garg
9717040694
--001a1139558641d73a051f3940ed--