Warehouse connectors

dosi-engine executes by SQL pushdown: it compiles a metric query to dialect SQL and the warehouse runs it. A connector (adapter) handles connection setup, statement submission, and normalizing results into a ResultSet (columns + rows of Null | Bool | Int | Float | Str, dates as ISO-8601 strings) so results compare equal across engines.

This page is the setup guide for each connector. Arrow-native result transfer (DuckDB, ClickHouse, StarRocks/Doris, Databricks) is described in arrow.md; the internal maturity ladder, status matrix, and changelog live in design/connector-maturity.md.

Connection profiles

Connection profiles use the Datus agent.yml datasources: vocabulary — dosi-exec keeps its own minimal QueryExecutor interface (deliberately not aligned with datus-db-adapters), but the configuration is shared so a Datus setup needs no translation. --connections <path> (env DOSI_CONNECTIONS) accepts either a full agent.yml (profiles under services.datasources) or a standalone file with the same map under a top-level datasources:. Without the flag, the file is discovered at ./dosi-connections.yaml, ~/.config/dosi/connections.yaml, ./conf/agent.yml, then ~/.datus/conf/agent.yml — an existing Datus install works with zero config. The pre-rename ./osi-connections.yaml and ~/.config/osi/connections.yaml are still discovered as fallbacks, each just after its dosi- counterpart.

datasources:
  local-duck:
    type: duckdb
    uri: duckdb:///warehouse.duckdb   # duckdb:////abs vs duckdb:///rel; omit for in-memory
  prod-sr:
    type: starrocks
    host: sr.internal
    port: 9030
    username: osi
    password: ${SR_PASSWORD}          # ${VAR} / ${VAR:-fallback} from the environment
    database: analytics
    default: true                     # used by --execute without --connection
  ch:
    type: clickhouse
    uri: http://localhost:8123        # or host/port
    username: default
    database: default
  trino:
    type: trino
    host: trino.internal
    port: 8080
    catalog: hive                     # session catalog for the compiled schema.table names
    schema: sales
  snowflake:
    type: snowflake
    account: ab12345.us-east-2.aws    # full identifier; JWT uses the leading locator
    username: DOSI_TEST
    warehouse: COMPUTE_WH
    role: SYSADMIN                    # optional
    database: ANALYTICS               # session default for two-part names
    private_key_file: ~/.config/dosi/snowflake_key.p8   # PKCS#8 PEM
    private_key_file_pwd: ${DOSI_SF_KEY_PASS}            # omit if the key is unencrypted

Semantics worth knowing:

