Apache Hive Overview

Apache Hive is a SQL abstraction tool that translates standard SQL queries into distributed compute jobs like MapReduce, simplifying big data development for engineers. A basic example query:

SELECT COUNT(*) FROM student_table;

Simulating Hive Core Functionality

Hive maps data stored on HDFS into structured tabular data. A Hive table has two core components:

1. Table Metadata: Describes core table properties, including table name, column names, column data types, and column constraints
2. Row Data: The actual record content stored in the table

For example, a sample student table tb_stu:

Unlike traditional relational databases such as MySQL or Oracle that manage both metadata and row data internally, Hive splits these responsibilities:

• Table metadata is stored and managed by the Hive Metastore service
• Actual row data is stored on HDFS

Hive Basic Architecture

Client Access Modes

Hive supports three primary client access patterns:

1. Command Line Client: Direct hive shell access on the local server
2. JDBC/ODBC Compliant Tools: Third-party applications like DataGrip and Beeline that follow standard database connection protocols
3. Web UI: A rarely used browser-based interface

Hive Metastore

Central service for storing, querying, and managing all Hive table metadata stored in the backend database.

Hive Driver

Handles core SQL execution workflows:

• Parses incoming SQL queries
• Creates and optimizes execution plans
• Submits compute jobs to the underlying execution engine
• Fine-tunes execution performance for efficiency

Compute Execusion Layer

• Requests compute resources from YARN cluster manager
• Fetches source data from HDFS for processing

Hive Driver supports multiple production-grade execution engines, including Apache MapReduce, Apache Tez, and Apache Spark.

Hive Deployment Modes

Prerequisites: A fully operational Hadoop cluster and accessible MySQL database service.

There are three standard Hive deployment configurations:

Embedded Mode (Testing Only)

1. Stores table metadata in the embedded Apache Derby database
2. No separate Metastore service configuration or startup required
3. Ideal only for quick local testing and initial product familiarization.

Local Mode

1. Uses an external relational database (most commonly MySQL) for persistent metadata storage
2. No dedicated Metastore service setup needed
3. Key Drawback: Multiple concurrent client connections will spawn multiple Metastore instances, leading to resource contention and operational conflicts.

Remote Mode

1. Uses an external relational database (MySQL) for metadata storage
2. Metastore service is configured separately and runs as a single global singleton instance
3. All client connections route through this single Metastore service, eliminating resource conflicts across multiple clients.

A comparative breakdown of the three deployment modes:

In standard production deployments, Hive runs as a single-node service but leverages HDFS for distributed storage and YARN/execution engines for distributed compute. Only the primary cluster node (e.g., node1) can run core Hive services, while other cluster nodes can connect remotely to use Hive clients.

Hive Client Usage

Local Client Access

To run Hive commands directly on the server hosting the Metastore service:

1. Start the Hive Metastore service in the background:

   nohup hive --service metastore &
   

2. Verify the service is active using the jps command, which will display the running Java process.
3. Launch the local unauthenticated Hive client:

   hive
   

Remote Client Access

To connect to Hive from a separate cluster node:

1. Start the Hive Metastore service first on the primary node
2. Start the HiveServer2 service, which exposes a JDBC/ODBC endpoint via Apache Thrift:

   nohup hive --service hiveserver2 &
   

3. Confirm HiveServer2 is running by checking port 10000:

   lsof -i:10000
   

4. Launch the Beeline JDBC client to establish a remote connection:

   beeline