Apache Hive / Kyuubi Overview

Apache Hive provides SQL semantics over datasets stored in distributed filesystems and object stores. A Hive deployment is not one daemon or one storage format. It combines a SQL language, metadata services, query compilation, an execution engine, warehouse storage, and client protocols.

Several names surrounding Hive refer to different layers:

1. Hive is the project, HiveQL language, compiler, optimizer, runtime integration, and supporting services.
2. Hive Metastore (HMS) stores and serves table, partition, schema, location, statistics, and transaction metadata. HMS stores metadata rather than table data.
3. HiveServer2 (HS2) accepts remote JDBC and ODBC sessions, compiles HiveQL, coordinates execution, and returns results.
4. hive CLI is the historical thick command-line client. It loads Hive runtime code locally and should not be confused with HiveServer2.
5. Beeline is a JDBC command-line client. Shared deployments use Beeline against HiveServer2 because authentication, configuration, auditing, and resource control remain on the server.
6. Apache Kyuubi is a multi-tenant SQL gateway with a HiveServer2-compatible protocol. It can manage engines such as Spark SQL, but Kyuubi is not HiveServer2 and does not turn Spark SQL into the Hive execution engine.
7. Kerberos authenticates users and services across Hadoop components. Kerberos does not define table schemas, execute SQL, or decide SQL privileges.
8. Apache Kite SDK belongs to the historical Hadoop dataset-tooling ecosystem. It is not part of a current Hive query path.

The distinctions matter because a JDBC connection can succeed while HMS is unavailable, an HMS lookup can succeed while HDFS permissions reject a query, and a Kyuubi endpoint can speak the HiveServer2 protocol without executing Hive’s compiler or runtime.

Architecture #

A shared Hive platform contains four independent planes:

HiveServer2 and HMS are separate services even when a small deployment runs them in one process. Their responsibilities differ:

• HiveServer2 owns client sessions and SQL operations.
• HMS owns durable metadata APIs.
• The metastore RDBMS is an HMS implementation detail and should not be queried or modified by applications.
• Storage systems hold table files independently from the metastore database.
• Execution engines run distributed work; HMS does not scan table files to answer a query.

With fully separated services and no Kyuubi, Beeline connects directly to HiveServer2:

Beeline
   | JDBC / HiveServer2 protocol
   v
HiveServer2
   |---- Thrift RPC -----------> Hive Metastore Service
   |                                  |
   |                                  v
   |                             Metastore RDBMS
   |
   |---- submit DAG -----------> YARN
   |                                  |
   |                                  v
   |                            Tez containers
   |
   `---------------------------> HDFS or object storage

If Spark SQL provides the SQL service instead, Beeline connects directly to Spark Thrift Server:

Beeline
   | JDBC / HiveServer2 protocol
   v
Spark Thrift Server
   |---- Thrift RPC -----------> Hive Metastore Service
   |                                  |
   |                                  v
   |                             Metastore RDBMS
   |
   |---- Spark application ----> YARN
   |                                  |
   |                                  v
   |                            Spark executors
   |
   `---------------------------> HDFS or object storage

In both deployments, the client connects to one specific SQL service. That service owns its client sessions and coordinates its execution engine, while the separately deployed HMS supplies metadata through its Thrift API. Query data flows between the execution containers and storage, not through HMS or the metastore RDBMS.

Kyuubi adds a gateway between Beeline and independently managed SQL engines:

Beeline
   | JDBC / HiveServer2 protocol
   v
Kyuubi server
   |---- launches or reuses ----> Per-user, group, or shared SQL engine
   |                                  |
   |                                  |----> YARN
   |                                  |----> Hive Metastore Service
   |                                  `----> HDFS or object storage
   |
   `---- authentication, routing, sessions, and high availability

Kyuubi separates the client-facing service from query execution. It can route users to isolated or shared engine applications and manage their lifecycle, while presenting a HiveServer2-compatible endpoint. It does not replace HMS, YARN, or storage, and an engine such as Spark SQL still supplies the query semantics and runtime.

The Docker-Based Hadoop/Spark/Hive Orchestration page provides a local multi-service lab. The sections below cover data semantics, query behavior, installation, security, and operations.

Follow an End-to-End Query #

Consider a Beeline query submitted to HiveServer2:

SELECT customer_id, SUM(amount) AS revenue
FROM warehouse_demo.orders
WHERE order_date >= '2026-08-01'
GROUP BY customer_id;

The request follows a sequence similar to the following:

1. Connect and authenticate. Beeline opens a JDBC connection to HiveServer2. HiveServer2 establishes a session identity through the configured authentication mechanism.
2. Parse and analyze. Hive parses HiveQL into an abstract syntax tree and resolves databases, tables, columns, functions, and types.
3. Read metadata. The compiler requests table locations, storage descriptors, partition definitions, statistics, and authorization-relevant metadata from HMS.
4. Authorize. HiveServer2 and any configured authorization plugin evaluate the operation against the authenticated identity and requested objects.
5. Build a logical plan. The planner creates scans, filters, projections, aggregations, joins, and writes.
6. Optimize. Rule-based and cost-based passes can prune partitions and columns, push predicates, reorder joins, select join implementations, and enable vectorized operators when metadata and file formats permit.
7. Build a physical plan. Hive translates the optimized plan into tasks or a directed acyclic graph for an execution engine supported by the installed Hive release.
8. Submit work. HiveServer2 submits distributed work, often through YARN, and propagates credentials or delegation tokens required to reach storage and other services.
9. Read and write storage. Tasks use the table’s InputFormat, SerDe, and file-format reader to produce rows. Output tasks use the corresponding writer and commit protocol.
10. Commit and update metadata. Successful writes publish files and, when required, add partitions, update statistics, or record transactional state in HMS.
11. Return results. HiveServer2 streams or buffers result rows for the JDBC client and records operation status and logs.

