amazon-web-services - 插入时的 Redshift 异常

Question

使用 redshift jdbc 驱动程序：1.2.16.1027

我经常遇到异常，例如：

java.sql.SQLNonTransientException: [Amazon][JDBC](10900) Not all parameters have been populated.
    at com.amazon.exceptions.ExceptionConverter.toSQLException(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.jdbc.common.SPreparedStatement.executeAnyUpdate(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.jdbc.common.SPreparedStatement.executeUpdate(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.abc.dao.impl.AbcDaoImpl.storeAbc(AbcDaoImpl.java:80) ~[dao-1.0-SNAPSHOT.jar:?]

还

com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: column "b" is of type double precision but expression is of type timestamp without time zone;
    at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.redshift.client.PGMessagingContext.handleMessage(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.redshift.client.PGMessagingContext.getParameterDescription(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.redshift.client.PGClient.prepareStatement(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.redshift.dataengine.PGQueryExecutor.<init>(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.redshift.dataengine.PGDataEngine.prepare(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.jdbc.common.SPreparedStatement.<init>(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.jdbc.jdbc41.S41PreparedStatement.<init>(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.jdbc.jdbc42.S42PreparedStatement.<init>(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.redshift.core.jdbc42.PGJDBC42PreparedStatement.<init>(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.redshift.core.jdbc42.PGJDBC42ObjectFactory.createPreparedStatement(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.jdbc.common.SConnection.prepareStatement(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]
    at com.amazon.jdbc.common.SConnection.prepareStatement(Unknown Source) ~[RedshiftJDBC42-no-awssdk-1.2.16.1027.jar:?]

此插入顺序适用于数百个批次的数千条记录，但经常会抛出这些异常。

插入看起来如下：

    private static final String INSERT_MANUAL =
        "INSERT INTO abc(a, b, c, d, e, f, g, h, i) VALUES ";
    private static final String INSERT_PART = "(?,?,?,?,?,?,?,?,?)";

- - 方法：

    StringBuilder query = new StringBuilder();
    query.append(INSERT_MANUAL);
    query.append(INSERT_PART);

    for(int i = 1; i < abc.size();i++){
        query.append(",").append(INSERT_PART);
    }
    try(
            Connection connection = dataSource.getConnection();
            PreparedStatement ps = connection.prepareStatement(query.toString())
    ){

        int i = 1;

        for(Abc q : abc){
            ps.setTimestamp(i++,new Timestamp(q.a()));
            ps.setInt(i++,q.b());
            ps.setString(i++,q.c());
            ps.setString(i++,q.d());
            ps.setDouble(i++,q.e());
            ps.setDouble(i++,q.f());
            ps.setDouble(i++,q.g());
            ps.setDouble(i++,q.h());
            ps.setInt(i++,q.i());
        }

        ps.executeUpdate();

        connection.commit();

    } catch (SQLException e) {
        throw new RuntimeException(e);
    }

字段未对齐或未设置字段实际上是不可能的。事实证明，编写这样的批量插入比执行 jdbc 批处理效率高 100 倍以上。

桌子：

create table abc
(
a timestamp,
b integer not null,
c varchar(45),
d varchar(12),
e double precision,
f double precision,
g double precision,
h double precision,
i integer
)

标签： amazon-web-servicesjdbcamazon-redshift