迭代Cassandra行使用LIMIT和OFFSET是否会产生意外副作用?

huangapple go评论113阅读模式
英文:

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&gt; select * from user LIMIT 1 OFFSET 1;
SyntaxException: line 1:27 mismatched input &#39;OFFSET&#39; 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(&quot;+ Page 1 has {} items&quot;, 
 page1.getAvailableWithoutFetching());
 Iterator&lt;Row&gt; page1Iter = page1.iterator();
 while (0 &lt;  page1.getAvailableWithoutFetching()) {
   LOGGER.info(&quot;Page1: &quot; + 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

huangapple
  • 本文由 发表于 2023年2月13日 23:54:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/75438248.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定