Custom Queries
The Custom Queries feature provides the ability to retrieve and modify data stored in AnythingDB using ArangoDB Query Language (AQL). AnythingDB is built on ArangoDB.
Access this guide to learn more about ArangoDB in Altair IoT Studio.
- Databases
- Databases allow isolating sets of collections from one to another. Graph databases organize and store data using a graph-like structure, where data entities are represented as nodes and their relationships as edges (also called links).
- Collections
- Collections store documents, similar to how files are stored in folders. You can use them to group records of similar kinds together. Collection types include:
- Documents
- Documents are referred to the JSON objects store.
Following this structure, in Altair IoT Studio, each Space will have its own database in AnythingDB and the data will be separated from other Spaces.
Each database will have the following Collections (please note Collections cannot be removed and new ones cannot be created).
- Type document:
-
Categories
: store documents about the Categories definedModels
: store documents about the Models definedVersions
: store documents about the Versions of the different Models definedThings
: store documents about the Things definedpropertiesHistory
: store documents with the records of Properties definedactionsHistory
: store documents with the records of Actions definedeventsHistory
: store documents with the records of Events defined
- Type edge:
-
Links
: store connections between Things. Note that connections between other elements are not allowed.
Each Collection will contain multiple documents. Each document could be defined with a set of attributes as shown in the schema below:
![](../../images/anythingdb/sw_graph.png)
It is now possible to run custom queries directly on the database. New endpoints have been added to execute AQL queries that will run in the ArangoDB database where the AnythingDB's Space information is stored.
The new endpoints that we have created are compatible with the ArangoDB API, so it means handling ArangoDB cursors. Here is the documentation about ArangoDB query and how to use it.
Running a Query
Queries can be run directly from the interface in Custom Queries.
Click + New Custom Query.
By default, the code corresponding to one of the examples offered by the platform is included. Find below a description for the different elements of the request:
- query
- The Anything DB query itself with the data to be collected from the database.
- count
- True/False. Define a count of items matching the query that should be returned in the result or not.
- batchSize
- Defines the number of elements to display in the response.
- bindVars
- Variables referenced in the query.
The response returned by the platform will have the following parameters:
- code
- Response status code
- count
- Number of elements that matched the query
- error
- True / false, depending on existing errors
- extra
- (stats): Stats about the execution of the query
- id
- See the pagination section below for more information
- hasMore
- True / false depending on if all elements were returned in result
- result
- Elements returned by the query.
Example Queries
To help you understand this tool, three query examples are included that can be chosen from the drop-down menu located at the top of the page.
To use these examples, replace the values in the bindVars parameter of the body.
Using AnythingDB Queries from a User Function
A powerful way to explore the use of AnythingDB from within the Altair IoT Studio platform is by automating these queries through the use of Functions. To be able to incorporate calls in the functions, use the https protocol using a POST call.
You must have the necessary credentials to make the call following the basic operation of our API.
https://api.swx.altairone.com/spaces/YourSpaceID/query/cursor/
The body of the POST call must include in JSON format the fields defined above, where the query to be made will be included.
The following code snippet includes an example of a Python function to perform a query using the cursor endpoint.
from swx.auth.token import get_token import requests import json API_HOST = 'https://api.swx.altairone.com' CLIENT_ID = "xxx::xxxxxxxxxxxxxxxxxxxx" CLIENT_SECRET = "xxxxxxxxxxxxxxxxxxxxxxx" def handle(req): with get_token(CLIENT_ID, CLIENT_SECRET, ["query"]) as token: data ={"query": "FOR c IN categories FILTER c.name ==@category_name FOR t IN things FILTER c._id IN t.categories RETURN t.status.name", "batchSize":5, "bindVars":{ "category_name":"CategoryName" } } PATH="/spaces/spacename/query/cursor/" headers = {"Authorization": "Bearer " + token.access_token} response = requests.request("POST", API_HOST + PATH , headers=headers,json=data) return { "body": response.json()['result'], "status_code": response.status_code }
The above example lists the things within the category with the name "CategoryName". For this example, the Client_Id and the Client_Secret correspond to an App previously defined in the Access Control section where the query scope has been established to be used.
Pagination
Occasionally, the result returned after a query will not show all the results. This will be indicated in the response field with the title hasMore. To access the rest of the results, use the field "id" that is returned in the query result.
Make a call with the URL indicated in the previous section, but incorporate the ID that was returned in the response.
https://api.swx.altairone.com/spaces/YourSpaceID/query/cursor/id
You can make calls to this URL sequentially until you get the rest of the values. The hasMore field will return false when the last data has been displayed.
For more information about the API, refer to the documentation.
Query Cursors Examples
This API is used to execute queries directly on the ArangoDB database.
Query Cursor Normal Request
{ "query": "FOR t IN things FILTER HAS(t.properties, @property_name) RETURN t._key", "count": true, "batchSize": 2, "bindVars": { "property_name": "cpu" } }
query
- Contains the query string to be executed. Only read-only queries are allowed.
count
- This is a boolean attribute. If we set it to true, the response will return a "count" field, with the total number of elements in the database that match the query sent.
batchSize
- Indicates the maximum number of results we want to be returned.
bindVars
- Key/values pair representing the bind parameters in the query. In this example @property_name.
There are more parameters that can be sent in the request, which you can find in the API.
With the request sent above a possible answer that you can obtain is the following:
{ "code": 201, "count": 3, "error": false, "extra": { "stats": { "executionTime": 0.006785640027374029, "peakMemoryUsage": 32768 } }, "hasMore": true, "id": "9895653453478", "result": [ "01H416JXT187JZ8R58NYW6GN97", "01H416KYGQ0X22HKAY0KCXEM9J" ] }
code
- Indicates the HTTP status code of the response.
count
- Total number of elements in the database that match the query sent.
error
- A boolean attribute that indicates if there has been any error executing the request.
extra
- An optional JSON object with extra information about the query result
contained in its
stats
sub-attribute. hasMore
- Boolean attribute that indicates if there are more results available for the cursor on the server.
id
- Temporary cursor id that is returned if there is more available data. It
can be used on the
/spaces/{space}/query/cursor/{cursor-id}
endpoint. result
- Array of result data expected from the query sent.
Query Cursor with Operations to Apply
Operations can be applied to the results
returned but only on Things
.
{ "query": "FOR t IN things FILTER HAS(t.properties, @property_name) RETURN t._key", "count": true, "batchSize": 2, "bindVars": { "property_name": "cpu" }, "then": { "op": "update", "resource": "properties", "data": { "cpu": 10 } } }
then
, with the following parameters:op
- Indicates the type of operation that we want to execute. In this case, we want to update a Thing.
resource
- Indicate on which resource you want to apply the indicated "op". In this
case, we are updating the
Properties
of the Things. data
- Data to be sent. In this case, we indicate the new cpu value.
The response will be as follows:
{ "code": 201, "count": 3, "error": false, "extra": { "stats": { "executionTime": 0.0069512189365923405, "peakMemoryUsage": 32768 } }, "hasMore": true, "id": "9895653458933", "result": [ "01GZG7BNXKECSVFENWDVZT0FA0", "01H416JXT187JZ8R58NYW6GN97" ], "then": { "error_count": 0, "success_count": 2 } }
then
, and it has 2 parameters:error_count
- Number of failed operations. In this case, number of things that have not been updated.
success_count
- Number of succeeded operations. In this case, the number of Things updated correctly.
Query Cursors to Return Linked Things
Since ArangoDB is a graph database, this
functionality has been included for the Links
of the Things, so
every time a Link
of a Thing
points to another
Thing
, a connection between them will be created.
The following example shows which
Things
are linked to each other:
{ "query": "FOR l in links FILTER l._from == @from RETURN l._to", "count": true, "bindVars": { "from": "things/01H4G9T37EYV2BQ0GXRV7V2MMG" } }
The response will be as follows, with the
result
parameters containing all the Things
IDs that matched the previous request:
{ "result": [ "things/01H4G4CWKPBZE6GY2SECA88RYJ", "things/01H4G4CYRHTNT3XSJ2XZHY9Z4R" ], "hasMore": false, "count": 2, "extra": { "stats": { "executionTime": 0.00025816199922701344, "peakMemoryUsage": 32768 } }, "error": false, "code": 201 }