Page History

...

The syntax to create a materialized view in Hive is very similar to the CTAS statement syntax, supporting common features such as partition columns, custom storage handler, or passing table properties.

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
  [DISABLE REWRITE]
  [COMMENT materialized_view_comment]
  [
PARTITIONED ON   [ROW FORMAT row_format(col_name, ...)]
    [STOREDCLUSTERED ASON file_format]
      | STORED (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)]
  [
    [ROW FORMAT row_format]
    [STORED AS file_format]
      | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
AS
<query>;

...

The rewriting algorithm can be enabled and disabled globally using the hive.materializedview.rewriting configuration property and hive.materializedview.rewriting.sql configuration properties (default value is is true). In addition, users can selectively enable/disable materialized views for rewriting. Recall that, by default, materialized views are enabled for rewriting at creation time. To alter that behavior, the following statement can be used:

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;

...

Hive supports two types of rewriting algorithms:

• Algebraic: this is part of Apache Calcite and it supports queries

...

• containing TableScan, Project, Filter, Join, and Aggregate operators. More information about

...

• this rewriting coverage can be

...

• found here. In the following, we include a few examples that briefly illustrate different rewritings.

Example 1

Consider the database schema created by the following DDL statements:

<DDL statements>

Assume we want to obtain frequently information about employees that were hired in different period granularities after 2016 and their departments. We may create the following materialized view:

...

CREATE MATERIALIZED VIEW mv1
AS
SELECT empid, deptname, hire_date
FROM emps JOIN depts
  ON (emps.deptno = depts.deptno)
WHERE hire_date >= '2016-01-01';

CREATE TABLE emps (
  empid INT,
  deptno INT,
  name VARCHAR(256),
  salary FLOAT,
  hire_date TIMESTAMP)
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

CREATE TABLE depts (
  deptno INT,
  deptname VARCHAR(256),
  locationid INT)
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

Assume we want to obtain frequently information about employees that were hired in different period granularities after 2016 and their departments. We may create the following materialized viewThen, the following query extracting information about employees that were hired in Q1 2018 is issued to Hive:

SELECT empidCREATE MATERIALIZED VIEW mv1
AS
SELECT empid, deptname, hire_date
FROM emps
 JOIN depts
  ON (emps.deptno = depts.deptno)
WHERE hire_date >= '20182016-01-01'
    AND hire_date <= '2018-03-31';

Hive will be able to rewrite the incoming query using the materialized view, including a compensation predicate on top of the scan over the materialization. Though the rewriting happens at the algebraic level, to illustrate this example, we include the SQL statement equivalent to the rewriting using the mv used by Hive to answer the incoming queryThen, the following query extracting information about employees that were hired in Q1 2018 is issued to Hive:

SELECT empid, deptname
FROM mv1emps
WHERE JOIN depts
  ON (emps.deptno = depts.deptno)
WHERE hire_date >= '2018-01-01'
    AND hire_date <= '2018-03-31';

...

For the second example, consider the database schema created by the following DDL statementsHive will be able to rewrite the incoming query using the materialized view, including a compensation predicate on top of the scan over the materialization. Though the rewriting happens at the algebraic level, to illustrate this example, we include the SQL statement equivalent to the rewriting using the mv used by Hive to answer the incoming query:

<DDL statements>

As you can observe, we declare multiple integrity constraints for the database, using the RELY keyword so they are visible to the optimizer. Now assume we want to create a materialization that denormalizes the database contents:

...

CREATE MATERIALIZED VIEW mv2
AS
SELECT <dims>,
    lo_revenue,
    lo_extendedprice * lo_discount AS d_price,
    lo_revenue - lo_supplycost
FROM customer, dates, lineorder, part, supplier
WHERE lo_orderdate = d_datekey
    AND lo_partkey = p_partkey
    AND lo_suppkey = s_suppkey
    AND lo_custkey = c_custkey;

SELECT empid, deptname
FROM mv1
WHERE hire_date >= '2018-01-01'
    AND hire_date <= '2018-03-31';

Example 2

For the second example, consider the star schema based on the SSB benchmark created by the following DDL statements:

CREATE TABLE `customer`(
  `c_custkey` BIGINT, 
  `c_name` STRING, 
  `c_address` STRING, 
  `c_city` STRING, 
  `c_nation` STRING, 
  `c_region` STRING, 
  `c_phone` STRING, 
  `c_mktsegment` STRING,
  PRIMARY KEY (`c_custkey`) DISABLE RELY)
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

CREATE TABLE `dates`(
  `d_datekey` BIGINT, 
  `d_date` STRING, 
  `d_dayofweek` STRING, 
  `d_month` STRING, 
  `d_year` INT, 
  `d_yearmonthnum` INT, 
  `d_yearmonth` STRING, 
  `d_daynuminweek` INT,
  `d_daynuminmonth` INT,
  `d_daynuminyear` INT,
  `d_monthnuminyear` INT,
  `d_weeknuminyear` INT,
  `d_sellingseason` STRING,
  `d_lastdayinweekfl` INT,
  `d_lastdayinmonthfl` INT,
  `d_holidayfl` INT,
  `d_weekdayfl`INT,
  PRIMARY KEY (`d_datekey`) DISABLE RELY)
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

CREATE TABLE `part`(
  `p_partkey` BIGINT, 
  `p_name` STRING, 
  `p_mfgr` STRING, 
  `p_category` STRING, 
  `p_brand1` STRING, 
  `p_color` STRING, 
  `p_type` STRING, 
  `p_size` INT, 
  `p_container` STRING,
  PRIMARY KEY (`p_partkey`) DISABLE RELY)
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

CREATE TABLE `supplier`(
  `s_suppkey` BIGINT, 
  `s_name` STRING, 
  `s_address` STRING, 
  `s_city` STRING, 
  `s_nation` STRING, 
  `s_region` STRING, 
  `s_phone` STRING,
  PRIMARY KEY (`s_suppkey`) DISABLE RELY)
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

CREATE TABLE `lineorder`(
  `lo_orderkey` BIGINT, 
  `lo_linenumber` int, 
  `lo_custkey` BIGINT not null DISABLE RELY,
  `lo_partkey` BIGINT not null DISABLE RELY,
  `lo_suppkey` BIGINT not null DISABLE RELY,
  `lo_orderdate` BIGINT not null DISABLE RELY,
  `lo_ordpriority` STRING, 
  `lo_shippriority` STRING, 
  `lo_quantity` DOUBLE, 
  `lo_extendedprice` DOUBLE, 
  `lo_ordtotalprice` DOUBLE, 
  `lo_discount` DOUBLE, 
  `lo_revenue` DOUBLE, 
  `lo_supplycost` DOUBLE, 
  `lo_tax` DOUBLE, 
  `lo_commitdate` BIGINT, 
  `lo_shipmode` STRING,
  PRIMARY KEY (`lo_orderkey`) DISABLE RELY,
  CONSTRAINT fk1 FOREIGN KEY (`lo_custkey`) REFERENCES `customer_n1`(`c_custkey`) DISABLE RELY,
  CONSTRAINT fk2 FOREIGN KEY (`lo_orderdate`) REFERENCES `dates_n0`(`d_datekey`) DISABLE RELY,
  CONSTRAINT fk3 FOREIGN KEY (`lo_partkey`) REFERENCES `ssb_part_n0`(`p_partkey`) DISABLE RELY,
  CONSTRAINT fk4 FOREIGN KEY (`lo_suppkey`) REFERENCES `supplier_n0`(`s_suppkey`) DISABLE RELY)
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

As you can observe, we declare multiple integrity constraints for the database, using the RELY keyword so they are visible to the optimizer. Now assume we want to create a materialization that denormalizes the database contents (consider dims to be the set of dimensions that we will be querying often):

CREATE MATERIALIZED VIEW mv2
AS
SELECT <dims>,
    lo_revenue,
    lo_extendedprice * lo_discount AS d_price,
    lo_revenue - lo_supplycost
FROM customer, dates, lineorder, part, supplier
WHERE lo_orderdate = d_datekey
    AND lo_partkey = p_partkey
    AND lo_suppkey = s_suppkey
    AND lo_custkey = c_custkey;

The materialized view above may accelerate queries that execute joins among the different tables in the database. For instance, consider the following query:

SELECT SUM(lo_extendedprice * lo_discount)
FROM lineorder, dates
WHERE lo_orderdate = d_datekey
  AND d_year = 2013
  AND lo_discount between 1 and 3;

Though the query does not use all tables present in the materialized view, it may be answered using the materialized view because the joins in mv2 preserve all the rows in the lineorder table (we know this because of the integrity constraints). Hence, the materialized view-based rewriting produced by the algorithm would be the following:

SELECT SUM(d_price)
FROM mv2
WHERE d_year = 2013
  AND lo_discount between 1 and 3;

Example 3

For the third example, consider the database schema with a single table that stores the edit events produced by a given website:

CREATE TABLE `wiki` (
  `time` TIMESTAMP, 
  `page` STRING, 
  `user` STRING, 
  `characters_added` BIGINT,
  `characters_removed` BIGINT)
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

For this example, we will use Druid to store the materialized view. Assume we want to execute queries over the table, however we are not interested on any information about the events at a higher time granularity level than a minute. We may create the following materialized view that rolls up the events by the minute:

CREATE MATERIALIZED VIEW mv3
STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler'
AS
SELECT floor(time to minute) as `__time`, page,
    SUM(characters_added) AS c_added,
    SUM(characters_removed) AS c_removed
FROM wiki
GROUP BY floor(time to minute), page;

Then, assume we need to answer the following query that extracts the number of characters added per month

The materialized view above may accelerate queries that execute joins among the different tables in the database. For instance, consider the following query:

...

SELECT SUM(lo_extendedprice * lo_discount)
FROM lineorder, dates
WHERE lo_orderdate = d_datekey
  AND d_year = 2013
  AND lo_discount between 1 and 3;

Though the query does not use all tables present in the materialized view, it may be answered using the materialized view because the joins in mv2 preserve all the rows in the lineorder table (we know this because of the integrity constraints). Hence, the materialized view-based rewriting produced by the algorithm would be the following:

SELECT SUM(d_price)
FROM mv2
WHERE d_year = 2013
  AND lo_discount between 1 and 3;

Example 3

For the third example, consider the database schema with a single table that stores the edit events produced by a given website:

...

<DDL statements>

floor(time to month),
    SUM(characters_added) AS c_added
FROM wiki
GROUP BY floor(time to month);

Hive will be able to rewrite the incoming query using mv3 by rolling up the data of the materialized view to month granularity and projecting the information needed for the query resultFor this example, we will use Druid to store the materialized view. Assume we want to execute queries over the table, however we are not interested on any information about the events at a higher time granularity level than a minute. We may create the following materialized view that rolls up the events by the minute:

CREATE MATERIALIZED VIEW mv3
AS
SELECTSELECT floor(time to month),
    SUM(c_added)
FROM mv3
GROUP BY floor(time to minute), page,
    SUM(characters_added) AS c_added,
    SUM(characters_removed) AS c_removed
FROM wiki
GROUP BY floor(time to minute), page;month);

