Use Vantage from a Jupyter notebook

We start with a plain Jupyter Lab notebook. Here, I’m using docker but any method of starting a notebook, including Jupyter Hub, Google Cloud AI Platform Notebooks, AWS SageMaker Notebooks, Azure ML Notebooks will do.

docker run --rm -p 8888:8888 -e JUPYTER_ENABLE_LAB=yes \
  -v "${PWD}":/home/jovyan/work jupyter/datascience-notebook

Docker logs will display the url that you need to go to:

Entered start.sh with args: jupyter lab
Executing the command: jupyter lab
....
To access the server, open this file in a browser:
    file:///home/jovyan/.local/share/jupyter/runtime/jpserver-7-open.html
Or copy and paste one of these URLs:
    http://d5c2323ae5db:8888/lab?token=5fb43e674367c6895e8c2404188aa550b5c7bdf96f5b4a3a
  or http://127.0.0.1:8888/lab?token=5fb43e674367c6895e8c2404188aa550b5c7bdf96f5b4a3a

We will open a new notebook and create a cell to install the required libraries:

import sys
!{sys.executable} -m pip install teradatasqlalchemy

Now, we will import Pandas and define the connection string to connect to Teradata. Since I’m running my notebook in Docker on my local machine and I want to connect to a local Vantage Express VM, I’m using host.docker.internal DNS name provided by Docker to reference the IP of my machine.

import pandas as pd
# Define the db connection string. Pandas uses SQLAlchemy connection strings.
# For Teradata Vantage, it's teradatasql://username:password@host/database_name .
# See https://pypi.org/project/teradatasqlalchemy/ for details.
db_connection_string = "teradatasql://dbc:dbc@host.docker.internal/dbc"

I can now call Pandas to query Vantage and move the result to a Pandas dataframe:

pd.read_sql("SELECT * FROM dbc.dbcinfo", con = db_connection_string)

The syntax above is concise but it can get tedious if all you need is to explore data in Vantage. We will use ipython-sql and its %%sql magic to create SQL-only cells. We start with importing the required libraries.

import sys
!{sys.executable} -m pip install ipython-sql teradatasqlalchemy

We load ipython-sql and define the db connection string:

%load_ext sql
# Define the db connection string. The sql magic uses SQLAlchemy connection strings.
# For Teradata Vantage, it's teradatasql://username:password@host/database_name .
# See https://pypi.org/project/teradatasqlalchemy/ for details.
%sql teradatasql://dbc:dbc@host.docker.internal/dbc

We can now use %sql and %%sql magic. Let’s say we want to explore data in a table. We can create a cell that says:

%%sql
SELECT * FROM dbc.dbcinfo

If we want to move the data to a Pandas frame, we can say:

result = %sql SELECT * FROM dbc.dbcinfo
result.DataFrame()

Run the image:

docker volume create notebooks
docker run -e "accept_license=Y" -p :8888:8888 \
  -v notebooks:/home/jovyan/JupyterLabRoot \
  teradata/jupyterlab-extensions

Docker logs will display the url that you need to go to. For example, this is what I’ve got:

Starting JupyterLab ...
Docker Build ID = 3.2.0-ec02012022
Using unencrypted HTTP

Enter this URL in your browser:  http://localhost:8888?token=96a3ab874a03779c400966bf492fe270c2221cdcc74b61ed

* Or enter this token when prompted by Jupyter: 96a3ab874a03779c400966bf492fe270c2221cdcc74b61ed
* If you used a different port to run your Docker, replace 8888 with your port number

Open up the URL and use the file explorer to open the following notebook: jupyterextensions → notebooks → sql → GettingStartedDemo.ipynb.

Go through the demo of the Teradata SQL Kernel: