Hive DDL Commands – Types of DDL Hive Commands

In this article, we are going to learn Hive DDL commands. The article describes the Hive Data Definition Language(DDL) commands for performing various operations like creating a table/database in Hive, dropping a table/database in Hive, altering a table/database in Hive, etc.

There are many DDL commands. This article will cover each DDL command individually, along with their syntax and examples.

For running Hive DDL commands, you must have Hive installed on your system.

Follow the Hive-3.1.2 installation guide to install Hive on your system.

Introduction to Hive DDL commands

Hive DDL commands are the statements used for defining and changing the structure of a table or database in Hive. It is used to build or modify the tables and other objects in the database.

The several types of Hive DDL commands are:

1. CREATE
2. SHOW
3. DESCRIBE
4. USE
5. DROP
6. ALTER
7. TRUNCATE

Table-1 Hive DDL commands

DDL Command	Use With
CREATE	Database, Table
SHOW	Databases, Tables, Table Properties, Partitions, Functions, Index
DESCRIBE	Database, Table, view
USE	Database
DROP	Database, Table
ALTER	Database, Table
TRUNCATE	Table

Before moving forward, note that the Hive commands are case-insensitive.

CREATE DATABASE is the same as create database.

So now, let us go through each of the commands deeply. Let’s start with the DDL commands on Databases in Hive.

DDL Commands On Databases in Hive

1. CREATE DATABASE in Hive

The CREATE DATABASE statement is used to create a database in the Hive. The DATABASE and SCHEMA are interchangeable. We can use either DATABASE or SCHEMA.

Syntax:

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

DDL CREATE DATABASE Example:

Here in this example, we are creating a database ‘dataflair’.

2. SHOW DATABASE in Hive

The SHOW DATABASES statement lists all the databases present in the Hive.

Syntax:

SHOW (DATABASES|SCHEMAS);

DDL SHOW DATABASES Example:

3. DESCRIBE DATABASE in Hive

The DESCRIBE DATABASE statement in Hive shows the name of Database in Hive, its comment (if set), and its location on the file system.

The EXTENDED can be used to get the database properties.

Syntax:

DESCRIBE DATABASE/SCHEMA [EXTENDED] db_name;

DDL DESCRIBE DATABASE Example:

4. USE DATABASE in Hive

The USE statement in Hive is used to select the specific database for a session on which all subsequent HiveQL statements would be executed.

Syntax:

USE database_name;

DDL USE DATABASE Example:

5. DROP DATABASE in Hive

The DROP DATABASE statement in Hive is used to Drop (delete) the database.

The default behavior is RESTRICT which means that the database is dropped only when it is empty. To drop the database with tables, we can use CASCADE.

Syntax:

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

DDL DROP DATABASE Example:

Here in this example, we are dropping a database ‘dataflair’ using the DROP statement.

6. ALTER DATABASE in Hive

The ALTER DATABASE statement in Hive is used to change the metadata associated with the database in Hive.

Syntax for changing Database Properties:

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);

DDL ALTER DATABASE properties Example:

In this example, we are setting the database properties of the ‘dataflair’ database after its creation by using the ALTER command.

Syntax for changing Database owner:

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

DDL ALTER DATABASE owner Example:

In this example, we are changing the owner role of the ‘dataflair’ database using the ALTER statement.

Syntax for changing Database Location:

ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;

Note: The ALTER DATABASE … SET LOCATION statement does not move the database current directory contents to the newly specified location.

This statement does not change the locations associated with any tables or partitions under the specified database. Instead, it changes the default parent-directory, where new tables will be added for this database.

No other metadata associated with the database can be changed.

DDL Commands on Tables in Hive

1. CREATE TABLE

The CREATE TABLE statement in Hive is used to create a table with the given name. If a table or view already exists with the same name, then the error is thrown. We can use IF NOT EXISTS to skip the error.

Syntax:

CREATE TABLE [IF NOT EXISTS] [db_name.] table_name [(col_name data_type [COMMENT col_comment], ... [COMMENT col_comment])] [COMMENT table_comment] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path];

 DDL CREATE TABLE Example:

In this table, we are creating a table ‘Employee’ in the ‘dataflair’ database.

ROW FORMAT DELIMITED means we are telling the Hive that when it finds a new line character, that means a new record.

FIELDS TERMINATED BY ‘,’ tells Hive what delimiter we are using in our files to separate each column.

STORED AS TEXTFILE is to tell Hive what type of file to expect.

2. SHOW TABLES in Hive

The SHOW TABLES statement in Hive lists all the base tables and views in the current database.

Syntax:

SHOW TABLES [IN database_name];

DDL SHOW TABLES Example:

3. DESCRIBE TABLE in Hive

The DESCRIBE statement in Hive shows the lists of columns for the specified table.

Syntax:

DESCRIBE [EXTENDED|FORMATTED] [db_name.] table_name[.col_name ( [.field_name])];

DDL DESCRIBE TABLE Example:

4. DROP TABLE in Hive

The DROP TABLE statement in Hive deletes the data for a particular table and remove all metadata associated with it from Hive metastore.

If PURGE is not specified then the data is actually moved to the .Trash/current directory. If PURGE is specified, then data is lost completely.

Syntax:

DROP TABLE [IF EXISTS] table_name [PURGE];

DDL DROP TABLE Example:

In the below example, we are deleting the ‘employee’ table.

5. ALTER TABLE in Hive

The ALTER TABLE statement in Hive enables you to change the structure of an existing table. Using the ALTER TABLE statement we can rename the table, add columns to the table, change the table properties, etc.

Syntax to Rename a table:

ALTER TABLE table_name RENAME TO new_table_name;

DDL ALTER TABLE name Example:

In this example, we are trying to rename the ‘Employee’ table to ‘Com_Emp’ using the ALTER statement.

Syntax to Add columns to a table:

ALTER TABLE table_name ADD COLUMNS (column1, column2) ;

DDL ALTER TABLE columns Example:

In this example, we are adding two columns ‘Emp_DOB’ and ‘Emp_Contact’ in the ‘Comp_Emp’ table using the ALTER command.

Syntax to set table properties:

ALTER TABLE table_name SET TBLPROPERTIES (‘property_key’=’property_new_value’);

DDL ALTER TABLE properties Example:

In this example, we are setting the table properties after table creation by using ALTER command.

6. TRUNCATE TABLE

TRUNCATE TABLE statement in Hive removes all the rows from the table or partition.

Syntax:

TRUNCATE TABLE table_name;

DDL TRUNCATE TABLE Example:

Summary

Thus in this article, we have studied how to create a database, drop a database, alter a database, etc. We have also studied statements for creating, dropping, altering a table in Hive.

These are the basic HiveQL DDL statements that one should know before working with Hive.

Still, if you have doubts, feel free to ask in the comment section.

Keep Learning!!