Repository: calcite Updated Branches: refs/heads/master 45b405c4c -> a2880dd59
[CALCITE-1901] SQL reference should say that "ONLY" is required after "FETCH ... ROWS" Improve sqlsh documentation, including listing which commands work on which OS. Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/a2880dd5 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/a2880dd5 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/a2880dd5 Branch: refs/heads/master Commit: a2880dd59bcf132a0a4d862815acd6f6ef244631 Parents: 45b405c Author: Julian Hyde <[email protected]> Authored: Mon Jul 31 11:26:55 2017 -0700 Committer: Julian Hyde <[email protected]> Committed: Mon Jul 31 11:28:04 2017 -0700 ---------------------------------------------------------------------- site/_docs/os_adapter.md | 74 +++++++++++++++++++++++++++++-------------- site/_docs/reference.md | 2 +- 2 files changed, 51 insertions(+), 25 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/a2880dd5/site/_docs/os_adapter.md ---------------------------------------------------------------------- diff --git a/site/_docs/os_adapter.md b/site/_docs/os_adapter.md index d8f8d5e..127bac3 100644 --- a/site/_docs/os_adapter.md +++ b/site/_docs/os_adapter.md @@ -45,9 +45,10 @@ We try to support all tables on every operating system, and to make sure that the tables have the same columns. But we rely heavily on operating system commands, and these differ widely. So: -* These commands only work on Linux and macOS (not Windows, even with Cygwin) -* `vmstat` has very different columns between Linux and macOS -* `files` and `ps` have the same column names but semantics differ +* These commands only work on Linux and macOS (not Windows, even with Cygwin); +* `vmstat` has very different columns between Linux and macOS; +* `files` and `ps` have the same column names but semantics differ; +* Other commands work largely the same. # A simple example @@ -86,6 +87,7 @@ care. Often adding a back-slash will suffice. # Tables and commands The OS adapter contains the following tables: + * `du` - Disk usage (based on `du` command) * `ps` - Processes (based on `ps` command) * `stdin` - Standard input @@ -99,20 +101,44 @@ New data sources are straightforward to add; please contribute yours! ## Example: du +How many class files, and what is their total size? In `bash`: + +{% highlight bash %} +$ du -ka . | grep '\.class$' | awk '{size+=$1} END {print FNR, size}' +4416 27960 +{% endhighlight %} + +In `sqlsh`: + {% highlight bash %} $ sqlsh select count\(\*\), sum\(size_k\) from du where path like \'%.class\' 4416 27960 {% endhighlight %} +The back-slashes are necessary because `(`, `*`, `)`, and `'` are shell meta-characters. + ## Example: files +How many files and directories? In `bash`, you would use `find`: + +{% highlight bash %} +$ find . -printf "%Y %p\n" | grep '/test/' | cut -d' ' -f1 | sort | uniq -c + 143 d + 1336 f +{% endhighlight %} + +In `sqlsh`, use the `files` table: + {% highlight bash %} $ sqlsh select type, count\(\*\) from files where path like \'%/test/%\' group by type -4416 27960 +d 143 +f 1336 {% endhighlight %} ## Example: ps +Which users have processes running? In `sqlsh`: + {% highlight bash %} $ sqlsh select distinct ps.\`user\` from ps avahi @@ -123,9 +149,26 @@ nobody daemon {% endhighlight %} -The `ps.` qualifier is necessary because USER is a SQL reserved word. +The `ps.` qualifier and back-quotes are necessary because USER is a SQL reserved word. + +Now a 'top N' problem: Which three users have the most processes? In `bash`: + +{% highlight bash %} +$ ps aux | awk '{print $1}' | sort | uniq -c | sort -nr | head -3 +{% endhighlight %} + +In `sqlsh`: + +{% highlight bash %} +$ ./sqlsh select count\(\*\), ps.\`user\` from ps group by ps.\`user\` order by 1 desc limit 3 +185 root +69 jhyde +2 avahi +{% endhighlight %} + +## Example: vmstat -# Example: vmstat +How's my memory? {% highlight bash %} $ ./sqlsh -o mysql select \* from vmstat @@ -139,7 +182,7 @@ $ ./sqlsh -o mysql select \* from vmstat ## Example: explain -To find out what columns a table has, use {{explain}}: +To find out what columns a table has, use `explain`: {% highlight bash %} $ sqlsh explain plan with type for select \* from du @@ -148,23 +191,6 @@ path VARCHAR CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT N size_b BIGINT NOT NULL {% endhighlight %} -## Aggregation and top-N - -Which user has the most processes? In bash: - -{% highlight bash %} -$ ps aux | awk '{print $1}' | sort | uniq -c | sort -nr | head -3 -{% endhighlight %} - -In `sqlsh`: - -{% highlight bash %} -$ ./sqlsh select count\(\*\), ps.\`user\` from ps group by ps.\`user\` order by 1 desc limit 3 -185 root -69 jhyde -2 avahi -{% endhighlight %} - ## Example: git How many commits and distinct authors per year? http://git-wip-us.apache.org/repos/asf/calcite/blob/a2880dd5/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 9fe6876..e911765 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -155,7 +155,7 @@ query: [ ORDER BY orderItem [, orderItem ]* ] [ LIMIT [ start, ] { count | ALL } ] [ OFFSET start { ROW | ROWS } ] - [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ] + [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] withItem: name
