Internal and External tables in Hive

admin

Today, I will present types of tables that can be created in hive in a different way.
I created three tables in hive.
create table empdtls (emp_id int ); { This is called Internal or Managed table}

create external table empdtls_ext (emp_id int ); { This is External table }

create external table empdtls_ext_v2 ( emp_id int ) location ‘/user/demo/hivetesting/’ { This is also called External table}

Now, we will check the TABLE_TYPE of all above tables using command DESCRIBE FORMATTED table_name

Internal Or Managed Table

/user/hive/warehouse/ is the location where all managed tables are present.

External Table without mentioning Location in create statement

External table data will be available at same path where internal tables data is present.
Table type is also reflecting as “MANAGED_TYPE”.

Now, question that comes to our mind is whether it will behave in same way as Internal table. Lets see …….

External table with location in create statement

Table Type is “EXTERNAL_TABLE”

TRUNCATE COMMAND

Internal Table :
truncate table default.empdtls;

It will truncate the data present in the empdtls table and file also gets deleted from the Managed hdfs location.


External Table with or without location in create statement
truncate table default.empdtls_ext;
truncate table default.empdtls_ext_v2;
It will throw the below error



DROP COMMAND
drop table empdtls;
drop table empdtls_ext;
drop table empdtls_ext_v2;

In case of drop command, folder structure will be removed from hdfs/s3 location in case of internal table only .See the below SS for this .




Related posts