Denodo Dialect for SQLAlchemy (feat. support for Apache Superset and Jupyter Notebook) - User Manual

最終更新日 2020年12月3日

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 SQLAlchemy. To install, just run:

pip install sqlalchemy

The Denodo dialect for SQLAlchemy can be downloaded from the Denodo Support Site.

To install 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.

To connect to Denodo VDP Server with SQLAlchemy, the following URL pattern can be used:

denodo://<username>:<password>@<host>:<odbcport>/<database>

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.

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.

Jupyter Notebook Installation

You can install Jupyter Notebook by following the installation manual.

If SQLAlchemy is not installed you can use pip from a Jupyter notebook to install it.

pip install sqlalchemy psycopg2

To execute Denodo queries you need to load the iPython SQL extension.

%load_ext sql

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)