This is an automated email from the ASF dual-hosted git repository.
bridgetb pushed a commit to branch asf-site
in repository https://gitbox.apache.org/repos/asf/drill-site.git
The following commit(s) were added to refs/heads/asf-site by this push:
new 3147214 doc edits
3147214 is described below
commit 3147214137a75c6f9146f75de59eb1cbc83aefd2
Author: Bridget Bevens <[email protected]>
AuthorDate: Mon Aug 6 16:16:10 2018 -0700
doc edits
---
docs/apache-drill-1-14-0-release-notes/index.html | 5 ++
docs/lateral-join/index.html | 66 ++++++++++++++++++++++-
feed.xml | 4 +-
3 files changed, 71 insertions(+), 4 deletions(-)
diff --git a/docs/apache-drill-1-14-0-release-notes/index.html
b/docs/apache-drill-1-14-0-release-notes/index.html
index 671646d..a9ee70d 100644
--- a/docs/apache-drill-1-14-0-release-notes/index.html
+++ b/docs/apache-drill-1-14-0-release-notes/index.html
@@ -1289,6 +1289,11 @@
<li>Early release of <a href="/docs/lateral-join/">lateral join</a>. (<a
href="https://issues.apache.org/jira/browse/DRILL-5999";>DRILL-5999</a>)<br></li>
</ul>
+<div class="admonition note">
+ <p class="first admonition-title">Note</p>
+ <p class="last">The MapR ODBC/JDBC drivers for Drill 1.14 are not yet
available, but should be available by the end of August. Earlier versions of
the drivers do not work with Apache Drill 1.14. </p>
+</div>
+
<p>The following sections list all the fixes and improvements in this release:
</p>
<h2> Sub-task
diff --git a/docs/lateral-join/index.html b/docs/lateral-join/index.html
index 967248b..3780cf5 100644
--- a/docs/lateral-join/index.html
+++ b/docs/lateral-join/index.html
@@ -1254,7 +1254,7 @@
</div>
- Aug 3, 2018
+ Aug 6, 2018
<link href="/css/docpage.css" rel="stylesheet" type="text/css">
@@ -1356,7 +1356,6 @@ tableReference:
<li>UNNEST is a SQL standard, whereas FLATTEN is not.<br></li>
<li>FLATTEN is only allowed in the SELECT list of query, not in the FROM
clause.<br></li>
<li>FLATTEN does not work with schema changes, but UNNEST can if the queries
do not have hash aggregates. FLATTEN requires all data in a column to be of the
same type. For example, if a column contains integers in some rows and float in
others, UNNEST can process the query, whereas FLATTEN cannot.<br></li>
-<li>The original order of a table can be maintained when you use UNNEST, but
not with FLATTEN.<br></li>
<li>LATERAL and UNNEST cover a wider set of use cases than FLATTEN. For
example, when you use LATERAL and UNNEST, Drill can perform a LEFT OUTER JOIN
on data. If you used FLATTEN, Drill must scan the source table twice to perform
an OUTER JOIN after flattening the data. Also, with LATERAL and UNNEST, you can
apply a filter, aggregate, or limit on each row. With FLATTEN, the filter or
aggregate is applied after flattening, however you cannot apply the limit on
each row.<br></li>
<li>FLATTEN unnests data into a table and processes the entire table; filters
and subqueries are applied on the entire table at the same time.<br></li>
</ul></li>
@@ -1583,6 +1582,69 @@ The subquery that corresponds to the lateral, aggregates
the order count, groupe
) t_orders
;
</code></pre></div>
+<h3 id="flatten-vs-lateral-and-unnest-queries">FLATTEN vs LATERAL and UNNEST
Queries</h3>
+
+<p>Each of the following examples shows a query written with the FLATTEN
function and also written with a lateral join and UNNEST relational operator,
which performs like a table function. These queries demonstrate how to use
LATERAL and UNNEST instead of FLATTEN to simplify the writing of such queries.
</p>
+
+<p><strong>Example 1</strong> </p>
+
+<p>The queries in this example return the order total for the top 50
customers. </p>
+
+<p><em>FLATTEN</em> </p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">
SELECT l.c_custkey, l.c_name, r.orderkey,
+ SUM(r.totalprice) sum_order
+ FROM customer l
+ INNER JOIN (SELECT g.custkey custkey, g.name, g.orderkey, g.totalprice
totalprice
+ FROM (SELECT row_number() OVER(PARTITION BY c_custkey)
+ AS rn, f.c_custkey custkey, f.c_name name, f.o.o_orderkey orderkey,
f.o.o_totalprice totalprice
+ FROM (SELECT c_custkey, c_name,
+ FLATTEN(c_orders)
+ AS o
+ FROM customer) f) g) r
+ ON (l.c_custkey = r.custkey)
+ GROUP BY l.c_custkey, l.c_name, r.orderkey
+ ORDER BY sum_order
+ LIMIT 50;
+</code></pre></div>
+<p><em>LATERAL and UNNEST</em> </p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">
SELECT customer.c_custkey, customer.c_name, orders.o_orderkey,
sum(orders.o_totalprice) total_spending from customer,
+ LATERAL (SELECT t.o.o_orderkey o_orderkey, t.o.o_totalprice o_totalprice
+ FROM
+ UNNEST(customer.c_orders) t(o)) orders
+ GROUP BY customer.c_custkey, customer.c_name, orders.o_orderkey
+ ORDER BY total_spending
+ LIMIT 50;
+</code></pre></div>
+<p><strong>Example 2</strong> </p>
+
+<p>The queries in this example return the first 2000 orders of the top 50
customers for orders greater than one thousand dollars. </p>
+
+<p><em>FLATTEN</em> </p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">
SELECT l.c_custkey, l.c_name, r.orderkey, r.totalprice
+ FROM customer l
+ LEFT OUTER JOIN (SELECT g.custkey custkey, g.name, g.orderkey, g.totalprice
totalprice
+ FROM (SELECT row_number() OVER(PARTITION BY c_custkey)
+ AS rn, f.c_custkey custkey, f.c_name name, f.o.o_orderkey orderkey,
f.o.o_totalprice totalprice
+ FROM (SELECT c_custkey, c_name,
+ FLATTEN(c_orders)
+ AS o
+ FROM customer) f
+ WHERE f.o.o_totalprice > 1000) g
+ WHERE rn < 2001) r
+ ON (l.c_custkey = r.custkey)
+ ORDER BY l.c_custkey, r.totalprice
+ LIMIT 50;
+</code></pre></div>
+<p><em>LATERAL and UNNEST</em> </p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">
SELECT customer.c_custkey, customer.c_name, orders.o_orderkey,
orders.o_totalprice
+ FROM customer
+ LEFT JOIN LATERAL (SELECT t.o.o_orderkey o_orderkey, t.o.o_totalprice
o_totalprice
+ FROM
+ UNNEST(customer.c_orders) t(o)
+ WHERE t.o.o_totalprice > 1000 LIMIT 2000) orders ON TRUE
+ ORDER BY c_custkey, orders.o_totalprice
+ LIMIT 50;
+</code></pre></div>
<div class="doc-nav">
diff --git a/feed.xml b/feed.xml
index 0386d33..d218be3 100644
--- a/feed.xml
+++ b/feed.xml
@@ -6,8 +6,8 @@
</description>
<link>/</link>
<atom:link href="/feed.xml" rel="self" type="application/rss+xml"/>
- <pubDate>Sat, 04 Aug 2018 22:08:22 -0700</pubDate>
- <lastBuildDate>Sat, 04 Aug 2018 22:08:22 -0700</lastBuildDate>
+ <pubDate>Mon, 06 Aug 2018 16:14:14 -0700</pubDate>
+ <lastBuildDate>Mon, 06 Aug 2018 16:14:14 -0700</lastBuildDate>
<generator>Jekyll v2.5.2</generator>
<item>
