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 66E5810A35 for ; Tue, 3 Sep 2013 18:19:04 +0000 (UTC) Received: (qmail 22536 invoked by uid 500); 3 Sep 2013 18:19:02 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 22252 invoked by uid 500); 3 Sep 2013 18:19:02 -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 22239 invoked by uid 99); 3 Sep 2013 18:19:01 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 03 Sep 2013 18:19:01 +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 Sanjay.Subramanian@wizecommerce.com designates 216.32.180.13 as permitted sender) Received: from [216.32.180.13] (HELO va3outboundpool.messaging.microsoft.com) (216.32.180.13) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 03 Sep 2013 18:18:56 +0000 Received: from mail219-va3-R.bigfish.com (10.7.14.250) by VA3EHSOBE014.bigfish.com (10.7.40.64) with Microsoft SMTP Server id 14.1.225.22; Tue, 3 Sep 2013 18:18:33 +0000 Received: from mail219-va3 (localhost [127.0.0.1]) by mail219-va3-R.bigfish.com (Postfix) with ESMTP id 1B9D58001E8 for ; Tue, 3 Sep 2013 18:18:33 +0000 (UTC) X-Forefront-Antispam-Report: CIP:157.56.232.197;KIP:(null);UIP:(null);IPV:NLI;H:BLUPRD0411HT003.namprd04.prod.outlook.com;RD:none;EFVD:NLI X-SpamScore: -1 X-BigFish: PS-1(zz9371I181fIc85eh14e3Mdd85k9a6kzz1f42h208ch1ee6h1de0h1fdah2073h1202h1e76h1d1ah1d2ah1fc6hzz1de098h18c673h1de097h8275bh8275dhz2fh2a8h839hbe3he5bhf0ah1288h12a5h12bdh137ah1441h1504h1537h153bh162dh1631h1758h18e1h1946h19b5h1ad9h1b0ah1bceh1d0ch1d2eh1d3fh1dc1h1dfeh1dffh1e1dh1fe8h1ff5h1155h) Received-SPF: pass (mail219-va3: domain of wizecommerce.com designates 157.56.232.197 as permitted sender) client-ip=157.56.232.197; envelope-from=Sanjay.Subramanian@wizecommerce.com; helo=BLUPRD0411HT003.namprd04.prod.outlook.com ;.outlook.com ; Received: from mail219-va3 (localhost.localdomain [127.0.0.1]) by mail219-va3 (MessageSwitch) id 13782323108269_23614; Tue, 3 Sep 2013 18:18:30 +0000 (UTC) Received: from VA3EHSMHS006.bigfish.com (unknown [10.7.14.230]) by mail219-va3.bigfish.com (Postfix) with ESMTP id F37E0680040 for ; Tue, 3 Sep 2013 18:18:29 +0000 (UTC) Received: from BLUPRD0411HT003.namprd04.prod.outlook.com (157.56.232.197) by VA3EHSMHS006.bigfish.com (10.7.99.16) with Microsoft SMTP Server (TLS) id 14.16.227.3; Tue, 3 Sep 2013 18:18:21 +0000 Received: from BLUPRD0411MB426.namprd04.prod.outlook.com ([169.254.10.202]) by BLUPRD0411HT003.namprd04.prod.outlook.com ([10.255.127.38]) with mapi id 14.16.0347.000; Tue, 3 Sep 2013 18:18:20 +0000 From: Sanjay Subramanian To: "user@hive.apache.org" Subject: Re: Hive Query - Issue Thread-Topic: Hive Query - Issue Thread-Index: AQHOp9Ay7iBJqEVOzEOEF+EeZbNAW5mz3wAA Date: Tue, 3 Sep 2013 18:18:19 +0000 Message-ID: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.255.127.4] Content-Type: multipart/alternative; boundary="_000_CE4B73DAB165sanjaysubramanianwizecommercecom_" MIME-Version: 1.0 X-OriginatorOrg: wizecommerce.com X-FOPE-CONNECTOR: Id%0$Dn%*$RO%0$TLS%0$FQDN%$TlsDn% X-Virus-Checked: Checked by ClamAV on apache.org --_000_CE4B73DAB165sanjaysubramanianwizecommercecom_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi When you do a SELECT * , the partition columns are returned as last N colu= mns (if u have N partitions) In this case the 63rd column in SELECT * is the partition column Instead of SELECT * Do a SELECT col1, col2, col3, =85.. Not to show the candle to the sun if u r a AWK/SED ninja :-) but to get all= column from hive u can do this hive -e "describe ur_table_name" | awk '{print $1","}'|sed '1i SELECT'|less Thanks sanjay From: Manickam P > Reply-To: "user@hive.apache.org" > Date: Monday, September 2, 2013 4:32 AM To: "user@hive.apache.org" > Subject: Hive Query - Issue Hello Experts, when i try to execute the below query i'm getting error. Please help me to = correct this. insert overwrite table table_baseline partition (sourcedate=3D'base_2013_08= ') select * from (select * from table_a where sourcedate=3D'tablea_2013_08'= union all select * from table_b where sourcedate=3D'tableb_2013_08') final My intention here is i want to populate the table_baseline by using the all= records from table_a and table_b with partition. I am getting the below er= ror. Error in semantic analysis: Line 1:23 Cannot insert into target table becau= se column number/types are different ''BASE_2013_08'': Table insclause-0 ha= s 62 columns, but query has 63 columns. I verified the column count and types everything is same but here it says s= ome difference. The same query works fine without having any partitions in = all the three tables but getting error while executing with partitions. please help. Thanks Manickam P CONFIDENTIALITY NOTICE =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D This email message and any attachments are for the exclusive use of the int= ended recipient(s) and may contain confidential and privileged information.= Any unauthorized review, use, disclosure or distribution is prohibited. If= you are not the intended recipient, please contact the sender by reply ema= il and destroy all copies of the original message along with any attachment= s, from your computer system. If you are the intended recipient, please be = advised that the content of this message is subject to access, review and d= isclosure by the sender's Email System Administrator. --_000_CE4B73DAB165sanjaysubramanianwizecommercecom_ Content-Type: text/html; charset="Windows-1252" Content-ID: <895D691D1D52204EAF2CA069CA8DE1A7@namprd04.prod.outlook.com> Content-Transfer-Encoding: quoted-printable
Hi 

