Explain any three Hive QL DDL command with its syntax and example ?

In Previous Years Questions

HiveQL DDL commands are used to create, modify, and delete databases, tables, and other objects within the Hive metastore.

1. CREATE DATABASE

• Syntax: CREATE DATABASE database_name;
• Example: CREATE DATABASE my_database; This command creates a new database named my_database in the Hive metastore.

2. CREATE TABLE

• Syntax:

CREATE TABLE table_name (
  column_1_name data_type [COMMENT 'comment'],
  column_2_name data_type [COMMENT 'comment'],
  ...
);

• Example

CREATE TABLE employee_data (
  employee_id INT COMMENT 'Unique ID of the employee',
  name STRING COMMENT 'Employee name',
  department STRING COMMENT 'Department of the employee',
  salary DOUBLE COMMENT 'Monthly salary of the employee'
);

This command creates a new table named employee_data with four columns: employee_id, name, department, and salary. Each column has a specific data type and an optional comment describing its purpose.

3. ALTER TABLE

• Syntax

ALTER TABLE table_name 
[ADD|DROP] COLUMN column_name data_type [COMMENT 'comment']
[RENAME TO new_table_name];

• Example

ALTER TABLE employee_data ADD COLUMN bonus DOUBLE COMMENT 'Monthly bonus of the employee';
ALTER TABLE employee_data RENAME TO employee_information;

The first command adds a new column named bonus to the employee_data table. The second command renames the employee_data table to employee_information.