• A secret written exactly as ${VAR} resolves lazily — the file loads with the variable unset, and the error (with an export hint) fires only when that connection is used. A plaintext password: works but warns on stderr.
• A broken entry (unset ${VAR} in a datasource you aren't using, an unsupported type: sqlite, …) never takes the file down; its error surfaces when the entry is named. Unknown entry keys are ignored (Datus adapters keep private settings there); the pre-1.0 osi spellings (dialect, user, password_env, path, url, a top-level connections: list) are rejected with a migration hint.
• At most one entry may set default: true.

Run: dosi query --metrics revenue --group-by orders.status --execute --connection prod-sr. --execute without --connection uses the default: true profile if one is marked, else local DuckDB (--db <file> or in-memory).

Connectors

DuckDB

In-process via the bundled duckdb crate (the default exec-duckdb feature): one lazily-opened connection per executor, results decoded once as Arrow RecordBatches (query_arrow) with rows derived through the shared normalizer. init_sql runs once at connection open; execute_batch works on in-memory databases (state persists on the connection). No external binary needed. Profile fields: uri (duckdb:///...; omit = in-memory).

--no-default-features swaps in the historical CLI shell-out body (system duckdb binary, JSON mode, fresh process per query — in-memory execute_batch is a config error there). Kept one release for lean builds.

datasources:
  local_duckdb:
    type: duckdb
    uri: duckdb:////absolute/path/warehouse.duckdb   # duckdb:///rel/path = relative; omit = in-memory

MySQL / TiDB / StarRocks / Doris

One adapter (dosi-exec/src/mysql.rs), MySQL wire protocol, text protocol on purpose (StarRocks/Doris prepared-statement support is patchy). All four pass the full corpus against live servers (MySQL 8.4, TiDB v8.5, StarRocks 3.3, Doris 2.1).

MySQL and TiDB have no FULL OUTER JOIN, but that is no longer a query limitation: a multi-branch merge (multiple metrics at different grains) is lowered there to the portable UNION-keys CTE + LEFT JOIN shape, the same one the Postgres family uses. StarRocks and Doris keep the FULL OUTER form.

All four share the same profile shape — only type differs. A driver URL also works: uri: mysql://user:pass@host:port/db.

Arrow Flight SQL (exec-flightsql, StarRocks/Doris only): add arrow_flight_port: to the profile and queries switch to the Flight executor — the FE plans, the client pulls Arrow batches directly from the BEs (columnar end to end, no row serialization through the FE). The key's value is the FE's flight port (StarRocks fe.conf arrow_flight_port, StarRocks ≥3.5.1; Doris fe.conf arrow_flight_sql_port, Doris ≥2.1.5 — one osi key for both). Remove the key to fall back to MySQL wire.

  prod-sr:
    type: starrocks
    host: sr.internal
    port: 9030                # MySQL wire — still used as seeding/DDL fallback
    arrow_flight_port: 9408   # presence opts queries into Arrow Flight SQL
    username: osi
    password: ${SR_PASSWORD}
    database: analytics

Findings from first live contact (StarRocks 4.0.5, 2026-07-13 — all handled in the adapter or documented):

• No DoPut: acceptPutStatement is unimplemented — the adapter runs every statement (DDL/DML/USE) through the query path (GetFlightInfo) instead of execute_update.
• Database-level DDL fails over Flight on SR 4.0 (CREATE/DROP DATABASE → generic RPC error); table-level DDL and DML reach the analyzer. Seed and manage schemas over MySQL wire (the corpus flight run does exactly that).
• BE reachability: the FE hands clients each BE's own address for direct reads. Client and BE must agree on that address — in Docker publish the BE flight port 1:1 (9419:9419), or use the engines' FE-proxy fallbacks (StarRocks arrow_flight_proxy* session vars; Doris public_host + arrow_flight_sql_proxy_port).
• The FE flight service can reset the very first connection after startup — the adapter retries the connect once.

datasources:
  mysql_prod:
    type: mysql              # or tidb
    host: ${MYSQL_HOST}
    port: 3306               # tidb default: 4000
    username: osi
    password: ${MYSQL_PASSWORD}
    database: analytics
  starrocks:
    type: starrocks          # or doris
    host: ${STARROCKS_HOST}
    port: 9030               # FE MySQL-protocol port (doris: 9030 too)
    username: ${STARROCKS_USER}
    password: ${STARROCKS_PASSWORD}
    database: ${STARROCKS_DATABASE}

Postgres

postgres crate (sync facade). Decode strategy: prepare for authoritative type OIDs, simple_query for text values — NUMERIC (SUM/AVG output) normalizes without a decimal dependency. Sessions are pinned to UTC (SET TIME ZONE 'UTC') so TIMESTAMPTZ text trims to zone-less ISO regardless of the server default. 32/32 corpus vs Postgres 16 — re-verified against a server configured with timezone=America/Los_Angeles (PST).

datasources:
  warehouse_pg:
    type: postgres
    host: pg.internal                # or uri: postgres://osi:pass@pg.internal/analytics
    port: 5432
    username: osi
    password: ${PG_PASSWORD}
    database: analytics

Hologres

Alibaba Cloud Hologres speaks the Postgres wire protocol, so it rides the Postgres driver and inherits its decode strategy verbatim (prepare for type OIDs, simple_query for text; sessions pinned to UTC). Compiled SQL is generated as PostgreSQL. Two things are Hologres-specific:

• Batch execution sends one statement at a time. A multi-statement script is a single implicit transaction, and Hologres rejects a transaction that mixes DDL with DML — so execute_batch splits the script (respecting string literals, quoted identifiers, dollar-quoted bodies, and comments) and sends each statement separately on the same connection. This is the same restriction the datus-hologres adapter raises from execute_queries.
• The console endpoint may carry its own port. host: accepts <instance>.hologres.aliyuncs.com or <instance>.hologres.aliyuncs.com:80; a port in both places must agree. A bare hostname defaults to port 80 (what Hologres publishes), not 5432.

Credentials are an Alibaba Cloud AccessKey pair. username/password and access_key_id/access_key_secret are aliases — use one spelling, not both; the AccessKey spellings are rejected on any other type: so they can never silently drop a credential.

datasources:
  hologres:
    type: hologres
    host: ${HOLOGRES_HOST}                    # or host:port; bare hostname defaults to :80
    port: ${HOLOGRES_PORT:-80}
    username: ${HOLOGRES_ACCESS_KEY_ID}       # alias: access_key_id
    password: ${HOLOGRES_ACCESS_KEY_SECRET}   # alias: access_key_secret
    database: ${HOLOGRES_DATABASE}
    schema: public                            # default; pins search_path
    sslmode: disable                          # see the TLS note below

TLS: sslmode accepts the full libpq vocabulary, with libpq's exact verification ladder: require encrypts without verifying (so self-signed server certificates work) — unless sslrootcert: is also set, in which case the chain is verified like verify-ca (libpq's documented back-compat behavior); verify-ca checks the chain against the CA bundle named by sslrootcert: (system roots excluded — trust exactly that CA), and verify-full adds hostname verification. verify-* without sslrootcert is rejected at construction with an actionable error. Hologres public endpoints on port 80 are plaintext (sslmode: disable), which is the configuration the corpus is verified against.

Build with --features exec-hologres (implied by exec-all). The corpus run is gated on HOLOGRES_HOST + HOLOGRES_ACCESS_KEY_ID / HOLOGRES_ACCESS_KEY_SECRET / HOLOGRES_DATABASE (the same variables the Python adapter documents), or on a plain DOSI_TEST_HOLOGRES_URL.

95/95 corpus vs live Hologres (2026-08-01), no declared skips — the first non-DuckDB engine with a clean sweep.

Two dialect details are handled for you:

• DATEDIFF is rewritten, not rejected. Hologres has DATEDIFF, but with the unit last (DATEDIFF(end, start, 'day'), computing d1 - d2), where models are written in the unit-first form. The compiler rotates the argument list, which fixes the unit position and the sign together. A call already written in Hologres' own spelling is left alone. Hologres' unit vocabulary is a subset — no week, no quarter.
• Sessions are pinned to UTC, which matters more here than on Postgres. Hologres does not accept DATE for DATE_TRUNC (its signature takes TIME|TIMESTAMP|TIMESTAMPTZ), so a date widens to TIMESTAMPTZ and the result carries a zone — and instances default to PRC (+08). The pin keeps time-grain buckets zone-less and comparable across engines.

One difference dosi does not normalize: Hologres uses C collation, so ORDER BY on text sorts by byte. That matches DuckDB but differs from a stock en_US.UTF-8 Postgres.

GaussDB

Huawei GaussDB / openGauss is PostgreSQL-derived (9.2 lineage): compiled SQL is generated as PostgreSQL, and the decode strategy is the Postgres executor's verbatim (prepare for type OIDs, simple_query for text; sessions pinned to UTC). What sets it apart is the wire protocol: GaussDB replaces PostgreSQL's SASL authentication with a private SHA256 handshake whose auth-code numbers collide with PG's SASL — stock Postgres drivers (libpq, tokio-postgres) fail before the password is even checked. The executor therefore uses the gaussdb driver (a rust-postgres fork carrying Datus' RFC 5802 fixes; see design/gaussdb-sha256-auth.md) and speaks that handshake natively, so servers with pg_hba method sha256 — the GaussDB production default — work without any server-side configuration change.

