Edit

Query Data with SQL or MQL

Once you have added the data management service and synced tabular data to the Viam app, you can perform queries against that data using either SQL or MQL.

You can:

Query tabular data in the Viam app: Run SQL or MQL queries against your synced tabular data from the Query subtab under the Data tab in the Viam app.
Query tabular data directly from a compatible client: Directly query tabular data from an MQL-compatible client, such as mongosh.

You can run queries against both the captured tabular data itself as well as its metadata, including machine ID, organization ID, and tags.

Only tabular data, such as sensor readings, can be queried using SQL or MQL. To search non-tabular data, such as images, see Filter Data. To perform searches against tabular data from within the Python SDK, use the TabularDataByFilter method.

Requirements

Before you can configure data query, you must:

Add the data management service to your machine.
Configure data capture for at least one component, such as a sensor. Only components that capture tabular data support data query. To search non-tabular data, see Filter Data.
Configure cloud sync, and sync data to the Viam app. When you are able to view your data in the Viam app, you are ready to proceed.

Query tabular data in the Viam app

Once you have synced tabular data to the Viam app, you can run SQL or MQL queries against your synced data from the Query subtab under the Data tab in the Viam app. You must have the owner role in order to query data in the Viam app.

Navigate to the Query subtab.
Select either SQL or MQL from the Query mode dropdown menu on the right-hand side, then enter your query using the respective syntax for the language you have selected in the text area at the top of your screen. For example:
- The following shows a SQL query that filters by the component name my-ultrasonic-sensor and limits the returned results to 5:
- The following shows the same search using MQL syntax:
Click Run query when ready to perform your query and get matching results. Query results are displayed as a JSON array below your query. For example:
- The following shows a SQL query that filters by component name and specific column names, and its returned results:
- The following shows a SQL query that returns a count of records matching the search criteria:

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

Query tabular data directly from a compatible client

Configure direct data query to be able to query captured tabular data in the Viam cloud using MQL or SQL from a MQL-compatible client, such as mongosh or MongoDB Compass. Synced data is stored in a MongoDB Atlas Data Federation instance.

You can query against both the captured tabular data itself as well as its metadata, including machine ID, organization ID, and tags.

Only tabular data, such as sensor readings, can be queried in this fashion.

Before being able to query data, you must configure data query.

Configure data query

Important

These steps are only required when querying tabular data directly from an MQL-compatible client, such as mongosh. You do not need to perform any additional configuration when querying data in the Viam app.

If you haven’t already, install the Viam CLI and authenticate to Viam.
Find your organization ID by running the following command, or from your organization’s Settings page in the Viam App:
```
viam organizations list
```
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 desired new 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 new database user for your org for use with data query. If you have already created this user, this command updates the password for that user instead.
Determine the hostname 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=<YOUR-ORGANIZATION-ID>
```
This command returns the hostname (including database name) to use to connect to your data store on the Viam organization’s MongoDB Atlas instance. You will need this to query your data in the next section.

For more information, see the documentation for the Viam CLI database command.

Query

Once you have synced tabular data to the Viam app, you can directly query that data from an MQL-compatible client, such as mongosh or Compass.

Open your chosen MQL-compatible client an connect to the Viam organization’s MongoDB Atlas instance. You can use any client that is capable of connecting to a MongoDB Atlas instance, including the mongosh shell, MongoDB Compass, and many third-party tools. To connect, use the hostname you determined when you configured direct data query, and structure your username in the following format:
```
db-user-<YOUR-ORG-ID>
```
Where <YOUR-ORG-ID> is your organization ID, determined from the viam organizations list CLI command. The full username you provide to your client should therefore resemble db-user-abcdef12-abcd-abcd-abcd-abcdef123456.

For example, to connect to your Viam organization’s MongoDB Atlas instance and query data using the mongosh shell:

If you haven’t already, download the mongosh shell. See the mongosh documentation for more information.
Run the following command to connect to the Viam organization’s MongoDB Atlas instance from mongosh:
```
mongosh "mongodb://<YOUR-DB-HOSTNAME>" --tls --authenticationDatabase admin --username db-user-<YOUR-ORG-ID>
```
Where:
- <YOUR-DB-HOSTNAME> is your organization’s assigned MongoDB Atlas instance hostname, determined from the viam data database hostname CLI command.
- <YOUR-ORG-ID> is your organization ID, determined from the viam organizations list CLI command. The full username you provide to the --username flag should therefore resemble db-user-abcdef12-abcd-abcd-abcd-abcdef123456.

Once connected, you can run SQL or MQL to query captured data directly. For example:

The following SQL query uses the MongoDB $sql aggregation pipeline stage to search the sensorData database and readings collection, and get sensor readings from an ultrasonic sensor on a specific robot_id where the recorded distance measurement is greater than .2 meters:

// Switch to sensorData database:
use sensorData

// Run query using $sql:
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 MQL query performs the same search as the SQL query above, but uses the MongoDB query language:

// Switch to sensorData database:
use sensorData

// Run query using $match:
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 } ]

Tip

If you use a data field that is named the same as a reserved SQL keyword, such as value or position, you must escape that field name in your query using backticks ( ` ). For example, to query against a field named value which is a subfield of the data field in the readings collection, you would use:

select data.`value` from readings

See the MongoDB Atlas Documentation for more information.

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

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, you could 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

With data query enabled, you can now visualize your machine’s uploaded tabular data using many popular data visualization services, such as Grafana. See Visualize Data for instructions on setting up and using these data visualization services with Viam, or the Visualize data with Grafana tutorial for a detailed walkthrough specific to Grafana.

To export your captured data from the cloud, see Export Data.

To adjust the rate at which your machine captures data, see Configure Data Capture.

To adjust the sync frequency, see Configure Cloud Sync.

Was this page helpful?

Glad to hear it! If you have any other feedback please let us know:

We're sorry about that. To help us improve, please tell us what we can do better:

Thank you!