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

kaiwpost · February 26, 2020, 7:25pm

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 · February 26, 2020, 7:51pm

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 · February 26, 2020, 8:01pm

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 · February 26, 2020, 8:20pm

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.

kaiwpost · February 26, 2020, 9:57pm

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 · February 26, 2020, 10:04pm

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

Ajit_Londhe · February 26, 2020, 10:19pm

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

kaiwpost · February 26, 2020, 10:59pm

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 · February 26, 2020, 11:06pm

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")

kaiwpost · February 26, 2020, 11:07pm

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

Ajit_Londhe · February 26, 2020, 11:14pm

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

kaiwpost · February 26, 2020, 11:44pm

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