How to efficiently write large amounts of data to Cassandra via Java or Python?

HuhangChen 0
Viewed 36

There are about millions of rows of data that need to be written to Cassandra.I have tried the following methods:

The first: According to the reference code given by Datastax java-driver or python-driver on GitHub, my code is similar to:

    // The following code is fixed, and this part will be omitted later.
    String cassandraHost = "******";
    String keyspace = "******";
    String table = "******";
    String insertCqlStr = " insert into " + keyspace + "." + table +"( "
            +     "id,date,value)"
            +     " values ( ?, ?, ?) ;";
    CqlSession session = CqlSession.builder()
            .addContactPoint(new InetSocketAddress(cassandraHost, 9042))
            .withLocalDatacenter("datacenter1")
            .withKeyspace(CqlIdentifier.fromCql(keyspace))
            .build();

    PreparedStatement preparedStatement = session.prepare(insertCqlStr);

    // The code below is changed, or just what I think it is.
    for(List<String> row: rows){
        session.execute(
            preparedInsertStatement.bind(row.get(0),     
            row.get(1), row.get(2))
          .setConsistencyLevel(ConsistencyLevel.ANY));
    }
    session.close();

This code works fine, but it's just too inefficient to write for me to accept.So I tried the asynchronous API provided by the driver, and the code is almost the same as the above code:

   for(List<String> row: rows){
        session.executeAsync(
            preparedInsertStatement.bind(row.get(0),     
            row.get(1), row.get(2))
          .setConsistencyLevel(ConsistencyLevel.ANY));
    }
    session.close();

Please excuse my lack of asynchronous programming experience for being so rude. It works, but it has a fatal problem, I found that it does not write all the data into the database. I would like to know the correct usage for calling an async API.

Also, I tried the relevant methods of the BatchStatement provided by the driver. I know this method is officially deprecated to improve performance and it has many limitations. For example, as far as I know, the number of statements in a batch cannot exceed 65535, and in the default configuration, the data length warning limit of batch is 5kb, and the error limit is 50kb. But I kept the number of statements below 65535 and modified the above default configuration:

    List<BoundStatement> boundStatements = new ArrayList<>();
    Integer count = 0;
    BatchStatement batchStatement = BatchStatement.newInstance(BatchType.UNLOGGED);
    for (List<String> row : rows){
    // The actual code here is looping multiple times instead of exiting directly.
        if(count >= 65535){
            break;
        }
        BoundStatement boundStatement = preparedStatement.bind(row.get(0),
                                        row.get(1), row.get(2));
        boundStatements.add(boundStatement);
        count += 1;
    }
    BatchStatement batch = batchStatement.addAll(boundStatements);
    session.execute(batch.setConsistencyLevel(ConsistencyLevel.ANY));
    // session.executeAsync(batch.setConsistencyLevel().ANY);
    session.close();

It also works. And it is actually more efficient than asynchronous APIs, and using synchronous interfaces can ensure data integrity. If the asynchronous API is used to execute BatchStatement here, the incomplete data mentioned above will also occur. But this method still doesn't meet my requirements, I need to execute it with multithreading. When I execute multiple threads it gives error: Caused by: com.datastax.oss.driver.api.core.DriverTimeoutException: Query timed out after PT2S

Summary: I've tried both synchronous and asynchronous writes and Batch related methods, and there are some issues that I can't accept. I now need to know how to properly use the async API to not lose data, and why I'm wrong. As for the BatchStatement related methods, I don't expect it to work, it would be great if you could give me a workable suggestion. Thank you!

datastax cassandra java database python
1 Answers

Instead of trying to write data loading code yourself, I would recommend to adopt a DSBulk tool that is heavily optimized for loading/unloading data to/from Cassandra. And it's open source, so you can even use it as a Java library.

There are few reason for that:

  • Writing async code isn't easy - you need to make sure that you aren't sending too many requests over the same connection (Cassandra has a limit on number of the in-flight requests). For driver 3.x you can use something like this, and driver 4.x has built-in rate limiting capabilities
  • Batch in Cassandra often leads to performance degradation when isn't used correctly. Batch should be used only for submitting the data that belongs to the same partition, otherwise it would cause a higher load on the coordinating node. Plus you also need to implement a custom routing.

DSBulk is doing all of that very efficiently as it was written by people who are working with Cassandra every day in large scale setups.

P.S. in your case, consistency level ANY means that coordinator just acknowledge receiving of data, but doesn't guarantee that it will be written (for example if it's crashed).

Alex Ott0
Related