How to point to a single file with external table

Question

Im trying to load hdfs data as external but get the following error.

The folder ml-100k has multiple datasets with different datasets, so I just need to load that particular file.

hive> create external table movie_ratings (movie_id int, user_id int, ratings int, field_4 int) location 'hdfs://hadoop-master:8020/user/hduser/gutenberg/ml-100k/u.data'
    > ;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:hdfs://hadoop-master:8020/user/hduser/gutenberg/ml-100k/u.data is not a directory or unable to create one)

David דודו Markovitz · Accepted Answer · 2017-03-03T15:56:26.623

4

You cannot create a table that points to a file, only to a directory, but there is a feature/bug that allows you to alter the location to a specific file.

create external table movie_ratings (movie_id int, user_id int, ratings int, field_4 int) location 'hdfs://hadoop-master:8020/user/hduser/gutenberg/ml-100k';

alter table movie_ratings set location 'hdfs://hadoop-master:8020/user/hduser/gutenberg/ml-100k/u.data';

edited Mar 03 '17 at 15:56

answered Mar 03 '17 at 15:51

David דודו Markovitz

42,900
6
64
88

How do I load only u.data if the filename is removed? – user1050619 Mar 03 '17 at 15:53
I wrote that I'm still editing the answer :-) see updated answer – David דודו Markovitz Mar 03 '17 at 15:57

54l3d · Answer 2 · 2017-03-03T16:03:45.983

0

You cannot create a Hive table over a specific file, you need to give a directory. So you can create a subdirectory under ml-100k/ and use it like this :

create external table movie_ratings (movie_id int, user_id int, ratings int, field_4 int) location 'hdfs://hadoop-master:8020/user/hduser/gutenberg/ml-100k/new_subfilder/'

The bug mentioned by @Dudu may solve a specific case, but its not safe for general use, because inserting into such table will create new files and will never append the specified one !

edited Mar 03 '17 at 16:03

answered Mar 03 '17 at 15:54

54l3d

3,913
4
32
58

Why do you think this is a bug? – David דודו Markovitz Mar 03 '17 at 16:26
Because its not accepted while creating, and accepted while altering, and because the reason mentioned in my answer. – 54l3d Mar 03 '17 at 16:29
So maybe the 'create' just has a missing functionality. That doesn't indicates a bug. Although the ability to read a specific file seems totally reasonable. If there are issues with the write operation than only the insert can be blocked by design and not the select. – David דודו Markovitz Mar 03 '17 at 16:35

How to point to a single file with external table

2 Answers2

Linked