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