Internal Tables in Hive – Part-3

This is a continuation of “Internal Tables in Hive” from the previous article. There is an option to decide where to place the data when creating the internal table in the Hive. Apart from the Hive’s default location (/user/hive/warehouse/database/table/), it can be HDFS or local file system.

Let’s see how this can be accomplished.

Consider the same data-set specified in the previous article.

CREATE TABLE Books3(
BookID INT,
BookName STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
STORED AS TEXTFILE
LOCATION ‘/home/cloudera/books’

The location specified above is Cloudera’s home directory which is it’s local by default.

Now, load the data into the table.
LOAD DATA LOCAL INPATH ‘Desktop/book.csv’ INTO TABLE Books2;

Internal Table-3

Now let’s verify the location of the data using Terminal.

InternalTable-3_Terminal-1

If you look at the slide-show, The data is placed in the home directory, not in the HDFS or the usual Hive warehouse. If you drop this table in Hive, you’ll loose this data as well.

Hope you find these articles helpful.