Teradata libraries

This option uses a regular Jupyter Lab notebook. We will see how to load the Teradata Python driver and use it from Python code. We will also examine the jupysql extension that adds support for SQL-only cells.

We start with a plain Jupyter Lab notebook. Here, we're 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 \
  -v "${PWD}":/home/jovyan/work quay.io/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:

Note

We've published a notebook with all the cells described below on GitHub: https://github.com/Teradata/quickstarts/blob/main/modules/ROOT/attachments/vantage-with-python-libraries.ipynb

%pip install teradatasqlalchemy

Now, we will import Pandas and define the connection string to connect to Teradata.

Note

The connection string format is teradatasql://username:password@host/database. Replace <host>, <username>, and <password> with your Teradata instance details. If you are connecting to a local Vantage Express VM from inside Docker on the same machine, use the special host.docker.internal hostname that Docker provides to reach the host machine.

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

# For a remote/cloud/trial Teradata instance:
db_connection_string = "teradatasql://<username>:<password>@<host>/dbc"

# For a local Vantage Express VM (running on the same machine as Docker):
# db_connection_string = "teradatasql://dbc:dbc@host.docker.internal/dbc"

Now we can call Pandas to query Teradata 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 Teradata. We will use jupysql and its %%sql magic to create SQL-only cells. We start with installing the required libraries.

Note

jupysql is the actively maintained successor to ipython-sql. It provides the same %sql / %%sql magic API and is compatible with current versions of all dependencies.

%pip install jupysql teradatasqlalchemy

We load jupysql and define the db connection string:

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

# For a remote/cloud/trial Teradata instance:
%sql teradatasql://<username>:<password>@<host>/dbc

# For a local Vantage Express VM (running on the same machine as Docker):
# %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()

There are many other features that jupysql provides, including variable substitution, plotting with matplotlib, writing results to a local csv file or back to the database. See the demo notebook for examples and jupysql documentation for a complete reference.