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 B6BD318F45 for ; Tue, 1 Mar 2016 04:06:53 +0000 (UTC) Received: (qmail 20636 invoked by uid 500); 1 Mar 2016 04:06:51 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 20559 invoked by uid 500); 1 Mar 2016 04:06:51 -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 20548 invoked by uid 99); 1 Mar 2016 04:06:51 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Mar 2016 04:06:51 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 6C8191805CD for ; Tue, 1 Mar 2016 04:06:51 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.21 X-Spam-Level: * X-Spam-Status: No, score=1.21 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H2=-0.001, T_KAM_HTML_FONT_INVALID=0.01, WEIRD_PORT=0.001] autolearn=disabled Authentication-Results: spamd3-us-west.apache.org (amavisd-new); dkim=pass (1024-bit key) header.d=altiscale.com Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id BwdtjZdfSCkX for ; Tue, 1 Mar 2016 04:06:49 +0000 (UTC) Received: from mail-oi0-f47.google.com (mail-oi0-f47.google.com [209.85.218.47]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTPS id 9F6545F393 for ; Tue, 1 Mar 2016 04:06:48 +0000 (UTC) Received: by mail-oi0-f47.google.com with SMTP id k67so63683234oia.3 for ; Mon, 29 Feb 2016 20:06:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=altiscale.com; s=google; h=mime-version:in-reply-to:references:date:message-id:subject:from:to; bh=v4Bym039pafA6yAX5cQ5jvlXiMRjG1LRrfgk276x9t8=; b=P7+c+8vuiWJcCvw0awCyi6CWp9ZfqTDc78/t1ZfiDO9cBmgbw4N2dF/4ltNqfbusMr D8ScwVmq7OT2Ne0MtjurHvNQZwju7IrKDZ66KiHFH/4uU+ulh1rnbS49TWg4V58tCLJt VAMU2YpKnGQgbPk3IjeEOuj5W8RQ7qgoUhmHU= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to; bh=v4Bym039pafA6yAX5cQ5jvlXiMRjG1LRrfgk276x9t8=; b=IoxZy9UETcvZCxewq1RT6hA73h/BMBR4VPvphkhoB3CehK007yypP4vAkp7JvWseSE mbiTXASlgL0B+CvoypZV3Pg3EPTwBlOVS2rCd9n4MKoFLtURn9KXinqE0GkRtncCiN69 4Ff8nn8N1XNGOSz7xZSgWxN0eLxDfORGC2sqa7F/89C3ic5txiPaPDGWpOkB6IIFrY94 wxveySoYwrWYLDJD+/L2Gmd9KZaLrTbHUztNeU8857at7kObH57vaEFsUcUnQo7VlUcg /4mEDQmTNxd6XEswUyu6WjnSYMXM8Pqntr154mVFEMZ0oaMLfY+wl9u9zMiNDmhORIOK iAeA== X-Gm-Message-State: AD7BkJJ9JgO4p8CYBRyw/bkCO3wZTd7/SAvnBflDV9ndb7yfQ4mzf0S1UdkW3GViV/pedk+oD0nbqBzgBJahjA== MIME-Version: 1.0 X-Received: by 10.202.190.87 with SMTP id o84mr14365325oif.109.1456805201860; Mon, 29 Feb 2016 20:06:41 -0800 (PST) Received: by 10.76.36.10 with HTTP; Mon, 29 Feb 2016 20:06:41 -0800 (PST) In-Reply-To: References: Date: Tue, 1 Mar 2016 09:36:41 +0530 Message-ID: Subject: Re: Sqoop Hcat Int partition error From: Biswajit Nayak To: user@hive.apache.org Content-Type: multipart/alternative; boundary=001a113d6772906ca7052cf4e4f6 --001a113d6772906ca7052cf4e4f6 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi Richa, Thats a work around. But how to handle the columns with INT type. Changing the type will be the last option for me. Regards Biswa On Tue, Mar 1, 2016 at 9:31 AM, Richa Sharma wrote: > Hi Biswajit > > The answer is in the last line of the error message. Change the data type > of partition column to string in hive and try again. > > Hope it helps ! > > Richa > > 16/02/12 08:04:12 ERROR tool.ExportTool: Encountered IOException running = export job: java.io.IOException: The table provided default.emp_details1 us= es unsupported partitioning key type for column salary : int. Only strin= g fields are allowed in partition columns in Catalog > > > On Tue, Mar 1, 2016 at 2:19 PM, Biswajit Nayak > wrote: > >> Hi All, >> >> I am trying to do a SQOOP export from hive( integer type partition) to >> mysql through HCAT and it fails with the following error. >> >> Versions:- >> >> Hadoop :- 2.7.1 >> Hive :- 1.2.0 >> Sqoop :- 1.4.5 >> >> Table in Hive :- >> >> >> hive> use default; >> OK >> Time taken: 0.028 seconds >> hive> describe emp_details1; >> OK >> id int >> name string >> deg string >> dept string >> salary int >> >> # Partition Information >> # col_name data_type comment >> >> salary int >> Time taken: 0.125 seconds, Fetched: 10 row(s) >> hive> >> >> hive> select * from emp_details1; >> OK >> 1201 gopal 50000 >> 1202 manisha 50000 >> 1203 kalil 50000 >> 1204 prasanth 50000 >> 1205 kranthi 50000 >> 1206 satish 50000 >> Time taken: 0.195 seconds, Fetched: 6 row(s) >> hive> >> >> >> Conf added to Hive metastore site.xml >> >> >> [alti-test-01@hdpnightly271-ci-91-services ~]$ grep -A5 -B2 -i "hive.met= astore.integral.jdo.pushdown" /etc/hive-metastore/hive-site.xml >> >> >> hive.metastore.integral.jdo.pushdown >> TRUE >> >> >> >> [alti-test-01@hdpnightly271-ci-91-services ~]$ >> >> >> The issue remains same >> >> >> [alti-test-01@hdpnightly271-ci-91-services ~]$ /opt/sqoop-1.4.5/bin/sqoo= p export --connect jdbc:mysql://localhost:3306/test --username hive --passw= ord ********* --table employee --hcatalog-database default --hcatalog-table= emp_details1 >> Warning: /opt/sqoop-1.4.5/bin/../../hbase does not exist! HBase imports = will fail. >> Please set $HBASE_HOME to the root of your HBase installation. >> Warning: /opt/sqoop-1.4.5/bin/../../accumulo does not exist! Accumulo im= ports will fail. >> Please set $ACCUMULO_HOME to the root of your Accumulo installation. >> Warning: /opt/sqoop-1.4.5/bin/../../zookeeper does not exist! Accumulo i= mports will fail. >> Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation. >> 16/02/12 08:04:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5 >> 16/02/12 08:04:00 WARN tool.BaseSqoopTool: Setting your password on the = command-line is insecure. Consider using -P instead. >> 16/02/12 08:04:00 INFO manager.MySQLManager: Preparing to use a MySQL st= reaming resultset. >> 16/02/12 08:04:00 INFO tool.CodeGenTool: Beginning code generation >> 16/02/12 08:04:01 INFO manager.SqlManager: Executing SQL statement: SELE= CT t.* FROM `employee` AS t LIMIT 1 >> 16/02/12 08:04:01 INFO manager.SqlManager: Executing SQL statement: SELE= CT t.* FROM `employee` AS t LIMIT 1 >> 16/02/12 08:04:01 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /op= t/hadoop >> Note: /tmp/sqoop-alti-test-01/compile/1b0d4b1c30f167eb57ef488232ab49c8/e= mployee.java uses or overrides a deprecated API. >> Note: Recompile with -Xlint:deprecation for details. >> 16/02/12 08:04:07 INFO orm.CompilationManager: Writing jar file: /tmp/sq= oop-alti-test-01/compile/1b0d4b1c30f167eb57ef488232ab49c8/employee.jar >> 16/02/12 08:04:07 INFO mapreduce.ExportJobBase: Beginning export of empl= oyee >> 16/02/12 08:04:08 INFO mapreduce.ExportJobBase: Configuring HCatalog for= export job >> 16/02/12 08:04:08 INFO hcat.SqoopHCatUtilities: Configuring HCatalog spe= cific details for job >> 16/02/12 08:04:08 INFO manager.SqlManager: Executing SQL statement: SELE= CT t.* FROM `employee` AS t LIMIT 1 >> 16/02/12 08:04:08 INFO hcat.SqoopHCatUtilities: Database column names pr= ojected : [id, name, deg, salary, dept] >> 16/02/12 08:04:08 INFO hcat.SqoopHCatUtilities: Database column name - i= nfo map : >> id : [Type : 4,Precision : 11,Scale : 0] >> name : [Type : 12,Precision : 20,Scale : 0] >> deg : [Type : 12,Precision : 20,Scale : 0] >> salary : [Type : 4,Precision : 11,Scale : 0] >> dept : [Type : 12,Precision : 10,Scale : 0] >> >> 16/02/12 08:04:10 INFO hive.metastore: Trying to connect to metastore wi= th URI thrift://hive-hdpnightly271-ci-91.test.altiscale.com:9083 >> 16/02/12 08:04:10 INFO hive.metastore: Connected to metastore. >> 16/02/12 08:04:11 INFO hcat.SqoopHCatUtilities: HCatalog full table sche= ma fields =3D [id, name, deg, dept, salary] >> 16/02/12 08:04:12 ERROR tool.ExportTool: Encountered IOException running= export job: java.io.IOException: The table provided default.emp_details1 u= ses unsupported partitioning key type for column salary : int. Only stri= ng fields are allowed in partition columns in Catalog >> >> >> Stuck with this issue. Any one had conquered this before. >> >> Regards >> Biswa >> >> > --001a113d6772906ca7052cf4e4f6 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Richa,=C2=A0

Thats a work around. Bu= t how to handle the columns with INT type. Changing the type will be the la= st option for me.=C2=A0

Regards
Biswa



On Tue, Mar 1, 2016 at 9:31 AM, Richa Sharma <mailtorichasharma@gmail.com> wrote:
Hi Biswajit

The answer is= in the last line of the error message. Change the data type of partition c= olumn to string in hive and try again.

Hope it hel= ps !

Richa
16/02/12 08:04:12 ERROR tool.ExportTool: Encounte=
red IOException running export job: java.io.IOException: The table provided=
 default.emp_details1 uses unsupported  partitioning key type  for column s=
alary : int.  Only string fields are allowed in partition columns in Catalo=
g

On Tue, Mar 1, 2016= at 2:19 PM, Biswajit Nayak <biswajit@altiscale.com> wr= ote:
Hi All,=C2=A0

I am trying to do a SQOOP export from hive(= integer type partition) to mysql through HCAT and it fails with the follow= ing error.=C2=A0

Versions:-

Hadoop :- =C2=A02.7.1
Hive =C2=A0 =C2=A0 =C2=A0:- = =C2=A01.2.0
Sqoop = =C2=A0 :- =C2=A01.4.5

Table in Hive :-


hive> use default;
OK
Time taken: 0.028 seconds
hive> describe emp_details1;
OK
id                      int                                        =20
name                    string                                     =20
deg                     string                                     =20
dept                    string                                     =20
salary                  int                                        =20

# Partition Information     =20
# col_name              data_type               comment            =20

salary                  int                                        =20
Time taken: 0.125 seconds, Fetched: 10 row(s)
hive>=20

hive> select * from emp_details1;
OK
1201    gopal           50000
1202    manisha         50000
1203    kalil           50000
1204    prasanth        50000
1205    kranthi         50000
1206    satish          50000
Time taken: 0.195 seconds, Fetched: 6 row(s)
hive>=20

= Conf added to Hive metastore site.xml


[alti-test-01@hdpnightly271-ci-91-services ~]$ grep -A5 -B2 -i "hive.m=
etastore.integral.jdo.pushdown" /etc/hive-metastore/hive-site.xml=20
    </property>
    <property>
        <name>hive.metastore.integral.jdo.pushdown</name>
        <value>TRUE</value>
    </property>

</configuration>
[alti-test-01@hdpnightly271-ci-91-services ~]$=20

= The issue remains same


[alti-test-01@hdpnightly271-ci-91-services ~]$ /opt/sqoop-1.4.5/bin/sqoop e=
xport --connect jdbc:mysql://localhost:3306/test --username hive --password=
 ********* --table employee --hcatalog-database default --hcatalog-table em=
p_details1
Warning: /opt/sqoop-1.4.5/bin/../../hbase does not exist! HBase imports wil=
l fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /opt/sqoop-1.4.5/bin/../../accumulo does not exist! Accumulo impor=
ts will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /opt/sqoop-1.4.5/bin/../../zookeeper does not exist! Accumulo impo=
rts will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
16/02/12 08:04:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5
16/02/12 08:04:00 WARN tool.BaseSqoopTool: Setting your password on the com=
mand-line is insecure. Consider using -P instead.
16/02/12 08:04:00 INFO manager.MySQLManager: Preparing to use a MySQL strea=
ming resultset.
16/02/12 08:04:00 INFO tool.CodeGenTool: Beginning code generation
16/02/12 08:04:01 INFO manager.SqlManager: Executing SQL statement: SELECT =
t.* FROM `employee` AS t LIMIT 1
16/02/12 08:04:01 INFO manager.SqlManager: Executing SQL statement: SELECT =
t.* FROM `employee` AS t LIMIT 1
16/02/12 08:04:01 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/h=
adoop
Note: /tmp/sqoop-alti-test-01/compile/1b0d4b1c30f167eb57ef488232ab49c8/empl=
oyee.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
16/02/12 08:04:07 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop=
-alti-test-01/compile/1b0d4b1c30f167eb57ef488232ab49c8/employee.jar
16/02/12 08:04:07 INFO mapreduce.ExportJobBase: Beginning export of employe=
e
16/02/12 08:04:08 INFO mapreduce.ExportJobBase: Configuring HCatalog for ex=
port job
16/02/12 08:04:08 INFO hcat.SqoopHCatUtilities: Configuring HCatalog specif=
ic details for job
16/02/12 08:04:08 INFO manager.SqlManager: Executing SQL statement: SELECT =
t.* FROM `employee` AS t LIMIT 1
16/02/12 08:04:08 INFO hcat.SqoopHCatUtilities: Database column names proje=
cted : [id, name, deg, salary, dept]
16/02/12 08:04:08 INFO hcat.SqoopHCatUtilities: Database column name - info=
 map :
    id : [Type : 4,Precision : 11,Scale : 0]
    name : [Type : 12,Precision : 20,Scale : 0]
    deg : [Type : 12,Precision : 20,Scale : 0]
    salary : [Type : 4,Precision : 11,Scale : 0]
    dept : [Type : 12,Precision : 10,Scale : 0]

16/02/12 08:04:10 INFO hive.metastore: Trying to connect to metastore with =
URI thrift://hive-hdpnightly271-ci-91.test.altiscale.com:9083
16/02/12 08:04:10 INFO hive.metastore: Connected to metastore.
16/02/12 08:04:11 INFO hcat.SqoopHCatUtilities: HCatalog full table schema =
fields =3D [id, name, deg, dept, salary]
16/02/12 08:04:12 ERROR tool.ExportTool: Encountered IOException running ex=
port job: java.io.IOException: The table provided default.emp_details1 uses=
 unsupported  partitioning key type  for column salary : int.  Only string =
fields are allowed in partition columns in Catalog





--001a113d6772906ca7052cf4e4f6--