HMS remains a side service throughout the query. It supplies metadata and transaction state, but data blocks flow between execution tasks and storage rather than through the metastore.

Understand Databases, Tables, and Locations #

A Hive database is a metadata namespace with a default storage location. A table contains:

• a database-qualified name;
• regular columns and partition columns;
• a storage location;
• a storage descriptor, including SerDe, InputFormat, and OutputFormat;
• table properties;
• optional bucketing, statistics, constraints, and transaction metadata.

Create a namespace and inspect its location:

CREATE DATABASE IF NOT EXISTS warehouse_demo
COMMENT 'Examples for the Hive overview';

DESCRIBE DATABASE EXTENDED warehouse_demo;
USE warehouse_demo;

Warehouse defaults come from service configuration, including managed and external warehouse settings supported by the selected Hive release. An explicit table LOCATION overrides the derived location:

CREATE EXTERNAL TABLE inbound_events (
  event_id    BIGINT,
  event_type  STRING,
  payload     STRING
)
PARTITIONED BY (event_date STRING)
STORED AS PARQUET
LOCATION 'hdfs:///data/inbound/events';

Use DESCRIBE FORMATTED and SHOW CREATE TABLE to inspect the effective metadata rather than inferring it from a DDL file:

DESCRIBE FORMATTED inbound_events;
SHOW CREATE TABLE inbound_events;

Choose Managed or External Ownership #

Managed and external tables differ in ownership rather than query syntax.

Managed Tables #

A managed table delegates lifecycle control to Hive:

CREATE TABLE customers (
  customer_id BIGINT,
  name        STRING,
  region      STRING
)
STORED AS ORC;

Dropping a managed table removes its metadata and, under the active Hive and filesystem semantics, its managed data. Managed locations should therefore contain only files whose lifecycle Hive controls.

External Tables #

An external table registers files whose lifecycle belongs to another process or system:

