Azure Databases: Accessing Config & OMOP database in RIS

• This document provides the step by step guide on how to access our config and omop databases using the ris server.
• This is an essential step when running our ongoing ETL processes.

The List of database:

• Access and appropriate privileges on the RDC postgres databases.

Configuration Databases:

• i2rdc3-dev-pg-flex-config.dev.i2rdc3.wustl.edu (RW)
• i2rdc3-qa-pg-flex-config.qa.i2rdc3.wustl.edu (RW)
• i2rdc3-prod-pg-flex-config.prod.i2rdc3.wustl.edu (RW)

RDC OMOP Databases:

• postgresql.dev.i2rdc3.wustl.edu (RO pg_stat_activity table)
• postgresql.qa.i2rdc3.wustl.edu (RO pg_stat_activity table)
• postgresql.prod.i2rdc3.wustl.edu (RO pg_stat_activity table)

Access to the NIFI Administator's Group for the appropriate Environment.

• nifi.dev.i2rdc3.wustl.edu
• nifi.qa.i2rdc3.wustl.edu
• nifi.prod.i2rdc3.wustl.edu

Step 1: Log into RIS Server:

• To access the RIS server, you will need to open a CLI terminal and ssh into compute1-client-2.ris.wustl.edu.
• Once ssh is successful, cd repos/ris-etl-deployments/rdc_scripts directory.

Step 2: Run interactive File

• In the repos/ris-etl-deployments/rdc_scripts directory, execute the get-interative-job.sh file.
• Once you run the interative job, you will be prompted to select supported environments.
• Once you select your environment, you will get a list of database connections to use to connect to the database.

Set Connection

• To initiate connection, you will need to enter the set_connection and the number associated to the connection i.e., set_connection 2.
  • set_connection 2 allows us to connect to the user that starts with rdc_etl_user@postgresql.
• After setting the connection, run the psql ${PG_STRING} to confirm the database.

Validate

• After connecting to the database server, proceed to validating that you are connected to the right database by listing the databases in the database server.