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

Example

CREATE MATERIALIZED VIEW druid_tSTORED AS 'org.apache.hadoop.hive.druid.DruidStorageHandler'ASSELECT a, b, cFROM src;