CREATE EXTERNAL TABLE application_logs (
  occurred_at STRING,
  level       STRING,
  message     STRING
)
PARTITIONED BY (log_date STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION 'hdfs:///shared/application-logs';

Dropping an external table ordinarily removes metadata while preserving files. Table properties such as external.table.purge, storage-specific behavior, and release-specific semantics can change that outcome. Inspect properties and test drop behavior against disposable data before adopting any cleanup workflow.

The ownership rule leads to two operational requirements:

1. Do not place unrelated files below a managed table location.
2. Do not assume that dropping external-table metadata cleans storage.

Changing EXTERNAL metadata after table creation does not automatically move files or transfer ownership between teams.

Map Files to Rows #

HMS stores a table’s logical schema and storage descriptor. Readers combine four pieces of information:

1. InputFormat splits storage objects and creates record readers.
2. SerDe deserializes records into Hive values and serializes values for output.
3. OutputFormat creates record writers and participates in output handling.
4. Table and SerDe properties supply delimiters, encoding details, compression settings, and format-specific behavior.

STORED AS ORC, STORED AS PARQUET, and STORED AS TEXTFILE expand to appropriate storage classes. Explicit classes remain useful for custom formats:

CREATE EXTERNAL TABLE csv_accounts (
  account_id BIGINT,
  email      STRING,
  active     BOOLEAN
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'separatorChar' = ',',
  'quoteChar' = '"',
  'escapeChar' = '\\'
)
STORED AS
  INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 'hdfs:///imports/accounts';

Metadata alone does not validate every file. A table can declare three columns while its text files contain two, five, or malformed fields. Depending on the SerDe and conversion path, incompatible values can become NULL, raise an error, or produce misleading rows.

Compare Common Storage Formats #

Text #

Text tables are suitable for ingestion boundaries and diagnostics because files remain easy to inspect. Costs include:

• no embedded authoritative schema;
• delimiter and escaping ambiguity;
• limited type-aware statistics;
• row-oriented scans;
• higher parsing cost;
• weaker predicate and column pruning.

Compression can reduce storage but may affect splitability. A single gzip stream, for example, cannot be divided into independent ranges by standard text readers.

ORC #

ORC is a columnar format with type metadata, stripes, indexes, compression, and statistics used for predicate pushdown. Hive’s vectorized reader and ACID implementation have close integration with ORC. ORC works well for Hive-managed analytical and transactional tables when every participating engine supports the required ORC and Hive features.

Parquet #

Parquet is also columnar and stores schema, row groups, encodings, compression, and statistics. Its broad engine support makes it common for shared analytical datasets. Compatibility still depends on logical types, timestamp interpretation, decimal precision, field mapping, and writer versions.

Neither format makes an incorrect table definition safe. HMS metadata, file-level schema, and each reader’s mapping rules must agree.

Treat Schema-on-Read as a Contract #

Hive applies table metadata when files are read. Schema-on-read allows several tables to interpret the same files differently, but it does not remove compatibility constraints.

Relatively safe changes include adding nullable columns that compatible readers treat as absent in older files. Riskier changes include:

• reordering columns;
• changing numeric width or decimal precision;
• renaming fields when readers map by name;
• dropping and reusing a field position;
• changing timestamp or character semantics;
• changing partition-column types;
• mixing files written with incompatible logical-type annotations.

ORC, Parquet, Hive, Spark, and Trino can map fields by position, name, or format-specific identifiers depending on configuration and version. Test schema changes through every intended reader before applying them to shared tables. Rewriting data into a new table provides a clearer migration boundary than repeatedly mutating metadata over old files.

Partition Tables Deliberately #

Partition columns divide one logical table into storage prefixes registered in HMS:

CREATE TABLE orders (
  order_id     BIGINT,
  customer_id  BIGINT,
  order_ts     TIMESTAMP,
  amount       DECIMAL(18,2),
  status       STRING
)
PARTITIONED BY (order_date STRING)
STORED AS ORC;

A typical HDFS layout is:

/warehouse/tablespace/managed/hive/warehouse_demo.db/orders/
├── order_date=2026-08-08/
│   └── 000000_0
└── order_date=2026-08-09/
    └── 000000_0

Partition values live in directory names and HMS metadata; they do not need to appear inside every data file. Query predicates allow partition pruning:

SELECT SUM(amount)
FROM orders
WHERE order_date BETWEEN '2026-08-01' AND '2026-08-09';

Direct predicates on partition columns are easiest to prune. Wrapping the column in a function or deriving the filter through a complex expression can prevent pruning, depending on optimizer capabilities.

Partitioning should match common filters without creating excessive metadata:

• hourly partitions can be appropriate for high-volume event data;
• one partition per user creates unbounded HMS and filesystem pressure;
• low-volume partitions often produce many small files;
• object-store partition discovery can require expensive listings.

Write Static and Dynamic Partitions #

Create a text staging table:

CREATE TABLE staging_orders (
  order_id     BIGINT,
  customer_id  BIGINT,
  order_ts     TIMESTAMP,
  amount       DECIMAL(18,2),
  status       STRING,
  order_date   STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

A static partition insert fixes the partition value in the statement:

INSERT OVERWRITE TABLE orders
PARTITION (order_date = '2026-08-09')
SELECT
  order_id,
  customer_id,
  order_ts,
  amount,
  status
FROM staging_orders
WHERE order_date = '2026-08-09';

A dynamic partition insert takes partition values from the final projected columns:

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

INSERT INTO TABLE orders
PARTITION (order_date)
SELECT
  order_id,
  customer_id,
  order_ts,
  amount,
  status,
  order_date
FROM staging_orders
WHERE order_date >= '2026-08-01';

Dynamic inserts can create many partitions in one statement. Apply release-appropriate limits for dynamic partitions and files, and reject unexpected partition cardinality before submission.

Register Partitions and Repair Metadata #

Files placed under an external table do not always create HMS partition records. Register a known partition explicitly:

ALTER TABLE application_logs
ADD IF NOT EXISTS
PARTITION (log_date = '2026-08-09')
LOCATION 'hdfs:///shared/application-logs/log_date=2026-08-09';

Inspect registered values:

SHOW PARTITIONS application_logs;

MSCK REPAIR TABLE scans compatible directory names and reconciles some missing metadata:

MSCK REPAIR TABLE application_logs;

Repair behavior differs by Hive release and options. A repair can require a large recursive listing, may add partitions without removing stale metadata, and can register directories that match the naming convention but contain invalid files. Object stores amplify listing cost and consistency concerns. Prefer explicit ALTER TABLE ADD PARTITION operations in ingestion pipelines because the producer already knows which partition it committed.

Removing files outside Hive also leaves stale partition metadata. Dropping a partition from an external table may remove only metadata, while managed-table behavior can remove data. Verify ownership semantics before running:

ALTER TABLE application_logs
DROP IF EXISTS PARTITION (log_date = '2026-07-01');

Use Bucketing as a Write Contract #

Bucketing hashes one or more columns into a declared number of files:

CREATE TABLE bucketed_orders (
  order_id     BIGINT,
  customer_id  BIGINT,
  order_ts     TIMESTAMP,
  amount       DECIMAL(18,2)
)
CLUSTERED BY (customer_id)
SORTED BY (order_ts)
INTO 32 BUCKETS
STORED AS ORC;

Bucket metadata can support sampling and some join or aggregation optimizations. Benefits require compatible hash functions, bucket counts, writers, and execution settings.

HMS records the bucket declaration but does not prove that existing files obey it. Copying arbitrary files into a bucketed table location can produce a table that claims 32 buckets while containing incorrectly hashed or named files. Write bucketed data through a compatible engine, inspect the physical output, and validate results before relying on bucket-aware optimizations. SORTED BY also describes the expected write layout; it does not sort files that already exist.

Load Files or Rewrite Rows #

LOAD DATA and INSERT serve different purposes.

LOAD DATA #

LOAD DATA LOCAL INPATH '/srv/hive-import/orders.tsv'
OVERWRITE INTO TABLE staging_orders;

LOAD DATA traditionally copies or moves files into a table or partition location without parsing and rewriting every row. LOCAL refers to the HiveServer2 host’s filesystem for a remote Beeline session, not the Beeline workstation. Release-specific checks can rewrite or reject some loads, especially for partitioned or bucketed targets, so consult the documentation for the installed release.

A successful load does not prove that delimiters, types, or bucket assignments are correct. Query and validate the staging table afterward.

INSERT #

INSERT INTO TABLE orders
PARTITION (order_date = '2026-08-09')
SELECT
  order_id,
  customer_id,
  order_ts,
  amount,
  status
FROM staging_orders
WHERE order_date = '2026-08-09';

INSERT executes a query and writes through the target table’s serializer and output format. The operation costs compute, but it enforces target projection and produces the configured storage format.

Build Tables with CTAS #

Create-table-as-select combines schema derivation and population:

CREATE TABLE high_value_orders
STORED AS ORC
AS
SELECT
  order_id,
  customer_id,
  order_ts,
  amount,
  status,
  order_date
FROM orders
WHERE amount >= 1000.00;

CTAS is useful for materialized intermediate datasets and format conversion. Explicit DDL remains preferable when consumers require precise column comments, constraints, partitioning, bucketing, transaction properties, or location control.

Write Analytical HiveQL #

Joins and Aggregations #

SELECT
  c.region,
  COUNT(*)      AS order_count,
  SUM(o.amount) AS revenue
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2026-08-01' AND '2026-08-09'
  AND o.status = 'PAID'
GROUP BY c.region
ORDER BY revenue DESC;

Common Table Expressions #

WITH paid_orders AS (
  SELECT customer_id, amount
  FROM orders
  WHERE order_date = '2026-08-09'
    AND status = 'PAID'
),
customer_totals AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM paid_orders
  GROUP BY customer_id
)
SELECT
  c.customer_id,
  c.name,
  t.revenue
FROM customer_totals t
JOIN customers c
  ON t.customer_id = c.customer_id
WHERE t.revenue >= 5000.00;

Window Functions #

SELECT
  customer_id,
  order_id,
  order_ts,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_ts DESC
  ) AS recency_rank,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_revenue
