You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by ts...@apache.org on 2015/06/02 06:55:33 UTC
[1/3] drill git commit: fix DATE_ADD/SUB
Repository: drill
Updated Branches:
refs/heads/gh-pages 5deca3ed9 -> 1e05eb3e2
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/083-select-having.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/083-select-having.md b/_docs/sql-reference/sql-commands/083-select-having.md
deleted file mode 100644
index 76828c5..0000000
--- a/_docs/sql-reference/sql-commands/083-select-having.md
+++ /dev/null
@@ -1,51 +0,0 @@
----
-title: "SELECT HAVING"
-parent: "SQL Commands"
----
-The HAVING clause filters group rows created by the GROUP BY clause. The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list. If there is no GROUP BY clause, the HAVING clause is applied to the entire result as a single group. The SELECT clause cannot refer directly to any column that does not have a GROUP BY clause.
-
-## Syntax
-The HAVING clause supports the following syntax:
-
-`[ HAVING boolean_expression ]`
-
-## Expression
-A *boolean expression* can include one or more of the following operators:
-
- * AND
- * OR
- * NOT
- * IS NULL
- * IS NOT NULL
- * LIKE
- * BETWEEN
- * IN
- * Comparison operators
- * Quantified comparison operators
-
-## Usage Notes
- * Any column referenced in a HAVING clause must be either a grouping column or a column that refers to the result of an aggregate function.
- * In a HAVING clause, you cannot specify:
- * An alias that was defined in the select list. You must repeat the original, unaliased expression.
- * An ordinal number that refers to a select list item. Only the GROUP BY and ORDER BY clauses accept ordinal numbers.
-
-## Examples
-The following example query uses the HAVING clause to constrain an aggregate result. Drill queries the `dfs.clicks workspace` and returns the total number of clicks for devices that indicate high click-throughs:
-
- 0: jdbc:drill:> select t.user_info.device, count(*) from \`clicks/clicks.json\` t
- group by t.user_info.device having count(*) > 1000;
-
- +------------+------------+
- | EXPR$0 | EXPR$1 |
- +------------+------------+
- | IOS5 | 11814 |
- | AOS4.2 | 5986 |
- | IOS6 | 4464 |
- | IOS7 | 3135 |
- | AOS4.4 | 1562 |
- | AOS4.3 | 3039 |
- +------------+------------+
-
-The aggregate is a count of the records for each different mobile device in the clickstream data. Only the activity for the devices that registered more than 1000 transactions qualify for the result set.
-
-
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/084-limit-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/084-limit-clause.md b/_docs/sql-reference/sql-commands/084-limit-clause.md
new file mode 100755
index 0000000..459c0c0
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/084-limit-clause.md
@@ -0,0 +1,51 @@
+---
+title: "LIMIT Clause"
+parent: "SQL Commands"
+---
+The LIMIT clause limits the result set to the specified number of rows. You can use LIMIT with or without an ORDER BY clause.
+
+
+## Syntax
+The LIMIT clause supports the following syntax:
+
+ [ LIMIT { count | ALL } ]
+
+Specifying ALL returns all records, which is equivalent to omitting the LIMIT clause from the SELECT statement.
+
+## Parameters
+*count*
+
+Specifies the maximum number of rows to return.
+If the count expression evaluates to NULL, Drill treats it as LIMIT ALL.
+
+## Examples
+The following example query includes the ORDER BY and LIMIT clauses and returns the top 20 sales totals by month and state:
+
+ 0: jdbc:drill:> select `month`, state, sum(order_total) as sales from orders group by `month`, state
+ order by 3 desc limit 20;
+ +------------+------------+------------+
+ | month | state | sales |
+ +------------+------------+------------+
+ | May | ca | 119586 |
+ | June | ca | 116322 |
+ | April | ca | 101363 |
+ | March | ca | 99540 |
+ | July | ca | 90285 |
+ | October | ca | 80090 |
+ | June | tx | 78363 |
+ | May | tx | 77247 |
+ | March | tx | 73815 |
+ | August | ca | 71255 |
+ | April | tx | 68385 |
+ | July | tx | 63858 |
+ | February | ca | 63527 |
+ | June | fl | 62199 |
+ | June | ny | 62052 |
+ | May | fl | 61651 |
+ | May | ny | 59369 |
+ | October | tx | 55076 |
+ | March | fl | 54867 |
+ | March | ny | 52101 |
+ +------------+------------+------------+
+ 20 rows selected
+
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/084-select-limit.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/084-select-limit.md b/_docs/sql-reference/sql-commands/084-select-limit.md
deleted file mode 100644
index 9a1d6b5..0000000
--- a/_docs/sql-reference/sql-commands/084-select-limit.md
+++ /dev/null
@@ -1,51 +0,0 @@
----
-title: "SELECT LIMIT"
-parent: "SQL Commands"
----
-The LIMIT clause limits the result set to the specified number of rows. You can use LIMIT with or without an ORDER BY clause.
-
-
-## Syntax
-The LIMIT clause supports the following syntax:
-
- [ LIMIT { count | ALL } ]
-
-Specifying ALL returns all records, which is equivalent to omitting the LIMIT clause from the SELECT statement.
-
-## Parameters
-*count*
-
-Specifies the maximum number of rows to return.
-If the count expression evaluates to NULL, Drill treats it as LIMIT ALL.
-
-## Examples
-The following example query includes the ORDER BY and LIMIT clauses and returns the top 20 sales totals by month and state:
-
- 0: jdbc:drill:> select `month`, state, sum(order_total) as sales from orders group by `month`, state
- order by 3 desc limit 20;
- +------------+------------+------------+
- | month | state | sales |
- +------------+------------+------------+
- | May | ca | 119586 |
- | June | ca | 116322 |
- | April | ca | 101363 |
- | March | ca | 99540 |
- | July | ca | 90285 |
- | October | ca | 80090 |
- | June | tx | 78363 |
- | May | tx | 77247 |
- | March | tx | 73815 |
- | August | ca | 71255 |
- | April | tx | 68385 |
- | July | tx | 63858 |
- | February | ca | 63527 |
- | June | fl | 62199 |
- | June | ny | 62052 |
- | May | fl | 61651 |
- | May | ny | 59369 |
- | October | tx | 55076 |
- | March | fl | 54867 |
- | March | ny | 52101 |
- +------------+------------+------------+
- 20 rows selected
-
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/085-offset-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/085-offset-clause.md b/_docs/sql-reference/sql-commands/085-offset-clause.md
new file mode 100755
index 0000000..92ee478
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/085-offset-clause.md
@@ -0,0 +1,29 @@
+---
+title: "OFFSET Clause"
+parent: "SQL Commands"
+---
+The OFFSET clause provides a way to skip a specified number of first rows in a result set before starting to return any rows.
+
+## Syntax
+The OFFSET clause supports the following syntax:
+
+ [ OFFSET start { ROW | ROWS } ]
+
+Specifying ALL returns all records, which is equivalent to omitting the LIMIT clause from the SELECT statement.
+
+## Parameters
+*rows*
+
+Specifies the number of rows Drill should skip before returning the result set.
+
+## Usage Notes
+ * The OFFSET number must be a positive integer and cannot be larger than the number of rows in the underlying result set or no rows are returned.
+ * You can use the OFFSET clause in conjunction with the LIMIT and ORDER BY clauses.
+ * When used with the LIMIT option, OFFSET rows are skipped before starting to count the LIMIT rows that are returned. If the LIMIT option is not used, the number of rows in the result set is reduced by the number of rows that are skipped.
+ * The rows skipped by an OFFSET clause still have to be scanned, so it might be inefficient to use a large OFFSET value.
+
+## Examples
+The following example query returns the result set from row 101 and on, skipping the first 100 rows of the table:
+
+ SELECT * FROM dfs.logs OFFSET 100 ROWS;
+
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/085-select-offset.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/085-select-offset.md b/_docs/sql-reference/sql-commands/085-select-offset.md
deleted file mode 100644
index 9e97051..0000000
--- a/_docs/sql-reference/sql-commands/085-select-offset.md
+++ /dev/null
@@ -1,29 +0,0 @@
----
-title: "SELECT OFFSET"
-parent: "SQL Commands"
----
-The OFFSET clause provides a way to skip a specified number of first rows in a result set before starting to return any rows.
-
-## Syntax
-The OFFSET clause supports the following syntax:
-
- [ OFFSET start { ROW | ROWS } ]
-
-Specifying ALL returns all records, which is equivalent to omitting the LIMIT clause from the SELECT statement.
-
-## Parameters
-*rows*
-
-Specifies the number of rows Drill should skip before returning the result set.
-
-## Usage Notes
- * The OFFSET number must be a positive integer and cannot be larger than the number of rows in the underlying result set or no rows are returned.
- * You can use the OFFSET clause in conjunction with the LIMIT and ORDER BY clauses.
- * When used with the LIMIT option, OFFSET rows are skipped before starting to count the LIMIT rows that are returned. If the LIMIT option is not used, the number of rows in the result set is reduced by the number of rows that are skipped.
- * The rows skipped by an OFFSET clause still have to be scanned, so it might be inefficient to use a large OFFSET value.
-
-## Examples
-The following example query returns the result set from row 101 and on, skipping the first 100 rows of the table:
-
- SELECT * FROM dfs.logs OFFSET 100 ROWS;
-
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/086-order-by-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/086-order-by-clause.md b/_docs/sql-reference/sql-commands/086-order-by-clause.md
new file mode 100755
index 0000000..10252af
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/086-order-by-clause.md
@@ -0,0 +1,71 @@
+---
+title: "ORDER BY Clause"
+parent: "SQL Commands"
+---
+The ORDER BY clause sorts the result set of a query.
+
+
+
+## Syntax
+The ORDER BY clause supports the following syntax:
+
+ [ ORDER BY expression
+ [ ASC | DESC ]
+ [ NULLS FIRST | NULLS LAST ]
+
+
+
+## Parameters
+*expression*
+
+Defines the sort order of the query result set, typically by specifying one or more columns in the select list.
+
+You can also specify:
+
+ * Columns that are not in the select list
+ * Expressions formed from one or more columns that exist in the tables referenced by the query
+ * Ordinal numbers that represent the position of select list entries (or the position of columns in the table if no select list exists)
+ * Aliases that define select list entries
+
+When the ORDER BY clause contains multiple expressions, the result set is sorted according to the first expression, then the second expression is applied to rows that have matching values from the first expression, and so on.
+
+ASC
+Specifies that the results should be returned in ascending order. If the order is not specified, ASC is the default.
+
+DESC
+Specifies that the results should be returned in descending order.
+
+NULLS FIRST
+Specifies that NULL values should be returned before non-NULL values.
+
+NULLS LAST
+Specifies that NULL values should be returned after non-NULL values.
+
+## Usage Notes
+ * NULL values are considered "higher" than all other values. With default ascending sort order, NULL values sort at the end.
+ * When a query does not contain an ORDER BY clause, the system returns result sets with no predictable ordering of the rows. The same query executed twice might return the result set in a different order.
+ * In any parallel system, when ORDER BY does not produce a unique ordering, the order of the rows is non-deterministic. That is, if the ORDER BY expression produces duplicate values, the return order of those rows may vary from other systems or from one run the system to the next.
+
+## Examples
+The following example query returns sales totals for each month in descending order, listing the highest sales month to the lowest sales month:
+
+ 0: jdbc:drill:> select `month`, sum(order_total)
+ from orders group by `month` order by 2 desc;
+ +------------+------------+
+ | month | EXPR$1 |
+ +------------+------------+
+ | June | 950481 |
+ | May | 947796 |
+ | March | 836809 |
+ | April | 807291 |
+ | July | 757395 |
+ | October | 676236 |
+ | August | 572269 |
+ | February | 532901 |
+ | September | 373100 |
+ | January | 346536 |
+ +------------+------------+
+
+
+
+
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/086-select-order-by.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/086-select-order-by.md b/_docs/sql-reference/sql-commands/086-select-order-by.md
deleted file mode 100644
index 794d029..0000000
--- a/_docs/sql-reference/sql-commands/086-select-order-by.md
+++ /dev/null
@@ -1,71 +0,0 @@
----
-title: "SELECT ORDER BY"
-parent: "SQL Commands"
----
-The ORDER BY clause sorts the result set of a query.
-
-
-
-## Syntax
-The ORDER BY clause supports the following syntax:
-
- [ ORDER BY expression
- [ ASC | DESC ]
- [ NULLS FIRST | NULLS LAST ]
-
-
-
-## Parameters
-*expression*
-
-Defines the sort order of the query result set, typically by specifying one or more columns in the select list.
-
-You can also specify:
-
- * Columns that are not in the select list
- * Expressions formed from one or more columns that exist in the tables referenced by the query
- * Ordinal numbers that represent the position of select list entries (or the position of columns in the table if no select list exists)
- * Aliases that define select list entries
-
-When the ORDER BY clause contains multiple expressions, the result set is sorted according to the first expression, then the second expression is applied to rows that have matching values from the first expression, and so on.
-
-ASC
-Specifies that the results should be returned in ascending order. If the order is not specified, ASC is the default.
-
-DESC
-Specifies that the results should be returned in descending order.
-
-NULLS FIRST
-Specifies that NULL values should be returned before non-NULL values.
-
-NULLS LAST
-Specifies that NULL values should be returned after non-NULL values.
-
-## Usage Notes
- * NULL values are considered "higher" than all other values. With default ascending sort order, NULL values sort at the end.
- * When a query does not contain an ORDER BY clause, the system returns result sets with no predictable ordering of the rows. The same query executed twice might return the result set in a different order.
- * In any parallel system, when ORDER BY does not produce a unique ordering, the order of the rows is non-deterministic. That is, if the ORDER BY expression produces duplicate values, the return order of those rows may vary from other systems or from one run the system to the next.
-
-## Examples
-The following example query returns sales totals for each month in descending order, listing the highest sales month to the lowest sales month:
-
- 0: jdbc:drill:> select `month`, sum(order_total)
- from orders group by `month` order by 2 desc;
- +------------+------------+
- | month | EXPR$1 |
- +------------+------------+
- | June | 950481 |
- | May | 947796 |
- | March | 836809 |
- | April | 807291 |
- | July | 757395 |
- | October | 676236 |
- | August | 572269 |
- | February | 532901 |
- | September | 373100 |
- | January | 346536 |
- +------------+------------+
-
-
-
-
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/087-select-union.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/087-select-union.md b/_docs/sql-reference/sql-commands/087-select-union.md
deleted file mode 100644
index 6d5af65..0000000
--- a/_docs/sql-reference/sql-commands/087-select-union.md
+++ /dev/null
@@ -1,42 +0,0 @@
----
-title: "SELECT UNION"
-parent: "SQL Commands"
----
-The UNION set operator returns all rows in the result sets of two separate query expressions. For example, if two employee tables exist, you can use the UNION set operator to merge the two tables and build a complete list of all the employees. Drill supports UNION ALL only. Drill does not support DISTINCT.
-
-
-## Syntax
-The UNION set operator supports the following syntax:
-
- query
- { UNION [ ALL ] }
- query
-
-
-## Parameters
-*query*
-
-Any SELECT query that Drill supports. See SELECT.
-
-## Usage Notes
- * The two SELECT query expressions that represent the direct operands of the UNION must produce the same number of columns. Corresponding columns must contain compatible data types. See Supported Data Types.
- * Multiple UNION operators in the same SELECT statement are evaluated left to right, unless otherwise indicated by parentheses.
- * You cannot use * in UNION ALL for schemaless data.
-
-## Examples
-The following example uses the UNION ALL set operator to combine click activity data before and after a marketing campaign. The data in the example exists in the `dfs.clicks workspace`.
-
- 0: jdbc:drill:> select t.trans_id transaction, t.user_info.cust_id customer from `clicks/clicks.campaign.json` t
- union all
- select u.trans_id, u.user_info.cust_id from `clicks/clicks.json` u limit 5;
- +-------------+------------+
- | transaction | customer |
- +-------------+------------+
- | 35232 | 18520 |
- | 31995 | 17182 |
- | 35760 | 18228 |
- | 37090 | 17015 |
- | 37838 | 18737 |
- +-------------+------------+
-
-This UNION ALL query returns rows that exist in two files (and includes any duplicate rows from those files): `clicks.campaign.json` and `clicks.json`
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/087-union-set-operator.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/087-union-set-operator.md b/_docs/sql-reference/sql-commands/087-union-set-operator.md
new file mode 100755
index 0000000..d55c50e
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/087-union-set-operator.md
@@ -0,0 +1,42 @@
+---
+title: "UNION Set Operator"
+parent: "SQL Commands"
+---
+The UNION set operator returns all rows in the result sets of two separate query expressions. For example, if two employee tables exist, you can use the UNION set operator to merge the two tables and build a complete list of all the employees. Drill supports UNION ALL only. Drill does not support DISTINCT.
+
+
+## Syntax
+The UNION set operator supports the following syntax:
+
+ query
+ { UNION ALL }
+ query
+
+
+## Parameters
+*query*
+
+Any SELECT query that Drill supports. See SELECT.
+
+## Usage Notes
+ * The two SELECT query expressions that represent the direct operands of the UNION must produce the same number of columns. Corresponding columns must contain compatible data types. See Supported Data Types.
+ * Multiple UNION operators in the same SELECT statement are evaluated left to right, unless otherwise indicated by parentheses.
+ * You cannot use * in UNION ALL for schemaless data.
+
+## Examples
+The following example uses the UNION ALL set operator to combine click activity data before and after a marketing campaign. The data in the example exists in the `dfs.clicks workspace`.
+
+ 0: jdbc:drill:> select t.trans_id transaction, t.user_info.cust_id customer from `clicks/clicks.campaign.json` t
+ union all
+ select u.trans_id, u.user_info.cust_id from `clicks/clicks.json` u limit 5;
+ +-------------+------------+
+ | transaction | customer |
+ +-------------+------------+
+ | 35232 | 18520 |
+ | 31995 | 17182 |
+ | 35760 | 18228 |
+ | 37090 | 17015 |
+ | 37838 | 18737 |
+ +-------------+------------+
+
+This UNION ALL query returns rows that exist in two files (and includes any duplicate rows from those files): `clicks.campaign.json` and `clicks.json`
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/088-select-where.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/088-select-where.md b/_docs/sql-reference/sql-commands/088-select-where.md
deleted file mode 100644
index 5d600fa..0000000
--- a/_docs/sql-reference/sql-commands/088-select-where.md
+++ /dev/null
@@ -1,59 +0,0 @@
----
-title: "SELECT WHERE"
-parent: "SQL Commands"
----
-The WHERE clause selects rows based on a boolean expression. Only rows for which the expression evaluates to TRUE are returned in the result.
-
-## Syntax
-The WHERE clause supports the following syntax:
-
- [ WHERE boolean_expression ]
-
-## Expression
-A boolean expression can include one or more of the following operators:
-
- * AND
- * OR
- * NOT
- * IS NULL
- * IS NOT NULL
- * LIKE
- * BETWEEN
- * IN
- * EXISTS
- * Comparison operators
- * Quantified comparison operators
-
-
-## Examples
-The following query compares order totals where the states are California and New York:
-
- 0: jdbc:drill:> select o1.cust_id, sum(o1.order_total) as ny_sales,
- (select sum(o2.order_total) from hive.orders o2
- where o1.cust_id=o2.cust_id and state='ca') as ca_sales
- from hive.orders o1 where o1.state='ny' group by o1.cust_id
- order by cust_id limit 20;
- +------------+------------+------------+
- | cust_id | ny_sales | ca_sales |
- +------------+------------+------------+
- | 1001 | 72 | 47 |
- | 1002 | 108 | 198 |
- | 1003 | 83 | null |
- | 1004 | 86 | 210 |
- | 1005 | 168 | 153 |
- | 1006 | 29 | 326 |
- | 1008 | 105 | 168 |
- | 1009 | 443 | 127 |
- | 1010 | 75 | 18 |
- | 1012 | 110 | null |
- | 1013 | 19 | null |
- | 1014 | 106 | 162 |
- | 1015 | 220 | 153 |
- | 1016 | 85 | 159 |
- | 1017 | 82 | 56 |
- | 1019 | 37 | 196 |
- | 1020 | 193 | 165 |
- | 1022 | 124 | null |
- | 1023 | 166 | 149 |
- | 1024 | 233 | null |
- +------------+------------+------------+
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/088-where-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/088-where-clause.md b/_docs/sql-reference/sql-commands/088-where-clause.md
new file mode 100755
index 0000000..d233d6d
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/088-where-clause.md
@@ -0,0 +1,73 @@
+---
+title: "WHERE Clause"
+parent: "SQL Commands"
+---
+The WHERE clause selects rows based on a boolean expression. Only rows for which the expression evaluates to TRUE are returned in the result.
+
+## Syntax
+The WHERE clause supports the following syntax:
+
+ WHERE boolean_expression
+
+## Expression
+A boolean expression can include one or more of the following operators:
+
+ * AND
+ * OR
+ * NOT
+ * IS NULL
+ * IS NOT NULL
+ * LIKE
+ * BETWEEN
+ * IN
+ * EXISTS
+ * Comparison operators
+ * Quantified comparison operators
+
+
+## Examples
+The following query compares order totals where the states are California and New York:
+
+ 0: jdbc:drill:> select o1.cust_id, sum(o1.order_total) as ny_sales,
+ (select sum(o2.order_total) from hive.orders o2
+ where o1.cust_id=o2.cust_id and state='ca') as ca_sales
+ from hive.orders o1 where o1.state='ny' group by o1.cust_id
+ order by cust_id limit 20;
+ +------------+------------+------------+
+ | cust_id | ny_sales | ca_sales |
+ +------------+------------+------------+
+ | 1001 | 72 | 47 |
+ | 1002 | 108 | 198 |
+ | 1003 | 83 | null |
+ | 1004 | 86 | 210 |
+ | 1005 | 168 | 153 |
+ | 1006 | 29 | 326 |
+ | 1008 | 105 | 168 |
+ | 1009 | 443 | 127 |
+ | 1010 | 75 | 18 |
+ | 1012 | 110 | null |
+ | 1013 | 19 | null |
+ | 1014 | 106 | 162 |
+ | 1015 | 220 | 153 |
+ | 1016 | 85 | 159 |
+ | 1017 | 82 | 56 |
+ | 1019 | 37 | 196 |
+ | 1020 | 193 | 165 |
+ | 1022 | 124 | null |
+ | 1023 | 166 | 149 |
+ | 1024 | 233 | null |
+ +------------+------------+------------+
+
+The following query uses a workspace named `dfw.views` and joins a view named “custview” with a hive table named “orders” to determine sales for each membership type:
+
+ 0: jdbc:drill:> select membership, sum(order_total) as sales from hive.orders, custview
+ where orders.cust_id=custview.cust_id
+ group by membership order by 2;
+ +------------+------------+
+ | membership | sales |
+ +------------+------------+
+ | "basic" | 380665 |
+ | "silver" | 708438 |
+ | "gold" | 2787682 |
+ +------------+------------+
+ 3 rows selected
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/089-select-with.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/089-select-with.md b/_docs/sql-reference/sql-commands/089-select-with.md
deleted file mode 100644
index 4253354..0000000
--- a/_docs/sql-reference/sql-commands/089-select-with.md
+++ /dev/null
@@ -1,95 +0,0 @@
----
-title: "SELECT WITH"
-parent: "SQL Commands"
----
-The WITH clause is an optional clause used to contain one or more common table
-expressions (CTE) where each CTE defines a temporary table that exists for the
-duration of the query. Each subquery in the WITH clause specifies a table
-name, an optional list of column names, and a SELECT statement.
-
-## Syntax
-
-The WITH clause supports the following syntax:
-
- [ WITH with_subquery [, ...] ]
- where with_subquery is:
- with_subquery_table_name [ ( column_name [, ...] ) ] AS ( query )
-
-## Parameters
-
-_with_subquery_table_name_
-
-A unique name for a temporary table that defines the results of a WITH clause
-subquery. You cannot use duplicate names within a single WITH clause. You must
-give each subquery a table name that can be referenced in the FROM clause.
-
-_column_name_
-
-An optional list of output column names for the WITH clause subquery,
-separated by commas. The number of column names specified must be equal to or
-less than the number of columns defined by the subquery.
-
-_query_
-
-Any SELECT query that Drill supports. See
-[SELECT]({{ site.baseurl }}/docs/SELECT+Statements).
-
-## Usage Notes
-
-Use the WITH clause to efficiently define temporary tables that Drill can
-access throughout the execution of a single query. The WITH clause is
-typically a simpler alternative to using subqueries in the main body of the
-SELECT statement. In some cases, Drill can evaluate a WITH subquery once and
-reuse the results for query optimization.
-
-You can use a WITH clause in the following SQL statements:
-
- * SELECT (including subqueries within SELECT statements)
-
- * CREATE TABLE AS
-
- * CREATE VIEW
-
- * EXPLAIN
-
-You can reference the temporary tables in the FROM clause of the query. If the
-FROM clause does not reference any tables defined by the WITH clause, Drill
-ignores the WITH clause and executes the query as normal.
-
-Drill can only reference a table defined by a WITH clause subquery in the
-scope of the SELECT query that the WITH clause begins. For example, you can
-reference such a table in the FROM clause of a subquery in the SELECT list,
-WHERE clause, or HAVING clause. You cannot use a WITH clause in a subquery and
-reference its table in the FROM clause of the main query or another subquery.
-
-You cannot specify another WITH clause inside a WITH clause subquery.
-
-For example, the following query includes a forward reference to table t2 in
-the definition of table t1:
-
-## Example
-
-The following example shows the WITH clause used to create a WITH query named
-`emp_data` that selects all of the rows from the `employee.json` file. The
-main query selects the `full_name, position_title, salary`, and `hire_date`
-rows from the `emp_data` temporary table (created from the WITH subquery) and
-orders the results by the hire date. The `emp_data` table only exists for the
-duration of the query.
-
-**Note:** The `employee.json` file is included with the Drill installation. It is located in the `cp.default` workspace which is configured by default.
-
- 0: jdbc:drill:zk=local> with emp_data as (select * from cp.`employee.json`) select full_name, position_title, salary, hire_date from emp_data order by hire_date limit 10;
- +------------------+-------------------------+------------+-----------------------+
- | full_name | position_title | salary | hire_date |
- +------------------+-------------------------+------------+-----------------------+
- | Bunny McCown | Store Assistant Manager | 8000.0 | 1993-05-01 00:00:00.0 |
- | Danielle Johnson | Store Assistant Manager | 8000.0 | 1993-05-01 00:00:00.0 |
- | Dick Brummer | Store Assistant Manager | 7900.0 | 1993-05-01 00:00:00.0 |
- | Gregory Whiting | Store Assistant Manager | 10000.0 | 1993-05-01 00:00:00.0 |
- | Juanita Sharp | HQ Human Resources | 6700.0 | 1994-01-01 00:00:00.0 |
- | Sheri Nowmer | President | 80000.0 | 1994-12-01 00:00:00.0 |
- | Rebecca Kanagaki | VP Human Resources | 15000.0 | 1994-12-01 00:00:00.0 |
- | Shauna Wyro | Store Manager | 15000.0 | 1994-12-01 00:00:00.0 |
- | Roberta Damstra | VP Information Systems | 25000.0 | 1994-12-01 00:00:00.0 |
- | Pedro Castillo | VP Country Manager | 35000.0 | 1994-12-01 00:00:00.0 |
- +------------+----------------+--------------+------------------------------------+
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/089-with-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/089-with-clause.md b/_docs/sql-reference/sql-commands/089-with-clause.md
new file mode 100755
index 0000000..5accdce
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/089-with-clause.md
@@ -0,0 +1,97 @@
+---
+title: "WITH Clause"
+parent: "SQL Commands"
+---
+The WITH clause is an optional clause used to contain one or more common table
+expressions (CTE) where each CTE defines a temporary table that exists for the
+duration of the query. Each subquery in the WITH clause specifies a table
+name, an optional list of column names, and a SELECT statement.
+
+## Syntax
+
+The WITH clause supports the following syntax:
+
+ WITH with_subquery [, ...]
+ where with_subquery is:
+ with_subquery_table_name [ ( column_name [, ...] ) ] AS ( query )
+
+## Parameters
+
+_with\_subquery\_table\_name_
+
+A unique name for a temporary table that defines the results of a WITH clause
+subquery. You cannot use duplicate names within a single WITH clause. You must
+give each subquery a table name that can be referenced in the FROM clause.
+
+_column\_name_
+
+An optional list of output column names for the WITH clause subquery,
+separated by commas. The number of column names specified must be equal to or
+less than the number of columns defined by the subquery.
+
+_query_
+
+Any SELECT query that Drill supports. See
+[SELECT]({{ site.baseurl }}/docs/SELECT+Statements).
+
+## Usage Notes
+
+Use the WITH clause to efficiently define temporary tables that Drill can
+access throughout the execution of a single query. The WITH clause is
+typically a simpler alternative to using subqueries in the main body of the
+SELECT statement. In some cases, Drill can evaluate a WITH subquery once and
+reuse the results for query optimization.
+
+You can use a WITH clause in the following SQL statements:
+
+ * SELECT (including subqueries within SELECT statements)
+
+ * CREATE TABLE AS
+
+ * CREATE VIEW
+
+ * EXPLAIN
+
+You can reference the temporary tables in the FROM clause of the query. If the
+FROM clause does not reference any tables defined by the WITH clause, Drill
+ignores the WITH clause and executes the query as normal.
+
+Drill can only reference a table defined by a WITH clause subquery in the
+scope of the SELECT query that the WITH clause begins. For example, you can
+reference such a table in the FROM clause of a subquery in the SELECT list,
+WHERE clause, or HAVING clause. You cannot use a WITH clause in a subquery and
+reference its table in the FROM clause of the main query or another subquery.
+
+You cannot specify another WITH clause inside a WITH clause subquery.
+
+For example, the following query includes a forward reference to table t2 in
+the definition of table t1:
+
+## Example
+
+The following example shows the WITH clause used to create a WITH query named
+`emp_data` that selects all of the rows from the `employee.json` file. The
+main query selects the `full_name, position_title, salary`, and `hire_date`
+rows from the `emp_data` temporary table (created from the WITH subquery) and
+orders the results by the hire date. The `emp_data` table only exists for the
+duration of the query.
+
+**Note:** The `employee.json` file is included with the Drill installation. It is located in the `cp.default` workspace which is configured by default.
+
+ 0: jdbc:drill:zk=local> WITH emp_data AS (SELECT * FROM cp.`employee.json`)
+ SELECT full_name, position_title, salary, hire_date
+ FROM emp_data ORDER BY hire_date LIMIT 10;
+ +------------------+-------------------------+------------+-----------------------+
+ | full_name | position_title | salary | hire_date |
+ +------------------+-------------------------+------------+-----------------------+
+ | Bunny McCown | Store Assistant Manager | 8000.0 | 1993-05-01 00:00:00.0 |
+ | Danielle Johnson | Store Assistant Manager | 8000.0 | 1993-05-01 00:00:00.0 |
+ | Dick Brummer | Store Assistant Manager | 7900.0 | 1993-05-01 00:00:00.0 |
+ | Gregory Whiting | Store Assistant Manager | 10000.0 | 1993-05-01 00:00:00.0 |
+ | Juanita Sharp | HQ Human Resources | 6700.0 | 1994-01-01 00:00:00.0 |
+ | Sheri Nowmer | President | 80000.0 | 1994-12-01 00:00:00.0 |
+ | Rebecca Kanagaki | VP Human Resources | 15000.0 | 1994-12-01 00:00:00.0 |
+ | Shauna Wyro | Store Manager | 15000.0 | 1994-12-01 00:00:00.0 |
+ | Roberta Damstra | VP Information Systems | 25000.0 | 1994-12-01 00:00:00.0 |
+ | Pedro Castillo | VP Country Manager | 35000.0 | 1994-12-01 00:00:00.0 |
+ +------------+----------------+--------------+------------------------------------+
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md b/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
index a7de7ea..514651c 100644
--- a/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
+++ b/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
@@ -74,21 +74,27 @@ Returns the sum of a date/time and a number of days/hours, or of a date/time and
### DATE_ADD Syntax
- DATE_ADD(keyword literal_date, integer)
+ DATE_ADD(keyword literal, integer)
DATE_ADD(keyword literal, interval expr)
DATE_ADD(column, integer)
+ DATE_ADD(column, interval expr)
+
*keyword* is the word date, time, or timestamp.
*literal* is a date, time, or timestamp literal. For example, a date in yyyy-mm-dd format enclosed in single quotation marks.
*integer* is a number of days to add to the date/time.
*column* is date, time, or timestamp data in a data source column.
*interval* is the keyword interval.
-*expr* is an interval expression.
+*expr* is an interval expression, such as the name of a data source column containing interval data.
### DATE_ADD Examples
+The following examples show how to use the syntax variations.
+
+**`DATE_ADD(keyword literal, integer)` Syntax Example**
+
Add two days to today's date May 15, 2015.
SELECT DATE_ADD(date '2015-05-15', 2) FROM sys.version;
@@ -99,7 +105,9 @@ Add two days to today's date May 15, 2015.
+------------+
1 row selected (0.07 seconds)
-Using the example data from the ["Casting Intervals"]({{site.baseurl}}/docs/data-type-conversion/#casting-intervals) section, add intervals from the `intervals.json` file to a literal timestamp using an interval expression. Create an interval expression that casts the interval data in the intervals.json file to a timestamp.
+**`DATE_ADD(keyword literal, interval expr)` Syntax Example**
+
+Using the example data from the ["Casting Intervals"]({{site.baseurl}}/docs/data-type-conversion/#casting-intervals) section, add intervals from the `intervals.json` file to a literal timestamp. Create an interval expression that casts the INTERVALDAY_col column, which contains P1D, P2D, and P3D, to a timestamp.
SELECT DATE_ADD(timestamp '2015-04-15 22:55:55', CAST(INTERVALDAY_col as interval second)) FROM dfs.`/Users/drilluser/apache-drill-1.0.0/intervals.json`;
+------------------------+
@@ -111,39 +119,57 @@ Using the example data from the ["Casting Intervals"]({{site.baseurl}}/docs/data
+------------------------+
3 rows selected (0.105 seconds)
-The query returns the sum of the timestamp plus 1, 2, and 3 days becuase the INTERVALDAY_col contains P1D, P2D, and P3D,
+The query output is the sum of the timestamp and 1, 2, and 3 days corresponding to P1D, P2D, and P3D.
-The Drill installation includes the `employee.json` file that has records of employee hire dates:
+**DATE_ADD(column, integer) Syntax Example**
- SELECT * FROM cp.`employee.json` LIMIT 1;
- +--------------+---------------+-------------+------------+--------------+-----------------+-----------+----------------+-------------+------------------------+----------+----------------+------------------+-----------------+---------+--------------------+
- | employee_id | full_name | first_name | last_name | position_id | position_title | store_id | department_id | birth_date | hire_date | salary | supervisor_id | education_level | marital_status | gender | management_role |
- +--------------+---------------+-------------+------------+--------------+-----------------+-----------+----------------+-------------+------------------------+----------+----------------+------------------+-----------------+---------+--------------------+
- | 1 | Sheri Nowmer | Sheri | Nowmer | 1 | President | 0 | 1 | 1961-08-26 | 1994-12-01 00:00:00.0 | 80000.0 | 0 | Graduate Degree | S | F | Senior Management |
- +--------------+---------------+-------------+------------+--------------+-----------------+-----------+----------------+-------------+------------------------+----------+----------------+------------------+-----------------+---------+--------------------+
- 1 row selected (0.137 seconds)
+Add two days to the value in the birth_date column.
-Look at the hire_dates for the employee 578 and 761 in `employee.json`.
+ SELECT DATE_ADD(CAST(birth_date AS date), 2) FROM cp.`employee.json` LIMIT 1;
+ +-------------+
+ | EXPR$0 |
+ +-------------+
+ | 1961-08-28 |
+ +-------------+
+ 1 row selected (0.209 seconds)
- SELECT hire_date FROM cp.`employee.json` where employee_id IN( '578','761');
- +------------------------+
- | hire_date |
- +------------------------+
- | 1996-01-01 00:00:00.0 |
- | 1998-01-01 00:00:00.0 |
- +------------------------+
- 2 rows selected (0.135 seconds)
+**`DATE_ADD(column, interval expr)` Syntax Example**
-Cast the hire_dates of the employees 578 and 761 to a timestamp, and add 10 hours to the hire_date timestamp. Because Drill reads data from JSON as VARCHAR, you need to cast the hire_date to the TIMESTAMP type.
+Add a 10 hour interval to the hire dates of employees listed in the `employee.json` file, which Drill includes in the installation.
- SELECT DATE_ADD(CAST(hire_date AS TIMESTAMP), interval '10' hour) FROM cp.`employee.json` where employee_id IN( '578','761');
- +------------------------+
- | EXPR$0 |
- +------------------------+
- | 1996-01-01 10:00:00.0 |
- | 1998-01-01 10:00:00.0 |
- +------------------------+
- 2 rows selected (0.172 seconds)
+1. Take a look at the employee data:
+
+ SELECT * FROM cp.`employee.json` LIMIT 1;
+ +--------------+---------------+-------------+------------+--------------+-----------------+-----------+----------------+-------------+------------------------+----------+----------------+------------------+-----------------+---------+--------------------+
+ | employee_id | full_name | first_name | last_name | position_id | position_title | store_id | department_id | birth_date | hire_date | salary | supervisor_id | education_level | marital_status | gender | management_role |
+ +--------------+---------------+-------------+------------+--------------+-----------------+-----------+----------------+-------------+------------------------+----------+----------------+------------------+-----------------+---------+--------------------+
+ | 1 | Sheri Nowmer | Sheri | Nowmer | 1 | President | 0 | 1 | 1961-08-26 | 1994-12-01 00:00:00.0 | 80000.0 | 0 | Graduate Degree | S | F | Senior Management |
+ +--------------+---------------+-------------+------------+--------------+-----------------+-----------+----------------+-------------+------------------------+----------+----------------+------------------+-----------------+---------+--------------------+
+ 1 row selected (0.137 seconds)
+
+2. Look at the hire_dates for the employee 578 and 761 in `employee.json`.
+
+ SELECT hire_date FROM cp.`employee.json` where employee_id IN( '578','761');
+ +------------------------+
+ | hire_date |
+ +------------------------+
+ | 1996-01-01 00:00:00.0 |
+ | 1998-01-01 00:00:00.0 |
+ +------------------------+
+ 2 rows selected (0.135 seconds)
+
+3. Cast the hire_dates of the employees 578 and 761 to a timestamp, and add 10 hours to the hire_date timestamp. Because Drill reads data from JSON as VARCHAR, you need to cast the hire_date to the TIMESTAMP type.
+
+ SELECT DATE_ADD(CAST(hire_date AS TIMESTAMP), interval '10' hour) FROM cp.`employee.json` where employee_id IN( '578','761');
+ +------------------------+
+ | EXPR$0 |
+ +------------------------+
+ | 1996-01-01 10:00:00.0 |
+ | 1998-01-01 10:00:00.0 |
+ +------------------------+
+ 2 rows selected (0.172 seconds)
+
+**`DATE_ADD(keyword literal, integer)` Syntax Example**
Add 1 year and 1 month to the timestamp 2015-04-15 22:55:55.
@@ -217,27 +243,21 @@ Returns the difference between a date/time and a number of days/hours, or betwee
DATE_ADD(column, integer)
+ DATE_SUB(column, interval expr)
+
*keyword* is the word date, time, or timestamp.
*literal* is a date, time, or timestamp literal. For example, a date in yyyy-mm-dd format enclosed in single quotation marks.
-*integer* is a number of days to subtract from the date, time, or timestamp.
+*integer* is a number of days to subtract from the date, time, or timestamp.
*column* is date, time, or timestamp data in the data source.
*interval* is the keyword interval.
-*expr* is an interval expression.
+*expr* is an interval expression, such as the name of a data source column containing interval data.
### DATE_SUB Examples
+The following examples show how to apply the syntax variations.
-Cast the hire_dates of the employees 578 and 761 to a timestamp, and add 10 hours to the hire_date timestamp. Because Drill reads data from JSON as VARCHAR, you need to cast the hire_date to the TIMESTAMP type.
+**`DATE_SUB(keyword literal, integer)` Syntax Example**
- SELECT DATE_SUB(CAST(hire_date AS TIMESTAMP), interval '10' hour) FROM cp.`employee.json` WHERE employee_id IN( '578','761');
- +------------------------+
- | EXPR$0 |
- +------------------------+
- | 1995-12-31 14:00:00.0 |
- | 1997-12-31 14:00:00.0 |
- +------------------------+
- 2 rows selected (0.161 seconds)
-
-Subtract two days to today's date May 15, 2015.
+Subtract two days from today's date May 15, 2015.
SELECT DATE_SUB(date '2015-05-15', 2) FROM sys.version;
+------------+
@@ -257,6 +277,8 @@ Subtact two months from April 15, 2015.
+------------+
1 row selected (0.088 seconds)
+**`DATE_SUB(keyword literal, interval expr)` Syntax Example**
+
Subtract 10 hours from the timestamp 2015-04-15 22:55:55.
SELECT DATE_SUB(timestamp '2015-04-15 22:55:55', interval '10' hour) FROM sys.version;
@@ -297,6 +319,29 @@ Subtract 1 day, 2 and 1/2 hours, and 45.100 seconds from the time 22:55:55.
+---------------+
1 row selected (0.095 seconds)
+**`DATE_SUB(column, integer)` Syntax Example**
+
+ SELECT DATE_SUB(CAST(birth_date AS date), 2) FROM cp.`employee.json` LIMIT 1;
+ +-------------+
+ | EXPR$0 |
+ +-------------+
+ | 1961-08-24 |
+ +-------------+
+ 1 row selected (0.158 seconds)
+
+**`DATE_SUB(column, interval expr)` Syntax Example**
+
+The `employee.json` file, which Drill includes in the installation, lists the hire dates of employees. Cast the hire_dates of the employees 578 and 761 to a timestamp, and add 10 hours to the hire_date timestamp. Because Drill reads data from JSON as VARCHAR, you need to cast the hire_date to the TIMESTAMP type.
+
+ SELECT DATE_SUB(CAST(hire_date AS TIMESTAMP), interval '10' hour) FROM cp.`employee.json` WHERE employee_id IN( '578','761');
+ +------------------------+
+ | EXPR$0 |
+ +------------------------+
+ | 1995-12-31 14:00:00.0 |
+ | 1997-12-31 14:00:00.0 |
+ +------------------------+
+ 2 rows selected (0.161 seconds)
+
## Other Date and Time Functions
The following examples show how to use these functions:
@@ -383,7 +428,7 @@ If you did not set up Drill for UTC time, TIMEOFDAY returns the local date and t
Returns a component of a timestamp, time, date, or interval.
-#### EXTRACT Syntax
+### EXTRACT Syntax
EXTRACT (extract_expression)
[2/3] drill git commit: fix DATE_ADD/SUB
Posted by ts...@apache.org.
fix DATE_ADD/SUB
Bridget's SELECT reorg
more Bridget's SELECT reorg
Bridget's Venki impersonation edits
Bridget's troubleshooting edits
Project: http://git-wip-us.apache.org/repos/asf/drill/repo
Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/a03aa49e
Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/a03aa49e
Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/a03aa49e
Branch: refs/heads/gh-pages
Commit: a03aa49eeabfaa3fe00e84c46adb0663c29b28bd
Parents: 5deca3e
Author: Kristine Hahn <kh...@maprtech.com>
Authored: Mon Jun 1 16:17:34 2015 -0700
Committer: Kristine Hahn <kh...@maprtech.com>
Committed: Mon Jun 1 16:25:58 2015 -0700
----------------------------------------------------------------------
_data/docs.json | 764 +++++++++----------
_docs/110-troubleshooting.md | 48 +-
.../070-configuring-user-impersonation.md | 20 +-
.../075-configuring-user-authentication.md | 2 +-
_docs/img/user_hops_four.PNG | Bin 0 -> 25601 bytes
_docs/img/user_hops_joined_view.PNG | Bin 0 -> 33467 bytes
_docs/img/user_hops_no_join.PNG | Bin 0 -> 32306 bytes
_docs/sql-reference/sql-commands/080-select.md | 5 +-
.../sql-commands/081-from-clause.md | 75 ++
.../sql-commands/081-select-from.md | 87 ---
.../sql-commands/082-group-by-clause.md | 51 ++
.../sql-commands/082-select-group-by.md | 51 --
.../sql-commands/083-having-clause.md | 51 ++
.../sql-commands/083-select-having.md | 51 --
.../sql-commands/084-limit-clause.md | 51 ++
.../sql-commands/084-select-limit.md | 51 --
.../sql-commands/085-offset-clause.md | 29 +
.../sql-commands/085-select-offset.md | 29 -
.../sql-commands/086-order-by-clause.md | 71 ++
.../sql-commands/086-select-order-by.md | 71 --
.../sql-commands/087-select-union.md | 42 -
.../sql-commands/087-union-set-operator.md | 42 +
.../sql-commands/088-select-where.md | 59 --
.../sql-commands/088-where-clause.md | 73 ++
.../sql-commands/089-select-with.md | 95 ---
.../sql-commands/089-with-clause.md | 97 +++
.../030-date-time-functions-and-arithmetic.md | 133 ++--
27 files changed, 1053 insertions(+), 995 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_data/docs.json
----------------------------------------------------------------------
diff --git a/_data/docs.json b/_data/docs.json
index ff291e5..f0a8fb6 100644
--- a/_data/docs.json
+++ b/_data/docs.json
@@ -2953,6 +2953,27 @@
"title": "FLATTEN",
"url": "/docs/flatten/"
},
+ "FROM Clause": {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "GROUP BY Clause",
+ "next_url": "/docs/group-by-clause/",
+ "parent": "SQL Commands",
+ "previous_title": "SELECT",
+ "previous_url": "/docs/select/",
+ "relative_path": "_docs/sql-reference/sql-commands/081-from-clause.md",
+ "title": "FROM Clause",
+ "url": "/docs/from-clause/"
+ },
"File System Storage Plugin": {
"breadcrumbs": [
{
@@ -2995,6 +3016,27 @@
"title": "Functions for Handling Nulls",
"url": "/docs/functions-for-handling-nulls/"
},
+ "GROUP BY Clause": {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "HAVING Clause",
+ "next_url": "/docs/having-clause/",
+ "parent": "SQL Commands",
+ "previous_title": "FROM Clause",
+ "previous_url": "/docs/from-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/082-group-by-clause.md",
+ "title": "GROUP BY Clause",
+ "url": "/docs/group-by-clause/"
+ },
"Getting Query Information": {
"breadcrumbs": [
{
@@ -3101,6 +3143,27 @@
"title": "Guidelines for Optimizing Aggregation",
"url": "/docs/guidelines-for-optimizing-aggregation/"
},
+ "HAVING Clause": {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "LIMIT Clause",
+ "next_url": "/docs/limit-clause/",
+ "parent": "SQL Commands",
+ "previous_title": "GROUP BY Clause",
+ "previous_url": "/docs/group-by-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/083-having-clause.md",
+ "title": "HAVING Clause",
+ "url": "/docs/having-clause/"
+ },
"HBase Storage Plugin": {
"breadcrumbs": [
{
@@ -3936,6 +3999,27 @@
"title": "KVGEN",
"url": "/docs/kvgen/"
},
+ "LIMIT Clause": {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "OFFSET Clause",
+ "next_url": "/docs/offset-clause/",
+ "parent": "SQL Commands",
+ "previous_title": "HAVING Clause",
+ "previous_url": "/docs/having-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/084-limit-clause.md",
+ "title": "LIMIT Clause",
+ "url": "/docs/limit-clause/"
+ },
"Learn Drill with the MapR Sandbox": {
"breadcrumbs": [
{
@@ -4995,6 +5079,48 @@
"title": "ODBC/JDBC Interfaces",
"url": "/docs/odbc-jdbc-interfaces/"
},
+ "OFFSET Clause": {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "ORDER BY Clause",
+ "next_url": "/docs/order-by-clause/",
+ "parent": "SQL Commands",
+ "previous_title": "LIMIT Clause",
+ "previous_url": "/docs/limit-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/085-offset-clause.md",
+ "title": "OFFSET Clause",
+ "url": "/docs/offset-clause/"
+ },
+ "ORDER BY Clause": {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "UNION Set Operator",
+ "next_url": "/docs/union-set-operator/",
+ "parent": "SQL Commands",
+ "previous_title": "OFFSET Clause",
+ "previous_url": "/docs/offset-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/086-order-by-clause.md",
+ "title": "ORDER BY Clause",
+ "url": "/docs/order-by-clause/"
+ },
"Operators": {
"breadcrumbs": [
{
@@ -7084,8 +7210,8 @@
}
],
"children": [],
- "next_title": "SELECT FROM",
- "next_url": "/docs/select-from/",
+ "next_title": "FROM Clause",
+ "next_url": "/docs/from-clause/",
"parent": "SQL Commands",
"previous_title": "EXPLAIN",
"previous_url": "/docs/explain/",
@@ -7093,195 +7219,6 @@
"title": "SELECT",
"url": "/docs/select/"
},
- "SELECT FROM": {
- "breadcrumbs": [
- {
- "title": "SQL Commands",
- "url": "/docs/sql-commands/"
- },
- {
- "title": "SQL Reference",
- "url": "/docs/sql-reference/"
- }
- ],
- "children": [],
- "next_title": "SELECT GROUP BY",
- "next_url": "/docs/select-group-by/",
- "parent": "SQL Commands",
- "previous_title": "SELECT",
- "previous_url": "/docs/select/",
- "relative_path": "_docs/sql-reference/sql-commands/081-select-from.md",
- "title": "SELECT FROM",
- "url": "/docs/select-from/"
- },
- "SELECT GROUP BY": {
- "breadcrumbs": [
- {
- "title": "SQL Commands",
- "url": "/docs/sql-commands/"
- },
- {
- "title": "SQL Reference",
- "url": "/docs/sql-reference/"
- }
- ],
- "children": [],
- "next_title": "SELECT HAVING",
- "next_url": "/docs/select-having/",
- "parent": "SQL Commands",
- "previous_title": "SELECT FROM",
- "previous_url": "/docs/select-from/",
- "relative_path": "_docs/sql-reference/sql-commands/082-select-group-by.md",
- "title": "SELECT GROUP BY",
- "url": "/docs/select-group-by/"
- },
- "SELECT HAVING": {
- "breadcrumbs": [
- {
- "title": "SQL Commands",
- "url": "/docs/sql-commands/"
- },
- {
- "title": "SQL Reference",
- "url": "/docs/sql-reference/"
- }
- ],
- "children": [],
- "next_title": "SELECT LIMIT",
- "next_url": "/docs/select-limit/",
- "parent": "SQL Commands",
- "previous_title": "SELECT GROUP BY",
- "previous_url": "/docs/select-group-by/",
- "relative_path": "_docs/sql-reference/sql-commands/083-select-having.md",
- "title": "SELECT HAVING",
- "url": "/docs/select-having/"
- },
- "SELECT LIMIT": {
- "breadcrumbs": [
- {
- "title": "SQL Commands",
- "url": "/docs/sql-commands/"
- },
- {
- "title": "SQL Reference",
- "url": "/docs/sql-reference/"
- }
- ],
- "children": [],
- "next_title": "SELECT OFFSET",
- "next_url": "/docs/select-offset/",
- "parent": "SQL Commands",
- "previous_title": "SELECT HAVING",
- "previous_url": "/docs/select-having/",
- "relative_path": "_docs/sql-reference/sql-commands/084-select-limit.md",
- "title": "SELECT LIMIT",
- "url": "/docs/select-limit/"
- },
- "SELECT OFFSET": {
- "breadcrumbs": [
- {
- "title": "SQL Commands",
- "url": "/docs/sql-commands/"
- },
- {
- "title": "SQL Reference",
- "url": "/docs/sql-reference/"
- }
- ],
- "children": [],
- "next_title": "SELECT ORDER BY",
- "next_url": "/docs/select-order-by/",
- "parent": "SQL Commands",
- "previous_title": "SELECT LIMIT",
- "previous_url": "/docs/select-limit/",
- "relative_path": "_docs/sql-reference/sql-commands/085-select-offset.md",
- "title": "SELECT OFFSET",
- "url": "/docs/select-offset/"
- },
- "SELECT ORDER BY": {
- "breadcrumbs": [
- {
- "title": "SQL Commands",
- "url": "/docs/sql-commands/"
- },
- {
- "title": "SQL Reference",
- "url": "/docs/sql-reference/"
- }
- ],
- "children": [],
- "next_title": "SELECT UNION",
- "next_url": "/docs/select-union/",
- "parent": "SQL Commands",
- "previous_title": "SELECT OFFSET",
- "previous_url": "/docs/select-offset/",
- "relative_path": "_docs/sql-reference/sql-commands/086-select-order-by.md",
- "title": "SELECT ORDER BY",
- "url": "/docs/select-order-by/"
- },
- "SELECT UNION": {
- "breadcrumbs": [
- {
- "title": "SQL Commands",
- "url": "/docs/sql-commands/"
- },
- {
- "title": "SQL Reference",
- "url": "/docs/sql-reference/"
- }
- ],
- "children": [],
- "next_title": "SELECT WHERE",
- "next_url": "/docs/select-where/",
- "parent": "SQL Commands",
- "previous_title": "SELECT ORDER BY",
- "previous_url": "/docs/select-order-by/",
- "relative_path": "_docs/sql-reference/sql-commands/087-select-union.md",
- "title": "SELECT UNION",
- "url": "/docs/select-union/"
- },
- "SELECT WHERE": {
- "breadcrumbs": [
- {
- "title": "SQL Commands",
- "url": "/docs/sql-commands/"
- },
- {
- "title": "SQL Reference",
- "url": "/docs/sql-reference/"
- }
- ],
- "children": [],
- "next_title": "SELECT WITH",
- "next_url": "/docs/select-with/",
- "parent": "SQL Commands",
- "previous_title": "SELECT UNION",
- "previous_url": "/docs/select-union/",
- "relative_path": "_docs/sql-reference/sql-commands/088-select-where.md",
- "title": "SELECT WHERE",
- "url": "/docs/select-where/"
- },
- "SELECT WITH": {
- "breadcrumbs": [
- {
- "title": "SQL Commands",
- "url": "/docs/sql-commands/"
- },
- {
- "title": "SQL Reference",
- "url": "/docs/sql-reference/"
- }
- ],
- "children": [],
- "next_title": "SHOW DATABASES and SHOW SCHEMAS",
- "next_url": "/docs/show-databases-and-show-schemas/",
- "parent": "SQL Commands",
- "previous_title": "SELECT WHERE",
- "previous_url": "/docs/select-where/",
- "relative_path": "_docs/sql-reference/sql-commands/089-select-with.md",
- "title": "SELECT WITH",
- "url": "/docs/select-with/"
- },
"SHOW DATABASES and SHOW SCHEMAS": {
"breadcrumbs": [
{
@@ -7297,8 +7234,8 @@
"next_title": "SHOW FILES",
"next_url": "/docs/show-files/",
"parent": "SQL Commands",
- "previous_title": "SELECT WITH",
- "previous_url": "/docs/select-with/",
+ "previous_title": "WITH Clause",
+ "previous_url": "/docs/with-clause/",
"relative_path": "_docs/sql-reference/sql-commands/090-show-databases-and-show-schemas.md",
"title": "SHOW DATABASES and SHOW SCHEMAS",
"url": "/docs/show-databases-and-show-schemas/"
@@ -7533,8 +7470,8 @@
}
],
"children": [],
- "next_title": "SELECT FROM",
- "next_url": "/docs/select-from/",
+ "next_title": "FROM Clause",
+ "next_url": "/docs/from-clause/",
"parent": "SQL Commands",
"previous_title": "EXPLAIN",
"previous_url": "/docs/explain/",
@@ -7554,14 +7491,14 @@
}
],
"children": [],
- "next_title": "SELECT GROUP BY",
- "next_url": "/docs/select-group-by/",
+ "next_title": "GROUP BY Clause",
+ "next_url": "/docs/group-by-clause/",
"parent": "SQL Commands",
"previous_title": "SELECT",
"previous_url": "/docs/select/",
- "relative_path": "_docs/sql-reference/sql-commands/081-select-from.md",
- "title": "SELECT FROM",
- "url": "/docs/select-from/"
+ "relative_path": "_docs/sql-reference/sql-commands/081-from-clause.md",
+ "title": "FROM Clause",
+ "url": "/docs/from-clause/"
},
{
"breadcrumbs": [
@@ -7575,14 +7512,14 @@
}
],
"children": [],
- "next_title": "SELECT HAVING",
- "next_url": "/docs/select-having/",
+ "next_title": "HAVING Clause",
+ "next_url": "/docs/having-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT FROM",
- "previous_url": "/docs/select-from/",
- "relative_path": "_docs/sql-reference/sql-commands/082-select-group-by.md",
- "title": "SELECT GROUP BY",
- "url": "/docs/select-group-by/"
+ "previous_title": "FROM Clause",
+ "previous_url": "/docs/from-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/082-group-by-clause.md",
+ "title": "GROUP BY Clause",
+ "url": "/docs/group-by-clause/"
},
{
"breadcrumbs": [
@@ -7596,14 +7533,14 @@
}
],
"children": [],
- "next_title": "SELECT LIMIT",
- "next_url": "/docs/select-limit/",
+ "next_title": "LIMIT Clause",
+ "next_url": "/docs/limit-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT GROUP BY",
- "previous_url": "/docs/select-group-by/",
- "relative_path": "_docs/sql-reference/sql-commands/083-select-having.md",
- "title": "SELECT HAVING",
- "url": "/docs/select-having/"
+ "previous_title": "GROUP BY Clause",
+ "previous_url": "/docs/group-by-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/083-having-clause.md",
+ "title": "HAVING Clause",
+ "url": "/docs/having-clause/"
},
{
"breadcrumbs": [
@@ -7617,14 +7554,14 @@
}
],
"children": [],
- "next_title": "SELECT OFFSET",
- "next_url": "/docs/select-offset/",
+ "next_title": "OFFSET Clause",
+ "next_url": "/docs/offset-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT HAVING",
- "previous_url": "/docs/select-having/",
- "relative_path": "_docs/sql-reference/sql-commands/084-select-limit.md",
- "title": "SELECT LIMIT",
- "url": "/docs/select-limit/"
+ "previous_title": "HAVING Clause",
+ "previous_url": "/docs/having-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/084-limit-clause.md",
+ "title": "LIMIT Clause",
+ "url": "/docs/limit-clause/"
},
{
"breadcrumbs": [
@@ -7638,14 +7575,14 @@
}
],
"children": [],
- "next_title": "SELECT ORDER BY",
- "next_url": "/docs/select-order-by/",
+ "next_title": "ORDER BY Clause",
+ "next_url": "/docs/order-by-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT LIMIT",
- "previous_url": "/docs/select-limit/",
- "relative_path": "_docs/sql-reference/sql-commands/085-select-offset.md",
- "title": "SELECT OFFSET",
- "url": "/docs/select-offset/"
+ "previous_title": "LIMIT Clause",
+ "previous_url": "/docs/limit-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/085-offset-clause.md",
+ "title": "OFFSET Clause",
+ "url": "/docs/offset-clause/"
},
{
"breadcrumbs": [
@@ -7659,14 +7596,14 @@
}
],
"children": [],
- "next_title": "SELECT UNION",
- "next_url": "/docs/select-union/",
+ "next_title": "UNION Set Operator",
+ "next_url": "/docs/union-set-operator/",
"parent": "SQL Commands",
- "previous_title": "SELECT OFFSET",
- "previous_url": "/docs/select-offset/",
- "relative_path": "_docs/sql-reference/sql-commands/086-select-order-by.md",
- "title": "SELECT ORDER BY",
- "url": "/docs/select-order-by/"
+ "previous_title": "OFFSET Clause",
+ "previous_url": "/docs/offset-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/086-order-by-clause.md",
+ "title": "ORDER BY Clause",
+ "url": "/docs/order-by-clause/"
},
{
"breadcrumbs": [
@@ -7680,14 +7617,14 @@
}
],
"children": [],
- "next_title": "SELECT WHERE",
- "next_url": "/docs/select-where/",
+ "next_title": "WHERE Clause",
+ "next_url": "/docs/where-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT ORDER BY",
- "previous_url": "/docs/select-order-by/",
- "relative_path": "_docs/sql-reference/sql-commands/087-select-union.md",
- "title": "SELECT UNION",
- "url": "/docs/select-union/"
+ "previous_title": "ORDER BY Clause",
+ "previous_url": "/docs/order-by-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/087-union-set-operator.md",
+ "title": "UNION Set Operator",
+ "url": "/docs/union-set-operator/"
},
{
"breadcrumbs": [
@@ -7701,14 +7638,14 @@
}
],
"children": [],
- "next_title": "SELECT WITH",
- "next_url": "/docs/select-with/",
+ "next_title": "WITH Clause",
+ "next_url": "/docs/with-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT UNION",
- "previous_url": "/docs/select-union/",
- "relative_path": "_docs/sql-reference/sql-commands/088-select-where.md",
- "title": "SELECT WHERE",
- "url": "/docs/select-where/"
+ "previous_title": "UNION Set Operator",
+ "previous_url": "/docs/union-set-operator/",
+ "relative_path": "_docs/sql-reference/sql-commands/088-where-clause.md",
+ "title": "WHERE Clause",
+ "url": "/docs/where-clause/"
},
{
"breadcrumbs": [
@@ -7725,11 +7662,11 @@
"next_title": "SHOW DATABASES and SHOW SCHEMAS",
"next_url": "/docs/show-databases-and-show-schemas/",
"parent": "SQL Commands",
- "previous_title": "SELECT WHERE",
- "previous_url": "/docs/select-where/",
- "relative_path": "_docs/sql-reference/sql-commands/089-select-with.md",
- "title": "SELECT WITH",
- "url": "/docs/select-with/"
+ "previous_title": "WHERE Clause",
+ "previous_url": "/docs/where-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/089-with-clause.md",
+ "title": "WITH Clause",
+ "url": "/docs/with-clause/"
},
{
"breadcrumbs": [
@@ -7746,8 +7683,8 @@
"next_title": "SHOW FILES",
"next_url": "/docs/show-files/",
"parent": "SQL Commands",
- "previous_title": "SELECT WITH",
- "previous_url": "/docs/select-with/",
+ "previous_title": "WITH Clause",
+ "previous_url": "/docs/with-clause/",
"relative_path": "_docs/sql-reference/sql-commands/090-show-databases-and-show-schemas.md",
"title": "SHOW DATABASES and SHOW SCHEMAS",
"url": "/docs/show-databases-and-show-schemas/"
@@ -8674,8 +8611,8 @@
}
],
"children": [],
- "next_title": "SELECT FROM",
- "next_url": "/docs/select-from/",
+ "next_title": "FROM Clause",
+ "next_url": "/docs/from-clause/",
"parent": "SQL Commands",
"previous_title": "EXPLAIN",
"previous_url": "/docs/explain/",
@@ -8695,14 +8632,14 @@
}
],
"children": [],
- "next_title": "SELECT GROUP BY",
- "next_url": "/docs/select-group-by/",
+ "next_title": "GROUP BY Clause",
+ "next_url": "/docs/group-by-clause/",
"parent": "SQL Commands",
"previous_title": "SELECT",
"previous_url": "/docs/select/",
- "relative_path": "_docs/sql-reference/sql-commands/081-select-from.md",
- "title": "SELECT FROM",
- "url": "/docs/select-from/"
+ "relative_path": "_docs/sql-reference/sql-commands/081-from-clause.md",
+ "title": "FROM Clause",
+ "url": "/docs/from-clause/"
},
{
"breadcrumbs": [
@@ -8716,14 +8653,14 @@
}
],
"children": [],
- "next_title": "SELECT HAVING",
- "next_url": "/docs/select-having/",
+ "next_title": "HAVING Clause",
+ "next_url": "/docs/having-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT FROM",
- "previous_url": "/docs/select-from/",
- "relative_path": "_docs/sql-reference/sql-commands/082-select-group-by.md",
- "title": "SELECT GROUP BY",
- "url": "/docs/select-group-by/"
+ "previous_title": "FROM Clause",
+ "previous_url": "/docs/from-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/082-group-by-clause.md",
+ "title": "GROUP BY Clause",
+ "url": "/docs/group-by-clause/"
},
{
"breadcrumbs": [
@@ -8737,14 +8674,14 @@
}
],
"children": [],
- "next_title": "SELECT LIMIT",
- "next_url": "/docs/select-limit/",
+ "next_title": "LIMIT Clause",
+ "next_url": "/docs/limit-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT GROUP BY",
- "previous_url": "/docs/select-group-by/",
- "relative_path": "_docs/sql-reference/sql-commands/083-select-having.md",
- "title": "SELECT HAVING",
- "url": "/docs/select-having/"
+ "previous_title": "GROUP BY Clause",
+ "previous_url": "/docs/group-by-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/083-having-clause.md",
+ "title": "HAVING Clause",
+ "url": "/docs/having-clause/"
},
{
"breadcrumbs": [
@@ -8758,14 +8695,14 @@
}
],
"children": [],
- "next_title": "SELECT OFFSET",
- "next_url": "/docs/select-offset/",
+ "next_title": "OFFSET Clause",
+ "next_url": "/docs/offset-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT HAVING",
- "previous_url": "/docs/select-having/",
- "relative_path": "_docs/sql-reference/sql-commands/084-select-limit.md",
- "title": "SELECT LIMIT",
- "url": "/docs/select-limit/"
+ "previous_title": "HAVING Clause",
+ "previous_url": "/docs/having-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/084-limit-clause.md",
+ "title": "LIMIT Clause",
+ "url": "/docs/limit-clause/"
},
{
"breadcrumbs": [
@@ -8779,14 +8716,14 @@
}
],
"children": [],
- "next_title": "SELECT ORDER BY",
- "next_url": "/docs/select-order-by/",
+ "next_title": "ORDER BY Clause",
+ "next_url": "/docs/order-by-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT LIMIT",
- "previous_url": "/docs/select-limit/",
- "relative_path": "_docs/sql-reference/sql-commands/085-select-offset.md",
- "title": "SELECT OFFSET",
- "url": "/docs/select-offset/"
+ "previous_title": "LIMIT Clause",
+ "previous_url": "/docs/limit-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/085-offset-clause.md",
+ "title": "OFFSET Clause",
+ "url": "/docs/offset-clause/"
},
{
"breadcrumbs": [
@@ -8800,14 +8737,14 @@
}
],
"children": [],
- "next_title": "SELECT UNION",
- "next_url": "/docs/select-union/",
+ "next_title": "UNION Set Operator",
+ "next_url": "/docs/union-set-operator/",
"parent": "SQL Commands",
- "previous_title": "SELECT OFFSET",
- "previous_url": "/docs/select-offset/",
- "relative_path": "_docs/sql-reference/sql-commands/086-select-order-by.md",
- "title": "SELECT ORDER BY",
- "url": "/docs/select-order-by/"
+ "previous_title": "OFFSET Clause",
+ "previous_url": "/docs/offset-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/086-order-by-clause.md",
+ "title": "ORDER BY Clause",
+ "url": "/docs/order-by-clause/"
},
{
"breadcrumbs": [
@@ -8821,14 +8758,14 @@
}
],
"children": [],
- "next_title": "SELECT WHERE",
- "next_url": "/docs/select-where/",
+ "next_title": "WHERE Clause",
+ "next_url": "/docs/where-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT ORDER BY",
- "previous_url": "/docs/select-order-by/",
- "relative_path": "_docs/sql-reference/sql-commands/087-select-union.md",
- "title": "SELECT UNION",
- "url": "/docs/select-union/"
+ "previous_title": "ORDER BY Clause",
+ "previous_url": "/docs/order-by-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/087-union-set-operator.md",
+ "title": "UNION Set Operator",
+ "url": "/docs/union-set-operator/"
},
{
"breadcrumbs": [
@@ -8842,14 +8779,14 @@
}
],
"children": [],
- "next_title": "SELECT WITH",
- "next_url": "/docs/select-with/",
+ "next_title": "WITH Clause",
+ "next_url": "/docs/with-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT UNION",
- "previous_url": "/docs/select-union/",
- "relative_path": "_docs/sql-reference/sql-commands/088-select-where.md",
- "title": "SELECT WHERE",
- "url": "/docs/select-where/"
+ "previous_title": "UNION Set Operator",
+ "previous_url": "/docs/union-set-operator/",
+ "relative_path": "_docs/sql-reference/sql-commands/088-where-clause.md",
+ "title": "WHERE Clause",
+ "url": "/docs/where-clause/"
},
{
"breadcrumbs": [
@@ -8866,11 +8803,11 @@
"next_title": "SHOW DATABASES and SHOW SCHEMAS",
"next_url": "/docs/show-databases-and-show-schemas/",
"parent": "SQL Commands",
- "previous_title": "SELECT WHERE",
- "previous_url": "/docs/select-where/",
- "relative_path": "_docs/sql-reference/sql-commands/089-select-with.md",
- "title": "SELECT WITH",
- "url": "/docs/select-with/"
+ "previous_title": "WHERE Clause",
+ "previous_url": "/docs/where-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/089-with-clause.md",
+ "title": "WITH Clause",
+ "url": "/docs/with-clause/"
},
{
"breadcrumbs": [
@@ -8887,8 +8824,8 @@
"next_title": "SHOW FILES",
"next_url": "/docs/show-files/",
"parent": "SQL Commands",
- "previous_title": "SELECT WITH",
- "previous_url": "/docs/select-with/",
+ "previous_title": "WITH Clause",
+ "previous_url": "/docs/with-clause/",
"relative_path": "_docs/sql-reference/sql-commands/090-show-databases-and-show-schemas.md",
"title": "SHOW DATABASES and SHOW SCHEMAS",
"url": "/docs/show-databases-and-show-schemas/"
@@ -9898,6 +9835,27 @@
"title": "Tutorials Introduction",
"url": "/docs/tutorials-introduction/"
},
+ "UNION Set Operator": {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "WHERE Clause",
+ "next_url": "/docs/where-clause/",
+ "parent": "SQL Commands",
+ "previous_title": "ORDER BY Clause",
+ "previous_url": "/docs/order-by-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/087-union-set-operator.md",
+ "title": "UNION Set Operator",
+ "url": "/docs/union-set-operator/"
+ },
"USE": {
"breadcrumbs": [
{
@@ -10435,6 +10393,48 @@
"title": "Value Vectors",
"url": "/docs/value-vectors/"
},
+ "WHERE Clause": {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "WITH Clause",
+ "next_url": "/docs/with-clause/",
+ "parent": "SQL Commands",
+ "previous_title": "UNION Set Operator",
+ "previous_url": "/docs/union-set-operator/",
+ "relative_path": "_docs/sql-reference/sql-commands/088-where-clause.md",
+ "title": "WHERE Clause",
+ "url": "/docs/where-clause/"
+ },
+ "WITH Clause": {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "SHOW DATABASES and SHOW SCHEMAS",
+ "next_url": "/docs/show-databases-and-show-schemas/",
+ "parent": "SQL Commands",
+ "previous_title": "WHERE Clause",
+ "previous_url": "/docs/where-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/089-with-clause.md",
+ "title": "WITH Clause",
+ "url": "/docs/with-clause/"
+ },
"What is Apache Drill": {
"breadcrumbs": [
{
@@ -13568,8 +13568,8 @@
}
],
"children": [],
- "next_title": "SELECT FROM",
- "next_url": "/docs/select-from/",
+ "next_title": "FROM Clause",
+ "next_url": "/docs/from-clause/",
"parent": "SQL Commands",
"previous_title": "EXPLAIN",
"previous_url": "/docs/explain/",
@@ -13589,14 +13589,14 @@
}
],
"children": [],
- "next_title": "SELECT GROUP BY",
- "next_url": "/docs/select-group-by/",
+ "next_title": "GROUP BY Clause",
+ "next_url": "/docs/group-by-clause/",
"parent": "SQL Commands",
"previous_title": "SELECT",
"previous_url": "/docs/select/",
- "relative_path": "_docs/sql-reference/sql-commands/081-select-from.md",
- "title": "SELECT FROM",
- "url": "/docs/select-from/"
+ "relative_path": "_docs/sql-reference/sql-commands/081-from-clause.md",
+ "title": "FROM Clause",
+ "url": "/docs/from-clause/"
},
{
"breadcrumbs": [
@@ -13610,14 +13610,14 @@
}
],
"children": [],
- "next_title": "SELECT HAVING",
- "next_url": "/docs/select-having/",
+ "next_title": "HAVING Clause",
+ "next_url": "/docs/having-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT FROM",
- "previous_url": "/docs/select-from/",
- "relative_path": "_docs/sql-reference/sql-commands/082-select-group-by.md",
- "title": "SELECT GROUP BY",
- "url": "/docs/select-group-by/"
+ "previous_title": "FROM Clause",
+ "previous_url": "/docs/from-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/082-group-by-clause.md",
+ "title": "GROUP BY Clause",
+ "url": "/docs/group-by-clause/"
},
{
"breadcrumbs": [
@@ -13631,14 +13631,14 @@
}
],
"children": [],
- "next_title": "SELECT LIMIT",
- "next_url": "/docs/select-limit/",
+ "next_title": "LIMIT Clause",
+ "next_url": "/docs/limit-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT GROUP BY",
- "previous_url": "/docs/select-group-by/",
- "relative_path": "_docs/sql-reference/sql-commands/083-select-having.md",
- "title": "SELECT HAVING",
- "url": "/docs/select-having/"
+ "previous_title": "GROUP BY Clause",
+ "previous_url": "/docs/group-by-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/083-having-clause.md",
+ "title": "HAVING Clause",
+ "url": "/docs/having-clause/"
},
{
"breadcrumbs": [
@@ -13652,14 +13652,14 @@
}
],
"children": [],
- "next_title": "SELECT OFFSET",
- "next_url": "/docs/select-offset/",
+ "next_title": "OFFSET Clause",
+ "next_url": "/docs/offset-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT HAVING",
- "previous_url": "/docs/select-having/",
- "relative_path": "_docs/sql-reference/sql-commands/084-select-limit.md",
- "title": "SELECT LIMIT",
- "url": "/docs/select-limit/"
+ "previous_title": "HAVING Clause",
+ "previous_url": "/docs/having-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/084-limit-clause.md",
+ "title": "LIMIT Clause",
+ "url": "/docs/limit-clause/"
},
{
"breadcrumbs": [
@@ -13673,14 +13673,14 @@
}
],
"children": [],
- "next_title": "SELECT ORDER BY",
- "next_url": "/docs/select-order-by/",
+ "next_title": "ORDER BY Clause",
+ "next_url": "/docs/order-by-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT LIMIT",
- "previous_url": "/docs/select-limit/",
- "relative_path": "_docs/sql-reference/sql-commands/085-select-offset.md",
- "title": "SELECT OFFSET",
- "url": "/docs/select-offset/"
+ "previous_title": "LIMIT Clause",
+ "previous_url": "/docs/limit-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/085-offset-clause.md",
+ "title": "OFFSET Clause",
+ "url": "/docs/offset-clause/"
},
{
"breadcrumbs": [
@@ -13694,14 +13694,14 @@
}
],
"children": [],
- "next_title": "SELECT UNION",
- "next_url": "/docs/select-union/",
+ "next_title": "UNION Set Operator",
+ "next_url": "/docs/union-set-operator/",
"parent": "SQL Commands",
- "previous_title": "SELECT OFFSET",
- "previous_url": "/docs/select-offset/",
- "relative_path": "_docs/sql-reference/sql-commands/086-select-order-by.md",
- "title": "SELECT ORDER BY",
- "url": "/docs/select-order-by/"
+ "previous_title": "OFFSET Clause",
+ "previous_url": "/docs/offset-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/086-order-by-clause.md",
+ "title": "ORDER BY Clause",
+ "url": "/docs/order-by-clause/"
},
{
"breadcrumbs": [
@@ -13715,14 +13715,14 @@
}
],
"children": [],
- "next_title": "SELECT WHERE",
- "next_url": "/docs/select-where/",
+ "next_title": "WHERE Clause",
+ "next_url": "/docs/where-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT ORDER BY",
- "previous_url": "/docs/select-order-by/",
- "relative_path": "_docs/sql-reference/sql-commands/087-select-union.md",
- "title": "SELECT UNION",
- "url": "/docs/select-union/"
+ "previous_title": "ORDER BY Clause",
+ "previous_url": "/docs/order-by-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/087-union-set-operator.md",
+ "title": "UNION Set Operator",
+ "url": "/docs/union-set-operator/"
},
{
"breadcrumbs": [
@@ -13736,14 +13736,14 @@
}
],
"children": [],
- "next_title": "SELECT WITH",
- "next_url": "/docs/select-with/",
+ "next_title": "WITH Clause",
+ "next_url": "/docs/with-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT UNION",
- "previous_url": "/docs/select-union/",
- "relative_path": "_docs/sql-reference/sql-commands/088-select-where.md",
- "title": "SELECT WHERE",
- "url": "/docs/select-where/"
+ "previous_title": "UNION Set Operator",
+ "previous_url": "/docs/union-set-operator/",
+ "relative_path": "_docs/sql-reference/sql-commands/088-where-clause.md",
+ "title": "WHERE Clause",
+ "url": "/docs/where-clause/"
},
{
"breadcrumbs": [
@@ -13760,11 +13760,11 @@
"next_title": "SHOW DATABASES and SHOW SCHEMAS",
"next_url": "/docs/show-databases-and-show-schemas/",
"parent": "SQL Commands",
- "previous_title": "SELECT WHERE",
- "previous_url": "/docs/select-where/",
- "relative_path": "_docs/sql-reference/sql-commands/089-select-with.md",
- "title": "SELECT WITH",
- "url": "/docs/select-with/"
+ "previous_title": "WHERE Clause",
+ "previous_url": "/docs/where-clause/",
+ "relative_path": "_docs/sql-reference/sql-commands/089-with-clause.md",
+ "title": "WITH Clause",
+ "url": "/docs/with-clause/"
},
{
"breadcrumbs": [
@@ -13781,8 +13781,8 @@
"next_title": "SHOW FILES",
"next_url": "/docs/show-files/",
"parent": "SQL Commands",
- "previous_title": "SELECT WITH",
- "previous_url": "/docs/select-with/",
+ "previous_title": "WITH Clause",
+ "previous_url": "/docs/with-clause/",
"relative_path": "_docs/sql-reference/sql-commands/090-show-databases-and-show-schemas.md",
"title": "SHOW DATABASES and SHOW SCHEMAS",
"url": "/docs/show-databases-and-show-schemas/"
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/110-troubleshooting.md
----------------------------------------------------------------------
diff --git a/_docs/110-troubleshooting.md b/_docs/110-troubleshooting.md
old mode 100644
new mode 100755
index e0c4227..4713c17
--- a/_docs/110-troubleshooting.md
+++ b/_docs/110-troubleshooting.md
@@ -12,7 +12,7 @@ You should also know the version of Drill running in the cluster. You can search
### Identify the Foreman
Issue the following query to identify the node running as the Foreman:
- SELECT host FROM sys.drillbits WHERE `current` = true;
+ SELECT host FROM sys.drillbits WHERE `` `current` `` = true;
### Identify the Drill Version
Issue the following query to identify the version of Drill running in your cluster:
@@ -28,7 +28,7 @@ Issue the following command to enable the verbose errors option:
## Troubleshooting
If you have any issues in Drill, search the following list for your issue and apply the suggested solution:
-### Query Parsing Errors
+**Query Parsing Errors**
Symptom:
PARSE ERROR: At line x, column x: ...
@@ -36,7 +36,7 @@ Solution: Verify that you are using valid syntax. See [SQL Reference]({{ site.ba
If you are using common words, they may be reserved words. Make sure to use back ticks
Confirm that you are using back ticks to quote identifiers when using special characters such as back slashes or periods from a file path.
-### Reserved Words
+**Reserved Words**
Symptom:
select count from dfs.drill.`test2.json`;
@@ -48,7 +48,7 @@ Solution: Fix with correct syntax. See [Reserved Keywords]({{ site.baseurl }}/do
select `count` from dfs.drill.`test2.json`;
-### Tables not found
+**Tables not found**
Symptom:
select * from dfs.drill.test2.json;
@@ -68,7 +68,7 @@ Solutions:
* Parquet
* JSON
-### Access nested fields without table name/alias
+**Access nested fields without table name/alias**
Symptom:
select x.y …
@@ -77,7 +77,7 @@ Solution: Add table name or alias to the field reference:
select t.x.y from t
-### Unexpected null values for columns in results
+**Unexpected null values for columns in results**
Symptom: The following type of query returns NULL values:
select t.price from t
@@ -86,7 +86,7 @@ Symptom: The following type of query returns NULL values:
Solution: Drill is schema-less system. Verify that column names are typed correctly.
-### Using functions with incorrect data types
+**Using functions with incorrect data types**
Symptom: Example
@@ -104,7 +104,7 @@ Symptom: Example
Solution: Ensure that the function is invoked with the correct data type parameters. In the example above, c3 is an unsupported date type.
-### Query takes a long time to return
+**Query takes a long time to return**
Symptom: Query takes longer to return than expected.
@@ -114,7 +114,7 @@ Solution: Review the [query profile]({{ site.baseurl }}/docs/query-profiles/) an
* Look at where Drill is currently spending time and try to optimize those operations.
* Confirm that Drill is taking advantage of the nature of your data, including things like partition pruning and projection pushdown.
-### Schema changes**
+**Schema changes**
Symptom:
@@ -125,7 +125,7 @@ Symptom:
Solution: Drill does not fully support schema changes. In this case, you will need to either ensure that your schemas are the same or only select columns that share schema.
-### Timestamps and Timezones other than UTC
+**Timestamps and Timezones other than UTC**
Symptoms: Issues with timestamp and timezone. Illegal instant due to time zone offset transition (America/New_York)
@@ -135,61 +135,61 @@ Solution: Convert data to UTC format. You are most likely trying to import date
`http://www.openkb.info/2015/05/understanding-drills-timestamp-and.html `
-### Unexpected ODBC issues
+**Unexpected ODBC issues**
Symptom: ODBC errors.
Solution: Make sure that the ODBC driver version is compatible with the server version.
Turn on ODBC driver debug logging to better understand failure.
-### Connectivity issues when connecting via ZooKeeper for JDBC/ODBC
+**Connectivity issues when connecting via ZooKeeper for JDBC/ODBC**
Symptom: Client cannot resolve ZooKeeper host names for JDBC/ODBC.
Solution: Ensure that Zookeeper is up and running. Verify that Drill has the correct drill-override.conf settings for the Zookeeper quorum.
-### Metadata queries take a long time to return
+**Metadata queries take a long time to return**
Symptom: Running SHOW databases/schemas/tables hangs (in general any information_schema queries hang).
Solution: Disable incorrectly configured storage plugins or start appropriate services. Check compatibility matrix for the appropriate versions.
-### Unexpected results due to implicit casting
+**Unexpected results due to implicit casting**
Symptom: rill implicitly casts based on order of precedence.
Solution: Review Drill casting behaviors and explicitly cast for the expected results. See [Data Types]({{ site.baseurl }}/docs/handling-different-data-types/).
-### Column alias causes an error
+**Column alias causes an error**
Symptom: Drill is not case sensitive, and you can provide any alias for a column name. However, if the storage type is case sensitive, the alias name may conflict and cause errors.
Solution: Verify that the column alias does not conflict with the storage type. See [Lexical Structures]({{ site.baseurl }}/docs/lexical-structure/#case-sensitivity).
-### List (arrays) contains null
+**List (arrays) contains null**
Symptom: UNSUPPORTED\_OPERATION ERROR: Null values are not supported in lists by default. Please set store.json.all\_text_mode to true to read lists containing nulls. Be advised that this will treat JSON null values as a string containing the word 'null'.
Solution: Change Drill session settings to enable all_text_mode per message.
Avoid selecting fields that are arrays containing nulls.
-### SELECT COUNT (\*) takes a long time to run
+**SELECT COUNT (\*) takes a long time to run**
Solution: In come cases, the underlying storage format does not have a built-in capability to return a count of records in a table. In these cases, Drill will do a full scan of the data to verify the number of records.
-### Tableau issues
+**Tableau issues**
Symptom: You see a lot of error messages in ODBC trace files or the performance is slow.
Solution: Verify that you have installed the TDC file shipped with the ODBC driver.
-### Group by using alias
+**Group by using alias**
Symptom: Invalid column.
Solution: Not supported. Use column name and/or expression directly.
-### Casting a Varchar string to an integer results in an error
+**Casting a Varchar string to an integer results in an error**
Symptom:
@@ -197,7 +197,7 @@ Symptom:
Solution: Per the ANSI SQL specification CAST to INT does not support empty strings. If you want to change this behavior, you can set Drill to use the cast empty string to null behavior. This can be done using the drill.exec.functions.cast_empty_string_to_null SESSION/SYSTEM option.
-### Unexpected exception during fragment initialization
+**Unexpected exception during fragment initialization**
Symptom: The error occurred during the Foreman phase of the query. The error typically occurs due to the following common causes:
@@ -206,7 +206,7 @@ Symptom: The error occurred during the Foreman phase of the query. The error typ
Solution: Enable the verbose errors option and run the query again to see if further insight is provided.
-### Queries running out of memory
+**Queries running out of memory**
Symptom:
@@ -220,7 +220,7 @@ Solution:
* Disable hash aggregation and hash sort for your session
* See [Configuration Options]({{ site.baseurl }}/docs/configuration-options-introduction/)
-### Unclear Error Message
+**Unclear Error Message**
Symptom: Cannot determine issue from error message.
@@ -230,7 +230,7 @@ Solution: Turn on verbose errors.
Determine your currently connected drillbit using select * from sys.drillbits. Then review logs Drill logs from that drillbit.
-### SQLLine error starting Drill in embedded mode
+**SQLLine error starting Drill in embedded mode**
Symptom:
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/configure-drill/070-configuring-user-impersonation.md
----------------------------------------------------------------------
diff --git a/_docs/configure-drill/070-configuring-user-impersonation.md b/_docs/configure-drill/070-configuring-user-impersonation.md
old mode 100644
new mode 100755
index 90e391c..e7d954e
--- a/_docs/configure-drill/070-configuring-user-impersonation.md
+++ b/_docs/configure-drill/070-configuring-user-impersonation.md
@@ -43,7 +43,7 @@ The following table lists the clients, storage plugins, and types of queries tha
</table>
## Impersonation and Views
-You can use views with impersonation to provide granular access to data and protect sensitive information. When you create a view, Drill stores the view definition in a file and suffixes the file with .drill.view. For example, if you create a view named myview, Drill creates a view file named myview.drill.view and saves it in the current workspace or the workspace specified, such as dfs.views.myview. See [CREATE VIEW]({{site.baseurl}}/docs/create-view) Command.
+You can use views with impersonation to provide granular access to data and protect sensitive information. When you create a view, Drill stores the view definition in a file and suffixes the file with .drill.view. For example, if you create a view named myview, Drill creates a view file named myview.drill.view and saves it in the current workspace or the workspace specified, such as dfs.views.myview. See [CREATE VIEW]({{site.baseurl}}/_docs/create-view) Command.
You can create a view and grant read permissions on the view to give other users access to the data that the view references. When a user queries the view, Drill impersonates the view owner to access the underlying data. If the user tries to access the data directory, Drill returns a permission denied error. A user with read access to a view can create new views from the originating view to further restrict access on data.
@@ -79,19 +79,25 @@ After you set this parameter, Drill applies the same permissions on each view cr
## Chained Impersonation
You can configure Drill to allow chained impersonation on views when you enable impersonation in the `drill-override.conf` file. Chained impersonation controls the number of identity transitions that Drill can make when a user queries a view. Each identity transition is equal to one hop.
-You can set the maximum number of hops on views to limit the number of times that Drill can impersonate a different user when a user queries a view. The default maximum number of hops is set at 3. When the maximum number of hops is set to 0, Drill does not allow impersonation chaining, and a user can only read data for which they have direct permission to access. An administrator may set the chain length to 0 to protect highly sensitive data. Only an administrator can change this setting.
+An administrator can set the maximum number of hops on views to limit the number of times that Drill can impersonate a different user when other users query a view. The default maximum number of hops is set at 3. When the maximum number of hops is set to 0, Drill does not allow impersonation chaining, and a user can only read data for which they have direct permission to access. An administrator may set the chain length to 0 to protect highly sensitive data.
The following example depicts a scenario where the maximum hop number is set to 3, and Drill must impersonate three users to access data when Chad queries a view that Jane created:
-
+
-In the previous example, Joe created a view V3 from views that user Frank created. In the following example, Joe created view V3 by joining a view that Frank created with a view that Bob created, thus increasing the number of identity transitions that Drill makes from 3 to 4, which exceeds the maximum hop setting of 3.
+In the previous example, Joe created V3 from the views that user Frank created. In the following example, Joe created V3 by joining a view that Frank created with a view that Bob created.
-In this scenario, when Chad queries Jane’s view Drill returns an error stating that the query cannot complete because the number of hops required to access the data exceeds the maximum hop setting of 3 that is configured.
+
-
+Although V3 was created by joining two different views, the number of hops remains at 3 because Drill does not read the views at the same time. Drill reads V2 first and then reads V1.
-If users encounter this error, you can increase the maximum hop setting to accommodate users running queries on views. When configuring the maximum number of hops that Drill can make, consider that joined views increase the number of identity transitions required for Drill to access the underlying data.
+In the next example, Bob queries V4 which was created by Frank. Frank's view was created from several underlying views. Charlie created V2 by joining Jane's V1 with Kris's V1.2. Kris's V1.2 was created from Amy's V1.1, increasing the complexity of the chaining. Assuming that the hop limit is set at 4, this scenario exceeds the limit.
+
+
+
+When Bob queries Franks’s view, Drill returns an error stating that the query cannot complete because the number of hops required to access the data exceeds the maximum hop setting of 4.
+
+If users encounter this error, the administrator can increase the maximum hop setting to accommodate users running queries on views.
### Configuring Impersonation and Chaining
Chaining is a system-wide setting that applies to all views. Currently, Drill does not provide an option to allow different chain lengths for different views.
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/configure-drill/075-configuring-user-authentication.md
----------------------------------------------------------------------
diff --git a/_docs/configure-drill/075-configuring-user-authentication.md b/_docs/configure-drill/075-configuring-user-authentication.md
index 94a03d0..b617adf 100755
--- a/_docs/configure-drill/075-configuring-user-authentication.md
+++ b/_docs/configure-drill/075-configuring-user-authentication.md
@@ -29,7 +29,7 @@ The following image illustrates the user authentication process in Drill:
### Installing and Configuring PAM
-Install and configure the provided Drill PAM. Drill only supports the PAM provided here. Optionally, you can build and implement a custom authenticator using the instructions under "Implementing and Configuring a Custom Authenticator."
+Install and configure the provided Drill PAM. Drill only supports the PAM provided here. Optionally, you can [build and implement a custom authenticator]({{ site.baseurl }}/docs/configuring-user-authentication/#implementing-and-configuring-a-custom-authenticator).
Complete the following steps to install and configure PAM for Drill:
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/img/user_hops_four.PNG
----------------------------------------------------------------------
diff --git a/_docs/img/user_hops_four.PNG b/_docs/img/user_hops_four.PNG
new file mode 100755
index 0000000..cf9bcd7
Binary files /dev/null and b/_docs/img/user_hops_four.PNG differ
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/img/user_hops_joined_view.PNG
----------------------------------------------------------------------
diff --git a/_docs/img/user_hops_joined_view.PNG b/_docs/img/user_hops_joined_view.PNG
new file mode 100755
index 0000000..cf8bf3b
Binary files /dev/null and b/_docs/img/user_hops_joined_view.PNG differ
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/img/user_hops_no_join.PNG
----------------------------------------------------------------------
diff --git a/_docs/img/user_hops_no_join.PNG b/_docs/img/user_hops_no_join.PNG
new file mode 100755
index 0000000..4f01e17
Binary files /dev/null and b/_docs/img/user_hops_no_join.PNG differ
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/080-select.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/080-select.md b/_docs/sql-reference/sql-commands/080-select.md
old mode 100644
new mode 100755
index edce91c..3e20134
--- a/_docs/sql-reference/sql-commands/080-select.md
+++ b/_docs/sql-reference/sql-commands/080-select.md
@@ -10,7 +10,10 @@ Drill supports the following ANSI standard clauses in the SELECT statement:
* WHERE clause
* GROUP BY clause
* HAVING clause
- * ORDER BY clause (with an optional LIMIT clause)
+ * UNION ALL set operator
+ * ORDER BY clause (with an optional LIMIT clause)
+ * Limit clause
+ * Offset clause
You can use the same SELECT syntax in the following commands:
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/081-from-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/081-from-clause.md b/_docs/sql-reference/sql-commands/081-from-clause.md
new file mode 100755
index 0000000..8f5ab37
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/081-from-clause.md
@@ -0,0 +1,75 @@
+---
+title: "FROM Clause"
+parent: "SQL Commands"
+---
+The FROM clause lists the references (tables, views, and subqueries) that data is selected from. Drill expands the traditional concept of a “table reference” in a standard SQL FROM clause to refer to files and directories in a local or distributed file system.
+
+## Syntax
+The FROM clause supports the following syntax:
+
+ ... FROM table_expression [, …]
+
+## Parameters
+*table_expression*
+
+Includes one or more *table_references* and is typically followed by the WHERE, GROUP BY, ORDER BY, or HAVING clause.
+
+*table_reference*
+
+ with_subquery_table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
+ table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
+ ( subquery ) [ AS ] alias [ ( column_alias [, ...] ) ]
+ table_reference [ ON join_condition ]
+
+ * *with\_subquery\_table_name*
+
+ A table defined by a subquery in the WITH clause.
+
+
+ * *table_name*
+
+ Name of a table or view. In Drill, you can also refer to a file system directory or a specific file.
+
+ * *alias*
+
+ A temporary alternative name for a table or view that provides a convenient shortcut for identifying tables in other parts of a query, such as the WHERE clause. You must supply an alias for a table derived from a subquery. In other table references, aliases are optional. The AS keyword is always optional. Drill does not support the GROUP BY alias.
+
+ * *column_alias*
+
+ A temporary alternative name for a column in a table or view.
+
+ * *subquery*
+
+ A query expression that evaluates to a table. The table exists only for the duration of the query and is typically given a name or alias, though an alias is not required. You can also define column names for tables that derive from subqueries. Naming column aliases is important when you want to join the results of subqueries to other tables and when you want to select or constrain those columns elsewhere in the query. A subquery may contain an ORDER BY clause, but this clause may have no effect if a LIMIT or OFFSET clause is not also specified.
+
+ * *join_type*
+
+ Specifies one of the following join types:
+
+ [INNER] JOIN
+ LEFT [OUTER] JOIN
+ RIGHT [OUTER] JOIN
+ FULL [OUTER] JOIN
+
+ * *ON join_condition*
+
+ A type of join specification where the joining columns are stated as a condition that follows the ON keyword.
+ Example:
+ ` homes join listing on homes.listid=listing.listid and homes.homeid=listing.homeid`
+
+## Join Types
+INNER JOIN
+
+Return matching rows only, based on the join condition or list of joining columns.
+
+OUTER JOIN
+
+Return all of the rows that the equivalent inner join would return plus non-matching rows from the "left" table, "right" table, or both tables. The left table is the first-listed table, and the right table is the second-listed table. The non-matching rows contain NULL values to fill the gaps in the output columns.
+
+## Usage Notes
+ * Joined columns must have comparable data types.
+ * A join with the ON syntax retains both joining columns in its intermediate result set.
+
+
+## Examples
+
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/081-select-from.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/081-select-from.md b/_docs/sql-reference/sql-commands/081-select-from.md
deleted file mode 100644
index acaaa87..0000000
--- a/_docs/sql-reference/sql-commands/081-select-from.md
+++ /dev/null
@@ -1,87 +0,0 @@
----
-title: "SELECT FROM"
-parent: "SQL Commands"
----
-The FROM clause lists the references (tables, views, and subqueries) that data is selected from. Drill expands the traditional concept of a “table reference” in a standard SQL FROM clause to refer to files and directories in a local or distributed file system.
-
-## Syntax
-The FROM clause supports the following syntax:
-
- ... FROM table_expression [, …]
-
-## Parameters
-*table_expression*
-
-Includes one or more *table_references* and is typically followed by the WHERE, GROUP BY, ORDER BY, or HAVING clause.
-
-*table_reference*
-
- with_subquery_table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
- table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
- ( subquery ) [ AS ] alias [ ( column_alias [, ...] ) ]
- table_reference [ ON join_condition ]
-
- * *with\_subquery\_table_name*
-
- A table defined by a subquery in the WITH clause.
-
-
- * *table_name*
-
- Name of a table or view. In Drill, you can also refer to a file system directory or a specific file.
-
- * *alias*
-
- A temporary alternative name for a table or view that provides a convenient shortcut for identifying tables in other parts of a query, such as the WHERE clause. You must supply an alias for a table derived from a subquery. In other table references, aliases are optional. The AS keyword is always optional. Drill does not support the GROUP BY alias.
-
- * *column_alias*
-
- A temporary alternative name for a column in a table or view.
-
- * *subquery*
-
- A query expression that evaluates to a table. The table exists only for the duration of the query and is typically given a name or alias, though an alias is not required. You can also define column names for tables that derive from subqueries. Naming column aliases is important when you want to join the results of subqueries to other tables and when you want to select or constrain those columns elsewhere in the query. A subquery may contain an ORDER BY clause, but this clause may have no effect if a LIMIT or OFFSET clause is not also specified.
-
- * *join_type*
-
- Specifies one of the following join types:
-
- [INNER] JOIN
- LEFT [OUTER] JOIN
- RIGHT [OUTER] JOIN
- FULL [OUTER] JOIN
-
- * *ON join_condition*
-
- A type of join specification where the joining columns are stated as a condition that follows the ON keyword.
- Example:
- ` homes join listing on homes.listid=listing.listid and homes.homeid=listing.homeid`
-
-## Join Types
-INNER JOIN
-
-Return matching rows only, based on the join condition or list of joining columns.
-
-OUTER JOIN
-
-Return all of the rows that the equivalent inner join would return plus non-matching rows from the "left" table, "right" table, or both tables. The left table is the first-listed table, and the right table is the second-listed table. The non-matching rows contain NULL values to fill the gaps in the output columns.
-
-## Usage Notes
- * Joined columns must have comparable data types.
- * A join with the ON syntax retains both joining columns in its intermediate result set.
-
-
-## Examples
-The following query uses a workspace named `dfw.views` and joins a view named “custview” with a hive table named “orders” to determine sales for each membership type:
-
- 0: jdbc:drill:> select membership, sum(order_total) as sales from hive.orders, custview
- where orders.cust_id=custview.cust_id
- group by membership order by 2;
- +------------+------------+
- | membership | sales |
- +------------+------------+
- | "basic" | 380665 |
- | "silver" | 708438 |
- | "gold" | 2787682 |
- +------------+------------+
- 3 rows selected
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/082-group-by-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/082-group-by-clause.md b/_docs/sql-reference/sql-commands/082-group-by-clause.md
new file mode 100755
index 0000000..9f09220
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/082-group-by-clause.md
@@ -0,0 +1,51 @@
+---
+title: "GROUP BY Clause"
+parent: "SQL Commands"
+---
+The GROUP BY clause identifies the grouping columns for the query. You typically use a GROUP BY clause in conjunction with an aggregate expression. Grouping columns must be declared when the query computes aggregates with standard functions such as SUM, AVG, and COUNT. Currently, Drill does not support grouping on aliases.
+
+
+## Syntax
+The GROUP BY clause supports the following syntax:
+
+
+ GROUP BY expression [, ...]
+
+
+## Parameters
+*column_name*
+
+Must be a column from the current scope of the query. For example, if a GROUP BY clause is in a subquery, it cannot refer to columns in the outer query.
+
+*expression*
+
+The list of columns or expressions must match the list of non-aggregate expressions in the select list of the query.
+
+
+## Usage Notes
+*SelectItems* in the SELECT statement with a GROUP BY clause can only contain aggregates or grouping columns.
+
+
+## Examples
+The following query returns sales totals grouped by month:
+
+ 0: jdbc:drill:> select `month`, sum(order_total)
+ from orders group by `month` order by 2 desc;
+ +------------+------------+
+ | month | EXPR$1 |
+ +------------+------------+
+ | June | 950481 |
+ | May | 947796 |
+ | March | 836809 |
+ | April | 807291 |
+ | July | 757395 |
+ | October | 676236 |
+ | August | 572269 |
+ | February | 532901 |
+ | September | 373100 |
+ | January | 346536 |
+ +------------+------------+
+
+
+
+
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/082-select-group-by.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/082-select-group-by.md b/_docs/sql-reference/sql-commands/082-select-group-by.md
deleted file mode 100644
index a1f799c..0000000
--- a/_docs/sql-reference/sql-commands/082-select-group-by.md
+++ /dev/null
@@ -1,51 +0,0 @@
----
-title: "SELECT GROUP BY"
-parent: "SQL Commands"
----
-The GROUP BY clause identifies the grouping columns for the query. You typically use a GROUP BY clause in conjunction with an aggregate expression. Grouping columns must be declared when the query computes aggregates with standard functions such as SUM, AVG, and COUNT. Currently, Drill does not support grouping on aliases.
-
-
-## Syntax
-The GROUP BY clause supports the following syntax:
-
-
- GROUP BY expression [, ...]
-
-
-## Parameters
-*column_name*
-
-Must be a column from the current scope of the query. For example, if a GROUP BY clause is in a subquery, it cannot refer to columns in the outer query.
-
-*expression*
-
-The list of columns or expressions must match the list of non-aggregate expressions in the select list of the query.
-
-
-## Usage Notes
-*SelectItems* in the SELECT statement with a GROUP BY clause can only contain aggregates or grouping columns.
-
-
-## Examples
-The following query returns sales totals grouped by month:
-
- 0: jdbc:drill:> select `month`, sum(order_total)
- from orders group by `month` order by 2 desc;
- +------------+------------+
- | month | EXPR$1 |
- +------------+------------+
- | June | 950481 |
- | May | 947796 |
- | March | 836809 |
- | April | 807291 |
- | July | 757395 |
- | October | 676236 |
- | August | 572269 |
- | February | 532901 |
- | September | 373100 |
- | January | 346536 |
- +------------+------------+
-
-
-
-
http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/083-having-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/083-having-clause.md b/_docs/sql-reference/sql-commands/083-having-clause.md
new file mode 100755
index 0000000..1a079b0
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/083-having-clause.md
@@ -0,0 +1,51 @@
+---
+title: "HAVING Clause"
+parent: "SQL Commands"
+---
+The HAVING clause filters group rows created by the GROUP BY clause. The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list. If there is no GROUP BY clause, the HAVING clause is applied to the entire result as a single group. The SELECT clause cannot refer directly to any column that does not have a GROUP BY clause.
+
+## Syntax
+The HAVING clause supports the following syntax:
+
+`[ HAVING boolean_expression ]`
+
+## Expression
+A *boolean expression* can include one or more of the following operators:
+
+ * AND
+ * OR
+ * NOT
+ * IS NULL
+ * IS NOT NULL
+ * LIKE
+ * BETWEEN
+ * IN
+ * Comparison operators
+ * Quantified comparison operators
+
+## Usage Notes
+ * Any column referenced in a HAVING clause must be either a grouping column or a column that refers to the result of an aggregate function.
+ * In a HAVING clause, you cannot specify:
+ * An alias that was defined in the select list. You must repeat the original, unaliased expression.
+ * An ordinal number that refers to a select list item. Only the GROUP BY and ORDER BY clauses accept ordinal numbers.
+
+## Examples
+The following example query uses the HAVING clause to constrain an aggregate result. Drill queries the `dfs.clicks workspace` and returns the total number of clicks for devices that indicate high click-throughs:
+
+ 0: jdbc:drill:> select t.user_info.device, count(*) from \`clicks/clicks.json\` t
+ group by t.user_info.device having count(*) > 1000;
+
+ +------------+------------+
+ | EXPR$0 | EXPR$1 |
+ +------------+------------+
+ | IOS5 | 11814 |
+ | AOS4.2 | 5986 |
+ | IOS6 | 4464 |
+ | IOS7 | 3135 |
+ | AOS4.4 | 1562 |
+ | AOS4.3 | 3039 |
+ +------------+------------+
+
+The aggregate is a count of the records for each different mobile device in the clickstream data. Only the activity for the devices that registered more than 1000 transactions qualify for the result set.
+
+
[3/3] drill git commit: Bridget's SQL command updates
Posted by ts...@apache.org.
Bridget's SQL command updates
Project: http://git-wip-us.apache.org/repos/asf/drill/repo
Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/1e05eb3e
Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/1e05eb3e
Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/1e05eb3e
Branch: refs/heads/gh-pages
Commit: 1e05eb3e28bd2b37cdbcd1b1bb0d3dc28ccc8fae
Parents: a03aa49
Author: Kristine Hahn <kh...@maprtech.com>
Authored: Mon Jun 1 18:27:42 2015 -0700
Committer: Kristine Hahn <kh...@maprtech.com>
Committed: Mon Jun 1 18:27:42 2015 -0700
----------------------------------------------------------------------
_data/docs.json | 124 ++++++++++++++++---
_docs/sql-reference/sql-commands/079-select.md | 79 ++++++++++++
.../sql-commands/080-select-list.md | 71 +++++++++++
_docs/sql-reference/sql-commands/080-select.md | 79 ------------
.../sql-commands/081-from-clause.md | 49 +++-----
.../sql-commands/083-having-clause.md | 2 +-
.../sql-commands/084-limit-clause.md | 8 +-
.../sql-commands/085-offset-clause.md | 5 +-
.../sql-commands/087-union-set-operator.md | 7 +-
.../sql-commands/088-where-clause.md | 10 +-
.../sql-commands/089-with-clause.md | 18 +--
11 files changed, 295 insertions(+), 157 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/drill/blob/1e05eb3e/_data/docs.json
----------------------------------------------------------------------
diff --git a/_data/docs.json b/_data/docs.json
index f0a8fb6..5df345e 100644
--- a/_data/docs.json
+++ b/_data/docs.json
@@ -2968,8 +2968,8 @@
"next_title": "GROUP BY Clause",
"next_url": "/docs/group-by-clause/",
"parent": "SQL Commands",
- "previous_title": "SELECT",
- "previous_url": "/docs/select/",
+ "previous_title": "SELECT List",
+ "previous_url": "/docs/select-list/",
"relative_path": "_docs/sql-reference/sql-commands/081-from-clause.md",
"title": "FROM Clause",
"url": "/docs/from-clause/"
@@ -7210,15 +7210,36 @@
}
],
"children": [],
- "next_title": "FROM Clause",
- "next_url": "/docs/from-clause/",
+ "next_title": "SELECT List",
+ "next_url": "/docs/select-list/",
"parent": "SQL Commands",
"previous_title": "EXPLAIN",
"previous_url": "/docs/explain/",
- "relative_path": "_docs/sql-reference/sql-commands/080-select.md",
+ "relative_path": "_docs/sql-reference/sql-commands/079-select.md",
"title": "SELECT",
"url": "/docs/select/"
},
+ "SELECT List": {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "FROM Clause",
+ "next_url": "/docs/from-clause/",
+ "parent": "SQL Commands",
+ "previous_title": "SELECT",
+ "previous_url": "/docs/select/",
+ "relative_path": "_docs/sql-reference/sql-commands/080-select-list.md",
+ "title": "SELECT List",
+ "url": "/docs/select-list/"
+ },
"SHOW DATABASES and SHOW SCHEMAS": {
"breadcrumbs": [
{
@@ -7470,12 +7491,12 @@
}
],
"children": [],
- "next_title": "FROM Clause",
- "next_url": "/docs/from-clause/",
+ "next_title": "SELECT List",
+ "next_url": "/docs/select-list/",
"parent": "SQL Commands",
"previous_title": "EXPLAIN",
"previous_url": "/docs/explain/",
- "relative_path": "_docs/sql-reference/sql-commands/080-select.md",
+ "relative_path": "_docs/sql-reference/sql-commands/079-select.md",
"title": "SELECT",
"url": "/docs/select/"
},
@@ -7491,11 +7512,32 @@
}
],
"children": [],
- "next_title": "GROUP BY Clause",
- "next_url": "/docs/group-by-clause/",
+ "next_title": "FROM Clause",
+ "next_url": "/docs/from-clause/",
"parent": "SQL Commands",
"previous_title": "SELECT",
"previous_url": "/docs/select/",
+ "relative_path": "_docs/sql-reference/sql-commands/080-select-list.md",
+ "title": "SELECT List",
+ "url": "/docs/select-list/"
+ },
+ {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "GROUP BY Clause",
+ "next_url": "/docs/group-by-clause/",
+ "parent": "SQL Commands",
+ "previous_title": "SELECT List",
+ "previous_url": "/docs/select-list/",
"relative_path": "_docs/sql-reference/sql-commands/081-from-clause.md",
"title": "FROM Clause",
"url": "/docs/from-clause/"
@@ -8611,12 +8653,12 @@
}
],
"children": [],
- "next_title": "FROM Clause",
- "next_url": "/docs/from-clause/",
+ "next_title": "SELECT List",
+ "next_url": "/docs/select-list/",
"parent": "SQL Commands",
"previous_title": "EXPLAIN",
"previous_url": "/docs/explain/",
- "relative_path": "_docs/sql-reference/sql-commands/080-select.md",
+ "relative_path": "_docs/sql-reference/sql-commands/079-select.md",
"title": "SELECT",
"url": "/docs/select/"
},
@@ -8632,11 +8674,32 @@
}
],
"children": [],
- "next_title": "GROUP BY Clause",
- "next_url": "/docs/group-by-clause/",
+ "next_title": "FROM Clause",
+ "next_url": "/docs/from-clause/",
"parent": "SQL Commands",
"previous_title": "SELECT",
"previous_url": "/docs/select/",
+ "relative_path": "_docs/sql-reference/sql-commands/080-select-list.md",
+ "title": "SELECT List",
+ "url": "/docs/select-list/"
+ },
+ {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "GROUP BY Clause",
+ "next_url": "/docs/group-by-clause/",
+ "parent": "SQL Commands",
+ "previous_title": "SELECT List",
+ "previous_url": "/docs/select-list/",
"relative_path": "_docs/sql-reference/sql-commands/081-from-clause.md",
"title": "FROM Clause",
"url": "/docs/from-clause/"
@@ -13568,12 +13631,12 @@
}
],
"children": [],
- "next_title": "FROM Clause",
- "next_url": "/docs/from-clause/",
+ "next_title": "SELECT List",
+ "next_url": "/docs/select-list/",
"parent": "SQL Commands",
"previous_title": "EXPLAIN",
"previous_url": "/docs/explain/",
- "relative_path": "_docs/sql-reference/sql-commands/080-select.md",
+ "relative_path": "_docs/sql-reference/sql-commands/079-select.md",
"title": "SELECT",
"url": "/docs/select/"
},
@@ -13589,11 +13652,32 @@
}
],
"children": [],
- "next_title": "GROUP BY Clause",
- "next_url": "/docs/group-by-clause/",
+ "next_title": "FROM Clause",
+ "next_url": "/docs/from-clause/",
"parent": "SQL Commands",
"previous_title": "SELECT",
"previous_url": "/docs/select/",
+ "relative_path": "_docs/sql-reference/sql-commands/080-select-list.md",
+ "title": "SELECT List",
+ "url": "/docs/select-list/"
+ },
+ {
+ "breadcrumbs": [
+ {
+ "title": "SQL Commands",
+ "url": "/docs/sql-commands/"
+ },
+ {
+ "title": "SQL Reference",
+ "url": "/docs/sql-reference/"
+ }
+ ],
+ "children": [],
+ "next_title": "GROUP BY Clause",
+ "next_url": "/docs/group-by-clause/",
+ "parent": "SQL Commands",
+ "previous_title": "SELECT List",
+ "previous_url": "/docs/select-list/",
"relative_path": "_docs/sql-reference/sql-commands/081-from-clause.md",
"title": "FROM Clause",
"url": "/docs/from-clause/"
http://git-wip-us.apache.org/repos/asf/drill/blob/1e05eb3e/_docs/sql-reference/sql-commands/079-select.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/079-select.md b/_docs/sql-reference/sql-commands/079-select.md
new file mode 100755
index 0000000..3e20134
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/079-select.md
@@ -0,0 +1,79 @@
+---
+title: "SELECT"
+parent: "SQL Commands"
+---
+Drill supports the following ANSI standard clauses in the SELECT statement:
+
+ * WITH clause
+ * SELECT list
+ * FROM clause
+ * WHERE clause
+ * GROUP BY clause
+ * HAVING clause
+ * UNION ALL set operator
+ * ORDER BY clause (with an optional LIMIT clause)
+ * Limit clause
+ * Offset clause
+
+You can use the same SELECT syntax in the following commands:
+
+ * CREATE TABLE AS (CTAS)
+ * CREATE VIEW
+
+INSERT INTO SELECT is not yet supported.
+
+## Column Aliases
+
+You can use named column aliases in the SELECT list to provide meaningful
+names for regular columns and computed columns, such as the results of
+aggregate functions. See the section on running queries for examples.
+
+You cannot reference column aliases in the following clauses:
+
+ * WHERE
+ * GROUP BY
+ * HAVING
+
+Because Drill works with schema-less data sources, you cannot use positional
+aliases (1, 2, etc.) to refer to SELECT list columns, except in the ORDER BY
+clause.
+
+## Joins
+
+Drill supports ANSI standard joins in the FROM and WHERE clauses:
+
+ * Inner joins
+ * Left, full, and right outer joins
+
+The following types of join syntax are supported:
+
+| Join type | Syntax |
+|--------------------------------|----------------------------------------------------|
+| Join condition in WHERE clause | FROM table1, table 2 WHERE table1.col1=table2.col1 |
+| ON join in FROM clause | FROM table1 JOIN table2 ON table1.col1=table2.col1 |
+
+
+Cross-joins are not yet supported. You must specify a join condition when more
+than one table is listed in the FROM clause.
+
+Non-equijoins are supported if the join also contains an equality condition on
+the same two tables as part of a conjunction:
+
+ table1.col1 = table2.col1 AND table1.c2 < table2.c2
+
+This restriction applies to both inner and outer joins.
+
+## Subqueries
+
+You can use the following subquery operators in Drill queries. These operators
+all return Boolean results.
+
+ * ALL
+ * ANY
+ * EXISTS
+ * IN
+ * SOME
+
+In general, correlated subqueries are supported. EXISTS and NOT EXISTS
+subqueries that do not contain a correlation join are not yet supported.
+
http://git-wip-us.apache.org/repos/asf/drill/blob/1e05eb3e/_docs/sql-reference/sql-commands/080-select-list.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/080-select-list.md b/_docs/sql-reference/sql-commands/080-select-list.md
new file mode 100755
index 0000000..e5d0cac
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/080-select-list.md
@@ -0,0 +1,71 @@
+---
+title: "SELECT List"
+parent: "SQL Commands"
+---
+
+The SELECT list names the columns, functions, and expressions that you want the query to return. The list represents the output of the query.
+
+## Syntax
+
+The SELECT list supports the following syntax:
+
+ SELECT [ DISTINCT ] columns[n] | * | expression [ AS column_alias ] [, ...]
+
+## Parameters
+COLUMNS[*n*]
+Array columns are used for reading data from text files. Use the columns[*n*] syntax in the SELECT list to return rows from text files in a columnar format. This syntax uses a zero-based index, so the first column is column 0.
+
+DISTINCT
+An option that eliminates duplicate rows from the result set, based on matching values in one or more columns.
+* (asterisk)
+Returns the entire contents of the table or file.
+
+*expression*
+An expression formed from one or more columns that exist in the tables, files, or directories referenced by the query. An expression can contain functions and aliases that define select list entries. You can also use a scalar aggregate subquery as the expression in the SELECT list.
+
+*scalar aggregate subquery*
+A scalar aggregate subquery is a regular SELECT query in parentheses that returns exactly one column value from one row. The returned value is used in the outer query. The scalar aggregate subquery must include an aggregate function, such as MAX(), AVG(), or COUNT(). If the subquery returns zero rows, the value of the subquery expression is null. If it returns more than one row, Drill returns an error. Scalar subqueries are not valid expressions in the following cases:
+
+* As default values for expressions
+* In GROUP BY and HAVING clauses
+
+AS *column_alias*
+A temporary name for a column in the final result set. The AS keyword is optional.
+
+## Examples
+The following example shows a query with a scalar subquery expression:
+
+ SELECT a1, (SELECT MAX(a2) FROM t2) AS max_a2 FROM t1;
+ +-----+-------+
+ | a1 | max_a2 |
+ +-----+-------+
+ | 1 | 9 |
+ | 2 | 9 |
+ | 3 | 9 |
+ | 4 | 9 |
+ | 5 | 9 |
+ | 6 | 9 |
+ | 7 | 9 |
+ | null | 9 |
+ | 9 | 9 |
+ | 10 | 9 |
+ +-----+-------+
+ 10 rows selected (0.244 seconds)
+
+The following example shows a query with array columns that return rows in column format for easier readability:
+
+ SELECT COLUMNS[0], COLUMNS[1] FROM dfs.`/Users/brumsby/drill/plays.csv`;
+ +------------+------------------------+
+ | EXPR$0 | EXPR$1 |
+ +------------+------------------------+
+ | 1599 | As You Like It |
+ | 1601 | Twelfth Night |
+ | 1594 | Comedy of Errors |
+ | 1595 | Romeo and Juliet |
+ | 1596 | The Merchant of Venice |
+ | 1610 | The Tempest |
+ | 1599 | Hamlet |
+ +------------+------------------------+
+ 7 rows selected (0.137 seconds)
+
+
http://git-wip-us.apache.org/repos/asf/drill/blob/1e05eb3e/_docs/sql-reference/sql-commands/080-select.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/080-select.md b/_docs/sql-reference/sql-commands/080-select.md
deleted file mode 100755
index 3e20134..0000000
--- a/_docs/sql-reference/sql-commands/080-select.md
+++ /dev/null
@@ -1,79 +0,0 @@
----
-title: "SELECT"
-parent: "SQL Commands"
----
-Drill supports the following ANSI standard clauses in the SELECT statement:
-
- * WITH clause
- * SELECT list
- * FROM clause
- * WHERE clause
- * GROUP BY clause
- * HAVING clause
- * UNION ALL set operator
- * ORDER BY clause (with an optional LIMIT clause)
- * Limit clause
- * Offset clause
-
-You can use the same SELECT syntax in the following commands:
-
- * CREATE TABLE AS (CTAS)
- * CREATE VIEW
-
-INSERT INTO SELECT is not yet supported.
-
-## Column Aliases
-
-You can use named column aliases in the SELECT list to provide meaningful
-names for regular columns and computed columns, such as the results of
-aggregate functions. See the section on running queries for examples.
-
-You cannot reference column aliases in the following clauses:
-
- * WHERE
- * GROUP BY
- * HAVING
-
-Because Drill works with schema-less data sources, you cannot use positional
-aliases (1, 2, etc.) to refer to SELECT list columns, except in the ORDER BY
-clause.
-
-## Joins
-
-Drill supports ANSI standard joins in the FROM and WHERE clauses:
-
- * Inner joins
- * Left, full, and right outer joins
-
-The following types of join syntax are supported:
-
-| Join type | Syntax |
-|--------------------------------|----------------------------------------------------|
-| Join condition in WHERE clause | FROM table1, table 2 WHERE table1.col1=table2.col1 |
-| ON join in FROM clause | FROM table1 JOIN table2 ON table1.col1=table2.col1 |
-
-
-Cross-joins are not yet supported. You must specify a join condition when more
-than one table is listed in the FROM clause.
-
-Non-equijoins are supported if the join also contains an equality condition on
-the same two tables as part of a conjunction:
-
- table1.col1 = table2.col1 AND table1.c2 < table2.c2
-
-This restriction applies to both inner and outer joins.
-
-## Subqueries
-
-You can use the following subquery operators in Drill queries. These operators
-all return Boolean results.
-
- * ALL
- * ANY
- * EXISTS
- * IN
- * SOME
-
-In general, correlated subqueries are supported. EXISTS and NOT EXISTS
-subqueries that do not contain a correlation join are not yet supported.
-
http://git-wip-us.apache.org/repos/asf/drill/blob/1e05eb3e/_docs/sql-reference/sql-commands/081-from-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/081-from-clause.md b/_docs/sql-reference/sql-commands/081-from-clause.md
index 8f5ab37..d38cccd 100755
--- a/_docs/sql-reference/sql-commands/081-from-clause.md
+++ b/_docs/sql-reference/sql-commands/081-from-clause.md
@@ -21,49 +21,42 @@ Includes one or more *table_references* and is typically followed by the WHERE,
( subquery ) [ AS ] alias [ ( column_alias [, ...] ) ]
table_reference [ ON join_condition ]
- * *with\_subquery\_table_name*
+ * *with\_subquery\_table_name*
+ A table defined by a subquery in the WITH clause.
- A table defined by a subquery in the WITH clause.
+ * *table_name*
+ Name of a table or view. In Drill, you can also refer to a file system directory or a specific file.
+ * *alias*
+ A temporary alternative name for a table or view that provides a convenient shortcut for identifying tables in other parts of a query, such as the WHERE clause. You must supply an alias for a table derived from a subquery. In other table references, aliases are optional. The AS keyword is always optional. Drill does not support the GROUP BY alias.
- * *table_name*
-
- Name of a table or view. In Drill, you can also refer to a file system directory or a specific file.
+ * *column_alias*
+ A temporary alternative name for a column in a table or view. You can use named column aliases in the SELECT list to provide meaningful names for regular columns and computed columns, such as the results of aggregate functions. You cannot reference column aliases in the following clauses:
+ WHERE
+ GROUP BY
+ HAVING
- * *alias*
+ Because Drill works with schema-less data sources, you cannot use positional aliases (1, 2, etc.) to refer to SELECT list columns, except in the ORDER BY clause.
- A temporary alternative name for a table or view that provides a convenient shortcut for identifying tables in other parts of a query, such as the WHERE clause. You must supply an alias for a table derived from a subquery. In other table references, aliases are optional. The AS keyword is always optional. Drill does not support the GROUP BY alias.
-
- * *column_alias*
-
- A temporary alternative name for a column in a table or view.
-
- * *subquery*
-
- A query expression that evaluates to a table. The table exists only for the duration of the query and is typically given a name or alias, though an alias is not required. You can also define column names for tables that derive from subqueries. Naming column aliases is important when you want to join the results of subqueries to other tables and when you want to select or constrain those columns elsewhere in the query. A subquery may contain an ORDER BY clause, but this clause may have no effect if a LIMIT or OFFSET clause is not also specified.
-
- * *join_type*
-
- Specifies one of the following join types:
+ * *subquery*
+ A query expression that evaluates to a table. The table exists only for the duration of the query and is typically given a name or alias, though an alias is not required. You can also define column names for tables that derive from subqueries. Naming column aliases is important when you want to join the results of subqueries to other tables and when you want to select or constrain those columns elsewhere in the query. A subquery may contain an ORDER BY clause, but this clause may have no effect if a LIMIT or OFFSET clause is not also specified.
+ * *join_type*
+ Specifies one of the following join types:
[INNER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN
- * *ON join_condition*
-
- A type of join specification where the joining columns are stated as a condition that follows the ON keyword.
- Example:
- ` homes join listing on homes.listid=listing.listid and homes.homeid=listing.homeid`
+ * *ON join_condition*
+ A type of join specification where the joining columns are stated as a condition that follows the ON keyword.
+ Example: ` homes join listing on homes.listid=listing.listid and homes.homeid=listing.homeid`
## Join Types
INNER JOIN
-
Return matching rows only, based on the join condition or list of joining columns.
-OUTER JOIN
-
+OUTER JOIN
Return all of the rows that the equivalent inner join would return plus non-matching rows from the "left" table, "right" table, or both tables. The left table is the first-listed table, and the right table is the second-listed table. The non-matching rows contain NULL values to fill the gaps in the output columns.
## Usage Notes
@@ -71,5 +64,3 @@ Return all of the rows that the equivalent inner join would return plus non-matc
* A join with the ON syntax retains both joining columns in its intermediate result set.
-## Examples
-
http://git-wip-us.apache.org/repos/asf/drill/blob/1e05eb3e/_docs/sql-reference/sql-commands/083-having-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/083-having-clause.md b/_docs/sql-reference/sql-commands/083-having-clause.md
index 1a079b0..be64be4 100755
--- a/_docs/sql-reference/sql-commands/083-having-clause.md
+++ b/_docs/sql-reference/sql-commands/083-having-clause.md
@@ -7,7 +7,7 @@ The HAVING clause filters group rows created by the GROUP BY clause. The HAVING
## Syntax
The HAVING clause supports the following syntax:
-`[ HAVING boolean_expression ]`
+ HAVING boolean_expression
## Expression
A *boolean expression* can include one or more of the following operators:
http://git-wip-us.apache.org/repos/asf/drill/blob/1e05eb3e/_docs/sql-reference/sql-commands/084-limit-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/084-limit-clause.md b/_docs/sql-reference/sql-commands/084-limit-clause.md
index 459c0c0..d691119 100755
--- a/_docs/sql-reference/sql-commands/084-limit-clause.md
+++ b/_docs/sql-reference/sql-commands/084-limit-clause.md
@@ -8,21 +8,21 @@ The LIMIT clause limits the result set to the specified number of rows. You can
## Syntax
The LIMIT clause supports the following syntax:
- [ LIMIT { count | ALL } ]
+ LIMIT { count | ALL }
Specifying ALL returns all records, which is equivalent to omitting the LIMIT clause from the SELECT statement.
## Parameters
*count*
-
Specifies the maximum number of rows to return.
If the count expression evaluates to NULL, Drill treats it as LIMIT ALL.
## Examples
The following example query includes the ORDER BY and LIMIT clauses and returns the top 20 sales totals by month and state:
- 0: jdbc:drill:> select `month`, state, sum(order_total) as sales from orders group by `month`, state
- order by 3 desc limit 20;
+ 0: jdbc:drill:> SELECT `month`, state, SUM(order_total)
+ AS sales FROM orders GROUP BY `month`, state
+ ORDER BY 3 DESC LIMIT 20;
+------------+------------+------------+
| month | state | sales |
+------------+------------+------------+
http://git-wip-us.apache.org/repos/asf/drill/blob/1e05eb3e/_docs/sql-reference/sql-commands/085-offset-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/085-offset-clause.md b/_docs/sql-reference/sql-commands/085-offset-clause.md
index 92ee478..8eb2f05 100755
--- a/_docs/sql-reference/sql-commands/085-offset-clause.md
+++ b/_docs/sql-reference/sql-commands/085-offset-clause.md
@@ -7,13 +7,10 @@ The OFFSET clause provides a way to skip a specified number of first rows in a r
## Syntax
The OFFSET clause supports the following syntax:
- [ OFFSET start { ROW | ROWS } ]
-
-Specifying ALL returns all records, which is equivalent to omitting the LIMIT clause from the SELECT statement.
+ OFFSET start { ROW | ROWS }
## Parameters
*rows*
-
Specifies the number of rows Drill should skip before returning the result set.
## Usage Notes
http://git-wip-us.apache.org/repos/asf/drill/blob/1e05eb3e/_docs/sql-reference/sql-commands/087-union-set-operator.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/087-union-set-operator.md b/_docs/sql-reference/sql-commands/087-union-set-operator.md
index d55c50e..68558b9 100755
--- a/_docs/sql-reference/sql-commands/087-union-set-operator.md
+++ b/_docs/sql-reference/sql-commands/087-union-set-operator.md
@@ -26,9 +26,10 @@ Any SELECT query that Drill supports. See SELECT.
## Examples
The following example uses the UNION ALL set operator to combine click activity data before and after a marketing campaign. The data in the example exists in the `dfs.clicks workspace`.
- 0: jdbc:drill:> select t.trans_id transaction, t.user_info.cust_id customer from `clicks/clicks.campaign.json` t
- union all
- select u.trans_id, u.user_info.cust_id from `clicks/clicks.json` u limit 5;
+ 0: jdbc:drill:> SELECT t.trans_id transaction, t.user_info.cust_id customer
+ FROM `clicks/clicks.campaign.json` t
+ UNION ALL
+ SELECT u.trans_id, u.user_info.cust_id FROM `clicks/clicks.json` u LIMIT 5;
+-------------+------------+
| transaction | customer |
+-------------+------------+
http://git-wip-us.apache.org/repos/asf/drill/blob/1e05eb3e/_docs/sql-reference/sql-commands/088-where-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/088-where-clause.md b/_docs/sql-reference/sql-commands/088-where-clause.md
index d233d6d..18a9c7d 100755
--- a/_docs/sql-reference/sql-commands/088-where-clause.md
+++ b/_docs/sql-reference/sql-commands/088-where-clause.md
@@ -28,11 +28,11 @@ A boolean expression can include one or more of the following operators:
## Examples
The following query compares order totals where the states are California and New York:
- 0: jdbc:drill:> select o1.cust_id, sum(o1.order_total) as ny_sales,
- (select sum(o2.order_total) from hive.orders o2
- where o1.cust_id=o2.cust_id and state='ca') as ca_sales
- from hive.orders o1 where o1.state='ny' group by o1.cust_id
- order by cust_id limit 20;
+ 0: jdbc:drill:> SELECT o1.cust_id, sum(o1.order_total) AS ny_sales,
+ (SELECT SUM(o2.order_total) FROM hive.orders o2
+ WHERE o1.cust_id=o2.cust_id and state='ca') AS ca_sales
+ FROM hive.orders o1 WHERE o1.state='ny' GROUP BY o1.cust_id
+ ORDER BY cust_id LIMIT 20;
+------------+------------+------------+
| cust_id | ny_sales | ca_sales |
+------------+------------+------------+
http://git-wip-us.apache.org/repos/asf/drill/blob/1e05eb3e/_docs/sql-reference/sql-commands/089-with-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/089-with-clause.md b/_docs/sql-reference/sql-commands/089-with-clause.md
index 5accdce..2b5ca89 100755
--- a/_docs/sql-reference/sql-commands/089-with-clause.md
+++ b/_docs/sql-reference/sql-commands/089-with-clause.md
@@ -17,20 +17,17 @@ The WITH clause supports the following syntax:
## Parameters
-_with\_subquery\_table\_name_
-
+*with_subquery_table_name*
A unique name for a temporary table that defines the results of a WITH clause
subquery. You cannot use duplicate names within a single WITH clause. You must
give each subquery a table name that can be referenced in the FROM clause.
-_column\_name_
-
+*column_name*
An optional list of output column names for the WITH clause subquery,
separated by commas. The number of column names specified must be equal to or
less than the number of columns defined by the subquery.
-_query_
-
+query
Any SELECT query that Drill supports. See
[SELECT]({{ site.baseurl }}/docs/SELECT+Statements).
@@ -44,12 +41,9 @@ reuse the results for query optimization.
You can use a WITH clause in the following SQL statements:
- * SELECT (including subqueries within SELECT statements)
-
- * CREATE TABLE AS
-
- * CREATE VIEW
-
+ * SELECT (including subqueries within SELECT statements)
+ * CREATE TABLE AS
+ * CREATE VIEW
* EXPLAIN
You can reference the temporary tables in the FROM clause of the query. If the