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 where name 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 %}