DDL scripts for results.achilles_results_derived etc

Developers
1

Hello,

I am able to get the DDL scripts for results schema using http::///WebAPI/ddl/results?dialect=postgresql

But, I am not able to find the ddl scripts for additional tables-

  1. achilles_results_derived
  2. achilles_analysis
  3. achilles_results
  4. achilles_results_dist
  5. achilles_heel_results

Please suggest where to locate the ddl scripts for the above mentioned tables.

Thank You

2

Hello @ambuj,

These tables are created automatically by Achilles when you run Achilles analysis. You can find more details in Achilles GitHub repository, and inside the “Running Achilles on Your CDM” document (PDF).

3

Hello @rookie_crewkie

I ran Achilles analysis using R, but at the end it throws error:
ERROR: relation “results.achilles_results_dist” does not exist.

Unsure at what step it create those tables.

4

@ambuj,

This is strange, normally it’s created under the hood during Achilles run. Quoting the PDF:

The Achilles package should be run sequentially. That is, achilles should be run first to generate the
achilles_results and achilles_results_dist tables, and then optionally, achillesHeel should be run next to
generate the achilles_heel_results and achilles_results_derived tables.

No further ideas, sorry. I’d try to rerun Achilles though, just in case.

5

@rookie_crewkie
I think the issue has been resolved. I had runHeel=T which is why it was running the achillesHeel along with achilles.
Now, I ran achilles first and it created tables for achilles_analysis and achilles_results.

Next, I executed achillesHeel; and got the error for results.achilles_results_dist.

6

I recently had the exact same issue. As a temporary solution I copied an existing achilles_results_dist table. This is not really a nice solution though.

@ambuj What solved the problem for you? Did you just rerun Achilles without Achilles Heel or did you do some other steps first?

7

@MaximMoinat
While running Achilles analysis in R using “achilles” function, set runHeel=F and execute. It will create achilles_results and achilles_analysis tables.

For achilles_results_derived and achilles_results_dist, it is said that it will be created while executing ‘achileesHeel’ function which didn’t happened with me too.

So, I did the same like you did of copying existing tables. Really can’t figure out what has been changes from ‘achillesHeel’ fucntion.

8

@ambuj – are you still having this issue? If so, can you please post an issue in the Git repo?

9

The derived table was created based on Patrick’s call for storing a result and making a rule based on that stored result.

Before paralelization, the sql was easier to find in the code.
here is it in version 1.5.1

10

Let’s focus on the errors in execution @ambuj is having. The DDL for these tables is not something anyone should be using manually. The Achilles package should be creating these tables. If the tables aren’t created, something went wrong.

11

Hi I’m not sure it is related to current to current topic, but I also do not have those tables (example achilles_results). I have only WebAPI and Atlas running with Postgres DB … And when I try to generate the cohort getting error that achilles_results is missing …

12

You need to run the Achilles R package on your data, see this link for more information: https://ohdsi.github.io/Achilles/.

The achilles function will generate summary statistics which are stored in achilles tables that sit in the results schema. The achillesHeel function will generate data quality checks based on those summary tables.

13

Just to be clear: WebAPI does not manage any CDM schemas (the standard CDM tables, nor the results schema). You must manually deploy those tables yourself for each CDM you want to deploy.

Achilles creates it’s own tables, on the specified schema. if you configure the Achilles process to write tables into the results schema of your CDM, then webapi (and Atlas by extension) will be able to read the Achilles results under the ‘DataSources’ section of Atlas.

Spread the word: WebAPI does not create schemas in your CDM, that is an activity related to your CDM ETL.

1 Like
14

Similar problem here.

We have Achilles creating and populating the achilles_xxx tables on the results schema on a local computer.

However, we would like to move these populated tables later to the webApi server which has not R installed.
(or at the moment just copy these from the synpuf5pct_20180710 data)

Shount’t it the SQL created by the webAPI’s URL

