duckdb-kql 0.0.14

Run Kusto Query Language (KQL) queries on DuckDB in Python. Develop KQL queries locally, run them in unit tests, as part of CI builds, have AI agents verify their work, 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.query(con, """
    Logs
    | where Timestamp > ago(1d) and Level == "Error"
    | summarize Count = count() by bin(Timestamp, 1h), Component
    | sort by Timestamp asc
""")

See the demo notebook.

Install

APIs are split into 3 layers to control features and dependencies. Select 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.

duckdb-kql serve

Then open https://dataexplorer.azure.com, choose 'Add connection', and enter http://127.0.0.1:31415.

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.query(con, """
    declare query_parameters(state:string);
    StormEvents | where State == state
""", {"state": user_input})

Scripts

Run several commands at once using scripts. 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
""")

Also available through the CLI.

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

Cross-cluster queries

Map remote databases to local ones to run queries referencing cross-cluster tables.

duckdb_kql.set_clusters({
    ('cluster1.eastus.kusto.windows.net', 'Customers'): 'Customers',
    ('cluster2.westus.kusto.windows.net', 'Sales'): 'Sales',
})

duckdb_kql.query(con, '''
    cluster('cluster1.eastus.kusto.windows.net').database('Customers').Customers
    | join kind=leftouter (cluster('cluster2.westus.kusto.windows.net').database('Sales').Orders) on CustomerId
    | project-away CustomerId1
''')

Macro expansion and entity groups

Register entity groups to run queries calling macro-expand.

duckdb_kql.set_entity_groups({
    'MyDatabases': ["database('Customers')"]
})

duckdb_kql.query(con, '''
    macro-expand MyDatabases as db ( db.Customers | where Tier == 'Free' ) | count
''')

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.