Getting visit_detail error when trying to run Synthea ETL R Script

[greshje]

I’m trying to run the Synthea ETL R Script using an MS Sql Server database.

Everything is working until it tries to insert into condition_occurrence. This gives the error shown below. The full script I’m running and errorReportSql.txt is attached.

Not sure how to debug this (I'm more of a Java person and have only basic understanding of R).

A couple of other things to note:

• The select query in the script executes and gives back id values for visit_detail_id and gives 7737 rows)
• “select * from synthea_etl.dbo.visit_detail” returns 0 rows

Thanks,
John

Running: insert_condition_occurrence.sql
| | 0%Error in .createErrorReport():
! Error executing SQL:
com.microsoft.sqlserver.jdbc.SQLServerEx
synthea-etl.r.txt
synthea-etl.r.txt
ception: The INSERT statement conflicted with the FOREIGN KEY constraint "fpk_CONDITION_OCCURRENCE_visit_detail_id". The conflict occurred in database "synthea_etl", table "dbo.VISIT_DETAIL", column 'visit_detail_id'.
An error report has been created at C:/Users/gresh/OneDrive/Documents/errorReportSql.txt
Run rlang::last_error() to see where the error occurred.

synthea-etl.r.txt
ouput.txt
errorReportSql.txt

[AnthonyMolinaro]

@greshje
Hi John,

Looks like the problem is caused by the fact that in loadEventTables() the condition_occurrence table is populated before the visit_detail table. When we incorporated the CommonDataModel package into the synthea etl for creating DDL, along with it came all the table constraints - which explains why no one ran into this earlier. I think the fix is just a simple matter of populating visit_detail before condition_occurrence - which is a quick fix I can apply now.

[AnthonyMolinaro]

@greshje
I just pushed the fix to Master. Please pull and let me know how it looks. If this quick fix doesn't do the trick, we'll need more time to investigate. Thanks.