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.
Follow the guided installation process on their website to install your preferred version of the application.
From the Anaconda Navigator home screen, select Jupyter Notebook from the list of supported applications to launch the program.
More information can be found in the official Anaconda documentation.
The official Jupyter documentation provides instructions to run the notebook from the command line interface (CLI).
Launch Jupyter Notebook
After you have opened a new Jupyter Notebook web application, select the New dropdown from the UI, followed by Python 3 to create a new Notebook. The Notebook editor appears.
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.
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.
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.
conn = psycopg2.connect('''sslmode=require host=<YOUR_HOST_CREDENTIAL> port=80 dbname=prod:all user=<YOUR_ORGANIZATION_ID> password=<YOUR_PASSWORD>''')"
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.
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 pdimport numpy as npdf = pd.DataFrame(samples,columns=colnames)df.fillna(0,inplace=True)
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.
Enter and run the df.head() function to see the tabularized query results.
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.