(Click to open topic with navigation)

4.102 Insight Database

This resource is deprecated beginning with the 9.0.2 release and will be removed in a future release. Use the viewpoint-query-helper plugin instead. See 4.224 ViewpointQueryHelper Plugin.

This section describes the behavior of the Insight database query API in Moab Web Services. It contains the URLs and responses delivered to and from MWS.

Supported methods

Resource GET PUT POST DELETE
/rest/insight/priv/<view> Get All Rows in a View -- -- --
/rest/insight/user/<view> Get All Rows the Current User Can See -- -- --

This topic contains these sections:

4.102.1 Getting Rows in a Relational Database View

Queries with JSON operators

The HTTP GET method is used to retrieve rows in a view or table of the Insight database in JSON format. An array of JSON objects is returned. Each JSON object corresponds to a row in the view/table. The field name and values in each JSON object correspond the column names and values in the view/table. For example if view myview1 in the Insight database contained the following data:

Table 4-2: "myview1"

column1 column2 column3 user_name
alpha delta 1 wsmith
bravo echo 2 tjones
charlie echo 3 wsmith

Then you would query this data as follows:

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3

The resulting JSON would look like this:

[
  {
    "column1":"alpha",
    "column2":"delta",
    "column3":1,
    "user_name":"wsmith"
  },
  {
    "column1":"bravo",
    "column2":"echo",
    "column3":2,
    "user_name":"tjones"
  },
  {
    "column1":"charlie",
    "column2":"echo",
    "column3":3,
    "user_name":"wsmith"
  }
]

You can query using MongoDB comparision and logical operators. For example:

Supported Mongo comparison operators include $gt, $gte, $in, $lt, $lte, $ne, and $nin. Supported logical operators include $or, $and, $not, and $nor.

Queries with dates and times

You can also query using date/time. For example:

Queries with wildcards

You can query for rows where a column value matches a specified pattern, instead of matching an exact string, by using the $like operator, which is similar to the SQL LIKE operator. $like allows the user of "%" as a wildcard that matches any substring. For example:

When performing $like queries, you may need to replace % with the URL-encoded value of %25; otherwise, the meta-character may be interpreted as %.

You can also use "_" to substitute for any single character. For example, to query for all rows that start with any character and end in "cho":

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&query={"column3":{"$like":"_cho"}}

This matches rows where colum3 has the value "acho," "bcho," "ccho," "dcho," "echo," etc.

Privileged queries vs. user queries

The examples above show queries performed using the privileged query URL (/rest/insight/priv). The privileged query does not impose any additional conditions to what you specify in your query and max and offset parameters. By default only administrators have permission to use the privileged query URL. If you grant an unprivileged user access to the privileged query URL, the user could see data he should not be permitted to see.

If an unprivileged user needs to query the database view, he should only have permission to do so with the user query URL. This URL (/rest/insight/user) adds the implicit condition that the value for the user_name column must match that of the user that is currently logged in.

For example, the following two queries, the first a user query performed by user wsmith and the second a privileged query, are essentially the same:

