CREATE MATERIALIZED VIEW

If you are familiar with the CREATE TABLE AS SELECT (CTAS) statement, you can quickly master how to create a materialized view.

Syntax

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
   [DISABLE REWRITE]
   [COMMENT materialized_view_comment]
   [PARTITIONED ON (column_name, ...)] 
   [
     [ROW FORMAT row_format]
     [STORED AS file_format]
        | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (serde_property_name=serde_property_value, ...)]
   ]
   [LOCATION file_path]
   [TBLPROPERTIES (tbl_property_name=tbl_property_value, ...)]
   AS
   <query>;

Required Parameters

query
The query to run for results that populate the contents of the materialized view.

Optional Parameters

db_name.materialized_view_name
The database name followed by a name, unique among materialized view names, for the materialized view. The name must be a valid a table name, including case-insensitive alphanumeric and underscore characters.
materialized_view_comment
A string literal enclosed in single quotation marks.
column_name
A key that determines how to do the partitioning, which divides the view of the table into parts.
'storage.handler.class.name'
The name of a storage handler, such as org.apache.hadoop.hive.druid.DruidStorageHandler, that conforms to the Apache Hive specifications for storage handlers in a table definition that uses the STORED BY clause. The default is hive.materializedview.fileformat.
serde_property_name

A property supported by SERDEPROPERTIES that you specify as part of the STORED BY clause. The property is passed to the serde provided by the storage handler. When not specified, Hive uses the default hive.materializedview.serde.

serde_property_value
A value of the SERDEPROPERTIES property.
file_path
The location on the file system for storing the materialized view.
tbl_property_name
A key that conforms to the Apache Hive specification for TBLPROPERTIES keys in a table.
tbl_property_value
The value of a TBLPROPERTIES key.

Usage

The materialized view creation statement meets the criteria of being atomic: it does not return incomplete results. By default, the optimizer uses materialized views to rewrite the query. You can store a materialized view in an external storage system using the STORED AS clause followed by a valid storage handler class name. You can set the DISABLE REWRITE option to alter automatic rewriting of the query at materialized view creation time. The table on which you base the materialized view, src in the example below, must be an ACID, managed table.

Examples

CREATE MATERIALIZED VIEW druid_t
  STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler'
  AS SELECT a, b, c
  FROM src;
CREATE MATERIALIZED VIEW mv4
  LOCATION '/user/csso_max'
  AS SELECT empid, deptname, hire_date
  FROM emps JOIN depts
  ON (emps.deptno = depts.deptno) 
  WHERE hire_date >= '2017-01-01';