Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 36 ยท Tuning and Integration

Hive with Spark, HBase, Kafka, HDFS and Beeline

Connect Hive to the rest of the data platform: the shared metastore with Spark and Trino, external tables on HDFS and S3, HBase and Kafka storage handlers, JDBC, Beeline and Python clients.

Hive's most lasting contribution is not the query engine but the metastore: a catalog of table names, columns, partitions and file locations that nearly every big-data tool understands. This lesson shows how Hive plugs into the rest of a data platform.

These integrations need real clusters and services, so connection commands and DDL are read-only. The editors model the core ideas (a shared catalog and incremental loading) in JavaScript and SQL.

One metastore, many engines

Because table definitions live in the Hive Metastore (HMS), different engines can query the same files without copying anything:

flowchart TD
    HMS[("Hive Metastore: names, schemas, partitions, locations")]
    FILES[("Files: HDFS, S3, ADLS, GCS")]
    HIVE["Hive (Tez/LLAP)"] --> HMS
    SPARK["Spark SQL"] --> HMS
    TRINO["Trino / Presto"] --> HMS
    IMP["Impala"] --> HMS
    HMS -. describes .-> FILES
    HIVE --> FILES
    SPARK --> FILES
    TRINO --> FILES
    IMP --> FILES

The catalog says what a table is; any engine can read the files it points to.

A tiny model of this idea:

JS A shared catalog (JavaScript model)

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Spark SQL on Hive tables

Spark connects to the metastore with enableHiveSupport() and a hive.metastore.uris setting:

Python
from pyspark.sql import SparkSession

spark = (SparkSession.builder
         .appName("report")
         .config("hive.metastore.uris", "thrift://metastore-host:9083")
         .enableHiveSupport()
         .getOrCreate())

spark.sql("SELECT sale_date, SUM(amount) FROM sales GROUP BY sale_date").show()
df = spark.table("sales")
df.write.mode("overwrite").partitionBy("sale_date").format("parquet").saveAsTable("sales_copy")

Cautions: ACID tables written by Hive are not generally readable by plain Spark unless a connector (the Hive Warehouse Connector or a vendor's equivalent) is used, so keep tables shared between Hive and Spark as external tables or insert-only. Spark and Hive treat some details differently (timestamps, case sensitivity, CHAR padding); test.

HDFS, S3 and external tables

An external table describes files that Hive does not own: dropping the table removes only metadata. This is the usual way to expose existing data:

SQL
CREATE EXTERNAL TABLE clicks (user_id BIGINT, url STRING, ts TIMESTAMP)
PARTITIONED BY (dt STRING)
STORED AS PARQUET
LOCATION 's3a://lake/clicks/';

MSCK REPAIR TABLE clicks;                 -- discover dt=... directories that already exist
ALTER TABLE clicks ADD PARTITION (dt = '2026-03-01') LOCATION 's3a://lake/clicks/dt=2026-03-01';

MSCK REPAIR TABLE scans the location and registers partition directories that are missing from the metastore. On object stores it can be slow for very large tables; adding partitions explicitly or enabling partition discovery (discover.partitions=true table property in Hive 4 with a metastore housekeeping task) avoids that.

Working with HDFS from the Hive shell: dfs -ls /warehouse; runs an HDFS command; LOAD DATA INPATH '/staging/file.csv' INTO TABLE t; moves an HDFS file into the table directory, while LOAD DATA LOCAL INPATH copies from the client machine.

HBase

HBaseStorageHandler maps a Hive table onto an HBase table, so you can run SQL over key-value data:

SQL
CREATE EXTERNAL TABLE hbase_users (rowkey STRING, name STRING, city STRING)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ('hbase.columns.mapping' = ':key,info:name,info:city')
TBLPROPERTIES ('hbase.table.name' = 'users');

Row-key lookups are fast; full scans through Hive are slow and put load on region servers, so use this for occasional joins, not bulk analytics.

Kafka

The Kafka storage handler (Hive 3.0 and later) exposes a topic as a table with the extra columns __key, __partition, __offset and __timestamp:

SQL
CREATE EXTERNAL TABLE kafka_orders (
  order_id BIGINT, customer_id BIGINT, amount DOUBLE
)
STORED BY 'org.apache.hadoop.hive.kafka.KafkaStorageHandler'
TBLPROPERTIES (
  'kafka.topic' = 'orders',
  'kafka.bootstrap.servers' = 'broker1:9092',
  'kafka.serde.class' = 'org.apache.hadoop.hive.serde2.JsonSerDe'
);

SELECT __partition, MIN(__offset), MAX(__offset), COUNT(*) FROM kafka_orders GROUP BY __partition;

Hive reads a bounded slice of the topic per query, which suits batch jobs that move data from Kafka into ORC tables on a schedule.

Importing from databases

The classic tool for loading relational tables into Hive was Apache Sqoop, which has been retired and moved to the Apache Attic, so new projects use alternatives: Spark JDBC reads, Apache NiFi, Debezium with Kafka, or Hive's own JDBC storage handler (STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler') for federated queries. Whatever tool you use, the standard pattern is incremental loading with a watermark: remember the highest id or timestamp loaded so far and fetch only newer rows.

SQL Incremental load with a watermark (SQLite stand-in) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Watermark loads miss updates and deletes in the source; for those you need change data capture and MERGE (MERGE and compaction).

Clients: JDBC, Beeline and Python

HiveServer2 (port 10000 by default) accepts connections through JDBC/ODBC using the Thrift protocol. Beeline is the command-line client:

Terminal
beeline -u "jdbc:hive2://hs2-host:10000/default;principal=hive/[email protected]"
beeline -u jdbc:hive2://hs2-host:10000/sales -n analyst -p 'secret' -e "SELECT COUNT(*) FROM orders"
beeline -u jdbc:hive2://hs2-host:10000 -f report.sql --outputformat=csv2 > report.csv
beeline -u jdbc:hive2://hs2-host:10000 --hivevar run_date=2026-03-01 -f daily.sql

Inside daily.sql you reference the variable as ${hivevar:run_date}. Other clients: the Hive JDBC driver for BI tools (Tableau, Superset, DBeaver), and Python libraries such as PyHive or impyla:

Python
from pyhive import hive
conn = hive.Connection(host="hs2-host", port=10000, database="sales", username="analyst")
cur = conn.cursor()
cur.execute("SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id LIMIT 10")
print(cur.fetchall())

Never put passwords in scripts or shell history; use Kerberos, a secrets store or environment variables (security).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why can Spark and Trino query the same table that Hive created?

    The metastore stores schema, partitions and location; each engine reads the underlying files itself.
  2. 2. What does MSCK REPAIR TABLE do?

    It scans the table location and adds missing partitions to the metastore.
  3. 3. What is true about dropping an external table?

    Hive does not own external data, so DROP TABLE leaves the files alone.
  4. 4. What is an incremental load with a watermark?

    The watermark marks how far the last load got; it misses source updates and deletes.

Summary

  • The Hive Metastore is the shared catalog that lets Hive, Spark, Trino and Impala read the same files.
  • External tables over HDFS or object stores leave data in place; MSCK REPAIR TABLE registers partitions.
  • Storage handlers expose HBase, Kafka, JDBC sources and more as tables.
  • Sqoop is retired; use Spark, NiFi, Debezium or the JDBC handler with watermark or change-data-capture loading.
  • Connect through HiveServer2 with JDBC, Beeline or Python clients.

Next: Security and HiveServer2