Atlas on Google Cloud — setup with BigQuery

In deze post beschrijven we hoe we Atlas hebben opgezet binnen een Google Cloud-omgeving, met BigQuery als source database. (Deze blogpost is in het Engels — de oorspronkelijke versie was internationaal gericht.)

Why Atlas?

But why setup Atlas anyway? Atlas is a very neat tool to conduct efficient analyses on standardized observational data converted to the OMOP Common Data Model (CDM). By converting healthcare data to a standardized format, researchers can easily apply their analyses to multiple data sources. Atlas facilitates these analyses and can be used out of the box to gain quick insights into OMOP CDM data. Also, since it is open source software, it is freely available and has a community of users and developers to help you on your way. Atlas mainly evolves around concept sets and cohorts which researchers can define based on the standardized vocabularies. Using these concept sets and cohorts, you can create groups of patients based on for example an exposure to a drug or diagnosis of a condition.

Although this might sound very easy, it takes quite some steps to get Atlas up and running. We chose to setup Atlas in our Google Cloud Platform (GCP) environment. Of course you can use any other cloud platform, or setup Atlas on premises. However, by showing it is possible to setup Atlas on a cloud platform such as GCP, we hope to prove you don’t need a large organization with existing IT infrastructure to start with data standardization in healthcare.

In this blog we’ll look at:

• Database setup (BigQuery)
• Creating a virtual machine to host Atlas
• Network and firewall rules
• Using Docker and Docker Compose
• Installing database drivers
• Refreshing your OMOP CDM data
• Debugging

Prerequisites

Since we’re running this project on Google Cloud, a Google Cloud project would come in handy. Also, this post assumes you already have data available in the OMOP CDM format.

• Google Cloud project
• BigQuery dataset containing OMOP data

Creating the BigQuery tables

To run Atlas, you need a database containing data formatted according to the OMOP Common Data Model. Usually you have an ETL-process in place to do just that. We’ve for instance designed an ETL in R to convert GemsTracker data from a MySQL database into OMOP CDM.

Once you have an ETL in place, you need a database to store the resulting data. Since we’re running this project in Google Cloud, we’ve opted for BigQuery. Luckily OHDSI provides us with the (almost) complete queries to initiate the necessary BigQuery tables. First, create a new BigQuery dataset dedicated to this data, and then run the BigQuery DDL. Simply replace @cdmDatabaseSchema with the name of the dataset you created. For now we’ll call this dataset cdm, but pick anything you like. When we created our tables, there were still some tiny bugs in the DDL code — you’ll spot soon enough. Also, you can use the version we prepared.

Lastly, we’ll also create two empty datasets for temporary and results data. We’ll call these temp and results. These datasets are needed later in the process.

Creating a VM to host Atlas

Next, we need a virtual machine to host Atlas. We use Google Compute Engine. To make sure our VM is secure, we use a private VPC and a VM without a public IP.

VPC network

First we create a private VPC-network. When creating a VPC-network, Google also asks you to create a new subnet. You can do this via the Google interface, or via cli:

gcloud compute networks create new-vpc-network \
  --project=new-project --subnet-mode=custom --mtu=1460 --bgp-routing-mode=regional

gcloud compute networks subnets create new-subnet \
  --project=new-project --range=10.0.0.0/24 --stack-type=IPV4_ONLY \
  --network=new-vpc-network --region=europe-west4

Firewall rules

Next, a firewall rule. This rule allows Identity-Aware Proxy (IAP) TCP forwarding to connect to the VM without a public IP. See Google’s documentation for more info on IAP.

We create a firewall rule to allow ingress from IAP via port 22 (SSH), limited to IP range 35.235.240.0/20 — Google’s internal range. We attach a network tag allow-ingress-from-iap so the rule only applies to VMs with that tag.

