Hive Materialized View.

Hive Indexing & Materialized View

Support for creating Index has been removed from the Hive starting from Hive version 3. And if users will try to create indexing in new version then user will get unrecognized input error as shown below.

CREATE INDEX index_test ON TABLE orders(order_id)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
IN TABLE orders_index_test;

FAILED: ParseException line 1:7 cannot recognize input near 'CREATE' 'INDEX' 'index_test' in ddl statement

Since indexing is removed, there are few other option which can be used to replace indexing in Hive.

• Materialized Views with automatic rebuilding/rewriting
• Columnar file formats(e.g ORC, Parquet), as they can do selective data scanning and may skip entire files or blocks to fetch data.

Note: In this tutorial, only Materialized view will be discussed in detail and advantage of Columnar file formats will be discussed in other tutorials later.


Materialized View: Creating materialized view is one of popular way to increase the perfomance of frequently used queries or data summary queries.

• Materialize view will create separate physical sub copy of the data.
• By default, materialized data will be stored in ORC format if data format is not specified during Materialized view creation.
• Materialize views in Hive can be clustered, partitioned (just like Hive tables) and can be stored on a external system using custom storage handler class.
• Hive will be able to automatically rewrite incoming queries using materialized view if optimizer believes it will improve the plan.
• Hive will stop query rewriting if the Materialized view content are stale(not updated from long time).
• Materialize views can be queried directly to avoid dependency on optimizer based query rewriting in Hive.
• By default, users have to trigger rebuild operation for a materialized view if data in the source tables changes.

  
  ALTER MATERIALIZED VIEW [database_name.materialized_view_name] REBUILD;
  

• Configuration parameter hive.materializedview.rewriting.time.window can be used to define the interval after which all the materialized views will be refreshed. This property can also be used as table property while defining Materialized views as well.

  
  SET hive.materializedview.rewriting.time.window=10min;
  

• Users can enable/disable optimizer based query rewriting using below ALTER command but automatic rewriting of materialized view cannot be enabled if materialized view is using non-transactional tables.

  
  ALTER MATERIALIZED VIEW mv_complete_orders ENABLE/DISABLE REWRITE;
  
  FAILED: SemanticException Automatic rewriting for materialized view cannot be enabled if the materialized view uses non-transactional tables
  

➠ Creating a Materialized View:

CREATE MATERIALIZED VIEW mv_complete_orders
AS
SELECT orders_transactional.order_id, order_item_product_id, order_item_subtotal 
FROM orders_transactional JOIN order_items_transactional ON (orders_transactional.order_id = order_items_transactional.order_item_order_id) 
WHERE orders_transactional.order_status='COMPLETE';

➠ Creating a Materialized View Stored in text foramt:

CREATE MATERIALIZED VIEW mv_complete_orders_delimited
ROW FORMAT delimited 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n'
STORED AS textfile
AS
SELECT orders_transactional.order_id, order_item_product_id, order_item_subtotal 
FROM orders_transactional JOIN order_items_transactional ON (orders_transactional.order_id = order_items_transactional.order_item_order_id) 
WHERE orders_transactional.order_status='COMPLETE';

➠ Creating a Materialized View with Auto Rewriting:

CREATE MATERIALIZED VIEW mv_complete_orders_autorewriting
TBLPROPERTIES("hive.materializedview.rewriting.time.window"="10min")
AS
SELECT orders_transactional.order_id, order_item_product_id, order_item_subtotal 
FROM orders_transactional JOIN order_items_transactional ON (orders_transactional.order_id = order_items_transactional.order_item_order_id) 
WHERE orders_transactional.order_status='COMPLETE';

➠ Dropping a Materialized View:

DROP MATERIALIZED VIEW mv_complete_orders;

➠ Selecting Data From a Materialized View:

SELECT order_id, order_item_subtotal, order_item_product_id
FROM mv_complete_orders
WHERE order_item_subtotal BETWEEN 130 AND 160;

order_id	order_item_subtotal	order_item_product_id
68789		150.0			502
68844		150.0			502
68844		150.0			502
68845		150.0			502
68880		149.94			1014
68880		149.94			1014
68883		150.0			502

➠ Explain plan of a query on Materialized View:

EXPLAIN SELECT order_id, order_item_subtotal, order_item_product_id
FROM mv_complete_orders
WHERE order_item_subtotal BETWEEN 130 AND 160;

OK
STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: mv_complete_orders
          Statistics: Num rows: 90 Data size: 1080 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: order_item_subtotal BETWEEN 130 AND 160 (type: boolean)
            Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: order_id (type: int), order_item_subtotal (type: float), order_item_product_id (type: int)
              outputColumnNames: _col0, _col1, _col2
              Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
              ListSink