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 6C3C1951C for ; Fri, 27 Jan 2012 05:38:31 +0000 (UTC) Received: (qmail 31743 invoked by uid 500); 27 Jan 2012 05:38:29 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 30891 invoked by uid 500); 27 Jan 2012 05:38:12 -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 30883 invoked by uid 99); 27 Jan 2012 05:38:09 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 27 Jan 2012 05:38:09 +0000 X-ASF-Spam-Status: No, hits=-0.1 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_MED,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [122.98.10.31] (HELO KECGATE03.infosys.com) (122.98.10.31) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 27 Jan 2012 05:38:01 +0000 X-TM-IMSS-Message-ID: <514c3182000e7425@infosys.com> Received: from blrkechub04.ad.infosys.com ([10.66.236.44]) by infosys.com ([122.98.10.31]) with ESMTP (TREND IMSS SMTP Service 7.1) id 514c3182000e7425 ; Fri, 27 Jan 2012 11:03:39 +0530 Received: from BLRKECHUB09.ad.infosys.com (10.66.236.139) by blrkechub04.ad.infosys.com (10.66.236.44) with Microsoft SMTP Server (TLS) id 8.2.176.0; Fri, 27 Jan 2012 11:07:39 +0530 Received: from hydhtchub02.ad.infosys.com (10.136.68.42) by Blrkechub09.ad.infosys.com (10.66.236.119) with Microsoft SMTP Server (TLS) id 14.1.218.12; Fri, 27 Jan 2012 11:07:39 +0530 Received: from HYDHTCMBX02.ad.infosys.com ([10.136.68.22]) by hydhtchub02.ad.infosys.com ([10.136.68.42]) with mapi; Fri, 27 Jan 2012 11:07:39 +0530 From: Madhusudhana Rao Podila To: "user@hive.apache.org" Date: Fri, 27 Jan 2012 11:07:36 +0530 Subject: Problem with Hive/HBase integration Thread-Topic: Problem with Hive/HBase integration Thread-Index: Aczcs2aZeBkp8qo4QkO8cHeNt54SEA== Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_B04808A8DE78DC40A4CF497F58B9DEC319C19FABD8HYDHTCMBX02ad_" MIME-Version: 1.0 X-Virus-Checked: Checked by ClamAV on apache.org --_000_B04808A8DE78DC40A4CF497F58B9DEC319C19FABD8HYDHTCMBX02ad_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi I have a problem in create a Hive table using existing HBase table (using= External Table concept) with multiple columns from column family (not us= ing as Map) Case-1 : I have created a table in HBase and able to map to Hive as an external ta= ble just using only one column from the column family HBase Created the table in HBase using the following command hbase(main):001:0> create 'hbasetohive', 'colfamily' 0 row(s) in 1.9700 seconds hbase(main):002:0> put 'hbasetohive', '1s', 'colfamily:val','1strowval' 0 row(s) in 0.2240 seconds hbase(main):003:0> scan 'hbasetohive' ROW COLUMN+CELL 1s column=3Dcolfamily:val, timestamp=3D1327676987075,= value=3D1strowva l 1 row(s) in 0.0840 seconds Hive hive> CREATE EXTERNAL TABLE hbase_hivetable_k(key string, value string) > STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' > WITH SERDEPROPERTIES("hbase.columns.mapping" =3D "colfamily:val") > TBLPROPERTIES("hbase.table.name" =3D "hbasetohive"); OK Time taken: 10.808 seconds hive> select * from hbase_hivetable_k; OK 1s 1strowval Time taken: 1.314 seconds Case 2 I have created a table in HBase with column family as cf_cdr with two col= umns caller_name, caller_number; Then I tried creating the Hive table usi= ng the HBase table that got created by specifying both columns from the c= olumn family, It is throwing Metaexteception: If I restrict to only one = column am able to create the table in Hive properly HBase hbase(main):004:0> create 'hb_cdr', 'cf_cdr' 0 row(s) in 1.4870 seconds hbase(main):005:0> put 'hb_cdr', 'cdr_r1', 'cf_cdr:caller_name', 'madhu' 0 row(s) in 0.0490 seconds hbase(main):006:0> put 'hb_cdr', 'cdr_r1', 'cf_cdr:caller_number', '08877= 232010' 0 row(s) in 0.0300 seconds hbase(main):007:0> put 'hb_cdr', 'cdr_r2', 'cf_cdr:caller_name', 'bharat' 0 row(s) in 0.0170 seconds hbase(main):008:0> scan 'hb_cdr' ROW COLUMN+CELL cdr_r1 column=3Dcf_cdr:caller_name, timestamp=3D132767789= 8993, value=3Dmad hu cdr_r1 column=3Dcf_cdr:caller_number, timestamp=3D1327677= 912648, value=3D0 8877232010 cdr_r2 column=3Dcf_cdr:caller_name, timestamp=3D132767791= 9720, value=3Dbha rat 2 row(s) in 0.1020 seconds Hive hive> CREATE EXTERNAL TABLE hv_hb_cdr(key string, c_name string, c_number= string) > STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' > WITH SERDEPROPERTIES("hbase.columns.mapping" =3D "cf_cdr:caller_nam= e, cf_cdr:caller_number") > TBLPROPERTIES("hbase.table.name" =3D "hb_cdr"); FAILED: Error in metadata: MetaException(message:Column Family cf_cdr is= not defined in hbase table hb_cdr) FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exe= c.DDLTask Is there anything issue in the above script? Please suggest Regards Madhusudhana Rao. Podila **************** CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended sol= ely for the use of the addressee(s). If you are not the intended recipient, p= lease notify the sender by e-mail and delete the original message. Further, you= are not to copy, disclose, or distribute this e-mail or its contents to any other= person and any such actions are unlawful. This e-mail may contain viruses. Infosys h= as taken every reasonable precaution to minimize this risk, but is not liable for = any damage you may sustain as a result of any virus in this e-mail. You should carry= out your own virus checks before opening the e-mail or attachment. Infosys reserve= s the right to monitor and review the content of all messages sent to or from t= his e-mail address. Messages sent to or from this e-mail address may be stored on th= e Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS*** --_000_B04808A8DE78DC40A4CF497F58B9DEC319C19FABD8HYDHTCMBX02ad_ Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hi

