Hive table backed by a txt file on S3

[Binesh Gummadi <bi...@gmail.com>]

Folks,

I have been using Hive with no issues until now with lzo and gz files
backing Hive tables. Compressed files are stored in Amazon S3 and I am
using EMR to process my data.

However I am having problem pointing S3 table to a flat text file. Text
files contains name and age separated by a tab located in
s3://mybucket/users.txt
Eg:
name1 31
name2 56
name3 40

Created hive table as follows.
create external table S3_users(user_name string, age int) ROW FORMAT
DELIMITED FIELDS TERMINATED BY '\t' location 's3://mybucket/';

I *couldn't* create table pointing to a text file like so:
create external table S3_users(user_name string, age int) ROW FORMAT
DELIMITED FIELDS TERMINATED BY '\t' location 's3://mybucket/users.txt';


select * from S3_users LIMIT 10; <-- *Works*
select * from S3_users where user_name = 'name1' <--  *Doesn't Work*

Exception is follows
java.io.FileNotFoundException: File /users.txt does not exist

What am I doing wrong here? How do I create a hive table pointing to a txt
file?

Thanks
Binnyg

Re: Hive table backed by a txt file on S3

[John Omernik <jo...@omernik.com>]

Try putting the location to the directory the file is in. If there are
other files you don't want to be included make a subdir.

On Thu, Oct 25, 2012 at 6:26 AM, Nitin Pawar <ni...@gmail.com>wrote:

> In that case, it looks like when you do a select * .. its just a cat
> operation.
>
> whats the error you are getting when you put something in where clause?
> If there is no error and you are not getting any result but you expect
> a result then the data loaded into table is not in the format you
> wanted.
>
> Will help if you can share sample data and table structure
>
> On Thu, Oct 25, 2012 at 3:49 AM, Binesh Gummadi
> <bi...@gmail.com> wrote:
> > Folks,
> >
> > I have been using Hive with no issues until now with lzo and gz files
> > backing Hive tables. Compressed files are stored in Amazon S3 and I am
> using
> > EMR to process my data.
> >
> > However I am having problem pointing S3 table to a flat text file. Text
> > files contains name and age separated by a tab located in
> > s3://mybucket/users.txt
> > Eg:
> > name1 31
> > name2 56
> > name3 40
> >
> > Created hive table as follows.
> > create external table S3_users(user_name string, age int) ROW FORMAT
> > DELIMITED FIELDS TERMINATED BY '\t' location 's3://mybucket/';
> >
> > I couldn't create table pointing to a text file like so:
> > create external table S3_users(user_name string, age int) ROW FORMAT
> > DELIMITED FIELDS TERMINATED BY '\t' location 's3://mybucket/users.txt';
> >
> >
> > select * from S3_users LIMIT 10; <-- Works
> > select * from S3_users where user_name = 'name1' <--  Doesn't Work
> >
> > Exception is follows
> > java.io.FileNotFoundException: File /users.txt does not exist
> >
> > What am I doing wrong here? How do I create a hive table pointing to a
> txt
> > file?
> >
> > Thanks
> > Binnyg
>
>
>
> --
> Nitin Pawar
>

Re: Hive table backed by a txt file on S3

[Nitin Pawar <ni...@gmail.com>]

In that case, it looks like when you do a select * .. its just a cat operation.

whats the error you are getting when you put something in where clause?
If there is no error and you are not getting any result but you expect
a result then the data loaded into table is not in the format you
wanted.

Will help if you can share sample data and table structure

On Thu, Oct 25, 2012 at 3:49 AM, Binesh Gummadi
<bi...@gmail.com> wrote:
> Folks,
>
> I have been using Hive with no issues until now with lzo and gz files
> backing Hive tables. Compressed files are stored in Amazon S3 and I am using
> EMR to process my data.
>
> However I am having problem pointing S3 table to a flat text file. Text
> files contains name and age separated by a tab located in
> s3://mybucket/users.txt
> Eg:
> name1 31
> name2 56
> name3 40
>
> Created hive table as follows.
> create external table S3_users(user_name string, age int) ROW FORMAT
> DELIMITED FIELDS TERMINATED BY '\t' location 's3://mybucket/';
>
> I couldn't create table pointing to a text file like so:
> create external table S3_users(user_name string, age int) ROW FORMAT
> DELIMITED FIELDS TERMINATED BY '\t' location 's3://mybucket/users.txt';
>
>
> select * from S3_users LIMIT 10; <-- Works
> select * from S3_users where user_name = 'name1' <--  Doesn't Work
>
> Exception is follows
> java.io.FileNotFoundException: File /users.txt does not exist
>
> What am I doing wrong here? How do I create a hive table pointing to a txt
> file?
>
> Thanks
> Binnyg



-- 
Nitin Pawar