hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gabriel Balan <gabriel.ba...@oracle.com>
Subject Re: External table returns no result.
Date Fri, 19 Feb 2016 16:34:52 GMT

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 


Mime
View raw message