FROM orders
WHERE order_date BETWEEN '2026-08-01' AND '2026-08-09';

Window functions preserve input-row granularity while computing values over a partition. A GROUP BY instead collapses rows into one output row per grouping key.

Inspect Plans with EXPLAIN #

Use EXPLAIN before running a costly statement:

EXPLAIN FORMATTED
SELECT
  c.region,
  SUM(o.amount) AS revenue
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
WHERE o.order_date = '2026-08-09'
GROUP BY c.region;

Review the plan for:

• selected partitions;
• projected columns;
• pushed predicates;
• join order and join implementation;
• estimated row counts;
• shuffle boundaries;
• vectorized operators;
• number and type of execution stages.

Plan syntax and detail vary by Hive release and execution engine. Compare plans under the same release and configuration rather than treating plan text as a stable API.

Collect Statistics for the Optimizer #

The cost-based optimizer needs current table, partition, and column statistics:

ANALYZE TABLE customers
COMPUTE STATISTICS;

ANALYZE TABLE customers
COMPUTE STATISTICS FOR COLUMNS customer_id, region;

ANALYZE TABLE orders
PARTITION (order_date = '2026-08-09')
COMPUTE STATISTICS;

ANALYZE TABLE orders
PARTITION (order_date = '2026-08-09')
COMPUTE STATISTICS FOR COLUMNS customer_id, amount, status;

Inspect recorded metadata:

DESCRIBE FORMATTED customers;
DESCRIBE FORMATTED orders PARTITION (order_date = '2026-08-09');

Statistics become stale after external file changes, partition replacement, or writes by engines that do not update HMS in the expected way. Missing or stale row counts can produce poor join orders and memory estimates. Automatic statistics settings reduce manual work but do not eliminate the need to monitor coverage and freshness.

Select and Configure an Execution Engine #

Hive compiles a query into work for an execution backend. Supported choices depend on the exact Hive distribution and release:

• MapReduce is part of Hive’s historical execution path and remains relevant to older deployments.
• Tez is supported by many Hive deployments and executes query stages as DAGs.
• Spark was available as a Hive execution engine in some release lines, but support, packaging, and maintenance status are version-dependent.
• Spark SQL through Spark or Kyuubi is a separate SQL engine even when it reads the same HMS catalog.

Never set an engine name only because another cluster component is installed. Verify the Hive release documentation, engine artifacts, Hadoop compatibility, service configuration, and operational tooling first.

Inspect and change a mutable session property:

SET hive.execution.engine;
SET hive.execution.engine=tez;
SET hive.execution.engine;

Configuration sources have increasing scope and precedence:

1. Hive’s built-in defaults provide baseline values.
2. hive-site.xml sets service-wide deployment values.
3. Service startup arguments such as --hiveconf override configured values for that process.
4. A session SET can override properties that HiveServer2 permits clients to change.
5. A statement hint can influence a supported optimizer decision for one query.

HiveServer2 can restrict or whitelist session-modifiable properties. Server-only settings, security controls, and static initialization values may reject SET changes or require a restart. hivevar values perform query substitution and are not equivalent to configuration:

SET hivevar:run_date=2026-08-09;

SELECT COUNT(*)
FROM orders
WHERE order_date = '${hivevar:run_date}';

Use bind parameters in application code when supported by the client path; textual substitution is not a general SQL-injection defense.

Understand the Main Optimizations #

Partition and Predicate Pruning #

Partition pruning avoids listing and opening unrelated partition locations. Predicate pushdown sends compatible filters into ORC or Parquet readers so stripes, row groups, and pages can be skipped from statistics.

Opaque UDFs, casts, incompatible types, or expressions around filtered columns can block pushdown. Confirm behavior in EXPLAIN rather than assuming a predicate reached the file reader.

Column Pruning #

Columnar readers can skip unreferenced columns:

