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.
Installation and Execution
This dialect requires the following Python modules:
- SQLAlchemy version >= 1.4, < 2.0.
- psycopg2 version 2.7 or higher.
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 sqlalchemy and psycopg2 dependencies do not need to be installed because when you install Denodo Dialect for SQLAlchemy both should be installed automatically.
Alternatively, these packages can be manually installed by means of:
pip install sqlalchemy<2.0
pip install psycopg2
Important: note also that psycopg2 has its own requirements for installation which need to be satisfied: https://www.psycopg.org/docs/install.html#prerequisites
The Denodo SQLAlchemy package can be installed from the public PyPI repository using pip:
pip install --upgrade denodo-sqlalchemy
Installation from Denodo Support Site
The sqlalchemy and psycopg2 dependencies must be manually installed using:
pip install sqlalchemy<2.0
pip install psycopg2
Important: note also that psycopg2 has its own requirements for installation which need to be satisfied: https://www.psycopg.org/docs/install.html#prerequisites
The Denodo dialect for SQLAlchemy can be downloaded from the Denodo Support Site.
To install dialect just run:
pip install route/to/denodo_sqlalchemy-20220419-py3-none-any.whl
To connect to Denodo VDP Server with SQLAlchemy, the following URL pattern can be used:
denodo://<username>:<password>@<host>:<odbcport>/<database>
Jupyter Notebook Installation
You can install Jupyter Notebook by following the installation manual.
The most immediate way to execute queries against Denodo is by using the create_engine() and execute() statements.
import sqlalchemy as db
engine=db.create_engine("denodo://admin:admin@localhost:9996/admin")
result_set=engine.execute("SELECT * FROM denodotest")
Alternatively, to execute queries, you can use the iPython SQL extension
%load_ext sql
If the extension is not installed just run:
pip install ipython-sql==0.4.1
Note that the Denodo dialect does not support SQLAlchemy 2.0 yet so the latest version of ipython-sql cannot be installed.
Connect with a Denodo database using the SQLAlchemy URL pattern for Denodo dialect.
%sql denodo://admin:admin@localhost:9996/admin
Run queries to Denodo views.
%sql select * from denodotest
It is also possible to use the results obtained from Denodo to draw histograms or other types of diagrams.
Another possible way is to install pandas to read data using SQL.
pip install pandas
When you’ve installed pandas, you will need to create a connection using:
db.create_engine("denodo://<user>:<pass>@<host>:<odbcport>/<database>")
To execute SQL queries you can use:
pd.read_sql_query(query,engine)
Apache Superset Installation
You can install Apache Superset by following the installation manual. SQLAlchemy is specified as a requirement so pip will install it if it is not already in place.
To use Superset it is necessary to download version 20201204 of the dialect from the Denodo Support Site. This is because the current version of SuperSet still does not support SQLAlchemy 1.4.
To install this older version of the dialect just unzip the Denodo dialect distribution inside the sqlalchemy/dialects folder making sure the files for the Denodo dialect are in a "..../dialects/denodo" folder.
If you have followed the recommendation to install superset in a virtualenv you must unzip the Denodo dialect in the directory /venv/lib/python3.6/site-packages/sqlalchemy/dialects
To create a new database you can use the SQLAlchemy URL pattern for Denodo dialect.
Once the database is created we can create tables and add them to a dashboard.
Limitations
- Note that Denodo Dialect for SQLAlchemy does not support CUID operations.