Query cold data

If your license allows this, you can directly connect to all the cold data in Marple DB via Trino and query this data using SQL.

This allows you to connect any Trino compatible tool to your data. The following guides help you to connect with some common tools. Check the Connect page in Marple DB for the values relevant to your workspace.

Grafana

• In Grafana, go to Configuration > Data Sources > Add data source

• Search for and install the "Trino" data source plugin

• Configure the data source:

  • Host https://query.db.<your-vpc-name>.marpledata.com:443

  • User mdb_<workspace-id>

  • Password <your-api-token>

  • Catalog mdb_<workspace-id>_hot

• Click "Save & Test"

Power BI

• Download and install the Trino ODBC driver

• In Power BI Desktop, click "Get Data" > "ODBC"

• Configure the connection:

  • Connection string Driver={Trino};Host=query.db.<your-vpc-name>.marpledata.com;Port=443;SSL=1

  • User mdb_<workspace-id>

  • Password <your-api-token>

• Select tables or write custom SQL queries

Metabase

• In Metabase, go to Admin > Databases > Add database

• Select "Starburst (Trino)" as the database type

• Configure the connection:

  • Host query.db.<your-vpc-name>.marpledata.com

  • Port 443

  • Catalog mdb_<workspace-id>_hot

  • Username mdb_>workspace-id>

  • Password <your-api-token>

• Enable "Use a secure connection (SSL)"

• Click "Save" to test and save the connection

Python

• Install the trino package: pip install trino

• Connect using the following code:

  from trino.dbapi import connect
  from trino.auth import BasicAuthentication
  conn = connect(
      host="query.db.<your-vpc-name>.marpledata.com",
      port=443,
      user="mdb_<workspace-id>",
      auth=BasicAuthentication("mdb_<workpace-id>", "<your-api-token>"),    
      catalog="mdb_<workspace-id>_hot",
      http_scheme="https",
  )
  cursor = conn.cursor()
  cursor.execute("SELECT * FROM mdb_nightly_hot.public.mdb_default_dataset LIMIT 10")
  rows = cursor.fetchall()
  print(rows)

DBeaver

• Download and install DBeaver

• Go to Database > New Database Connection

• Search for and select "Trino"

• Enter connection details:

  • Host query.db.<your-vpc-name>.marpledata.com

  • Port 443

  • Username mdb_<workspace-id>

  • Password <your-api-token>

• Click "Test Connection" to verify, then "Finish"

Other

Trino Clients & Documentation:

https://trino.io/docs/current/client

• JDBC URL jdbc:trino://query.db.<your-vpc-name>.marpledata.com:443

• Host query.db.<your-vpc-name>.marpledata.com

• Port 443

• Username mdb_<workspace-id>

• Hot Catalog

  mdb_<workspace-id>_hot

• Cold Catalog

  mdb_<workspace-id>_cold

• Password

  <your-api-token>

Structure

Trino allows querying the hot and cold data in Marple DB as one unified database. To access this data, the correct schema should be specified

• Hot (postgres) - All high level metadata about datasets and signals lives in these tables:

  • mdb_<datapool>_dataset information about all individual datasets in a datapool

  • mdb_<datapool>_signal information about all individual signals in a datapool

  • mdb_<datapool>_signal_enum mapping between name and id for all signals in a datapool

  -> schema: mdb_<workspace-id>_hot

• Cold (parquet data lake in Apache Iceberg format) - All raw data in a unified table per datapool with columns dataset, signal , time, value, value_signal

  -> schema: mdb_<workspace-id>_cold

Example Queries

List datasets in the hot catalog (Postgres)

--- Get dataset info from hot catalog
SELECT
   id, stream_id, path, created_at, created_by, backup_path,
   n_signals, n_datapoints, timestamp_start, timestamp_stop,  
   JSON_EXTRACT_SCALAR(metadata, '$.metadata_key') as metadata_key -- get a specific metadata key
FROM mdb_<workspace-id>_hot.public.mdb_<datapool>_dataset
LIMIT 10

Select the signal id for a given signal name from the hot catalog (Postgres)

--- Get signal id from hot catalog
SELECT id
FROM mdb_<workspace-id>_hot.public.mdb_<datapool>_signal_enum
WHERE name = {signal_name}

Get signal info from the hot catalog

--- Get signal info from hot catalog
SELECT
   name, unit, description, metadata, storage_status,
   time_min, time_max, count,
   JSON_EXTRACT_SCALAR(stats, '$.frequency') as frequency -- get a specific stat key
FROM mdb_<workspace-id>_hot.public.mdb_<datapool>_signal
WHERE dataset_id = {dataset_id}
AND signal_id = {signal_id}

Query raw data from the cold catalog (Iceberg)

-- Get signal data from Iceberg
SELECT time, value, value_text
FROM mdb_<workspace-id>_cold.default.data
WHERE dataset = {dataset_id}
AND signal = {signal_id}
LIMIT 1000