英文:
Would iteration over Cassandra rows with LIMIT and OFFSET have any unexpected side effects?
问题
我的项目有一个巨大的Cassandra表。我的Cassandra在Kubernetes上运行,有5个节点。作为后端,我使用Spring Boot 2.X。
我尝试更新整个表中的值。由于表的大小,我不使用"SELECT * FROM TABLE"查询。
相反,我考虑使用"limit"和"offset"来进行查询:
String sql = "SELECT * FROM " + tableName + " LIMIT " + limit + " OFFSET " + offset;
通过递归调用:
private boolean migrateBookingTable(Database database, Statement statement, String tableName, int limit, int offset) throws SQLException, LiquibaseException {
String sql = "SELECT * FROM " + tableName + " LIMIT " + limit + " OFFSET " + offset;
try (ResultSet resultSet = statement.executeQuery(sql)) {
//如果结果集为空,我们完成了
if (!resultSet.isBeforeFirst()) {
return false;
}
while (resultSet.next()) {
//一些逻辑
}
database.execute...
}
return migrateBookingTable(database, statement, tableName, limit, offset+limit);
}
我在一个小型测试环境中进行了测试,它有效。但由于Cassandra的特性和生产环境中有5个节点,我不确定是否会产生副作用。
这种方法可行吗?
英文:
My Project has a huge Cassadra Table. My Cassadra running with 5 nodes in a kubernetes. As Backend I use Spring Boot 2.X.
I try to update values in my entire Table. Because of the size of the table I do not use a "SELECT * FROM TABLE" query.
Instead I think about to use "limit" with "offset"
String sql = "SELECT * FROM " + tableName + " LIMIT " + limit + " OFFSET " + offset;
With recursive call
private boolean migrateBookingTable(Database database, Statement statement, String tableName, int limit, int offset) throws SQLException, LiquibaseException {
String sql = "SELECT * FROM " + tableName + " LIMIT " + limit + " OFFSET " + offset;
try (ResultSet resultSet = statement.executeQuery(sql)) {
//if resultSet is empty, we are done
if (!resultSet.isBeforeFirst()) {
return false;
}
while (resultSet.next()) {
//some logic
}
database.execute...
}
return migrateBookingTable(database, statement, tableName, limit, offset+limit);
}
I tested it on a small test environment. Its worked. But because of cassandra peculiarities and the fact of 5 Nodes on production. Im not sure about side effects.
Is this an "ok" way to go?
答案1
得分: 1
OFFSET
不是CQL语言的一部分,不确定您是如何测试的。
SyntaxException: line 1:27 mismatched input 'OFFSET' expecting EOF (...* from user LIMIT 1 [OFFSET]...)
由于表的大小,我不使用"SELECT * FROM TABLE"查询。
没有Spring Data Cassandra允许的可怕的findAll()
,每个请求都会分页处理Cassandra。为什么不使用Cassandra驱动程序的默认分页行为呢?
SimpleStatement statement = QueryBuilder.selectFrom(USER_TABLENAME).all().build()
.setPageSize(10) // 每页10条
.setTimeout(Duration.ofSeconds(1)) // 1秒超时
.setConsistencyLevel(ConsistencyLevel.ONE);
ResultSet page1 = session.execute(statement);
LOGGER.info("+ Page 1 has {} items",
page1.getAvailableWithoutFetching());
Iterator<Row> page1Iter = page1.iterator();
while (0 < page1.getAvailableWithoutFetching()) {
LOGGER.info("Page1: " + page1Iter.next().getString(USER_EMAIL));
}
// 为第二页做准备
ByteBuffer pagingStateAsBytes = page1.getExecutionInfo().getPagingState();
statement.setPagingState(pagingStateAsBytes);
ResultSet page2 = session.execute(statement);
Spring Data还允许使用Slice
进行分页。
英文:
OFFSET
is not part of CQL language, not sure how you tested.
cqlsh:ks1> select * from user LIMIT 1 OFFSET 1;
SyntaxException: line 1:27 mismatched input 'OFFSET' expecting EOF (...* from user LIMIT 1 [OFFSET]...)
> Because of the size of the table I do not use a "SELECT * FROM TABLE" query.
Without the awful findAll()
allowed by Spring Data Cassandra every request is paged with Cassandra. Why not going with the default behaviour of Paging with the cassandra drivers.
SimpleStatement statement = QueryBuilder.selectFrom(USER_TABLENAME).all().build()
.setPageSize(10) // 10 per pages
.setTimeout(Duration.ofSeconds(1)) // 1s timeout
.setConsistencyLevel(ConsistencyLevel.ONE);
ResultSet page1 = session.execute(statement);
LOGGER.info("+ Page 1 has {} items",
page1.getAvailableWithoutFetching());
Iterator<Row> page1Iter = page1.iterator();
while (0 < page1.getAvailableWithoutFetching()) {
LOGGER.info("Page1: " + page1Iter.next().getString(USER_EMAIL));
}
// Getting ready for page2
ByteBuffer pagingStateAsBytes = page1.getExecutionInfo().getPagingState();
statement.setPagingState(pagingStateAsBytes);
ResultSet page2 = session.execute(statement);
Spring Data Also allow paging with Slice
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论