person_count not present in ohdsi_results.achilles_result_concept_count in 2.13.0

billsanto commented 1 year ago

Perhaps either person_count and descendant_person_count need to be added to Achilles or removed from WebAPI. WebAPI throws an error because these fields are not generated by Achilles. Is an Achilles update is imminent?

Expected behavior

No error in log

Actual behavior

broadsea-webtools2 | 2023-03-30 09:34:40.274 INFO taskExecutor-3 org.springframework.batch.core.job.SimpleStepHandler - [] - Executing step: [warming cache: OHDSI-CDMV5-OMOP_DEID-B1 counts] broadsea-webtools2 | 2023-03-30 09:34:47.006 ERROR taskExecutor-3 org.ohdsi.webapi.cdmresults.service.CDMCacheService - [] - Failed to warm cache OHDSI-CDMV5-OMOP_DEID-B1. Exception: PreparedStatementCallback; bad SQL grammar [select cc.concept_id, record_count, descendant_record_count, person_count, descendant_person_count broadsea-webtools2 | from ohdsi_results.achilles_result_concept_count cc broadsea-webtools2 | join omop.concept c on cc.concept_id = c.concept_id broadsea-webtools2 | WHERE cc.concept_id >= ? and cc.concept_id <= ?]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'person_count'.

Steps to reproduce behavior

Update Atlas/WebAPI to 2.13.0 Run Achilles 1.7 with the optimizeAtlasCache = TRUE option to generate the achilles_result_concept_count table: achilles(connectionDetails, cdmDatabaseSchema = "OMOP_DEID.omop", resultsDatabaseSchema = "OMOP_DEID.ohdsi_results", scratchDatabaseSchema = "OMOP_DEID.ohdsi_temp", numThreads = 1, outputFolder = "./achilles_output2", cdmVersion = "5.3.1", optimizeAtlasCache = TRUE, sqlOnly = TRUE )

generates the following at the tail end of the achilles.sql:

IF OBJECT_ID('OMOP_DEID.ohdsi_results.achilles_result_concept_count', 'U') IS NOT NULL drop table OMOP_DEID.ohdsi_results.achilles_result_concept_count;


WITH concepts AS (
SELECT
CAST(ancestor_concept_id AS VARCHAR)   ancestor_id,
CAST(descendant_concept_id AS VARCHAR) descendant_id
FROM OMOP_DEID.omop.concept_ancestor ca
UNION
SELECT
CAST(concept_id AS VARCHAR) ancestor_id,
CAST(concept_id AS VARCHAR) descendant_id
FROM OMOP_DEID.omop.concept c
), counts AS (
SELECT stratum_1 concept_id, MAX (count_value) agg_count_value
FROM OMOP_DEID.ohdsi_results.achilles_results
WHERE analysis_id IN (2, 4, 5, 201, 225, 301, 325, 401, 425, 501, 505, 525, 601, 625, 701, 725, 801, 825,
826, 827, 901, 1001, 1201, 1425, 1801, 1825, 1826, 1827, 2101, 2125, 2301)
GROUP BY stratum_1
UNION
SELECT stratum_2 AS concept_id, SUM (count_value) AS agg_count_value
FROM OMOP_DEID.ohdsi_results.achilles_results
WHERE analysis_id IN (405, 605, 705, 805, 807, 1805, 1807, 2105)
GROUP BY stratum_2
)

select concept_id, record_count, descendant_record_count into OMOP_DEID.ohdsi_results.achilles_result_concept_count from ( SELECT concepts.ancestor_id concept_id, isnull(max(c1.agg_count_value), 0) record_count, isnull(sum(c2.agg_count_value), 0) descendant_record_count FROM concepts LEFT JOIN counts c1 ON concepts.ancestor_id = c1.concept_id LEFT JOIN counts c2 ON concepts.descendant_id = c2.concept_id GROUP BY concepts.ancestor_id ) Q ;

chrisknoll commented 1 year ago

The achilles_result_concept_count table that is required for 2.13 is not the same as the table produced from Achilles when optimizeAtlasCache=TRUE. This was a missed detail when the record count caching was implemented in Atlas in 2.13...and this is something that should probably be removed from Achilles (or achilles should provide the person_count fields).

The actual script you should run to initialize this table is here.

billsanto commented 1 year ago

Thanks for pointing to the script--I did get it to run after updating the variables.

OHDSI / WebAPI