Save Symptoms as Obs from external DB into openmrs db(Resolved)

Development
1

Hi @dkayiwa, @ibacher, @mozzy , @ruhanga

I’m attempting to save observations (obs) in the OpenMRS database from an external database using the ETL approach. I’ve chosen not to use etl directly because it doesn’t provide me with full control over the data.

Instead, I’m considering using the openmrs-etl pipeline, although it seems like it might require more work than necessary.

Initially, patients are already loaded into OpenMRS from the same external database. The goal is to ensure that these observations and associated encounters are attached to the corresponding patients. I attempted to achieve this using getPatientById(existing patient id).

Here is my logic. I would appreciate your suggestions. Thank you.

2

Am looking to achieve exactly something like https://github.com/kimaina/openmrs-elt/blob/master/views/obs_with_encounter.sql. whats your suggestion ?

3

Hey @dkayiwa. @akimaina , Any easiest way of achieving this ?, Will my way be the quickest way to achieve, this or using already exisiting pipelines ?. Using my way makes the server hangs for several minutes which is quite annoying :wink:

4

Using the already existing pipelines would be the ideal!

1 Like
5

Okay! Great , Let me leverage using this GitHub - kimaina/openmrs-etl: openmrs - mysql - debezium - kafka - spark - scala Wondering why its still under @kimaina not openmrs repo specifically ?

6

We do not discourage having repositories that are not under the openmrs organisation. Here is another example: GitHub - mekomsolutions/openmrs-module-initializer: The OpenMRS Initializer module is an API-only module that processes the content of the configuration folder when it is found inside OpenMRS' application data directory.

1 Like
8

So, a couple of notes:

  1. I don’t think that project is actively maintained (though, I assume you’re welcome to fork it).
  2. The goal of that project was primarily extracting data from OpenMRS for analytics pipelines. Your use-case is the exact opposite of that.
  3. The right solution for this is probably openmrs-eip, (or, more specifically, the combination of Apache Camel and ActiveMQ; the openmrs-watcher is only useful if you’re reacting to things happening in the OpenMRS database) which is intended to capture live updates and propagate them elsewhere. dbsync is an openmrs-eip application that writes to an OpenMRS database.
1 Like
9

Good catch! , this it true basing on your explanation.

Thanks @ibacher , My goal is to extract , transform and load data load into openmrs db as long as i have access to the external db. In this case, am entitled to db-view which lives in a certain db that holds data to be loaded into openmrs db. Let me try it out. thanks.

10

@ibacher , dbsync works only for two openmrs databases which is quite different than my scenario,Unless there is a way for customizing it to adhere other dbs, Am only getting data from external Microsoft sql database which is not openmrs specific. Will this work for me ?

11

@sharif No, it won’t work out of the box, because dbsync was built to sync two OpenMRS instances. The point of sharing dbsync is that it’s built on top of openmrs-eip and writes to an OpenMRS database. You can’t use it directly, but it might be a helpful example of some of the code (particularly the parts that rebuild the search index).

We don’t really have anything in the ecosystem other than etllite that I’m aware of that’s purpose-built for extracting data from an external db and importing into an OpenMRS instance.

1 Like
12

Thanks @ibacher , Well, Do you mind looking into my logic as stated above which is intended to achieve same thing, I added dammy code that ideally should work,.though it’s quite not security authentic for now because we have to expose db credentials which might not be appropriate , I would like to get your opinion before I switch to etlite. Thank you

2 Likes
13

Hey @ibacher @dkayiwa , i resorted to using etl velocity template function, However am encountering issues to deal with saving obs, especially when am populating patient object, as seen here

#set($patientClass = "org.openmrs.Patient")
#set($personNameClass = "org.openmrs.PersonName")
#set($encounterClass = "org.openmrs.Encounter")
#set($patientIdentifierClass = "org.openmrs.PatientIdentifier")
#set($personAttributeClass = "org.openmrs.PersonAttribute")
#set($calendarClass = $util.loadClass("java.util.Calendar"))
#set($personAddressClass = "org.openmrs.PersonAddress")
#set($personPhoneAttributeTypeName = "Telephone Number")
#set($personLanguageAttributeTypeName = "personLanguage")
#set($patientIdentifierTypeClass = "org.openmrs.PatientIdentifierType")
#set($locationClass = "org.openmrs.Location") 
#set($encounterProviderClass = "org.openmrs.EncounterProvider") 
#set($encounterType = "org.openmrs.EncounterType") 
#set($obs = "org.openmrs.Obs") 

