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 a little bit different here how data summarized and data processing through the 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, the 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 a 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 mean that local tables on premises or within the database. External tables are external to hive warehouse system with HDFS path. In Managed tables no need to give an extra keyword for at the time of creating a table but in external tables, we need an external keyword for table creation. Mainly if the table was dropped in managed table entire data will be lost but in the external table, only metadata will be lost.

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