Denodo Dialect for SQLAlchemy - User Manual
You can translate the document:
Introduction
SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.
It provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.
The dialect is the system SQLAlchemy uses to communicate with various types of DBAPI implementations and databases.
With the Denodo Dialect for SQLAlchemy we can connect with Denodo databases and query data from Denodo views.
The Denodo Dialect for SQLAlchemy can use two different drivers:
- psycopg2 (which will connect to port 9996 of VDP)
- flightsql (which will connect to port 9994 of VDP 9.1+)
SQLAlchemy compatibility
The Denodo Dialect for SQLAlchemy requires SQLAlchemy 1.4.
However, in order to use Flight SQL connectivity (available since Denodo 9.1), SQLAlchemy 2.0 is required.
Installation and Execution
This dialect requires the following Python libraries:
- sqlalchemy>=1.4.36
- psycopg2>=2.9.6
In order to use the Flight SQL interface, these dependencies apply:
- sqlalchemy>=2.0.0
- adbc-driver-flightsql~=1.3.0
- pyarrow~=19.0.0
The dialect will be available from two different locations:
- The Python Package Index (PyPI)
- The Denodo Support Site.
The recommended way to install the dialect is by using the PyPi repository.
Installation from PyPi
The dialect can be easily installed into a Python environment using pip:
pip install denodo-sqlalchemy
Or:
pip install denodo-sqlalchemy[flightsql]
Note the flightsql extras dependency package will need to be specified in order for pip to install the dependencies needed to use the Flight SQL interface.
Important: note also that psycopg2 has its own requirements for installation which need to be satisfied: https://www.psycopg.org/docs/install.html#prerequisites
Installation from Denodo Support Site
Although less recommended as an installation method, the Denodo Dialect for SQLAlchemy can also be downloaded from the Denodo Support Site.
If installed from the package distributed from the Denodo Support Site, dependencies will need to be previously installed, either manually or via pip. See above (or the dialect’s README files) for a list of the specific dependency versions needed.
Important: note also that psycopg2 has its own requirements for installation which need to be satisfied: https://www.psycopg.org/docs/install.html#prerequisites
Once the package is unzipped, in order to install the dialect just run:
pip install route/to/denodo_sqlalchemy-<version>-py3-none-any.whl
Installing in Apache Superset
There are several scenarios to be taken into consideration regarding the use of this Dialect in Apache Superset:
- Denodo distributes customized Docker images of Apache Superset as a part of the “Denodo Dashboard” DenodoConnect component. These Docker images already contain the Denodo DBEngineSpec and the Denodo Dialect for SQLAlchemy, both pre-installed.
- Standard distributions of Apache Superset 5.0+ will include the Denodo DBEngineSpec and documentation in order to easily install the Denodo Dialect for SQLAlchemy.
- Standard distributions of Apache Superset 4.x can be manually added the Denodo DBEngineSpec (denodo.py) into its superset/db_engine_specs folder, and then installed the Denodo Dialect for SQLAlchemy.
Note that, as of Apache Superset 4.x, Superset uses SQLAlchemy 1.4 and therefore only the psycopg2 driver will be available to connect to Virtual DataPort (no Flight SQL connectivity will be available).
To create a new database you can use the SQLAlchemy URL pattern for Denodo dialect.
Usage
psycopg2
The dialect can be used with the psycopg2 (PostgreSQL) driver like:
uri = "denodo+psycopg2://<user>:<password>@<host>:<port[9996]>/<database>"
engine = sqlalchemy.create_engine(uri)
Note psycopg2 is the default driver so the above URL would be actually equivalent to:
uri = "denodo://<user>:<password>@<host>:<port:9996>/<database>"
Then data can be easily queried using pandas Data Frames:
query = "SELECT * FROM <table>"
data = pandas.read_sql(query, engine)
Query parameters can be expressed using the pyformat parameter style:
query = "SELECT * FROM books WHERE category = %(category)s"
data = pandas.read_sql(query, engine, params={"category": "Computer"})
flightsql
The dialect can be used with the flightsql (Flight SQL) driver like:
uri = "denodo+flightsql://<user>:<password>@<host>:<port[9994]>/<database>"
engine = sqlalchemy.create_engine(uri)
Note that the Flight SQL interface will assume TLS/SSL is enabled at the server and, if not, this should be explicitly signaled:
engine = sqlalchemy.create_engine(
uri,
connect_args={
denodo_sqlalchemy.flightsql.CONNECT_ARGS_USE_ENCRYPTION: "false"
})
Then data can be easily queried using pandas Data Frames:
query = "SELECT * FROM <table>"
data = pandas.read_sql(query, engine)
Query parameters can be expressed using the qmark parameter style:
query = "SELECT * FROM books WHERE category = ?"
data = pandas.read_sql(query, engine, params=("Computer",))
Limitations
- Note that Denodo Dialect for SQLAlchemy does not support CUID operations.