How to resolve the error in ETL?

[YiKuanLiu]

I'm trying to build up the mimic-omop,
and when I finally went to the last step, the check result is not good, as below:

psql:etl/check_etl.sql:1: unrecognized value "" for "ECHO"
Available values are: none, errors, queries, all.
Output format is unaligned.
Pager usage is off.
psql:etl/check_etl.sql:15: etl/StandardizedVocabularies/CONCEPT/check_etl.sql: No such file or directory
psql:etl/check_etl.sql:16: etl/StandardizedVocabularies/COHORT_DEFINITION/check_etl.sql: No such file or directory
psql:etl/check_etl.sql:17: etl/StandardizedVocabularies/ATTRIBUTE_DEFINITION/check_etl.sql: No such file or directory
psql:etl/check_etl.sql:18: etl/StandardizedDerivedElements/COHORT_ATTRIBUTE/check_etl.sql: No such file or directory
psql:etl/check_etl.sql:19: etl/StandardizedHealthSystemDataTables/CARE_SITE/check_etl.sql: No such file or directory
1..4
ok 1 - PERSON -- number patients match
ok 2 - PERSON -- gender distribution matches
ok 3 - PERSON -- date of birth year distributions match
ok 4 - PERSON -- no births after deaths
1..1
not ok 1 - number of unique patients who die in the database
Failed test 1: "number of unique patients who die in the database"
Results differ beginning at row 1:
have: (15759)
want: (14849)
Looks like you failed 1 test of 1
1..7
ok 1 - Visit_occurrence table -- same number admission
ok 2 - number of hospital admissions who die in-hospital match
ok 3 - Visit_occurrence table -- same distribution adm
ok 4 - Visit_occurrence table -- distribution admit source value
ok 5 - Visit_occurrence table -- repartition discharge_to_source_value
ok 6 - Visit_occurrence table -- links checker
ok 7 - Visit_occurrence table -- start_date > end_date
1..1
ok 1 - OBSERVATION_PERIOD -- row count matches visit_occurrence
1..7
not ok 1 - VISIT_DETAIL -- test same number transfers
Failed test 1: "VISIT_DETAIL -- test same number transfers"
Results differ beginning at row 1:
have: NULL
want: (184142,19)
ok 2 - VISIT_DETAIL -- test visit_source_value and visit_source_concept_id match
not ok 3 - VISIT_DETAIL -- test admitting_source_concept_id and admitting_source_value match
Failed test 3: "VISIT_DETAIL -- test admitting_source_concept_id and admitting_source_value match"
Results differ beginning at row 1:
have: (0)
want: (139516)
not ok 4 - VISIT_DETAIL -- test patients number in visit_detail/icustays
Failed test 4: "VISIT_DETAIL -- test patients number in visit_detail/icustays"
Results differ beginning at row 1:
have: (0,0)
want: (46476,57786)
not ok 5 - VISIT_DETAIL -- check start_datetime < end_datetime
Failed test 5: "VISIT_DETAIL -- check start_datetime < end_datetime"
Number of columns or their types differ between the queries:
have: (161)
want: (0)
not ok 6 - VISIT_DETAIL -- check start_date < end_date
Failed test 6: "VISIT_DETAIL -- check start_date < end_date"
Number of columns or their types differ between the queries:
have: (35)
want: (0)
ok 7 - VISIT_DETAIL -- check care site is never null
Looks like you failed 5 tests of 7
1..7
not ok 1 - MEASUREMENT -- check distribution of all labs match
Failed test 1: "MEASUREMENT -- check distribution of all labs match"
Results differ beginning at row 1:
have: (22014,-22,472,794)
want: NULL
psql:etl/StandardizedClinicalDataTables/MEASUREMENT/check_etl.sql:67: ERROR: syntax error at or near "AS"
LINE 7: AS org_name
^
QUERY:
SELECT org_name::TEXT, count(1)
FROM
(
SELECT DISTINCT ON
(hadm_id, spec_type_desc, org_name, coalesce(charttime, chartdate))
AS org_name
FROM microbiologyevents
WHERE org_name IS NOT NULL
) tmp
GROUP BY org_name ORDER BY 2, 1 desc;

