Basic Commands of Hive

Apache Hive is a data warehouse system that runs SQL like queries called HQL (Hive Query Language). It was developed by Facebook in 2007. It is now an open-source Apache Product. It has a high-level abstraction layer on top of MapReduce and Apache Spark. 

Executing a query in Hive and Impala

Parse HQL.

• Make Optimizations.
• Plan execution.
• Submit job(s) to the cluster.
• Monitor progress.
• Process data using MapReduce or Apache Spark.
• Store the data in HDFS.

Hive Commands:

Data Definition Language (DDL):

DDL statements are used to create and modify the table.

Examples - CREATE, DROP, ALTER, SHOW, TRUNCATE, DESCRIBE Statements.

• To create a table enter the command 'create database<database name>'.

• To list out the databases in Hive enter the command 'show databases'.
• To use a database enter the command 'USE <database name>'.

• To find the information about the schema of the table enter the command 'DESCRIBE <table name>'.

Data Manipulation Language (DML):

DML statements are used to store, delete, insert data in the database.

Examples - LOAD, INSERT Statements.

• The LOAD command is used to load the data into the hive table. If in the command local is specified then in the command we will give the local file system path else if local is not specified then we have to give the HDFS path on the file. 

        LOAD DATA <LOCAL> INPATH <file path> into table [tablename]

• 'Create External' Table - The create external command is used to create a table and provides the location where it will be created. An external points to any other location in HDFS for its storage rather than its default location.

Insert Command:

The insert command is used to load the data into the hive table.

• INSERT OVERWRITE is used to overwrite the existing data into the table.
• INSERT INTO is used to append the data into the table.