BigData – Apache Hive Tables

In this section we are going to cover the following :-

1. Type of tables
2. 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.

Creating a Managed Table

How to know the table is Managed table or External table . Use Command ” DESC FORMATTED ‘< table name >’ “.

Checking the Type of Table in Hive

(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.

Creating External Table in Hive

Checking the table information :-

Checking Type of Table in Hive

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.

Creating View in Hive

Lets Query the View :

Views in Hive

View data in Hive which contains only select values.