Last hint, this is MAC instructions, but may also apply to linux:
@Chris_Knoll
You are right, R is not picking up the correct Java version. Let me follow the instructions to make R chose the correct Java version.
I will keep you posted. Thanks for your help.
Quick update from my end:
- executed the command: sudo R CMD javareconf
and found that R is picking up the latest Java installed i.e.:
> library(rJava)
> .jinit()
> .jcall(âjava/lang/Systemâ, âSâ, âgetPropertyâ, âjava.runtime.versionâ)
[1] â11.0.2+9-LTSâ
>
When I ran Achilles after that, it is running with out any errors. I ran it in Single Thread mode and it is still running (for more than 2 hrs now).
Few queries I have:
-
My understanding is that: The Data Sources in Atlas will be populated (I can see the drop down list in âData Sources -> Select a Sourceâ), once the Achilles run is completed. Right?
-
The Configuration in Atlas looks ok. Can you please confirm if it looks ok.
-
In the Jobs section, I always see that Warming up for the results is always failing. I am not sure if that is an issue.
a) Can I manually initiate the warming up, or is it always done automatically.
b) Will this cause an issue in the Data sources getting populated in Atlas? -
How can I force stop the Achilles run, so that I can rerun it in MultiThread mode (in case it takes too long in Single thread mode).
The dropdown in Data Sources is populated based on the values in source and source_daimon, regardless of if you ran Achillse. You will get an error when trying to access a report if you did not run Achilles.
It looks like you have 2 sources, one with a CDM schema, one with a Results schema You should have your cdm schema and results schema attached to the same source (ie: both daimons have the same source_id.
Thatâs a problem you should investigate. Check your logs. Probably related to the lack of Achilles results,since warming the cache reads from the Achilles results tables (for record counts in vocabulary). It automatically starts up when WebAPI is started (ie: not when you refresh your browser (that is Atlas), but when you start the WebAPI service. It will not have an impact on the Data Sources in Atlas, since the dropdown is populated by the values in the Source/SourceDaimon table and if you try to access a report that doesnât have achilles tasbles, it will just give you an error loading the report.
Safest way is to kill the execution at the database level. That will signal back to R that the statement was terminated and it will gracefully end the Achilles process. if you try to close the R process, it may still leave the execution hanging on the oracle side.
It is very important that your tables are properly indexed when doing the achilles analysis. It is touching every record and aggregating by concepts, so having an index by conceptId on any table with a _concept_id column would be helpful. For other queries, indexing on person_id is helpful. I do not have a lot of oracle performance experience, so I canât give you specific guidance there. You may need to discuss with your local DBA.
Thanks a lot for your responses. They are very helpful.
For the Results schema -
- All the tables in this schema will be empty before we run Achilles, right?
- When we run Achilles for the second time (or any subsequent run), do we need to truncate all the tables in the results schema? i mean should all the tables in results schema be empty before each run of Achilles?
Only the tables that start with achilles_ are used by Achilles. Iâm not sure if you run the process again, it truncates those tables (@Ajit_Londhe, can you confirm?). The other tables in the results schema are used by Atlas/WebAPI analysis executions (cohort generations, etc). You can ignore those tables, they are not used by Achilles.
If you use the createTables parameter, the Achilles tables will be dropped and recreated. If you donât, then all analyses you specify will be deleted from the Achilles tables and rerun.
When I ran Achilles now, (this happened in the first run also), the Achilles_Analysis table got created and the 197 records are present in the same. I am attaching the file here. Other than this table, no other Achilles Tables got created.
- What are the tables that actually have the data visible from Atlas (like the number of persons with various demographics etc).
- Is there a log file that I can see to know what actually is going on and if there are any errors when the Achilles R packages are running?
One note though: We have our CDM schema names as c##results and c##cdm. If you think the â##â might be causing an issue, then we can wait. I am in the process of getting the names changed and I will run achilles after that.achilles_analysis_197recs_21pr19.pdf (69.1 KB)
The ## in schema names will result in problems for any of our OHDSI tools which leverage the sql-translation function to translate sql into the target dialect.
Hi @Ajit_Londhe, I have a CDM in Oracle and was able to test this oraclefixes branch. The good news is that Achilles ran for all analyses until the AchillesHeel component. The log of the run is below. The specific error triggered is âORA-00905: missing keywordâ. I pasted the triggering query from the error report below. I think the issue is related to the line of the query that has âINNER JOIN First Achilles run Sentinelpro2019.concept c1â â notice that âFirst Achilles runâ looks suspiciously unlike a table to join with. Can you please check if there is a possible problem in the Achilles code?
ERROR REPORT
DBMS:
oracle
Error:
java.sql.SQLSyntaxErrorException: ORA-00905: missing keyword
SQL:
--HINT DISTRIBUTE_ON_KEY(analysis_id)
CREATE TABLE SENTINELPRO2019.kgb821als_tmpheel_rule_10
AS
SELECT
analysis_id,
ACHILLES_HEEL_warning,
rule_id,
record_count
FROM
(SELECT or1.analysis_id,
CAST(CONCAT('ERROR: ', CONCAT(cast(or1.analysis_id as VARCHAR(10)), CONCAT('-', CONCAT(oa1.analysis_name, CONCAT('; ', CONCAT(cast(COUNT(DISTINCT stratum_1) AS VARCHAR(19)), ' concepts in data are not in correct vocabulary')))))) AS VARCHAR(255)) AS ACHILLES_HEEL_warning,
10 as rule_id,
COUNT(DISTINCT stratum_1) as record_count
FROM SENTINELPRO2019.achilles_results or1
INNER JOIN SENTINELPRO2019.ACHILLES_analysis oa1
ON or1.analysis_id = oa1.analysis_id
INNER JOIN First Achilles run Sentinelpro2019.concept c1
ON or1.stratum_1 = CAST(c1.concept_id AS VARCHAR(19))
WHERE or1.analysis_id IN (202)
AND or1.stratum_1 IS NOT NULL
AND c1.concept_id <> 0
AND lower(c1.domain_id) NOT IN ('visit')
GROUP BY or1.analysis_id,
oa1.analysis_name
) A
R version:
R version 3.6.0 (2019-04-26)
Platform:
x86_64-w64-mingw32
LOG:
Analysis 413 (Number of condition occurrence records with invalid visit_id) -- START
| | | 0% | |=============================================================================================================================================| 100%
Executing SQL took 0.0312 secs
[Main Analysis] [COMPLETE] 413 (0.046803 secs)
Analysis 420 (Number of condition occurrence records by condition occurrence start month) -- START
| | | 0% | |=============================================================================================================================================| 100%
...
Executing SQL took 0.0312 secs
[Main Analysis] [COMPLETE] 2200 (0.046801 secs)
Analysis 2201 (Number of note records, by note_type_concept_id) -- START
| | | 0% | |=============================================================================================================================================| 100%
Executing SQL took 0.0624 secs
[Main Analysis] [COMPLETE] 2201 (0.062401 secs)
Warning: Parameter 'oracleTempSchema' not found in SQL
Warning: Parameter 'oracleTempSchema' not found in SQL
Warning: Parameter 'oracleTempSchema' not found in SQL
....
Warning: Parameter 'oracleTempSchema' not found in SQL
Warning: Parameter 'oracleTempSchema' not found in SQL
Merging scratch Achilles tables
| | | 0% | |====================================================================== | 50% | |=============================================================================================================================================| 100%
Executing SQL took 0.952 secs
| | | 0% | |====================================================================== | 50% | |=============================================================================================================================================| 100%
Executing SQL took 0.359 secs
Done. Achilles results can now be found in schema SENTINELPRO2019
Connecting using Oracle driver
- using THIN to connect
| | | 0% | | | 0% | |================== | 12% | |=================================== | 25% | |===================================================== | 38% | |====================================================================== | 50% | |======================================================================================== | 62% | |========================================================================================================== | 75% | |=========================================================================================================================== | 88% | |=============================================================================================================================================| 100%
Executing SQL took 1.05 secs
Beginning single-threaded operations
Beginning single-threaded execution
Beginning single-threaded execution
Connecting using Oracle driver
- using THIN to connect
Executing Achilles Heel. This could take a while
Executing Achilles Heel. This could take a while
| | | 0% | |=============================================================================================================================================| 100%
Executing SQL took 0.0936 secs
| | | 0%Error: Error executing SQL:
java.sql.SQLSyntaxErrorException: ORA-00905: missing keyword
An error report has been created at ...
>
@rkboyce
I have couple of queries for he situation where CDM is on Oracle (we have the same setup):
- Where I can see the log file after the Achilles is run.
- My understanding is that two additional tables are created after Achilles is run. But I could always see only one table ACHILLES_ANALYSES and it had 197 records for me.
Could you please let me know, what you have see on your environment.
Thanks and Regards
Sarath Manikonda
Hi Ajit, Chris,
I could run Achilles finally after renaming the schemas to CDM and RESULTS respectively.
Below is the command I have used after creating the connection in R:
achilles(connectionDetails,
cdmDatabaseSchema=âcdmâ,
resultsDatabaseSchema=âresultsâ,
scratchDatabaseSchema = âresultsâ,
vocabDatabaseSchema=âcdmâ,
numThreads=10,
sourceName=âTruven CDMâ,
cdmVersion=â5.3.0â,
runHeel=TRUE,
createTable=TRUE,
runCostAnalysis=FALSE)
However I am getting different errors this time:
Warning: Parameter âoracleTempSchemaâ not found in SQL â This warning is appearing a bunch of times (more than 100 i think)
Could you please let me know if I am missing something. Or what exactly is the issue.
@Ajit_Londhe,
I tried to run Achilles in Single Thread mode and that is also giving me issues. Any help in resolving this issue is much appreciated.
When I see the error report, i see that the Results table is not getting created. I used this command in R:
achilles(connectionDetails,
cdmDatabaseSchema=âcdmâ,
resultsDatabaseSchema=âresultsâ,
vocabDatabaseSchema=âcdmâ,
numThreads=1,
sourceName=âTruven CDMâ,
cdmVersion=â5.3.0â,
runHeel=TRUE,
createTable=TRUE,
runCostAnalysis=FALSE)
All â just back from vacation. Iâll review the oraclefixes branch and get back to you early next week. Thanks!
Hi @smanikonda - I see Achilles_Analysis with 197 records, Achilles_Results with 95K records, and Achilles_Results_Dist with ~23K records. Is that what you were asking for?
Hi Ajit,
I have created a smaller set of data in Oracle and ran Achilles with the following parameters. However it is stuck for more than 4 hrs. Please see the screenshot. While waiting for an update from you, I wanted to run with a small set of data with Oracle as source and then with PSQL as a source. Will keep you posted on my observations.
library(Achilles)
connectionDetails â createConnectionDetails(
dbms=âoracleâ,
server=âserver/sidâ,
user=âresults_testâ,
password=âpwdâ,
schema=âresults_testâ,
port=â1521â)
achilles(connectionDetails,
cdmDatabaseSchema=âcdm_testâ,
resultsDatabaseSchema=âresults_testâ,
vocabDatabaseSchema=âcdm_testâ,
numThreads=1,
sourceName=âTruv Test CDMâ,
cdmVersion=â5.3.0â,
runHeel=TRUE,
createTable=TRUE,
runCostAnalysis=FALSE)
Hi @smanikonda,
If indexes were not already created for the CDM, you will want those before running Achilles. It helps to tremendously speed things up.
Dave
2 Likes
Hi @Dave.Barman
I created all the indexes, but did not create the constraints yet. Do you think I should run the constraints also before I run Achilles?