Vmstat table function for sqlsh
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/45b405c4 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/45b405c4 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/45b405c4 Branch: refs/heads/master Commit: 45b405c4c97474b32e58e9a8a55d159e2044685d Parents: d23e529 Author: Josh Elser <[email protected]> Authored: Tue Jul 25 15:33:08 2017 -0400 Committer: Julian Hyde <[email protected]> Committed: Thu Jul 27 17:07:47 2017 -0700 ---------------------------------------------------------------------- .../org/apache/calcite/adapter/os/SqlShell.java | 2 + .../calcite/adapter/os/VmstatTableFunction.java | 160 +++++++++++++++++++ .../calcite/adapter/os/OsAdapterTest.java | 21 +++ site/_docs/os_adapter.md | 27 +++- 4 files changed, 208 insertions(+), 2 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/45b405c4/plus/src/main/java/org/apache/calcite/adapter/os/SqlShell.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/SqlShell.java b/plus/src/main/java/org/apache/calcite/adapter/os/SqlShell.java index 8ec69dd..bf91b41 100644 --- a/plus/src/main/java/org/apache/calcite/adapter/os/SqlShell.java +++ b/plus/src/main/java/org/apache/calcite/adapter/os/SqlShell.java @@ -78,6 +78,7 @@ public class SqlShell { addView(b, "git_commits", "select * from table(\"git_commits\"(true))"); addView(b, "ps", "select * from table(\"ps\"(true))"); addView(b, "stdin", "select * from table(\"stdin\"(true))"); + addView(b, "vmstat", "select * from table(\"vmstat\"(true))"); b.append(" } ],\n") .append(" functions: [ {\n"); addFunction(b, "du", DuTableFunction.class); @@ -85,6 +86,7 @@ public class SqlShell { addFunction(b, "git_commits", GitCommitsTableFunction.class); addFunction(b, "ps", PsTableFunction.class); addFunction(b, "stdin", StdinTableFunction.class); + addFunction(b, "vmstat", VmstatTableFunction.class); b.append(" } ]\n") .append(" }\n") .append(" ]\n") http://git-wip-us.apache.org/repos/asf/calcite/blob/45b405c4/plus/src/main/java/org/apache/calcite/adapter/os/VmstatTableFunction.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/VmstatTableFunction.java b/plus/src/main/java/org/apache/calcite/adapter/os/VmstatTableFunction.java new file mode 100644 index 0000000..5b7de14 --- /dev/null +++ b/plus/src/main/java/org/apache/calcite/adapter/os/VmstatTableFunction.java @@ -0,0 +1,160 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.adapter.os; + +import org.apache.calcite.DataContext; +import org.apache.calcite.linq4j.Enumerable; +import org.apache.calcite.linq4j.function.Function1; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeFactory; +import org.apache.calcite.rel.type.RelDataTypeFactory.FieldInfoBuilder; +import org.apache.calcite.schema.ScannableTable; +import org.apache.calcite.schema.Schema; +import org.apache.calcite.schema.Statistic; +import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.ImmutableBitSet; +import org.apache.calcite.util.Util; + +import com.google.common.collect.ImmutableList; + +import java.util.List; + +/** + * Table function that executes the OS "vmstat" command + * to share memory statistics. + */ +public class VmstatTableFunction { + + private VmstatTableFunction() {} + + public static ScannableTable eval(boolean b) { + return new ScannableTable() { + public Enumerable<Object[]> scan(DataContext root) { + final RelDataType rowType = getRowType(root.getTypeFactory()); + final List<String> fieldNames = + ImmutableList.copyOf(rowType.getFieldNames()); + final String[] args; + final String osName = System.getProperty("os.name"); + final String osVersion = System.getProperty("os.version"); + Util.discard(osVersion); + // Fork out to a shell so that we can get normal text-munging support. + // Could do this here too.. + switch (osName) { + case "Mac OS X": // tested on version 10.11.6 + args = new String[]{ + "/bin/sh", "-c", + "vm_stat | tail -n +2 | awk '{print $NF}' | sed 's/\\.//' | tr '\\n' ' '"}; + break; + default: + args = new String[]{"/bin/sh", "-c", "vmstat -n | tail -n +3"}; + } + return Processes.processLines(args) + .select( + new Function1<String, Object[]>() { + public Object[] apply(String line) { + final String[] fields = line.trim().split("\\s+"); + final Object[] values = new Object[fieldNames.size()]; + for (int i = 0; i < values.length; i++) { + try { + values[i] = field(fieldNames.get(i), fields[i]); + } catch (RuntimeException e) { + e.printStackTrace(System.out); + throw new RuntimeException("while parsing value [" + + fields[i] + "] of field [" + fieldNames.get(i) + + "] in line [" + line + "]"); + } + } + return values; + } + + private Object field(String field, String value) { + if (value.isEmpty()) { + return 0; + } + if (value.endsWith(".")) { + return Long.parseLong(value.substring(0, value.length())); + } + return Long.parseLong(value); + } + }); + } + + public RelDataType getRowType(RelDataTypeFactory typeFactory) { + final String osName = System.getProperty("os.name"); + final FieldInfoBuilder builder = typeFactory.builder(); + switch (osName) { + case "Mac OS X": + return builder + .add("pages_free", SqlTypeName.BIGINT) + .add("pages_active", SqlTypeName.BIGINT) + .add("pages_inactive", SqlTypeName.BIGINT) + .add("pages_speculative", SqlTypeName.BIGINT) + .add("pages_throttled", SqlTypeName.BIGINT) + .add("pages_wired_down", SqlTypeName.BIGINT) + .add("pages_purgeable", SqlTypeName.BIGINT) + .add("translation_faults", SqlTypeName.BIGINT) + .add("pages_copy_on_write", SqlTypeName.BIGINT) + .add("pages_zero_filed", SqlTypeName.BIGINT) + .add("pages_reactivated", SqlTypeName.BIGINT) + .add("pages_purged", SqlTypeName.BIGINT) + .add("pages_file_backed", SqlTypeName.BIGINT) + .add("pages_anonymous", SqlTypeName.BIGINT) + .add("pages_stored_compressor", SqlTypeName.BIGINT) + .add("pages_occupied_compressor", SqlTypeName.BIGINT) + .add("decompressions", SqlTypeName.BIGINT) + .add("compressions", SqlTypeName.BIGINT) + .add("pageins", SqlTypeName.BIGINT) + .add("pageouts", SqlTypeName.BIGINT) + .add("swapins", SqlTypeName.BIGINT) + .add("swapouts", SqlTypeName.BIGINT) + .build(); + default: + return builder + .add("proc_r", SqlTypeName.BIGINT) + .add("proc_b", SqlTypeName.BIGINT) + .add("mem_swpd", SqlTypeName.BIGINT) + .add("mem_free", SqlTypeName.BIGINT) + .add("mem_buff", SqlTypeName.BIGINT) + .add("mem_cache", SqlTypeName.BIGINT) + .add("swap_si", SqlTypeName.BIGINT) + .add("swap_so", SqlTypeName.BIGINT) + .add("io_bi", SqlTypeName.BIGINT) + .add("io_bo", SqlTypeName.BIGINT) + .add("system_in", SqlTypeName.BIGINT) + .add("system_cs", SqlTypeName.BIGINT) + .add("cpu_us", SqlTypeName.BIGINT) + .add("cpu_sy", SqlTypeName.BIGINT) + .add("cpu_id", SqlTypeName.BIGINT) + .add("cpu_wa", SqlTypeName.BIGINT) + .add("cpu_st", SqlTypeName.BIGINT) + .build(); + } + } + + public Statistic getStatistic() { + return Statistics.of(1000d, ImmutableList.of(ImmutableBitSet.of(1))); + } + + public Schema.TableType getJdbcTableType() { + return Schema.TableType.TABLE; + } + }; + } +} + +// End VmstatTableFunction.java http://git-wip-us.apache.org/repos/asf/calcite/blob/45b405c4/plus/src/test/java/org/apache/calcite/adapter/os/OsAdapterTest.java ---------------------------------------------------------------------- diff --git a/plus/src/test/java/org/apache/calcite/adapter/os/OsAdapterTest.java b/plus/src/test/java/org/apache/calcite/adapter/os/OsAdapterTest.java index 0f1adbb..69d142b 100644 --- a/plus/src/test/java/org/apache/calcite/adapter/os/OsAdapterTest.java +++ b/plus/src/test/java/org/apache/calcite/adapter/os/OsAdapterTest.java @@ -54,6 +54,7 @@ import static org.junit.Assert.fail; * <li>./sqlsh select \* from git_commits * <li>./sqlsh select \* from ps * <li>(echo cats; echo and dogs) | ./sqlsh select \* from stdin + * <li>./sqlsh select \* from vmstat * </ul> */ public class OsAdapterTest { @@ -164,6 +165,26 @@ public class OsAdapterTest { sql(q).returnsUnordered("author=Julian Hyde <[email protected]>"); } + @Test public void testVmstat() { + sql("select * from vmstat") + .returns( + new Function<ResultSet, Void>() { + public Void apply(ResultSet r) { + try { + assertThat(r.next(), is(true)); + final int c = r.getMetaData().getColumnCount(); + for (int i = 0; i < c; i++) { + assertThat(r.getLong(i + 1), notNullValue()); + assertThat(r.wasNull(), is(false)); + } + return null; + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + }); + } + @Test public void testStdin() throws SQLException { try (Hook.Closeable ignore = Hook.STANDARD_STREAMS.addThread( new Function<Holder<Object[]>, Void>() { http://git-wip-us.apache.org/repos/asf/calcite/blob/45b405c4/site/_docs/os_adapter.md ---------------------------------------------------------------------- diff --git a/site/_docs/os_adapter.md b/site/_docs/os_adapter.md index 1636485..d8f8d5e 100644 --- a/site/_docs/os_adapter.md +++ b/site/_docs/os_adapter.md @@ -39,6 +39,16 @@ The OS adapter launches processes, and is potentially a security loop-hole. It is included in Calcite's "plus" module, which is not enabled by default. You must think carefully before enabling it in a security-sensitive situation. +# Compatibility + +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 + # A simple example Every bash hacker knows that to find the 3 largest files you type @@ -76,11 +86,12 @@ care. Often adding a back-slash will suffice. # Tables and commands The OS adapter contains the following tables: -* `du` - Disk usage -* `ps` - Processes +* `du` - Disk usage (based on `du` command) +* `ps` - Processes (based on `ps` command) * `stdin` - Standard input * `files` - Files (based on the `find` command) * `git_commits` - Git commits (based on `git log`) +* `vmstat` - Virtual memory (based on `vmstat` command) Most tables are implemented as views on top of table functions. @@ -114,6 +125,18 @@ daemon The `ps.` qualifier is necessary because USER is a SQL reserved word. +# Example: vmstat + +{% highlight bash %} +$ ./sqlsh -o mysql select \* from vmstat ++--------+--------+----------+----------+----------+-----------+---------+---------+-------+-------+-----------+-----------+--------+--------+--------+--------+--------+ +| proc_r | proc_b | mem_swpd | mem_free | mem_buff | mem_cache | swap_si | swap_so | io_bi | io_bo | system_in | system_cs | cpu_us | cpu_sy | cpu_id | cpu_wa | cpu_st | ++--------+--------+----------+----------+----------+-----------+---------+---------+-------+-------+-----------+-----------+--------+--------+--------+--------+--------+ +| 12 | 0 | 54220 | 5174424 | 402180 | 4402196 | 0 | 0 | 15 | 35 | 3 | 2 | 7 | 1 | 92 | 0 | 0 | ++--------+--------+----------+----------+----------+-----------+---------+---------+-------+-------+-----------+-----------+--------+--------+--------+--------+--------+ +(1 row) +{% endhighlight %} + ## Example: explain To find out what columns a table has, use {{explain}}:
