cgivre commented on code in PR #3036:
URL: https://github.com/apache/drill/pull/3036#discussion_r2775314545
##########
docs/dev/MaterializedViews.md:
##########
@@ -0,0 +1,388 @@
+# Materialized Views
+
+Materialized views in Apache Drill provide a mechanism to store pre-computed
query results for improved query performance. Unlike regular views which are
virtual and execute the underlying query each time they are accessed,
materialized views persist the query results as physical data that can be
queried directly.
+
+## Overview
+
+Materialized views are useful for:
+- Accelerating frequently executed queries with complex aggregations or joins
+- Reducing compute resources for repetitive analytical workloads
+- Providing consistent snapshots of data at a point in time
+
+Drill's materialized view implementation includes:
+- SQL syntax for creating, dropping, and refreshing materialized views
+- Automatic query rewriting using Calcite's SubstitutionVisitor
+- Integration with Drill Metastore for centralized metadata management
+- Parquet-based data storage for efficient columnar access
+
+## SQL Syntax
+
+### CREATE MATERIALIZED VIEW
+
+```sql
+CREATE MATERIALIZED VIEW [schema.]view_name AS select_statement
+CREATE OR REPLACE MATERIALIZED VIEW [schema.]view_name AS select_statement
+CREATE MATERIALIZED VIEW IF NOT EXISTS [schema.]view_name AS select_statement
+```
+
+Examples:
+
+```sql
+-- Create a materialized view with aggregations
+CREATE MATERIALIZED VIEW dfs.tmp.sales_summary AS
+SELECT region, product_category, SUM(amount) as total_sales, COUNT(*) as
num_transactions
+FROM dfs.`/data/sales`
+GROUP BY region, product_category;
+
+-- Create or replace an existing materialized view
+CREATE OR REPLACE MATERIALIZED VIEW dfs.tmp.customer_stats AS
+SELECT customer_id, COUNT(*) as order_count, AVG(order_total) as avg_order
+FROM dfs.`/data/orders`
+GROUP BY customer_id;
+
+-- Create only if it doesn't exist
+CREATE MATERIALIZED VIEW IF NOT EXISTS dfs.tmp.daily_metrics AS
+SELECT date_col, SUM(value) as daily_total
+FROM dfs.`/data/metrics`
+GROUP BY date_col;
+```
+
+### DROP MATERIALIZED VIEW
+
+```sql
+DROP MATERIALIZED VIEW [schema.]view_name
+DROP MATERIALIZED VIEW IF EXISTS [schema.]view_name
+```
+
+Examples:
+
+```sql
+-- Drop a materialized view (error if not exists)
+DROP MATERIALIZED VIEW dfs.tmp.sales_summary;
+
+-- Drop only if it exists (no error if not exists)
+DROP MATERIALIZED VIEW IF EXISTS dfs.tmp.old_view;
+```
+
+### REFRESH MATERIALIZED VIEW
+
+```sql
+REFRESH MATERIALIZED VIEW [schema.]view_name
+```
+
+The REFRESH command re-executes the underlying query and replaces the stored
data with fresh results.
+
+Example:
+
+```sql
+-- Refresh the materialized view with current data
+REFRESH MATERIALIZED VIEW dfs.tmp.sales_summary;
+```
+
+## Query Rewriting
+
+Drill supports automatic query rewriting where queries against base tables can
be transparently rewritten to use materialized views when appropriate. This
feature leverages Apache Calcite's SubstitutionVisitor for structural query
matching.
+
+### Enabling Query Rewriting
+
+Query rewriting is controlled by the
`planner.enable_materialized_view_rewrite` option:
+
+```sql
+-- Enable materialized view rewriting (enabled by default)
+SET `planner.enable_materialized_view_rewrite` = true;
+
+-- Disable materialized view rewriting
+SET `planner.enable_materialized_view_rewrite` = false;
+```
+
+### How Rewriting Works
+
+When query rewriting is enabled, Drill's query planner:
+
+1. Discovers all available materialized views in accessible schemas
+2. Filters candidates to those with COMPLETE refresh status
+3. For each candidate, parses the MV's defining SQL and converts it to a
relational expression
+4. Uses Calcite's SubstitutionVisitor to check if the MV's query structure
matches part or all of the user's query
+5. If a match is found, substitutes the matching portion with a scan of the
materialized view data
+6. Selects the rewritten plan if it offers better performance characteristics
+
+### Rewriting Scenarios
+
+Query rewriting can apply in several scenarios:
+
+**Exact Match**: The user's query exactly matches the MV definition.
+
+```sql
+-- MV definition
+CREATE MATERIALIZED VIEW dfs.tmp.region_totals AS
+SELECT r_regionkey, COUNT(*) as cnt FROM cp.`region.json` GROUP BY r_regionkey;
+
+-- This query will use the MV
+SELECT r_regionkey, COUNT(*) as cnt FROM cp.`region.json` GROUP BY r_regionkey;
+```
+
+**Partial Match with Additional Filters**: The user's query adds filters on
top of the MV.
+
+```sql
+-- This query may use the MV and apply the filter
+SELECT r_regionkey, cnt FROM dfs.tmp.region_totals WHERE cnt > 10;
+```
+
+**Aggregate Rollup**: Higher-level aggregations computed from MV aggregates.
+
+### Viewing the Execution Plan
+
+Use EXPLAIN to see if a materialized view is being used:
+
+```sql
+EXPLAIN PLAN FOR
+SELECT r_regionkey, COUNT(*) FROM cp.`region.json` GROUP BY r_regionkey;
+```
+
+If the MV is used, the plan will show a scan of the materialized view data
location rather than the original table.
+
+## Storage Architecture
+
+### Definition Storage
+
+Materialized view definitions are stored as JSON files with the
`.materialized_view.drill` extension in the workspace directory. This follows
the same pattern as regular Drill views (`.view.drill` files).
+
+The definition file contains:
+- View name
+- Defining SQL statement
+- Field names and types
+- Workspace schema path
+- Data storage path
+- Last refresh timestamp
+- Refresh status (PENDING or COMPLETE)
+
+Example definition file structure:
+
+```json
+{
+ "name": "sales_summary",
+ "sql": "SELECT region, SUM(amount) as total FROM sales GROUP BY region",
+ "fields": [
+ {"name": "region", "type": "VARCHAR"},
+ {"name": "total", "type": "DOUBLE"}
+ ],
+ "workspaceSchemaPath": ["dfs", "tmp"],
+ "dataStoragePath": "sales_summary",
+ "lastRefreshTime": 1706900000000,
+ "refreshStatus": "COMPLETE"
+}
+```
+
+### Data Storage
+
+Materialized view data is stored as Parquet files in a directory named
`{view_name}_mv_data` within the workspace. Parquet format provides:
+- Efficient columnar storage
+- Compression
+- Predicate pushdown support
+- Schema evolution capabilities
+
+For a materialized view named `sales_summary` in `dfs.tmp`, the storage
structure would be:
+
+```
+/tmp/
+ sales_summary.materialized_view.drill # Definition file
+ sales_summary_mv_data/ # Data directory
+ 0_0_0.parquet # Data files
+ 0_0_1.parquet
+ ...
Review Comment:
Good idea!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
