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

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

Download original 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 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>

psycopg2 adapter

Apache Superset and Jupyter Notebook use the psycopg2 adapter by default and this adapter sends a few “dummy” queries to make sure that the database is a PostgreSQL with the right catalog tables.

Just copy and paste the VQL below in your VQL shell, inside the database you are going to specify in the connection string:

CREATE OR REPLACE FOLDER '/pg compatibility' ;

CREATE OR REPLACE VIEW pg_namespace FOLDER = '/pg compatibility' AS SELECT NULL AS oid, null as nspname FROM dual() CONTEXT ('formatted' = 'yes');

CREATE OR REPLACE VIEW pg_type FOLDER = '/pg compatibility' AS SELECT NULL AS oid, NULL AS typarray, NULL AS typname, NULL AS typnamespace FROM dual();

If you are using SQLAlchemy with Apache Superset or Jupyter Notebook you have different alternatives to the creation of these views, please check the section of each tool to know more about it.

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.

If you want to avoid the creation of “pg_” views in Denodo server you can add the use_native_hstore: false parameter in the database creation just using the “Extra” section:

 

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

If you want to avoid the creation of “pg_” views in the Denodo server you can 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>",

            use_native_hstore=False)

To execute SQL queries you can use:

pd.read_sql_query(query,engine)

If you have not installed pandas, you must create the "pg_" views and 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.