duckdb-kql 0.0.12

Run Kusto Query Language (KQL) queries on DuckDB in Python. Develop KQL queries locally, run them in unit tests, as part of CI builds, etc. without the need for network.

import duckdb_kql

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

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

Several statements at once — the shape Azure Data Explorer calls a database script, for setting a database up in KQL alone. Blank lines separate the statements:

duckdb_kql.script(con, """
    .set-or-replace Errors <| Logs | where Level == "Error"

    .set-or-replace ByComponent <| Errors | summarize n = count() by Component
""")

Keep that in a .kql file and the CLI runs it, so the file that sets a database up lives in the repository:

duckdb-kql script schema.kql -d logs.duckdb

See the demo notebook.

Install

APIs are split into 3 layers to control features and dependencies. Install based on needs.

See Getting started.

To reduce runtime dependencies, translate KQL queries to SQL at build time using the CLI.

duckdb-kql translate -o query.sql query.kql

See Build-time translation.

KustoClient query

Run queries using APIs compatible with Kusto client SDK (azure-kusto-data).

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

client = KustoClient(con)

response = client.execute("NetDefaultDB", """
    Requests
    | where Status >= 500
    | summarize Errors = count() by Service
    | sort by Errors desc
""")

dataframe_from_result_table(table)

See Kusto client.

Local HTTP server

Start a local KQL HTTP server using the CLI, then open https://dataexplorer.azure.com, choose 'Add connection', and enter http://127.0.0.1:31415.

duckdb-kql serve

See Kusto server.

Query validation and translation

Use validate() and to_sql() to respectively validate the KQL query and translate it to SQL.

>>> 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>' ...")]

Query parameters

Declare parameters and pass values to defend against query injections.

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

Coverage

Correctness measured against the real KQL engine (the Kusto Emulator).

Supported operators: where, project, project-away, project-rename, extend, summarize, join, mv-expand, distinct, count, sort / order by, top, take / limit, union, macro-expand, lookup, parse, parse-where, 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.

Documentation

Design

The KQL parser is generated by ANTLR from Microsoft's KQL grammar. Translation targets DuckDB SQL as a chain of Common Table Expressions (CTEs), one per KQL operator. DuckDB handles query optimization and execution.

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.

License

MIT. See LICENSE, THIRD-PARTY-NOTICES.md, and licenses/.

Trademarks

"DuckDB" is a trademark of the DuckDB Foundation. "Kusto", "Azure", and "Azure Data Explorer" are trademarks of Microsoft Corporation. This project is independent and is not affiliated with, endorsed by, or sponsored by either the DuckDB Foundation or Microsoft. Product names are used descriptively, to indicate compatibility.