A Google BigQuery Approach to Insights

“Data has stolen the limelight in the technology space today. The goal is to turn data into information, and information into insight”~ Carly Fiorina, former CEO, Hewlett-Packard Co.

Our team has been working on Google Cloud Platform(GCP) for sometime now. Through this blog, I will share an enriching experience of developing a Physicians’ Dashboard, which may help fellow engineers and enthusiasts to create a development environment using Google BigQuery.


We developed a Dashboard to help physicians in their efforts to ensure that their patients get the desired pain relief with minimal use of harmful and addictive drugs. This dashboard help physicians understand their own opioid prescribing behavior with respect to their peers, while providing a reference to publicly available information on opioids.

Opioids are a class of drugs that include the illegal drug heroin, synthetic opioids such as fentanyl, and pain relievers available legally by prescription, such as oxycodone (OxyContin®), hydrocodone (Vicodin®), codeine, morphine, and many others. Any long-term use can put someone at the risk of addiction even if the substance is used as prescribed. Many people who use opioids will develop a tolerance to them, a phenomenon that can trigger the cycle of addiction.

To develop this dashboard we used BigQuery as database backend with feature rich UI, based on D3JS. With the help of Python as middleware we knitted all these together.

Data Source

We gathered data from National Institute on Drug Abuse and related it to opioid crisis.

The Problem

One can wonder, “How does a person gets access to opioids and eventually gets addicted to them?”

It is a known fact by now that one common way to get opioids is through prescription by physicians for pain management. Since opioids are highly addictive, it is critical for physicians to prescribe them by following the best practices and keeping their patients health in mind. One way is to prescribe the right doses by obtaining the latest prescription information on these drugs and keeping abreast of their peers’ prescribing behavior.

So, physicians need a method to monitor their prescribing pattern against their peers and a way to check long time usage by the patient, including overdosing.

Our proposed dashboard serves the purpose by providing this information along with the other useful publicly available information about the drug to a physician, so that he /she can take an informed decision while prescribing the drug.

Our Solution

“A good solution is not always a complex solution”

The dashboard we developed is a simple yet powerful tool that can be used by a doctor like a ready reckoner to get information about the peers’ prescribing behaviour for a particular drug. Our intuitive platform with a simple user interface allows a physician to search and get information with highly detailed graphs and visualizations from the publicly available data. Let us walk through the architecture/technical details:

  1. Our solution is a web-based application developed on the top of Flask framework which makes it very easy to extend/change the application. It is hosted on a VPS (compute engine) and can be accessed from anywhere through internet.
  2. Our data source is google’s BigQuery where we have ported all the backend data. This data was further sorted, filtered, cleaned and reduced. We used Python as a middle layer to practically take care of everything:
  • It take inputs from the user and converts it into a BigQuery “query”
  • Authenticates itself on GCP BigQuery and runs the query on our stored data
  • Receives the result as a row object and converts it to json for the frontend display.

The following diagram will help you understand the above details better: 

The following sample Graph shows the distribution of Top 10 drugs prescribed in the state:


Our solution can be modified or extended to deal with medical data as well as any other kind of data such as financial data, and build queries for the same. Our approach is very modular and the queries reside in a local flat file database. The flexibility of the architecture makes it domain agnostic and hence this design can work across any domain.

In the meantime, if you are looking for Cloud Services – ONAP, OpenStack, Kubernetes, Cloud Native Application, DevSecOps and Infrastructure Modernization please contact us.

Contributor: Atulji Srivastava