Introduction
The Python notebook has become the swiss-army-knife for data science, transforming static code into shareable, version-controlled narratives. Yet, as workloads and datasets grow in complexity, the connection between the data scientist and the data is often the primary bottleneck.
To realize the full potential of Python Workbooks like Jupyter, the Denodo FlightSQL interface, available through the ADBC driver, moves beyond the limitations of legacy drivers. This integration is purpose-built for large-scale data transfers, significantly reducing overhead and providing the high-speed throughput essential for data science workloads.
While direct connections using the package are fully supported, we will focus exclusively on SQLAlchemy for this guide. Utilizing SQLAlchemy on top of the ABDC driver offers useful abstractions that make writing and executing VQL much simpler.
Connectivity Options
Denodo provides multiple pathways to interface with its Virtual Data Port (VDP) server, depending on your performance requirements and existing architecture:
- ABDC FlightSQL Driver: Utilize the FlightSQL driver adbc-driver-flightsql directly within Python without any dependencies on SQLAlchemy (port 9994 of VDP 9.1+).
For instructions on how to use the adbc-driver-flightsql package directly, please refer to the dedicated section in the documentation.
- ADBC via Denodo Dialect for SQLAlchemy (port 9994 of VDP 9.1+): Detailed below in this article.
- Legacy Support via Psycopg2 Denodo Dialect for SQLAlchemy (port 9996 of VDP) .
ADBC via Denodo Dialect for SQLAlchemy
Requirements
- Denodo VDP
- Jupyter Notebooks
- Denodo recommends using Python virtual environments. Virtual environments help manage python dependencies and versions. additional information
Installing Jupyter Notebooks for Denodo
The Denodo SQLAlchemy package can be installed from the public PyPI repository using pip:
For flightsql
pip install denodo-sqlalchemy[flightsql] |
For psycopg2
pip install denodo-sqlalchemy |
Additionally, Jupyter Notebook can also be installed with pip
pip install jupyter |
Then start Jupyter with the command
jupyter notebook |
Querying Denodo with Jupyter Notebook
Creating a notebook
After starting Jupyter Notebook, a browser window automatically opens which will list the current working directory contents. Select New > Python
Querying Denodo
The recommended way to connect to a database using Jupyter is through the SQLAlchemy library. Once you have imported the library, we can use a standard SQLAlchemy program to query Denodo. For example:
Flightsql
import sqlalchemy from denodo.sqlalchemy import flightsql as denodo_flightsql import pandas # connect using the flightsql SQLAlchemy URI pattern for Denodo uri="denodo+flightsql://<user>:<password>@<host>:9994/<database>" engine = sqlalchemy.create_engine(uri) # Execute the query and display using Pandas df = pandas.read_sql("SELECT id, name, age FROM vector_doc_parse.test LIMIT 100", engine) df |
psycopg2
import sqlalchemy as db import pandas # connect using the psycopg2 SQLAlchemy URL pattern for Denodo dialect engine=db.create_engine("denodo+psycopg2://<user>:<password>@<host>:9996/admin") # Execute the query and display using Pandas df = pandas.read_sql("SELECT * from personal_data_crm_db", engine) df |
Output
More information is available on the Denodo Dialect for SQLAlchemy user manual where you can find further information on the procedure for installing modules and how to easily execute queries with SQLAlchemy.
References
Denodo Dialect for SQLAlchemy - User Manual
How to connect to Denodo from Python - a starter for Data Scientists
PYPI Denodo Dialect for Sqlalchemy
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.
Recommended resources Recommendations generated by AI
Questions
