How to query the database

Here we show how to query the NSAPH database from Python.

We use public data (climate, pollution, census) in the query, hence it can be executed in any environment.

Setup

To set up your execution environment, one can use either Python Virtual Environment or Conda environment.

Create Python Virtual Environment

First, we need to create a Python virtual environment. This can be done with commands like:

python3 -m venv .nsaph
source .nsaph/bin/activate

Creating Conda environment

To run the software on older operating systems, use Conda environment.

Install NSAPH packages

Next, we need to install NSAPH core packages. This can be done using GitHub install:

python -m pip install git+https://github.com/NSAPH-Data-Platform/nsaph-utils.git
python -m pip install git+https://github.com/NSAPH-Data-Platform/nsaph-core-platform.git

If you are getting errors installing nsaph-utils package or, if you see errors like “R Home is not defined”, you might need to set up Conda environment instead of Python Virtual Environment.

Create connection definition file

We need to create or update a database.ini file that stores connections to the database. Here is a sample file I use:

[mimic]
host=localhost
database=mimicii
user=postgres
password=*****

[nsaph2]
host=nsaph.cluster.uni.edu
database=nsaph
user=dbuser
password=*********
ssh_user=johndoe

Note that the first connection uses my local instance of PostgreSQL on my laptop. The second connects to NSAPH database. It is using ssh tunnel to connect - this is defined by adding ssh_user parameter.

mbouzinier is my username for both ssh and the database.

Executing the query

We will use the following sample Python program to execute a query (with public data) on the NSAPH database: query.py

Copy the file into your local directory and execute it:

python -u query.py database.ini nsaph2

Using EXPLAIN to optimize queries

You do not want to run a query that will take a week to execute. When we have hundreds of millions of records, this can easily happen. SQL is a declarative language, hence, an SQL statement describes what you want to do. DBMS optimizer decides how to do it. It should understand your query correctly. To ensure, it did, use EXPLAIN query before trying to execute. See documentation for EXPLAIN.

Here are a few more links that might be useful:

Unfortunately, less useful is the tutorial The queries below (given as examples) take 4 to 8 minutes each. I suggest running them with EXPLAIN first, note the costs and compare them with any costs of the queries you will write. Pay attention how indices are used: the most expensive part is scan.