Required Queries
Marple Insight requires three elementary queries to be defined to work.
1. Dataset List Query
The Dataset List query retrieves all available datasets from your datasource within a selected time range.
Input Parameters
Your query should support the following optional filters:
timestamp_start
– (Optional) - The start time of the selection (Unix epoch in nanoseconds) .timestamp_stop
– (Optional) - The end time of the selection (Unix epoch in nanoseconds).metadata
– (Optional) - A dictionary of key-value pairs for filtering datasets based on metadata, e.g.,{‘outing’: [‘O1’, ‘O2’], ‘car’: [‘car1’]}
.
Expected Output
Each dataset should be represented as a row containing:
display_name
(Required) – A user-friendly dataset name.timestamp_start
(Required) – The timestamp of the first datapoint in the dataset in nanoseconds.timestamp_stop
(Required) – The timestamp of the last datapoint in the dataset in nanoseconds.realtime
(Optional, default: False) – A flag indicating whether the dataset contains real-time data.Additional metadata fields (Optional) – Any relevant metadata attributes.
Postgres example:
select
id as dataset,
to_char(created_at, 'YYYY-MM-DD HH:mm:ss') as created_at,
timestamp_start,
timestamp_stop,
datapoints,
metadata
from dataset

2. Signal List Query
The Signal List query retrieves all available signals within a given dataset.
Input Parameter
Your query should support the following:
dataset
(Required) – A key-value pair for each metadata key checked in the dataset filter. Used in queries as{{ dataset.id }}
.
Expected Output
Each signal should be represented as a row containing:
name
(Required) – The name of the signal or a unique identifier.display_name
(Optional, default: name) – The display name of the signal, shown to the user.count
(Optional, at least 1 of count or frequency must be provided) – The number of records in the signal.frequency
(Optional, at least 1 of count or frequency must be provided) – The frequency of measured datapoints.unit
(Optional) – The unit of measurement for the signal.description
(Optional) – The description of the signal.Additional metadata fields (Optional) – Any relevant metadata attributes.
Postgres example:
SELECT
name,
unit,
description,
count,
metadata
FROM signal_table
WHERE dataset_id = {{ dataset.dataset }}

3. Time Series Query
The Time Series query retrieves time-series data for a single signal within a given dataset and time range.
Input Parameters
Your query should accept the following inputs:
dataset
(Required) – A key-value pair for each metadata key checked in the dataset filter. Used in queries as{{ dataset.id }}
.signal
(Required) – A key-value pair wherename
is the signal name. Used in queries as{{ signal.name }}
.timestamp_start
(Optional) – The start time of the time series in Unix epoch nanoseconds.timestamp_stop
(Optional) – The end time of the time series in Unix epoch nanoseconds.
Expected Output
Each row of the query output should contain:
timestamp
(Required) – The timestamp of the data point in Unix epoch nanoseconds.value
(Required) – The measured value of the signal.
This query is used as a "building block" for other queries. Make sure it is extendable; for example, for a Postgres connection, do not add a ";" at the end.
Postgres example:
select
time as "timestamp",
value
from raw_data
where name = '{{ signal.name }}'
{%- if timestamp_start %}
and time >= to_timestamp({{ timestamp_start }}::BIGINT)
{%- endif %}
{%- if timestamp_stop %}
and time <= to_timestamp({{ timestamp_stop }}::BIGINT)
{%- endif %}

Last updated