Skip to content

Getting Started with Dorieh in ReD Environment

Introduction

Dorieh is a data engineering platform designed for manipulating diverse data types, specializing in ETL (Extract, Transform, Load) processes. In particular, it efficiently processes research health data provided by CMS (Centers for Medicare and Medicaid Services), optimizing it for statistical model datasets. The platform involves data cleansing, quality control, and aggregations required to run various statistical tools effectively.

For more details, visit the Dorieh Documentation.

This page will walk you through:

  1. Connecting to a Dorieh-enabled computing environment
  2. Setting up your personalized workspace (only once)
  3. Launching and verifying the Dorieh shell
  4. Testing database connectivity and running sample workflows
  5. Validating the Medicare data ETL pipeline

See the following related documentation pages:

  • Testing the ETL Workflow for Medicare Data
  • Adding new ResDac Data Adding new Medicare Data from ResDac to the ReD PostgreSQL Data Warehouse.
  • Exporting the content of the ReD PostgreSQL Data Warehouse to Parquet.

After completing the steps described here, you will have a verified computing environment ready for running ETL pipelines and exporting results as Parquet files.

Connecting to the environment

Please follow these steps:

  1. Log in to Okta (our SSO provider).

  2. From Okta, access the Open OnDemand (OOD) dashboard.

  3. From the OOD dashboard: Navigate to: Menu ▶ Clusters ▶ urcds-red-pc-prod Shell Access

Available clusters

At the time of writing, urcds-red-pc-prod Shell Access is the only entry for Clusters

You are now in the login node shell.

Note

If this is your first time logging in, proceed to Setting Up Your Workspace. Otherwise, you can jump directly to the Starting Dorieh Shell section.

Setting up workspace (one time procedure)

Create a script to reserve a worker node

Create a SLURM script that reserves a node for your session:

  1. Open vi (or another editor)
vi sleeping_job.sh
  1. Paste the following content:
#!/bin/bash  
#SBATCH -p dominici-dorieh   
#SBATCH -t 0-04:00 ### format is D-HH:MM   (days-hours:minutes)
#SBATCH -c 6 ### Number of cores to reserve 
hostname  
sleep infinity

In the above script adjust as needed:

  • The time (-t parameter). For testing 4 hours is enough, but for production you should allocate at least a week, e.g., 7-00:00 or more.
  • The number of cores. Available cores are used in production mode to parallelize initial data ingestion. It can also be used to allocate a separate node (e.g., for Parquet export) or to allow multiple users to work on the same node

Create working directories

Create the following directories that will be used by workflows:

cd  
mkdir -p mnt/work mnt/empty  
cd mnt

Create the database.ini file

In the working directory (~/mnt)

  1. Open vi (or another editor)
vi database.ini
  1. Paste the following content:
[dorieh]  
database=dorieh  
secret=aws:region=us-east-1:name=arn:aws:secretsmanager:us-east-1:443883268402:secret:rds_cluster-38699459-260f-4961-bd70-6c49df2295c8-1N9QF9
name=rds_cluster-38699459-260f-4961-bd70-6c49df2295c8  
host=dorieh-dev-aurora-cluster.cluster-cr2c3umyeq57.us-east-1.rds.amazonaws.com

[sandbox]  
database=sandbox  
secret=aws:region=us-east-1:name=arn:aws:secretsmanager:us-east-1:443883268402:secret:rds_cluster-38699459-260f-4961-bd70-6c49df2295c8-1N9QF9
name=rds_cluster-38699459-260f-4961-bd70-6c49df2295c8  
host=dorieh-dev-aurora-cluster.cluster-cr2c3umyeq57.us-east-1.rds.amazonaws.com

This provides working credentials for both sandbox (development and testing) and production databases.

Once complete, proceed to the Starting Dorieh Shell section.

Starting Dorieh shell

Create a worker node and ssh there

  1. Submit the fake job script and check the status of the job to find the worker node name:
sbatch sleeping_job.sh   
squeue

Worker node name

Note what is the worker node name

  1. SSH into the worker node (replace worker-node-name with the actual worker node name):
ssh dominici-dorieh-st-dominici-dorieh-cr-0-1 
# or whatever the worker node name is

Start singularity shell and perform sanity checks

Once you are on the worker node you need to start singularity shell for Dorieh. The following commands start a singularity shell and perform a few basic sanity checks.

singularity shell --bind /shared/home/${USER}/mnt/:/home/dorieh --bind /data --bind /shared/dorieh-logs/ /shared/software/singularity/dorieh/urcds-dorieh_latest.sif 
cd /home/dorieh/  
ls -alF  
# You should now see the database.ini file and a directory named empty.

## Check that Dorieh is installed
dorieh_version   

## Check connection to the data warehouse:
python -m dorieh.platform.util.psql  --connection  sandbox --db database.ini 'SELECT version();'

## Check that you can run a CWL Workflow
toil-cwl-runner /usr/local/lib/python3.9/site-packages/dorieh/cwl/version.cwl 

## Check that you can connect to the data warehouse from a CWL workflow
toil-cwl-runner /usr/local/lib/python3.9/site-packages/dorieh/cwl/handshake.cwl --database database.ini --connection sandbox

The very last command leaves two output files (to be renamed to something more meaningful):

  • grant.err - should be empty with length 0

  • grant.log - contains a list of all tables in the database

Testing Medicare processing workflow

Perform steps described in the Testing Medicare Workflow to ensure that Dorieh environment is fully functional