Tech Tips: Google Cloud Healthcare

Summary

This post is a continuation of my previous on the Google Healthcare API. In this post, I'll push the FHIR datastore into Google's data warehouse - BigQuery. Once in BigQuery, the data can then be subjected to traditional analytics tools (SQL queries) and visualized with Google's report/dashboard tool - Data Studio. For the purposes of these demos, I extended the Synthea-generated recordsets to 50 patient bundles.

Architecture

Below is a diagram of the cloud architecture. FHIR data (JSON-based) is transformed into relational database tables on BigQuery. SQL queries can then be created to analyze the data. Finally, the output of those queries can be saved as Views and then presented in charts in Data Studio.

BigQuery Execution

FHIR Export

Below is the gcloud command-line to export an FHIR datastore to BigQuery. This is a one-time export; however, it is possible to configure a continuous stream of updates from the FHIR store to BigQuery as well.

gcloud healthcare fhir-stores export bq $FHIR_STORE_ID \
  --dataset=$DATASET_ID \
  --location=$LOCATION \
  --bq-dataset=bq://$PROJECT_ID.$BIGQUERY_DATASET_ID \
  --schema-type=analytics

Query 1 - Top Ten Medications

At this point, a relational database is created within BigQuery and ready for analytics. Below are a query and its output to find the top 10 prescribed meds within the FHIR datastore.

Query 2 - Demographics

Below is a query that provides a bucketing of the patient age groups.

Query 3 - Top Ten Conditions

Below is a query to derive the top 10 conditions within the patient population.

Views

I then created views for each of these queries. Those views will be used for the presentation layer of the output in Data Studio. Below is the view of the demographics query.