How to query the database

Here we show how to query the Dorieh 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 .dorieh
source .dorieh/bin/activate

Creating Conda environment

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

Install Dorieh package

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

python -m pip install dorieh

If you are getting errors installing dorieh package with FST format support (dorieh[FST]), 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

See also: Managing database connections.

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

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

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

[dorieh]
database=dorieh
secret=aws:region=us-east-1:name=nsaph/public/dorieh/

Note that the first connection uses a local instance of PostgreSQL. The second connects to a database that is not accessible from a local machine.
It is using ssh tunnel to connect to a remote host that - this is defined by adding ssh_user parameter. The third connection uses AWS Secrets Manager to retrieve connection credentials.

johndoe is a username for ssh while dbuser is a username for the database.

Executing the query

We will use the following sample Python program to execute a query (with public data) on a Dorieh 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.