External table returns no result.

[Amrit Jangid <am...@goibibo.com>]

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

--