Error running Achilles against Databricks

venkyvb · March 22, 2023, 4:09am

I am trying to run Achilles against CDM in Databricks using RStudio. I am seeing an error -

2023-03-22 00:00:07	Beginning single-threaded execution
Connecting using Spark JDBC driver
  |=================================================================================| 100%
Executing SQL took 4.42 secs
Error in rJava::.jcall("java/lang/System", "S", "getProperty", "java.version") : 
  java.sql.SQLException: [Simba][JDBC](10040) Cannot use commit while Connection is in auto-commit mode.

In the JDBC connection string I am setting the following parameters (based on information from other threads) - UseNativeQuery=1;IgnoreTransactions=1. (I also tried without the IgnoreTransactions=1, I am getting the same error).

Any hints here would be helpful.

Ajit_Londhe · March 22, 2023, 1:04am

This is a known issue with DatabaseConnector::insertTable (Achilles makes use of this to insert static analysis information).

The Databricks Working Group is looking into this, but so far, the autocommit capability needed isn’t looking promising.

venkyvb · March 22, 2023, 4:14am

Thanks for the clarification. Just wanted to check if there any workarounds that can be used to run Achilles against CDM in Databricks using Spark JDBC ?

venkyvb · March 22, 2023, 5:24pm

As a workaround I ran Achilles with sqlOnly = TRUE, via Rstudio, which generated an achilles.sql file. I ran the generated achilles.sql against Databricks. The script ran fine, and I can see tables like achilles_results, achilles_results_dist, with data populated, but looks like the ACHILLES_analysis table is not created as a part of the SQL script (not sure if there are other tables missing as well).

Consequently, when I try to browse the data source dashboards in Atlas this fails as the ACHILLES_analysis table is missing.

Are there any other scripts that need to be run to make sure all the Achilles tables are created in the results schema ?

Ajit_Londhe · March 22, 2023, 6:13pm

You can simply take the achilles_analysis CSV file from here and create it as a table

github.com

OHDSI/Achilles/blob/main/inst/csv/achilles/achilles_analysis_details.csv

ANALYSIS_ID,DISTRIBUTION,DISTRIBUTED_FIELD,ANALYSIS_NAME,STRATUM_1_NAME,STRATUM_2_NAME,STRATUM_3_NAME,STRATUM_4_NAME,STRATUM_5_NAME,IS_DEFAULT,CATEGORY
0,-1,,Source name,,,,,,1,General
1,0,,Number of persons,,,,,,1,Person
2,0,,Number of persons by gender,gender_concept_id,,,,,1,Person
3,0,,Number of persons by year of birth,year_of_birth,,,,,1,Person
4,0,,Number of persons by race,race_concept_id,,,,,1,Person
5,0,,Number of persons by ethnicity,ethnicity_concept_id,,,,,1,Person
7,0,,Number of persons with invalid provider_id,,,,,,1,Person
8,0,,Number of persons with invalid location_id,,,,,,1,Person
9,0,,Number of persons with invalid care_site_id,,,,,,1,Person
10,0,,Number of all persons by year of birth by gender,year_of_birth,gender_concept_id,,,,1,Person
11,0,,Number of non-deceased persons by year of birth by gender,year_of_birth,gender_concept_id,,,,1,Person
12,0,,Number of persons by race and ethnicity,race_concept_id,ethnicity_concept_id,,,,1,Person
101,0,,"Number of persons by age, with age at first observation period",age,,,,,1,Person
102,0,,"Number of persons by gender by age, with age at first observation period",gender_concept_id,age,,,,1,Person
103,1,,Distribution of age at first observation period,,,,,,1,Observation Period
104,1,,Distribution of age at first observation period by gender,gender_concept_id,,,,,1,Observation Period
105,1,,Length of observation (days) of first observation period,,,,,,1,Observation Period
106,1,,Length of observation (days) of first observation period by gender,gender_concept_id,,,,,1,Observation Period
107,1,,Length of observation (days) of first observation period by age decile,age decile,,,,,1,Observation Period

This file has been truncated. show original

venkyvb · March 22, 2023, 7:03pm

Thanks @Ajit_Londhe ! This did the trick. Now I am able to view the Atlas data source dashboards.