&= nbsp;

I have a problem in create a Hive table using exis= ting HBase table (using External Table concept) with multiple columns fro= m column family (not using as Map)

 

Case-1 :

I have created a table= in HBase and able to map to Hive as an external table just using only on= e column from the column family

 <= /span>

HBase

Create= d the table in HBase using the following command

        =        

hbase(main):001:0> create '= hbasetohive', 'colfamily'

0 row(s) in 1.9700 seconds

 

hbase(main):002:0> put 'hbasetohiv= e', '1s', 'colfamily:val','1strowval'

0 row(s) in 0.2240 seconds

 

hbase(main):003:0> sca= n 'hbasetohive'

ROW          &n= bsp;         COLUMN+CELL &nb= sp;           &nbs= p;            = ;            =             <= /o:p>

 1s = ;            =        column=3Dcolfamily:val, timestamp=3D= 1327676987075, value=3D1strowva

        &= nbsp;           &n= bsp;  l          &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;

1 row= (s) in 0.0840 seconds

 

Hive=

 

hive> CREATE EXTERNAL TABLE hbase_hiv= etable_k(key string, value string)

    > STORED BY 'org.apache.= hadoop.hive.hbase.HBaseStorageHandler'

    > WITH SERDEPROPERTI= ES("hbase.columns.mapping" =3D "colfamily:val")<= /o:p>

  &= nbsp; > TBLPROPERTIES("hbase.table.name" =3D "hbasetohi= ve");

OK

Ti= me taken: 10.808 seconds

hive> select * from hbase_hivetable_k;

=

OK

1s    &= nbsp; 1strowval

Time taken: 1.314 seconds

<= o:p> 

Case 2

<= p class=3DMsoPlainText> 

I have created a table in HB= ase with column family as cf_cdr with two columns caller_name, caller_num= ber; Then I tried creating the Hive table using the HBase table that got = created by specifying both columns from the column family,  It is th= rowing Metaexteception: If I restrict to only one column am able to creat= e the table in Hive properly

 

HBase

=

hbase(main):004:0> = create 'hb_cdr', 'cf_cdr'

0 row(s) in 1.4870 seconds

 

hbase(main):005:0> put 'hb_cdr', '= cdr_r1', 'cf_cdr:caller_name', 'madhu'

0 row(s) in 0.0490 seconds

<= p class=3DMsoPlainText style=3D'margin-left:.5in'> 

hbase(main):006:0> pu= t 'hb_cdr', 'cdr_r1', 'cf_cdr:caller_number', '08877232010'

0 row(s) in 0.0300 se= conds

 

hba= se(main):007:0> put 'hb_cdr', 'cdr_r2', 'cf_cdr:caller_name', 'bharat'=

0 row(s= ) in 0.0170 seconds

 

hbase(main):008:0> scan 'hb_cdr'

ROW     &= nbsp;           &n= bsp;  COLUMN+CELL        &nb= sp;           &nbs= p;            = ;            =     

 cdr_r1        =         column=3Dcf_cdr:caller_na= me, timestamp=3D1327677898993, value=3Dmad

      &n= bsp;           &nb= sp;    hu        &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ; 

 cdr_r1          =       column=3Dcf_cdr:caller_number, timestamp= =3D1327677912648, value=3D0

         &n= bsp;           &nb= sp; 8877232010         =             &= nbsp;           &n= bsp;           &nb= sp;    

 cdr_r2      &nb= sp;         column=3Dcf_cdr:calle= r_name, timestamp=3D1327677919720, value=3Dbha

     &nbs= p;            = ;     rat       &n= bsp;           &nb= sp;           &nbs= p;            = ;            =  

= 2 row(s) in 0.1020 seconds

&nb= sp;

Hive

 

hive&= gt; CREATE EXTERNAL TABLE hv_hb_cdr(key string, c_name string, c_number s= tring)

    > STOR= ED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

    > WITH SERDEPROPERTIES("= hbase.columns.mapping" =3D "cf_cdr:caller_name, cf_cdr:caller_n= umber")

    >= ; TBLPROPERTIES("hbase.table.name" =3D "hb_cdr");

FAILED: Error in metadata: MetaExcepti= on(message:Column Family  cf_cdr is not defined in hbase table hb_cd= r)

FAILED: Execution Error, return = code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

 

Is there any= thing issue in the above script?

=  

Please suggest

 

Regards

Madhusudhana Rao. Podila

=  

= 
*************=
*** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended sol=
ely 
for the use of the addressee(s). If you are not the intended recipient, p=
lease 
notify the sender by e-mail and delete the original message. Further, you=
 are not 
to copy, disclose, or distribute this e-mail or its contents to any other=
 person and 
any such actions are unlawful. This e-mail may contain viruses. Infosys h=
as taken 
every reasonable precaution to minimize this risk, but is not liable for =
any damage 
you may sustain as a result of any virus in this e-mail. You should carry=
 out your 
own virus checks before opening the e-mail or attachment. Infosys reserve=
s the 
right to monitor and review the content of all messages sent to or from t=
his e-mail 
address. Messages sent to or from this e-mail address may be stored on th=
e 
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***
--_000_B04808A8DE78DC40A4CF497F58B9DEC319C19FABD8HYDHTCMBX02ad_--