Phoenix Materialized Views: What Actually Exists and How to Get the Same Effect

If you came here looking for a CREATE MATERIALIZED VIEW statement in Apache Phoenix, here is the useful answer up front: Phoenix does not have one. There is no phoenix.mv.enable property and no automatic aggregate-refresh machinery. What Phoenix does support — and what most people mean when they say "materialized view" in this context — is a view with an index built on top of it. The index is a real HBase table that Phoenix keeps in sync with the base table, so queries against the view's key columns can be answered from the index instead of scanning the base table. That gives you the practical benefit of a materialized view (precomputed, fast reads) with a different mechanism.

The mechanism: view plus index, not a refresh job

A Phoenix view is a logical layer over an HBase table — it stores no data itself. A Phoenix index (global or local) is a separate physical structure that duplicates selected columns, keyed for fast lookup. When you combine them, the index on the view acts as the "materialized" part:

• Writes to the base table are synchronously reflected in the index table by Phoenix's coprocessor on the region server. There is no batch refresh; consistency is maintained at write time.
• Reads that match the index key are served from the index table. The query planner rewrites the query automatically when the index covers the query (all referenced columns are present in the index, either in the key or via INCLUDE).

This is meaningfully different from a relational materialized view: you cannot pre-aggregate (GROUP BY results are not stored), and you cannot pre-join. What you can precompute is row layout — a different primary key order and a denormalized column set.

Worked example: an orders table queried by customer

Assume Phoenix 5.x on HBase 2.x (the same pattern works on Phoenix 4.10+). Your base table is keyed by order ID, but your hot query filters by customer and date:

-- Run via sqlline.py or any JDBC client; needs CREATE permission
CREATE TABLE orders (
  order_id     BIGINT NOT NULL PRIMARY KEY,
  customer_id  BIGINT,
  order_date   DATE,
  status       VARCHAR,
  total        DECIMAL
);

-- A view restricting to the rows/columns the app cares about
CREATE VIEW active_orders (
  pk2 VARCHAR  -- extra key column appended to the view PK
) AS SELECT * FROM orders WHERE status != 'ARCHIVED';

-- The "materialized" part: a global index re-keyed by customer
CREATE INDEX idx_orders_by_cust
  ON active_orders (customer_id, order_date)
  INCLUDE (total, status);

Now a query like SELECT customer_id, order_date, total FROM active_orders WHERE customer_id = 42 can be answered entirely from the index table — a covering index scan — without touching the base table's regions. Verify this with EXPLAIN in sqlline; the plan should reference the index table (Phoenix names index tables after the index) rather than a full scan over ORDERS. Do not trust that the index is used just because it exists — check the plan.

Operational notes

• Where to run: DDL via sqlline.py or JDBC against the Phoenix query server / thick client. Index creation needs write access to HBase (it creates a real table).
• Async population: on a large existing table, creating the index does not backfill it inline. Populate it with the IndexTool MapReduce job (hbase org.apache.phoenix.mapreduce.index.IndexTool) run from the Phoenix server host, then verify the index state is ACTIVE in SYSTEM.CATALOG.
• Risk: every write to the base table now costs an extra write to the index region. On write-heavy tables this is the price of admission.

Limits and common mistakes

• No aggregation or joins in the index. If you need precomputed rollups, maintain a separate summary table yourself (upsert from a scheduled job) — that is the honest Phoenix equivalent of an aggregate materialized view.
• Covering or nothing. If your SELECT references a column not in the index key or INCLUDE list, Phoenix falls back to the data table (or does a slower index-plus-lookup, depending on version and hints). Add columns with INCLUDE rather than recreating the index.
• Global indexes are not transactionally atomic with the base table unless you use transactional tables (OMID/TEPHRA); a failed write can leave the index out of sync, in which case rebuild with IndexTool.
• Hotspotting: re-keying by a low-cardinality or monotonically increasing column (like a date) can concentrate writes on one region. Salt the index (SALT_BUCKETS) or choose a leading key with good distribution.
• Storage cost: each index duplicates the included columns. Check the index table's size in the HBase shell or UI before creating several on one table.

How to check it works

Insert a row into orders, then query active_orders filtering on customer_id and confirm the new row appears — propagation should be immediate, not scheduled. Run EXPLAIN on the query and confirm the plan scans the index table. Finally, list tables in the HBase shell (list) and confirm the index table exists and is enabled. If any of these fail, check the index state in SYSTEM.CATALOG (SELECT INDEX_STATE FROM SYSTEM.CATALOG WHERE INDEX_NAME = 'IDX_ORDERS_BY_CUST') — a non-ACTIVE state usually means the backfill never ran or a write failed.

Review note: any reference you find to a native Phoenix CREATE MATERIALIZED VIEW statement or an phoenix.mv.enable flag should be treated as inaccurate for current Phoenix releases; the view-plus-index pattern above is the supported mechanism.