SELECT customer_id, amount
FROM orders
WHERE order_date = '2026-08-09';

SELECT * increases I/O, deserialization, network transfer, and schema-coupling risk. Project only required columns in durable jobs.

Vectorization #

Vectorized execution processes batches of column values rather than one row object at a time. ORC and compatible operators benefit most. Unsupported types, UDFs, SerDes, expressions, or execution paths can fall back to row mode. Check the plan and runtime counters for the installed release before attributing a performance change to vectorization.

Cost-Based Optimization #

The cost-based optimizer uses row counts, data sizes, distinct-value estimates, and column distributions to compare plans. CBO cannot compensate for absent or misleading statistics. Refresh statistics after material changes and validate join order with EXPLAIN.

Join Strategy #

Small dimensions can be broadcast or map-joined to avoid shuffling the large side:

SELECT /*+ MAPJOIN(c) */
  o.order_id,
  c.region,
  o.amount
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
WHERE o.order_date = '2026-08-09';

Hints should document a measured exception rather than replace statistics. Broadcasting a table that exceeds task memory causes failures or long garbage-collection pauses.

Large joins require compatible partitioning and shuffle capacity. Bucket-aware joins can reduce work only when both metadata and physical files satisfy the required bucket contract.

Skew #

A few high-frequency keys can concentrate records in one reducer or task. Symptoms include one task running far longer than peers and disproportionate shuffle input. Responses include:

• filtering or aggregating before the join;
• separating known heavy keys;
• salting keys and recombining results;
• using a supported skew-join optimization;
• broadcasting the genuinely small side;
• increasing parallelism without creating excessive small output files.

No single setting corrects every skew pattern because data distribution and query shape determine the bottleneck.

Small Files #

Small files increase:

• NameNode and HMS-related metadata pressure;
• object-store listing and request cost;
• task setup overhead;
• reader initialization;
• output commit operations.

Small files often originate from high partition cardinality, excessive task parallelism, frequent micro-batches, or one writer per upstream partition. Prefer larger batch writes, bounded partition counts, and periodic rewrites. A compaction query can rewrite one partition:

INSERT OVERWRITE TABLE orders
PARTITION (order_date = '2026-08-09')
SELECT
  order_id,
  customer_id,
  order_ts,
  amount,
  status
FROM orders
WHERE order_date = '2026-08-09';

Test overwrite semantics with the chosen table type and execution engine. Transactional tables use their compaction subsystem instead of treating a non-transactional rewrite as equivalent.

Understand Hive ACID Tables #

Hive ACID tables add row-level transaction metadata and snapshot-aware readers. Exact requirements and supported operations differ across releases, storage backends, table types, and interoperating engines. Verify the transaction documentation for every writer and reader.

A conservative full-ACID definition uses ORC and explicit bucketing:

CREATE TABLE account_balances (
  account_id BIGINT,
  balance    DECIMAL(18,2),
  updated_at TIMESTAMP
)
CLUSTERED BY (account_id) INTO 16 BUCKETS
STORED AS ORC
TBLPROPERTIES (
  'transactional' = 'true'
);

Run transactional DML:

INSERT INTO account_balances
VALUES
  (1001, 125.50, CURRENT_TIMESTAMP),
  (1002, 980.00, CURRENT_TIMESTAMP);

UPDATE account_balances
SET balance = balance + 25.00,
    updated_at = CURRENT_TIMESTAMP
WHERE account_id = 1001;

DELETE FROM account_balances
WHERE account_id = 1002;

MERGE INTO account_balances AS target
USING balance_updates AS source
ON target.account_id = source.account_id
WHEN MATCHED THEN UPDATE SET
  balance = source.balance,
  updated_at = source.updated_at
WHEN NOT MATCHED THEN INSERT VALUES (
  source.account_id,
  source.balance,
  source.updated_at
);

MERGE, update, delete, insert-only tables, external transactional tables, and non-ORC combinations have release-specific restrictions. Do not infer support from DDL parsing alone; execute concurrency, restart, compaction, and cross-engine tests.

Base and Delta Files #

Transactional storage contains logical generations:

• base_WRITEID directories contain a compacted snapshot up to a write ID.
• delta_MIN_MAX directories contain inserted or updated rows for a write-ID range.
• delete_delta_MIN_MAX directories contain delete events for rows in older files.

Readers obtain a valid-write-ID list from the transaction system and merge visible base, delta, and delete-delta content into a snapshot. File listings therefore do not directly represent current table rows.

Locking and Transaction State #

HMS stores open transactions, write IDs, locks, and compaction state in its RDBMS. A typical transaction manager setting is:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
  <name>hive.support.concurrency</name>
  <value>true</value>
</property>
<property>
  <name>hive.txn.manager</name>
  <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
</configuration>

Inspect state through supported SQL commands:

SHOW TRANSACTIONS;
SHOW LOCKS account_balances EXTENDED;
SHOW COMPACTIONS;

Long-running or abandoned transactions can hold snapshots, delay cleaner work, and retain obsolete files. Diagnose the owning query or service before aborting transactions through release-specific administrative procedures.

Compaction #

Minor compaction combines delta files. Major compaction rewrites deltas into a new base. A cleaner removes obsolete files only after no valid reader needs them.

Request compaction:

ALTER TABLE account_balances COMPACT 'minor';
ALTER TABLE account_balances COMPACT 'major';
SHOW COMPACTIONS;

