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