S3 hive external table on subdirectories is not working

Question

I have following s3 directory structure.

Data/
   Year=2015/
         Month=01/
            Day=01/
                files
            Day=02/
                files
         Month=02/
            Day=01/
                files
            Day=02/
                files
         .
         .
         .

   Year=2014/
         Month=01/
            Day=01/
                files
            Day=02/
                files
         Month=02/
            Day=01/
                files
            Day=02/
                files

So i am creating hive external table as follow

CREATE external TABLE trips
(
 trip_id  STRING,probe_id STRING,provider_id STRING,
 is_moving TINYINT,is_completed BOOLEAN,start_time STRING,
 start_lat  DOUBLE,start_lon DOUBLE,start_lat_adj DOUBLE) 
  PARTITIONED BY (year INT,month INT,day INT)
  STORED AS TEXTFILE
  LOCATION 's3n://accesskey:secretkey@bucket/data/';

When i run query on this table no data is returned without any exception. If i place same files in one directory only and without partitioning, then it runs fine. I also tried bey setting

set mapred.input.dir.recursive=true;
set hive.mapred.supports.subdirectories=true;

Any idea where i am wrong?

score 1 · Answer 1 · answered Dec 08 '17 at 19:27

You need to run ALTER TABLE trips RECOVER PARTITIONS command. This command will create metadata for table partitions which exist in S3. See docs here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-RecoverPartitions(MSCKREPAIRTABLE)

S3 hive external table on subdirectories is not working

1 Answers1