Add Support for Trino Query Engine as CDM database store.

[shorrocka]

We have deployed Trino trino.io as our query engine for our CDM data it has been incredible performant for us. Query times for complex queries have been improved as much as 50x. We are hoping to add support to Atlas in order to run it with that as the backing system in order to speed up Atlas performance.

There is a jdbc driver that can be used to send queries to the system.

I am wondering what steps would be need in order to add this as a possible CDM store? I have started to modify some of the other OHDSI packages, SqlRender, DatabaseConnector and Achilles to run against Trino as a first step and have been successful.

Any help or guidance would be greatly appreciated before I start diving into the code base.

Let me know if there is anything I can provide that can be helpful in terms of trino context.

Best,
Alex

[corleyma]

@shorrocka Any progress with this since you posted? We would also be really interested in using Trino as a backend for Atlas and with other OHDSI tools.

[shorrocka]

Hey @corleyma yeah I made pretty great progress got to about 90% of the way there I think, then kinda hit a wall.

Trino doesn't support batch queries in the jdbc driver ie. sending a bunch of queries separated by semicolons. I couldn't find in the code stack where it can set them to send individually, I know it exists as some of the other supported CDM backends also don't allow batch queries, BiqQuery and Imapala if I remember correctly.

I had to put the project down for as we had some other priorities pop up but I am hoping to take another shot at in the next couple weeks. At least get the forks of the repos be available if you wanted to take a crack at it as well.

Happy to know someone else is using Trino with OMOP it's been a game changer for us.

[shorrocka]

Okay so here is where things stand in more detail.

I have forked the dependencies that I see are needed just for the WebAPI to work. This includes the SqlRender package, and the ArachneCommons package, I also have the achilles and database-conector packages but will include those elsewhere.
https://github.com/PEDSnet/SqlRenderTrino
https://github.com/PEDSnet/ArachneCommonsTrino

Lastly here is the forked version of the actual WebAPI:
https://github.com/PEDSnet/WebAPITrino

The changes there are not a lot I've tried to mostly edit the edges of things but think I need to get a bit more in depth with it. The snag comes from the way the queries get sent to the trino server using JDBC. It sends them as a "statement" and the driver needs them to be sent as a "PreparedStatment" I confirmed this with the trino development team.

