Cohort Pathway error - ATLAS-2.8.0 with WebAPI 2.8.0

Chidam · January 8, 2021, 4:13am

Hi All,

In the latest version of ATLAS 2.8.0 with WebAPI 2.8.0. I am facing error in the cohort pathway.

can anyone please advise me what could be the error.

thanks
regards
Chidam

Chris_Knoll · January 8, 2021, 2:16pm

You’ll have to go into your WebAPI logs to find the underlying error, as it’s not clear what the core error was that led to the ‘bad sql exception’. You should be able to find sometthing like ‘invalid cast’ or ‘table does not exist’. But, without that information, we can’t tell.

Chidam · January 12, 2021, 1:49am

Thanks for your reply Chris.

Please see the error below, its extracted from the webapi log, SQL script refers temp table,
if any suggestions please let me know.

The log:

2021-01-11 12:28:23.623 DEBUG http-nio-8080-exec-2 org.hibernate.event.internal.AbstractFlushingEventListener - - Flushed: 0 (re)creations, 0 updates, 0 removals to 1 collections

2021-01-11 12:28:23.623 DEBUG http-nio-8080-exec-2 org.hibernate.internal.util.EntityPrinter - - Listing entities:

2021-01-11 12:28:23.623 DEBUG http-nio-8080-exec-2 org.hibernate.internal.util.EntityPrinter - - org.ohdsi.webapi.pathway.domain.PathwayAnalysisGenerationEntity{startTime=2021-01-11 12:27:44.35, id=27, endTime=2021-01-11 12:28:03.26, source=org.ohdsi.webapi.source.Source#1, exitMessage=org.ohdsi.webapi.exception.AtlasException: java.lang.RuntimeException: java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [select event_cohort_index, subject_id, CAST(cohort_start_date AS DATETIME) AS cohort_start_date, CAST(cohort_end_date AS DATETIME) AS cohort_end_date

INTO #event_cohort_eras

FROM (

            SELECT ec.cohort_index AS event_cohort_index,

              e.subject_id,

              e.cohort_start_date,

              dateadd(d, 1, e.cohort_end_date) as cohort_end_date

            FROM temp.temp_cohort_g1yzxu65 e

              JOIN ( SELECT 32 AS cohort_definition_id, 1 AS cohort_index UNION ALL SELECT 31 AS cohort_definition_id, 0 AS cohort_index ) ec ON e.cohort_definition_id = ec.cohort_definition_id

              JOIN temp.temp_cohort_g1yzxu65 t ON t.cohort_start_date &lt;= e.cohort_start_date AND e.cohort_start_date &lt;= t.cohort_end_date AND t.subject_id = e.subject_id

            WHERE t.cohort_definition_id = 30

) RE]; SQL state [null]; error code [0]; Failed to execute batch update; nested exception is java.sql.SQLException: Failed to execute batch update

            at org.ohdsi.webapi.common.generation.TransactionalTasklet.execute(TransactionalTasklet.java:42)

            at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406)

            at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330)

            at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)

            at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:272)

            at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81)

            at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374)

            at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)

            at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)

            at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257)

            at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200)

            at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148)

            at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:392)

            at org.springframework.batch.core.job.SimpleJob.doExecute(Simple, pathwayAnalysis=&lt;uninitialized&gt;, info=component[createdBy,hashCode]{createdBy=org.ohdsi.webapi.shiro.Entities.UserEntity#1000, hashCode=-96867646}, status=FAILED}

2021-01-11 12:28:23.624 DEBUG http-nio-8080-exec-2 org.hibernate.internal.util.EntityPrinter - - org.ohdsi.webapi.source.SourceDaimon{tableQualifier=results, daimonType=Results, sourceDaimonId=3, source=org.ohdsi.webapi.source.Source#1, priority=0}

2021-01-11 12:28:23.624 DEBUG http-nio-8080-exec-2 org.hibernate.internal.util.EntityPrinter - - org.ohdsi.webapi.source.SourceDaimon{tableQualifier=dbo, daimonType=Vocabulary, sourceDaimonId=2, source=org.ohdsi.webapi.source.Source#1, priority=0}

2021-01-11 12:28:23.624 DEBUG http-nio-8080-exec-2 org.hibernate.internal.util.EntityPrinter - - org.ohdsi.webapi.source.SourceDaimon{tableQualifier=temp, daimonType=Temp, sourceDaimonId=4, source=org.ohdsi.webapi.source.Source#1, priority=0}

2021-01-11 12:28:23.624 DEBUG http-nio-8080-exec-2 org.hibernate.internal.util.EntityPrinter - - org.ohdsi.webapi.source.Source{sourceId=1, sourceKey=DM-2015_to_2018, daimons=[org.ohdsi.webapi.source.SourceDaimon#1, org.ohdsi.webapi.source.SourceDaimon#2, org.ohdsi.webapi.source.SourceDaimon#3, org.ohdsi.webapi.source.SourceDaimon#4], sourceDialect=sql server, keyfileName=null, password=xxxxxxxxxxxxxx, createdDate=null, createdBy=null, deletedDate=null, krbAdminServer=null, keyfile=null, krbAuthMethod=PASSWORD, modifiedDate=null, modifiedBy=null, sourceConnection=}

2021-01-11 12:28:23.624 DEBUG http-nio-8080-exec-2 org.hibernate.internal.util.EntityPrinter - - org.ohdsi.webapi.pathway.domain.PathwayAnalysisGenerationEntity{startTime=2021-01-08 15:59:04.307, id=26, endTime=2021-01-08 15:59:09.67, source=org.ohdsi.webapi.source.Source#1, exitMessage=org.ohdsi.webapi.exception.AtlasException: java.lang.RuntimeException: java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [select event_cohort_index, subject_id, CAST(cohort_start_date AS DATETIME) AS cohort_start_date, CAST(cohort_end_date AS DATETIME) AS cohort_end_date

INTO #event_cohort_eras

FROM (

            SELECT ec.cohort_index AS event_cohort_index,

              e.subject_id,

              e.cohort_start_date,

              dateadd(d, 1, e.cohort_end_date) as cohort_end_date

            FROM temp.temp_cohort_nqm7q5du e

              JOIN ( SELECT 32 AS cohort_definition_id, 1 AS cohort_index UNION ALL SELECT 31 AS cohort_definition_id, 0 AS cohort_index ) ec ON e.cohort_definition_id = ec.cohort_definition_id

              JOIN temp.temp_cohort_nqm7q5du t ON t.cohort_start_date &lt;= e.cohort_start_date AND e.cohort_start_date &lt;= t.cohort_end_date AND t.subject_id = e.subject_id

            WHERE t.cohort_definition_id = 30

) RE]; SQL state [null]; error code [0]; Failed to execute batch update; nested exception is java.sql.SQLException: Failed to execute batch update

            at org.ohdsi.webapi.common.generation.TransactionalTasklet.execute(TransactionalTasklet.java:42)

            at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406)

            at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330)

            at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)

            at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:272)

            at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81)

            at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374)

            at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)

            at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)

            at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257)

            at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200)

            at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148)

            at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:392)

            at org.springframework.batch.core.job.SimpleJob.doExecute(Simple, pathwayAnalysis=&lt;uninitialized&gt;, info=component[createdBy,hashCode]{createdBy=org.ohdsi.webapi.shiro.Entities.UserEntity#1000, hashCode=-96867646}, status=FAILED}

2021-01-11 12:28:23.624 DEBUG http-nio-8080-exec-2 org.hibernate.internal.util.EntityPrinter - - org.ohdsi.webapi.source.SourceDaimon{tableQualifier=dbo, daimonType=CDM, sourceDaimonId=1, source=org.ohdsi.webapi.source.Source#1, priority=0}

2021-01-11 12:28:23.626 DEBUG http-nio-8080-exec-2 org.hibernate.engine.transaction.internal.TransactionImpl - - On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false

2021-01-11 12:28:23.626 DEBUG http-nio-8080-exec-2 org.hibernate.engine.transaction.internal.TransactionImpl - - begin

2021-01-11 12:28:23.649 DEBUG http-nio-8080-exec-2 org.hibernate.query.criteria.internal.CriteriaQueryImpl - - Rendered criteria query -> select generatedAlias0 from UserEntity as generatedAlias0 where generatedAlias0.login=:param0

2021-01-11 12:28:23.649 DEBUG http-nio-8080-exec-2 org.hibernate.SQL - - select userentity0_.ID as ID1_52_, userentity0_.last_viewed_notifications_time as last_vie2_52_, userentity0_.LOGIN as LOGIN3_52_, userentity0_.NAME as NAME4_52_ from dbo.SEC_USER userentity0_ where userentity0_.LOGIN=?

2021-01-11 12:28:23.651 DEBUG http-nio-8080-exec-2 org.hibernate.loader.Loader - - Result set row: 0

Chris_Knoll · January 11, 2021, 4:35pm

I still can’t see from this log message what the underlying problem is, but it does give you the query that failed to execute…if you want to try to create a temp cohort table in your temp schema:

create table @temp_database_schema.@target_table
(
  COHORT_DEFINITION_ID int NOT NULL,
  SUBJECT_ID bigint NOT NULL,
  cohort_start_date date NOT NULL,
  cohort_end_date date NOT NULL
);

(where you replace the temp_databaes_schema and target table placeholders with the correct values).

Then run the SQL and see if it causes any issues:

select event_cohort_index, subject_id, CAST(cohort_start_date AS DATETIME) AS cohort_start_date, CAST(cohort_end_date AS DATETIME) AS cohort_end_date

INTO #event_cohort_eras

FROM (

            SELECT ec.cohort_index AS event_cohort_index,

              e.subject_id,

              e.cohort_start_date,

              dateadd(d, 1, e.cohort_end_date) as cohort_end_date

            FROM temp.temp_cohort_g1yzxu65 e

              JOIN ( SELECT 32 AS cohort_definition_id, 1 AS cohort_index UNION ALL SELECT 31 AS cohort_definition_id, 0 AS cohort_index ) ec ON e.cohort_definition_id = ec.cohort_definition_id

              JOIN temp.temp_cohort_g1yzxu65 t ON t.cohort_start_date &lt;= e.cohort_start_date AND e.cohort_start_date &lt;= t.cohort_end_date AND t.subject_id = e.subject_id

            WHERE t.cohort_definition_id = 30
) RE

Note, before executing this, you should populate the temporary cohort table with data from your own results schema so that you actually execute with data. It looks like you want to insert the records for cohort IDs: 30, 31 and 32 into that temp table. Then that query will run to create a sub-set of the cohort records for just the cohorts in your analysis.

Additionally (you don’t have to paste more logs here), you may want to scan through your logs and see if something else stands out…look for ‘table does not exist’ or ‘invalid cast’ or some type of error that may explain why the batch sql failed.

Chidam · January 12, 2021, 5:43am

Thanks Chris,

Can you give the details steps how to populate the temporary cohort table?

"Note, before executing this, you should populate the temporary cohort table with data from your own results schema so that you actually execute with data. It looks like you want to insert the records for cohort IDs: 30, 31 and 32 into that temp table. Then that query will run to create a sub-set of the cohort records for just the cohorts in your analysis."

Can give more steps … to run this query in my environment. Thanks for your help

thanks
regards
Chidam

Chris_Knoll · January 12, 2021, 2:44pm

Just create the table, and then INSERT INTO {your table} SELECT (cohort_definition_id, subject_id, cohort_start_date, cohort_end_date from {your cohort table}.

Chidam · January 13, 2021, 7:54am

Hi Chris,

Sorry to ask for more details, i am not very familiar with temp tables. Can give the script to create the table? where I need to create in the CDM result schema?

Just create the table, and then INSERT INTO {your table} SELECT (cohort_definition_id, subject_id, cohort_start_date, cohort_end_date from {your cohort table}.

{my cohort table} - cohort table exist in both ohdsi and cdm both databases, But my cohort table is empty.

please advise.

thanks
regards
Chidam

Chidam · January 13, 2021, 8:15am

Hi Chris,

When i try the same in the older version 2.7.6, i am getting
Adding a value to a ‘date’ column caused an overflow.;

Please advise me, where i need to change to solve this date column overflow.

the log is as follows:

2021-01-13 14:27:29.953 DEBUG http-nio-8080-exec-3 org.hibernate.internal.util.EntityPrinter - - org.ohdsi.webapi.pathway.domain.PathwayAnalysisGenerationEntity{startTime=2021-01-13 14:12:42.07, id=10914, endTime=2021-01-13 14:13:04.547, source=org.ohdsi.webapi.source.Source#1, exitMessage=org.ohdsi.webapi.exception.AtlasException: java.lang.RuntimeException: java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [select id, event_cohort_index, subject_id, CAST(cohort_start_date AS DATETIME) AS cohort_start_date, CAST(cohort_end_date AS DATETIME) AS cohort_end_date
INTO #raw_events
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY e.cohort_start_date) AS id,
ec.cohort_index AS event_cohort_index,
e.subject_id,
e.cohort_start_date,
dateadd(d, 1, e.cohort_end_date) as cohort_end_date
FROM temp.temp_cohort_cenwhr1k e
JOIN ( SELECT 32 AS cohort_definition_id, 0 AS cohort_index UNION ALL SELECT 25 AS cohort_definition_id, 1 AS cohort_index UNION ALL SELECT 26 AS cohort_definition_id, 2 AS cohort_index UNION ALL SELECT 27 AS cohort_definition_id, 6 AS cohort_index UNION ALL SELECT 28 AS cohort_definition_id, 5 AS cohort_index UNION ALL SELECT 29 AS cohort_definition_id, 7 AS cohort_index UNION ALL SELECT 30 AS cohort_definition_id, 4 AS cohort_index UNION ALL SELECT 31 AS cohort_definition_id, 3 AS cohort_index ) ec ON e.cohort_definition_id = ec.cohort_definition_id
JOIN temp.temp_cohort_cenwhr1k t ON t.cohort_start_date <= e.cohort_start_date AND e.cohort_start_date <= t.cohort_end_date AND t.subject_id = e.subject_id
WHERE t.cohort_definition_id = 23
) RE]; SQL state [S0003]; error code [517]; Adding a value to a ‘date’ column caused an overflow.; nested exception is java.sql.BatchUpdateException: Adding a value to a ‘date’ column caused an overflow.
at org.ohdsi.webapi.common.generation.TransactionalTasklet.execute(TransactionalTasklet.java:42)

Konstantin_Yaroshove · January 13, 2021, 9:18am

It might be related to analysis design. Could you please check that cohorts used in this pathway analysis have no design errors and you are able to get counts on your data?

Chidam · January 13, 2021, 9:48am

Hi,

No design issue in the cohort. I can run and get the result counts in target and event cohorts in the cohorts Definition section. This issue in the pathway

thanks
regards
Chidam

Chris_Knoll · January 13, 2021, 11:57pm

Ok, so we’re closer: I can’t tell what would be ‘adding to a date column’ from the query, but that’s the core of the issue.

I think you said that you generated the cohorts, so the cohort table in your RESULTS schema should be populated with records. My guess is that you somehow specified the cohort_end date to be some kind of max date, and tehrefore this statement may lead to an overflow:

dateadd(d, 1, e.cohort_end_date) as cohort_end_date

If you do find which cohort table has records in it, can you please let me know what the MAX(cohort_end_date) is in that table?

Chidam · January 14, 2021, 7:43am

Hi Chris,

Thanks. Yes max date shows ‘9999-12-31’
very strange. How this big date stored in results.cohort table? what i need to next?

Thanks for your help.

regards
Chidam

Chris_Knoll · January 14, 2021, 3:49pm

it probably comes from your observation_period table. When you define a cohort that doesn’t have an end date, it will us the observation period end date of the person.