Unit 4: Introduction to Apache Hive - Subjective Questions

Apache Hive is a data warehouse system built on Hadoop that allows users to query and analyze large datasets using HiveQL.

Major components

• User Interface: Users submit HiveQL statements through tools such as Beeline, JDBC, ODBC, or other clients.
• Driver: Manages the lifecycle of a query. It creates sessions, receives HiveQL statements, and monitors query execution.
• Compiler: Parses the HiveQL query, performs semantic analysis, and creates an execution plan.
• Optimizer: Improves the execution plan using techniques such as partition pruning, predicate pushdown, and join optimization.
• Execution Engine: Executes the optimized plan using an engine such as MapReduce, Tez, or Spark.
• Metastore: Stores metadata about databases, tables, columns, partitions, storage formats, and HDFS locations. It commonly uses an external relational database.
• Hadoop Distributed File System: Stores the actual table data across a cluster.
• YARN: Allocates cluster resources and schedules jobs.

Query flow

1. A client submits a HiveQL statement.
2. The driver sends it to the compiler.
3. The compiler obtains table metadata from the metastore.
4. The optimizer produces an efficient execution plan.
5. The execution engine submits tasks to the configured processing framework.
6. Data is read from or written to HDFS, and the result is returned to the client.

Thus, Hive provides a SQL-like abstraction over distributed data while relying on Hadoop services for storage and computation.

A typical Hive installation involves the following steps:

1. Install prerequisites:

   • Install a compatible Java Development Kit.
   • Install and configure Hadoop.
   • Verify that HDFS and YARN are working.

2. Download and extract Hive:

   • Download a compatible Hive binary distribution.
   • Extract it to a directory such as /opt/hive.

3. Configure environment variables:

   • Set HIVE_HOME to the Hive installation directory.
   • Add $HIVE_HOME/bin to PATH.
   • Verify that JAVA_HOME and HADOOP_HOME are defined.

4. Configure Hive:

   • Copy the template configuration file to hive-site.xml.
   • Configure properties such as the metastore connection URL, driver, user name, password, warehouse directory, and execution engine.

5. Prepare HDFS directories:

   • Create the Hive warehouse and temporary directories.
   • Assign suitable HDFS permissions.

6. Configure the metastore:

   • Use an external relational database such as MySQL or PostgreSQL for multi-user environments.
   • Add the corresponding JDBC driver to Hive's library directory.
   • Initialize the metastore schema using schematool.

7. Start required services:

   • Start HDFS and YARN.
   • Start the Hive Metastore service.
   • Start HiveServer2 for remote client access.

8. Test the installation:

   • Connect through Beeline.
   • Create a database and a test table.
   • Run SHOW DATABASES; and a simple SELECT query.

The embedded metastore is useful only for basic testing because it generally supports a single user. An external metastore database is preferred for production deployments.

Hive data types specify the form of values that can be stored in table columns. They are broadly classified as primitive and complex data types.

Primitive data types

• Numeric types: TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, and DECIMAL
• Boolean type: BOOLEAN, which stores TRUE or FALSE
• String types: STRING, VARCHAR, and CHAR
• Binary type: BINARY, used for byte sequences
• Date and time types: DATE, TIMESTAMP, and supported interval types

Important characteristics

• INT is suitable for ordinary whole numbers, whereas BIGINT supports a larger range.
• FLOAT and DOUBLE store approximate numeric values.
• DECIMAL(p,s) stores exact fixed-point values, where is precision and is scale.
• CHAR(n) has fixed length, while VARCHAR(n) has variable length up to a specified limit.
• STRING is commonly used when no explicit maximum length is required.

Choosing an appropriate type improves storage efficiency, query correctness, and compatibility with functions and operators.

Hive supports complex data types for representing nested and semi-structured information.

1. Array

An ARRAY is an ordered collection of elements of the same type.

CREATE TABLE student_skills (
  student_id INT,
  skills ARRAY<STRING>
);

An element can be accessed by a zero-based index:

SELECT skills[0] FROM student_skills;

2. Map

A MAP stores key-value pairs. Keys must be primitive values, while values may be primitive or complex.

CREATE TABLE product_ratings (
  product_id INT,
  ratings MAP<STRING, INT>
);

A value can be accessed through its key:

SELECT ratings['quality'] FROM product_ratings;

3. Struct

A STRUCT groups named fields that may have different types.

CREATE TABLE employees (
  employee_id INT,
  address STRUCT<city:STRING, state:STRING, pin:INT>
);

A field is accessed using dot notation:

SELECT address.city FROM employees;

