Hive DDL Commands Explained with Examples

Hive supports most of all traditional SQL commands since there are many commands, let’s learn the most commonly used Hive DDL (Data Definition Language) commands with examples.

Hive DDL Database Commands

Among several Hive DDL Commands, here I will be covering the most commonly used DDL commands.

DDL commands are used to create databases, tables, modify the structure of the table, and drop the database and tables e.t.c.

Create Database

In Hive, CREATE DATABASE statement is used to create a Database, this takes an optional clause IF NOT EXISTS, using this option, it creates only when database not already exists.

If the Database already exists you will get an error Database db_name already exists.
To check if the database already exists before creating, use IF NOT EXISTS clause.
You can change the location of the database using LOCATION clause


CREATE DATABASE IF NOT EXISTS emp;

By default Hive stores the database at warehouse location /user/hive/warehouse, Below command changes the location of the database.


CREATE DATABASE temp LOCATION '/project/hive/warehouse';

Show Databases

Hive by default contains a default database. You can get all databases in Hive using SHOW DATABASES; statement.


0: jdbc:hive2://> SHOW DATABASES;
OK
default
emp
Time taken: 0.059 seconds, Fetched: 2 row(s)

Use Database

By using the USE command you can set the current database for all subsequent HiveQL statements.


0: jdbc:hive2://>USE emp;

Describe Database

Describes the database


0: jdbc:hive2://>DESCRIBE DATABASE emp;
0: jdbc:hive2://>DESCRIBE SCHEMA emp; 
0: jdbc:hive2://>DESCRIBE DATABASE EXTENDED emp;
0: jdbc:hive2://>DESCRIBE SCHEMA EXTENDED emp;

These commands yields the below output.


0: jdbc:hive2://>DESCRIBE DATABASE emp;
+----------+----------+----------------------------------------------------+-------------+-------------+-------------+
| db_name  | comment  |                      location                      | owner_name  | owner_type  | parameters  |
+----------+----------+----------------------------------------------------+-------------+-------------+-------------+
| emp      |          | hdfs://192.168.1.148:9000/user/hive/warehouse/emp.db | prabha      | USER        |             |
+----------+----------+----------------------------------------------------+-------------+-------------+-------------+
1 row selected (0.07 seconds)
0: jdbc:hive2://>

Drop Database


jdbc:hive2://>DROP DATABASE emp;
jdbc:hive2://>DROP DATABASE emp CASCADE;
jdbc:hive2://>DROP DATABASE emp RESTRICT;

Hive DDL Table Commands

In this section, let’s learn the most used HIve DDL commands that are used on the Tables.

Create Table

Hive supports many types of tables like Managed, External, Temporary and Transactional tables. To make it simple for our example here, I will be Creating a Hive managed table.

Hive managed table is also called the Internal table where Hive owns and manages the metadata and actual table data/files on HDFS.


CREATE TABLE IF NOT EXISTS emp.employee (
 id int,
 name string,
 age int,
 gender string )
 COMMENT 'Employee Table'
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ',';

Show Tables

In order to list all existing tables in a database use SHOW TABLES command


SHOW TABLES; // This shows all tables from the current database
SHOW TABLES in emp; // This shows all tables in the emp database


+--------------------+
|      tab_name      |
+--------------------+
| employee           |
+--------------------+
1 rows selected (0.067 seconds)
0: jdbc:hive2://>

Describe Table

To know the structure of the table use DESCRIBE TABLE command

For more information you can also try these commands.


jdbc:hive2://> DESCRIBE FORMATTED employee;
jdbc:hive2://> DESCRIBE EXTENDED employee;

Truncate Table

Truncate table is used to truncate the table meaning it deletes all the contents of the table and the structure of the table


jdbc:hive2://>TRUNCATE TABLE emp;

Alter Table

To rename a Table use ALTER TABLE command.


jdbc:hive2://>ALTER TABLE employee RENAME TO employee2;

Drop Table

Drop Table is used to Drop the table from a Hive database.


jdbc:hive2://>DROP TABLE employee2;
jdbc:hive2://>DROP TABLE emp.employee2;
jdbc:hive2://>DROP TABLE IF EXISTS employee2 PURGE;

Conclusion

In this Most Used Hive DDL Commands, you have learned several HiveQL commands that are used to create database, tables, update these and finally dropping these.

Happy Learning!!