integrating non-blocking snowflake queries with mutiny

[simbo1905]

Hi folks,

Snowflake can do async queries as per jdbc-asynchronous-query. This looks something like:

// async some large ELT work on the cloud data warehouse
final var resultSet = statement.unwrap(SnowflakeStatement.class).executeAsyncQuery(sql_command);

// we can check the status of the async query
QueryStatus queryStatus = resultSet.unwrap(SnowflakeResultSet.class).getStatus();

// if queryStatus  is either QueryStatus.RUNNING or QueryStatus.RESUMING_WAREHOUSE we need to wait.

// if queryStatus is failed we need to fail else complete need to emit the ELT query result/rowcount for more processing 

My question is: How can I convert such a non-blocking "check job status" API into a mutiny Uni or Multi so that I can author async ELT processing pipelines using mutiny Uni/Multi pipelines?

Thanks!

[jponge]

I'm not familiar with this library, but a quick look at the library suggests that what they call "asynchronous" is based on polling, which will be imperative. It would have been simpler for you if, say, the API exposed CompletionStage because we have direct adapters for them.

You could use something like https://smallrye.io/smallrye-mutiny/2.5.1/guides/integrate-a-non-reactive-source/ to expose a Uni/Multi.

Another similar option is to use an emitter (see https://smallrye.io/smallrye-mutiny/2.5.1/tutorials/creating-uni-pipelines/#creating-unis-using-an-emitter-advanced and https://smallrye.io/smallrye-mutiny/2.5.1/tutorials/creating-multi-pipelines/#creating-multis-using-an-emitter-advanced and have another thread do the polling work, and eventually posting events using the emitter. The idea is that the emitter lambda is called upon subscription, and this is where you would make the query and spin the polling thread.

[simbo1905]

Thanks for that. I suspect you are correct that what they term "async" is indeed polling the task on the cloud warehouse to see if it is completed. Thank you for the links and the excellent framework.