http://<server:port>/WebAPI/ddl/results?dialect=<your_cdm_database_dialect>&schema=<your_results_schema>&vocabSchema=<your_vocab_schema>&tempSchema=<your_temp_schema>&initConceptHierarchy=true

Also include something like

CREATE TABLE IF NOT EXISTS <your_results_schema>. achilles_resutls
 (analysis_id INT64 not null,
  analysis_name STRING not null,
   ...
15

The WebAPI server doesn’t need the CDM databases physically residing on it, it just needs logical access to the CDMs.

So the process flow would be:

  1. Run Achilles using any client with R set up that can access your CDM database (located on a database platform). Achilles, as a consequence, will create and populate the achilles tables. These tables are located in whichever schema in the CDM database you deem as the “results” schema. This can be in the same schema as your CDM tables, or be a separate schema. Once complete, your CDM database now has achilles tables and content stored within.
  2. When you are ready to add the CDM source to WebAPI/Atlas, run the DDL script from WebAPI to ensure all of the other dependency tables are available in the CDM database’s “results” schema.
  3. Add the CDM database connection information to WebAPI’s source and source daimon tables.
  4. WebAPI can then talk to the CDM database to execute analytic queries against the clinical tables, pull up summary level stats using the achilles tables, etc.
16

thanks @Ajit_Londhe,
I will explain my situation better.

  1. I have the CDM in a local airgaped computer on postgress.
  2. I ran the achilles on that CDM and got the achilles_* tables (for this exmaple achilles_results.cvs) .
  3. I extract all tables resutls, vocabularies and CDM encript them and sent the to the server admin wehre the CDM will be installed for users.
  4. On the server side they need to copy the received tables to the database BigQueri. For this they have previously created the schemas by running the the DDL script from WebAPI.

The problem is that, when they tried to copy the tables. The DDL script has not created a table for achilles_results and then they cannot load achilles_results.cvs into the BigQueri.

{{
Im not an expert in databases, correct my solution if wrong:
I think we should change the last steps as follow :
4. Import the .csv files into the BigQueri which will create the tables (is this possible, or will this make the wrong types on colums ?)
5. run the DDL script that will link the tables (will this be inefficient to run this on populated tables?)
}}

17

Ah I see now. So we would need a method to create the DDLs on your production platform. This isn’t handled by WebAPI, and Achilles doesn’t actually run any empty create table statements – it creates the tables using CREATE TABLE AS SELECT (CTAS) statements.

@Chris_Knoll – certainly I could add a function to write out Achilles create table scripts that @Javier could then run manually on his production platform, but I’m wondering if this is something that could be part of the DDL service in WebAPI; perhaps it can create the Achilles tables if they don’t already exist in the results schema. That way it won’t affect existing Achilles tables, but will create them if they’re not found.

18

Thanks a lot @Ajit_Londhe, that is exactly what I was asking, just better explain.

I hope @Chris_Knoll also agrees that DDL service in WebAPI should create the achilles tables if not exists.

19

I think the DDL for achilles belongs in achilles (so a function that outputs the ddl to a file or a function that returns the long string of the DDL would make sense)…but as far as WebAPI goes, i think it would be possible to have another endpoint under the DDL service for achilles (so, our results schema is ddl/results, achilles could be ddl/achilles). What I worries are:

  1. Anyone who wants to house their DDL for their own database schema for their own app can petntion webAPI to maintain it, which I worry about
  2. you’ll have two copies of the ddl (one in webapi and one in R package) unless you encapsulate the DDL generation into a stand-alone object (woudl be java) so that webAPI and Achilles can both refer to the same thing.
  3. You’ll have to take some steps to have achilles versions synchronized with webapi versions if you want to have the change in one reflected in the other. That can be a headache if there’s not alignment on the release schedule between the two.

I understand Ajit’s point that he doesn’t actually have DDL and he does his table creation from CTAS, but I think the point’s still stand that you’ll have the logic in two places and coordinating releases will be problematic.

For @Javier, isn’t there a data migration tool that you can leverage to pull data from one database out to another? Many database platforms support this.

1 Like