Skip to main content

Azure Data Explorer (Kusto) dialect for SQLAlchemy

Project description

Azure Data Explorer (Kusto) dialect for SQLAlchemy

pypi

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.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

sqlalchemy-kusto-2.0.0.tar.gz (16.3 kB view hashes)

Uploaded source

Built Distribution

sqlalchemy_kusto-2.0.0-py3-none-any.whl (16.0 kB view hashes)

Uploaded py3

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring Facebook / Instagram Facebook / Instagram PSF Sponsor Fastly Fastly CDN Google Google Object Storage and Download Analytics Huawei Huawei PSF Sponsor Microsoft Microsoft PSF Sponsor NVIDIA NVIDIA PSF Sponsor Pingdom Pingdom Monitoring Salesforce Salesforce PSF Sponsor Sentry Sentry Error logging StatusPage StatusPage Status page