Unit 4: Introduction to Apache Hive

I. Orientation — Data Warehousing on Hadoop

Apache Hive is a distributed data-warehouse system developed initially at Facebook (2007–2008) and later established as an Apache project. It provides a SQL-like language, HiveQL, for reading, transforming, and summarizing large datasets stored in distributed systems such as HDFS or compatible object stores.

• Governing principle: Hive applies a schema when data is read rather than requiring a rigid schema when data is written; this is commonly called schema-on-read.
• Primary purpose: It supports batch analytics, data warehousing, ETL, reporting, and large-scale aggregation rather than low-latency transaction processing.
• Execution model: Hive compiles HiveQL into distributed execution plans that can run through engines such as Apache Tez or MapReduce.
• Metadata management: Table names, columns, partitions, storage formats, and locations are recorded in the Hive Metastore.
• Storage independence: Data may be represented as text, ORC, Parquet, Avro, or other formats through SerDe and input/output format components.
• Table convention:
  • Managed table: Hive manages the table’s data and metadata according to the configured warehouse behavior.
  • External table: Hive manages metadata while the data remains at an explicitly specified location.
• Scalability assumption: Queries generally process many records in parallel, so startup latency is accepted in exchange for high throughput.
• Core interfaces: Users commonly submit HiveQL through Beeline connected to HiveServer2, while applications use JDBC or ODBC.

II. Hive Installation — Components and Deployment

A. Hive installation

Hive installation establishes the software, Hadoop connectivity, metadata database, warehouse directories, and services required to execute HiveQL.

• Prerequisites: A typical installation requires a compatible Java runtime, Hadoop configuration, and access to HDFS or another supported storage system.
  • JAVA_HOME identifies the Java installation.
  • HADOOP_HOME or the Hadoop commands available through PATH provide Hadoop libraries and utilities.
• Package setup: Download a compatible Apache Hive binary release, extract it, and define its location.

export HIVE_HOME=/opt/apache-hive
export PATH=$PATH:$HIVE_HOME/bin

• Configuration files: Hive reads settings from $HIVE_HOME/conf, especially hive-site.xml.
  • javax.jdo.option.ConnectionURL identifies the Metastore database.
  • hive.metastore.warehouse.dir identifies the default warehouse location.
  • Hadoop files such as core-site.xml and hdfs-site.xml provide filesystem connectivity.
• Metastore database: The Metastore stores metadata through a relational database.
  • Embedded Derby is convenient for local experimentation but is unsuitable for concurrent production use.
  • PostgreSQL, MySQL, or another supported database is normally used for shared deployments.
• Schema initialization: schematool creates the Metastore schema for the selected database.

schematool -dbType derby -initSchema

• HDFS preparation: Warehouse and temporary directories must exist and have suitable permissions.

hdfs dfs -mkdir -p /user/hive/warehouse
hdfs dfs -mkdir -p /tmp

• Service startup: Production-style access normally uses a Metastore service and HiveServer2.

hive --service metastore &
hiveserver2 &
beeline -u jdbc:hive2://localhost:10000/default

• Verification: A simple database and table operation confirms that parsing, metadata access, and storage are working.

CREATE DATABASE training;
SHOW DATABASES;

• Deployment limitation: Hive, Hadoop, Java, execution-engine, and storage-format versions must be mutually compatible; authentication and authorization also require cluster-specific configuration.

III. Hive Data Model — Primitive and Complex Values

A. Hive data types

Hive data types define how column values are interpreted, stored, compared, and processed by HiveQL expressions.

• Integer types: TINYINT, SMALLINT, INT, and BIGINT represent increasingly large signed integers; BIGINT is commonly used for identifiers and counts.
• Floating-point types: FLOAT and DOUBLE represent approximate numeric values and may introduce binary rounding.
• Exact decimal type: DECIMAL(p,s) stores fixed-precision values, where p is total precision and s is digits after the decimal point; DECIMAL(10,2) can represent values such as 12500.75.
• Character types:
  • STRING stores variable-length text.
  • VARCHAR(n) limits text to at most n characters.
  • CHAR(n) represents fixed-length character data with padding semantics.
• Binary and logical types: BINARY stores byte sequences, while BOOLEAN stores TRUE or FALSE.
• Temporal types: DATE represents a calendar date, TIMESTAMP represents date-and-time information, and interval types represent durations.
• Complex types:
  • ARRAY<T> stores an ordered collection of values of type T.
  • MAP<K,V> stores key-value pairs.
  • STRUCT<f1:T1,...> stores named fields.
  • UNIONTYPE<T1,T2,...> stores one value selected from several declared types.
• Concrete definition: The following table combines primitive and nested data.

CREATE TABLE customers (
    customer_id BIGINT,
    name STRING,
    balance DECIMAL(12,2),
    active BOOLEAN,
    phones ARRAY<STRING>,
    address STRUCT<city:STRING,pincode:INT>
);

• Value access: Array indexes, map keys, and structure fields are accessed as phones[0], attributes['tier'], and address.city.
• Null semantics: NULL means missing or unknown; comparisons such as col = NULL are incorrect, so HiveQL uses col IS NULL.
• Conversion rule: CAST(value AS type) performs explicit conversion, as in CAST('42' AS INT); invalid conversions commonly produce NULL.

IV. Physical Data Organization — Hash-Based Distribution

A. Hive bucketing

Hive bucketing divides table data into a fixed number of physical files by applying a hash function to one or more bucket columns.

• Definition: For N buckets, Hive derives a bucket number from the hash of the bucket key, conceptually mapping each row to a value from 0 through N−1.
• Declaration: Bucketing is specified with CLUSTERED BY and INTO ... BUCKETS.

