Description

The @QueryStats system procedure lets you query the results of the @Statistics system procedure as if it were a database table, filtering, aggregating, and ordering the results as you wish. You specify the query in a SQL-like statement as an argument to the procedure.

The query string is formatted as a SELECT statement, using the statistics result columns as selection expressions and FROM STATISTICS(selector,delta-flag) as the table specifier. For example, the following query string returns the names of all export targets and their associated tables:

SELECT target, source from STATISTICS(EXPORT,0);

You can also use standard SQL functions and clauses such as WHERE, GROUP BY, and ORDER BY to filter, aggregate, and re-order the output. For example, the following query reports the total number of pending rows for each currently active export target, sorted in descending order:

SELECT target, SUM(tuple_pending) from STATISTICS(EXPORT,0)
   WHERE active = 'TRUE' GROUP BY target ORDER BY SUM(tuple_pending) DESC;

Note that although the query string is SQL like, it is not a true SQL statement and not all SQL expressions are supported. For instance, you cannot use complex arithmetic expressions or all forms of joins or subclauses. However, you can join multiple results as long as you separate the "tables" with commas and specify the join constraints in the WHERE clause. For example, the following example joins information about transaction invocations with the resulting output size per host and connection:

SELECT a.hostname, a.connection_id, 
       SUM(a.invocations), SUM(b.bytes_written)
  from STATISTICS(initiator,0) AS a,  STATISTICS(iostats,0) as b
       WHERE a.connection_id = b.connection_id 
       GROUP BY a.hostname, a.connection_id;

Return Values

Returns one VoltTable with the results of the query. The name, number, and datatype of the columns correspond to the columns in the query. The number of rows matches the number of @Statistics results matching the query.

Examples

The following program example uses @QueryStats to determine how many clients are connected to each host of the cluster.

try {
    String query = "SELECT hostname, count(*)" +
          " from statistics(liveclients,0) group by hostname;"; 
    VoltTable[] results = client.callProcedure("@QueryStats",
       query ).getResults();
    return results;
}
catch (Exception e) {
    e.printStackTrace();
}

In the sqlcmd utility, you can use the querystats directive as a short cut for invoking the @QueryStats system procedure.

$ sqlcmd
1> querystats SELECT hostname,count(*) from statistics(liveclients,0) group by hostname;