February 03, 2020 /   prestodb   cancelling prestodb query   java   jdbc   spring jdbc

Use Case

Prestodb queries are not automatically cancelled when client has closed connection. They are cancelled only after this query.client.timeout has elapsed and default value is 5m.

Sometimes need arises when your current query become obsolete as user has request for more recent information. In that case you may end up many queries running on prestodb server but you don't want result for.

Prestodb Query

Every prestodb query has one unique id something like 20200203_110750_00151_hi5cj And every query is represented as a HTTP resources of the form http://prestohost.com/v1/query/20200203_110750_00151_hi5cj

Cancelling Prestodb Query

To cancel a query you need to call DELETE method on presto query resource of the form http://prestohost.com/v1/query/QUERY_ID

Only problem here is how to get query id programaticaly. Here's what we can do in Java, assuming we are using Spring JDBC module.

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;

import com.facebook.presto.jdbc.PrestoResultSet;

public class CustomRowMapperResultSetExtracter<T> extends RowMapperResultSetExtractor<T> {
    public CustomRowMapperResultSetExtracter(final RowMapper<T> rowMapper) {
        super(rowMapper);
    }
    @Override
    public List<T> extractData(final ResultSet rs) throws SQLException {
        final String queryId = ((PrestoResultSet) rs).getQueryId();
        // Save query id for future reference
                ...
                ...
        return super.extractData(rs);
    }
}


Then You can extend JdbcTemplate class as per your requirement. Something like this

public class CustomJdbcTemplate extends JdbcTemplate {

    public CustomJdbcTemplate() {
        super();
    }
    public CustomJdbcTemplate(final DataSource dataSource) {
        super(dataSource);
    }
    public <T> List<T> query(final String sql, final RowMapper<T> rowMapper) throws Exception {
        return query(sql, new CustomRowMapperResultSetExtracter<T>(rowMapper));
    }

}


And this is how you can delete or cancel an existing query

final URL url = new URL("http://HOST_NAME/v1/query/QUERY_ID");
final HttpURLConnection httpCon = (HttpURLConnection) url.openConnection();
httpCon.setDoOutput(true);
httpCon.setRequestMethod("DELETE");
httpCon.getOutputStream(); 

Related Posts

Password Authentication in PrestoDb