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" <gabriel.ba...@oracle.com> 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) > <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-RecoverPartitions(MSCKREPAIRTABLE)> > . > > > 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.jan...@goibibo.com > To: hue-u...@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 > >