Authentication support, by the server's hba method × the account's password storage format (password_encryption_type at the time the password was set):

password storage hba	md5	sha256
MD5 (type=0)	✅	✅ (server falls back to MD5)
SHA256 (type=2, default)	❌ rejected with hint¹	✅ native SHA256
MD5+SHA256 (type=1)	✅	✅ native SHA256

¹ In this hybrid the server omits the PBKDF2 iteration count from the handshake, so no client can derive the key. The error hint says so: re-set the account password under password_encryption_type = 1, or switch the hba method to sha256. SM3-stored passwords (type=3) are likewise rejected with an actionable error.

datasources:
  gaussdb:
    type: gaussdb
    host: gauss.internal             # or uri: postgres://osi:pass@gauss.internal:8000/analytics
    port: 8000                       # GaussDB(DWS) default; openGauss uses 5432
    username: osi
    password: ${GAUSSDB_PASSWORD}
    database: analytics
    schema: main                     # optional; pins search_path
    sslmode: require                 # managed instances enable ssl=on
    # sslrootcert: /etc/ssl/gauss-ca.pem   # only for verify-ca / verify-full

Compatibility modes: GaussDB databases are created in PG, A (Oracle — the GaussDB default) or B (MySQL) compatibility mode; newer GaussDB kernels add M (full-MySQL). PG, A and B are supported: the engine's emitted SQL surface is verified against each mode by the full corpus (149/150 on PG, A and B alike — the one skip is the same 3-arg DATEDIFF case Postgres itself declares), and the per-construct semantic differences are recorded and continuously asserted in design/gaussdb-compat-contract.yaml.

