Redshift / Java:SQL执行挂起,永不返回

时间:2022-02-28 23:05:24

The application that im working on runs a sequence of queries on AWS Redshift. Some of the queries take longer to execute due to the data volume.

我正在处理的应用程序在AWS Redshift上运行一系列查询。由于数据量的原因,某些查询需要更长的时间才能执行。

The queries seem to finish on Redshift when i check the execution details on the server. However, the java application seems to hang indefinitely without throwing any exception or even terminating.

当我检查服务器上的执行细节时,查询似乎在Redshift上完成。但是,java应用程序似乎无限期挂起而不会抛出任何异常甚至终止。

Here's the code that executes the query.

这是执行查询的代码。

private void execSQLStrings(String[] queries, String dataset, String dbType) throws Exception {
    Connection conn = null;

    if (dbType.equals("redshift")) {
        conn=getRedshiftConnection();
    } else if (dbType.equals("rds")){
        conn=getMySQLConnection();
    }

    Statement stmt=conn.createStatement();

    String qry=null;
    debug("Query Length: " + queries.length);
    for (int ii=0;ii<queries.length;++ii) {
        qry=queries[ii];

        if (dataset != null) {
            qry=qry.replaceAll("DATASET",dataset);
        }

        debug(qry);
        stmt.execute(qry);
    }
    stmt.close();
    conn.close();
}

I cant post the query that im running at the moment but it has multiple table joins and group by conditions and its an 800m row table. The query takes about 7~8 mins to complete on the server.

我不能发布我正在运行的查询,但它有多个表连接和按条件分组,它有一个800米的行表。在服务器上完成查询大约需要7~8分钟。

1 个解决方案

#1


1  

You need to update the DSN Timeout and/ or KeepAlive settings to make sure that your connections stay alive. Refer: http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html

您需要更新DSN超时和/或KeepAlive设置,以确保您的连接保持活动状态。参考:http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html

#1


1  

You need to update the DSN Timeout and/ or KeepAlive settings to make sure that your connections stay alive. Refer: http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html

您需要更新DSN超时和/或KeepAlive设置,以确保您的连接保持活动状态。参考:http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html