CREATE 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 hdfs_path]
   [TBLPROPERTIES (tbl_property_name=tbl_property_value, ...)]
   AS
   <query>;

Required Parameters

query

The query to execute 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 conform to Apache Hive specifications for 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. When not specified, Hive uses the default 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 specifed, Hive uses the default hive.materializedview.serde.

serde_property_value

A value of the SERDEPROPERTIES property.

hdfs_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.

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';