Declare the mode you expect in the profile and the executor will verify it on first connect, refusing to run on a mismatch (a wrong assumption changes semantics silently, so this fails hard):

    compat_mode: PG                  # optional: PG | A | B

M-compatibility databases are refused at first connect with an actionable error: M is not a semantic variant but a different SQL dialect entirely — MySQL syntax where even CAST(x AS varchar) is a syntax error (verified live on GaussDB Kernel 505), so PostgreSQL-dialect SQL cannot run there.

Two mode-specific data semantics are declared rather than papered over, because they live in the stored data, not in queries: in A mode the empty string is NULL (writing '' stores NULL; col = '' never matches — use col = '' OR col IS NULL for a portable emptiness test), and in B mode string equality ignores trailing spaces (MySQL PAD SPACE). Two further divergences are fleet-level rather than GaussDB-specific and are documented in the contract file: integer division yields reals in every GaussDB mode (the MySQL/ClickHouse side of an existing engine split), and B sorts NULLs first ascending (exactly like MySQL/TiDB).

TLS: same libpq sslmode/sslrootcert treatment as the Postgres connector, on the fork's native-tls implementation. Managed Huawei-cloud instances typically enable ssl=on with a self-signed certificate — sslmode: require is the right setting there (verified live against a managed GaussDB Kernel 505.2.1).

