Create external table hive, location contains multiple files inside

Question

CREATE EXTERNAL TABLE IF NOT EXISTS LOGS (LGACT STRING,NTNAME STRING)  
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  
LOCATION '/user/hive/warehouse/LOGS/test';

under 'test' folder I am writing files daily. for eg:

/user/hive/warehouse/LOGS/test/20170420
/user/hive/warehouse/LOGS/test/20170421
/user/hive/warehouse/LOGS/test/20170422

I cannot see any data inside LOGS table that i have created.

But, I create the table using

LOCATION '/user/hive/warehouse/LOGS/test/20170422';

I can see that days records.

I want to see all the data under /test directory in my HIVE table, also the /test directory is populated daily with new files.

I have already answered this question here: https://stackoverflow.com/a/43804942/2700344 — leftjoin, Jun 03 '17 at 05:16
Possible duplicate of [Hive create table with inputs from nested sub-directories](https://stackoverflow.com/questions/24438148/hive-create-table-with-inputs-from-nested-sub-directories) — leftjoin, Jun 03 '17 at 06:24
@leftjoin - It is like arguing that the way to restart an old Windows was to press `ctrl`+`alt`+`delete`+`esc`+`space` — David דודו Markovitz, Jun 03 '17 at 12:52

David דודו Markovitz · Accepted Answer · 2017-06-03T09:15:41.193

Option 1

In order to support sub-directories

set mapred.input.dir.recursive=true;

and if you Hive version is lower than 2.0.0 then also

set hive.mapred.supports.subdirectories=false;

Option 2

Create a partitioned table

CREATE EXTERNAL TABLE IF NOT EXISTS LOGS (LGACT STRING,NTNAME STRING)  
partitioned by (dt date)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  
LOCATION '/user/hive/warehouse/LOGS/test';

alter table LOGS add if not exists partition (dt=date '2017-04-20') LOCATION '/user/hive/warehouse/LOGS/test/20170420';
alter table LOGS add if not exists partition (dt=date '2017-04-21') LOCATION '/user/hive/warehouse/LOGS/test/20170421';
alter table LOGS add if not exists partition (dt=date '2017-04-22') LOCATION '/user/hive/warehouse/LOGS/test/20170422';

It would be easier to manage if you keep your directories using the standard convention, e.g. dt=2017-04-20 instead of 20170420

Thanks. Option 1 does the work. But if I do select * on hive command line i can see the data. But if i check the same table using HUE i dont see any records? Do we need to change any config on HUE level ? — voldy, Jun 05 '17 at 18:04

score 0 · Answer 2 · answered Jun 03 '17 at 08:56

0

By default hive reads only the files (not directories) inside the specified location in external table. If you want to enable adding the directories, then set the below parameter:

set mapred.input.dir.recursive=true;

answered Jun 03 '17 at 08:56

Lakshman Battini

1,842
11
25

This is only a partial answer and the full answer was already given. – David דודו Markovitz Jun 03 '17 at 09:11

Create external table hive, location contains multiple files inside

2 Answers2

Linked

Related