duckdb-kql

Run Kusto KQL queries on DuckDB, from Python. Pure Python, no server, no JVM.

Status: pre-alpha. The API is settling but not frozen. Coverage is real and measured — see Coverage — and anything outside it raises rather than guessing.

import duckdb_kql

con = duckdb_kql.connect()
con.sql("CREATE TABLE Logs AS SELECT * FROM 'logs.parquet'")

df = duckdb_kql.df(con, """
    Logs
    | where Timestamp > ago(1d) and Level == "Error"
    | summarize Count = count() by bin(Timestamp, 1h), Component
    | sort by Timestamp asc
""")

New here? Start with Getting started.

Install

Install only the layer you need — each adds one dependency.

pip install duckdb-kql              # translate KQL to SQL         (antlr4 only)
pip install 'duckdb-kql[duckdb]'    # ... and run it               (+ duckdb)
pip install 'duckdb-kql[kusto]'     # ... via the Kusto SDK API    (+ pandas)
pip install 'duckdb-kql[all]'       # everything

Python 3.10 or newer. Fully typed — the package ships py.typed, so your type checker sees real types across all three layers (details).

No-runtime-dependency option

Translate at build time and the output has no dependency on this package at all — not even Python. Only your CI machine installs it.

duckdb-kql translate queries/ -o build/sql/ --check   # fails the build if a .sql is stale

See Build-time translation.

Query it from the Azure Data Explorer UI

duckdb-kql serve puts a local Kusto REST endpoint in front of a DuckDB file, so Kusto's own tools can query it. Standard library only — no new dependency.

duckdb-kql serve analytics.duckdb     # http://127.0.0.1:31415

Open https://dataexplorer.azure.com, choose Add connection, and paste that URL. It listens on loopback only and cannot be told otherwise, because it answers unauthenticated queries: see A local Kusto endpoint.

Three layers

Importing duckdb_kql never imports duckdb, so Layer 0 genuinely installs and runs without a database.

Layer 0 — translate

>>> import duckdb_kql
>>> duckdb_kql.to_sql("print x = 1 + 1")
'SELECT (CAST(1 AS BIGINT) + CAST(1 AS BIGINT)) AS "x"'

>>> duckdb_kql.validate("Logs | where Level ==")
[Diagnostic(span=SourceSpan(line=1, column=21), message="mismatched input '<EOF>' ...")]

Layer 1 — execute

import duckdb_kql

con = duckdb_kql.connect("analytics.duckdb")   # duckdb.connect + TimeZone=UTC
rel = duckdb_kql.kql(con, "StormEvents | summarize n = count() by State")
rel.fetchall()

Layer 2 — the Kusto SDK interface

For code already written against azure-kusto-data: change the import and the connection string, leave the queries alone.

from duckdb_kql.kusto import KustoClient, ClientRequestProperties
from duckdb_kql.kusto.helpers import dataframe_from_result_table

client = KustoClient("analytics.duckdb")
props = ClientRequestProperties()
props.set_parameter("state", user_input)

response = client.execute("Storm", """
    declare query_parameters(state:string);
    StormEvents | where State == state | take 10
""", props)

df = dataframe_from_result_table(response.primary_results[0])

Details, including what it refuses and why: docs/kusto-client.md.

Query parameters

Never build a query by concatenating strings. Declare parameters and pass values; they are bound as values, so the generated SQL contains no caller-controlled text at all.

duckdb_kql.kql(con, """
    declare query_parameters(state:string);
    StormEvents | where State == state
""", {"state": user_input})     # safe whatever user_input contains

Coverage

Measured against the real KQL engine (the Kusto Emulator), not asserted.

Supported operators: where, project, project-away, project-rename, extend, summarize, join, mv-expand, distinct, count, sort / order by, take / limit, render; sources print, datatable, range, and tables; plus let and declare query_parameters.

The support matrix lists every operator, function and type — supported or not — with the known limitations and Kusto discrepancies for each. It is generated from the translator's own registries and probed at build time, so it cannot claim support that does not exist.

Why refusal matters

The failure mode this project is built to avoid is not a crash — it is a query that runs and returns a different answer than Kusto would. KQL and SQL look alike in places where they behave differently: % is a mathematical modulo in KQL and takes the dividend's sign in DuckDB; extract's arguments are in the opposite order; KQL weeks start on Sunday; make_datetime truncates where make_timestamp rounds. Every mapping is verified against the emulator rather than inferred from documentation, and where an honest mapping does not exist — hash_xxhash64, datetime_part('nanosecond') — the answer is an error, not an approximation.

Documentation

Design

The parser is generated by ANTLR from Microsoft's own Apache-2.0 KQL grammar. Translation targets DuckDB SQL as a chain of CTEs — one per KQL operator — so DuckDB does all execution and optimization, and the generated SQL stays readable.

Development

pip install -e ".[dev]"
pytest

tools/regen_parser.sh        # regenerate the parser (maintainers; needs Java)

The acceptance suite compares against the Kusto Emulator, which runs in Docker; see docs/oracle-harness.md. It is a development and CI tool only — never a runtime dependency, and never redistributed.

License

MIT — see LICENSE. Vendors an Apache-2.0 grammar and MIT-licensed documentation samples; see THIRD-PARTY-NOTICES.md.