GET http://localhost:8080/mws/rest/insight/user/myview1?api-version=3&query={"column2":"echo"}
GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&query={$and":[{"column2":"echo"},{"user_name":"wsmith"}]

Note that the user query URL can only access views that have a user_name column.

Field selection

To select which columns you want to see in the results, use the fields parameter. For example, to see only column1 and column2:

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&fields=column1,column2

Sorting

You can also sort the results by a one or more columns in ascending or descending order. Specifying additional sort columns provides an alternate value by which MWS will sort the rows if the values in the first column(s) match in multiple rows. There is no limit to the number of columns you can specify in your sort URL.

All sort columns must be specified in the fields selection parameter.

To sort the results by a single column in ascending order:

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&sort={"column1":1}

To sort the results by a single column in descending order:

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&sort={"column1":-1}

To add a second sort column by which MWS will sort any rows with the same value in the first column:

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&sort=[{"column1":1},{"column2":1}]

To add a third sort column by which MWS will sort any rows with the same values in the first column and the second column:

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&sort=[{"column1":1},{"column2":1},{"column3":1}]

Using aggregate functions, group by, and having clauses

You can use SQL aggregate functions like count(), sum(), and avg(). Aggregate functions perform an operation on a group of rows that have the same value with respect to an SQL 'GROUP BY' clauses. Aggregate functions are specified in the fields URL parameter.

Usage Scenario:

  1. Assume you have the following data:
    2. user_name column1 column2
    alice 3.0 alpha
    alice 11.0 bravo
    alice 4.0 charlie
    bob 5.0 delta
    bob 1.0 echo
    dave 10.0 foxtrot

  1. You want to get the sum of the column1 value for all rows corresponding to a user. For example you would like to know that the sum of column1 values for alice is 3 + 11 + 4 = 18.

  1. You would then use the sum() function and group-by clause as follows:
    2. GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&fields=user_name,sum(column1)&group-by=user_name

    Via SQL

    SELECT user_name,SUM(column1) FROM myview1 GROUP BY user_name

    It will return the following data:

    [
  {
    "user_name":"alice",
    "sum":"18",
  },
  {
    "user_name":"bob",
    "sum":"6",
  },
  {
    "user_name":"dave",
    "sum":"10",
  },
    ]
  1. Now try using the having clause to query only for rows where the results of an aggregate function meet some constraint. For example, to query only for users where the sum of column1 is greater than or equal to 10. Run the following:
    2. GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&fields=user_name,sum(column1)&group-by=user_name&having={"sum(column1)":{"$gte":10}}

    Via SQL

    SELECT user_name,SUM(column1) FROM myview1 GROUP BY user_name HAVING SUM(column1) >= 10

Having clauses have an identical syntax to query clauses except that query clauses constrain individual columns and having clauses constrain the results of aggregate functions over a set of rows that have been grouped together.

Other commonly used SQL aggregate functions include average and count. For example, to find out the

Quick reference

GET http://localhost:8080/mws/rest/insight/priv/<view>?api-version=3
GET http://localhost:8080/mws/rest/insight/user/<view>?api-version=3

4.102.1.A Get All Rows in a View

URLs and parameters

GET http://localhost:8080/mws/rest/insight/priv/<view>?api-version=3
GET http://localhost:8080/mws/rest/insight/user/<view>?api-version=3
Parameter Required Type Description Example
fields No Comma-separated string Includes only specified fields in output. fields=name,type
group-by No Comma-separated-string Used in conjunction with aggregate functions. group-by=name
having No JSON Queries for specific rows using results from aggregate functions. having= {"sum(col1)":100
max No Integer The maximum number of items to return. max=100
offset No Integer The index of the first item to return. offset=200
pretty No Boolean true shows pretty printing of output. Default is false. pretty=true
query No JSON

Queries for specific results.

It is possible to query by one or more fields based on a subset of MongoDB query syntax. (See above.)

 query={"name":"node003"}
sort No JSON Sort the results. Use 1 for ascending and -1 for descending. sort={"name":-1}

Get all rows

Queries performed with the privileged query URL (/rest/insight/priv/<view>) return all rows in a view that match that query.

Sample response

GET http://localhost:8080/mws/rest/insight/priv/node_management_view?api-version=3&pretty=true&fields=name,state,user_name
------------------------------------

{
  "totalCount": 5,
  "resultCount": 5,
  "results":   [
    {
    "job_name": "Moab.613",
    "job_state": "Running",
    "user_name": "wsmith"
    },
    {
    "job_name": "Moab.614",
    "job_state": "Running",
    "user_name": "tjones"
    },
    {
    "job_name": "Moab.615",
    "job_state": "Idle",
    "user_name": "wsmith"
    },
    {
    "job_name": "Moab.616",
    "job_state": "Running",
    "user_name": "tjones"
    },
    {
    "job_name": "Moab.617",
    "job_state": "Idle",
    "user_name": "tjones"
    },
   ]
}

4.102.1.B Get All Rows the Current User Can See

Queries performed with the user query URL (/rest/insight/user/<view>) only return rows that the current user is authorized to see. The rows returned contain values in the user_name column that match that of the user who is currently logged in. For example, if tjones is logged in, then the user query URL will only return rows where user_name is equal to "tjones" and that match any other query submitted.

Note that you can only use the user query URL to query views with a user_name column.

Sample response

GET http://localhost:8080/mws/rest/insight/user/node_management_view?api-version=3&pretty=true&fields=name,state,user_name&query={“job_state”:”Running”}
{
  "totalCount": 2,
  "resultCount": 2,
  "results":   [
    {
      "job_name": "Moab.614",
      "job_state": "Running",
      "user_name": "tjones"
    },
    {
      "job_name": "Moab.616",
      "job_state": “Running",
      "user_name": "tjones"
    },
  ]
}

Because tjones is currently logged in, MWS only returns information about running jobs submitted by tjones even though the query does not explicitly filter by user_name.

Related Topics 

© 2016 Adaptive Computing