HBase + Hive Integration, in depth: column mapping, what actually pushes down, snapshot reads, HFile loads and lifecycle traps

How the handler plans a query

At planning time, the handler's input format asks HBase for the table's regions and creates roughly one split per region, so a 400-region table becomes 400 scan tasks. Before that, a predicate analyser inspects the WHERE clause and extracts the conditions it can turn into a Scan's start and stop rows, or a timestamp range; everything else stays in the Hive plan. Each task opens a scanner against the RegionServer that owns its region, deserialises cells into Hive rows with the HBase SerDe, and hands them to the rest of the query.

Two alternative paths matter operationally. If hive.hbase.snapshot.name is set, the handler switches to a snapshot input format and reads the snapshot's files from HDFS, restoring references into hive.hbase.snapshot.restoredir, which defaults to /tmp and should point at a dedicated directory the Hive user can write to. That takes RegionServers out of the read path entirely. If hive.hbase.generatehfiles is true, inserts write HFiles instead of Puts, for later bulk loading.

Mapping columns, families and types

A mapping is a comma-separated list with exactly one entry per Hive column, in order. :key is the row key, family:qualifier is one cell, family: maps a whole family to a Hive MAP whose keys are qualifiers, family:prefix.* maps qualifiers that start with a prefix, and :timestamp exposes the cell timestamp as a BIGINT or TIMESTAMP. A suffix of #b says the cell holds binary-encoded values, as written by HBase's Bytes.toBytes; #s, the default unless hbase.table.default.storage.type says otherwise, means the value is a UTF-8 string.

-- HBase shell, owned by the HBase team: pre-split, compressed, sized for the online path
create 'events', {NAME => 'e', COMPRESSION => 'SNAPPY'}, {NAME => 'a'},
       SPLITS => ['1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f']

-- Hive: register it, never let Hive create or drop it
CREATE EXTERNAL TABLE events_hb (
  rowkey       STRING,
  event_type   STRING,
  amount_cents BIGINT,
  attrs        MAP<STRING, STRING>,
  ts           BIGINT
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
  "hbase.columns.mapping" = ":key,e:type,e:amt#b,a:,:timestamp"
)
TBLPROPERTIES ("hbase.table.name" = "events");

The encoding must match the writer exactly. If the application writes amount_cents with Bytes.toBytes(long) and the mapping omits #b, Hive tries to parse eight raw bytes as decimal text and returns NULL for every row, with no error. Map a column as STRING first when you are unsure, inspect values, then tighten the type.

Composite keys can be declared as a STRUCT key with a collection delimiter for simple delimited keys, or with a key factory class for anything else. In practice many teams keep the key as one string and parse parts with split(), because predicates on struct fields do not become key ranges.

What actually pushes down

Pushdown is the single most important behaviour to understand, and it is narrow. In Hive's HiveHBaseTableInputFormat, equality on the key column is always pushed down. The range operators >, >=, < and <= are pushed only when the key is comparable as stored: either the Hive key type is STRING or the key is stored in binary form. A key declared INT but stored as a string gets equality pushdown only, because string order and numeric order disagree ("10" sorts before "9"). If a :timestamp column is mapped, the same range operators on it become a time range on the Scan. Pushdown is governed by hive.optimize.ppd.storage, which defaults to true.

Every other predicate, on event_type, on a map entry, on a function of the key such as substr(rowkey, 1, 4), is evaluated by Hive after HBase has returned every row in the table. So WHERE event_type = 'refund' is a full table scan of the online cluster, however selective it looks. Check the plan with EXPLAIN and the RegionServer request counters on a small test before running anything at scale.

Binary keys need care too. HBase compares keys as unsigned bytes, while Bytes.toBytes writes signed integers in two's complement, so negative numbers sort after positive ones and a pushed range that crosses zero is wrong. Keep binary-integer keys non-negative or use string keys with fixed-width zero padding.

Tuning scans and using snapshots

Three session properties shape the Scan each task opens. hbase.scan.cache sets how many rows each RPC fetches; larger values cut round trips for full scans but increase memory per scanner and the time between client calls. hbase.scan.cacheblock decides whether scanned blocks enter the RegionServer block cache; the handler defaults it to false, which is right for analytics because a full scan would otherwise evict the online working set. hbase.scan.batch limits cells per row per call, which matters for very wide rows.

SET hbase.scan.cache=500;          -- rows per RPC; raise for full scans, lower for wide rows
SET hbase.scan.cacheblock=false;   -- keep analytics out of the block cache (the default)