4. Union type

A UNIONTYPE can store one value selected from several declared types. It is less commonly used than arrays, maps, and structs.

Complex types reduce the need to flatten naturally nested data. Functions such as EXPLODE and LATERAL VIEW can convert collections into multiple rows for analysis.

Type conversion changes a value from one Hive data type to another.

Implicit conversion

Hive may automatically convert compatible values during expression evaluation. For example, an integer may be promoted to a larger numeric type when it is used with a BIGINT or DOUBLE. Implicit conversion is generally allowed when it does not cause an unsafe loss of information.

Explicit conversion

The CAST function is used when conversion must be requested directly:

SELECT CAST salary AS DOUBLE FROM employees;
SELECT CAST order_date AS STRING FROM orders;

The general form is:

CAST(expression AS data_type)

Invalid conversions

If a string does not represent a valid target value, Hive commonly returns NULL instead of producing a meaningful converted value. For example, casting 'abc' to INT results in NULL.

Handling NULL

• NULL represents a missing or unknown value.
• Arithmetic involving NULL normally produces NULL.
• IS NULL and IS NOT NULL must be used to test nullability.
• Functions such as COALESCE and NVL can replace null values.

Explicit casts should be used carefully because incompatible or malformed source data may silently become NULL.

Partitioning divides a Hive table into separate directory structures based on the values of one or more partition columns. Each distinct partition value generally corresponds to a directory in HDFS.

For example:

CREATE TABLE sales (
  sale_id BIGINT,
  product_id INT,
  amount DECIMAL(10,2)
)
PARTITIONED BY (year INT, month INT)
STORED AS ORC;

A partition may be stored in a directory similar to:

/warehouse/sales/year=2025/month=1/

Purpose of partitioning

• Reduces the quantity of data scanned by a query.
• Improves performance through partition pruning.
• Organizes data according to commonly used filtering dimensions.
• Simplifies the addition, deletion, or archival of groups of records.

For example:

SELECT SUM(amount)
FROM sales
WHERE year = 2025 AND month = 1;

Hive can read only the matching partition instead of scanning the complete table. Partition columns are virtual metadata columns and are usually derived from the directory path rather than stored in every data record.

Static and dynamic partitioning differ in how partition values are supplied during insertion.

Static partitioning

In static partitioning, the partition value is specified explicitly in the query.

INSERT INTO TABLE sales
PARTITION (year = 2025, month = 1)
SELECT sale_id, product_id, amount
FROM sales_stage
WHERE year = 2025 AND month = 1;

Characteristics:

• The target partition is known in advance.
• It provides direct control over the destination.
• Separate statements may be required for multiple partitions.
• It is suitable for loading a small number of known partitions.

Dynamic partitioning

In dynamic partitioning, Hive derives partition values from columns returned by the SELECT statement.

INSERT INTO TABLE sales
PARTITION (year, month)
SELECT sale_id, product_id, amount, year, month
FROM sales_stage;

Characteristics:

• Multiple partitions can be created in a single operation.
• Dynamic partition columns normally appear last in the SELECT list and in the same order as the partition specification.
• Appropriate configuration properties and limits must be set to allow dynamic partition creation.
• Creating too many partitions can produce excessive metadata and small files.

Comparison

Static partitioning is simpler and safer for predictable loads, while dynamic partitioning is more convenient for large input datasets containing many partition values. A statement may also combine static and dynamic partitions, such as a static year and a dynamic month.

Partition pruning is an optimization in which Hive reads only partitions that satisfy the query's partition predicates.

For example:

SELECT *
FROM web_logs
WHERE log_date = '2025-01-15';

If log_date is a partition column, Hive can avoid scanning all other partition directories.

Partition management commands

• Add a partition:

ALTER TABLE web_logs
ADD PARTITION (log_date = '2025-01-15')
LOCATION '/data/logs/2025-01-15';

• List partitions:

SHOW PARTITIONS web_logs;

• Drop a partition:

ALTER TABLE web_logs
DROP IF EXISTS PARTITION (log_date = '2025-01-15');

• Recover partitions from directory structures:

MSCK REPAIR TABLE web_logs;

Partition pruning works best when filters directly reference partition columns. Applying complicated transformations to partition columns may prevent or reduce pruning. Excessively fine-grained partitioning should also be avoided because it increases metastore overhead and may create many small directories.

Bucketing divides table data into a fixed number of files based on the hash value of one or more bucket columns.

A simplified bucket assignment rule is:

where is the total number of buckets.

Example table:

