Repository: drill Updated Branches: refs/heads/gh-pages 1e05eb3e2 -> 3516a1f4a
Bridget's SQL command updates move audit logging out of main menu Bridget's image link fixes DRILL-3078 partial add query logging to intro Project: http://git-wip-us.apache.org/repos/asf/drill/repo Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/5d5349db Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/5d5349db Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/5d5349db Branch: refs/heads/gh-pages Commit: 5d5349db44c75263173af652800aad2405d1add2 Parents: a03aa49 Author: Kristine Hahn <kh...@maprtech.com> Authored: Mon Jun 1 18:27:42 2015 -0700 Committer: Kristine Hahn <kh...@maprtech.com> Committed: Tue Jun 2 14:47:48 2015 -0700 ---------------------------------------------------------------------- _data/docs.json | 240 ++++++++++++------- _docs/074-query-audit-logging.md | 5 - _docs/075-getting-query-information.md | 56 ----- .../070-configuring-user-impersonation.md | 8 +- .../001-log-and-debug-introduction.md | 2 + _docs/log-and-debug/005-query-audit-logging.md | 56 +++++ .../010-installing-the-driver-on-windows.md | 7 +- .../020-configuring-connections-on-windows.md | 48 ++-- _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 +- 18 files changed, 433 insertions(+), 317 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/drill/blob/5d5349db/_data/docs.json ---------------------------------------------------------------------- diff --git a/_data/docs.json b/_data/docs.json index f0a8fb6..c002e8f 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/" @@ -3037,23 +3037,6 @@ "title": "GROUP BY Clause", "url": "/docs/group-by-clause/" }, - "Getting Query Information": { - "breadcrumbs": [ - { - "title": "Query Audit Logging", - "url": "/docs/query-audit-logging/" - } - ], - "children": [], - "next_title": "SQL Reference", - "next_url": "/docs/sql-reference/", - "parent": "Query Audit Logging", - "previous_title": "Query Audit Logging", - "previous_url": "/docs/query-audit-logging/", - "relative_path": "_docs/075-getting-query-information.md", - "title": "Getting Query Information", - "url": "/docs/getting-query-information/" - }, "Getting Started": { "breadcrumbs": [], "children": [ @@ -4335,6 +4318,23 @@ "relative_path": "_docs/log-and-debug/004-review-the-java-stack-trace.md", "title": "Review the Java Stack Trace", "url": "/docs/review-the-java-stack-trace/" + }, + { + "breadcrumbs": [ + { + "title": "Log and Debug", + "url": "/docs/log-and-debug/" + } + ], + "children": [], + "next_title": "SQL Reference", + "next_url": "/docs/sql-reference/", + "parent": "Log and Debug", + "previous_title": "Review the Java Stack Trace", + "previous_url": "/docs/review-the-java-stack-trace/", + "relative_path": "_docs/log-and-debug/005-query-audit-logging.md", + "title": "Query Audit Logging", + "url": "/docs/query-audit-logging/" } ], "next_title": "Log and Debug Introduction", @@ -5759,32 +5759,19 @@ "url": "/docs/project-bylaws/" }, "Query Audit Logging": { - "breadcrumbs": [], - "children": [ + "breadcrumbs": [ { - "breadcrumbs": [ - { - "title": "Query Audit Logging", - "url": "/docs/query-audit-logging/" - } - ], - "children": [], - "next_title": "SQL Reference", - "next_url": "/docs/sql-reference/", - "parent": "Query Audit Logging", - "previous_title": "Query Audit Logging", - "previous_url": "/docs/query-audit-logging/", - "relative_path": "_docs/075-getting-query-information.md", - "title": "Getting Query Information", - "url": "/docs/getting-query-information/" + "title": "Log and Debug", + "url": "/docs/log-and-debug/" } ], - "next_title": "Getting Query Information", - "next_url": "/docs/getting-query-information/", - "parent": "", + "children": [], + "next_title": "SQL Reference", + "next_url": "/docs/sql-reference/", + "parent": "Log and Debug", "previous_title": "Review the Java Stack Trace", "previous_url": "/docs/review-the-java-stack-trace/", - "relative_path": "_docs/074-query-audit-logging.md", + "relative_path": "_docs/log-and-debug/005-query-audit-logging.md", "title": "Query Audit Logging", "url": "/docs/query-audit-logging/" }, @@ -7210,15 +7197,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 +7478,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 +7499,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 +8640,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 +8661,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/" @@ -8980,8 +9030,8 @@ "next_title": "SQL Reference Introduction", "next_url": "/docs/sql-reference-introduction/", "parent": "", - "previous_title": "Getting Query Information", - "previous_url": "/docs/getting-query-information/", + "previous_title": "Query Audit Logging", + "previous_url": "/docs/query-audit-logging/", "relative_path": "_docs/080-sql-reference.md", "title": "SQL Reference", "url": "/docs/sql-reference/" @@ -12899,46 +12949,33 @@ "relative_path": "_docs/log-and-debug/004-review-the-java-stack-trace.md", "title": "Review the Java Stack Trace", "url": "/docs/review-the-java-stack-trace/" - } - ], - "next_title": "Log and Debug Introduction", - "next_url": "/docs/log-and-debug-introduction/", - "parent": "", - "previous_title": "Physical Operators", - "previous_url": "/docs/physical-operators/", - "relative_path": "_docs/073-log-and-debug.md", - "title": "Log and Debug", - "url": "/docs/log-and-debug/" - }, - { - "breadcrumbs": [], - "children": [ + }, { "breadcrumbs": [ { - "title": "Query Audit Logging", - "url": "/docs/query-audit-logging/" + "title": "Log and Debug", + "url": "/docs/log-and-debug/" } ], "children": [], "next_title": "SQL Reference", "next_url": "/docs/sql-reference/", - "parent": "Query Audit Logging", - "previous_title": "Query Audit Logging", - "previous_url": "/docs/query-audit-logging/", - "relative_path": "_docs/075-getting-query-information.md", - "title": "Getting Query Information", - "url": "/docs/getting-query-information/" + "parent": "Log and Debug", + "previous_title": "Review the Java Stack Trace", + "previous_url": "/docs/review-the-java-stack-trace/", + "relative_path": "_docs/log-and-debug/005-query-audit-logging.md", + "title": "Query Audit Logging", + "url": "/docs/query-audit-logging/" } ], - "next_title": "Getting Query Information", - "next_url": "/docs/getting-query-information/", + "next_title": "Log and Debug Introduction", + "next_url": "/docs/log-and-debug-introduction/", "parent": "", - "previous_title": "Review the Java Stack Trace", - "previous_url": "/docs/review-the-java-stack-trace/", - "relative_path": "_docs/074-query-audit-logging.md", - "title": "Query Audit Logging", - "url": "/docs/query-audit-logging/" + "previous_title": "Physical Operators", + "previous_url": "/docs/physical-operators/", + "relative_path": "_docs/073-log-and-debug.md", + "title": "Log and Debug", + "url": "/docs/log-and-debug/" }, { "breadcrumbs": [], @@ -13568,12 +13605,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 +13626,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/" @@ -13937,8 +13995,8 @@ "next_title": "SQL Reference Introduction", "next_url": "/docs/sql-reference-introduction/", "parent": "", - "previous_title": "Getting Query Information", - "previous_url": "/docs/getting-query-information/", + "previous_title": "Query Audit Logging", + "previous_url": "/docs/query-audit-logging/", "relative_path": "_docs/080-sql-reference.md", "title": "SQL Reference", "url": "/docs/sql-reference/" http://git-wip-us.apache.org/repos/asf/drill/blob/5d5349db/_docs/074-query-audit-logging.md ---------------------------------------------------------------------- diff --git a/_docs/074-query-audit-logging.md b/_docs/074-query-audit-logging.md deleted file mode 100644 index f305725..0000000 --- a/_docs/074-query-audit-logging.md +++ /dev/null @@ -1,5 +0,0 @@ ---- -title: "Query Audit Logging" ---- - - http://git-wip-us.apache.org/repos/asf/drill/blob/5d5349db/_docs/075-getting-query-information.md ---------------------------------------------------------------------- diff --git a/_docs/075-getting-query-information.md b/_docs/075-getting-query-information.md deleted file mode 100644 index d5e2953..0000000 --- a/_docs/075-getting-query-information.md +++ /dev/null @@ -1,56 +0,0 @@ ---- -title: "Getting Query Information" -parent: "Query Audit Logging" ---- -The query log provides audit log functionality for the queries executed by various drillbits in the cluster. The log records important information about queries executed on the Drillbit where Drill runs. The log includes query text, start time, end time, user, status, schema, and the query id. You can query one of the following log files, depending on whether you run Drill in embedded or distributed mode, to get audit logging information: - -* `sqlline_queries.json` (embedded mode) -* `drillbit_queries.json` (distributed mode) - -## Checking the Most Recent Queries - -For example, to check the most recent queries, query the log using this command: - - SELECT * FROM dfs.`default`.`/Users/drill-user/apache-drill-1.0.0/log/sqlline_queries.json` t ORDER BY `start` LIMIT 5; - - +----------------+------------+---------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+----------------+------------+ - | finish | outcome | queryId | queryText | schema | start | username | - +----------------+------------+---------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+----------------+------------+ - | 1431752662216 | FAILED | 2aa9302b-bf6f-a378-d66e-151834e87b16 | select * from dfs.`default`.`/Users/nrentachintala/Downloads/testgoogle.json` t limit 1 | | 1431752660376 | anonymous | - | 1431752769079 | COMPLETED | 2aa92fc1-b722-c27a-10f7-57a1cf0dd366 | SELECT KVGEN(checkin_info) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` LIMIT 2 | | 1431752765303 | anonymous | - | 1431752786341 | COMPLETED | 2aa92faf-2103-047b-9761-32eedefba1e6 | SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` LIMIT 20 | | 1431752784532 | anonymous | - | 1431752809084 | FAILED | 2aa92f97-61d3-1e9a-97b0-c754f5b568d5 | SELECT SUM(checkintbl.checkins.`value`) AS TotalCheckins FROM (SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` ) checkintbl | | 1431752808923 | anonymous | - | 1431752853992 | COMPLETED | 2aa92f87-0250-c6ac-3700-9ae1f98435b8 | SELECT SUM(checkintbl.checkins.`value`) AS TotalCheckins FROM (SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` ) checkintbl | | 1431752824947 | anonymous | - +----------------+------------+---------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+----------------+------------+ - 5 rows selected (0.532 seconds) - -{% include startnote.html %}This document aligns Drill output for example purposes. Drill output is not aligned in this case.{% include endnote.html %} - -## Checking Drillbit Traffic - -To check the total number of queries executed since the session started on the Drillbit, use the following command: - - SELECT COUNT(*) FROM dfs.`default`.`/Users/drill-user/apache-drill-1.0.0/log/sqlline_queries.json`; - - +---------+ - | EXPR$0 | - +---------+ - | 32 | - +---------+ - 1 row selected (0.144 seconds) - -## Getting Query Success Statistics - -To get the total number of successful and failed executions, run the following command: - - SELECT outcome, COUNT(*) FROM dfs.`default`.`/Users/drill-user/apache-drill-1.0.0/log/sqlline_queries.json` GROUP BY outcome; - - +------------+---------+ - | outcome | EXPR$1 | - +------------+---------+ - | COMPLETED | 18 | - | FAILED | 14 | - +------------+---------+ - 2 rows selected (0.219 seconds) - -Note the queryid column in the audit can be correlated with the profiles of the queries for troubleshooting/diagnostics purposes. \ No newline at end of file http://git-wip-us.apache.org/repos/asf/drill/blob/5d5349db/_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 index e7d954e..4c68daa 100755 --- 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. @@ -83,17 +83,17 @@ An administrator can set the maximum number of hops on views to limit the number 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 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. - + 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. 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. http://git-wip-us.apache.org/repos/asf/drill/blob/5d5349db/_docs/log-and-debug/001-log-and-debug-introduction.md ---------------------------------------------------------------------- diff --git a/_docs/log-and-debug/001-log-and-debug-introduction.md b/_docs/log-and-debug/001-log-and-debug-introduction.md index 473883f..8b50cea 100644 --- a/_docs/log-and-debug/001-log-and-debug-introduction.md +++ b/_docs/log-and-debug/001-log-and-debug-introduction.md @@ -13,3 +13,5 @@ Drill provides two standard output files: * drill.log Drill also provides a special file, drillbit_queries.json, on each Drill node. This log provides the QueryID and profile for every query run on a Drillbit. The Profile view in the Drill Web UI lists the last one-hundred queries that Drill ran. To see information for queries beyond the last one-hundred, you can view the drillbit_queries.json file on each Drill node. + +Drill also provides [audit logging]({{site.baseurl}}/docs/query-audit-logging/) of queries executed by various drillbits in the cluster. http://git-wip-us.apache.org/repos/asf/drill/blob/5d5349db/_docs/log-and-debug/005-query-audit-logging.md ---------------------------------------------------------------------- diff --git a/_docs/log-and-debug/005-query-audit-logging.md b/_docs/log-and-debug/005-query-audit-logging.md new file mode 100644 index 0000000..7829352 --- /dev/null +++ b/_docs/log-and-debug/005-query-audit-logging.md @@ -0,0 +1,56 @@ +--- +title: "Query Audit Logging" +parent: "Log and Debug" +--- +The query log provides audit log functionality for the queries executed by various drillbits in the cluster. The log records important information about queries executed on the Drillbit where Drill runs. The log includes query text, start time, end time, user, status, schema, and the query id. You can query one of the following log files, depending on whether you run Drill in embedded or distributed mode, to get audit logging information: + +* `sqlline_queries.json` (embedded mode) +* `drillbit_queries.json` (distributed mode) + +## Checking the Most Recent Queries + +For example, to check the most recent queries, query the log using this command: + + SELECT * FROM dfs.`default`.`/Users/drill-user/apache-drill-1.0.0/log/sqlline_queries.json` t ORDER BY `start` LIMIT 5; + + +----------------+------------+---------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+----------------+------------+ + | finish | outcome | queryId | queryText | schema | start | username | + +----------------+------------+---------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+----------------+------------+ + | 1431752662216 | FAILED | 2aa9302b-bf6f-a378-d66e-151834e87b16 | select * from dfs.`default`.`/Users/nrentachintala/Downloads/testgoogle.json` t limit 1 | | 1431752660376 | anonymous | + | 1431752769079 | COMPLETED | 2aa92fc1-b722-c27a-10f7-57a1cf0dd366 | SELECT KVGEN(checkin_info) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` LIMIT 2 | | 1431752765303 | anonymous | + | 1431752786341 | COMPLETED | 2aa92faf-2103-047b-9761-32eedefba1e6 | SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` LIMIT 20 | | 1431752784532 | anonymous | + | 1431752809084 | FAILED | 2aa92f97-61d3-1e9a-97b0-c754f5b568d5 | SELECT SUM(checkintbl.checkins.`value`) AS TotalCheckins FROM (SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` ) checkintbl | | 1431752808923 | anonymous | + | 1431752853992 | COMPLETED | 2aa92f87-0250-c6ac-3700-9ae1f98435b8 | SELECT SUM(checkintbl.checkins.`value`) AS TotalCheckins FROM (SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` ) checkintbl | | 1431752824947 | anonymous | + +----------------+------------+---------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+----------------+------------+ + 5 rows selected (0.532 seconds) + +{% include startnote.html %}This document aligns Drill output for example purposes. Drill output is not aligned in this case.{% include endnote.html %} + +## Checking Drillbit Traffic + +To check the total number of queries executed since the session started on the Drillbit, use the following command: + + SELECT COUNT(*) FROM dfs.`default`.`/Users/drill-user/apache-drill-1.0.0/log/sqlline_queries.json`; + + +---------+ + | EXPR$0 | + +---------+ + | 32 | + +---------+ + 1 row selected (0.144 seconds) + +## Getting Query Success Statistics + +To get the total number of successful and failed executions, run the following command: + + SELECT outcome, COUNT(*) FROM dfs.`default`.`/Users/drill-user/apache-drill-1.0.0/log/sqlline_queries.json` GROUP BY outcome; + + +------------+---------+ + | outcome | EXPR$1 | + +------------+---------+ + | COMPLETED | 18 | + | FAILED | 14 | + +------------+---------+ + 2 rows selected (0.219 seconds) + +Note the queryid column in the audit can be correlated with the profiles of the queries for troubleshooting/diagnostics purposes. \ No newline at end of file http://git-wip-us.apache.org/repos/asf/drill/blob/5d5349db/_docs/odbc-jdbc-interfaces/using-odbc-on-windows/010-installing-the-driver-on-windows.md ---------------------------------------------------------------------- diff --git a/_docs/odbc-jdbc-interfaces/using-odbc-on-windows/010-installing-the-driver-on-windows.md b/_docs/odbc-jdbc-interfaces/using-odbc-on-windows/010-installing-the-driver-on-windows.md index 51035b1..6c65f91 100755 --- a/_docs/odbc-jdbc-interfaces/using-odbc-on-windows/010-installing-the-driver-on-windows.md +++ b/_docs/odbc-jdbc-interfaces/using-odbc-on-windows/010-installing-the-driver-on-windows.md @@ -12,10 +12,11 @@ Each computer where you install the driver must meet the following system requirements: * One of the following operating systems (32- and 64-bit editions are supported): - * Windows® Vista + * Windows® 8 and 8.1 * Windows® 7 Professional - * Windows® Server 2008 R2 - * .NET Framework 2.0 is required to run the Drill Explorer application. + * Windows® Server 2012, 2012 R2 + * Windows® Server 2008, 2013 R2 + * .NET Framework 4.5.2 is required to run the Drill Explorer application. * 60 MB of available disk space * The client must be able to resolve the actual hostname of the Drill node(s) with the IP(s). Verify that a DNS entry was created on the client machine for the Drill node(s). If not, create the following entry for the Drill node(s) in the `%WINDIR%\system32\drivers\etc\hosts` file: http://git-wip-us.apache.org/repos/asf/drill/blob/5d5349db/_docs/odbc-jdbc-interfaces/using-odbc-on-windows/020-configuring-connections-on-windows.md ---------------------------------------------------------------------- diff --git a/_docs/odbc-jdbc-interfaces/using-odbc-on-windows/020-configuring-connections-on-windows.md b/_docs/odbc-jdbc-interfaces/using-odbc-on-windows/020-configuring-connections-on-windows.md index 41f314b..2fa0115 100644 --- a/_docs/odbc-jdbc-interfaces/using-odbc-on-windows/020-configuring-connections-on-windows.md +++ b/_docs/odbc-jdbc-interfaces/using-odbc-on-windows/020-configuring-connections-on-windows.md @@ -16,27 +16,33 @@ Create a DSN that an application can use to connect to Drill data sources. If you want to create a DSN for a 32-bit application, you must use the 32-bit version of the ODBC Administrator to create the DSN. - 1. To launch the ODBC Administrator, click **Start > All Programs > MapR Drill ODBC Driver 1.0 (32|64-bit) > (32|64-bit) ODBC Administrator**. -The ODBC Data Source Administrator window appears. - - To launch the 32-bit version of the ODBC driver on a 64-bit machine, run: -`C:\WINDOWS\SysWOW64\odbcad32.exe`. - 2. Click the **System DSN** tab to create a system DSN or click the **User DSN** tab to create a user DSN. A system DSN is available for all users who log in to the machine. A user DSN is available to the user who creates the DSN. - 3. Click **Add**. - 4. Select **MapR Drill ODBC Driver** and click **Finish**. - The _MapR Drill ODBC Driver DSN Setup_ window appears. - 5. In the **Data Source Name** field, enter a name for the DSN, - 6. Optionally, enter a description of the DSN in the Description field. - 7. In the Connection Type section, select a connection type and enter the associated connection details: - - <table style='table-layout:fixed;width:100%'><tbody><tr><th>Connection Type</th><th >Properties</th><th >Descriptions</th></tr><tr><td rowspan="2" valign="top" width="10%">Zookeeper Quorum</td><td valign="top" style='width: 100px;'>Quorum</td><td valign="top" style='width: 400px;'>A comma-separated list of servers in a Zookeeper cluster.For example, <ip_zookeepernode1>:5181,<ip_zookeepernode21>:5181,â¦</td></tr><tr><td valign="top">ClusterID</td><td valign="top">Name of the drillbit cluster. The default is drillbits1. You may need to specify a different value if the cluster ID was changed in the drill-override.conf file.</td></tr><tr><td colspan="1" valign="top">Direct to Drillbit</td><td colspan="1" valign="top"> </td><td colspan="1" valign="top">Provide the IP address or host name of the Drill server and the port number that that the Drill server is listening on. The port number defaults to 31010. You may need to specify a different value if the port number was changed in the drill-override.conf file.</td></tr></tbody></table> - For information on selecting the appropriate connection type, see [Connection Types]({{ site.baseurl }}/docs/configuring-connections-on-windows/#connection-type). - 8. In the **Default Schema** field, select the default schema that you want to connect to. - For more information about the schemas that appear in this list, see Schemas. - 9. Optionally, perform one of the following operations: - - <table ><tbody><tr><th >Option</th><th >Action</th></tr><tr><td valign="top">Update the configuration of the advanced properties.</td><td valign="top">Edit the default values in the <strong>Advanced Properties</strong> section. <br />For more information, see <a href="#advanced-properties">Advanced Properties</a>.</td></tr><tr><td valign="top">Configure the types of events that you want the driver to log.</td><td valign="top">Click <strong>Logging Options</strong>. <br />For more information, see <a href="#logging-options">Logging Options</a>.</td></tr><tr><td valign="top">Create views or explore Drill sources.</td><td valign="top">Click <strong>Drill Explorer</strong>. <br />For more information, see <a href="/docs/using-drill-explorer-on-windows">Using Drill Explorer</a> to Browse Data and Create Views.</td></tr></tbody></table> - 10. Click **OK** to save the DSN. +1. To launch the ODBC Administrator, click **Start > All Programs > MapR Drill ODBC Driver 1.0 (32|64-bit) > (32|64-bit) ODBC Administrator**. + + To launch the 32-bit version of the ODBC driver on a 64-bit machine, run: +`C:\WINDOWS\SysWOW64\odbcad32.exe`. + The ODBC Data Source Administrator window appears. +2. Create a system or user DSN on the **System DSN** or **User DSN** tab, respectively. A system DSN is available for all users who log in to the machine. A user DSN is available to the user who creates the DSN. +3. Click **Add**. +4. Select **MapR Drill ODBC Driver** and click **Finish**. + The _MapR Drill ODBC Driver DSN Setup_ window appears. +5. In the **Data Source Name** field, enter a name for the DSN. +6. Optionally, enter a description of the DSN in the Description field. +7. In the Connection Type section, select one of the following connection types: + * ZooKeeper Quorum + * Direct to Drillbit +8. If you select **ZooKeeper Quorum**, provide values for the following properties: + * Quorum + A comma separated list of ZooKeeper nodes in the following format: + `<host name/ip address> : <port number>, <host name/ip address> : <port number>, . . .` + * Cluster ID + Name of the drillbit cluster, drillbits1 by default. Check the `drill-override.conf` file for any cluster name changes. + + If you select Direct to Drillbit, provide the IP address or host name of the Drill server and the Drill listen port number, 31010 by default. Check the `drill-override.conf` file for any port changes. +9. In **Default Schema**, select the [default schema]({{site.baseurl}}/docs/configuring-connections-on-windows/#schema) to connect to. +10. Optionally, perform the following operations: + * Update the [advanced properties]({{site.baseurl}}/docs/configuring-connections-on-windows/#advanced-properties) configuration. + * Configure [logging options]({{site.baseurl}}/docs/configuring-connections-on-windows/#logging-options) to log types of events. +11. Click **OK** to save the DSN. ## Configuration Options http://git-wip-us.apache.org/repos/asf/drill/blob/5d5349db/_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/5d5349db/_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/5d5349db/_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/5d5349db/_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/5d5349db/_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/5d5349db/_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/5d5349db/_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/5d5349db/_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/5d5349db/_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/5d5349db/_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