-- Range scan for one user over one day: pushed down as start/stop rows
SELECT event_type, count(*), sum(amount_cents)
FROM events_hb
WHERE rowkey >= '7f3a|u1842|2026100100' AND rowkey < '7f3a|u1842|2026100200'
GROUP BY event_type;

For whole-table exports, prefer the snapshot path: take an HBase snapshot, set hive.hbase.snapshot.name and a restore directory, and run the export. The RegionServers do no work, the read is consistent as of the snapshot, and the cost moves to HDFS reads. The Hive user needs read access to HBase's files, so test permissions on your distribution first.

Writing to HBase from Hive

An INSERT into a mapped table issues Puts through HBase's write path. Its semantics differ from native Hive tables in ways that surprise people. INSERT OVERWRITE does not delete existing rows; it only overwrites cells for the keys it writes. Two Hive rows with the same key collapse into one HBase row, the later Put winning for each cell. And SET hive.hbase.wal.enabled=false skips the write-ahead log for speed, which means a RegionServer crash loses acknowledged writes; reserve it for loads you can rerun from source.

For large loads, write HFiles. Set hive.hbase.generatehfiles=true and hfile.family.path to an HDFS directory whose last path component is the column family name, which is how the output format knows the family. The handler supports a single column family per load, and rows must reach each writer sorted by row key, which the Hive bulk-load design achieves with range partitioning and CLUSTER BY on the key. Then hand the directory to HBase's bulk-load tool. The online cluster only adopts finished files, so a large backfill costs almost nothing on the RegionServers.

Who owns the table

Lifecycle rules have changed across releases, so read them from your version. In current Hive source, creating a mapped table when the HBase table does not exist creates it, with one family per mapped family and default settings: no pre-splits and no compression. Dropping a managed table, or an external table with external.table.purge set to true, disables and deletes the HBase table. Hive 3 distributions also changed what plain CREATE TABLE means for non-native tables.

The safe pattern is the one in the DDL above: the HBase team creates and owns the table, and Hive registers it as EXTERNAL without the purge property, so DROP TABLE only removes metadata. Keep the mapping DDL in version control next to the application's schema.

Worked example: disputes versus daily totals

A payments service writes events to events with keys of the form salt|user|yyyyMMddHH, where the salt is the first four hex characters of the user id's hash, so writes spread evenly across the pre-split regions. Analysts want two things: one user's events for a given day during disputes, and daily totals by event type.

The dispute query is the range scan above. The application computes the salt, the bounds become start and stop rows, and one region serves a few hundred rows in milliseconds. That is a good fit for Hive, or better still for Phoenix or the application itself when it is interactive.

The daily totals are not. event_type is not in the key, so every query reads the whole table through the RegionServers. At 2 TB, by now split into roughly 200 regions of about 10 GB, each query launches about 200 scan tasks that together stream the whole table through the same RegionServers the payments service reads from. The fix is to stop querying HBase for analytics: a nightly job takes a snapshot, reads it through the snapshot path with a key range covering yesterday's hours, and writes ORC partitioned by day. Analysts query the ORC table; HBase serves the online path. The same pattern in reverse, Hive computing a reference table and bulk-loading it as HFiles, keeps loads off the write path.

Failure modes

• Accidental full scans from non-key predicates. Symptom: RegionServer read latency and request counts spike while the query runs.
• Scanner lease expiry. If a task processes a large cached batch slowly, the gap between scanner calls can exceed the HBase scanner timeout and the scan fails. Lower hbase.scan.cache or speed up the downstream operator.
• Silent NULLs from a string or binary encoding mismatch, or from values that do not parse as the declared Hive type.
• Unexpected deletes from dropping a managed or purge-enabled table.
• Lost writes from disabling the WAL on loads that cannot be replayed.
• Classpath and security drift. Hive needs HBase client jars and hbase-site.xml that match the cluster, and on Kerberised clusters the job needs HBase delegation tokens. HBase permissions, for example Ranger policies, apply in addition to Hive's.

Choosing the right tool

Operationally, give Hive jobs that touch online tables their own queue with a concurrency limit, and alert on RegionServer latency during batch windows.

What to do next

1. List every Hive table mapped to HBase and confirm each is EXTERNAL without purge unless you want Hive to own the data.
2. Verify each column's encoding against the writing application and fix #b suffixes before analysts trust the numbers.
3. Run EXPLAIN on the top queries and rewrite or relocate any whose filters are not on the row key.
4. Move whole-table exports to the snapshot path and land the results in ORC or Parquet.
5. Use HFile generation for large loads and keep the WAL on for everything else.
6. Set scan caching per job, leave block caching off, and cap concurrency for queries against online tables.

Related reading: Impala and HBase, HBase bulk load, how HBase scans work, HBase filters and Apache Phoenix.