HIVE: INTERNAL AND EXTERNAL TABLES

By default, the table created in the hive is an internal table, so we need not specify an internal keyword while table creation.

In the above query, we use the “comment”  keyword to refer to the table as “Internal student table” for our own reference, but you can comment it anything you want.

By using the keywords rows format delimited and fields terminated by (’|’) we inform Hive about the format of the data. The columns can be differentiated using the pipe symbol (|). This helps hive to understand how to read and write the data.

Map keys in the ‘subj_score’ column can be differentiated by the (:) symbol and the collection items in the ‘gender_age’ struct is differentiated using the (,) symbol.

Now that we have learned to create an internal table let’s see how we can load the data and populate it.

_{Loading the Data}

To load the data, we use the following command:

LOAD DATA LOCAL INPATH '/home/Hadoop/student.txt'
OVERWRITE INTO TABLE student_internal

Here we are importing the data from a file present in the local HDFS path-‘/home/Hadoop/student.txt’ and overwriting or we can say loading it into the student_internal table we created above.

_Storage

An internal table is stored on HDFS in the /user/hive/warehouse directory which is its default storage location. This location can be changed by updating the path in the configuration file present in the config file – hive.metastore.warehouse.dir.

We can also alter the location of the table by providing a new path present in HDFS using the SET LOCATION clause.

 ALTER TABLE student_internal
SET LOCATION
 'hdfs://localhost:8020/user/tables/student';

_Security

Hive is exclusively responsible for the security and management of the data present in the internal table.

If we delete the table using the drop command, then the metadata, as well as the data present in the table, will get deleted from the master node and HDFS respectively. So, it is strongly advised to never use the drop command on an internal table because it may be serving a production line of a business.

As a solution to this problem, we can use ENABLE NO_DROP clause to alter the table which will prevent the table from getting dropped.

> ALTER TABLE student_internal ENABLE NO_DROP;

If we want to prevent the data from being queried, then we can use the ENABLE ONLINE clause.

> ALTER TABLE student_internal ENABLE OFFLINE;

_Usage

We may use an internal table if:

1. Data is temporary and doesn’t affect businesses in real-time.

2. If we want the hive to manage the data and the tables.

External Table

When a user creates a table in Hive specifying the external keyword, then an external table is created. The data present in the external table will be fully managed by HDFS contrary to an internal table.

_{Creating a Table}

For creating an external table, we use the flowing command:

>CREATE EXTERNAL TABLE  student_external
(
 name string,
class ARRAY,
gender_age STRUCT,
subj_score MAP
            )
                    COMMENT ' External student table'
       ROW FORMAT DELIMITED
           FIELDS TERMINATED BY '|'
                    COLLECTION ITEMS TERMINATED BY ','
             MAP KEYS TERMINATED BY ':'
      STORED AS TEXTFILE;
                     LOCATION '/user/tables/students';

                As seen above, the external table has 4 columns-

>LOAD DATA LOCAL INPATH '/home/Hadoop/student.txt'
 OVERWRITE INTO TABLE student_external