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

db_name.materialized_view_name

The database name followed by a name, unique among materialized view names, for the materialized view in dot notation. 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 and 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 HDFS file system for storing the materialized view.

tbl_property_name

A key that conforms to the Apache Hive specification to TBLPROPERTIES keys in a table.

tbl_property_value

The value of a TBLPROPERTIES key.

query

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

Description

The materialized view creation statement is atomic (not visible unitl all results are populated). 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;