Commonly used Hadoop Hive Commands and Examples

  • Post author:
  • Post last modified:October 8, 2020
  • Post category:BigData
  • Reading time:4 mins read

If you are already familiar with the SQL then Hive command syntax are easy to understand. In this article, we will discuss on the commonly used Hadoop Hive commands.

Read:

Commonly used Hadoop Hive commands

Below are the most commonly used Hadoop Hive commands:

Hive Create Database

A database is a collection of namespace in Hive. Below is the syntax to create database in Hive:

CREATE DATABASE [IF NOT EXISTS] db_name;

Example:

hive> CREATE DATABASE IF NOT EXISTS TRAINING;
OK
Time taken: 9.253 seconds
hive>

Hive Show Database

Display the databases present in Hive. Below is the syntax to see all available databases in Hive:

SHOW DATABASES;

Example:

hive> SHOW DATABASES;
OK
default
training
Time taken: 2.346 seconds, Fetched: 2 row(s)
hive>

Hive Drop database

It drops all tables and deletes the database in Hive. Below is the syntax to drop database in Hive:

DROP DATABASE IF EXISTS db_name;

Example:

hive> DROP DATABASE IF EXISTS TRAINING;
OK
Time taken: 1.165 seconds
hive>

Hive Create Table

This command creates a table in hive. Below is the syntax to create tables in Hive:

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

Example:

hive> CREATE TABLE IF NOT EXISTS test(col1 char(10),col2 char(20));
OK
Time taken: 1.1 seconds
hive>

Hive Drop Table

This removes the table data and their metadata. Below is the syntax to drop tables in Hive:

DROP TABLE [IF EXISTS] table_name;

Example:

hive> DROP TABLE test1;
OK
Time taken: 1.165 seconds
hive>

Hive Alter Table

You can alter table to modify attributes of Hive table.

ALTER TABLE table_name ADD COLUMNS (column1, column2) ;
ALTER TABLE table_name RENAME TO table_new_name;

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>

Hive Select Data from Table

This command is used to select the columns from a table. Below is the 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];

Example:

hive> SELECT * FROM test;
OK
1 abc 100
2 bcd 102
3 cde 103
4 def 104
Time taken: 2.036 seconds, Fetched: 4 row(s)
hive>

Hive Load Data

This command loads the data into the Hive table. Below is the syntax to load data to hive table from external file.

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

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>

Hive Describe Formatted

This command provide details view about table schema. You will get detail information such as column names, data types, constraint and its name if defined, etc.

Following is the syntax

describe formatted table_name;

For example,

describe formatted inventory;

Hope this helps 🙂

Tags: ,