When you do a SELECT * ,  the partition columns are returned as l= ast N columns  (if u have N partitions) 

In this case the 63rd column in SELECT * is the partition column

Instead of SELECT *
Do a 
SELECT
             col1,
             col2,
             col3,  
=85..


Not to show the candle to the sun if u r a AWK/SED ninja :-) but = to get all column from hive u can do this

hive -e "describe ur_table_name" | awk '{print $1","}'|sed '1i SELECT'|= less

Thanks

sanjay
From: Manickam P <manickam.p@outlook.com>
Reply-To: "user@hive.apache.org" <user@hive.apache.org>
Date: Monday, September 2, 2013 4:3= 2 AM
To: "user@hive.apache.org" <user@hive.apache.org>
Subject: Hive Query - Issue

Hello Experts, 

when i try to execute the below query i'm getti= ng error. Please help me to correct this.

insert overwrite table table_baseline partition= (sourcedate=3D'base_2013_08') select * from (select * fro= m table_a where sourcedate=3D'tablea_2013_08' union all select * from table= _b where sourcedate=3D'tableb_2013_08') final

My intention here is i want to populate the table_baseline by using th= e all records from table_a and table_b with partition. I am getting the bel= ow error. 

Error in semantic analysis: Line 1:23 Cannot insert into target tab= le because column number/types are different ''BASE_2013_08'': Table inscla= use-0 has 62 columns, but query has 63 columns.

I verified the column count and types everything is same but here it s= ays some difference. The same query works fine without having any partition= s in all the three tables but getting error while executing with partitions= . 


please help.



Thanks
Manickam P

CONFIDENTIALITY NOTICE
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
This email message and any attachments are for the exclusive use of the int= ended recipient(s) and may contain confidential and privileged information.= Any unauthorized review, use, disclosure or distribution is prohibited. If= you are not the intended recipient, please contact the sender by reply email and destroy all copies of the ori= ginal message along with any attachments, from your computer system. If you= are the intended recipient, please be advised that the content of this mes= sage is subject to access, review and disclosure by the sender's Email System Administrator.
--_000_CE4B73DAB165sanjaysubramanianwizecommercecom_--