Loading data directories into Hive DB

[Rob Stewart <ro...@googlemail.com>]

Hi there,

Briefly, I'm doing a comparative study on Pig, Hive, JavaMR and JAQL.

I have used a useful tool developed by the fella's in the Pig dev team to
generate my test data.

It allows me to specify the number of mappers to run (it is itself a
MapReduce job). I am left with a folder e.g. for 2 mappers.

InputData/JoinApp/part-00000
InputData/JoinApp/part-00001


Now, for Pig, JAQL and Java MR, all I need to specify is InputData/JoinApp
as the "input" for the scripts, and it understands that it is a partitioned
dataset. e.g. for Pig:
myinput = LOAD 'InputData/JoinApp' USING TextLoader();

and for JAQL:
$input = read(lines("InputData/JoinApp"));

However, with Hive if I try:
CREATE EXTERNAL TABLE Text(words STRING)
LOCATION 'InputData/JoinApp/HiveTable';
hadoop dfs -cp InputData/JoinApp InputData/JoinApp/HiveTable

I get the following error:
cp: Target InputData/JoinApp is a directory

I'm clearly missing something. How can I create a script to allow to import
partitioned datasets as above into a Hive table?

Thanks,

Rob Stewart

Re: Loading data directories into Hive DB

[Mayuran Yogarajah <ma...@casalemedia.com>]

Hi,
>
> I'm clearly missing something. How can I create a script to allow to 
> import partitioned datasets as above into a Hive table?
So you want to load part-00000 and part-00001 as two separate partitions?
If so, you'll need to have those files in separate subdirs.

I put them into subdirs like this, so you can partition it by mapper.
/user/hadoop/InputData/JoinApp/mapper1/part-00000
/user/hadoop/InputData/JoinApp/mapper2/part-00001

Then in Hive:
create external table test (
words string )
partitioned by (mapper string)
stored as textfile location '/user/hadoop;

Then add the partitions:
alter table test add partition (mapper='mapper1') location 
'/user/hadoop/InputData/JoinApp/mapper1';
alter table test add partition (mapper='mapper2') location 
'/user/hadoop/InputData/JoinApp/mapper2';

Sanity check:
hive> show partitions test;
OK
mapper=mapper1
mapper=mapper2

hive> select *from test;
OK
bar     mapper1
foo     mapper2

Hope this helps..

M

Re: Loading data directories into Hive DB

[Zheng Shao <zs...@gmail.com>]

In addition, you can just say:

CREATE EXTERNAL TABLE Text(words STRING)
LOCATION 'InputData/JoinApp';

SELECT * FROM Text LIMIT 10;

You don't need to do "LOAD" because Hive external tables are kind of
"pointers" - they point to your data.
Just create an external table that points to your data, and start querying
the data!


Zheng

On Thu, Jan 14, 2010 at 4:19 PM, Eric Sammer <er...@lifeless.net> wrote:

> On 1/14/10 6:54 PM, Rob Stewart wrote:
> > However, with Hive if I try:
> > CREATE EXTERNAL TABLE Text(words STRING)
> > LOCATION 'InputData/JoinApp/HiveTable';
> > hadoop dfs -cp InputData/JoinApp InputData/JoinApp/HiveTable
> >
> > I get the following error:
> > cp: Target InputData/JoinApp is a directory
> >
> > I'm clearly missing something. How can I create a script to allow to
> > import partitioned datasets as above into a Hive table?
>
> Rob:
>
> The only error here is in the dfs -cp syntax and is unrelated to Hive,
> itself. Hive will handle multiple files in the
> InputData/JoinApp/HiveTable/ directory if you copy them in there, as
> you'd expect.
>
> The proper syntax to copy the files into that Hive directories is:
>
> hadoop dfs -cp 'InputData/JoinApp/*' InputData/JoinApp/HiveTable/
>
> Note the wildcard indicating "all files in JoinApp."
>
> --
> Eric Sammer
> eric@lifeless.net
> http://esammer.blogspot.com
>



-- 
Yours,
Zheng

Re: Loading data directories into Hive DB

[Eric Sammer <er...@lifeless.net>]

On 1/14/10 6:54 PM, Rob Stewart wrote:
> However, with Hive if I try:
> CREATE EXTERNAL TABLE Text(words STRING)
> LOCATION 'InputData/JoinApp/HiveTable';
> hadoop dfs -cp InputData/JoinApp InputData/JoinApp/HiveTable
> 
> I get the following error:
> cp: Target InputData/JoinApp is a directory
> 
> I'm clearly missing something. How can I create a script to allow to
> import partitioned datasets as above into a Hive table?

Rob:

The only error here is in the dfs -cp syntax and is unrelated to Hive,
itself. Hive will handle multiple files in the
InputData/JoinApp/HiveTable/ directory if you copy them in there, as
you'd expect.

The proper syntax to copy the files into that Hive directories is:

hadoop dfs -cp 'InputData/JoinApp/*' InputData/JoinApp/HiveTable/

Note the wildcard indicating "all files in JoinApp."

-- 
Eric Sammer
eric@lifeless.net
http://esammer.blogspot.com