Hive DDL Commands – Types of DDL Hive Commands

Want to run Hive queries for creating, modifying, dropping, altering tables and databases?

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.

Keeping you updated with latest technology trends, Join DataFlair on Telegram

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 CommandUse With
CREATEDatabase, Table
SHOWDatabases, Tables, Table Properties, Partitions, Functions, Index
DESCRIBEDatabase, Table, view
USEDatabase
DROPDatabase, Table
ALTERDatabase, Table
TRUNCATETable

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

CREATE DATABASE - Hive DDL commands

Creating DataBase - Hive DDL commands

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:

SHOW DATABASES - Hive DDL commands

listing DataBase - Hive DDL commands

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:

DESCRIBE DATABASE - Hive DDL commands

Describing DataBase - Hive DDL commands

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:

USE DATA BASE - Hive DDL commands

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.

DROP DATABASE - Hive DDL commands

listing DataBase - Hive DDL commands

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.

SETDB PROPERTIES- Hive DDL commands

checking DB PROPERTIES - Hive DDL commands

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.

SETOWNERROLE - Hive DDL commands

Showing changed role - Hive DDL commands

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.

CREATE TABLE - Hive DDL commands

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.

Don’t know about different Data Types supported by hive? Read Hive Data Types article.

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:

SHOW TABLES - Hive DDL commands

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:

DESCRIBE TABLE - Hive DDL commands

4. DROP TABLE in Hive

Hadoop Quiz

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.

DROP TABLE - Hive DDL commands

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.

ALTERTABLERENAME - Hive DDL commands

listing table afterrename - Hive DDL commands

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.

ALTER TABLE ADD COLUMN - Hive DDL commands

describingtableafterrename-HiveDDLcommands

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.

ALTER TABLE PROPERTIES - Hive DDL commands

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:

TRUNCATE TABLE - Hive DDL commands

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.

Don’t miss Apache Hive Architecture & Components in detail.

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

Keep Learning!!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.