Site icon Spark By {Examples}

Hive DDL Commands Explained with Examples

Naveen Nelamali
hive ddl commands

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.

Before you proceed make sure you have HiveServer2 started and connected to Hive using Beeline

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.


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

hive ddl commands

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!!

Exit mobile version