Presto: Implement PrestoStatement#cancel for the JDBC driver

[sd4324530]

I use presto-jdbc, and want to kill the query job

[cawallin]

The Presto UI (available at the coordinator's ip or hostname/port) has a "kill query" button. Also, see the section "Killing a running query" here: https://prestodb.io/docs/current/connector/system.html.

I'm going to close this issue, but let us know if you have any other questions.

[electrum]

For JDBC, you can call the cancel() method on Statement. This can be called from a different thread since executing a query is usually a blocking operation.

[sd4324530]

@electrum Class PrestoStatement's method cancel():

    @Override
    public void cancel()
            throws SQLException
    {
        throw new SQLFeatureNotSupportedException("cancel");
    }

[sd4324530]

@cawallin I hope I can control it by code, not click the button

[electrum]

@sd4324530 Ah, you're right, cancel() is not implemented (but would be easy to do so -- feel free to send a Pull Request). There are two ways for JDBC:

1. Interrupt the thread that is executing the query (i.e call the interrupt() method on Thread)
2. Execute a query: CALL system.runtime.kill_query('id')

You can get the query ID a few ways:

1. Getting the query ID from the ResultSet using resultSet.unwrap(PrestoResultSet.class).getQueryId()
2. Using a progress monitor on Statement using statement.unwrap(PrestoStatement.class).setProgressMonitor(...)
3. By querying the system.runtime.queries table

[cawallin]

@sd4324530 Also, the JDBC driver available here has cancel implemented: www.teradata.com/presto

[sd4324530]

@electrum I find this in source code:

killQuery: function() {
        $.ajax({url: 'v1/query/' + this.state.query.queryId, type: 'DELETE'});
    },

hahaha

[arbfranklin]

@cawallin I couldn't find the completed block in the teradata branch either?

@electrum Is it possible the fix might be as simple as calling close()?

@Override
public void cancel()
        throws SQLException
{
    checkOpen();
    currentResult.get().close();
}

It ends up issuing a DELETE, and seems to mirror the presto-cli's CTRL-C behaviour.

[cawallin]

@arbfranklin the Teradata JDBC driver is closed-source but free; the code isn't the code in presto-jdbc.

[arbfranklin]

@cawallin With that in mind, can we re-open this ticket such that a fix can be proposed & integrated?

[cawallin]

@arbfranklin We can definitely re-open this ticket with regards to fixing the open source JDBC driver

[signed0]

Would it be possible to get someone to review the pull request?

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.

[mhlaskar1991]

Is there any way to execute CALL system.runtime.kill_query('id') from application code using JDBC? I tried but faced the following exception :
Exception in thread "main" java.sql.SQLException: SQL statement is not a query: CALL
system.runtime.kill_query('id')
at io.prestosql.jdbc.PrestoStatement.executeQuery(PrestoStatement.java:77)
at App.main(App.java:19)

[findepi]

@mhlaskar1991 you're using the PrestSQL driver
PrestSQL releases are maintained at https://github.com/prestosql/presto.
Also, there is the #troubleshooting channel on Presto Community Slack (https://prestosql.io/slack.html)