You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by fr...@apache.org on 2019/01/09 22:21:12 UTC
svn commit: r1850910 [10/25] - in /calcite/site: ./ assets/ assets/css/
community/ css/ develop/ docs/ docs/api/ docs/testapi/ downloads/ fonts/
img/ js/ news/ news/2014/ news/2014/06/ news/2014/06/27/
news/2014/06/27/release-0.8.0-incubating/ news/201...
Added: calcite/site/docs/lattice.html
URL: http://svn.apache.org/viewvc/calcite/site/docs/lattice.html?rev=1850910&view=auto
==============================================================================
--- calcite/site/docs/lattice.html (added)
+++ calcite/site/docs/lattice.html Wed Jan 9 22:21:11 2019
@@ -0,0 +1,782 @@
+<!DOCTYPE HTML>
+<html lang="en-US">
+<head>
+ <meta charset="UTF-8">
+ <title>Lattices</title>
+ <meta name="viewport" content="width=device-width,initial-scale=1">
+ <meta name="generator" content="Jekyll v3.7.3">
+ <link rel="stylesheet" href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic,900">
+ <link rel="stylesheet" href="/css/screen.css">
+ <link rel="icon" type="image/x-icon" href="/favicon.ico">
+ <!--[if lt IE 9]>
+ <script src="/js/html5shiv.min.js"></script>
+ <script src="/js/respond.min.js"></script>
+ <![endif]-->
+</head>
+
+
+<body class="wrap">
+ <header role="banner">
+ <nav class="mobile-nav show-on-mobiles">
+ <ul>
+ <li class="">
+ <a href="/">Home</a>
+ </li>
+ <li class="">
+ <a href="/downloads/">Download</a>
+ </li>
+ <li class="">
+ <a href="/community/">Community</a>
+ </li>
+ <li class="">
+ <a href="/develop/">Develop</a>
+ </li>
+ <li class="">
+ <a href="/news/">News</a>
+ </li>
+ <li class="current">
+ <a href="/docs/">Docs</a>
+ </li>
+</ul>
+
+ </nav>
+ <div class="grid">
+ <div class="unit one-third center-on-mobiles">
+ <h1>
+ <a href="/">
+ <span class="sr-only">Apache Calcite</span>
+ <img src="/img/logo.png" width="226" height="140" alt="Calcite Logo">
+ </a>
+ </h1>
+ </div>
+ <nav class="main-nav unit two-thirds hide-on-mobiles">
+ <ul>
+ <li class="">
+ <a href="/">Home</a>
+ </li>
+ <li class="">
+ <a href="/downloads/">Download</a>
+ </li>
+ <li class="">
+ <a href="/community/">Community</a>
+ </li>
+ <li class="">
+ <a href="/develop/">Develop</a>
+ </li>
+ <li class="">
+ <a href="/news/">News</a>
+ </li>
+ <li class="current">
+ <a href="/docs/">Docs</a>
+ </li>
+</ul>
+
+ </nav>
+ </div>
+</header>
+
+
+ <section class="docs">
+ <div class="grid">
+
+ <div class="docs-nav-mobile unit whole show-on-mobiles">
+ <select onchange="if (this.value) window.location.href=this.value">
+ <option value="">Navigate the docsâ¦</option>
+ <optgroup label="Overview">
+ </optgroup>
+ <optgroup label="Advanced">
+ </optgroup>
+ <optgroup label="Avatica">
+ </optgroup>
+ <optgroup label="Reference">
+ </optgroup>
+ <optgroup label="Meta">
+ </optgroup>
+
+ </select>
+</div>
+
+
+ <div class="unit four-fifths">
+ <article>
+ <h1>Lattices</h1>
+ <!--
+
+-->
+
+<p>A lattice is a framework for creating and populating materialized views,
+and for recognizing that a materialized view can be used to solve a
+particular query.</p>
+
+<ul id="markdown-toc">
+ <li><a href="#concept" id="markdown-toc-concept">Concept</a></li>
+ <li><a href="#demonstration" id="markdown-toc-demonstration">Demonstration</a></li>
+ <li><a href="#statistics" id="markdown-toc-statistics">Statistics</a></li>
+ <li><a href="#lattice-suggester" id="markdown-toc-lattice-suggester">Lattice suggester</a></li>
+ <li><a href="#further-directions" id="markdown-toc-further-directions">Further directions</a></li>
+ <li><a href="#references" id="markdown-toc-references">References</a></li>
+</ul>
+
+<h2 id="concept">Concept</h2>
+
+<p>A lattice represents a star (or snowflake) schema, not a general
+schema. In particular, all relationships must be many-to-one, heading
+from a fact table at the center of the star.</p>
+
+<p>The name derives from the mathematics: a
+<a href="https://en.wikipedia.org/wiki/Lattice_(order)">lattice</a>
+is a
+<a href="https://en.wikipedia.org/wiki/Partially_ordered_set">partially
+ordered set</a> where any two elements have a unique greatest lower
+bound and least upper bound.</p>
+
+<p>[<a href="#ref-hru96">HRU96</a>] observed that the set of possible
+materializations of a data cube forms a lattice, and presented an
+algorithm to choose a good set of materializations. Calciteâs
+recommendation algorithm is derived from this.</p>
+
+<p>The lattice definition uses a SQL statement to represent the star. SQL
+is a useful short-hand to represent several tables joined together,
+and assigning aliases to the column names (it more convenient than
+inventing a new language to represent relationships, join conditions
+and cardinalities).</p>
+
+<p>Unlike regular SQL, order is important. If you put A before B in the
+FROM clause, and make a join between A and B, you are saying that
+there is a many-to-one foreign key relationship from A to B. (E.g. in
+the example lattice, the Sales fact table occurs before the Time
+dimension table, and before the Product dimension table. The Product
+dimension table occurs before the ProductClass outer dimension table,
+further down an arm of a snowflake.)</p>
+
+<p>A lattice implies constraints. In the A to B relationship, there is a
+foreign key on A (i.e. every value of Aâs foreign key has a
+corresponding value in Bâs key), and a unique key on B (i.e. no key
+value occurs more than once). These constraints are really important,
+because it allows the planner to remove joins to tables whose columns
+are not being used, and know that the query results will not change.</p>
+
+<p>Calcite does not check these constraints. If they are violated,
+Calcite will return wrong results.</p>
+
+<p>A lattice is a big, virtual join view. It is not materialized (it
+would be several times larger than the star schema, because of
+denormalization) and you probably wouldnât want to query it (far too
+many columns). So what is it useful for? As we said above, (a) the
+lattice declares some very useful primary and foreign key constraints,
+(b) it helps the query planner map user queries onto
+filter-join-aggregate materialized views (the most useful kind of
+materialized view for DW queries), (c) gives Calcite a framework
+within which to gather stats about data volumes and user queries, (d)
+allows Calcite to automatically design and populate materialized
+views.</p>
+
+<p>Most star schema models force you to choose whether a column is a
+dimension or a measure. In a lattice, every column is a dimension
+column. (That is, it can become one of the columns in the GROUP BY clause
+to query the star schema at a particular dimensionality). Any column
+can also be used in a measure; you define measures by giving the
+column and an aggregate function.</p>
+
+<p>If âunit_salesâ tends to be used much more often as a measure rather
+than a dimension, thatâs fine. Calciteâs algorithm should notice that
+it is rarely aggregated, and not be inclined to create tiles that
+aggregate on it. (By âshouldâ I mean âcould and one day willâ. The
+algorithm does not currently take query history into account when
+designing tiles.)</p>
+
+<p>But someone might want to know whether orders with fewer than 5 items
+were more or less profitable than orders with more than 100. All of a
+sudden, âunit_salesâ has become a dimension. If thereâs virtually zero
+cost to declaring a column a dimension column, I figured letâs make
+them all dimension columns.</p>
+
+<p>The model allows for a particular table to be used more than once,
+with a different table alias. You could use this to model say
+OrderDate and ShipDate, with two uses to the Time dimension table.</p>
+
+<p>Most SQL systems require that the column names in a view are unique.
+This is hard to achieve in a lattice, because you often include
+primary and foreign key columns in a join. So Calcite lets you refer
+to columns in two ways. If the column is unique, you can use its name,
+[âunit_salesâ]. Whether or not it is unique in the lattice, it will be
+unique in its table, so you can use it qualified by its table alias.
+Examples:</p>
+
+<ul>
+ <li>[âsalesâ, âunit_salesâ]</li>
+ <li>[âship_dateâ, âtime_idâ]</li>
+ <li>[âorder_dateâ, âtime_idâ]</li>
+</ul>
+
+<p>A âtileâ is a materialized table in a lattice, with a particular
+dimensionality. The âtilesâ attribute
+of the <a href="/docs/model.html#lattice">lattice JSON element</a>
+defines an initial set of tiles to materialize.</p>
+
+<h2 id="demonstration">Demonstration</h2>
+
+<p>Create a model that includes a lattice:</p>
+
+<figure class="highlight"><pre><code class="language-json" data-lang="json"><span class="p">{</span><span class="w">
+ </span><span class="s2">"version"</span><span class="p">:</span><span class="w"> </span><span class="s2">"1.0"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"defaultSchema"</span><span class="p">:</span><span class="w"> </span><span class="s2">"foodmart"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"schemas"</span><span class="p">:</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="p">{</span><span class="w">
+ </span><span class="s2">"type"</span><span class="p">:</span><span class="w"> </span><span class="s2">"jdbc"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"name"</span><span class="p">:</span><span class="w"> </span><span class="s2">"foodmart"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"jdbcUser"</span><span class="p">:</span><span class="w"> </span><span class="s2">"FOODMART"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"jdbcPassword"</span><span class="p">:</span><span class="w"> </span><span class="s2">"FOODMART"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"jdbcUrl"</span><span class="p">:</span><span class="w"> </span><span class="s2">"jdbc:hsqldb:res:foodmart"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"jdbcSchema"</span><span class="p">:</span><span class="w"> </span><span class="s2">"foodmart"</span><span class="w">
+ </span><span class="p">},</span><span class="w">
+ </span><span class="p">{</span><span class="w">
+ </span><span class="s2">"name"</span><span class="p">:</span><span class="w"> </span><span class="s2">"adhoc"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"lattices"</span><span class="p">:</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="p">{</span><span class="w">
+ </span><span class="s2">"name"</span><span class="p">:</span><span class="w"> </span><span class="s2">"star"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"sql"</span><span class="p">:</span><span class="w"> </span><span class="p">[</span><span class="w">
+ </span><span class="s2">"select 1 from </span><span class="se">\"</span><span class="s2">foodmart</span><span class="se">\"</span><span class="s2">.</span><span class="se">\"</span><span class="s2">sales_fact_1997</span><span class="se">\"</span><span class="s2"> as </span><span class="se">\"</span><span class="s2">s</span><span class="se">\"</span><span class="s2">"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"join </span><span class="se">\"</span><span class="s2">foodmart</span><span class="se">\"</span><span class="s2">.</span><span class="se">\"</span><span class="s2">product</span><span class="se">\"</span><span class="s2"> as </span><span class="se">\"</span><span class="s2">p</span><span class="se">\"</span><span class="s2"> using (</span><span class="se">\"</span><span class="s2">product_id</span><span class="se">\"</span><span class="s2">)"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"join </span><span class="se">\"</span><span class="s2">foodmart</span><span class="se">\"</span><span class="s2">.</span><span class="se">\"</span><span class="s2">time_by_day</span><span class="se">\"</span><span class="s2"> as </span><span class="se">\"</span><span class="s2">t</span><span class="se">\"</span><span class="s2"> using (</span><span class="se">\"</span><span class="s2">time_id</span><span class="se">\"</span><span class="s2">)"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"join </span><span class="se">\"</span><span class="s2">foodmart</span><span class="se">\"</span><span class="s2">.</span><span class="se">\"</span><span class="s2">product_class</span><span class="se">\"</span><span class="s2"> as </span><span class="se">\"</span><span class="s2">pc</span><span class="se">\"</span><span class="s2"> on </span><span class="se">\"</span><span class="s2">p</span><span class="se">\"</span><span class="s2">.</span><span class="se">\"</span><span class="s2">product_class_id</span><span class="se">\"</span><span class="s2"> = </span><span class="se">\"</span><span class="s2">pc</span><span class="se">\"</span><span class="s2">.</span><span class="se">\"</span><span class="s2">product_class_id</span><span class="se">\"</span><span class="s2">"</span><span class="w">
+ </span><span class="p">],</span><span class="w">
+ </span><span class="s2">"auto"</span><span class="p">:</span><span class="w"> </span><span class="kc">true</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"algorithm"</span><span class="p">:</span><span class="w"> </span><span class="kc">true</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"rowCountEstimate"</span><span class="p">:</span><span class="w"> </span><span class="mi">86837</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"defaultMeasures"</span><span class="p">:</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="p">{</span><span class="w">
+ </span><span class="s2">"agg"</span><span class="p">:</span><span class="w"> </span><span class="s2">"count"</span><span class="w">
+ </span><span class="p">}</span><span class="w"> </span><span class="p">]</span><span class="w">
+ </span><span class="p">}</span><span class="w"> </span><span class="p">]</span><span class="w">
+ </span><span class="p">}</span><span class="w"> </span><span class="p">]</span><span class="w">
+</span><span class="p">}</span></code></pre></figure>
+
+<p>This is a cut-down version of
+<a href="https://github.com/apache/calcite/blob/master/core/src/test/resources/hsqldb-foodmart-lattice-model.json">hsqldb-foodmart-lattice-model.json</a>
+that does not include the âtilesâ attribute, because we are going to generate
+tiles automatically. Letâs log into sqlline and connect to this schema:</p>
+
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="err">$</span> <span class="n">sqlline</span> <span class="k">version</span> <span class="mi">1</span><span class="p">.</span><span class="mi">3</span><span class="p">.</span><span class="mi">0</span>
+<span class="n">sqlline</span><span class="o">></span> <span class="o">!</span><span class="k">connect</span> <span class="n">jdbc</span><span class="p">:</span><span class="n">calcite</span><span class="p">:</span><span class="n">model</span><span class="o">=</span><span class="n">core</span><span class="o">/</span><span class="n">src</span><span class="o">/</span><span class="n">test</span><span class="o">/</span><span class="n">resources</span><span class="o">/</span><span class="n">hsqldb</span><span class="o">-</span><span class="n">foodmart</span><span class="o">-</span><span class="n">lattice</span><span class="o">-</span><span class="n">model</span><span class="p">.</span><span class="n">json</span> <span class="nv">"sa"</span> <span class="nv">""</span></code></pre></figure>
+
+<p>Youâll notice that it takes a few seconds to connect.
+Calcite is running the optimization algorithm, and creating and
+populating materialized views. Letâs run a query and check out its plan:</p>
+
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">sqlline</span><span class="o">></span> <span class="k">select</span> <span class="nv">"the_year"</span><span class="p">,</span><span class="nv">"the_month"</span><span class="p">,</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">as</span> <span class="k">c</span>
+<span class="p">.</span> <span class="p">.</span> <span class="p">.</span> <span class="p">.</span><span class="o">></span> <span class="k">from</span> <span class="nv">"sales_fact_1997"</span>
+<span class="p">.</span> <span class="p">.</span> <span class="p">.</span> <span class="p">.</span><span class="o">></span> <span class="k">join</span> <span class="nv">"time_by_day"</span> <span class="k">using</span> <span class="p">(</span><span class="nv">"time_id"</span><span class="p">)</span>
+<span class="p">.</span> <span class="p">.</span> <span class="p">.</span> <span class="p">.</span><span class="o">></span> <span class="k">group</span> <span class="k">by</span> <span class="nv">"the_year"</span><span class="p">,</span><span class="nv">"the_month"</span><span class="p">;</span>
+<span class="o">+</span><span class="c1">----------+-----------+------+</span>
+<span class="o">|</span> <span class="n">the_year</span> <span class="o">|</span> <span class="n">the_month</span> <span class="o">|</span> <span class="k">C</span> <span class="o">|</span>
+<span class="o">+</span><span class="c1">----------+-----------+------+</span>
+<span class="o">|</span> <span class="mi">1997</span> <span class="o">|</span> <span class="n">September</span> <span class="o">|</span> <span class="mi">6663</span> <span class="o">|</span>
+<span class="o">|</span> <span class="mi">1997</span> <span class="o">|</span> <span class="n">April</span> <span class="o">|</span> <span class="mi">6590</span> <span class="o">|</span>
+<span class="o">|</span> <span class="mi">1997</span> <span class="o">|</span> <span class="n">January</span> <span class="o">|</span> <span class="mi">7034</span> <span class="o">|</span>
+<span class="o">|</span> <span class="mi">1997</span> <span class="o">|</span> <span class="n">June</span> <span class="o">|</span> <span class="mi">6912</span> <span class="o">|</span>
+<span class="o">|</span> <span class="mi">1997</span> <span class="o">|</span> <span class="n">August</span> <span class="o">|</span> <span class="mi">7038</span> <span class="o">|</span>
+<span class="o">|</span> <span class="mi">1997</span> <span class="o">|</span> <span class="n">February</span> <span class="o">|</span> <span class="mi">6844</span> <span class="o">|</span>
+<span class="o">|</span> <span class="mi">1997</span> <span class="o">|</span> <span class="n">March</span> <span class="o">|</span> <span class="mi">7710</span> <span class="o">|</span>
+<span class="o">|</span> <span class="mi">1997</span> <span class="o">|</span> <span class="n">October</span> <span class="o">|</span> <span class="mi">6479</span> <span class="o">|</span>
+<span class="o">|</span> <span class="mi">1997</span> <span class="o">|</span> <span class="n">May</span> <span class="o">|</span> <span class="mi">6866</span> <span class="o">|</span>
+<span class="o">|</span> <span class="mi">1997</span> <span class="o">|</span> <span class="n">December</span> <span class="o">|</span> <span class="mi">8717</span> <span class="o">|</span>
+<span class="o">|</span> <span class="mi">1997</span> <span class="o">|</span> <span class="n">July</span> <span class="o">|</span> <span class="mi">7752</span> <span class="o">|</span>
+<span class="o">|</span> <span class="mi">1997</span> <span class="o">|</span> <span class="n">November</span> <span class="o">|</span> <span class="mi">8232</span> <span class="o">|</span>
+<span class="o">+</span><span class="c1">----------+-----------+------+</span>
+<span class="mi">12</span> <span class="k">rows</span> <span class="n">selected</span> <span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">147</span> <span class="n">seconds</span><span class="p">)</span>
+
+<span class="n">sqlline</span><span class="o">></span> <span class="k">explain</span> <span class="n">plan</span> <span class="k">for</span>
+<span class="p">.</span> <span class="p">.</span> <span class="p">.</span> <span class="p">.</span><span class="o">></span> <span class="k">select</span> <span class="nv">"the_year"</span><span class="p">,</span><span class="nv">"the_month"</span><span class="p">,</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">as</span> <span class="k">c</span>
+<span class="p">.</span> <span class="p">.</span> <span class="p">.</span> <span class="p">.</span><span class="o">></span> <span class="k">from</span> <span class="nv">"sales_fact_1997"</span>
+<span class="p">.</span> <span class="p">.</span> <span class="p">.</span> <span class="p">.</span><span class="o">></span> <span class="k">join</span> <span class="nv">"time_by_day"</span> <span class="k">using</span> <span class="p">(</span><span class="nv">"time_id"</span><span class="p">)</span>
+<span class="p">.</span> <span class="p">.</span> <span class="p">.</span> <span class="p">.</span><span class="o">></span> <span class="k">group</span> <span class="k">by</span> <span class="nv">"the_year"</span><span class="p">,</span><span class="nv">"the_month"</span><span class="p">;</span>
+<span class="o">+</span><span class="c1">--------------------------------------------------------------------------------+</span>
+<span class="o">|</span> <span class="n">PLAN</span> <span class="o">|</span>
+<span class="o">+</span><span class="c1">--------------------------------------------------------------------------------+</span>
+<span class="o">|</span> <span class="n">EnumerableCalc</span><span class="p">(</span><span class="n">expr</span><span class="o">#</span><span class="mi">0</span><span class="p">..</span><span class="mi">2</span><span class="o">=</span><span class="p">[</span><span class="err">{</span><span class="n">inputs</span><span class="err">}</span><span class="p">],</span> <span class="n">the_year</span><span class="o">=</span><span class="p">[</span><span class="err">$</span><span class="n">t1</span><span class="p">],</span> <span class="n">the_month</span><span class="o">=</span><span class="p">[</span><span class="err">$</span><span class="n">t0</span><span class="p">],</span> <span class="k">C</span><span class="o">=</span><span class="p">[</span><span class="err">$</span><span class="n">t2</span><span class="p">])</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">EnumerableAggregate</span><span class="p">(</span><span class="k">group</span><span class="o">=</span><span class="p">[</span><span class="err">{</span><span class="mi">3</span><span class="p">,</span> <span class="mi">4</span><span class="err">}</span><span class="p">],</span> <span class="k">C</span><span class="o">=</span><span class="p">[</span><span class="err">$</span><span class="n">SUM0</span><span class="p">(</span><span class="err">$</span><span class="mi">7</span><span class="p">)])</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">EnumerableTableScan</span><span class="p">(</span><span class="k">table</span><span class="o">=</span><span class="p">[[</span><span class="n">adhoc</span><span class="p">,</span> <span class="n">m</span><span class="err">{</span><span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">31</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">36</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span><span class="p">]])</span> <span class="o">|</span>
+<span class="o">+</span><span class="c1">--------------------------------------------------------------------------------+</span></code></pre></figure>
+
+<p>The query gives the right answer, but plan is somewhat surprising.
+It doesnât read the <code class="highlighter-rouge">sales_fact_1997</code> or <code class="highlighter-rouge">time_by_day</code> tables, but instead
+reads from a table called <code class="highlighter-rouge">m{16, 17, 27, 31, 32, 36, 37}</code>. This is one of the
+tiles created at the start of the connection.</p>
+
+<p>Itâs a real table, and you can even query it directly. It has only 120 rows,
+so is a more efficient way to answer the query:</p>
+
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">sqlline</span><span class="o">></span> <span class="o">!</span><span class="k">describe</span> <span class="nv">"adhoc"</span><span class="p">.</span><span class="nv">"m{16, 17, 27, 31, 32, 36, 37}"</span>
+<span class="o">+</span><span class="c1">-------------+-------------------------------+--------------------+-----------+-----------------+</span>
+<span class="o">|</span> <span class="n">TABLE_SCHEM</span> <span class="o">|</span> <span class="k">TABLE_NAME</span> <span class="o">|</span> <span class="k">COLUMN_NAME</span> <span class="o">|</span> <span class="n">DATA_TYPE</span> <span class="o">|</span> <span class="n">TYPE_NAME</span> <span class="o">|</span>
+<span class="o">+</span><span class="c1">-------------+-------------------------------+--------------------+-----------+-----------------+</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">31</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">36</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="n">recyclable_package</span> <span class="o">|</span> <span class="mi">16</span> <span class="o">|</span> <span class="n">BOOLEAN</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">31</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">36</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="n">low_fat</span> <span class="o">|</span> <span class="mi">16</span> <span class="o">|</span> <span class="n">BOOLEAN</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">31</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">36</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="n">product_family</span> <span class="o">|</span> <span class="mi">12</span> <span class="o">|</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">30</span><span class="p">)</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">31</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">36</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="n">the_month</span> <span class="o">|</span> <span class="mi">12</span> <span class="o">|</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">30</span><span class="p">)</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">31</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">36</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="n">the_year</span> <span class="o">|</span> <span class="mi">5</span> <span class="o">|</span> <span class="n">SMALLINT</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">31</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">36</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="n">quarter</span> <span class="o">|</span> <span class="mi">12</span> <span class="o">|</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">30</span><span class="p">)</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">31</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">36</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="n">fiscal_period</span> <span class="o">|</span> <span class="mi">12</span> <span class="o">|</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">30</span><span class="p">)</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">31</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">36</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="n">m0</span> <span class="o">|</span> <span class="o">-</span><span class="mi">5</span> <span class="o">|</span> <span class="n">BIGINT</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="o">|</span>
+<span class="o">+</span><span class="c1">-------------+-------------------------------+--------------------+-----------+-----------------+</span>
+
+<span class="n">sqlline</span><span class="o">></span> <span class="k">select</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">as</span> <span class="k">c</span>
+<span class="p">.</span> <span class="p">.</span> <span class="p">.</span> <span class="p">.</span><span class="o">></span> <span class="k">from</span> <span class="nv">"adhoc"</span><span class="p">.</span><span class="nv">"m{16, 17, 27, 31, 32, 36, 37}"</span><span class="p">;</span>
+<span class="o">+</span><span class="c1">-----+</span>
+<span class="o">|</span> <span class="k">C</span> <span class="o">|</span>
+<span class="o">+</span><span class="c1">-----+</span>
+<span class="o">|</span> <span class="mi">120</span> <span class="o">|</span>
+<span class="o">+</span><span class="c1">-----+</span>
+<span class="mi">1</span> <span class="k">row</span> <span class="n">selected</span> <span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">12</span> <span class="n">seconds</span><span class="p">)</span></code></pre></figure>
+
+<p>Letâs list the tables, and you will see several more tiles. There are also
+tables of the <code class="highlighter-rouge">foodmart</code> schema, and the system tables <code class="highlighter-rouge">TABLES</code> and <code class="highlighter-rouge">COLUMNS</code>,
+and the lattice itself, which appears as a table called <code class="highlighter-rouge">star</code>.</p>
+
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">sqlline</span><span class="o">></span> <span class="o">!</span><span class="n">tables</span>
+<span class="o">+</span><span class="c1">-------------+-------------------------------+--------------+</span>
+<span class="o">|</span> <span class="n">TABLE_SCHEM</span> <span class="o">|</span> <span class="k">TABLE_NAME</span> <span class="o">|</span> <span class="n">TABLE_TYPE</span> <span class="o">|</span>
+<span class="o">+</span><span class="c1">-------------+-------------------------------+--------------+</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">18</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">19</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">36</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">4</span><span class="p">,</span> <span class="mi">7</span><span class="p">,</span> <span class="mi">16</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">4</span><span class="p">,</span> <span class="mi">7</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">7</span><span class="p">,</span> <span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">19</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">m</span><span class="err">{</span><span class="mi">7</span><span class="p">,</span> <span class="mi">16</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">27</span><span class="p">,</span> <span class="mi">30</span><span class="p">,</span> <span class="mi">32</span><span class="p">,</span> <span class="mi">37</span><span class="err">}</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">adhoc</span> <span class="o">|</span> <span class="n">star</span> <span class="o">|</span> <span class="n">STAR</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">foodmart</span> <span class="o">|</span> <span class="n">customer</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">foodmart</span> <span class="o">|</span> <span class="n">product</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">foodmart</span> <span class="o">|</span> <span class="n">product_class</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">foodmart</span> <span class="o">|</span> <span class="n">promotion</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">foodmart</span> <span class="o">|</span> <span class="n">region</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">foodmart</span> <span class="o">|</span> <span class="n">sales_fact_1997</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">foodmart</span> <span class="o">|</span> <span class="n">store</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">foodmart</span> <span class="o">|</span> <span class="n">time_by_day</span> <span class="o">|</span> <span class="k">TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">metadata</span> <span class="o">|</span> <span class="n">COLUMNS</span> <span class="o">|</span> <span class="n">SYSTEM_TABLE</span> <span class="o">|</span>
+<span class="o">|</span> <span class="n">metadata</span> <span class="o">|</span> <span class="n">TABLES</span> <span class="o">|</span> <span class="n">SYSTEM_TABLE</span> <span class="o">|</span>
+<span class="o">+</span><span class="c1">-------------+-------------------------------+--------------+</span></code></pre></figure>
+
+<h2 id="statistics">Statistics</h2>
+
+<p>The algorithm that chooses which tiles of a lattice to materialize depends on
+a lot of statistics. It needs to know <code class="highlighter-rouge">select count(distinct a, b, c) from star</code>
+for each combination of columns (<code class="highlighter-rouge">a, b, c</code>) it is considering materializing. As
+a result the algorithm takes a long time on schemas with many rows and columns.</p>
+
+<p>We are working on a
+<a href="https://issues.apache.org/jira/browse/CALCITE-1616">data profiler</a>
+to address this.</p>
+
+<h2 id="lattice-suggester">Lattice suggester</h2>
+
+<p>If you have defined a lattice, Calcite will self-tune within that lattice.
+But what if you have not defined a lattice?</p>
+
+<p>Enter the Lattice Suggester, which builds lattices based on incoming queries.
+Create a model with a schema that has <code class="highlighter-rouge">"autoLattice": true</code>:</p>
+
+<figure class="highlight"><pre><code class="language-json" data-lang="json"><span class="p">{</span><span class="w">
+ </span><span class="s2">"version"</span><span class="p">:</span><span class="w"> </span><span class="s2">"1.0"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"defaultSchema"</span><span class="p">:</span><span class="w"> </span><span class="s2">"foodmart"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"schemas"</span><span class="p">:</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="p">{</span><span class="w">
+ </span><span class="s2">"type"</span><span class="p">:</span><span class="w"> </span><span class="s2">"jdbc"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"name"</span><span class="p">:</span><span class="w"> </span><span class="s2">"foodmart"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"jdbcUser"</span><span class="p">:</span><span class="w"> </span><span class="s2">"FOODMART"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"jdbcPassword"</span><span class="p">:</span><span class="w"> </span><span class="s2">"FOODMART"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"jdbcUrl"</span><span class="p">:</span><span class="w"> </span><span class="s2">"jdbc:hsqldb:res:foodmart"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"jdbcSchema"</span><span class="p">:</span><span class="w"> </span><span class="s2">"foodmart"</span><span class="w">
+ </span><span class="p">},</span><span class="w"> </span><span class="p">{</span><span class="w">
+ </span><span class="s2">"name"</span><span class="p">:</span><span class="w"> </span><span class="s2">"adhoc"</span><span class="p">,</span><span class="w">
+ </span><span class="s2">"autoLattice"</span><span class="p">:</span><span class="w"> </span><span class="kc">true</span><span class="w">
+ </span><span class="p">}</span><span class="w"> </span><span class="p">]</span><span class="w">
+</span><span class="p">}</span></code></pre></figure>
+
+<p>This is a cut-down version of
+<a href="https://github.com/apache/calcite/blob/master/core/src/test/resources/hsqldb-foodmart-lattice-model.json">hsqldb-foodmart-lattice-model.json</a></p>
+
+<p>As you run queries, Calcite will start to build lattices based on those
+queries. Each lattice is based on a particular fact table. As it sees more
+queries on that fact table, it will evolve the lattice, joining more dimension
+tables to the star, and adding measures.</p>
+
+<p>Each lattice will then optimize itself based on both the data and the queries.
+The goal is to create summary tables (tiles) that are reasonably small but are
+based on more frequently used attributes and measures.</p>
+
+<p>This feature is still experimental, but has the potential to make databases
+more âself-tuningâ than before.</p>
+
+<h2 id="further-directions">Further directions</h2>
+
+<p>Here are some ideas that have not yet been implemented:</p>
+<ul>
+ <li>The algorithm that builds tiles takes into account a log of past queries.</li>
+ <li>Materialized view manager sees incoming queries and builds tiles for them.</li>
+ <li>Materialized view manager drops tiles that are not actively used.</li>
+ <li>Lattice suggester adds lattices based on incoming queries,
+transfers tiles from existing lattices to new lattices,
+and drops lattices that are no longer being used.</li>
+ <li>Tiles that cover a horizontal slice of a table; and a rewrite algorithm that
+can answer a query by stitching together several tiles and going to the raw
+data to fill in the holes.</li>
+ <li>API to invalidate tiles, or horizontal slices of tiles, when the underlying
+data is changed.</li>
+</ul>
+
+<h2 id="references">References</h2>
+
+<ul>
+<li>[<a name="ref-hru96">HRU96</a>] V. Harinarayan, A. Rajaraman and J. Ullman.
+ <a href="http://web.eecs.umich.edu/~jag/eecs584/papers/implementing_data_cube.pdf">Implementing
+ data cubes efficiently</a>.
+ In <i>Proc. ACM SIGMOD Conf.</i>, Montreal, 1996.</li>
+</ul>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <div class="section-nav">
+ <div class="left align-right">
+
+
+
+ <a href="/docs/materialized_views.html" class="prev">Previous</a>
+
+ </div>
+ <div class="right align-left">
+
+
+
+
+
+ <a href="/docs/avatica_overview.html" class="next">Next</a>
+
+ </div>
+ </div>
+ <div class="clear"></div>
+
+
+ </article>
+ </div>
+
+ <div class="unit one-fifth hide-on-mobiles">
+ <aside>
+
+ <h4>Overview</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/index.html">Background</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/tutorial.html">Tutorial</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/algebra.html">Algebra</a></li>
+
+
+</ul>
+
+
+ <h4>Advanced</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/adapter.html">Adapters</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/spatial.html">Spatial</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/stream.html">Streaming</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/materialized_views.html">Materialized Views</a></li>
+
+
+
+
+
+
+
+
+
+ <li class="current"><a href="/docs/lattice.html">Lattices</a></li>
+
+
+</ul>
+
+
+ <h4>Avatica</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/avatica_overview.html">Overview</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/avatica_roadmap.html">Roadmap</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/avatica_json_reference.html">JSON Reference</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/avatica_protobuf_reference.html">Protobuf Reference</a></li>
+
+
+</ul>
+
+
+ <h4>Reference</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/reference.html">SQL language</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/model.html">JSON/YAML models</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/howto.html">HOWTO</a></li>
+
+
+</ul>
+
+
+ <h4>Meta</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/history.html">History</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/powered_by.html">Powered by Calcite</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/apidocs">API</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/testapidocs">Test API</a></li>
+
+
+</ul>
+
+
+ </aside>
+</div>
+
+
+ <div class="clear"></div>
+
+ </div>
+ </section>
+
+
+ <footer role="contentinfo">
+ <div id="poweredby">
+ <a href="http://www.apache.org/">
+ <span class="sr-only">Apache</span>
+ <img src="/img/feather.png" width="190" height="77" alt="Apache Logo"></a>
+ </div>
+ <div id="copyright">
+ <p>The contents of this website are Copyright © 2019
+ <a href="https://www.apache.org/">Apache Software Foundation</a>
+ under the terms of
+ the <a href="https://www.apache.org/licenses/">
+ Apache License v2</a>. Apache Calcite and its logo are
+ trademarks of the Apache Software Foundation.</p>
+ </div>
+</footer>
+
+ <script>
+ var anchorForId = function (id) {
+ var anchor = document.createElement("a");
+ anchor.className = "header-link";
+ anchor.href = "#" + id;
+ anchor.innerHTML = "<span class=\"sr-only\">Permalink</span><i class=\"fa fa-link\"></i>";
+ anchor.title = "Permalink";
+ return anchor;
+ };
+
+ var linkifyAnchors = function (level, containingElement) {
+ var headers = containingElement.getElementsByTagName("h" + level);
+ for (var h = 0; h < headers.length; h++) {
+ var header = headers[h];
+
+ if (typeof header.id !== "undefined" && header.id !== "") {
+ header.appendChild(anchorForId(header.id));
+ }
+ }
+ };
+
+ document.onreadystatechange = function () {
+ if (this.readyState === "complete") {
+ var contentBlock = document.getElementsByClassName("docs")[0] || document.getElementsByClassName("news")[0];
+ if (!contentBlock) {
+ return;
+ }
+ for (var level = 1; level <= 6; level++) {
+ linkifyAnchors(level, contentBlock);
+ }
+ }
+ };
+</script>
+
+
+</body>
+</html>
Propchange: calcite/site/docs/lattice.html
------------------------------------------------------------------------------
svn:executable = *
Added: calcite/site/docs/materialized_views.html
URL: http://svn.apache.org/viewvc/calcite/site/docs/materialized_views.html?rev=1850910&view=auto
==============================================================================
--- calcite/site/docs/materialized_views.html (added)
+++ calcite/site/docs/materialized_views.html Wed Jan 9 22:21:11 2019
@@ -0,0 +1,803 @@
+<!DOCTYPE HTML>
+<html lang="en-US">
+<head>
+ <meta charset="UTF-8">
+ <title>Materialized Views</title>
+ <meta name="viewport" content="width=device-width,initial-scale=1">
+ <meta name="generator" content="Jekyll v3.7.3">
+ <link rel="stylesheet" href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic,900">
+ <link rel="stylesheet" href="/css/screen.css">
+ <link rel="icon" type="image/x-icon" href="/favicon.ico">
+ <!--[if lt IE 9]>
+ <script src="/js/html5shiv.min.js"></script>
+ <script src="/js/respond.min.js"></script>
+ <![endif]-->
+</head>
+
+
+<body class="wrap">
+ <header role="banner">
+ <nav class="mobile-nav show-on-mobiles">
+ <ul>
+ <li class="">
+ <a href="/">Home</a>
+ </li>
+ <li class="">
+ <a href="/downloads/">Download</a>
+ </li>
+ <li class="">
+ <a href="/community/">Community</a>
+ </li>
+ <li class="">
+ <a href="/develop/">Develop</a>
+ </li>
+ <li class="">
+ <a href="/news/">News</a>
+ </li>
+ <li class="current">
+ <a href="/docs/">Docs</a>
+ </li>
+</ul>
+
+ </nav>
+ <div class="grid">
+ <div class="unit one-third center-on-mobiles">
+ <h1>
+ <a href="/">
+ <span class="sr-only">Apache Calcite</span>
+ <img src="/img/logo.png" width="226" height="140" alt="Calcite Logo">
+ </a>
+ </h1>
+ </div>
+ <nav class="main-nav unit two-thirds hide-on-mobiles">
+ <ul>
+ <li class="">
+ <a href="/">Home</a>
+ </li>
+ <li class="">
+ <a href="/downloads/">Download</a>
+ </li>
+ <li class="">
+ <a href="/community/">Community</a>
+ </li>
+ <li class="">
+ <a href="/develop/">Develop</a>
+ </li>
+ <li class="">
+ <a href="/news/">News</a>
+ </li>
+ <li class="current">
+ <a href="/docs/">Docs</a>
+ </li>
+</ul>
+
+ </nav>
+ </div>
+</header>
+
+
+ <section class="docs">
+ <div class="grid">
+
+ <div class="docs-nav-mobile unit whole show-on-mobiles">
+ <select onchange="if (this.value) window.location.href=this.value">
+ <option value="">Navigate the docsâ¦</option>
+ <optgroup label="Overview">
+ </optgroup>
+ <optgroup label="Advanced">
+ </optgroup>
+ <optgroup label="Avatica">
+ </optgroup>
+ <optgroup label="Reference">
+ </optgroup>
+ <optgroup label="Meta">
+ </optgroup>
+
+ </select>
+</div>
+
+
+ <div class="unit four-fifths">
+ <article>
+ <h1>Materialized Views</h1>
+ <!--
+
+-->
+
+<p>There are several different ways to exploit materialized views in Calcite.</p>
+
+<ul id="markdown-toc">
+ <li><a href="#materialized-views-maintained-by-calcite" id="markdown-toc-materialized-views-maintained-by-calcite">Materialized views maintained by Calcite</a></li>
+ <li><a href="#expose-materialized-views-to-calcite" id="markdown-toc-expose-materialized-views-to-calcite">Expose materialized views to Calcite</a> <ul>
+ <li><a href="#view-based-query-rewriting" id="markdown-toc-view-based-query-rewriting">View-based query rewriting</a> <ul>
+ <li><a href="#substitution-via-rules-transformation" id="markdown-toc-substitution-via-rules-transformation">Substitution via rules transformation</a></li>
+ <li><a href="#rewriting-using-plan-structural-information" id="markdown-toc-rewriting-using-plan-structural-information">Rewriting using plan structural information</a> <ul>
+ <li><a href="#rewriting-coverage" id="markdown-toc-rewriting-coverage">Rewriting coverage</a> <ul>
+ <li><a href="#join-rewriting" id="markdown-toc-join-rewriting">Join rewriting</a></li>
+ <li><a href="#aggregate-rewriting" id="markdown-toc-aggregate-rewriting">Aggregate rewriting</a></li>
+ <li><a href="#aggregate-rewriting-with-aggregation-rollup" id="markdown-toc-aggregate-rewriting-with-aggregation-rollup">Aggregate rewriting (with aggregation rollup)</a></li>
+ <li><a href="#query-partial-rewriting" id="markdown-toc-query-partial-rewriting">Query partial rewriting</a></li>
+ <li><a href="#view-partial-rewriting" id="markdown-toc-view-partial-rewriting">View partial rewriting</a></li>
+ <li><a href="#union-rewriting" id="markdown-toc-union-rewriting">Union rewriting</a></li>
+ <li><a href="#union-rewriting-with-aggregate" id="markdown-toc-union-rewriting-with-aggregate">Union rewriting with aggregate</a></li>
+ </ul>
+ </li>
+ <li><a href="#limitations" id="markdown-toc-limitations">Limitations</a></li>
+ </ul>
+ </li>
+ </ul>
+ </li>
+ </ul>
+ </li>
+ <li><a href="#references" id="markdown-toc-references">References</a></li>
+</ul>
+
+<h2 id="materialized-views-maintained-by-calcite">Materialized views maintained by Calcite</h2>
+
+<p>For details, see the <a href="/docs/lattice.html">lattices documentation</a>.</p>
+
+<h2 id="expose-materialized-views-to-calcite">Expose materialized views to Calcite</h2>
+
+<p>Some Calcite adapters as well as projects that rely on Calcite have their own notion of materialized views.</p>
+
+<p>For example, Apache Cassandra allows the user to define materialized views based on existing tables which are automatically maintained.
+The Cassandra adapter automatically exposes these materialized views to Calcite.</p>
+
+<p>Another example is Apache Hive. When a materialized view is created in Hive, the user can specify whether the view may be used in query optimization. If the user chooses to do so, the materialized view will be registered with Calcite.</p>
+
+<p>By registering materialized views in Calcite, the optimizer has the opportunity to automatically rewrite queries to use these views.</p>
+
+<h3 id="view-based-query-rewriting">View-based query rewriting</h3>
+
+<p>View-based query rewriting aims to take an input query which can be answered using a preexisting view and rewrite the query to make use of the view.
+Currently Calcite has two implementations of view-based query rewriting.</p>
+
+<h4 id="substitution-via-rules-transformation">Substitution via rules transformation</h4>
+
+<p>The first approach is based on view substitution.
+<code class="highlighter-rouge">SubstitutionVisitor</code> and its extension <code class="highlighter-rouge">MaterializedViewSubstitutionVisitor</code> aim to substitute part of the relational algebra tree with an equivalent expression which makes use of a materialized view. The scan over the materialized view and the materialized view definition plan are registered with the planner. Afterwards, transformation rules that try to unify expressions in the plan are triggered. Expressions do not need to be equivalent to be replaced: the visitor might add a residual predicate on top of the expression if needed.</p>
+
+<p>The following example is taken from the documentation of <code class="highlighter-rouge">SubstitutionVisitor</code>:</p>
+
+<ul>
+ <li>Query: <code class="highlighter-rouge">SELECT a, c FROM t WHERE x = 5 AND b = 4</code></li>
+ <li>Target (materialized view definition): <code class="highlighter-rouge">SELECT a, b, c FROM t WHERE x = 5</code></li>
+ <li>Result: <code class="highlighter-rouge">SELECT a, c FROM mv WHERE b = 4</code></li>
+</ul>
+
+<p>Note that <code class="highlighter-rouge">result</code> uses the materialized view table <code class="highlighter-rouge">mv</code> and a simplified condition <code class="highlighter-rouge">b = 4</code>.</p>
+
+<p>While this approach can accomplish a large number of rewritings, it has some limitations. Since the rule relies on transformation rules to create the equivalence between expressions in the query and the materialized view, it might need to enumerate exhaustively all possible equivalent rewritings for a given expression to find a materialized view substitution. However, this is not scalable in the presence of complex
+views, e.g., views with an arbitrary number of join operators.</p>
+
+<h4 id="rewriting-using-plan-structural-information">Rewriting using plan structural information</h4>
+
+<p>In turn, an alternative rule that attempts to match queries to views by extracting some structural information about the expression to replace has been proposed.</p>
+
+<p><code class="highlighter-rouge">AbstractMaterializedViewRule</code> builds on the ideas presented in [<a href="#ref-gl01">GL01</a>] and introduces some additional extensions.
+The rule can rewrite expressions containing arbitrary chains of Join, Filter, and Project operators.
+Additionally, the rule can rewrite expressions rooted at an Aggregate operator, rolling aggregations up if necessary. In turn, it can also produce rewritings using Union operators if the query can be partially answered from a view.</p>
+
+<p>To produce a larger number of rewritings, the rule relies on information exposed as constraints defined over the database tables, e.g., <em>foreign keys</em>, <em>primary keys</em>, <em>unique keys</em> or <em>not null</em>.</p>
+
+<h5 id="rewriting-coverage">Rewriting coverage</h5>
+
+<p>Let us illustrate with some examples the coverage of the view rewriting algorithm implemented in <code class="highlighter-rouge">AbstractMaterializedViewRule</code>. The examples are based on the following database schema.</p>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>CREATE TABLE depts(
+ deptno INT NOT NULL,
+ deptname VARCHAR(20),
+ PRIMARY KEY (deptno)
+);
+CREATE TABLE locations(
+ locationid INT NOT NULL,
+ state CHAR(2),
+ PRIMARY KEY (locationid)
+);
+CREATE TABLE emps(
+ empid INT NOT NULL,
+ deptno INT NOT NULL,
+ locationid INT NOT NULL,
+ empname VARCHAR(20) NOT NULL,
+ salary DECIMAL (18, 2),
+ PRIMARY KEY (empid),
+ FOREIGN KEY (deptno) REFERENCES depts(deptno),
+ FOREIGN KEY (locationid) REFERENCES locations(locationid)
+);
+</code></pre></div></div>
+
+<h6 id="join-rewriting">Join rewriting</h6>
+
+<p>The rewriting can handle different join orders in the query and the view definition. In addition, the rule tries to detect when a compensation predicate could be used to produce a rewriting using a view.</p>
+
+<ul>
+ <li>Query:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid
+FROM depts
+JOIN (
+ SELECT empid, deptno
+ FROM emps
+ WHERE empid = 1) AS subq
+ON depts.deptno = subq.deptno
+</code></pre></div></div>
+
+<ul>
+ <li>Materialized view definition:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid
+FROM emps
+JOIN depts USING (deptno)
+</code></pre></div></div>
+
+<ul>
+ <li>Rewriting:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid
+FROM mv
+WHERE empid = 1
+</code></pre></div></div>
+
+<h6 id="aggregate-rewriting">Aggregate rewriting</h6>
+
+<ul>
+ <li>Query:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT deptno
+FROM emps
+WHERE deptno > 10
+GROUP BY deptno
+</code></pre></div></div>
+
+<ul>
+ <li>Materialized view definition:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid, deptno
+FROM emps
+WHERE deptno > 5
+GROUP BY empid, deptno
+</code></pre></div></div>
+
+<ul>
+ <li>Rewriting:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT deptno
+FROM mv
+WHERE deptno > 10
+GROUP BY deptno
+</code></pre></div></div>
+
+<h6 id="aggregate-rewriting-with-aggregation-rollup">Aggregate rewriting (with aggregation rollup)</h6>
+
+<ul>
+ <li>Query:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT deptno, COUNT(*) AS c, SUM(salary) AS s
+FROM emps
+GROUP BY deptno
+</code></pre></div></div>
+
+<ul>
+ <li>Materialized view definition:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid, deptno, COUNT(*) AS c, SUM(salary) AS s
+FROM emps
+GROUP BY empid, deptno
+</code></pre></div></div>
+
+<ul>
+ <li>Rewriting:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT deptno, SUM(c), SUM(s)
+FROM mv
+GROUP BY deptno
+</code></pre></div></div>
+
+<h6 id="query-partial-rewriting">Query partial rewriting</h6>
+
+<p>Through the declared constraints, the rule can detect joins that only append columns without altering the tuples multiplicity and produce correct rewritings.</p>
+
+<ul>
+ <li>Query:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT deptno, COUNT(*)
+FROM emps
+GROUP BY deptno
+</code></pre></div></div>
+
+<ul>
+ <li>Materialized view definition:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid, depts.deptno, COUNT(*) AS c, SUM(salary) AS s
+FROM emps
+JOIN depts USING (deptno)
+GROUP BY empid, depts.deptno
+</code></pre></div></div>
+
+<ul>
+ <li>Rewriting:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT deptno, SUM(c)
+FROM mv
+GROUP BY deptno
+</code></pre></div></div>
+
+<h6 id="view-partial-rewriting">View partial rewriting</h6>
+
+<ul>
+ <li>Query:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT deptname, state, SUM(salary) AS s
+FROM emps
+JOIN depts ON emps.deptno = depts.deptno
+JOIN locations ON emps.locationid = locations.locationid
+GROUP BY deptname, state
+</code></pre></div></div>
+
+<ul>
+ <li>Materialized view definition:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid, deptno, state, SUM(salary) AS s
+FROM emps
+JOIN locations ON emps.locationid = locations.locationid
+GROUP BY empid, deptno, state
+</code></pre></div></div>
+
+<ul>
+ <li>Rewriting:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT deptname, state, SUM(s)
+FROM mv
+JOIN depts ON mv.deptno = depts.deptno
+GROUP BY deptname, state
+</code></pre></div></div>
+
+<h6 id="union-rewriting">Union rewriting</h6>
+
+<ul>
+ <li>Query:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid, deptname
+FROM emps
+JOIN depts ON emps.deptno = depts.deptno
+WHERE salary > 10000
+</code></pre></div></div>
+
+<ul>
+ <li>Materialized view definition:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid, deptname
+FROM emps
+JOIN depts ON emps.deptno = depts.deptno
+WHERE salary > 12000
+</code></pre></div></div>
+
+<ul>
+ <li>Rewriting:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid, deptname
+FROM mv
+UNION ALL
+SELECT empid, deptname
+FROM emps
+JOIN depts ON emps.deptno = depts.deptno
+WHERE salary > 10000 AND salary <= 12000
+</code></pre></div></div>
+
+<h6 id="union-rewriting-with-aggregate">Union rewriting with aggregate</h6>
+
+<ul>
+ <li>Query:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid, deptname, SUM(salary) AS s
+FROM emps
+JOIN depts ON emps.deptno = depts.deptno
+WHERE salary > 10000
+GROUP BY empid, deptname
+</code></pre></div></div>
+
+<ul>
+ <li>Materialized view definition:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid, deptname, SUM(salary) AS s
+FROM emps
+JOIN depts ON emps.deptno = depts.deptno
+WHERE salary > 12000
+GROUP BY empid, deptname
+</code></pre></div></div>
+
+<ul>
+ <li>Rewriting:</li>
+</ul>
+
+<div class="highlighter-rouge"><div class="highlight"><pre class="highlight"><code>SELECT empid, deptname, SUM(s)
+FROM (
+ SELECT empid, deptname, s
+ FROM mv
+ UNION ALL
+ SELECT empid, deptname, SUM(salary) AS s
+ FROM emps
+ JOIN depts ON emps.deptno = depts.deptno
+ WHERE salary > 10000 AND salary <= 12000
+ GROUP BY empid, deptname) AS subq
+GROUP BY empid, deptname
+</code></pre></div></div>
+
+<h5 id="limitations">Limitations</h5>
+
+<p>This rule still presents some limitations. In particular, the rewriting rule attempts to match all views against each query. We plan to implement more refined filtering techniques such as those described in [<a href="#ref-gl01">GL01</a>].</p>
+
+<h2 id="references">References</h2>
+
+<ul>
+<li>[<a name="ref-gl01">GL01</a>] Jonathan Goldstein and Per-Ã¥ke Larson.
+ <a href="http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.95.113">Optimizing queries using materialized views: A practical, scalable solution</a>.
+ In <i>Proc. ACM SIGMOD Conf.</i>, 2001.</li>
+</ul>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <div class="section-nav">
+ <div class="left align-right">
+
+
+
+ <a href="/docs/stream.html" class="prev">Previous</a>
+
+ </div>
+ <div class="right align-left">
+
+
+
+
+
+ <a href="/docs/lattice.html" class="next">Next</a>
+
+ </div>
+ </div>
+ <div class="clear"></div>
+
+
+ </article>
+ </div>
+
+ <div class="unit one-fifth hide-on-mobiles">
+ <aside>
+
+ <h4>Overview</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/index.html">Background</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/tutorial.html">Tutorial</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/algebra.html">Algebra</a></li>
+
+
+</ul>
+
+
+ <h4>Advanced</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/adapter.html">Adapters</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/spatial.html">Spatial</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/stream.html">Streaming</a></li>
+
+
+
+
+
+
+
+
+
+ <li class="current"><a href="/docs/materialized_views.html">Materialized Views</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/lattice.html">Lattices</a></li>
+
+
+</ul>
+
+
+ <h4>Avatica</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/avatica_overview.html">Overview</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/avatica_roadmap.html">Roadmap</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/avatica_json_reference.html">JSON Reference</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/avatica_protobuf_reference.html">Protobuf Reference</a></li>
+
+
+</ul>
+
+
+ <h4>Reference</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/reference.html">SQL language</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/model.html">JSON/YAML models</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/howto.html">HOWTO</a></li>
+
+
+</ul>
+
+
+ <h4>Meta</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/history.html">History</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/powered_by.html">Powered by Calcite</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/apidocs">API</a></li>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/testapidocs">Test API</a></li>
+
+
+</ul>
+
+
+ </aside>
+</div>
+
+
+ <div class="clear"></div>
+
+ </div>
+ </section>
+
+
+ <footer role="contentinfo">
+ <div id="poweredby">
+ <a href="http://www.apache.org/">
+ <span class="sr-only">Apache</span>
+ <img src="/img/feather.png" width="190" height="77" alt="Apache Logo"></a>
+ </div>
+ <div id="copyright">
+ <p>The contents of this website are Copyright © 2019
+ <a href="https://www.apache.org/">Apache Software Foundation</a>
+ under the terms of
+ the <a href="https://www.apache.org/licenses/">
+ Apache License v2</a>. Apache Calcite and its logo are
+ trademarks of the Apache Software Foundation.</p>
+ </div>
+</footer>
+
+ <script>
+ var anchorForId = function (id) {
+ var anchor = document.createElement("a");
+ anchor.className = "header-link";
+ anchor.href = "#" + id;
+ anchor.innerHTML = "<span class=\"sr-only\">Permalink</span><i class=\"fa fa-link\"></i>";
+ anchor.title = "Permalink";
+ return anchor;
+ };
+
+ var linkifyAnchors = function (level, containingElement) {
+ var headers = containingElement.getElementsByTagName("h" + level);
+ for (var h = 0; h < headers.length; h++) {
+ var header = headers[h];
+
+ if (typeof header.id !== "undefined" && header.id !== "") {
+ header.appendChild(anchorForId(header.id));
+ }
+ }
+ };
+
+ document.onreadystatechange = function () {
+ if (this.readyState === "complete") {
+ var contentBlock = document.getElementsByClassName("docs")[0] || document.getElementsByClassName("news")[0];
+ if (!contentBlock) {
+ return;
+ }
+ for (var level = 1; level <= 6; level++) {
+ linkifyAnchors(level, contentBlock);
+ }
+ }
+ };
+</script>
+
+
+</body>
+</html>
Propchange: calcite/site/docs/materialized_views.html
------------------------------------------------------------------------------
svn:executable = *