Time-series with Arcadic

Setup

Arcadic.TimeSeries is a tenant-blind client for ArcadeDB's time-series surface: the TIMESERIES DDL, InfluxDB line-protocol writes, JSON query/latest reads, PromQL reads (instant, range, labels, series), continuous aggregates, and downsampling policies. This notebook walks all of it against a throwaway sensor type.

This notebook needs a running ArcadeDB >= 26.7.2 — the /api/v1/ts routes don't exist on older servers (they answer a plain 404). The quickest way is Docker:

docker run -d --name arcadic-timeseries \
  -p 2480:2480 \
  -e JAVA_OPTS="-Darcadedb.server.rootPassword=playwithdata" \
  arcadedata/arcadedb:latest

Only the HTTP port (2480) is needed — every operation below, including PromQL reads, rides the same HTTP endpoint; none of it needs Bolt (see getting_started.livemd for the Bolt transport).

Every connection value can be overridden with an environment variable set before you launch Livebook (ARCADIC_HTTP_URL, ARCADIC_PASSWORD); otherwise the defaults match the docker run above.

Re-running cells. The continuous-aggregate and downsampling cells create and drop within the same cell, so they re-run cleanly. The sensor type cell is the one exception: CREATE TIMESERIES TYPE has no IF NOT EXISTS on 26.7.2 (dropping a type that doesn't exist yet is a server error too), so that cell (under Define the type) issues an explicit, discarded drop_type immediately before create_type! — safe to re-run in isolation. Writing points is append-only by design (see the callout below) — re-running that section adds more points, it never errors, but the row counts you see will grow with every re-run. Run Cleanup at the bottom, then top to bottom, for a clean slate.

Install

Requires an unreleased arcadic build. Arcadic.TimeSeries is not in any published release yet. Concretely, as of this writing: Hex hosts only 0.4.0, so the ~> 0.6 requirement below doesn't resolve at all (Mix.install fails immediately with version solving failed, not a silent downgrade); the module also isn't yet on the main branch on GitHub, so {:arcadic, github: "baselabs/arcadic"} won't have it either until that catches up. The only way to run this notebook today is a local path dependency against a checkout that has the module, e.g. {:arcadic, path: "/path/to/your/arcadic/checkout"} in place of the pin below. Once a release carrying it is published (and pushed), the ~> 0.6 pin resolves normally and this note no longer applies.

Mix.install([
  {:arcadic, "~> 0.7"},
  {:kino, "~> 0.14"}
])

Connect

We create a throwaway arcadic_timeseries database to play in (only if it does not already exist, so this cell is safe to re-run).

http_url = System.get_env("ARCADIC_HTTP_URL", "http://localhost:2480")
password = System.get_env("ARCADIC_PASSWORD", "playwithdata")
database = "arcadic_timeseries"

admin = Arcadic.connect(http_url, database, auth: {"root", password})
{:ok, true} = Arcadic.Server.ready?(admin)

unless match?({:ok, true}, Arcadic.Server.database_exists?(admin, database)) do
  Arcadic.Server.create_database!(admin, database)
end

conn = Arcadic.connect(http_url, database, auth: {"root", password})

Define the type

Arcadic.TimeSeries.create_type!/4 emits CREATE TIMESERIES TYPE — a sensor type tagged by sensor_id/location, carrying temperature/humidity fields, millisecond precision, and a 90-day retention window.

# No IF NOT EXISTS on 26.7.2 -- the discarded drop makes this cell safe to re-run
# in isolation (dropping a not-yet-existing type errors too; `_ =` discards either way).
_ = Arcadic.TimeSeries.drop_type(conn, "sensor")

:ok =
  Arcadic.TimeSeries.create_type!(conn, "sensor", "ts",
    fields: [temperature: "DOUBLE", humidity: "DOUBLE"],
    tags: [sensor_id: "STRING", location: "STRING"],
    precision: :millisecond,
    retention: {90, :days}
  )

Writing points

Arcadic.TimeSeries.write/3 builds InfluxDB line protocol from structured point maps — typed fields, string tags, and an explicit timestamp. Note there are two precision grammars: the DDL takes :millisecond-style tokens (as in create_type! above), while the write path takes wire tokens (:ns | :us | :ms | :s) — each rejects the other's. Wire :precision declares the unit of the timestamps you put in the body, independent of the type's declared storage PRECISION: the timestamps below are epoch-milliseconds, hence precision: :ms. Omitting it means :ns — millisecond timestamps read as nanoseconds land every point in January 1970.

now_ms = System.system_time(:millisecond)

:ok =
  Arcadic.TimeSeries.write(
    conn,
    [
      %{
        type: "sensor",
        tags: %{"sensor_id" => "s1", "location" => "lab-a"},
        fields: %{"temperature" => 21.5, "humidity" => 45.0},
        timestamp: now_ms - 60_000
      },
      %{
        type: "sensor",
        tags: %{"sensor_id" => "s1", "location" => "lab-a"},
        fields: %{"temperature" => 22.0, "humidity" => 44.0},
        timestamp: now_ms
      }
    ],
    precision: :ms
  )

Arcadic.TimeSeries.write_lines/3 is the raw passthrough — useful for relaying an already-built batch (e.g. forwarding a Telegraf line) without going through the point-map builder.

:ok =
  Arcadic.TimeSeries.write_lines(
    conn,
    "sensor,sensor_id=s2,location=lab-b temperature=19.0,humidity=50.0 #{now_ms}",
    precision: :ms
  )

Operational contract (from the Arcadic.TimeSeries moduledoc, live-probed on 26.7.2 — read this before relying on retries or mixed-type batches):

• Append-only, non-idempotent. No dedup, no upsert, no server-assigned id: the identical point written twice is TWO rows. A lost response followed by a naive retry duplicates every point in the body. There is no idempotent write path — before retrying an unconfirmed write, verify whether it landed with a windowed query/3 count over the batch's time range.
• Mixed-body partial swallow. When at least one line's type exists, lines naming an unknown type are silently dropped (HTTP 204) rather than erroring. Verify with query/3 or Arcadic.Schema.types/1 when it matters.
• Unknown FIELD zero-fill. A line whose field name is not on the type inserts a zero-filled row (204, no error).
• Unknown tag KEY fails open on query/3/latest/3 — the filter is ignored server-side rather than rejected.

Querying

A raw window query returns the server's columnar shape — columns/rows/count — for every point regardless of tag.

{:ok, %{columns: columns, rows: rows, count: count}} =
  Arcadic.TimeSeries.query(conn, "sensor", from: now_ms - 3_600_000, to: now_ms + 3_600_000)

{columns, count}

:tags is a map the server ANDs together — every entry must match.

{:ok, %{count: s1_count}} =
  Arcadic.TimeSeries.query(conn, "sensor",
    from: now_ms - 3_600_000,
    to: now_ms + 3_600_000,
    tags: %{"sensor_id" => "s1", "location" => "lab-a"}
  )

s1_count

:aggregation with a REQUIRED :bucket_interval buckets the window and reduces each bucket — here, an hourly AVG of temperature for s1.

{:ok, %{aggregations: aggregations, buckets: buckets}} =
  Arcadic.TimeSeries.query(conn, "sensor",
    from: now_ms - 3_600_000,
    to: now_ms + 3_600_000,
    tags: %{"sensor_id" => "s1"},
    aggregation: [%{field: "temperature", type: :avg}],
    bucket_interval: {1, :hours}
  )

{aggregations, buckets}

Arcadic.TimeSeries.latest/3 returns the single newest point matching one tag filter.

{:ok, %{columns: latest_columns, latest: latest}} =
  Arcadic.TimeSeries.latest(conn, "sensor", tag: {"sensor_id", "s1"})

Enum.zip(latest_columns, latest) |> Map.new()

PromQL

Every TIMESERIES type doubles as a Prometheus metric: the metric name is the type name, and tags are labels — no separate registration step. :time/from/to are epoch-seconds here (PromQL convention), unlike query/3's epoch-milliseconds.

# +1: div/2 floors the second, and a Prometheus instant query excludes a sample AT OR
# AFTER the eval instant -- evaluating one second past the floor is what actually shows
# the millisecond-timestamped sample written above (live-probed on 26.7.2).
now_s = div(now_ms, 1000) + 1

{:ok, %{"resultType" => "vector", "result" => instant_result}} =
  Arcadic.TimeSeries.prom_query(conn, ~S(sensor{sensor_id="s1"}), time: now_s)

instant_result

{:ok, %{"resultType" => "matrix", "result" => range_result}} =
  Arcadic.TimeSeries.prom_query_range(
    conn,
    ~S(sensor{sensor_id="s1"}),
    now_s - 3600,
    now_s + 60,
    60
  )

range_result

{:ok, labels} = Arcadic.TimeSeries.prom_labels(conn)
{:ok, metric_names} = Arcadic.TimeSeries.prom_label_values(conn, "__name__")

# "sensor" -- the TIMESERIES type name, not a name we registered anywhere -- shows up
# among __name__'s values: the metric name IS the type name.
{labels, "sensor" in metric_names}

SQL over time-series

Arcadic.query/4 reaches sensor like any other type — the ts.timeBucket SQL function buckets the timestamp column, and the params-only rule (:name placeholders, never interpolation) applies here exactly as it does everywhere else in Arcadic.

{:ok, sql_rows} =
  Arcadic.query(
    conn,
    "SELECT ts.timeBucket('1h', ts) AS hour, sensor_id, avg(temperature) AS avg_temp " <>
      "FROM sensor WHERE sensor_id = :sensor_id GROUP BY hour, sensor_id",
    %{"sensor_id" => "s1"},
    language: "sql"
  )

Kino.DataTable.new(sql_rows)

Continuous aggregates & downsampling

Arcadic.TimeSeries.create_aggregate/3 materializes a SELECT as a document type, refreshed on demand; neither it nor drop_aggregate/2 has an IF EXISTS form, so this cell creates, refreshes, reads, and drops within itself — safe to re-run.

:ok =
  Arcadic.TimeSeries.create_aggregate(
    conn,
    "hourly_sensor",
    "SELECT ts.timeBucket('1h', ts) AS hour, sensor_id, avg(temperature) AS avg_temp " <>
      "FROM sensor GROUP BY hour, sensor_id"
  )

:ok = Arcadic.TimeSeries.refresh_aggregate(conn, "hourly_sensor")

{:ok, ca_rows} = Arcadic.query(conn, "SELECT FROM hourly_sensor", %{}, language: "sql")

:ok = Arcadic.TimeSeries.drop_aggregate(conn, "hourly_sensor")

ca_rows

A downsampling policy tells ArcadeDB to compact older points at a coarser granularity; add and drop it here too, in the same cell.

:ok =
  Arcadic.TimeSeries.add_downsampling(conn, "sensor", after: {7, :days}, granularity: {1, :hours})

:ok = Arcadic.TimeSeries.drop_downsampling(conn, "sensor")

Prometheus & Grafana integration

The PromQL family above is arcadic's app-facing wrapper for the same data a real Prometheus/Grafana deployment would scrape — the wire endpoints below are deployment configuration, not something arcadic calls on your behalf.

Point a Prometheus server's remote-write/remote-read at this database's /ts prefix to have it treat ArcadeDB as its storage backend:

remote_write:
  - url: "http://<host>:2480/api/v1/ts/<db>/prom/write"
    basic_auth:
      username: root
      password: <password>
remote_read:
  - url: "http://<host>:2480/api/v1/ts/<db>/prom/read"
    basic_auth:
      username: root
      password: <password>

Point Grafana's built-in Prometheus datasource at the same prefix (its query path) to graph sensor alongside any other Prometheus-compatible source:

apiVersion: 1
datasources:
  - name: ArcadeDB Sensor
    type: prometheus
    url: http://<host>:2480/api/v1/ts/<db>/prom
    basicAuth: true
    basicAuthUser: root
    secureJsonData:
      basicAuthPassword: <password>

ArcadeDB also exposes Grafana Infinity/native endpoints server-side (/grafana/health|metadata|query) for panels that talk to it directly rather than through the Prometheus datasource. Arcadic wraps the app-facing PromQL and query/write surfaces above; wiring either machine-to-machine path into a running Prometheus or Grafana instance is deployment configuration outside the client's scope.

Cleanup

Drop the throwaway database when you are done (or before a fresh top-to-bottom run). This tears down sensor, its data, and any continuous aggregate or downsampling policy left over from a partial run.

Arcadic.Server.drop_database(admin, database)