Difference between Managed and External Tables with Syntax in HIVE

Difference between Managed and External Tables with Syntax in HIVE

Apache is HIVE is mainly used for data summarization for querying language.  Hive SQL is same like as SQL but little bit different here how data summarized and data processing through query language.

Here explain about Apache tables difference between tables with syntax.

Hive table is logically made up of the data associated with metadata describing the layout of the data in the table

There are two types of Hive table:

1.Managed or Default or Internal Table

2.External Table

 


1.Managed Table:

When will create a table in Hive, default Hive will manage the data, which means that Hive moves the data into its warehouse  directory.

The default warehouse location of HDFS:

“/usr/hive/warehouse/<table name>>/<<table data>>”

 

Syntax to create managed table in Hive

CREATE TABLE <TableName>

ROW FORMAT DELIMITED

FIELDS TERMINATED BY<DELIMITER>

LINES TERMINATED BY<DELIMITER>

STORED AS <FILE FORMAT>;

 

2.External Table:

External tables are external to the hive warehouse path. External table data will be stored in an external location of HDFS which we specify at the table schema.

Warehouse location of HDFS:

“/usr/hive/warehouse/<table name>>/<<table data>>”

 

CREATE EXTERNAL TABLE <TableName>

ROW FORMAT DELIMITED

FIELDS TERMINATED BY<DELIMITER>

LINES TERMINATED BY<DELIMITER>

STORED AS <FILE FORMAT> LOCATION “HDFS PATH”;

 


Difference between Managed table and External Table:

Default tables means that local tables on premises or with in database. External tables are external to hive ware house system with HDFS path. In Managed tables no need to give extra keyword for at the time of create table but in external tables we need external keyword for table creation. Mainly if the table was dropped in managed table entire data will be lose but in external table only meta data will be lose.

Will check with this video for how to create managed tables and load the data in hive and go with as well as External tables.

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *