How to pick up all data into hive from subdirectories

Question

I have data organized in directories in a particular format (shown below) and want to add these to hive table. I want to add all data of 2012 directory. All below names are directory names, and the inner most dir (3rd level) has the actual data files. Is there any way to pick in the data directly without having to change this dir structure. Any pointers are appreciated.

/2012/
|
|---------2012-01
            |---------2012-01-01
            |---------2012-01-02
            |...
            |...
            |---------2012-01-31
|
|---------2012-02
            |---------2012-02-01
            |---------2012-02-02
            |...
            |...
            |---------2012-02-28
|
|---------2012-03
|...
|...
|---------2012-12

Queries tried so far without luck:

CREATE EXTERNAL TABLE sampledata
(datestr string, id string, locations string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
LOCATION '/path/to/data/2012/*/*'; 

CREATE EXTERNAL TABLE sampledata
(datestr string, id string, locations string)
partitioned by (ystr string, ymstr string, ymdstr string) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|';

ALTER TABLE sampledata
ADD 
PARTITION (ystr ='2012') 
LOCATION '/path/to/data/2012/';

SOLUTION: This small parameter fixes my issue. Adding to the question where it might be beneficial for others:

SET mapred.input.dir.recursive=true;

Which one of the above CREATE TABLE syntax helped you with this. I am facing the same issue. My data is distributed in multiple directories. — pratpor, Jun 09 '16 at 05:49
Did wild card in LOCATION worked for you? I can't get that to work. It creates schema but retrieves no results for me when I query table. — nir, Aug 03 '17 at 22:38

Yash Sharma · Accepted Answer · 2013-12-27T11:26:29.177

11

Answering my own question with solution that works for my case. SET mapred.input.dir.recursive=true;

edited Dec 27 '13 at 11:26

answered Dec 25 '13 at 09:12

Yash Sharma

1,674
2
16
23

3

And also (Hive 1.2): set hive.mapred.supports.subdirectories=true; – mountrix Oct 29 '16 at 01:17
2

How does this change your create external table statement? Do you still use globs in the location? – dlamblin Jun 27 '17 at 23:07

score 1 · Answer 2 · answered Dec 24 '13 at 07:22

1

ALTER TABLE sampledata
ADD 
PARTITION (ystr ='2012', ymstr='2012-01', ymdstr='2012-01-01') 
LOCATION '/path/to/data/2012/2012-01/2012-01-01';

answered Dec 24 '13 at 07:22

pensz

1,871
1
13
18

This will give me data only for the 2012-01-01 directory. I need all the data in the super-parent dir 2012, for all the days for 2012. – Yash Sharma Dec 24 '13 at 07:29
When you query `select * from sampledata where ystr = '2012'`, hive will use all sub directory as input. – pensz Dec 24 '13 at 09:12

score 1 · Answer 3 · answered May 06 '17 at 19:22

1

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

answered May 06 '17 at 19:22

deepak agarwal

31
4

Please spend time tor read this [how-to-answer](http://stackoverflow.com/help/how-to-answer). Only post the code is not the best answer. – thewaywewere May 06 '17 at 19:48

score 0 · Answer 4 · edited Oct 11 '17 at 00:47

0

The following worked on hortonworks

alter table .... set blproperties (
    "hive.input.dir.recursive" = "TRUE",
    "hive.mapred.supports.subdirectories" = "TRUE",
    "hive.supports.subdirectories" = "TRUE",
    "mapred.input.dir.recursive" = "TRUE");

edited Oct 11 '17 at 00:47

answered Oct 10 '17 at 16:32

enjoy

1
1

How to pick up all data into hive from subdirectories

4 Answers4

Linked