[Hive-user] Loading files into tables

Look up for local :
http://wiki.apache.org/hadoop/Hive/GettingStarted

-Ajo.

Thanks Ajo.
Please confirm if my understanding is correct.
That means when I do "LOAD DATA *LOCAL* INPATH 'filepath' [OVERWRITE] INTO
TABLE tablename" data in is local file system. If I need to run HIVE queries
(which in turn would be converted to Map Reduce jobs) I need to pull the
data some other table for which data is in HDFS by means of

INSERT OVERWRITE TABLE tablename_new SELECT * FROM tablename ... (kind of)

So those LOCAL tables are kind of temporary.

Amlan

(which comes from hive-default.xml /user/hive/warehouse/ ).

create table test_table ( test string);
load data local inpath '/root/1.csv' into table test_table;
describe extended test_table; # "find the hdfs dir of table test_table, for
my case the data of table test_table is saved under
hdfs /root/hive/warehouse/test_table
dfs -ls /root/hive/warehouse/test_table
-rw-r--r-- 3 root supergroup 1445813 2011-02-01 11:50
/root/hive/warehouse/test1/1.csv

HDFS gives you the ability to distribute disk access for jobs across
computers.

You don't need to have the file in HDFS to run a hive job.

Local tables are like hive tables in all other senses except that they are
on the local disk rather than HDFS. The only other difference I know of is
that when you call "drop table" on a local table, only the metadata on the
table gets deleted. For tables on HDFS, the table data gets deleted with the
metadata.

-Ajo.

Local tables are like hive tables in all other senses except that they are on the local disk rather than HDFS. The only other difference I know of is that when you call "drop table" on a local table, only the metadata on the table gets deleted. For tables on HDFS, the table data gets deleted with the metadata.


Ajo,

Guess there is a confusion here. No concept of Local tables in Hive AFAIK. The behavior you mention is for EXTERNAL tables. And the data for external tables can be on local file system or HDFS, depending on configuration. The other tables are addressed as MANAGED tables for which Hive creates a directory under warehouse dir.

-Ajo.

On Tue, Feb 1, 2011 at 8:41 AM, Amlan Mandal wrote:
Thanks Ajo.
Please confirm if my understanding is correct.
That means when I do "LOAD DATA *LOCAL* INPATH 'filepath' [OVERWRITE] INTO TABLE tablename" data in is local file system. If I need to run HIVE queries (which in turn would be converted to Map Reduce jobs) I need to pull the data some other table for which data is in HDFS by means of

INSERT OVERWRITE TABLE tablename_new SELECT * FROM tablename ... (kind of)

So those LOCAL tables are kind of temporary.

See - http://wiki.apache.org/hadoop/Hive/LanguageManual/DML That should clarify load local.


Amlan

Yes. Hive creates a file for it on HDFS.

As Ping Zhu mentioned, do a 'describe formatted <tablename>' or 'describe extended <tablename>' after loading data. Check that location on HDFS.

You can also check the logs (they are usually at /tmp/<username>/hive.log). You can see the local file getting copied to HDFS scratch directory and then being moved to a directory under warehouse. If you find anything strange, can u please post them here?

Sent from Amlan's iPhone

Ow boy.
Hive has a directory in HDFS called the warehouse directory default
/user/hive/warehouse
When you run 'create table atable' it is managed by a directory
/user/hive/warehouse/atable
If you load afile.txt into that table it goes here
/user/hive/warehouse/atable/afile

The differences (to name a few)
1) is EXTERNAL tables are NOT inside /user/hive/warehouse. They are
anywhere because external tables allow you
to specify LOCATION /user/edward/bla for the table, (and for
partitions that may be inside the table)
2) If DROP an EXTERNAL table no data is delete from HDFS.

Your confusion might stem from the fact that tables are either normal
'create table X' or external 'create external table X' but hive has no
'internal' keyword.