How to connect Jupyter / Python to the warehouse

Do specialized analysis in Jupyter against data stored in Benchling

Some of our customers want to connect Jupyter to the warehouse. While there's lots of ways to do this, we thought we would walk through an example of one way this could be done.

First, let's install jupyter and the appropriate packages for connecting to a Postgres database...

$ brew install postgresql
$ pip install jupyter-lab
$ pip install ipython-sql
$ pip install psycopg2

Next, let's start a new notebook.

$ jupyter-lab

Next, let's configure our connection to the warehouse. First, we go to our settings page and generate a new set of warehouse credentials. The settings page can be found by clicking the user icon in the lower left hand corner.

Now, in jupyter, let's configure the connection:

%load_ext sql
username = 'INSERT_USERNAME_HERE'
password = 'INSERT_PASSWORD_HERE'
host = 'postgres-warehouse.INSERT_TENANT.benchling.com:5432'

%sql postgresql://$username:$password@$host/warehouse

If you don't have any errors, the connection should have worked! You can test it by running an example query like so:

%sql SELECT * FROM registry_entity LIMIT 100

Feel free to contact us if you have any questions or issues!