1 Answer

0 votes
by
Hive - Create Table

In Hive, we can create a table by using the conventions similar to the SQL. It supports a wide range of flexibility where the data files for tables are stored. It provides two types of table: -

Internal table

External table

Internal Table

The internal tables are also called managed tables as the lifecycle of their data is controlled by the Hive. By default, these tables are stored in a subdirectory under the directory defined by hive.metastore.warehouse.dir (i.e. /user/hive/warehouse). The internal tables are not flexible enough to share with other tools like Pig. If we try to drop the internal table, Hive deletes both table schema and data.

Let's create an internal table by using the following command:-

hive> create table demo.employee (Id int, Name string , Salary float)  

row format delimited  

fields terminated by ',' ;  

Hive Create Table

Here, the command also includes the information that the data is separated by ','.

Let's see the metadata of the created table by using the following command:-

hive> describe demo.employee  

Hive Create Table

Let's see the result when we try to create the existing table again.

Hive Create Table

In such a case, the exception occurs. If we want to ignore this type of exception, we can use if not exists command while creating the table.

hive> create table if not exists demo.employee (Id int, Name string , Salary float)  

row format delimited  

fields terminated by ',' ;   

Hive Create Table

While creating a table, we can add the comments to the columns and can also define the table properties.

hive> create table demo.new_employee (Id int comment 'Employee Id', Name string comment 'Employee Name', Salary float comment 'Employee Salary')  

comment 'Table Description'  

TBLProperties ('creator'='Gaurav Chawla', 'created_at' = '2019-06-06 11:00:00');  

Hive Create Table

Let's see the metadata of the created table by using the following command: -

hive> describe new_employee;  

Hive Create Table

Hive allows creating a new table by using the schema of an existing table.

hive> create table if not exists demo.copy_employee  

like demo.employee;  

Hive Create Table

Hive Create Table

Here, we can say that the new table is a copy of an existing table.

External Table

The external table allows us to create and access a table and a data externally. The external keyword is used to specify the external table, whereas the location keyword is used to determine the location of loaded data.

As the table is external, the data is not present in the Hive directory. Therefore, if we try to drop the table, the metadata of the table will be deleted, but the data still exists.

To create an external table, follow the below steps: -

Let's create a directory on HDFS by using the following command: -

hdfs dfs -mkdir /HiveDirectory  

Now, store the file on the created directory.

hdfs dfs -put hive/emp_details /HiveDirectory  

Let's create an external table using the following command: -

hive> create external table emplist (Id int, Name string , Salary float)  

row format delimited  

 fields terminated by ','   

location '/HiveDirectory';  

Hive Create Table

Now, we can use the following command to retrieve the data: -

select * from emplist;

Related questions

0 votes
asked Sep 7, 2019 in Big Data | Hadoop by john ganales
0 votes
asked Sep 7, 2019 in Big Data | Hadoop by john ganales
...