Issues with WebAPI-Daimons and results schema using different schemas

Implementers
1

Hi:

We are currently evaluating/implementing OHDSI at our institution in Germany. While installing, configuring and loading our data (schema: demo), I ran into following problem with WebAPI (schema: webapi), Achilles (schema: demo_results), Atlas:

Case 1:
WebAPI uses 1 source and 3 daimons (0 and 1 point to demo and 2 to demo_results. We can search concepts and see the number of hits in our data. However, the cohort_analysis cannot be seen due to an SQL-Error: results.cohort (or other tables) does not exist. Of course, this is in webapi.*

Case 2:
WebAPI uses 1 source and 3 daimons (0 and 1 point to demo and 2 to webapi. We can see the cohort_analysis now. However, the concept_search in the vocabulary fails, because webapi.achilles_results. Of course, this is in demo_results.*

So either way fails?! Putting achilles_results into webapi.* cannot be the solution, because the results depend on possibly various CDM-schemas.

I read various links in the forum and looked at the tools including theirs issues at github (e.g. https://github.com/OHDSI/WebAPI/wiki/Multiple-datasets-configuration) but perhaps I miss something?

Many thanks,
Martin

2

Hi @leenad,
You are correct that if you have an environment where the results schema is not going to be stored int he same schema as the webapi’s schema, then you’re going to have to manually create the tables in a results schema that is in the same database as your CDM.

The reason the default behavior for WebAPI is to create the results tables in the webAPI schema is that for simple setup cases where people only have 1 CDM, they can just deploy WebAPI and configure the SOURCE and SOURCE_DAIMON tables to point to the webAPI schema and not have to worry about multiple result schemas. But for multiple CDMs, the results tables will have to be created manually. @Frank and @Ajit_Londhe have the latest list of tables that need to be manually defined.

For Case 2 above: that should have worked for you, if you have 0 and 1 (the CDM and Vocab) and 2 (the results) is set to webapi. What you need to do is run Achilles specifying the results schema as ‘webAPI’ (or just copy the tables out of demo_results into webapi). I think most dbms have a sql command that lets you rename a table to a different schema. Not sure what your db platform is tho. but I think if you move the Achilles tables (and only the achilles tables, webAPI maintains the DDL of all the other tables you see in webapi so make sure you are careful what you move) you should be able to pull back the results.

3

@Vojtech_Huser:
I read the above link to the multiple datasets configuration:

I think there’s a problem with the way you describe the schemas:

For example, a read-only schema with CDM data (e.g., person table, measurement table) may be in schema dataA. To allow (for example for Achilles) to write to result tables (e.g., achilles_results table), a corresponding schema (with write privileges) would be data_A_results.

The use of snake_case here I find a little confusing, but I think the point you are making is that if you have a CDM in schema ‘dataA’ and it is read-only, you can create a new schema ‘dataA_results’ that is writable, and when you execute Achilles, specify dataA as the cdm_database_schema and dataA_results as the results_database_schema.

Bold part above is the important part. Could you update the wiki page with this information? This would also be a good place to put which tables (and maybe the DDL?) that need to be created so that it’s documented and others can benefit from this thread.

4

Dear Chris:

many thanks for your reply that makes things much clearer.
For me, I would prefer to have CDM, vocab and Achilles’ results in one schema and the Webapi - which possibly provides access to several CDMs/results - distinct. So I will manually create/migrate the necessary tables from webapi to cdm and have Achilles generating its (his?) tables in the same schema. So everything dependent on a specific cdm stays together (area of concern).
In our case, I have downloaded the demo dataset from your ftp server for demonstration and (test)import several datasets from our hospital/projects. So we will have several cdms around - at least for now.
Best,
Martin
PS: We are using postgres (and I am the administrator knowing my tools, so every setup is possible :slight_smile: )
PPS: Case 2 had not worked, because I had Achilles generate the results in a cdm-specific schema, trying to keep the webapi-schema clean from anything cdm-dependent.

5

Hopefully you are getting closer to a working configuration. As more sites come online that have multiple sources this issue is becoming more prominent. The good news is that an upcoming build of WebAPI will include the ability to automatically install the proper tables into the proper schema to support this type of deployment. It is still at least a month or two away from general availability but I will post once it is available and would be very interested in getting feedback on how it works in your environment.

6

It seems, that it is working now :smile: using following layout/schemas:

  • cdm1 (daimon 0,1) with data, cdm1results (daimon3)
  • cdm2 (daimon 0,1) with data, cdm2results (daimon3)
  • webapi
    I copied all table definitions (cohort, feas_, heracles_ etc.) from webapi to each of the results schema.
    Now I can browse the vocabulary in ATLAS, define concept sets, generate cohort reports in both sources and also the cohort/reporting tab reveals nice graphics after generating the views.
    I am looking forward to any new release and will happily help testing and improving.
    BTW: We will also look into operating a second webapi installation simulating yet another hospital with distinct data (yes, we mean it serious :smile: )
1 Like
7

Ok, one note on multiple webAPI installations. Do not point 2 different WebAPIs to the same results schemas. Each WebAPI maintains its own set of identifiers (cocnept set IDs, cohort def ids, etc) so if you have different WebAPIs writing to the same result schema, you’ll have result #1 from WebAPI A colliding with result #1 from WebAPI B (#1 in WebAPI is a different thing than #1 in WebAPI B).

-Chris

1 Like
8

I am attempting the same thing.

I have a working configuration of the type:
data schema = data1_v5,
results schema = webapi

and want to add another data CDM
data_schema = data2_v5

Before I read/comprehended @leenad above description, I tried a couple of different ways to configure ( multiple WebAPIs being the main failed method ).

I settled on trying to duplicate Martin’s configuration. I wanted to let flyaway create the results schemas, so I ran WebAPI once configured with each data schema and let it create the different results schemas.

So I ended up with this configuration of schemas:
data1_v5 with results1_v5
data2_v5 with results2_v5
webapi

I configured the webapi.source with a source for each data schema ( even though they were on the same DB )
source_id, source_name, source_key, source_connection, source_dialect
1, CDM, data1_v5, jdbc:xxxx, postgresql
2, CDM, data2_v5, jdbc:xxxx, postgresql

and the source_daimon for both:
source_daimon_id, source_id, daimon_type, table_qualifier, priority
1, 1, 0, data1_v5, 0
2, 1, 1, data1_v5, 0
3, 1, 2, results1_v5, 0
4, 2, 0, data2_v5, 0
5, 2, 1, data2_v5, 0
6, 2, 3, results2_v5, 0

With this configuration, I ran into issues with connecting to the DB. There were no tomcat errors during startup, but I got the “could not read resultSet” error when I tried to retrieve WebAPI/source/sources.

I could not get past this. When I renamed the results1_v5 schema back to webapi, it started working again for the one CDM version of WebAPI.

So I don’t know if there is something in the results1_v5 schema ( the original results of a working one CDM system ) that allows the system to work and that was not created by flyaway. The only tables missing are the achilles ones, but I haven’t compared data other than source/source_daimon.

I am continuing to work on it, but any ideas would be appreciated and worth a beer ( or 2 ) in DC.

Thanks,
Richard

9

PS. I know it sounds like one, but it shouldn’t be a permissions issue. By the end, pretty much anyone could read any table!

10

I think I have it working. I just took it apart, rebuilt it and it started working. Sorry about the free beer tease.

11

Something I found useful was to have the Web API write out it’s log files.
I was able to see the what was being sent to the database.

See attached log4j.xml file for changes. File is in src/main/resources.
Not sure if you can just replace this file. I replaced the file in the
source then rebuild the war and re-deployed, but that might be overkill.

12

I would love that log4j file. I was messing with it and the JULI config to try and get more verbosity.
Thanks!

13

Richard, email me at dtorok@ephir.com and I will send you the file. I don’t know how to add an attachment to the forum reply and if I paste the file in the reply the XML in the log file is getting intermingled with the markup symbols.

14

Good idea. For the future if you just restart the new logging properties will apply.

Get Outlook for Androidhttps://aka.ms/ghei36

15

Upload in the lower right corner of the reply screen should allow you to upload XML file.

16

Upload says it is limited to the following file extensions (jpg, jpeg, png, gif, .ico, .xls, .xlsx).

17

Ah! Let me look into that further then since what you are trying to do, attach or share some structured data in a post while preserving layout, is, of course, quite reasonable and useful.

18

@DTorok @t_abdul_basser

I’ve now added XML as a valid file option for forum file uploads.

1 Like
19

Great. Thanks @admin!

20

Changes to WebAPI log4j.xml file to save a file rather than write to the console.log4j.xml (1.3 KB)