dodopizza/sqlalchemy-kusto

Kusto dialect for SQLAlchemy

Azure Data Explorer (Kusto) dialect for SQLAlchemy

sqlalchemy-kusto implements a DBAPI (PEP-249) and SQLAlchemy dialect that enables SQL query execution via SQLAlchemy.

Current project includes support for two dialects: SQL dialect and KQL dialect.

SQL dialect

Current implementation has full support for SQL queries. But pay your attention that Kusto implementation of T-SQL has not full coverage; check the list of known issues.

KQL dialect

KQL dialect still in progress. Please, use it on your own risk for now.

Notice that implemented Kusto dialects don't support DDL statements and inserts, deletes, updates.

Installation

pip install sqlalchemy-kusto

Library usage

Using DBAPI

from sqlalchemy_kusto import connect

connection = connect(
        cluster=kusto_url,
        database=database_name,
        msi=False,
        user_msi=None,
        azure_ad_client_id=kusto_client_id,
        azure_ad_client_secret=kusto_client_secret,
        azure_ad_tenant_id=kusto_tenant_id,
)

result = connection.execute(f"select 1").fetchall()

Using SQLAlchemy raw sql

from sqlalchemy.engine import create_engine

engine = create_engine(
    f"kustosql+{kusto_url}/{database_name}?"
    f"msi=False&azure_ad_client_id={kusto_client_id}&"
    f"azure_ad_client_secret={kusto_client_secret}&"
    f"azure_ad_tenant_id={kusto_tenant_id}"
)
engine.connect()
cursor = engine.execute(f"select top 1")
data_rows = cursor.fetchall()

Using SQLAlchemy

from sqlalchemy import create_engine, MetaData, Table, Column, String, Integer

engine = create_engine(
    f"kustosql+{kusto_url}/{database_name}?"
    f"msi=False&azure_ad_client_id={kusto_client_id}&"
    f"azure_ad_client_secret={kusto_client_secret}&"
    f"azure_ad_tenant_id={kusto_tenant_id}"
)

my_table = Table(
        "MyTable",
        MetaData(),
        Column("Id", Integer),
        Column("Text", String),
)

query = my_table.select().limit(5)

engine.connect()
cursor = engine.execute(query)
print([row for row in cursor])

Using with Apache Superset

Apache Superset starting from version 1.5 also supports Kusto database engine spec.
When connecting to a new data source you may choose a data source type either KustoSQL or KustoKQL depending on the dialect you want to use.

There are following connection string formats:

# KustoSQL
kustosql+https://<CLUSTER_URL>/<DATABASE>?azure_ad_client_id=<CLIENT_ID>&azure_ad_client_secret=<CLIENT_SECRET>&azure_ad_tenant_id=<TENANT_ID>&msi=False

# KustoKQL
kustokql+https://<CLUSTER_URL>/<DATABASE>?azure_ad_client_id=<CLIENT_ID>&azure_ad_client_secret=<CLIENT_SECRET>&azure_ad_tenant_id=<TENANT_ID>&msi=False

Important notice on package version compatibility.
Apache Superset stable releases 1.5 and 2.0 dependent on sqlalchemy==1.3.24. If you want to use sqlalchemy-kusto with these versions you need to install version 1.* of the package.

Current master branch of the apache/superset dependent on sqlalchemy==1.4.36. If you want to use sqlalchemy-kusto with the latest unstable version of apache/superset, you need to install version 2.* of the package.

Contributing

Please see the CONTRIBUTING.md for development setup and contributing process guidelines.

---

Issue in Apache Superset repository that inspired current solution.

sqlalchemy-kusto's Issues

KQL dialect. Rewrite using visitor pattern

For example method _extract_let_statements was created for one purpose to deal with queries like that:

select * from (
    let inner_query = logs | take 100
) limit 100

Does sqlalchemy-kusto support using access token returned from Microsoft AAD service principal?

Is your feature request related to a problem? Please describe.
We previous use the below parameter to connect Kusto.

from sqlalchemy_kusto import connect

# Define your Kusto connection details
kusto_url = "https://<kusto_cluster>.kusto.windows.net"
database_name = "<your_database_name>"
kusto_client_id = "<your_client_id>"
kusto_client_secret = "<your_client_secret>"
kusto_tenant_id = "<your_tenant_id>"

# Connect to Kusto using AAD authentication
connection = connect(
    cluster=kusto_url,
    database=database_name,
    msi=False,
    azure_ad_client_id=kusto_client_id,
    azure_ad_client_secret=kusto_client_secret,
    azure_ad_tenant_id=kusto_tenant_id,
)

# Execute a sample query
result = connection.execute("select 1").fetchall()
print(result)

However, secret is not supported due to security concern. Is it supported to use the access_token returned from service principal as the authentication in kusto?

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

Kusto broken by cryptography incompatibility

This issue is a duplicate of apache/superset#24636 on the superset repository.

We are using Superset 2.0.1 and installed the sqlalchemy-kusto=1.1.0 version to avoid the issue with 2.0.1 supporting sqlalchemy==1.3.24 while sqlalchemy-kusto=2.0.0 supports sqlalchemy==1.4.36 . We are avoiding upgrading to 2.1 because of issue apache/superset#23848

Unfortunately, when trying to connect to Kusto we are getting the error below:
ERROR: (cryptography 36.0.2 (/usr/local/lib/python3.8/site-packages), Requirement.parse('cryptography<4,>=0.6'), {'msal'})

Add schema support in KQL dialect

Currently, KQL-dialect doesn't support schemas. But schemas will be useful for cross-database joins, for example.

Here is more detailed description:

SQL syntax allows schemas, e.g.

SELECT * FROM mydb.tbl LIMIT 100

KQL dialect should convert it to more complicated construction:

database("mydb").tbl | limit 100

Memory leak with unused cursor buildup

Running the queries on a loop leads to an obvious memory buildup here with: https://github.com/dodopizza/sqlalchemy-kusto/blob/main/sqlalchemy_kusto/dbapi.py#L109

Relax dependency version constraints

Hello,

Thank you for writing this package. However, I installed it and noticed that your setup.py pins very specific, older versions of azure-kusto-data (of which the latest version is 3.1.1) and sqlalchemy (latest 1.4.40). My pip is yelling at me that I have incompatible versions of these packages and I don't know whether these are real incompatibilities or not. Could you please relax the version constraints in your setup.py to provide a range of compatible versions of these dependencies?

REQUIREMENTS = [
    "azure-kusto-data==2.1.1",
    "sqlalchemy==1.3.24",
]

Are credentials required?

On the development side, I was looking for test options and saw a few things:

• https://dataexplorer.azure.com/freecluster
• https://hub.docker.com/_/microsoft-kusto

but these don't seem to provide the "service principal" or anything and that seems like it's required. What's the correct way of connecting if I only have a cluster ID and database name?

Support for azure.core.credentials.TokenCredential

Support for azure.core.credentials.TokenCredential via KustoConnectionStringBuilder.with_aad_user_token_authentication(cluster, user_token).

Propose adding a "user_token" parameter to sqlalchemy_kusto.dbapi.Connection to pass in token generated by for example, DefaultAzureCredential:

79: elif user_token is not None:
80: kcsb = KustoConnectionStringBuilder.with_aad_user_token_authentication(cluster, user_token)