gpt4 book ai didi

jdbc - 如何在 Snowflake JDBC 中执行批量更新

转载 作者:行者123 更新时间:2023-12-05 04:10:23 26 4
gpt4 key购买 nike

我正在尝试对 PreparedStatement 执行雪花批处理更新,但没有成功。我能够执行每个单独的查询 (preparedStatement.executeQuery()),以便查询工作。但是一旦我切换到 batchUpdate(),我就会遇到一个异常,似乎它没有设置我的变量(在 insertEventStatement.executeBatch() 处抛出)。

net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error: error line 1 at position 134
Bind variable ? not set.
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:88)
at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:384)
at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:421)
at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:240)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:180)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:152)
at net.snowflake.client.core.SFStatement.execute(SFStatement.java:637)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeUpdateInternal(SnowflakeStatementV1.java:135)
at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeBatch(SnowflakePreparedStatementV1.java:1045)

有人看到我可能遗漏了什么吗?

public void insertBotEvents(List<SnowflakeRecord> snowflakeRecords) throws SQLException
{
try
{
PreparedStatement insertEventStatement = connection.prepareStatement(INSERT_EVENT);
PreparedStatement insertMetaEventStatement = connection.prepareStatement(INSERT_META_EVENT);
for (SnowflakeRecord snowflakeRecord : snowflakeRecords)
{
insertEventStatement.setString(1, snowflakeRecord.getUserId());
insertEventStatement.setString(2, snowflakeRecord.getSessionId());
insertEventStatement.setString(3, snowflakeRecord.getEventTime().toString());
insertEventStatement.setString(4, snowflakeRecord.getEventType());
insertEventStatement.setString(5, snowflakeRecord.getEventValue());
insertEventStatement.setString(6, snowflakeRecord.getAdditionalInfo());
insertEventStatement.setString(7, snowflakeRecord.getAffiliateName());

System.out.println("\nINSERTING NEW RECORD: \n" + snowflakeRecord);

insertEventStatement.addBatch();

if (snowflakeRecord.getSmVuiEventDto() != null && snowflakeRecord.getSmVuiEventDto().getParameters() != null)
{
for (ParametersDto parametersDto : snowflakeRecord.getSmVuiEventDto().getParameters())
{

insertMetaEventStatement.setString(1, snowflakeRecord.getSmVuiEventDto().getName());
insertMetaEventStatement.setString(2, parametersDto.getName());
insertMetaEventStatement.setString(3, parametersDto.getValue());
insertMetaEventStatement.setString(4, snowflakeRecord.getUserId());
insertMetaEventStatement.setString(5, snowflakeRecord.getSessionId());
insertMetaEventStatement.setString(6, snowflakeRecord.getEventTime().toString());
insertMetaEventStatement.setString(7, snowflakeRecord.getEventType());
insertMetaEventStatement.setString(8, snowflakeRecord.getEventValue());
insertMetaEventStatement.setString(9, snowflakeRecord.getAdditionalInfo());

insertMetaEventStatement.addBatch();
}
}
}
insertEventStatement.executeBatch();
insertMetaEventStatement.executeBatch();
System.out.println("\nCommitting changes to snowflake.");
connection.commit();
}
catch (Exception e)
{
connection.rollback();
System.err.println("Unable to execute SQL. Rolling back snowflake import");
e.printStackTrace();
throw e;
}

}

查询如下:

private static final String INSERT_EVENT =
"INSERT INTO EVENT " +
"(USER_ID, " +
" SESSION_ID, " +
" EVENT_DATETIME, " +
" EVENT_TYPE, " +
" EVENT_VALUE, " +
" ADDITIONAL_INFO, " +
" AFFILIATE_ID " +
") " +
"SELECT " +
" ?, " +
" ?, " +
" ?, " +
" ?, " +
" ?, " +
" ?, " +
" AFFILIATE_ID " +
"FROM AFFILIATE_LOOKUP " +
"WHERE AFFILIATE_NAME = ?";

private static final String INSERT_META_EVENT =
"INSERT INTO EVENT_META " +
"(EVENT_ID, " +
" META_TYPE, " +
" META_NAME, " +
" META_VALUE " +
") " +
"SELECT EVENT_ID, ?, ?, ? " +
"FROM SM_VUI_EVENT " +
"WHERE USER_ID=? AND " +
" SESSION_ID=? AND " +
" EVENT_DATETIME=? AND " +
" EVENT_TYPE=? AND " +
" EVENT_VALUE=? AND " +
" ADDITIONAL_INFO=?";

编辑:显然,它在我使用的 JDBC 版本 (3.0.18) 中不可用,但在最近的版本中添加 (3.0.21)。如果它不起作用,我会进一步更新。

最佳答案

刚刚确认 Snowflake 目前不支持在包含子查询的 INSERT 语句中提供绑定(bind)数组,但我们正在努力。

关于jdbc - 如何在 Snowflake JDBC 中执行批量更新,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/44554002/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com