Build with --features exec-gaussdb (implied by exec-all). The corpus run is gated on DOSI_TEST_GAUSSDB_URL (a postgres:// endpoint); tests/docker/ ships an openGauss service preconfigured with a PG-compatibility database and an sha256-hba login.

149/150 corpus vs live openGauss 7.0.0-RC2 (2026-08-13), over the native SHA256 handshake — the one declared skip is the same 3-arg DATEDIFF case Postgres itself declares (rejected with the identical SQLSTATE 42883).

Oracle

Oracle Database has a first-class generator: compiled SQL uses FETCH FIRST for limits, TRUNC(x, 'FMT') for time-grain truncation, quoted interval literals (INTERVAL '1' MONTH), and table aliases without AS. Oracle 23ai+ even ships a native 3-arg DATEDIFF, so the case every other engine declares as a skip runs there (19c/21c would need the skip — declare it when a live run of those vintages exists).

The executor uses the oracle crate over ODPI-C. There is no build-time Oracle dependency — ODPI-C compiles with the crate and dlopens the Oracle Instant Client (free, ~80MB) at first connect. Only machines that actually reach Oracle need it installed; everyone else is unaffected. If it is missing, connecting fails with an actionable DPI-1047 hint. Install: Linux dnf install oracle-instantclient-basic (or unzip + LD_LIBRARY_PATH), macOS: mount the DMG and symlink libclntsh.dylib into ~/lib — the architecture must match the binary (an x86_64 build under Rosetta needs the Intel client).

Sessions are pinned at connect: TIME_ZONE = 'UTC' (the zone-less Value contract) and ISO NLS_DATE_FORMAT/NLS_TIMESTAMP_FORMAT — the compiled CAST('2024-01-01' AS DATE) literals parse via NLS, whose DD-MON-RR default would reject them.

datasources:
  oracle:
    type: oracle
    host: db.internal                 # or uri: oracle://osi:pass@db.internal:1521/ORCLPDB1
    port: 1521
    username: osi
    password: ${ORACLE_PASSWORD}
    database: ORCLPDB1                # the service name

Oracle schema == user: compiled SQL references main.<table>, so the login user (or a schema it can see) is main — the corpus container sets APP_USER: main. One semantic difference dosi does not normalize: Oracle treats '' as NULL.

Build with --features exec-oracle (implied by exec-all). The corpus run is gated on DOSI_TEST_ORACLE_URL (oracle://user:pass@host:port/service); tests/docker/ ships a gvenzl/oracle-free (23ai) service.

150/150 corpus vs Oracle 23ai Free (2026-08-13), zero declared skips — joining DuckDB, Snowflake, and Hologres as the zero-skip engines, courtesy of 23ai's native DATEDIFF.

ClickHouse

HTTP interface with JSONCompact output and output_format_json_quote_64bit_integers=0. One statement per request → seeding splits batches client-side. Profile: uri (or host/port), username, password, database. 45/45 corpus vs ClickHouse 24.8 (2026-07-13, incl. per-case Arrow parity).

Arrow path (exec-http-arrow): the same POST with default_format=ArrowStream — batches decode as a true stream (lz4-framed; output_format_arrow_string_as_string=1 keeps strings Utf8). Gotcha handled in the adapter: ClickHouse's Arrow writer exports Date as bare UInt16 (epoch days) and DateTime as bare UInt32 (epoch seconds), and v24.8 has no output setting to preserve them — the adapter runs DESCRIBE (query) first (type inference only, no execution) and re-types those columns to Date32/Timestamp on the fly (a pure reinterpretation; DateTime64 and Date32 already export correctly).

datasources:
  ch:
    type: clickhouse
    uri: http://ch.internal:8123     # or host/port (port defaults to 8123)
    username: default
    password: ${CLICKHOUSE_PASSWORD}
    database: analytics

Trino

REST /v1/statement with nextUri polling (50→200 ms backoff). HTTP 503 is retried per the protocol ("busy, ask again"), bounded at ~10 s before surfacing a timeout error. Tests seed through the built-in memory catalog. Profile: uri (or host/port), username (X-Trino-User), catalog/schema (or database as catalog[.schema]) session defaults. 32/32 corpus vs Trino 467.

datasources:
  trino:
    type: trino
    host: trino.internal
    port: 8080                       # or uri: http://trino.internal:8080
    username: osi                    # sent as X-Trino-User
    catalog: hive                    # session catalog for compiled schema.table names
    schema: sales                    # optional session schema

Snowflake

dosi-exec/src/snowflake.rs on the exec-snowflake feature (ureq + rustls TLS, rsa/sha2/pkcs8 for auth — all gated, no SDK). Talks the SQL API v2 (/api/v2/statements) with key-pair JWT auth: the JWT iss/sub use the account locator (uppercased, region stripped) and a SHA-256 fingerprint of the public key; the PKCS#8 private key may be PBES2-encrypted (passphrase via private_key_file_pwd: ${VAR}). Profile: account, username, warehouse, optional role/schema/database, private_key_file.

datasources:
  snowflake:
    type: snowflake
    account: ${SNOWFLAKE_ACCOUNT}    # full identifier, e.g. ab12345.us-east-2.aws
    username: ${SNOWFLAKE_USER}
    warehouse: COMPUTE_WH
    role: SYSADMIN                   # optional
    database: ANALYTICS              # session default for two-part names
    schema: PUBLIC                   # optional
    private_key_file: ~/.config/dosi/snowflake_key.p8   # PKCS#8 PEM
    private_key_file_pwd: ${SNOWFLAKE_KEY_PASSPHRASE}  # omit if unencrypted

Two gotchas the corpus surfaced, both handled in the adapter:

• Stateless API — each request is independent, so USE never persists. Warehouse/database/schema context ships in every request body; seed tables are fully qualified (DB.main.<table>) so the bootstrap CREATE DATABASE isn't blocked by a not-yet-existent context DB, while queries carry the database context so a two-part main.<table> resolves.
• DATE encoding — the API returns DATE as a count of days since the epoch (e.g. 19723 = 2024-01-01), not an ISO string; the decoder converts it. Unquoted identifiers come back upper-cased, so the corpus compares column names case-insensitively.

45/45 corpus live against a live account (2026-07-12). CI is credential-gated in L2 weekly (repo secrets DOSI_TEST_SNOWFLAKE_*), skipped where unset.

Databricks

dosi-exec/src/databricks.rs on the exec-databricks feature (ureq + rustls TLS; no SDK). Talks the Statement Execution API (/api/2.0/sql/statements) against a SQL warehouse with Personal Access Token (Bearer) auth. Results are requested disposition: INLINE, format: JSON_ARRAY; a statement that doesn't finish inside wait_timeout is polled to a terminal state, and results spanning multiple inline chunks are stitched via result/chunks/{n}. Profile: host, warehouse (the SQL warehouse id), password (the PAT), catalog, optional schema.

datasources:
  databricks:
    type: databricks
    host: dbc-xxxx.cloud.databricks.com   # a pasted https://.../ URL is tolerated
    warehouse: e9678899fc3ffa09           # SQL warehouse id (from Connection details)
    password: ${DATABRICKS_TOKEN}         # a dapi... PAT
    catalog: workspace                    # Unity Catalog catalog (session context)
    schema: analytics                     # optional default schema for queries

Two things the corpus surfaced, both handled:

• Catalog as context, not USE — the API is stateless per request, so the catalog ships in every request body. Seeding runs with the catalog context only (fully-qualified main.<table> names) so the bootstrap CREATE SCHEMA main isn't gated on a not-yet-existent default schema; queries carry catalog (+ optional schema) so two-part main.<table> resolves. The corpus therefore needs a writable catalog — the read-only samples catalog can't be seeded.
• Encoding — every cell is a JSON string keyed by manifest.schema.columns[].type_name; unlike Snowflake, DATE already comes back ISO (2024-01-01), so no epoch-days shim. TIMESTAMP does arrive ISO-8601 (2020-01-01T00:00:00.000Z), canonicalized to the reference's 2020-01-01 00:00:00. Unquoted identifiers return lower-cased, so the corpus compares column names case-insensitively.

Arrow-native path (exec-databricks-arrow): the same executor also implements execute_arrow with format=ARROW_STREAM + disposition=EXTERNAL_LINKS — each result chunk is a self-contained Arrow-IPC stream on a presigned cloud-storage URL, fetched without the workspace token (the URL is already signed) and decoded once with arrow-ipc; the row execute() derives from those batches via the shared normalizer. No re-typing (unlike ClickHouse): Databricks exports real Date32/Timestamp Arrow types. A zero-row result carries no links, so the schema is synthesized from the manifest (found by the parity gate). This is the recommended path for Databricks over the Foundry ADBC driver: the wire is already columnar Arrow, so the driver's C-Data-Interface zero-copy handoff saves nothing a direct arrow-ipc decode doesn't — and it avoids a heavy Go .so (see design/arrow-adbc.md §4).

45/45 corpus live against a free-tier Serverless SQL warehouse (2026-07-13), row and Arrow paths both green (Arrow parity vs the row path and the DuckDB reference). CI is credential-gated in L2 weekly (repo secrets DOSI_TEST_DATABRICKS_*), skipped where unset.