CONTEXT: PL/pgSQL function results_eq(text,text,text) line 7 at OPEN
ok 2 - MEASUREMENT -- there is source concept in measurement not described
ok 3 - MEASUREMENT -- check for duplicate primary keys
ok 4 - MEASUREMENT -- standard concept checker
not ok 5 - MEASUREMENT -- check row counts match
Failed test 5: "MEASUREMENT -- check row counts match"
Results differ beginning at row 1:
have: NULL
want: (1,6330)
ok 6 - Measurement pass, w00t!
Looks like you planned 7 tests but ran 6
1..5
ok 1 - PROCEDURE_OCCURRENCE -- check all procedureevents_mv rows inserted
ok 2 - PROCEDURE_OCCURRENCE -- check label is consistent with source_value
ok 3 - PROCEDURE_OCCURRENCE -- check all CPT code rows inserted
ok 4 - PROCEDURE_OCCURRENCE -- check CPT subsections mapped correctly
ok 5 - PROCEDURE_OCCURRENCE -- check ICD procedure rows inserted
1..1
ok 1 - PROVIDER -- check caregivers/providers match
1..8
ok 1 - CONDITION OCCURRENCE -- check ICD diagnoses row count matches
ok 2 - CONDITION OCCURRENCE -- diagnosis in admission same
ok 3 - CONDITION OCCURRENCE -- distrib diagnosis the same
ok 4 - CONDITION OCCURRENCE -- there is source concept in measurement not described
ok 5 - CONDITION OCCURRENCE -- primary key checker
ok 6 - CONDITION OCCURRENCE -- standard concept checker
not ok 7 - CONDITION OCCURRENCE -- start_datetime should be > end_datetime
Failed test 7: "CONDITION OCCURRENCE -- start_datetime should be > end_datetime"
Results differ beginning at row 1:
have: (585)
want: (0)
ok 8 - CONDITION OCCURRENCE -- start_date should be > end_date
Looks like you failed 1 test of 8
1..10
not ok 1 - OBSERVATION -- religion distribution matches (concept 4052017)
Failed test 1: "OBSERVATION -- religion distribution matches (concept 4052017)"
Results differ beginning at row 1:
have: NULL
want: (LUTHERAN,1)
not ok 2 - OBSERVATION -- language distribution matches (concept 40758030)
Failed test 2: "OBSERVATION -- language distribution matches (concept 40758030)"
Results differ beginning at row 1:
have: NULL
want: (*YOR,1)
not ok 3 - OBSERVATION -- marital distribution matches (concept 40766231)
Failed test 3: "OBSERVATION -- marital distribution matches (concept 40766231)"
Results differ beginning at row 1:
have: NULL
want: ("LIFE PARTNER",15)
not ok 4 - OBSERVATION -- insurance distribution matches (concept 46235654)
Failed test 4: "OBSERVATION -- insurance distribution matches (concept 46235654)"
Results differ beginning at row 1:
have: NULL
want: ("Self Pay",611)
not ok 5 - OBSERVATION -- ethnicity distribution matches (concept 44803968)
Failed test 5: "OBSERVATION -- ethnicity distribution matches (concept 44803968)"
Results differ beginning at row 1:
have: NULL
want: ("AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGNIZED TRIBE",3)
ok 6 - OBSERVATION -- source concept described
ok 7 - OBSERVATION -- primary key is always unique
not ok 8 - OBSERVATION -- datetimeevents number
Failed test 8: "OBSERVATION -- datetimeevents number"
Results differ beginning at row 1:
have: (0)
want: (4485342)
ok 9 - OBSERVATION -- Standard concept checker
Looks like you planned 10 tests but ran 9
1..4
not ok 1 - DRUG_EXPOSURE -- check number of patients with prescription matches
Failed test 1: "DRUG_EXPOSURE -- check number of patients with prescription matches"
Results differ beginning at row 1:
have: (0,0)
want: (39363,50216)
not ok 2 - DRUG_EXPOSURE -- check drug_source_value matches source
Failed test 2: "DRUG_EXPOSURE -- check drug_source_value matches source"
Results differ beginning at row 1:
have: NULL
want: ("zz ",1)
ok 3 - DRUG_EXPOSURE -- is concept source id full filled
ok 4 - DRUG_EXPOSURE -- Standard concept checker
Looks like you failed 2 tests of 4
1..2
ok 1 - visit occurrence nb
ok 2 - check radio nb
1..3
ok 1 - primary key checker
ok 2 - source concept described
ok 3 - source concept described
ok 4 - Note Nlp pass, w00t!
Looks like you planned 3 tests but ran 4

So, my question is: why are these errors invoked? And what are the solutions?
Thank you!!

[parisni]

hi ! do you have the etl/StandardizedHealthSystemDataTables/CARE_SITE/check_etl.sql files ? apparently most of the tests works. BTW tests are not mandatory to run the ETL

[YiKuanLiu]

Hi, thanks for the replying!
I checked my folder etl/StandardizedHealthSystemDataTables/CARE_SITE/,
there's only 2 files: etl.sql and README.md. Is that right?
How can I get the check_etl.sql files?

[YiKuanLiu]

@parisni
I've check my mimic database with omop search_path, and I found there's nothing (0 rows) in the omop.measurement...

Is that implying some certain step got wrong? I think it should be related to the errors.
Please help me out.

Thanks