Broken search paths and bad SQL file names in Linux bulk load

[turbomam]

I had trouble creating a Synthea OMOP database with the R scripts (see #13 #14 #15)

So today I tried the Linux bulk loader now on Ubuntu 18 and PostgreSQL 11. I think everything went well up to vocab mapping and core ETL, but then it was all errors after that. See end of post.

I think the search path my not be set correctly in ETL/SQL/AllVisitTable.sql or the ETL steps. Also, the reference to ETL/SQL/AAViTable.sql should be replaced with ETL/SQL/AAVITable.sql

I tried running some of the R scripts individually after that, I think that exposed some other bugs. But it also may have led to duplicate row IDs and mismatched concept realms (see #18)

So finally I set the search path (in DBeaver)

set search_path=cdm_synthea10,native;

and then just copied, pasted, and executed he remaining ETL/SQL/*.sql files in DBeaver, too.

PS there's also a bad filename in

PGOPTIONS="--search_path=$CDM" psql -f "ETL/SQL/cdm_v5.3.constraints_ddl.sql" "postgresql://$USERNAME:$PASSWORD@localhost/$DB"

but maybe you don't care as much about that since it's commented out.

Attempted bulk load session

ubuntu@ip-172-31-88-67:~/ETL-Synthea$ ./bulk-load mimicomop native cdm_synthea10 mimicuser SECRET /home/ubuntu/synthea/output/csv/ /opt/mimicomop/data/vocab
DROP TABLE
CREATE TABLE
---etc---
creating cdm tables
psql:ETL/SQL/drop_cdm_tables.sql:1: ERROR:  table "achilles_analysis" does not exist
psql:ETL/SQL/drop_cdm_tables.sql:2: ERROR:  table "achilles_heel_results" does not exist
psql:ETL/SQL/drop_cdm_tables.sql:3: ERROR:  table "achilles_results" does not exist
psql:ETL/SQL/drop_cdm_tables.sql:4: ERROR:  table "achilles_results_derived" does not exist
psql:ETL/SQL/drop_cdm_tables.sql:5: ERROR:  table "achilles_results_dist" does not exist
DROP TABLE
---etc---
psql:ETL/SQL/drop_cdm_tables.sql:26: ERROR:  table "drug_strength" does not exist
DROP TABLE
---etc---
CREATE TABLE
---etc---
loading data from synthea csv files to relational database
COPY 639
COPY 3096
COPY 8103
COPY 37811
COPY 14695
COPY 8468
COPY 182176
COPY 1152
COPY 31648
loading data from vocab csv files to relational database
COPY 6136766
COPY 60879903
COPY 41501098
COPY 356
COPY 7856597
COPY 46
COPY 2616623
COPY 488
COPY 90
creating vocab maps...
DROP TABLE
SELECT 6136766
CREATE INDEX
CREATE INDEX
DROP TABLE
SELECT 4902384
CREATE INDEX
CREATE INDEX
creating visit logic tables...
psql:ETL/SQL/AllVisitTable.sql:1: NOTICE:  table "ip_visits" does not exist, skipping
DROP TABLE
psql:ETL/SQL/AllVisitTable.sql:2: NOTICE:  table "er_visits" does not exist, skipping
DROP TABLE
psql:ETL/SQL/AllVisitTable.sql:3: NOTICE:  table "op_visits" does not exist, skipping
DROP TABLE
DROP TABLE
psql:ETL/SQL/AllVisitTable.sql:57: ERROR:  relation "encounters" does not exist
LINE 14:    FROM encounters
                 ^
psql:ETL/SQL/AllVisitTable.sql:90: ERROR:  relation "encounters" does not exist
LINE 22:   FROM encounters CL1
                ^
psql:ETL/SQL/AllVisitTable.sql:113: ERROR:  relation "encounters" does not exist
LINE 10:  FROM encounters
               ^
DROP SEQUENCE
CREATE SEQUENCE
psql:ETL/SQL/AllVisitTable.sql:131: ERROR:  relation "ip_visits" does not exist
LINE 6:    SELECT * FROM IP_VISITS
                         ^
psql:ETL/SQL/AllVisitTable.sql:133: NOTICE:  table "ip_visits" does not exist, skipping
DROP TABLE
psql:ETL/SQL/AllVisitTable.sql:134: NOTICE:  table "er_visits" does not exist, skipping
DROP TABLE
psql:ETL/SQL/AllVisitTable.sql:135: NOTICE:  table "op_visits" does not exist, skipping
DROP TABLE
ETL/SQL/AAViTable.sql: No such file or directory
DROP TABLE
SELECT 539
Performing ETL
Loading person
DROP SEQUENCE
CREATE SEQUENCE
psql:ETL/SQL/insert_person.sql:56: ERROR:  relation "patients" does not exist
LINE 51:  from patients p
               ^
Loading observation_period
DROP SEQUENCE
CREATE SEQUENCE
psql:ETL/SQL/insert_observation_period.sql:24: ERROR:  relation "encounters" does not exist
LINE 18:   join encounters e
                ^
Loading visit_occurrence
psql:ETL/SQL/insert_visit_occurrence.sql:54: ERROR:  relation "all_visits" does not exist
LINE 48: from all_visits av
              ^
Loading observation
DROP SEQUENCE
CREATE SEQUENCE
psql:ETL/SQL/insert_observation.sql:96: ERROR:  relation "allergies" does not exist
LINE 46: from allergies a
              ^
Loading condition_occurrence
DROP SEQUENCE
CREATE SEQUENCE
psql:ETL/SQL/insert_condition_occurrence.sql:56: ERROR:  relation "conditions" does not exist
LINE 44: from conditions c
              ^
Loading procedure_occurrence
DROP SEQUENCE
CREATE SEQUENCE
psql:ETL/SQL/insert_procedure_occurrence.sql:50: ERROR:  relation "procedures" does not exist
LINE 38: from procedures pr
              ^
Loading measurement
DROP SEQUENCE
CREATE SEQUENCE
psql:ETL/SQL/insert_measurement.sql:102: ERROR:  relation "procedures" does not exist
LINE 50: from procedures pr
              ^
Loading drug exposure
DROP SEQUENCE
CREATE SEQUENCE
psql:ETL/SQL/insert_drug_exposure.sql:154: ERROR:  relation "conditions" does not exist
LINE 56: from conditions c
              ^
Loading condition_era
DROP SEQUENCE
CREATE SEQUENCE
INSERT 0 0
Loading drug_era
DROP SEQUENCE
CREATE SEQUENCE
INSERT 0 0

[AnthonyMolinaro]

@turbomam

Hi Mark, you were right, there was a typo in AAVITable.sql. This has been corrected and merged into Master.

Thanks!