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

What is Hive DDL commands

Hadoop Quiz

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>
Let’s read about Apache Hive Architecture & Components in detail

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>
Read about Apache Hive Data Types in detail

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:

Join DataFlair on Telegram
  • 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');

Best books to learn Apache Hive in detail.
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>

Do you the difference between HBase and 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>

Configure Hive Metastore to MySQL in detail.

If these professionals can make a switch to Big Data, so can you:
Rahul Doddamani Story - DataFlair
Rahul Doddamani
Java → Big Data Consultant, JDA
Follow on
Mritunjay Singh Success Story - DataFlair
Mritunjay Singh
PeopleSoft → Big Data Architect, Hexaware
Follow on
Rahul Doddamani Success Story - DataFlair
Rahul Doddamani
Big Data Consultant, JDA
Follow on
I got placed, scored 100% hike, and transformed my career with DataFlair
Enroll now
Deepika Khadri Success Story - DataFlair
Deepika Khadri
SQL → Big Data Engineer, IBM
Follow on
DataFlair Web Services
You could be next!
Enroll now

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.
See Also- Hive Partitions-Types of Hive Partitioning with Examples
For reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.