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 C245E1824A for ; Sat, 20 Feb 2016 14:48:50 +0000 (UTC) Received: (qmail 74496 invoked by uid 500); 20 Feb 2016 14:48:49 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 74427 invoked by uid 500); 20 Feb 2016 14:48:49 -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 74417 invoked by uid 99); 20 Feb 2016 14:48:49 -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; Sat, 20 Feb 2016 14:48:48 +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 8F951180179 for ; Sat, 20 Feb 2016 14:48:48 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.179 X-Spam-Level: * X-Spam-Status: No, score=1.179 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_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd3-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id 8Xunssr8xTrd for ; Sat, 20 Feb 2016 14:48:47 +0000 (UTC) Received: from mail-ob0-f177.google.com (mail-ob0-f177.google.com [209.85.214.177]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id 2A72E5FAD3 for ; Sat, 20 Feb 2016 14:48:46 +0000 (UTC) Received: by mail-ob0-f177.google.com with SMTP id jq7so132514600obb.0 for ; Sat, 20 Feb 2016 06:48:46 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=8LPXSgHBQRv8lGPmMQqSlh0S7hyoZY48UJZNtJmMx60=; b=HveXELgscoygscl2I9m+1Z74DN0+1MPCOZys5JbN9Rf413Wvx1UViat573mfJ3nH0q cl8JUIv8O+VbaMMIAIaV+bzCsACQZTgXoNqxweVZcA5USWDQDTyDFkO/sYIEIV2EoXq3 LUl1d1DsK1U5LFDQMdq5etqgEMfBOF10CtK0PwOi/8ghRHAIV9Jj7paTqEn77JShjVXi fKl6gGak16E758Mu9rNJe4rVXs+jwS4EzjULVbf1eQW+DhD9zgnUKbHlhfu6HwGSKVSF TY3DXid+KcA6fo7JIdcjP0hRlOmmjjgQBk90z/i+iIHbvMyFvxYaQ2Cp96bOUPT9Les6 kXQQ== 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:content-type; bh=8LPXSgHBQRv8lGPmMQqSlh0S7hyoZY48UJZNtJmMx60=; b=AGvmRuyNBT/CdJiAES/XXWwDjzTqmv6P0f0T7Yr6oJZxqw08znX8SnDEoyilavTur2 soOLXJKX6go0fnL/o939CpH8j+vngpH52TRQF+FhP481eEg56DQSqvcN9gSSwLOrjGld G8CX50KfzpeXYkUAwHFrIsgm6RRaqveENO1ZEfKZeSio4GqG3bvVTO0Skhc8Qks81W6N R/DHNF6y4vj+KgJ7jJsGDlDYjb5FlML+LOlhIeb0l1L0S+dSRiSNl36kiaMEEZPwEISg GK3F/i7zkeEnQ6mLlN5x3p1HousnEar0nkUFalSpoV13QsiuI2euj1pNqSLB41tjuXJx aolA== X-Gm-Message-State: AG10YORFDa6molWzjJgfLJiLz4HOrzWyj+YTRdwmPTKer2YletfJx32NjazVdNUZpUYd/llURQeflXMVbi8KlA== MIME-Version: 1.0 X-Received: by 10.182.131.194 with SMTP id oo2mr15776804obb.84.1455979725092; Sat, 20 Feb 2016 06:48:45 -0800 (PST) Received: by 10.202.85.144 with HTTP; Sat, 20 Feb 2016 06:48:45 -0800 (PST) Received: by 10.202.85.144 with HTTP; Sat, 20 Feb 2016 06:48:45 -0800 (PST) In-Reply-To: <185fd7bf-84d9-400f-a48f-70af4fc924c1@default> References: <185fd7bf-84d9-400f-a48f-70af4fc924c1@default> Date: Sat, 20 Feb 2016 22:48:45 +0800 Message-ID: Subject: Re: External table returns no result. From: Divya Gehlot To: user@hive.apache.org Content-Type: multipart/alternative; boundary=089e01634b8a507ef4052c34b289 --089e01634b8a507ef4052c34b289 Content-Type: text/plain; charset=UTF-8 Yes Gabriel is correct .. Even I got into same issue and it got resolved by MSCK repair table .....command On Feb 20, 2016 12:35 AM, "Gabriel Balan" wrote: > Hi > > It's not enough to make dirs in hdfs. You need to let the metastore know > you're adding partitions. > Try to Recover Partitions (MSCK REPAIR TABLE) > > . > > > hth > Gabriel Balan > > The statements and opinions expressed here are my own and do not > necessarily represent those of Oracle Corporation. > > ----- Original Message ----- > From: amrit.jangid@goibibo.com > To: hue-user@cloudera.org, user@hive.apache.org > Sent: Friday, February 19, 2016 2:21:29 AM GMT -05:00 US/Canada Eastern > Subject: External table returns no result. > > Hi, > > Trying to run queries over HDFS data using Hive external table. > > Created a table using the following syntax but select * from stats returns > no result. > > CREATE EXTERNAL TABLE `stats`( >> `filename` string, >> `ts` string, >> `type` string, >> `module` string, >> `method` string, >> `line` string, >> `query` string, >> `qt` string, >> `num_results` string, >> `result_count` int, >> `search_time` string, >> `millis` float, >> `ip` string) >> PARTITIONED BY ( >> `years` bigint, >> `months` bigint, >> `days` bigint, >> `hours` int) >> ROW FORMAT DELIMITED >> FIELDS TERMINATED BY '\t' >> STORED AS INPUTFORMAT >> 'org.apache.hadoop.mapred.TextInputFormat' >> OUTPUTFORMAT >> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' >> LOCATION >> 'hdfs://nmlgo1912:8020/user/goibibo/external/logs/provider=stats' > > > The folder structure is as given below, there are *multiple bzip2 files* residing > inside hours folder containing required data. > > > /user/goibibo/external/logs/provider=stats/years=2016/months=201602/days=20160202/hours=01/ > { 1.bzip2, 2.bzip2 ...} > > > Also, if table is created without partition and we point LOCATION directly > to any particular hour everything works fine. Issue is with the partitioned > table. > > Hive 0.13 ( CDH 5.3 ) > > Please help. > -- > > Regards, > Amrit > DataPlatform Team > > --089e01634b8a507ef4052c34b289 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Yes Gabriel is correct ..
Even I got into same issue and it got resolved by MSCK repair=C2=A0 table .= ....command

On Feb 20, 2016 12:35 AM, "Gabriel Balan&qu= ot; <gabriel.balan@oracle.co= m> wrote:
<= div style=3D"font-size:medium">Hi

It's not enough to make = dirs in hdfs.=C2=A0You need to let th= e metastore know you're adding partitions.
Try to=C2=A0Recover Partitions (MSCK REPAIR TABLE).


hth
Gabriel Balan

The statements and opinions expressed here= are my own and do not necessarily represent those of Oracle Corporation.
----- Original Message -----
From: amrit.jangid@goibibo.com
To: hue-user@cloudera.org, user@hive.apa= che.org
Sent: Friday, February 19, 2016 2:21:29 AM GMT -05:00 US/Can= ada Eastern
Subject: External table returns no result.

Hi,=C2=A0

Trying to run queries over HDFS data using H= ive external table.

Created a table using the foll= owing syntax but select * from stats returns no result.=C2=A0
CREATE EXTERNAL TABLE `stats`(
`filename` string,
= `ts` st= ring,
`type` string,
`module` string,
`method` string,
<= span style=3D"color:rgb(38,50,56);font-size:13px;line-height:16px">`line` s= tring,
`query` string,
`qt` string,
`num_results` string,
`resu= lt_count` int,
`search_time` string,
`millis` float,
`ip` string)<= br>PARTITIONED BY (
`years` bigint,
`months` bigint,
`days` bigint= ,
`hours` int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t&#= 39;
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputForm= at'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyT= extOutputFormat'
LOCATION
'hdfs://nmlgo1912:8020/user/= goibibo/external/logs/provider=3Dstats'
<= br>
The folder structure is as given below, there are=C2=A0mul= tiple bzip2 files=C2=A0residing inside hours folder containing required= data.=C2=A0

/user/goibibo/external/logs/provider=3Dstats= /years=3D2016/months=3D201602/days=3D20160202/hours=3D01/ { 1.bzip2, 2.bzip= 2 ...}=C2=A0

Also, if ta= ble is created without partition and we point LOCATION directly to any part= icular hour everything works fine. Issue is with the partitioned table.

Hive 0.13 ( CDH 5.3 )

Please= help.=C2=A0
--
<= div dir=3D"ltr">

Rega= rds,
Amrit =C2=A0
= DataPlatform=C2=A0Team

--089e01634b8a507ef4052c34b289--