olap-sql

Introduction

olap-sql is a Go library that turns high-level OLAP query definitions into adapted SQL for multiple database backends (ClickHouse, MySQL, PostgreSQL, SQLite). You describe what you want — metrics, dimensions, filters — and olap-sql figures out how to query it.

How it works

Query (metrics + dimensions + filters)
        ↓
  Dictionary (schema/config)
        ↓
  Clause (backend-specific IR)
        ↓
  SQL string  ──►  Database  ──►  Result

---

Quick Start

1. Install

go get github.com/awatercolorpen/olap-sql

2. Define the schema (TOML)

Create olap-sql.toml describing your data model:

sets = [
  {name = "wikistat", type = "clickhouse", data_source = "wikistat"},
]

sources = [
  {database = "", name = "wikistat", type = "fact"},
]

metrics = [
  {data_source = "wikistat", type = "METRIC_SUM",    name = "hits",     field_name = "hits", value_type = "VALUE_INTEGER"},
  {data_source = "wikistat", type = "METRIC_COUNT",  name = "count",    field_name = "*",    value_type = "VALUE_INTEGER"},
  {data_source = "wikistat", type = "METRIC_DIVIDE", name = "hits_avg", value_type = "VALUE_FLOAT", dependency = ["wikistat.hits", "wikistat.count"]},
]

dimensions = [
  {data_source = "wikistat", type = "DIMENSION_SINGLE", name = "date", field_name = "date", value_type = "VALUE_STRING"},
]

3. Create a Manager

package main

import (
    "encoding/json"
    "fmt"
    "log"

    olapsql "github.com/awatercolorpen/olap-sql"
    "github.com/awatercolorpen/olap-sql/api/types"
)

func main() {
    cfg := &olapsql.Configuration{
        // Map each DB type to a connection option.
        ClientsOption: olapsql.ClientsOption{
            "clickhouse": {
                DSN:  "clickhouse://localhost:9000/default",
                Type: types.DBTypeClickHouse,
            },
        },
        // Point to your TOML schema file.
        DictionaryOption: &olapsql.Option{
            AdapterOption: olapsql.AdapterOption{Dsn: "olap-sql.toml"},
        },
    }

    manager, err := olapsql.NewManager(cfg)
    if err != nil {
        log.Fatal(err)
    }

    // --- Build the query ---
    queryJSON := `{
      "data_set_name": "wikistat",
      "time_interval": {"name": "date", "start": "2021-05-06", "end": "2021-05-08"},
      "metrics":    ["hits", "hits_avg"],
      "dimensions": ["date"]
    }`

    query := &types.Query{}
    if err := json.Unmarshal([]byte(queryJSON), query); err != nil {
        log.Fatal(err)
    }

    // --- (Optional) Inspect the generated SQL ---
    sql, err := manager.BuildSQL(query)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("Generated SQL:", sql)

    // --- Run the query ---
    result, err := manager.RunSync(query)
    if err != nil {
        log.Fatal(err)
    }

    out, _ := json.MarshalIndent(result, "", "  ")
    fmt.Println(string(out))
}

Generated SQL (ClickHouse):

SELECT
  wikistat.date AS date,
  SUM(wikistat.hits) AS hits,
  (1.0 * SUM(wikistat.hits)) / NULLIF(COUNT(*), 0) AS hits_avg
FROM wikistat AS wikistat
WHERE wikistat.date >= '2021-05-06'
  AND wikistat.date < '2021-05-08'
GROUP BY wikistat.date

Result JSON:

{
  "dimensions": ["date", "hits", "hits_avg"],
  "source": [
    {"date": "2021-05-06T00:00:00Z", "hits": 147,  "hits_avg": 49},
    {"date": "2021-05-07T00:00:00Z", "hits": 7178, "hits_avg": 897.25}
  ]
}

---

Common Patterns

Add filters

query := &types.Query{
    DataSetName: "wikistat",
    Metrics:     []string{"hits"},
    Filters: []*types.Filter{
        {
            OperatorType: types.FilterOperatorTypeLessEquals,
            Name:         "date",
            Value:        []any{"2021-05-06"},
        },
    },
}

Generated SQL:

SELECT SUM(wikistat.hits) AS hits
FROM wikistat AS wikistat
WHERE wikistat.date <= '2021-05-06'

Stream large result sets

For large queries, use RunChan to receive rows one at a time instead of buffering everything in memory:

result, err := manager.RunChan(query)

Inspect SQL without executing

Use BuildSQL to preview the generated query (useful for debugging):

sql, err := manager.BuildSQL(query)
fmt.Println(sql)

---

Documentation

Document	Description
Configuration	Configure Manager, clients, and the OLAP dictionary
Query	Define metrics, dimensions, filters, orders, and limits
Result	Parse and work with query results

---

Requirements

• Go 1.22+ (uses range-over-integer syntax)
• Supported databases: ClickHouse, MySQL, PostgreSQL, SQLite

---

License

See the License File.