Connect Jupyter Notebook to Query Service

This document covers the steps required to connect Jupyter Notebook with Adobe Experience Platform Query Service.

Getting started

This guide requires that you already have access to Jupyter Notebook and are familiar with its interface. To download Jupyter Notebook or for more information, see the official Jupyter Notebook documentation.

To acquire the necessary credentials for connecting Jupyter Notebook to Experience Platform, you must have access to the Queries workspace in the Platform UI. Please contact your organization administrator if you do not currently have access to the Queries workspace.

TIP

Anaconda Navigator is a desktop graphical user interface (GUI) that provides an easier way to install and launch common Python programs such as Jupyter Notebook. It also helps to manage packages, environments, and channels without using command-line commands.
You can install your preferred version of the application from their website.
Follow the guided installation process. From the Anaconda Navigator home screen, select Jupyter Notebook from the list of supported applications to launch the program.
The Anaconda Navigator home screen with Jupyter Notebook highlighted.
More information can be found in their official documentation.

Launch Jupyter Notebook

After you have opened a new Jupyter Notebook web application, select the New dropdown followed by Python 3 to create a new Notebook. The Notebook editor appears.

The Jupiter Notebook File tab with the New dropdown and Python 3 highlighted.

On the first line of the Notebook editor, enter the following value: pip install psycopg2-binary and select Run from the command bar. A success message appears below the input line.

IMPORTANT

As part of this process to form a connection, you must select Run to execute each line of code.

The Notebook UI with the install libraries command highlighted.

Next, import a PostgreSQL database adapter for Python. Enter the value: import psycopg2and select Run. There is no success message for this process. If there is no error message, continue to the next step.

The Notebook UI with the import database driver code highlighted.

You must now provide your Adobe Experience Platform credentials by entering the value: conn = psycopg2.connect("{YOUR_CREDENTIALS}"). Your connection credentials can be found in the Queries section, under the Credentials tab of the Platform UI. See the documentation on how to find your organization credentials for detailed instructions.

The use of non-expiring credentials is recommended when using third-party clients to save the effort of repeatedly entering your details. See the documentation for instructions on how to generate and use non-expiring credentials.

IMPORTANT

When copying credentials from the Platform UI, ensure that there is no additional formatting of the credentials. They should all be in one line, with a single space between the properties and values. The credentials are enclosed in quotation marks and not comma-separated.

The Notebook UI with the connection credentials highlighted.

Your Jupyter Notebook instance is now connected to Query Service.

Example query execution

Now that you have connected Jupyter Notebook to Query Service, you can perform queries on your datasets using your Notebook inputs. The following example uses a simple query to demonstrate the process.

Enter the following values:

cur = conn.cursor()
cur.execute('''{YOUR_QUERY_HERE}''')
data = [r for r in cur]

Next, call the parameter (data in the example above) to display the query results in an unformatted response.

The Notebook UI with commands to return and display SQL results within the Notebook.

To format the results in a more human-readable way, use the following commands:

  • colnames = [desc[0] for desc in cur.description]
  • import pandas as pd
  • import numpy as np

These commands do not generate a success message. If there is no error message you can then use a function to output the results of your SQL query in a table format.

The commands required to format the SQL results.

Enter and run the df.head() function to see the tabularized query results.

Tabularized results of your SQL query within Jupyter Notebook.

Next steps

Now that you’ve connected with Query Service, you can use Jupyter Notebook to write queries. For more information on how to write and run queries, please read the running queries guide.

On this page