Introduction to Hive

Hive is an open-source data warehouse system built on top of Hadoop. It enables the mapping of structured and semi-structured data files stored in HDFS in to database tables, providing a SQL-like language called HiveQL (HQL) for querying and analyzing large datasets. Hive's core functionality is to translate HiveQL queries into MapReduce programs that execute on the Hadoop cluster.

Hive Archietcture and Components

Hive's architecture consists of several key components:

• User Interface: Provides an interface for writing SQL queries.
• Metadata Storage: Stores information about HDFS data files and their mapping to tables, including table names, column definitions, delimiters, and table properties (like whether it's an external table). This metadata is typically stored in relational databases.
• Driver: Includes components like the parser, compiler, optimizer, and executor that transform HQL queries.
• Execution Engine: Hive doesn't process data files directly but uses an engine for processing. Hive 3.0 supports MapReduce, Tez, and Spark as execution engines.

Hive vs. Traditional Databases

Hive is not a database but a data warehouse system. While it shares some similarities with relational databases in its data model (tables, columns, etc.), it has distinct characteristics:

• Hive uses HDFS for storage and MapReduce for processing.
• Hive data can be organized at three granularity levels:
  • Table
  • Partition
  • Bucket

Hive Data Organization

In Hive, data is organized in a hierarchical structure that mirrors traditional databases:

• Databases: Default database is named "default". Each database has its own directory in HDFS at the path specified by hive.metastore.warehouse.dir (default: /user/hive/warehouse).
• Tables: Hive tables correspond to directories in HDFS. Table data is stored at ${hive.metastore.warehouse.dir}/database_name.db/table_name.

Table Types

1. Internal Tables

Internal tables are managed by Hive. When you drop an internal table, both the metadata and the data are deleted. The default location is determined by hive.metastore.warehouse.dir.

CREATE TABLE internal_table (
  id INT,
  name STRING
);

2. External Tables

External tables allow data to exist outside of Hive's managed directory. When you drop an external table, only the metadata is deleted; the data remains intact.

CREATE EXTERNAL TABLE external_table (
  id INT,
  name STRING
) LOCATION '/path/to/data';

Partitioning

Partitioning is an optimization technique that divides a table into subdirectories based on partition column values (like dates). This allows faster queries on specific partitions.

CREATE TABLE sales_data (
  order_id INT,
  customer_id INT,
  amount DECIMAL(10,2)
)
PARTITIONED BY (date STRING, region STRING);

Bucketing

Bucketing is another optimization technique that divides table data into a fixed number of files based on hash values of specified columns. This improves join operations and sampling queries.

SET hive.enforce.bucketing=true;

CREATE TABLE customer_data (
  customer_id INT,
  name STRING,
  email STRING
)
CLUSTERED BY (customer_id) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

Hive Metadata

Metadata in Hive describes data attributes such as storage location, history, and schema information. Hive metadata is stored in relational databases like Derby (default) or MySQL.

Hive Operations

Data Loading

There are two primary methods for loading data into Hive tables:

1. LOAD DATA: Directly loads data from HDFS or local filesystem into a table. ``` LOAD DATA [LOCAL] INPATH '/path/to/data' [OVERWRITE] INTO TABLE table_name;

2. INSERT INTO/overwrite: Loads data from one table to another using SQL queries. ``` INSERT [OVERWRITE] INTO TABLE target_table SELECT * FROM source_table;

Data Exporting

Data can be exported from Hive tables to local filesystem or HDFS:

INSERT OVERWRITE [LOCAL] DIRECTORY '/export/path'
SELECT * FROM table_name;

Table Modification Operations

Hive provides several commands for modifying tables:

• Renaming tables:``` ALTER TABLE old_name RENAME TO new_name;

• Changing table properties:``` ALTER TABLE table_name SET TBLPROPERTIES ('property_name'='value');

• Adding partitions:``` ALTER TABLE table_name ADD PARTITION (date='2023-01-01', region='east');