CREATE TABLE customers (
  customer_id BIGINT,
  customer_name STRING,
  city STRING
)
CLUSTERED BY (customer_id) INTO 16 BUCKETS
STORED AS ORC;

Features of bucketing

• The number of buckets is fixed in the table definition.
• Rows with the same bucket-column value are placed in the same bucket.
• Bucketing can improve joins when compatible tables are bucketed on the join key.
• It supports efficient sampling through TABLESAMPLE.
• It may improve data distribution and reduce unnecessary data processing.

Unlike partitioning, bucketing normally creates files inside a table or partition rather than a separate directory for every bucket-column value. Correctly bucketed data must be written using Hive-compatible insertion or processing so that rows are placed in the proper files.

A bucketed table is created using the CLUSTERED BY clause.

CREATE TABLE bucketed_orders (
  order_id BIGINT,
  customer_id BIGINT,
  order_total DECIMAL(12,2)
)
CLUSTERED BY (customer_id)
SORTED BY (order_id ASC)
INTO 8 BUCKETS
STORED AS ORC;

Meaning of the clauses

• CLUSTERED BY (customer_id) specifies the column used to compute the bucket number.
• INTO 8 BUCKETS specifies the fixed number of bucket files.
• SORTED BY (order_id ASC) requests ordering of records inside each bucket.
• Sorting is local to a bucket; it does not provide global ordering across the table.

Populating the table

Data should be inserted through Hive or another engine that correctly implements Hive's bucketing rules:

INSERT INTO TABLE bucketed_orders
SELECT order_id, customer_id, order_total
FROM orders_stage;

Simply copying arbitrary files into the table directory does not guarantee valid bucketing.

Benefits

• Bucket-aware joins may reduce data movement when tables use compatible bucket definitions.
• Sorted buckets can help operations that benefit from ordered records.
• Bucket sampling can read selected buckets instead of the full table.

The bucket count should be chosen according to data volume, parallelism, file sizes, and join patterns. Too many buckets may create small files, while too few may limit parallel processing.

Partitioning and bucketing both divide data, but they operate differently.

When to use partitioning

Use partitioning when queries commonly filter by predictable dimensions such as date, country, department, or event category. Avoid partitioning directly on extremely high-cardinality columns because it may create too many directories and metadata entries.

When to use bucketing

Use bucketing when a column has many distinct values, is frequently used in joins, or is suitable for deterministic sampling. Bucketing is also useful when partitioning by that column would produce an excessive number of partitions.

Combined use

A table may be partitioned by date and bucketed by customer ID. The date filter enables partition pruning, while bucketing distributes customers across files and may improve joins. The chosen bucket count and definitions should be compatible across tables if bucket-based join optimization is expected.

HiveQL Data Definition Language operations create, modify, and remove databases and table metadata.

Database operations

CREATE DATABASE IF NOT EXISTS analytics;
SHOW DATABASES;
USE analytics;
DROP DATABASE analytics CASCADE;

Table creation

CREATE TABLE employees (
  employee_id INT,
  employee_name STRING,
  salary DECIMAL(10,2)
)
STORED AS ORC;

Metadata inspection

SHOW TABLES;
DESCRIBE employees;
DESCRIBE FORMATTED employees;

Table alteration

ALTER TABLE employees RENAME TO staff;
ALTER TABLE staff ADD COLUMNS (department STRING);

Table removal

DROP TABLE IF EXISTS staff;

Truncation

TRUNCATE TABLE employees;

A managed table is controlled by Hive, and dropping it commonly removes both metadata and managed data. An external table generally allows Hive to remove metadata without deleting externally managed data. Exact behavior can depend on Hive version, table properties, and configuration.

HiveQL provides several methods for loading and manipulating data.

LOAD DATA

LOAD DATA INPATH '/input/employees.csv'
INTO TABLE employees;

This operation generally moves or copies files into the table location. It does not necessarily transform every row like a conventional database insert.

INSERT INTO

INSERT INTO TABLE high_salary_employees
SELECT * FROM employees WHERE salary > 50000;

INSERT INTO appends data to existing table data.

INSERT OVERWRITE

INSERT OVERWRITE TABLE employee_summary
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

INSERT OVERWRITE replaces the existing data in the target table or selected partition.

Multi-table insert

A single source scan can populate multiple destinations:

FROM employees
INSERT INTO TABLE permanent_staff
SELECT employee_id, employee_name WHERE employment_type = 'P'
INSERT INTO TABLE contract_staff
SELECT employee_id, employee_name WHERE employment_type = 'C';

Row-level changes