Here is the entire error I see when generating a cohort:
ERROR taskExecutor-1 org.springframework.batch.core.step.AbstractStep - [] - Encountered an error executing step cohortDefinition.generateCohort in job generateCohort org.ohdsi.webapi.exception.AtlasException: org.springframework.dao.InvalidDataAccessApiUsageException: StatementCallback; SQL [CREATE TABLE Codesets (codeset_id int NOT NULL, concept_id bigint NOT NULL ) ]; Batches not supported; nested exception is java.sql.SQLFeatureNotSupportedException: Batches not supported at org.ohdsi.webapi.common.generation.TransactionalTasklet.execute(TransactionalTasklet.java:42) ~[classes/:?] at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406) ~[spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330) ~[spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE] at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:272) ~[spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81) ~[spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374) ~[spring-batch-infrastructure-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144) ~[spring-batch-infrastructure-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257) ~[spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200) [spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) [spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:392) [spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:135) [spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306) [spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE] at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135) [spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_442] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_442] at java.lang.Thread.run(Thread.java:750) [?:1.8.0_442] Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: StatementCallback; SQL [CREATE TABLE Codesets (codeset_id int NOT NULL, concept_id bigint NOT NULL ) ]; Batches not supported; nested exception is java.sql.SQLFeatureNotSupportedException: Batches not supported at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:94) ~[spring-jdbc-4.3.25.RELEASE.jar:4.3.25.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.25.RELEASE.jar:4.3.25.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) ~[spring-jdbc-4.3.25.RELEASE.jar:4.3.25.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:418) ~[spring-jdbc-4.3.25.RELEASE.jar:4.3.25.RELEASE] at org.ohdsi.webapi.util.CancelableJdbcTemplate.batchUpdate(CancelableJdbcTemplate.java:111) ~[classes/:?] at org.ohdsi.webapi.generationcache.GenerationCacheHelper.runCancelableCohortGeneration(GenerationCacheHelper.java:82) ~[classes/:?] at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.lambda$prepareQueries$0(GenerateCohortTasklet.java:86) ~[classes/:?] at org.ohdsi.webapi.generationcache.GenerationCacheHelper.lambda$computeCacheIfAbsent$1(GenerationCacheHelper.java:64) ~[classes/:?] at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE] at org.ohdsi.webapi.generationcache.GenerationCacheHelper.computeCacheIfAbsent(GenerationCacheHelper.java:51) ~[classes/:?] at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.prepareQueries(GenerateCohortTasklet.java:82) ~[classes/:?] at org.ohdsi.webapi.common.generation.CancelableTasklet.doTask(CancelableTasklet.java:33) ~[classes/:?] at org.ohdsi.webapi.common.generation.CancelableTasklet.doTask(CancelableTasklet.java:17) ~[classes/:?] at org.ohdsi.webapi.common.generation.TransactionalTasklet.lambda$execute$0(TransactionalTasklet.java:37) ~[classes/:?] at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE] at org.ohdsi.webapi.common.generation.TransactionalTasklet.execute(TransactionalTasklet.java:37) ~[classes/:?] ... 18 more Caused by: java.sql.SQLFeatureNotSupportedException: Batches not supported at io.trino.jdbc.TrinoStatement.addBatch(TrinoStatement.java:399) ~[trino-jdbc-469.jar:469] at org.ohdsi.webapi.util.CancelableJdbcTemplate$1BatchUpdateStatementCallback.doInStatement(CancelableJdbcTemplate.java:56) ~[classes/:?] at org.ohdsi.webapi.util.CancelableJdbcTemplate$1BatchUpdateStatementCallback.doInStatement(CancelableJdbcTemplate.java:45) ~[classes/:?] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407) ~[spring-jdbc-4.3.25.RELEASE.jar:4.3.25.RELEASE] at org.ohdsi.webapi.util.CancelableJdbcTemplate.batchUpdate(CancelableJdbcTemplate.java:111) ~[classes/:?] at org.ohdsi.webapi.generationcache.GenerationCacheHelper.runCancelableCohortGeneration(GenerationCacheHelper.java:82) ~[classes/:?] at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.lambda$prepareQueries$0(GenerateCohortTasklet.java:86) ~[classes/:?] at org.ohdsi.webapi.generationcache.GenerationCacheHelper.lambda$computeCacheIfAbsent$1(GenerationCacheHelper.java:64) ~[classes/:?] at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE] at org.ohdsi.webapi.generationcache.GenerationCacheHelper.computeCacheIfAbsent(GenerationCacheHelper.java:51) ~[classes/:?] at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.prepareQueries(GenerateCohortTasklet.java:82) ~[classes/:?] at org.ohdsi.webapi.common.generation.CancelableTasklet.doTask(CancelableTasklet.java:33) ~[classes/:?] at org.ohdsi.webapi.common.generation.CancelableTasklet.doTask(CancelableTasklet.java:17) ~[classes/:?] at org.ohdsi.webapi.common.generation.TransactionalTasklet.lambda$execute$0(TransactionalTasklet.java:37) ~[classes/:?] at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE] at org.ohdsi.webapi.common.generation.TransactionalTasklet.execute(TransactionalTasklet.java:37) ~[classes/:?] ... 18 more

Looking into the CancelableJdbcTemplate file I can see there is logic for creating the PreparedStatment and it does seem like the default for most dbms systems, but I may just be missing how to implement it for trino. I tried to add it as connection that does not support batch updates similar to impala but that ends in the same error message.

Please let me know if I am looking in the right place or if there is other logic that I am missing.

[corleyma]

I see your changes to SQLRender fork, but I think your forks of Arachne and WebAPI don't include your changes?

[chrisknoll]

I think in some hades tools the execute sql will perform a split sql on the statements, and then create the individual sql statements that appear in a batch. I'm surprised WebAPI doesn't do this as well since cancelable statement attempts to allow cancelation of long running queries by checking a cancel flag between each statement being executed (ie: it doesn't send an Out-of-band command to the server to cancel some task by process ID, but instead between steps of queries it will check if the user requested cancellation).

So, the work here would be how to split statements and parameterize each individual SQL before executing.

[shorrocka]

Got it so it most likely involve creating its own method to handle that kind of splitting.

Would using a prepared statement based on the rendered sql work as well? This may be able to get around needing to split up the query.

Would this want to be implemented within the SQLRender package similar to the BigQuery/Spark setups? Or just directly within the WebAPI?

Thanks in advance for the guidance.

[chrisknoll]

Split sql already exists in SqlRender which is used in WebAPI. so it would just be a matter of splitting sql using that function in the appropriate places. I seem to recall that it does do this in most cases using a PreparedStatementRenderer but I could be remembering wrong.