Hive Queries, Joins, Views, UDFs, Scripts and Optimization

HiveQL

HiveQL is Hive’s SQL-oriented declarative language for defining metadata and querying distributed data through generated execution plans.

Hive join

A Hive join combines rows by key using strategies such as shuffle join, broadcast map join, bucket join or sort-merge join according to size and layout.

Hive view

A stored Hive query presented like a virtual table; it stores the query definition rather than a separate copy of its result.

UDF

A user-defined function extends a query or processing engine with reusable application-specific transformation logic.

lateral view and explode

LATERAL VIEW with explode converts each array or map element into a row while retaining columns from the original Hive row.

rollup and cube

ROLLUP calculates hierarchical subtotals, while CUBE calculates aggregates for every combination of the listed grouping dimensions.

Hive script

A file of HiveQL statements executed together to create objects, load data or run repeatable analytical transformations.

execution plan

The operators, stages, exchanges and access paths selected by an engine to carry out a logical query or computation.

predicate and partition pruning

Predicate pruning skips row groups using statistics, while partition pruning avoids opening entire directory partitions excluded by filter values.

Hive optimization

Hive optimization uses statistics, columnar formats, partition pruning, join selection.

SQL — Structured Query Language Basics

What is SQL?

SQL (Structured Query Language) is the standard language for interacting with RDBMS.

SQL Categories

Basic SQL Examples

-- CREATE TABLE
CREATE TABLE employees (
    emp_id      INT PRIMARY KEY,
    emp_name    VARCHAR(100) NOT NULL,
    department  VARCHAR(50),
    salary      DECIMAL(10, 2),
    hire_date   DATE
);

-- INSERT data
INSERT INTO employees (emp_id, emp_name, department, salary, hire_date)
VALUES (1, 'Alice', 'Engineering', 75000.00, '2022-01-15');

INSERT INTO employees VALUES (2, 'Bob', 'Marketing', 65000.00, '2021-06-01');

-- SELECT queries
SELECT * FROM employees;    -- all rows, all columns

SELECT emp_name, salary FROM employees WHERE salary > 70000;

-- WHERE with multiple conditions
SELECT * FROM employees 
WHERE department = 'Engineering'
AND salary > 60000;

-- ORDER BY
SELECT emp_name, salary FROM employees ORDER BY salary DESC;

-- GROUP BY + aggregation
SELECT department, COUNT(*) as count, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

-- JOIN (combine multiple tables)
SELECT e.emp_name, d.dept_name, d.location
FROM employees
e
INNER JOIN departments d ON e.department = d.dept_id;

-- UPDATE
UPDATE employees SET salary = 80000 WHERE emp_id = 1;

-- DELETE
DELETE FROM employees WHERE emp_id = 2;

SQL Aggregate Functions

Hive Queries, Joins and Views

Hive queries filter with WHERE, aggregate with GROUP BY and filter groups with HAVING. Window functions calculate values across related rows without collapsing them. Join cardinality must be understood because duplicate keys can multiply output. A map-side join can distribute a sufficiently small table to workers and avoid a large shuffle. Join order, statistics and skew influence the chosen plan.

A view stores a query definition rather than copied result data. It can simplify repeated logic and restrict exposed columns. A materialized view stores results and needs refresh or rewrite support to remain consistent. EXPLAIN reveals operators, scans, exchanges and join strategies before a costly run.

Functions and Optimization

Built-in scalar functions transform one row at a time. Aggregate functions combine groups while table-generating functions such as explode can emit several rows. A UDF extends scalar behavior. Aggregate and table functions use different interfaces because they manage state or multiple output rows. External scripts add process and serialization cost and require controlled deployment.

Optimization begins with partition pruning, column projection and appropriate columnar files. Predicate pushdown lets a reader skip row groups using stored statistics. Accurate table statistics help join planning. Vectorized execution processes batches efficiently. Compression reduces I/O while excessive small files increase scheduling work. Query correctness still depends on null behavior, key grain and data quality even when the physical plan is fast.