Apache Hive DDL Operations

Apache Hive DDL stands for (Data Definition Language) which is used to define or change the structure of Databases, Tables, indexes, and so on. The most commonly used DDL are CREATE, DROP, ALTER, SHOW, and so on.

The following is the list of DDL statements that are supported in Apache Hive.

  1. CREATE
  2. DROP
  3. TRUNCATE
  4. ALTER
  5. SHOW
  6. DESCRIBE
  7. USE
Commands Commands Use With
CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTION, INDEX
DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX
TRUNCATE TABLE
ALTER DATABASE/SCHEMA, TABLE, VIEW
SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, VIEWS, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE
DESCRIBE DATABASE/SCHEMA, TABLE_NAME, VIEW_NAME, MATERIALIZED_VIEW_NAME
USE DATABASE

Let us see each command in detail.

DDL Commands on Databases


1. Create Database

The Create Database command is useful in creating a database in Apache Hive. We can use the DATABASE as well as SCHEMA.

Create Database Syntax:

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

Create Database Statement:

create database if not exists cloudduggudb
comment "Cloudduggu Database"
location '/hive/warehouse/' with
DBPROPERTIES ('createdby'='Cloudduggu','createdfor'='Cloudduggu');

Command Output:

hive dbcreation


2. Drop Database

The Drop Database command is used to drop a database in Hive. The default mode is RESTRICT and the user will not able to drop a database unless the database is non-empty. If we want to delete a database then we will have to change the mode to CASCADE.

Drop Database Syntax:

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

Drop Database Statement:

drop database if exists cloudduggudb CASCADE;

Command Output:

hive dbcreation


3. Alter Database

The Alter Database command is useful in altering the structure of the database.

Alter Database Syntax:

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

Alter Database Statement:

alter database cloudduggudb  set OWNER ROLE admin;

Command Output:

hive alter


4. Use Database

The USE Database command is used in those case where we want to set a database and wants to operate on that particular database objects.

Use Database Syntax:

USE database_name;

Use Database Statement:

USE cloudduggudb;

Command Output:

hive usedb


5. Show Database

The Show Database command is used to present the list of databases.

Show Database Syntax:

show databases;

Command Output:

hive showdb


DDL Commands on Tables


1. Create Table

The Create table command is used to create a table in the present database. In the below example we are creating a table named “serde_example” in the cloudduggudb database. The table storage location in HDFS would be “/hive/warehouse/cloudduggudb.db/”.

Create Table 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];

Create Table Statement:

CREATE TABLE serde_example(
  hosts STRING,
  identitys STRING,
  users STRING,
  times STRING,
  request STRING,
  status STRING,
  sizes STRING,
  referer STRING,
  agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\"[^\"]*\") ([^ \"]*|\"[^\"]*\"))?",
  "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"
)
STORED AS TEXTFILE
LOCATION '/hive/warehouse/cloudduggudb.db/serde_example';

Command Output:

hive createtable


2. Create Table from Existing Table

In Apache Hive a new table can be created based on an existing table, in this process, the only table structure is created, table content is not copied.

Create Table Syntax:

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
Like [db_name].existing_table
[LOCATION hdfs_path]

Create Table Statement:

create table if not exists Employee.data
like cloudduggudb.serde_example
LOCATION '/hive/warehouse/';

Command Output:

hive_create_table_from_other_table


3. Truncate Table

The Truncate Table command is used to delete all rows from the table and no structure of the table. once the data is deleted it is pushed to the trash file system if that is enabled.

Truncate Table Syntax:

TRUNCATE [TABLE] table_name [PARTITION partition_spec];partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value, …)

Truncate Table Statement:

truncate table Employee.data;

Command Output:

truncate_table


4. Alter Table

The Alter command is used to change the structure of the table.

Rename Table Syntax:

ALTER TABLE table_name RENAME TO new_table_name;

Rename Table Statement:

ALTER TABLE Employee.data RENAME TO Employee.Datanew;

Command Output:

rename_table

Add Column Syntax:

ALTER TABLE table_name
[PARTITION partition_spec]
ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
[CASCADE|RESTRICT]

Add Column Statement:

ALTER TABLE cloudduggudb.emp ADD COLUMNS (city STRING);

Command Output:

add_column

Rename Column Syntax:

ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
  [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];

Rename Column Statement:

ALTER TABLE cloudduggudb.emp CHANGE empid employeeid int;

Command Output:

rename_column

Alter Table Properties Syntax:

ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);

Alter Table Properties Statement:

ALTER TABLE cloudduggudb.emp SET TBLPROPERTIES ('creator' = 'cloudduggu');

Command Output:

alter_creator

We can perform other operations using the Alter command.

Name Commands Description
Rename Table ALTER TABLE table_name1 RENAME TO new_table_name; We can change the name of a table to a different name.
Alter Table Properties ALTER TABLE table_name2 SET TBLPROPERTIES table_properties; table_properties: : (property_name = property_value, property_name = property_value, ... )" We can use this statement to add your own metadata to the tables.
Alter Table Comment ALTER TABLE table_name3 SET TBLPROPERTIES ('comment' = new_comment); We can change the comment for your table.
Add SerDe Properties ALTER TABLE table_name4 [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties]; ALTER TABLE table_name5 [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties; serde_properties: : (property_name = property_value, property_name = property_value, ... )" We can change a table's SerDe or add user-defined metadata to the table's SerDe object.
Alter Table Storage Properties ALTER TABLE table_name6 CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS;" We can change the table's physical storage properties.
Alter Table Constraints ALTER TABLE table_name7 ADD CONSTRAINT constraint_name PRIMARY KEY (column, ...) DISABLE NOVALIDATE; We can add or remove the table’s constraints using the Alter command.
Alter Partition(Add Partitions) ALTER TABLE page_view ADD PARTITION (dt='2010-09-08', country='ind') location '/path/to/user/part100908' PARTITION (dt='2010-08-09', country='ind') location '/path/to/user/part100809'; We can use ALTER TABLE ADD PARTITION to add partitions to a table.
Alter Partition(Rename Partitions) ALTER TABLE table_name8 PARTITION partition_spec RENAME TO PARTITION partition_spec; We can change the value of a partition column.
Alter Column ALTER TABLE table_name9 [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT]; We can change a column's name, data type, comment, or position, or an arbitrary combination of them.

5. Describe Table

The Describe command shows the detailed structure of a table like its columns, data type, and so on.

Describe Table Syntax:

DESCRIBE [EXTENDED|FORMATTED]table_name[.col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

Describe Table Statement:

describe  cloudduggudb.emp;

Command Output:

describe_table


6. Describe EXTENDED Table

The Describe usage with EXTENDED command shows the complete details of the table like table type, last access time, create time, last modification time, and so on.

Describe EXTENDED Table Syntax:

DESCRIBE [EXTENDED|FORMATTED]table_name[.col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

Describe EXTENDED Table Statement:

describe EXTENDED cloudduggudb.emp.

Command Output:

describe_table_extend


7. Describe FORMATTED Table

The Describe usage with FORMATTED command also shows similar details of the table like table type, last access time, create time, last modification time, and so on.

Describe FORMATTED Table Syntax:

DESCRIBE [EXTENDED|FORMATTED]table_name[.col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

Describe FORMATTED Table Statement:

describe FORMATTED cloudduggudb.emp.

Command Output:

describe_table_formatted


8. Drop-Table

The Drop-table command is used to drop the metadata and content of a table. When we perform the drop table command data is usually moved to the Trash/Current directory if Trash is configured.

Drop-Table Syntax:

DROP TABLE [IF EXISTS] [db_name.]table_name [PURGE];

Drop-Table Statement:

drop table if exists cloudduggudb.emp purge;

Command Output:

drop_table