OHDSI Home | Forums | Wiki | Github

Are "Schemas" in Achilles really databases when using SQL Server


(Kai Post) #1

Hi there,
I am a little confused by the nomenclature. I am trying to get Achilles docker to work and I am using the following in env_vars:

ACHILLES_CDM_SCHEMA=shs
ACHILLES_VOCAB_SCHEMA=shs
ACHILLES_RES_SCHEMA=shs_achilles

Both schemas exist within the same SQL Server database. However, when I run the docker image I get the following error messages, indicating that they may have to be separate databases instead? If this is the case I can do this, but then why is it trying to write to the CDM schema/database? I thought it would be creating tables in the RES schema/database? I gave the user write privileges to RES, but only read on CDM.

Connecting using SQL Server driver
Warning: Cohort table not found, will skip analyses 1700 and 1701
Beginning single-threaded execution
Connecting using SQL Server driver
|=================================== | 50%Error: Error executing SQL:
com.microsoft.sqlserver.jdbc.SQLServerException: CREATE TABLE permission denied in database ‘shs’.
An error report has been created at /opt/app/errorReport.txt
Warning: folder DEFAULT/DEFAULT/2020-02-26T19.06.26 already exists
Connecting using SQL Server driver
Error in rJava::.jcall(statement, “V”, “close”) :
com.microsoft.sqlserver.jdbc.SQLServerException: Database ‘shs_achilles’ does not exist. Make sure that the name is entered correctly.


(Dave Barman) #2

Hi Kai,

Yes, you may need to add the dbo user infront of the the ‘schema’ name to get this to work too.

I can help you debug this though, I went through it not too long ago, though I did not use the Docker.

-Dave


(Ajit Londhe) #3

Hi @kaiwpost,

Schema names refer to the fully qualified schema name. In SQL Server, this means using database.schema (e.g. cdm.dbo). Achilles will only write to the results schema.

Thanks,
Ajit


(Dave Barman) #4

Something to note, Kai, be sure your CDM is in the ‘default’ collation. ACHILLES is expecting a case-insensitive database, and from my experience, doing a conversion normally requires case-sensitivity to be enabled at some point during the ETL.

In order to get around this without having to rewrite ACHILLES scripts, I copied the final CDM database into a new MS-SQL database and set it to default collation (in-sensitive) and directed Achilles to run against this. Works like a charm.


(Kai Post) #5

Thank you so much everyone for your help. Using fully qualified schema names indeed did the trick. However, now I’ve come to another hurdle. The script seems to be looking for a table in RES that has not been created yet. Any thoughts?

Executing SQL took 0.0687 secs
[Main Analysis] [COMPLETE] 2200 (0.071940 secs)
Analysis 2201 (Number of note records, by note_type_concept_id) – START
|======================================================================| 100%
Executing SQL took 0.0728 secs
[Main Analysis] [COMPLETE] 2201 (0.076313 secs)
Merging scratch Achilles tables
|=================================== | 50%Error: Error executing SQL:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘#s_tmpach_11’.
An error report has been created at /opt/app/errorReport.txt
Warning: folder DEFAULT/DEFAULT/2020-02-26T21.43.10 already exists
Connecting using SQL Server driver
Generating condition treemap
| | 0%Error: Error executing SQL:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘shs.shs_achilles.achilles_results’.


(Ajit Londhe) #6

Looks like 1 of the analyses failed. Do you have an errorReport.txt file that shows the error?


(Ajit Londhe) #7

Actually – what does the log file say for analysis 11?


(Kai Post) #8

I think I don’t have my volume mounted correctly, so I can’t see the errorReport.txt, but I was able to see the following problem in the log file for analysis 11:

ERROR Error: Error executing SQL: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘shs.shs.death’.

I assume this is a CDM V6 issue?


(Ajit Londhe) #9

Correct – because you’re on V6 you’re seeing this error. I did just push a new branch that supports V6, but it is not ready for primetime yet. If you want to try it and provide feedback in the git repo (which is greatly appreciated), you can install it using:

devtools::install_github("OHDSI/Achilles", ref = "cdm_v6_2020")


(Kai Post) #10

Wonderful! I will give this a try. Thanks for your help.


(Ajit Londhe) #11

Sure. Run it with runCostAnalysis = FALSE, as those are not ready yet.


(Kai Post) #12

The log file does not show any more analysis problems with your CDM6 code! I am still left with the same error message in the end, though. I will have to work out mounting the output volume so I can find the errorReport.txt file.

Thanks for getting me a step closer! I will provide feedback once I have all of this figured out.

2020-02-26 23:40:39 [Main thread] FATAL Achilles FUN Error in .mergeAchillesScratchTables(resultsTable = table, connectionDetails = connectionDetails, : unused argument (cdmVersionFolder = cdmVersionFolder) Calls: achilles -> lapply -> FUN
2020-02-26 23:40:40 [Main thread] FATAL DatabaseConnector .createErrorReport Error: Error executing SQL: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘shs_oklahoma.shs_oklahoma_achilles.achilles_results’. An error report has been created at /opt/app/errorReport.txt

t