Metastore-side initiator, worker, and cleaner processes must be enabled according to the installed release. A successful ALTER TABLE ... COMPACT request only queues work; it does not prove that a worker ran or that obsolete files were cleaned. Monitor queue age, failed attempts, open transactions, and filesystem growth.

Install and Configure Hive #

Select a Hive release only after checking its documented compatibility with the chosen Java, Hadoop, metastore database, and execution-engine versions. A process that starts successfully does not prove that every query path is compatible.

Extract Hive on the service hosts and define stable locations:

export JAVA_HOME=/opt/java
export HADOOP_HOME=/opt/hadoop
export HIVE_HOME=/opt/hive
export HIVE_CONF_DIR=/etc/hive/conf
export PATH="$HIVE_HOME/bin:$HADOOP_HOME/bin:$JAVA_HOME/bin:$PATH"

Copy $HIVE_HOME/conf/hive-env.sh.template to the configuration directory when the release provides that template. Set service-specific JVM options and Hadoop paths there rather than modifying scripts under $HIVE_HOME/bin.

Hive needs:

1. Hadoop client configuration that resolves the intended filesystem and resource manager;
2. hive-site.xml with warehouse, metastore, HiveServer2, execution, and security settings;
3. the JDBC driver for the selected metastore RDBMS in the metastore process’s classpath; and
4. service identities with access to warehouse, scratch, log, and local work directories.

Prepare HDFS paths with permissions that match the deployment’s service and group model:

hdfs dfs -mkdir -p /tmp /warehouse/tablespace/managed/hive
hdfs dfs -chmod 1777 /tmp
hdfs dfs -chown -R hive:hadoop /warehouse/tablespace/managed/hive
hdfs dfs -chmod -R 0770 /warehouse/tablespace/managed/hive

The example grants warehouse access to the hive service and hadoop group. Replace those identities and modes with the authorization model used by the cluster.

For a single-process experiment, Derby can initialize a local metastore:

mkdir -p "$HOME/hive-derby-lab"
cd "$HOME/hive-derby-lab"
schematool -dbType derby -initSchema --verbose
schematool -dbType derby -info
hiveserver2

Derby stores metastore_db relative to the process configuration and working directory unless configured otherwise. Running schema initialization and HiveServer2 from inconsistent locations can therefore create two independent metastores. Derby’s single-owner behavior makes it unsuitable for a shared HMS or multiple HiveServer2 instances.

Deploy HMS with an External RDBMS #

Shared deployments should use a supported external RDBMS such as PostgreSQL or MySQL for metastore state. Embedded Derby permits one owning process and is appropriate only for isolated tests.

Configure the metastore process with a JDBC driver and properties similar to:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:postgresql://metastore-db.example.com:5432/hive_metastore</value>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>org.postgresql.Driver</value>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>hive_metastore</value>
  </property>
  <property>
    <name>hadoop.security.credential.provider.path</name>
    <value>jceks://file/etc/hive/conf/hive-metastore.jceks</value>
  </property>
  <property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/warehouse/tablespace/managed/hive</value>
  </property>
  <property>
    <name>hive.metastore.warehouse.external.dir</name>
    <value>/warehouse/tablespace/external/hive</value>
  </property>
  <property>
    <name>hive.metastore.schema.verification</name>
    <value>true</value>
  </property>
</configuration>

Create the matching password alias through a Hadoop credential provider supported by the deployed release:

hadoop credential create javax.jdo.option.ConnectionPassword \
  -provider jceks://file/etc/hive/conf/hive-metastore.jceks

The command prompts for the value. Protect the provider file with the Hive service identity and the platform’s secret-distribution controls. Do not place a clear-text production password in source control.

Initialize a new schema once:

export HIVE_CONF_DIR=/etc/hive/conf

schematool -dbType postgres -initSchema --verbose
schematool -dbType postgres -info

Start the Thrift metastore:

hive --service metastore -p 9083

HiveServer2 and other HMS clients need a metastore URI:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
  <property>
    <name>hive.metastore.uris</name>
    <value>thrift://hive-metastore.example.com:9083</value>
  </property>
  <property>
    <name>hive.metastore.client.socket.timeout</name>
    <value>600</value>
  </property>
</configuration>

Before a Hive upgrade:

1. Stop or fence writers according to the release’s upgrade procedure.
2. Back up the metastore RDBMS through its native backup tools.
3. Verify the current schema and software version.
4. Run the documented schema upgrade once.
5. Start HMS and verify reads before reopening writers.

Typical schema commands are:

schematool -dbType postgres -info
schematool -dbType postgres -upgradeSchema --verbose
schematool -dbType postgres -info

Never edit metastore tables manually to bypass a failed migration. HMS schema details are internal and can change between releases.

Serve SQL through HiveServer2 #

Start HiveServer2 in the foreground for service-manager supervision:

hiveserver2 \
  --hiveconf hive.server2.thrift.bind.host=0.0.0.0 \
  --hiveconf hive.server2.thrift.port=10000

Production process supervision should handle restart policy, logs, environment, resource limits, and credentials. Starting HMS first is necessary but not sufficient; verify that HiveServer2 can reach HMS, storage, the execution backend, and its scratch directories.

Connect with Beeline:

beeline \
  -u 'jdbc:hive2://hiveserver2.example.com:10000/warehouse_demo' \
  -n alice

Run a small DDL smoke test before submitting distributed work:

CREATE DATABASE IF NOT EXISTS demo;
USE demo;
CREATE TABLE IF NOT EXISTS greeting (id INT, name STRING);
SHOW TABLES;
DESCRIBE FORMATTED greeting;

