Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 36 ยท Schema Design

Schema Patterns: Time Series and Wide Tables

Common HBase schema patterns: tall versus wide tables, time-series buckets, secondary index tables, denormalization and counters, with runnable Java stand-ins.

HBase has no joins, no secondary indexes and no ad-hoc queries. You design the schema backwards from the questions the application must answer, and you accept duplicated data to make those questions cheap. This lesson collects the patterns that appear again and again: tall versus wide tables, time-series buckets, index tables and counters.

It builds on row keys, hotspotting and column families and versions.

Apache HBase cannot run in the live editors. The runnable code below is Java: sorted-map stand-ins that model the row layout. Real client calls appear in read-only blocks.

Start from the queries

Write down each query first: "latest 20 messages in a conversation", "all readings for sensor S between two times", "orders of a customer". For each one decide the key prefix that makes it a single Get or a short Scan. If two important queries need different key orders, you store the data twice (two tables) rather than hoping for an index.

flowchart LR
  Q1["Query: orders by customer"] --> T1["Table orders<br/>key: customer | rev-ts | order"]
  Q2["Query: orders by status"] --> T2["Table orders_by_status<br/>key: status | rev-ts | order"]
  W["Application write"] --> T1
  W --> T2

One write fans out to two tables, one per query.

Tall versus wide

  • A wide table puts many things into one row as qualifiers: row user42 with m:0001, m:0002, ... one column per message. A single Get or Scan returns a whole conversation, and writes to one row are atomic.
  • A tall table makes each thing its own row: user42|0001, user42|0002. Scans over a key prefix return the items, and rows can be split across regions.

Prefer tall for unbounded or large collections. Rows are never split across regions, so a very wide row can grow larger than a region and cannot be scanned in parts without the batch setting. Use wide for small, bounded groups you want to read or update atomically, such as the settings of one user.

Java Wide row versus tall rows (stand-in)

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Both layouts answer "messages of user 42 starting at 3", but the tall layout scales to millions of messages per user without making a single huge row.

Pattern: time-series buckets

Sensor data arrives as (metric, timestamp, value). One row per data point is simple but creates many rows and repeats the key for every value. Another approach is one row per metric per hour, with the offset inside the hour as the qualifier:

TEXT
row key      : metricId | hourStartEpochSeconds
qualifier    : seconds offset within the hour (0..3599)   e.g. "0012"
value        : the reading

An hour of one-second data becomes one row with 3,600 columns: about 3,600 times fewer row keys, and a scan over a time range reads whole hours. Key salting (see hotspotting) is still needed to avoid all writes hitting the current hour: put metricId first so each metric writes to its own key range.

Java Hourly buckets for a time series (stand-in)

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Twelve readings spread over three rows (the range crosses an hour boundary, so the first reading is in a different row from the next), and the range query reads them back in time order.

Pattern: secondary index tables

HBase indexes only the row key. To search by another attribute, maintain an index table whose key starts with that attribute and whose value is the main table's row key. The application (or a coprocessor, see Coprocessors: Observers) writes both. A lookup is then two reads: index to find the key, main table to fetch the row. The cost: two writes per update, and no cross-table transaction, so a crash between the writes can leave the index stale. Design reads to tolerate that, by always checking the main row and ignoring stale hits.

Pattern: denormalization

If a page needs a user's name next to each of their posts, copy the name into the post row. You trade extra write work and the risk of stale copies for single-Get reads. Copy only data that rarely changes, and store a reference (the user key) next to the copy so you can refresh it later.

Pattern: counters

HBase has atomic increments on a single cell: Increment or Table.incrementColumnValue. They are right for page views or likes, but one very hot counter row is a hotspot. Spread a hot counter over several rows (views|page9|0 .. views|page9|7) and sum them on read.

Java
// read-only example
Increment inc = new Increment(Bytes.toBytes("views|page9|" + shard))
    .addColumn(Bytes.toBytes("c"), Bytes.toBytes("n"), 1L);
table.increment(inc);

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why design an HBase schema starting from queries?

    With only the row key as an index and no joins, each query should map to a key prefix or point read.
  2. 2. When is a tall table better than a wide one?

    Rows are never split across regions, so unbounded collections belong in many rows (tall).
  3. 3. What is the downside of a secondary index table?

    The main table and index are separate rows or tables; a failure between the writes can leave them out of sync.
  4. 4. A single page-view counter row receives huge traffic. What helps?

    A hot row is a hotspot on one region; spreading the counter over several rows spreads the load.

Summary

  • Derive the schema from the queries; duplicate data for each key order you need.
  • Tall tables for unbounded collections, wide rows for small atomic groups.
  • Time buckets cut row counts for time series; keep a salting or metric prefix to avoid write hotspots.
  • Index tables and denormalized copies trade write cost for read speed, with eventual consistency.
  • Shard hot counters.

Next: Filters and Scans