UPDATE, DELETE, and MERGE are available for supported transactional ACID tables when the required transaction settings, table format, and table properties are configured. They should not be assumed to work like ordinary row-level operations on every non-transactional Hive table.

Important HiveQL query clauses include the following:

• SELECT: Chooses columns or expressions.
• FROM: Identifies the source table.
• WHERE: Filters individual rows before grouping.
• GROUP BY: Forms groups for aggregate calculations.
• HAVING: Filters groups after aggregation.
• ORDER BY: Produces global ordering and may require a final reducer.
• SORT BY: Sorts data within each reducer without guaranteeing global order.
• DISTRIBUTE BY: Controls which reducer receives a row.
• CLUSTER BY: Combines distribution and ascending sorting on the same columns.
• LIMIT: Restricts the number of output rows.

Example:

SELECT department,
       COUNT(*) AS employee_count,
       AVG(salary) AS average_salary
FROM employees
WHERE employment_status = 'ACTIVE'
GROUP BY department
HAVING COUNT(*) >= 10
ORDER BY average_salary DESC
LIMIT 5;

Processing logic

1. Rows are read from employees.
2. WHERE removes inactive employees.
3. Remaining rows are grouped by department.
4. COUNT and AVG calculate aggregate values.
5. HAVING removes groups with fewer than ten employees.
6. ORDER BY globally sorts the result.
7. LIMIT returns only the top five rows.

Understanding the difference between WHERE and HAVING, and between ORDER BY and SORT BY, is essential for both correctness and performance.

HiveQL supports several relational join types.

Join types

• Inner join: Returns rows with matching keys in both tables.

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id;

• Left outer join: Returns every row from the left table and matching rows from the right table. Unmatched right-side values become NULL.
• Right outer join: Returns every row from the right table and matching rows from the left table.
• Full outer join: Returns matched rows and all unmatched rows from both tables.
• Left semi join: Returns rows from the left table for which a match exists in the right table. It is useful for existence tests.
• Cross join: Produces the Cartesian product and can generate a very large result.

Optimization considerations

• Map-side join: A sufficiently small table may be loaded into memory and joined with a large table without a normal reduce-side shuffle.
• Bucket-aware join: Compatible bucketed tables may reduce shuffling when joined on their bucket columns.
• Sort-merge bucket join: Can be useful when both tables are compatibly bucketed and sorted.
• Partition pruning: Partition filters should be applied so that irrelevant data is not scanned.
• Data skew: Highly frequent join keys can overload particular tasks and may require skew-handling strategies.
• Statistics: Accurate table and column statistics help the optimizer choose a better join plan.

Conditions on the nullable side of an outer join must be placed carefully. Putting such conditions in WHERE may remove NULL-extended rows and unintentionally change the effective result of the outer join.

HiveQL operators can be classified into several categories.

Arithmetic operators

Used for numeric calculations: +, -, *, /, %, and supported division operations.

SELECT price * quantity AS total FROM sales;

Relational operators

Used to compare values: =, !=, <>, <, >, <=, and >=.

SELECT * FROM employees WHERE salary >= 50000;

Logical operators

Used to combine or negate conditions: AND, OR, and NOT.

SELECT * FROM employees
WHERE department = 'IT' AND salary > 60000;

Null operators

IS NULL and IS NOT NULL test whether a value is missing.

Pattern and range operators

• LIKE performs simple wildcard matching.
• RLIKE or REGEXP performs regular-expression matching.
• BETWEEN checks a range.
• IN checks membership in a list or supported subquery.

Bitwise operators

Operators such as &, |, and ^ operate on the bits of integer values where supported.

Operator precedence affects expression evaluation. Parentheses should be used when the intended order is not obvious.

In Hive, NULL represents an unknown or missing value. Comparisons involving NULL generally produce an unknown result rather than TRUE or FALSE.

For example:

salary = NULL

is not the correct way to find missing salaries. The correct expression is:

salary IS NULL

Three-valued logic

Logical expressions may evaluate to TRUE, FALSE, or NULL.

• TRUE AND NULL produces NULL.
• FALSE AND NULL produces FALSE.
• TRUE OR NULL produces TRUE.
• FALSE OR NULL produces NULL.
• NOT NULL produces NULL.

In a WHERE clause, only rows for which the condition evaluates to TRUE are retained. Rows producing FALSE or NULL are filtered out.

Null-handling functions

• NVL(value, replacement) returns the replacement when the value is NULL.
• COALESCE(v1, v2, ...) returns the first non-null argument.

Example:

SELECT employee_id, COALESCE(bonus, 0) AS effective_bonus
FROM employees;