gcloud compute --project=new-project firewall-rules create allow-ingress-from-iap \
  --direction=INGRESS --priority=1000 --network=new-vpc-network \
  --action=ALLOW --rules=tcp:22 --source-ranges=35.235.240.0/20 \
  --target-tags=allow-ingress-from-iap

Compute instance

The VM that hosts Atlas. A basic Ubuntu 22 image is enough. Make sure the boot disk is large enough, and disable the public IP.

gcloud compute instances create atlas-instance \
  --project=new-project --zone=europe-west4-a --machine-type=e2-medium \
  --network-interface=stack-type=IPV4_ONLY,subnet=new-subnet,no-address \
  --tags=allow-ingress-from-iap,http-server,https-server \
  --create-disk=auto-delete=yes,boot=yes,size=50,type=pd-balanced,image=projects/ubuntu-os-cloud/global/images/family/ubuntu-2204-lts

Configuring the VM

Installing Docker

Now our VM is running, time to install Docker and Docker Compose. We use Broadsea to run Atlas. DigitalOcean has a perfect tutorial for installing Docker on Ubuntu — these next steps come from there.

sudo apt update
sudo apt install apt-transport-https ca-certificates curl software-properties-common
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg
echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
sudo apt update
sudo apt install docker-ce docker-compose
sudo systemctl status docker

Clone Broadsea

git clone git@github.com:OHDSI/Broadsea.git --branch v3.0.0
git clone git@github.com:OHDSI/WebAPI.git --branch v2.13.0

Service account

We need a Google service account with the right rights to run BigQuery jobs. Create a service account in Google IAM. Grant at least the BigQuery User and BigQuery Job User roles, and download the service account key. Upload that JSON file to the VM — Google’s web SSH has a convenient upload button.

BigQuery drivers

Broadsea ships with PostgreSQL drivers. To use BigQuery, we need to download the appropriate drivers ourselves:

sudo apt-get install unzip
sudo curl -o SimbaJDBCDriverforGoogleBigQuery42_1.2.25.1029.zip \
  https://storage.googleapis.com/simba-bq-release/jdbc/SimbaJDBCDriverforGoogleBigQuery42_1.2.25.1029.zip
sudo unzip SimbaJDBCDriverforGoogleBigQuery42_1.2.25.1029.zip -d bigquery
sudo rm SimbaJDBCDriverforGoogleBigQuery42_1.2.25.1029.zip

Installing WebAPI

Because we use our own WebAPI drivers, we do a custom WebAPI installation. Copy the BigQuery drivers into the WebAPI folder:

sudo cp -R bigquery WebAPI

Add the following line to the Dockerfile to make sure the BigQuery drivers are available during installation:

COPY /bigquery/ /code/src/main/extras/bigquery/

We also need a settings.xml file. Copy the sample:

sudo mkdir WebAPIConfig
sudo cp sample_settings.xml WebAPIConfig/settings.xml

The WebAPI’s pom.xml contains profiles per data source. We use the webapi-bigquery profile, modified to match the version numbers of our downloaded drivers. The full pom.xml block goes into the file — see the accompanying repository for the exact contents.

Connecting and refreshing data

After the build, the WebAPI talks to BigQuery directly. To refresh the OMOP CDM data, we usually run our ETL on a schedule (Cloud Scheduler triggers Cloud Run) and then trigger Atlas’s /refresh endpoint to invalidate caches.

Debugging

Most issues during initial setup come from:

• Service account permissions — make sure both BigQuery User and BigQuery Job User are present.
• Driver version mismatches — the pom.xml profile must match the version numbers in your downloaded drivers exactly.
• Network reachability — IAP TCP forwarding works for SSH, but the WebAPI talks to BigQuery via Google’s API; that needs egress, which a private VPC by default allows via Cloud NAT or Private Google Access.

---

The full code repository sits at github.com/Timformatie/Broadsea. Neem contact op als je vragen hebt over het opzetten van een OMOP-pipeline binnen jullie eigen omgeving.