Hive DDL Commands

Hive DDL Commands : Types of DDL Hive Commands

1. Objective

In this Hive tutorial, we will learn about Hive DDL Commands. However, there are many types of Hive DDL Commands. So, in this article, we will learn about each Hive commands individually. Also, we will cover syntax of each DDL Commands, as well as examples of Hive DDL Commands to understand well.

What is Hive DDL commands

2. Introduction to Hive DDL Commands

There are several types of Hive DDL commands, we commonly use. such as:

1. Create Database Statement
2. Hive Show Database
3. Drop database
4. Creating Hive Tables
5. Browse the table
6. Altering and Dropping Tables
7. Hive Select Data from Table
8. Hive Load Data

Let’s discuss each Hive DDL commands in detail:

a. Create Database Statement

Basically, in Apache Hive, the database is a namespace or a collection of tables.

• Syntax-

hive> CREATE SCHEMA userdb;  

hive> SHOW DATABASES;

Or,

CREATE DATABASE [IF NOT EXISTS] db_name;

• For Example-

hive> CREATE DATABASE IF NOT EXISTS TRAINING;

OK

Time taken: 9.253 seconds

hive>

b. Hive Show Database

However, with this Hive DDL commands, we generally display the databases present in Hive. Moreover, to see all available databases in Hive below is the syntax:

• Syntax-

hive> DROP DATABASE IF EXISTS userdb;  

Or,

SHOW DATABASES;

• For Example-

hive> SHOW DATABASES;

OK

default

training

Time taken: 2.346 seconds, Fetched: 2 row(s)

hive>

c. Drop database

The Syntax & Example of Drop database – Hive DDL commands are:

• Syntax-

hive> DROP DATABASE IF EXISTS userdb;  

Or,

DROP DATABASE IF EXISTS db_name;

• For Example-

hive> DROP DATABASE IF EXISTS TRAINING;

OK

Time taken: 1.165 seconds

hive>

d. Creating Hive Tables

However, with two columns “Create a table” called Sonoo. Although, the first being an integer and the other a string.

hive> CREATE TABLE Sonoo(foo INT, bar STRING);  

To be more specific, create a table is what we call  HIVE_TABLE with two columns and a partition column called ds. Moreover, the partition column is a virtual column. However,  it is not part of the data itself but is derived from the partition that a particular dataset is loaded into. In addition, tables are assumed to be of text input format and the delimiters are assumed to be ^A(ctrl-a), by default.

hive> CREATE TABLE HIVE_TABLE (foo INT, bar STRING) PARTITIONED BY (ds STRING);

Or,

CREATE [TEMPORARY ] [EXTERNAL] TABLE [IF NOT EXISTS] db_name table_name;

• For Example-

hive> CREATE TABLE IF NOT EXISTS test(col1 char(10),col2 char(20));

OK

Time taken: 1.1 seconds

hive>

e. Browse the table

The Syntax of Browse the table – Hive DDL commands are:

• Syntax-

hive>  Show tables;  

f. Altering and Dropping Tables

The Syntax of Altering and Dropping Tables – Hive DDL commands are:

• Syntax-

hive> ALTER TABLE Sonoo RENAME TO Kafka;  

hive> ALTER TABLE Kafka ADD COLUMNS (col INT);  

hive> ALTER TABLE HIVE_TABLE ADD COLUMNS (col1 INT COMMENT ‘a comment’);  

hive> ALTER TABLE HIVE_TABLE REPLACE COLUMNS (col2 INT, weight STRING, baz INT COMMENT ‘baz replaces new_col1’);  

Let’s discuss both individually:

i. Hive Drop Table

Generally, with these Hive DDL commands, we remove the table data and their metadata. Moreover, to drop tables in Hive below is the syntax:

• Syntax-

DROP TABLE [IF EXISTS] table_name;

• For Example-

hive> DROP TABLE test1;

OK

Time taken: 1.165 seconds

hive>

ii. Hive Alter Table

Basically, with these Hive DDL commands, we can alter table to modify attributes of Hive table.

• Syntax-

ALTER TABLE table_name ADD COLUMNS (column1, column2) ;

ALTER TABLE table_name RENAME TO table_new_name;

• For Example-

hive> ALTER TABLE test1 ADD COLUMNS(col3 char(10),col4 char(10));

OK

Time taken: 0.56 seconds

hive>

hive> ALTER TABLE test1 RENAME TO test2;

OK

Time taken: 0.343 seconds

hive>

g. Hive Select Data from Table

However, to select the columns from a table we use Hive select Data from Table command. Moreover,  Syntax for it is:

• Syntax-

SELECT [ALL | DISTINCT ] select_col, select_col FROM table WHERE

where_condition [GROUP BY col_list] [HAVING having_con] [ORDER BY

col_list][LIMIT number];

• For Example-

hive> SELECT * FROM test;

OK

abc 100

 bcd 102

cde 103

def 104

Time taken: 2.036 seconds, Fetched: 4 row(s)

hive>

h. Hive Load Data

To load the data into the Hive table, we use Hive Load Data command. Moreover, to load data to hive table from external file, below is the syntax.

• Syntax-

LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE table_name;

• For Example-

hive> LOAD DATA LOCAL INPATH ‘sample.txt’ INTO TABLE test2;

Loading data to table default.test2

Table default.test2 stats: [numFiles=1, numRows=0, totalSize=32, rawDataSize=0]

OK

Time taken: 2.797 seconds

hive>

3. Conclusion

As a result, we have seen all Hive DDL commands: Create Database Statement, Hive Show Database, Drop database, Creating Hive Tables, Browse the table, Altering and Dropping Tables, Hive Select Data from Table, and Hive Load Data with syntax and examples. Still, if you have doubt, feel free to ask in the comment section.