Correct null handling is important because expressions such as column <> value do not automatically include rows where the column is NULL.

Arithmetic operators

Arithmetic operators perform calculations on numeric values:

SELECT quantity * unit_price AS amount,
       amount_before_tax + tax AS final_amount
FROM order_items;

Common operators include +, -, *, /, and %. Division behavior depends on operand types, so explicit casting may be required when a fractional result is expected.

Pattern matching

LIKE uses simple wildcard patterns:

• % matches zero or more characters.
• _ matches exactly one character.

SELECT * FROM customers
WHERE customer_name LIKE 'A%';

RLIKE or REGEXP supports regular expressions:

SELECT * FROM customers
WHERE email RLIKE '^[A-Za-z0-9._%+-]+@example\\.com$';

Range checking

BETWEEN checks whether a value lies within an inclusive range:

SELECT * FROM products
WHERE price BETWEEN 100 AND 500;

This includes both 100 and 500.

Membership checking

IN compares a value with a list of possible values:

SELECT * FROM employees
WHERE department IN ('IT', 'HR', 'Finance');

The corresponding negative forms include NOT LIKE, NOT BETWEEN, and NOT IN. Care is required when NOT IN is used with values or subquery results containing NULL, because three-valued logic may prevent expected rows from being returned.

Set operations

UNION ALL combines the results of multiple compatible queries and retains duplicate rows:

SELECT customer_id FROM online_customers
UNION ALL
SELECT customer_id FROM store_customers;

UNION DISTINCT, or UNION in versions where it is treated as distinct, removes duplicate rows but requires additional processing. The participating queries must return compatible numbers and types of columns.

Depending on the Hive version, other set operations such as INTERSECT and EXCEPT may also be supported. Portability should be checked before relying on version-specific syntax.

Table-generating operations

Functions such as EXPLODE transform a collection into multiple rows. They are commonly combined with LATERAL VIEW.

SELECT student_id, skill
FROM student_skills
LATERAL VIEW EXPLODE(skills) skill_table AS skill;

If a student's skills array contains three elements, the query produces three output rows for that student.

A map can also be exploded into key-value rows:

SELECT product_id, rating_type, score
FROM product_ratings
LATERAL VIEW EXPLODE(ratings) rating_table AS rating_type, score;

Set operations combine complete query results vertically, whereas table-generating functions expand nested values within each source row.

Assume that sales queries frequently filter by date and join sales records with customer data using customer_id. A suitable design is to partition by date-related columns and bucket by customer.

CREATE TABLE ecommerce_sales (
  sale_id BIGINT,
  customer_id BIGINT,
  product_id BIGINT,
  quantity INT,
  unit_price DECIMAL(12,2),
  sale_timestamp TIMESTAMP
)
PARTITIONED BY (sale_year INT, sale_month INT)
CLUSTERED BY (customer_id)
SORTED BY (customer_id ASC)
INTO 32 BUCKETS
STORED AS ORC;

Design justification

• Partitioning by year and month: Most time-based reports can use partition pruning.
• Bucketing by customer ID: Customer ID may have very high cardinality and is unsuitable as a direct partition column. It is also a frequent join key.
• Thirty-two buckets: Provides parallelism while avoiding one directory for every customer. The actual number should be chosen from data volume and target file size.
• ORC format: Provides columnar storage, compression, predicate pushdown, and statistics useful to Hive optimization.
• Sorting: Sorting customer IDs within buckets may help compatible join strategies and ordered processing.

Dynamic partition insertion

INSERT INTO TABLE ecommerce_sales
PARTITION (sale_year, sale_month)
SELECT sale_id,
       customer_id,
       product_id,
       quantity,
       unit_price,
       sale_timestamp,
       YEAR(sale_timestamp),
       MONTH(sale_timestamp)
FROM sales_stage;

Partition-pruned summary query

SELECT product_id,
       SUM(quantity) AS units_sold,
       SUM(quantity * unit_price) AS revenue
FROM ecommerce_sales
WHERE sale_year = 2025 AND sale_month = 1
GROUP BY product_id;

Customer join

SELECT s.sale_id, c.customer_name, s.quantity * s.unit_price AS amount
FROM ecommerce_sales s
JOIN customers c
ON s.customer_id = c.customer_id
WHERE s.sale_year = 2025 AND s.sale_month = 1;

For a bucket-aware join, the customer table should use a compatible bucket column and bucket count, and the data must actually be written according to the declared bucketing rules. This combined design supports partition pruning, distributed joins, compact columnar storage, and scalable analysis.