Query Builder

TwinEdge provides powerful tools for querying your industrial data, from a visual drag-and-drop builder to full SQL access.

Professional Feature

Query Builder and SQL Lab are available on Professional and Enterprise plans.

Visual Query Builder

Overview

The visual query builder lets you create queries without writing SQL:

1. Go to Analytics → Query Builder
2. Select data sources and metrics
3. Apply filters and aggregations
4. Preview results
5. Save or export

Building a Query

Step 1: Select Data Source

Choose from:

• Telemetry Data: Time-series sensor readings
• Alerts: Historical alert data
• Assets: Asset metadata and status
• Custom Tables: Your uploaded datasets

Step 2: Choose Metrics

Select columns to include:

• Dimensions: Grouping columns (asset_id, timestamp, tag_name)
• Measures: Numeric columns for aggregation (value, count)

Step 3: Apply Filters

Add filter conditions:

asset_id = 'Pump_001'
AND timestamp >= '2026-01-01'
AND tag_name IN ('vibration_x', 'temperature')

Filter operators:

• =, !=: Exact match
• >, <, >=, <=: Comparison
• IN: Multiple values
• LIKE: Pattern matching
• BETWEEN: Range

Step 4: Set Aggregation

Aggregate functions:

• SUM: Total value
• AVG: Average value
• MIN/MAX: Minimum/Maximum
• COUNT: Number of records
• STDDEV: Standard deviation

Group by:

• Time bucket (minute, hour, day, week, month)
• Asset
• Tag name
• Custom dimension

Step 5: Sort and Limit

• Order By: Sort results
• Limit: Maximum rows to return

Example Queries

Average hourly temperature by asset:

SELECT
  asset_id,
  date_trunc('hour', timestamp) AS hour,
  AVG(value) AS avg_temp
FROM telemetry
WHERE tag_name = 'temperature'
  AND timestamp >= NOW() - INTERVAL '7 days'
GROUP BY asset_id, hour
ORDER BY hour DESC

Alert count by severity (last 30 days):

SELECT
  severity,
  COUNT(*) AS alert_count
FROM alerts
WHERE triggered_at >= NOW() - INTERVAL '30 days'
GROUP BY severity

SQL Lab

Full SQL Access

SQL Lab provides direct SQL access to your data:

1. Go to Analytics → SQL Lab
2. Write or paste your SQL query
3. Click Run Query
4. View results in the table

Schema Browser

Browse available tables and columns:

• Left panel: List of tables
• Click table: View columns and types
• Search: Find specific tables/columns

Available Tables

Table	Description
telemetry	Time-series sensor data
telemetry_hourly	Pre-aggregated hourly data
telemetry_daily	Pre-aggregated daily data
assets	Asset metadata
alerts	Alert history
alert_rules	Alert configurations
ml_predictions	ML model predictions
ml_datasets	Uploaded ML datasets

Telemetry Table Schema

CREATE TABLE telemetry (
  id BIGINT,
  organization_id UUID,
  data_source_id UUID,
  asset_id VARCHAR(255),
  tag_name VARCHAR(255),
  value DOUBLE PRECISION,
  quality VARCHAR(50),
  timestamp TIMESTAMPTZ,
  ingested_at TIMESTAMPTZ
);

Query Limits

Tier	Row Limit	Timeout
Professional	100,000	5 minutes
Enterprise	1,000,000	10 minutes

Query Templates

Common query patterns:

Time-series data:

SELECT
  timestamp,
  value
FROM telemetry
WHERE asset_id = 'Pump_001'
  AND tag_name = 'vibration_x'
  AND timestamp >= NOW() - INTERVAL '1 hour'
ORDER BY timestamp

Downsampled data:

SELECT
  date_trunc('minute', timestamp) AS minute,
  AVG(value) AS avg_value,
  MIN(value) AS min_value,
  MAX(value) AS max_value
FROM telemetry
WHERE asset_id = 'Pump_001'
  AND tag_name = 'temperature'
  AND timestamp >= NOW() - INTERVAL '24 hours'
GROUP BY minute
ORDER BY minute

Asset comparison:

SELECT
  asset_id,
  AVG(value) AS avg_temp,
  MAX(value) AS max_temp
FROM telemetry
WHERE tag_name = 'temperature'
  AND timestamp >= NOW() - INTERVAL '7 days'
GROUP BY asset_id
ORDER BY avg_temp DESC

Join with alerts:

SELECT
  a.name AS alert_name,
  a.severity,
  t.asset_id,
  AVG(t.value) AS avg_value_during_alert
FROM alerts a
JOIN telemetry t ON t.asset_id = a.asset_id
  AND t.timestamp BETWEEN a.triggered_at AND COALESCE(a.resolved_at, NOW())
WHERE a.triggered_at >= NOW() - INTERVAL '7 days'
GROUP BY a.name, a.severity, t.asset_id

Query Management

Saving Queries

Save frequently used queries:

1. Write your query
2. Click Save Query
3. Enter name and description
4. Optionally share with team

Query History

View past queries:

1. Go to Query History tab
2. See all executed queries
3. Re-run or modify previous queries

Sharing Queries

Share saved queries with team members:

1. Open saved query
2. Click Share
3. Set permissions (view/edit)
4. Copy share link

Exporting Results

Export Formats

Format	Best For
CSV	Spreadsheet analysis
Excel	Reports with formatting
JSON	API integrations
Parquet	Large datasets, ML

Export Steps

1. Run your query
2. Click Export button
3. Select format
4. Download file

Large Exports

For exports exceeding row limits:

1. Create a scheduled export
2. Set the query and schedule
3. Receive download link via email

Query Performance

Optimization Tips

1. Use time filters first: Always filter by timestamp
2. Limit columns: Select only needed columns
3. Use aggregations: Pre-aggregate large datasets
4. Index-friendly filters: Filter on asset_id, tag_name
5. **Avoid SELECT ***: Specify exact columns

Slow Query Indicators

• Queries taking > 30 seconds
• Large result sets (> 100k rows)
• Full table scans

Using Pre-Aggregated Tables

For better performance on historical queries:

-- Instead of this (slow):
SELECT date_trunc('hour', timestamp), AVG(value)
FROM telemetry
WHERE timestamp >= NOW() - INTERVAL '30 days'
GROUP BY 1

-- Use this (fast):
SELECT hour, avg_value
FROM telemetry_hourly
WHERE hour >= NOW() - INTERVAL '30 days'

API Access

Execute Query

POST /api/v1/bi/query/execute
Authorization: Bearer YOUR_API_KEY
Content-Type: application/json

{
  "sql": "SELECT * FROM telemetry WHERE asset_id = 'Pump_001' LIMIT 100",
  "format": "json"
}

List Saved Queries

GET /api/v1/bi/query/saved
Authorization: Bearer YOUR_API_KEY

Save Query

POST /api/v1/bi/query/save
Authorization: Bearer YOUR_API_KEY
Content-Type: application/json

{
  "name": "Daily Temperature Summary",
  "sql": "SELECT date_trunc('day', timestamp), AVG(value) FROM telemetry...",
  "description": "Average daily temperature for all assets"
}

Security

Row-Level Security

All queries automatically apply organization filters:

• You can only access your organization's data
• Cross-tenant queries are blocked
• RLS is enforced at the database level

Audit Logging

All query executions are logged:

• Query text
• User who executed
• Timestamp
• Rows returned
• Execution time

Next Steps

• Scheduled Reports - Automate query reporting
• ML Models - Use queries for ML datasets
• Dashboards - Visualize query results