Custom Reports - synthetichealth/synthea GitHub Wiki
(The internal H2 database and Custom Report feature have been removed from Synthea for performance reasons. This page is left for historical reference only)
Synthea allows for the creation of custom reports each time a population is generated, using the CustomSqlReport
class. This class uses the internal H2 data store to run arbitrary SQL queries against. These queries are defined in a configurable SQL file, and the results are exported in CSV format to ./output/reports/
. Currently only SELECT statements are allowed in the SQL.
The Synthea DataStore uses an H2 database under the hood, so any queries must be valid H2-style syntax. See H2 Database Functions for more information on what functions are available.
Configuration Settings
generate.database_type
: REQUIRED: Set toin-memory
orfile
to enable the database against which the queries will be runexporter.custom_report
: Set totrue
to enable the custom reports. Defaults tofalse
.exporter.custom_report_queries_file
: Set this to the location of an SQL file (relative tosrc/main/resources
) containing queries to run at each population generation. Defaults tocustom_queries.sql
(akasrc/main/resources/custom_queries.sql
)
Available Database Tables
(see also src/main/java/org/mitre/synthea/datastore/DataStore.java
for more information on how these tables are populated)
PERSON
CREATE TABLE IF NOT EXISTS PERSON
(id varchar, name varchar, date_of_birth bigint, date_of_death bigint,
race varchar, gender varchar, socioeconomic_status varchar)
ATTRIBUTE
CREATE TABLE IF NOT EXISTS ATTRIBUTE
(person_id varchar, name varchar, value varchar)
PROVIDER
CREATE TABLE IF NOT EXISTS PROVIDER (id varchar, name varchar)
PROVIDER_ATTRIBUTE
CREATE TABLE IF NOT EXISTS PROVIDER_ATTRIBUTE
(provider_id varchar, name varchar, value varchar)
ENCOUNTER
CREATE TABLE IF NOT EXISTS ENCOUNTER
(id varchar, person_id varchar, provider_id varchar, name varchar, type varchar,
start bigint, stop bigint, code varchar, display varchar, system varchar)
CONDITION
CREATE TABLE IF NOT EXISTS CONDITION
(person_id varchar, name varchar, type varchar, start bigint, stop bigint,
code varchar, display varchar, system varchar)
MEDICATION
CREATE TABLE IF NOT EXISTS MEDICATION
(id varchar, person_id varchar, provider_id varchar, name varchar, type varchar,
start bigint, stop bigint, code varchar, display varchar, system varchar)
PROCEDURE
CREATE TABLE IF NOT EXISTS PROCEDURE
(person_id varchar, encounter_id varchar, name varchar, type varchar,
start bigint, stop bigint, code varchar, display varchar, system varchar)
REPORT
CREATE TABLE IF NOT EXISTS REPORT
(id varchar, person_id varchar, encounter_id varchar, name varchar, type varchar,
start bigint, code varchar, display varchar, system varchar)
OBSERVATION
CREATE TABLE IF NOT EXISTS OBSERVATION
(person_id varchar, encounter_id varchar, report_id varchar, name varchar,
type varchar, start bigint, value varchar, unit varchar,
code varchar, display varchar, system varchar)
IMMUNIZATION
CREATE TABLE IF NOT EXISTS IMMUNIZATION
(person_id varchar, encounter_id varchar, name varchar, type varchar,
start bigint, code varchar, display varchar, system varchar)
CAREPLAN
CREATE TABLE IF NOT EXISTS CAREPLAN
(id varchar, person_id varchar, provider_id varchar, name varchar, type varchar,
start bigint, stop bigint, code varchar, display varchar, system varchar)
IMAGING_STUDY
CREATE TABLE IF NOT EXISTS IMAGING_STUDY
(id varchar, uid varchar, person_id varchar, encounter_id varchar, start bigint,
modality_code varchar, modality_display varchar, modality_system varchar,
bodysite_code varchar, bodysite_display varchar, bodysite_system varchar,
sop_class varchar)
CLAIM
CREATE TABLE IF NOT EXISTS CLAIM
(id varchar, person_id varchar, encounter_id varchar, medication_id varchar,
time bigint, cost decimal)
COVERAGE
CREATE TABLE IF NOT EXISTS COVERAGE (person_id varchar, year int, category varchar)
QUALITY_OF_LIFE
CREATE TABLE IF NOT EXISTS QUALITY_OF_LIFE
(person_id varchar, year int, qol double, qaly double, daly double)
UTILIZATION
CREATE TABLE IF NOT EXISTS UTILIZATION
(provider_id varchar, year int, encounters int, procedures int,
labs int, prescriptions int)
UTILIZATION_DETAIL
CREATE TABLE IF NOT EXISTS UTILIZATION_DETAIL
(provider_id varchar, year int, category varchar, value int)
Sample Queries
(Note: these queries have been formatted for readability. Currently the report generator requires that all queries be contained on a single line.)
Sample Query 1: select everything from the "person" table.
select * from person;
Sample Query 2: select the number of living people.
select count(*) from person where person.DATE_OF_DEATH is null;
Sample Query 3: select the people that have an active diagnosis of diabetes, along with the age of diagnosis
SELECT p.name, p.gender,
DATEADD('MILLISECOND', p.DATE_OF_BIRTH, DATE '1970-01-01') DOB,
DATEADD('MILLISECOND', c.start , DATE '1970-01-01') onset_date,
DATEDIFF('YEAR',
DATEADD('MILLISECOND', p.DATE_OF_BIRTH, DATE '1970-01-01'),
DATEADD('MILLISECOND', c.start, DATE '1970-01-01')) age_at_diagnosis
FROM PERSON p, CONDITION c
WHERE p.ID = c.PERSON_ID
AND c.code = '44054006';