How to Use Hive in Hadoop: Simple Guide with Examples

To use Hive in Hadoop, first install Hive and configure it to connect with your Hadoop cluster. Then, use HiveQL commands to create tables, load data, and run SQL-like queries on data stored in HDFS.

Syntax

Hive uses HiveQL, a SQL-like language, to interact with data stored in Hadoop's HDFS. The basic syntax includes commands to create databases, tables, load data, and query data.

• CREATE TABLE: Defines a new table.
• LOAD DATA: Loads data into the table from HDFS.
• SELECT: Queries data from tables.

sql

CREATE TABLE table_name (
  column1 STRING,
  column2 INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

LOAD DATA INPATH '/path/in/hdfs/datafile.csv' INTO TABLE table_name;

SELECT * FROM table_name LIMIT 10;

Example

This example shows how to create a Hive table, load data from HDFS, and query the data.

sql

CREATE TABLE employees (
  name STRING,
  age INT,
  department STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

LOAD DATA INPATH '/user/hadoop/employees.csv' INTO TABLE employees;

SELECT name, department FROM employees WHERE age > 30;

Output

name	department
John Doe	Sales
Jane Smith	Marketing

Common Pitfalls

Common mistakes when using Hive in Hadoop include:

• Not setting the correct FIELDS TERMINATED BY character matching the data file.
• Loading data from a local path instead of HDFS path.
• Forgetting to start the Hive service before running queries.
• Using incompatible data formats without proper SerDe configuration.

sql

/* Wrong: Loading data from local file path */
LOAD DATA LOCAL INPATH '/home/user/employees.csv' INTO TABLE employees;

/* Right: Loading data from HDFS path */
LOAD DATA INPATH '/user/hadoop/employees.csv' INTO TABLE employees;

Quick Reference

Command	Description
CREATE TABLE	Create a new table in Hive
LOAD DATA INPATH	Load data from HDFS into a Hive table
SELECT	Query data from Hive tables
SHOW TABLES	List all tables in the current database
DESCRIBE table_name	Show table schema

Key Takeaways

Hive lets you run SQL-like queries on Hadoop data using HiveQL.

Always load data from HDFS paths, not local file system paths.

Define table schema carefully matching your data format.

Start Hive service before running queries to avoid connection errors.

Use Hive commands like CREATE TABLE, LOAD DATA, and SELECT to manage and query data.