Queries for OHDSI on RDBMS for use on Hadoop

dkane · October 20, 2016, 5:32pm

Team, we’ve started work to build out a draft of the OHDSI data model on Hadoop (Impala), based on the existing data models from RDBMS.

We thought that it might be helpful to include views that may be common to how people use their data today. We are seeking some popular sample queries from people that are grabbing data sets today in the relational model so that we can build those views into Impala.

Can anyone share some common select statements that you might use today in the existing OHDSI Common Data Model?

Thanks,
Derek

schuemie · October 21, 2016, 8:05am

Hi @dkane,

I don’t know if this is helpful to mention, but most people in the OHDSI community interact with their data in the Common Data Model (CDM) through open source applications. Some of these applications are interactive web-based tools such as Atlas and Achilles. Others are R packages such as CohortMethod and SelfControlledCaseSeries.

These applications use SQL, and you can find the SQL for example here and here. The SQL uses some markup for parameterization, and is automatically translated to the SQL dialects we currently support (SQL Server, PostgreSQL, Oracle, RedShift, PDW) through a tool we developed called SqlRender.

Hope this helps.

Cheers,
Martijn

dkane · October 21, 2016, 11:47am

Martin,
Thanks for the background info. I’m a bit new to this, but it sounds like I
need to spend some time with SQL render.

Derek

Al_Pivonka · October 24, 2016, 12:53pm

Good day,
With the use of open source tools like (IPython) Jupiter, one should be able to utilize the OHDSI’s R-packages to create a standard OHDSI NoteBook and point it at different storage platforms (HDFS/Amazon S3) and storage formats (TEXT/AVRO/Parquet) and other data sources (HIVE/IMPALA/etc).

Chris_Knoll · October 25, 2016, 12:59am

Here’s a drug-era builder SQL. It’s kinda complicated, it uses window functions and nested queries. No temp tables on this one tho, but it might be a good test:

gist.github.com

https://gist.github.com/chrisknoll/a18c8e15ff66f26fac84

Drug Era Builder v5

with cteDrugTarget (DRUG_EXPOSURE_ID, PERSON_ID, DRUG_CONCEPT_ID, DRUG_TYPE_CONCEPT_ID, DRUG_EXPOSURE_START_DATE, DRUG_EXPOSURE_END_DATE) as
(
-- Normalize DRUG_EXPOSURE_END_DATE to either the existing drug exposure end date, or add days supply, or add 1 day to the start date
	select d.DRUG_EXPOSURE_ID, d. PERSON_ID, c.CONCEPT_ID, d.DRUG_TYPE_CONCEPT_ID, DRUG_EXPOSURE_START_DATE, 
		COALESCE(DRUG_EXPOSURE_END_DATE, DATEADD(day,DAYS_SUPPLY,DRUG_EXPOSURE_START_DATE), DATEADD(day,1,DRUG_EXPOSURE_START_DATE)) as DRUG_EXPOSURE_END_DATE
	FROM DRUG_EXPOSURE d
		join CONCEPT_ANCESTOR ca on ca.DESCENDANT_CONCEPT_ID = d.DRUG_CONCEPT_ID
		join CONCEPT c on ca.ANCESTOR_CONCEPT_ID = c.CONCEPT_ID
		where c.VOCABULARY_ID = 'RxNorm'
		and c.CONCEPT_CLASS_ID = 'Ingredient'

This file has been truncated. show original

And another one that calculates percentiles from the year of birth in the person table:

gist.github.com

https://gist.github.com/chrisknoll/1b38761ce8c5016ec5b2

Calculate Percentiles

with rawData(count_value) as -- replace below query to get the values you would like to find percentiles of
(
  select p.YEAR_OF_BIRTH
  from dbo.PERSON p
),
overallStats (avg_value, stdev_value, min_value, max_value, total) as
(
  select avg(1.0 * count_value) as avg_value,
    stdev(count_value) as stdev_value,
    min(count_value) as min_value,

This file has been truncated. show original