Run a script non-interactively:

beeline \
  -u 'jdbc:hive2://hiveserver2.example.com:10000/warehouse_demo' \
  -n alice \
  --showHeader=true \
  --outputformat=tsv2 \
  -f report.sql

Useful Beeline commands include:

!info
!tables
!describe orders
!set
!quit

Beeline commands begin with !; HiveQL statements end with a semicolon.

Connect through JDBC #

A Java client needs the Hive JDBC driver and its compatible dependencies:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public final class HiveRevenueQuery {
    public static void main(String[] args) throws Exception {
        String url = System.getenv("HIVE_JDBC_URL");
        Properties properties = new Properties();
        properties.setProperty("user", System.getenv().getOrDefault("HIVE_USER", "alice"));

        try (Connection connection = DriverManager.getConnection(url, properties);
             PreparedStatement statement = connection.prepareStatement(
                 "SELECT customer_id, SUM(amount) AS revenue " +
                 "FROM warehouse_demo.orders " +
                 "WHERE order_date = ? " +
                 "GROUP BY customer_id")) {
            statement.setString(1, "2026-08-09");

            try (ResultSet rows = statement.executeQuery()) {
                while (rows.next()) {
                    System.out.printf(
                        "%d\t%s%n",
                        rows.getLong("customer_id"),
                        rows.getBigDecimal("revenue")
                    );
                }
            }
        }
    }
}

Set the URL outside source code:

export HIVE_JDBC_URL='jdbc:hive2://hiveserver2.example.com:10000/warehouse_demo'
java -cp 'app.jar:hive-jdbc.jar:lib/*' HiveRevenueQuery

Dependency assembly depends on the Hive release and application build system. Use the JDBC artifacts documented for the target server because an arbitrary driver version can fail during authentication, protocol negotiation, or type conversion.

Separate Authentication from Authorization #

Security decisions span several layers.

Kerberos Authentication #

Kerberos proves an identity. A secured client obtains a ticket:

kinit alice@EXAMPLE.COM
klist

beeline \
  -u 'jdbc:hive2://hiveserver2.example.com:10000/warehouse_demo;principal=hive/hiveserver2.example.com@EXAMPLE.COM'

HiveServer2 itself uses a service principal and protected keytab. HMS, HDFS, YARN, and other services can have separate principals. Successful Kerberos authentication does not grant SELECT, DROP, HDFS read, or YARN submission rights.

The Kerberos Beeline-to-Kyuubi walkthrough follows ticket acquisition, SASL/GSSAPI authentication, TLS, Kyuubi session selection, and Hadoop delegation for a complete JDBC URL.

SQL Authorization #

SQL privileges require a Hive authorization mode or an external policy system such as Apache Ranger. Depending on deployment and version, policy enforcement can cover:

• database, table, column, and URI access;
• row filters and column masking;
• administrative operations;
• audit records.

Every query path must enforce equivalent policy. A table protected in HiveServer2 can still leak if users can read its HDFS files directly or reach another engine with weaker authorization.

doAs and Storage Identity #

With HiveServer2 impersonation enabled, storage and execution operations can run as the connected user. Hadoop proxy-user rules, delegation tokens, local user mapping, and HDFS permissions must support that model.

With impersonation disabled, operations run as the Hive service identity. SQL authorization then becomes essential because HDFS sees one service account for many users. Choose one identity model deliberately and test auditing across HS2, YARN, and storage.

HDFS Permissions #

HDFS permissions govern warehouse directories, scratch paths, staging paths, and direct file access. Verify from the service identity:

hdfs dfs -ls -d /warehouse/tablespace/managed/hive
hdfs dfs -test -w /warehouse/tablespace/managed/hive
hdfs dfs -ls -d /tmp/hive

World-writable warehouse directories remove an important defense. Shared scratch directories may need sticky-bit semantics, while table data should use service ownership, groups, ACLs, or policies aligned with the authorization model.

TLS and Secrets #

Kerberos does not automatically encrypt every JDBC transport. Configure HiveServer2 TLS and trust validation when network confidentiality is required:

beeline \
  -u 'jdbc:hive2://hiveserver2.example.com:10000/warehouse_demo;ssl=true;sslTrustStore=/etc/security/truststore.jks'

Supply truststore passwords through a protected client configuration or secret mechanism rather than command-line history. Apply the same rule to metastore database passwords, LDAP credentials, keytabs, cloud-storage credentials, and JDBC URLs containing secrets.

Place Kyuubi Correctly #

Kyuubi exposes a HiveServer2-compatible interface and manages per-user or shared SQL engines. A common path is:

Beeline or JDBC
    -> Kyuubi server
    -> Spark SQL engine
    -> HMS-compatible catalog
    -> HDFS or object storage

The protocol can resemble HiveServer2 while semantics differ:

• Spark SQL parses and optimizes the query rather than Hive.
• Spark configuration controls execution.
• Hive-only syntax, authorization hooks, ACID behavior, and UDFs may not carry over.
• HMS can remain the shared catalog without becoming the execution engine.

Document the endpoint as Kyuubi rather than labeling every HiveServer2-protocol service as HiveServer2.

Treat Kite as Historical Context #

Kite SDK offered Java APIs and command-line tools for datasets, schemas, and Hadoop storage integrations. Existing systems may still contain Kite-created metadata or code, but Hive does not require Kite. Migrate or maintain such applications according to their own dependency constraints rather than adding Kite to a new Hive architecture.