• SQL text: The materialized view definition query text is compared to the incoming query text or it's subquery text. It supports all kind of operators and aggregate functions.

Materialized view maintenance

When data in the source tables used by a materialized view changes, e.g., new data is inserted or existing data is modified, we will need to refresh the contents of the materialized view to keep it up-to-date with those changes. Currently, the rebuild operation for a materialized view needs to be triggered by the user. In particular, the user should execute the following statementThen, assume we need to answer the following query that extracts the number of characters added per month:

SELECTALTER floor(timeMATERIALIZED to month),
    SUM(characters_added) AS c_added
FROM wiki
GROUP BY floor(time to month);

Hive will be able to rewrite the incoming query using mv3 by rolling up the data of the materialized view to month granularity and projecting the information needed for the query result:

...

SELECT floor(time to month),
    SUM(c_added)
FROM mv3
GROUP BY floor(time to month);

VIEW [db_name.]materialized_view_name REBUILD;

Hive supports incremental view maintenance, i.e., only refresh data that was affected by the changes in the original source tables. Incremental view maintenance will decrease the rebuild step execution time. In addition, it will preserve LLAP cache for existing data in the materialized view.

By default, Hive will attempt to rebuild a materialized view incrementally, falling back to full rebuild if it is not possible.

Current implementation only supports incremental rebuild when there were INSERT operations over the source tables, while UPDATE and DELETE operations will force a full rebuild of the materialized view.

