In this section we are going to cover the following :-
- Type of tables
- Creating views
Type of Tables : You can have Managed table and External Table in Hive.
(i) Managed Table :
Managed tables are like normal database tables if you drop the table both data and the table will no longer present in your Database. Internal tables get stored in the directory “/user/hive/warehouse” by default it can be changed by updating the location in the config “hive.metastore.warehouse.dir“.
Managed table also goes by the name “Internal table“.
Use : When you want Hive to manage the lifecycle of your table and data.
How to know the table is Managed table or External table . Use Command ” DESC FORMATTED ‘< table name >’ “.
(ii) External Table :
External Tables are the table which is stored in HDFS. If you drop your external table, the table will be dropped but data will be still there in HDFS. This is because Hive maintains its metadata on the master node, therefore, the deleting of table won’t delete the data.
To create external table . Use command :
CREATE EXTERNAL TABLE <table name> ( col datatype ) LOCATION ‘/hdfs/’;
Use : The Table and data used by many processes like ETL etc.
Checking the table information :-
2 . VIEW in Hive :
View is another feature close to relation database View. View is logical object build to query data by hiding away its complex queries like where clause , sub-joins, and other filter conditions. In Hive, a view never stores the data like the table.
You can create a View following ways :
CREATE VIEW <Viewname> AS SELECT <columns > FROM <table name>;
CREATE VIEW <viewname> AS SELECT * FROM <tab1> JOIN <tab2> ON <conditon>;
We already have one table which contains data of employs Id , name, and their salary.
Lets Query the View :