UnderStanding External Table In Hive


Usually when you create tables in hive using raw data in HDFS, it moves them to a different location – “/user/hive/warehouse”. If you created a simple table, it will be located inside the data warehouse. The following hive command creates a table with data location at “/user/hive/warehouse/empl”

hive> CREATE TABLE EMPL(ID int,NAME string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
OK
Time taken: 0.933 seconds

now load csv into this table

hive> load data inpath 'hdfs://localhost:54310/dat.csv' into table empl;
Loading data to table default.empl
Table default.empl stats: [numFiles=0, numRows=0, totalSize=0, rawDataSize=0]
OK

first

now try to drop this table

hive> drop table empl;
OK
Time taken: 0.397 seconds

second
When you drop the table, the raw data is lost as the directory corresponding to the table in warehouse is deleted.
You may also not want to delete the raw data as some one else might use it

so here comes the concept of external table

For External Tables ,Hive does not move the data into its warehouse directory. If the external table is dropped, then the table metadata is deleted but not the data.have a look at the below commands

hive> CREATE EXTERNAL TABLE EMPL(ID int,NAME string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/opt/carbonStore' ;
OK
Time taken: 0.13 seconds

To create external table, simply point to the location of data while creating the tables. This will ensure that the data is not moved into a location inside the warehouse directory.

now load data in this table

hive> load data inpath 'hdfs://localhost:54310/opt/carbonStore/dat.csv' into table empl;
Loading data to table default.empl
Table default.empl stats: [numFiles=0, totalSize=0]
OK
Time taken: 0.379 seconds

now drop this table

hive> drop table empl;
OK
Time taken: 0.301 seconds

even though table is deleted but still raw data is not

final.png

i hope this blog will help to understand external table concept

KNOLDUS-advt-sticker

Advertisements
This entry was posted in Scala. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s