Query sensor data with third-party tools

You can use the data management service to capture sensor data from any machine and sync that data to the cloud. Then, you can follow the steps on this page to query it using SQL or MQL. For example, you can configure data capture for several sensors on one machine, or for several sensors across multiple machines, to report the ambient operating temperature. You can then run queries against that data to search for outliers or edge cases, to analyze how the ambient temperature affects your machines’ operation.

Prerequisites

Captured sensor data. Click to see instructions.
Follow the guide to capture sensor data.
The Viam CLI to set up data query. Click to see instructions.

You must have the Viam CLI installed to configure querying with third-party tools.

To download the Viam CLI on a macOS computer, run the following commands:

brew tap viamrobotics/brews
brew install viam

To download the Viam CLI on a Linux computer with the aarch64 architecture, run the following commands:

sudo curl -o /usr/local/bin/viam https://storage.googleapis.com/packages.viam.com/apps/viam-cli/viam-cli-stable-linux-arm64
sudo chmod a+rx /usr/local/bin/viam

To download the Viam CLI on a Linux computer with the amd64 (Intel x86_64) architecture, run the following commands:

sudo curl -o /usr/local/bin/viam https://storage.googleapis.com/packages.viam.com/apps/viam-cli/viam-cli-stable-linux-amd64
sudo chmod a+rx /usr/local/bin/viam

You can also install the Viam CLI using brew on Linux amd64 (Intel x86_64):

brew tap viamrobotics/brews
brew install viam

If you have Go installed, you can build the Viam CLI directly from source using the go install command:

go install go.viam.com/rdk/cli/viam@latest

To confirm viam is installed and ready to use, issue the viam command from your terminal. If you see help instructions, everything is correctly installed. If you do not see help instructions, add your local go/bin/* directory to your PATH variable. If you use bash as your shell, you can use the following command:

echo 'export PATH="$HOME/go/bin:$PATH"' >> ~/.bashrc

For more information see install the Viam CLI.

`mongosh` or another third-party tool for querying data. Click to see instructions.
Download the mongosh shell or another third-party tool that can connect to a MongoDB data source to follow along. See the mongosh documentation for more information.

Query data in the Viam app

Once your data has synced, you can query your data from within the Viam app using SQL or MQL.

You must have the owner role in order to query data in the Viam app.

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.

See examples
  • The following shows a SQL query that filters by component name and specific column names, and its returned results:

    Viam App Data Query tab with a SQL query shown and results shown below including two matching records
  • The following shows a SQL query that returns a count of records matching the search criteria:

    Viam App Data Query tab with a SQL query shown with the resulting count of matching records displayed below

For more information on MQL syntax, see the MQL (MongoDB Query Language) documentation.

Configure data query

If you want to query data from third party tools, you have to configure data query to obtain the credentials you need to connect to the third party service.

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. Your password must be at least 8 characters long, and include at least one uppercase, one number, and one special character (such as $ or %):

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
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 both the connection URI to your organization’s MongoDB Atlas Data Federation instance, as well as its hostname and database name:

  • 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 this information to query your data in the next section.

Query data using third-party tools

You can use third-party tools, such as the mongosh shell, MongoDB Compass, to query against captured sensor data.

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 "<YOUR-DB-CONNECTION-URI>"

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.

The following MQL query performs the same search as the SQL query above, but uses the MongoDB query language:

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 data.readings.distance > 0.2",
            format: "jdbc"
        }}
    ] )
[ { '': { numStanding: 215 } } ]

The following query uses the MongoDB $sql aggregation pipeline stage:

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 } ]

For information on connecting to your Atlas instance from other MQL clients, see the MongoDB Atlas Connect to your Cluster Tutorial.

Need to query by date? Click here.
Query by date

When using MQL to query your data by date or time range, you can optimize query performance by avoiding the MongoDB $toDate expression, using the BSON date type instead.

For example, use the following query to search by a date range in the mongosh shell, using the JavaScript Date() constructor to specify an explicit start timestamp, and use the current time as the end timestamp:

// 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 }
        } }
    ] )

Next steps

On top of querying sensor data with third-party tools, you can also query it with the Python SDK or visualize it.

To see sensor data in action, check out this tutorial:

Have questions, or want to meet other people working on robots? Join our Community Discord.

If you notice any issues with the documentation, feel free to file an issue or edit this file.