Query sensor data with third-party tools

1. Query with SQL or MQL

Navigate to the Query page. Then, select either SQL or MQL from the Query mode dropdown menu on the right-hand side.

2. Run your query

This example query returns 5 readings from a component called my-sensor:

SELECT * FROM readings
WHERE component_name = 'my-sensor' LIMIT 5

[
    { "$match": { "component_name": "my-sensor" } },
    { "$limit": 5 }
]

3. Review results

Click Run query when ready to perform your query and get matching results. Query results are displayed as a JSON array below your query.

1. Authenticate with the CLI

Authenticate using a personal access token:

viam login

2. Find your organization ID

To create a database user allowing you to access your data, find your organization ID:

viam organizations list

3. Configure a new database user

Configure a new database user for the Viam organization’s MongoDB Atlas Data Federation instance, which is where your machine’s synced data is stored.

Provide your organization’s org-id from step 2, and a password for your database user.

viam data database configure --org-id=<YOUR-ORGANIZATION-ID> --password=<NEW-DBUSER-PASSWORD>

This command configures a database user for your organization for use with data query, and sets the password. If you have run this command before, this command instead updates the password to the new value you set.

4. Determine the connection URI

Determine the connection URI (also known as a connection string) for your organization’s MongoDB Atlas Data Federation instance by running the following command with the organization’s org-id from step 2:

viam data database hostname --org-id=abcd1e2f-a1b2-3c45-de6f-ab123456c123
# Example output
MongoDB Atlas Data Federation instance hostname: data-federation-abcd1e2f-a1b2-3c45-de6f-ab123456c123-0z9yx.a.query.mongodb.net
MongoDB Atlas Data Federation instance connection URI: mongodb://db-user-abcd1e2f-a1b2-3c45-de6f-ab123456c123:YOUR-PASSWORD-HERE@data-federation-abcd1e2f-a1b2-3c45-de6f-ab123456c123-0z9yx.a.query.mongodb.net/?ssl=true&authSource=admin

This command returns:

• hostname: the MongoDB Atlas Data Federation instance hostname

• connection URI: the MongoDB Atlas Data Federation instance connection uniform resource indicator. This is the connection URI to your organization’s MongoDB Atlas Data Federation instance, which is of the form:

  mongodb://<USERNAME>:<YOUR-PASSWORD>@<HOSTNAME>/?ssl=true&authSource=admin
  

Most MQL-compatible database clients require the connection URI, along with your user credentials, to connect to this server.

Some MQL-compatible database client instead require a hostname and database name, along with your user credentials, to connect to this server.

You will need the connection URI to query your data in the next section.

1. Connect to your Viam organization’s data

Run the following command to connect to your Viam organization’s MongoDB Atlas instance from mongosh using the connection URI you obtained during query configuration:

mongosh "mongodb://db-user-abcd1e2f-a1b2-3c45-de6f-ab123456c123:YOUR-PASSWORD-HERE@data-federation-abcd1e2f-a1b2-3c45-de6f-ab123456c123-0z9yx.a.query.mongodb.net/?ssl=true&authSource=admin"

2. Query data from a compatible client

Once connected, you can run SQL or MQL statements to query captured data directly.

The following query searches the sensorData database and readings collection, and gets sensor readings from an ultrasonic sensor on a specific robot_id where the recorded distance measurement is greater than .2 meters.

use sensorData
db.readings.aggregate(
    [
        { $match: {
            'robot_id': 'abcdef12-abcd-abcd-abcd-abcdef123456',
            'component_name': 'my-ultrasonic-sensor',
            'data.readings.distance': { $gt: .2 } } },
        { $count: 'numStanding' }
    ] )
[ { numStanding: 215 } ]

use sensorData
db.aggregate(
[
    { $sql: {
        statement: "select count(*) as numStanding from readings \
            where robot_id = 'abcdef12-abcd-abcd-abcd-abcdef123456' and \
            component_name = 'my-ultrasonic-sensor' and (CAST (data.readings.distance AS DOUBLE)) > 0.2",
        format: "jdbc"
    }}
] )
[ { '': { numStanding: 215 } } ]

select data.`value` from readings

// Switch to sensorData database:
use sensorData

// Set desired start and end times:
const startTime = new Date('2024-02-10T19:45:07.000Z')
const endTime = new Date()

// Run query using $match:
db.readings.aggregate(
    [
        { $match: {
            time_received: {
                $gte: startTime,
                $lte: endTime }
        } }
    ] )