Operate HiveServer2 and HMS #

Track Query and Session State #

HiveServer2 maintains sessions and operations separately. A disconnected client can leave work running until timeout or cancellation policy removes it. Monitor:

• open and idle sessions;
• active and queued operations;
• query IDs and YARN application IDs;
• compile, queue, execution, and fetch time;
• result-set and operation-log retention;
• authentication and authorization failures.

Use the service’s supported metrics, JMX, logs, and management endpoints for the installed release. Log formats and web interfaces are not stable across all releases.

Capture client-visible diagnostics:

beeline \
  -u 'jdbc:hive2://hiveserver2.example.com:10000/warehouse_demo' \
  --verbose=true \
  -f failing-query.sql

Correlate the Hive query ID with HiveServer2 logs, engine DAG or job IDs, and YARN applications.

Check Metastore Health #

HMS latency affects compilation, partition listing, authorization, and transaction processing. Monitor:

• RDBMS connectivity and connection-pool saturation;
• slow queries and lock waits in the metastore database;
• HMS request latency and error rate;
• schema version;
• partition and column-statistics volume;
• open transactions and compaction queues;
• database storage, backup, and replication health.

Verify the schema through the supported tool:

schematool -dbType postgres -info

Do not use direct SQL updates as a routine repair mechanism.

Check HDFS, YARN, and Tez #

Separate service failures from SQL failures:

hdfs dfsadmin -report
yarn node -list -all
yarn application -list -appStates ACCEPTED,RUNNING,FAILED
yarn logs -applicationId application_1234567890000_0042

Replace the application ID with the value reported by HiveServer2 or YARN. For Tez, inspect the DAG status and available Tez diagnostics. An HS2 session can remain healthy while an execution application is rejected for queue capacity, missing resources, credentials, or container memory.

Check Warehouse and Scratch Permissions #

Test all paths used by a write:

hdfs dfs -ls -d /warehouse/tablespace/managed/hive
hdfs dfs -ls -d /warehouse/tablespace/external/hive
hdfs dfs -ls -d /tmp
hdfs dfs -du -h /warehouse/tablespace/managed/hive/warehouse_demo.db

A read-only query can succeed while CTAS or INSERT fails because the service cannot create a staging directory or rename committed output.

Check Partitions and Statistics #

Compare physical and metastore state:

SHOW PARTITIONS orders;
DESCRIBE FORMATTED orders PARTITION (order_date = '2026-08-09');
SELECT COUNT(*) FROM orders WHERE order_date = '2026-08-09';

An empty query result can mean:

• no partition metadata;
• a wrong partition location;
• files under a different directory spelling;
• incompatible SerDe or schema;
• missing storage credentials;
• predicate values that do not match the registered partition string.

Refresh statistics after correcting data or metadata.

Check Compaction Backlog #

SHOW TRANSACTIONS;
SHOW COMPACTIONS;
SHOW LOCKS account_balances EXTENDED;

Investigate failed compactions, absent workers, long-open transactions, and cleaner delays. Deleting ACID delta directories manually can corrupt snapshots and is not a compaction strategy.

Troubleshoot by Layer #

Change one layer at a time. Replacing JARs, changing metastore rows, and deleting files simultaneously removes the evidence needed to identify the original fault.

Interoperate with Spark, Trino, and Iceberg #

Shared metadata does not imply identical semantics.

Spark #

Spark SQL can use an HMS-compatible catalog and read Hive tables. Spark then supplies its own parser, optimizer, execution engine, file readers, committers, and configuration. Compatibility depends on:

• storage format and SerDe;
• partition registration;
• timestamp and decimal settings;
• bucketing implementation;
• Hive ACID support;
• catalog client version;
• authorization integration.

Spark’s enableHiveSupport() configures Hive catalog integration; it does not route Spark SQL through HiveServer2.

Trino #

Trino’s Hive connector can use HMS for table and partition metadata while reading storage directly. Queries do not pass through HiveServer2. SQL syntax, type mapping, views, security, ACID capabilities, and write behavior depend on the connector and release.

Iceberg #

Iceberg is a table format with snapshot metadata, manifests, schema and partition evolution, and its own commit model. HMS can act as an Iceberg catalog by storing pointers and identifiers, but HMS does not replace Iceberg’s table metadata.

Keep the layers distinct:

Do not register an Iceberg table as a plain external Parquet table and write around its metadata. Such writes bypass Iceberg snapshots and can make files invisible or corrupt table invariants. Use a catalog and connector combination explicitly supported by every participating engine.

Plan Changes against Exact Releases #

Hive behavior changes across release lines, especially for:

• Java and Hadoop compatibility;
• execution engines;
• ACID table requirements;
• external-table drop semantics;
• authorization plugins;
• metastore schema versions;
• vectorization and optimizer rules;
• timestamp, decimal, ORC, and Parquet behavior;
• object-store commit protocols;
• interoperability with Spark, Trino, and table formats.

Read the documentation and release notes for the installed version before adopting a property or syntax from another cluster. A configuration key accepted at startup may be ignored, deprecated, or semantically different.

Official References #

• Apache Hive Documentation
• Apache Hive Downloads and Release Information
• Hive Language Manual
• HiveServer2 Clients and Beeline
• Hive Transactions and ACID
• Hive Configuration Properties

The Apache wiki is read-only for migrated topics and links to the current documentation. Prefer the documentation set matching the deployed release when details differ from the latest pages.