#foreach($row in $rows)

    #*Encounter *#
    #set($encounter = $util.newObject($encounterClass)) 
    // generate new encouter id

    #* patient *#
    #set($patient = $util.newObject($patientClass))
    //create all patient details
    #set($patientId = $row.patientId)
    //$ patient id from the db
    #set($patient.patientId = $patientId)
    #set($log = $logFactory.getLog($patientId))
    //setting patient by  patient id
    $encounter.setPatient($patient)

    #*Calendar*#
    #set($calendar = $util.newObject($calendarClass))
    #set($encounter.setEncounterDateTime= $row.visitDate)

    #* location *#
    #set($location = $util.newObject("org.openmrs.Location"))
    #set($locationUuid = "3b7617c9-778f-4f48-83e7-5514eb6ed946")
    #set($encounter.setLocation = $locationService.getLocationByUuid($locationUuid))

    #* encounter type *#
    #set($encounterType = $util.newObject("org.openmrs.EncounterType"))
    #set($encounterTypeUuid = "43c3630f-abfe-4fe1-8c92-b73b65199a3d")
    #set($encounter.encounterType = $encounterService.getEncounterTypeByUuid($encounterTypeUuid))
   
    // use set instead of arrayList
    #set($obsList = [])

    #set($obsDiarrhoea = $util.newObject("org.openmrs.Obs"))
    $obsDiarrhoea.concept = $conceptService.getConceptByName("Diarrhoea")
    $obsDiarrhoea.valueCoded = $conceptService.getConceptByName($row.Diarrhea)
    $obsList.add($obsDiarrhoea)

    #set($obsParaparesis = $util.newObject("org.openmrs.Obs"))
    $obsParaparesis.concept = $conceptService.getConceptByName("Paraparesis")
    $obsParaparesis.valueCoded = $conceptService.getConceptByName($row.Paraparesis)
    $obsList.add($obsParaparesis)

    #set($obsDiabetesMellitus = $util.newObject("org.openmrs.Obs"))
    $obsDiabetesMellitus.concept = $conceptService.getConceptByName("Diabetes Mellitus")
    $obsDiabetesMellitus.valueCoded = $conceptService.getConceptByName($row.DiabetesMellitus)
    $obsList.add($obsDiabetesMellitus)

    #set($obsRash = $util.newObject("org.openmrs.Obs"))
    $obsRash.concept = $conceptService.getConceptByName("Rash")
    $obsRash.valueCoded = $conceptService.getConceptByName($row.Rash)
    $obsList.add($obsRash)

    $encounter.setObs($obsList)
        
    $encounterService.saveEncounter($encounter)
    
    #if($encounterService == $null)
        #log("Encounter Service is null")
    #end
#end

Not sure what could i be missing in the template cc @druchniewicz

14

Hello @sharif what happens when you run ‘Test’ action on the ETL mapping? Do you see a raw result from DB (the result of SQL Query)? Do you see a table, titled Transformed, which shows a result of Transform Velocity script? Did you try to write Load script which just creates an encounter with date and patient? Do you see any exceptions in the log?

Regarding the script:

  1. Are you sure that the external DB shares the same ID as OpenMRS Patient table id to reference Patients? It doesn’t look right. Did you mean UUID or maybe OpenMRS PatientIdentifier?
  2. I do not see $logFactory defined anywhere in the script. Is it injected service?
  3. You create new Location object, then a line later you read an existing Location and never use the created one - some leftovers here.
  4. #set($encounter.setLocation = value) doesn’t look like a correct Property reference.
  5. I’m not sure what’s an intention behind the creation of Obs. Usually, the Obs is simply an answer to question. In your case, for obsDiarrhoea question is a Concept named Diarrhoea and an answer is a Concept with UUID from $row.Diarrhea. I imagine, it can work when Diarrhoea is Boolean type concept and the $row.Diarrhea contains OpenMRS UUID of Yes or No concept UUIDs.

Finally, I would just connect with debugger and see if saveEncounter is called and what happens.

1 Like
15

Hi @pwargulak , the query returns data into the table,

This query here SELECT TOP 2 * FROM Tama_symptom WHERE PatientID IN (SELECT PatientId FROM Tama_NewPatientInfo); returns symptoms for patients who are already existing in OpenMRS , So in this case am setting patient by its id from row.patientId

Sorry, i have an updated velocity template here

16

Hi @dkayiwa @ibacher @pwargulak . this is still a blocker using etl velocity function template..

17

Hello @sharif

Please check the logs - there must be at least some information about what’s going on.

When you create log factory, I suggest to provide a string (category name) instead passing a variable patientId - #set($log = $logFactory.getLog(‘org.openmrs.module.api.etllite’)). Then, create the log as soon as possible in the script, and start writing into it as soon as possible, e.g.: $log.info(“Symptoms ETL Start”), $log.info("Found patient for id: " + $patientId), … This should show you more information where the script fails. If you don’t see logs, check log.level GP.

There is only Obs.setValueCoded(Concept) so that CONCEPT_UUIDS_FOR_PREDICTION_VALUE will not work.

1 Like
18

Thanks @dkayiwa @ibacher @pwargulak . i resolved this. Since the fix looks resuable/generic. you can refer to this velocity template script.