CREATE TABLE sales_bucketed (
    sale_id BIGINT,
    customer_id BIGINT,
    amount DECIMAL(10,2)
)
CLUSTERED BY (customer_id) INTO 8 BUCKETS
STORED AS ORC;

• Population: Data should be inserted through Hive so that rows are written to the correct bucket files.

INSERT INTO sales_bucketed
SELECT sale_id, customer_id, amount
FROM sales_stage;

• Performance use: Compatible bucketed tables can reduce work for joins, grouping, and sampling because matching hash keys may be colocated.
• Sampling: TABLESAMPLE can select bucket-based subsets when the sampling expression aligns with the table’s bucket definition.

SELECT *
FROM sales_bucketed
TABLESAMPLE(BUCKET 1 OUT OF 8 ON customer_id);

• Bucketing versus sorting: SORTED BY may arrange rows inside each bucket, but it does not replace the hash-based bucket assignment.
• Limitations: Bucketing does not eliminate data skew; a frequently occurring key can overload one bucket. Benefits also depend on accurate metadata, compatible bucket counts, and correctly bucketed files.

V. Directory-Level Data Organization — Partition Pruning

A. Hive partitioning

Hive partitioning divides a table into directory-based subsets identified by one or more partition columns.

• Definition: Each distinct partition value usually corresponds to a path such as year=2025/month=3, allowing Hive to avoid scanning unrelated directories.
• Declaration: Partition columns are listed separately from ordinary table columns.

CREATE TABLE web_logs (
    user_id BIGINT,
    url STRING,
    status INT
)
PARTITIONED BY (log_date DATE)
STORED AS PARQUET;

• Static partitioning: The partition value is explicitly supplied in the insertion statement.

INSERT INTO web_logs PARTITION (log_date='2025-03-01')
SELECT user_id, url, status
FROM staged_logs;

• Dynamic partitioning: Partition values come from query output; the dynamic partition columns normally appear last in the selected column list.

INSERT INTO web_logs PARTITION (log_date)
SELECT user_id, url, status, event_date
FROM staged_logs;

• Partition pruning: A predicate such as WHERE log_date='2025-03-01' allows Hive to scan only the matching partition when pruning is available.
• Metadata operations: Partitions can be added, inspected, or removed with ALTER TABLE, SHOW PARTITIONS, and DROP PARTITION.
• Bucketing contrast:
  1. Partitioning: Creates directory-level divisions and works best for low- or medium-cardinality fields such as date or region.
  2. Bucketing: Creates a fixed number of hash-based files and is suitable for high-cardinality keys such as customer_id.
• Limitations: Excessively fine partitioning creates many small directories and metadata entries; partitioning directly by a nearly unique identifier is usually inefficient.

VI. Hive Query Language — Definition, Manipulation, and Retrieval

A. HiveQL operations

HiveQL operations create metadata structures, load or transform data, retrieve records, and control selected database objects.

• Database operations: CREATE DATABASE, USE, SHOW DATABASES, and DROP DATABASE organize schemas.
• Table DDL: CREATE, ALTER, TRUNCATE, and DROP define or modify tables and partitions.
• Data loading: LOAD DATA moves or copies files according to the filesystem context; it does not parse and transform every row like an INSERT ... SELECT.
• Data insertion:
  • INSERT INTO appends data.
  • INSERT OVERWRITE replaces data in the target table or partition.
• Retrieval: SELECT, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT perform filtering, aggregation, global sorting, and result restriction.
• Join operations: Hive supports inner, left outer, right outer, full outer, cross, and other join forms depending on version and configuration.
• Worked query: This operation filters completed sales, groups them by region, and retains totals above 100000.

SELECT region, SUM(amount) AS total_amount
FROM sales
WHERE status = 'COMPLETE'
GROUP BY region
HAVING SUM(amount) > 100000
ORDER BY total_amount DESC;

• Transactional operations: UPDATE, DELETE, and MERGE require transactional table support and appropriate table properties, formats, and configuration.
• Execution insight: EXPLAIN displays the query plan, helping identify scans, joins, aggregations, and partition pruning.

VII. Hive Expression System — Operators and Predicates

A. Hive operators

Hive operators combine, compare, transform, and test values inside expressions and query conditions.

• Arithmetic operators: +, -, *, /, %, and unary signs perform numeric calculations; price * quantity computes a row-level amount.
• Relational operators: =, <>, !=, <, <=, >, and >= compare compatible values and return Boolean results.
• Logical operators: AND, OR, and NOT combine predicates; parentheses should make precedence explicit.

WHERE status = 'PAID'
  AND (region = 'East' OR region = 'West')

• Null operators: IS NULL and IS NOT NULL test missing values; ordinary comparisons with NULL evaluate as unknown rather than true.
• Range and membership operators: BETWEEN tests an inclusive range, while IN checks membership in a list or subquery result.
• Pattern operators:
  • LIKE uses % for any sequence and _ for one character.
  • RLIKE or REGEXP performs regular-expression matching.
• Collection operators: array_col[index], map_col[key], and struct_col.field retrieve elements from complex values.
• String concatenation: Hive commonly uses the concat() function rather than assuming SQL’s || behavior.
• Precedence: Arithmetic is generally evaluated before comparison, and comparison before logical combination; explicit parentheses prevent ambiguous expressions.
• Three-valued logic: Conditions may evaluate to TRUE, FALSE, or NULL; a WHERE clause retains only rows whose condition evaluates to TRUE.