To execute incremental maintenance, following conditions should be met if there were only INSERT operations over the source tables:

• The materialized view should only use transactional tables (the source tables must be transactional), either micromanaged or ACID or a storage format that supports snapshots (ex. Iceberg)
• If the materialized view definition contains a Group By clause, the materialized view should be stored in an ACID table or a storage format that supports snapshots (ex. Iceberg v2), since it needs to support MERGE operation. For materialized view definitions consisting of Scan-Project-Filter-Join, this restriction does not exist.
• If the materialized view definition contains a Group By clause the following aggregate functions are supported: COUNT, SUM, AVG (only if both COUNT and SUM defined for the same column), MIN, MAX

If there were UPDATE and DELETE operations over the source tables:

• The materialized view should only use transactional tables (the source tables must be transactional), either micromanaged or ACID
• The materialized view definition must contain a Group By clause and a COUNT(*) function call.
• The materialized view should be stored in an ACID table or a storage format that supports snapshots (ex. Iceberg v2), since it needs to support MERGE operation.   
• The following aggregate functions are supported: COUNT, SUM, AVG (only if both COUNT and SUM defined for the same column)

A rebuild operation acquires an exclusive write lock over the materialized view, i.e., for a given materialized view, only one rebuild operation can be executed at a given time

Materialized view maintenance

When data in the sources tables used by a materialized view changes, e.g., new data is inserted or existing data is modified, we will need to refresh the contents of the materialized view to keep it up-to-date with those changes. Currently, the rebuild operation for a materialized view needs to be triggered by the user. In particular, the user should execute the following statement:

...

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;

Hive supports incremental view maintenance, i.e., only refresh data that was affected by the changes in the original source tables. Incremental view maintenance will decrease the rebuild step execution time. In addition, it will preserve LLAP cache for existing data in the materialized view. To execute incremental view maintenance, the materialized view should only use transactional tables, either micromanaged or ACID.

By default, Hive will attempt to rebuild a materialized view incrementally, falling back to full rebuild if it is not possible. Current implementation only supports incremental rebuild when there were INSERT operations over the source tables, while UPDATE and DELETE operations will force a full rebuild of the materialized view.

Materialized view lifecycle

...