BATCHes

DSE Version: 6.7

Intro

Exercises

Slides

Resources

In the last section we added a latest_videos table. Now we have two tables to store video data. This is a form of denormalization we use with Apache Cassandra™ in order to scale our application. But how do we keep denormalized tables in sync? Cassandra has a feature known as BATCH to help solve the denormalization issues.

No write up.

Exercise: BATCHes

In this exercise, you will:

  • Learn how to use BATCH

  • Create code to insert into two denormalized tables

  • Continue to become an Apache Cassandratm App Dev Hero

In the last section we added a latest_videos table. Now we have two tables to store video data. This is one form of denormalization we use with Apache Cassandra™ in order to scale our application.

Apache Cassandra™ does not enforce referential integrity (foreign keys), so it is upon us to ensure that if a video record exists in one of these two tables, it also exists in the other. So, when a user adds a new video, our application must insert its data into both tables. This can be a bit of a complex problem - what happens if we insert a record into one table and something goes wrong before we can insert the record into the second table? Cassandra doesn't leave us high and dry. BATCHes are for just these scenarios. We want the database to either commit to both tables or not commit at all.

Steps

1) Access the killrvideo keyspace in cqlsh. Let's introduce ourselves to BATCH using cqlsh. In Che, if cqlsh is not already running, start it now.

Need the command to launch cqlsh? Click here.
Launch cqlsh with the following (remember the password is KVPassword):
cqlsh --username KVUser --cqlshrc /projects/creds/cqlshrc

2) TRUNCATE the video tables. Let's make sure the latest_videos and videos tables are empty. This will make it obvious when our BATCH statements work. In cqlshTRUNCATE both tables.

Need the CQL commands to truncate the tables? Click here
TRUNCATE killrvideo.videos;
TRUNCATE killrvideo.latest_videos;

3) Create a BATCH. Below are two INSERT statements for the same video - one for the videos table and the other for the latest_videos table. Create a BATCH using these INSERT statements. Then, execute the BATCH using cqlsh. Your cheat sheet may be helpful!

Are you a little uncertain as to how to proceed? Click here for a small hint.
BEGIN BATCH
  // Put the two INSERT statements here...
APPLY BATCH;
Are you disappointed by the previous hint and want more? Click here for the solution.
BEGIN BATCH
  // Insert into the videos table here:
  INSERT INTO killrvideo.videos (videoid, added_date, name, description)
  VALUES (07976270-80b1-426d-ac59-11f4a7d4a898, '2019-02-11', 'Funny Cat', 'Funny cat is funny');

  // Insert into the latest_videos table here:
  INSERT INTO killrvideo.latest_videos (bucket, videoid, added_date, name)
  VALUES (0, 07976270-80b1-426d-ac59-11f4a7d4a898, '2019-02-11', 'Funny Cat');
APPLY BATCH;

Woot! Good job!

4) Check out the tables. Let's see the result of the previous BATCH command. SELECT all rows for both tables and inspect the results:

Want the CQL for the SELECT commands? Click here.
SELECT * FROM killrvideo.videos;
SELECT * FROM killrvideo.latest_videos;

5) TRUNCATE the tables again. We'll clean out the tables again. Back in cqlsh, TRUNCATE the tables.

Need the CQL commands to truncate the tables? Click here
TRUNCATE killrvideo.videos;
TRUNCATE killrvideo.latest_videos;

6) Write the same BATCH command in ScratchPad.java. Let's freshen up your ScratchPad.java file. Copy the code below and paste it over what you already have in scratchpad/src/main/java/com.datastax.academy.cloud.demo/ScratchPad.java. Bust a move!

package com.datastax.academy.cloud.demo;

import java.io.File;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import com.datastax.oss.driver.api.core.cql.BatchStatement;
import com.datastax.oss.driver.api.core.cql.BoundStatement;
import com.datastax.oss.driver.api.core.cql.DefaultBatchType;
import com.datastax.oss.driver.api.core.cql.PreparedStatement;
import com.datastax.oss.driver.api.core.cql.ResultSet;
import com.datastax.oss.driver.api.core.cql.Row;
import com.datastax.oss.driver.api.core.cql.SimpleStatement;
import com.datastax.oss.driver.api.core.cql.SimpleStatementBuilder;
import com.datastax.dse.driver.api.core.DseSession;
import com.datastax.oss.driver.api.core.DefaultConsistencyLevel;
import com.datastax.oss.driver.api.querybuilder.QueryBuilder;
import com.datastax.oss.driver.api.querybuilder.insert.RegularInsert;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ScratchPad {
  static final Logger LOGGER = LoggerFactory.getLogger(ScratchPad.class);
  
  static PreparedStatement insertVideoStatementPrepared;
  static PreparedStatement insertLatestVideoStatementPrepared;
  
  public static void addNewVideo(DseSession session, Video video) throws Exception {
    LOGGER.debug("------Start add new video------");

    Instant now = Instant.now();

    BoundStatement insertVideoStatementBound = insertVideoStatementPrepared.bind()
      .setUuid("videoid", video.getVideoid())
      .setUuid("userid", video.getUserid()) 
      .setString("name", video.getName())
      .setString("description", video.getDescription())
      .setString("location", video.getLocation())
      .setInt("location_type", 0)
      .setString("preview_image_location", video.getPreviewImageLocation())
      .setInstant("added_date", Instant.now())
      .setSet("tags", video.getTags(), String.class);

    BoundStatement insertLatestVideoStatementBound = insertLatestVideoStatementPrepared.bind()
      .setInt("bucket", 0)
      .setInstant("added_date", now)
      .setUuid("videoid", video.getVideoid())
      .setString("name", video.getName())
      .setString("preview_image_location", video.getPreviewImageLocation())
      .setUuid("userid", video.getUserid());
      
    //----------------------------------------------------------------
    // Build a BatchStatement:
    //----------------------------------------------------------------
    BatchStatement batch = ...

    //----------------------------------------------------------------
    // Execute the statement here:
    //----------------------------------------------------------------
    session. ...
  }

  public static void init(DseSession session) {
    // Insert into videos (from a previous exercise)
    SimpleStatementBuilder insertVideoBuilder = 
      QueryBuilder.insertInto("killrvideo", "videos")
        .value("videoId", QueryBuilder.bindMarker())
        .value("userId", QueryBuilder.bindMarker())
        .value("name", QueryBuilder.bindMarker())
        .value("description", QueryBuilder.bindMarker())
        .value("location", QueryBuilder.bindMarker())
        .value("location_type", QueryBuilder.bindMarker())
        .value("preview_image_location", QueryBuilder.bindMarker())
        .value("tags", QueryBuilder.bindMarker())
        .value("added_date", QueryBuilder.bindMarker())
        .builder();
        
    // Insert into latest_videos (following the same pattern)
    SimpleStatementBuilder insertLatestVideoBuilder = 
      QueryBuilder.insertInto("killrvideo", "latest_videos")
        .value("bucket", QueryBuilder.bindMarker())
        .value("added_date", QueryBuilder.bindMarker())
        .value("videoid", QueryBuilder.bindMarker())
        .value("name", QueryBuilder.bindMarker())
        .value("preview_image_location", QueryBuilder.bindMarker())
        .value("userid", QueryBuilder.bindMarker())
        .builder();    
                       
    SimpleStatement insertVideoStatement = insertVideoBuilder.build();
    SimpleStatement insertLatestVideoStatement = insertLatestVideoBuilder.build();
        
    insertVideoStatementPrepared = session.prepare(insertVideoStatement);
    insertLatestVideoStatementPrepared = session.prepare(insertLatestVideoStatement);
  }

  public static void main(String[] args) {
    LOGGER.info("Starting main()...");
    try {
      SessionManagement.initSession();
      DseSession session = SessionManagement.getSession();
      init(session);
    
      // Here's a Video instance for the Funny Cat video
      Video video = new Video(UUID.randomUUID(), UUID.randomUUID(), "Funny Cat", 
        "Funny Cat is Funny", "5yFfCAaedgA",
         0, "https://img.youtube.com/vi/_u28VT47vmc/hqdefault.jpg", 
         null, Instant.now());
            
      // Here's the test call
      addNewVideo(session, video);
    }
    catch (Exception e) { e.printStackTrace(); }
    finally { SessionManagement.closeSession(); }
    System.exit(0);
  }
}

7) Take a tour of the code. Whoa, that's a lot of code. Or at least it seems that way, but most of it you have already seen (or written!). We have the code from a previous exercise that inserts a video into the videos table using a PreparedStatement. We just added another PreparedStatement to insert a video into the latest_videos table as well.

Let's break this code down piece by piece:

We have two static class-level fields for our final PreparedStatements. Remember, in a previous exercise we wanted to prepare() our statements once and reuse them many times. To do that, we made our insertVideoStatementPrepared a static class-level field. We follow the same pattern for insertLatestVideoStatementPrepared as well. Let's take a look at the code that does the prepare()ing:

Here is the init() method. Recall we used the init() method in VideoAccess.java to prepare() the insertVideoStatementPrepared class level field.

We begin by using QueryBuilder to build up the query into a SimpleStatementBuilder. We then build() that to make a SimpleStatement we name insertVideoStatement. Finally, we prepare() our insertVideoStatement and save the results away in the class level field insertVideoStatementPrepared.

Here we also add code following this same pattern to create insertLatestVideoStatementPrepared as well. So now we have two PreparedStatements targeting the tables that store video data.

Finally, here's the addNewVideo() code. Again, like the other areas of this ScratchPad.java file, we already added the code similar to what you have done before to insert a video into the videos table. We bind() our class level insertLatestVideosStatementPrepared and set the individual column values from the video object.

8) BATCH the BoundStatements and execute(). So now we have two BoundStatements: insertVideoStatementBound and insertLatestVideoStatementBound. Your job is to combine these two into a BatchStatement and execute() it. Notice we marked where to do so in the addNewVideo() method. Don't forget...your cheat sheet may come in helpful here. Go get 'em ninja!

Are you stuck and need more help? Click here for the solution.
package com.datastax.academy.cloud.demo;

import java.io.File;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import com.datastax.oss.driver.api.core.cql.BatchStatement;
import com.datastax.oss.driver.api.core.cql.BoundStatement;
import com.datastax.oss.driver.api.core.cql.DefaultBatchType;
import com.datastax.oss.driver.api.core.cql.PreparedStatement;
import com.datastax.oss.driver.api.core.cql.ResultSet;
import com.datastax.oss.driver.api.core.cql.Row;
import com.datastax.oss.driver.api.core.cql.SimpleStatement;
import com.datastax.oss.driver.api.core.cql.SimpleStatementBuilder;
import com.datastax.dse.driver.api.core.DseSession;
import com.datastax.oss.driver.api.core.DefaultConsistencyLevel;
import com.datastax.oss.driver.api.querybuilder.QueryBuilder;
import com.datastax.oss.driver.api.querybuilder.insert.RegularInsert;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ScratchPad {
  static final Logger LOGGER = LoggerFactory.getLogger(ScratchPad.class);
  
  static PreparedStatement insertVideoStatementPrepared;
  static PreparedStatement insertLatestVideoStatementPrepared;
  
  public static void addNewVideo(DseSession session, Video video) throws Exception {
    LOGGER.debug("------Start add new video------");

    Instant now = Instant.now();

    BoundStatement insertVideoStatementBound = insertVideoStatementPrepared.bind()
      .setUuid("videoid", video.getVideoid())
      .setUuid("userid", video.getUserid()) 
      .setString("name", video.getName())
      .setString("description", video.getDescription())
      .setString("location", video.getLocation())
      .setInt("location_type", 0)
      .setString("preview_image_location", video.getPreviewImageLocation())
      .setInstant("added_date", Instant.now())
      .setSet("tags", video.getTags(), String.class);

    BoundStatement insertLatestVideoStatementBound = insertLatestVideoStatementPrepared.bind()
      .setInt("bucket", 0)
      .setInstant("added_date", now)
      .setUuid("videoid", video.getVideoid())
      .setString("name", video.getName())
      .setString("preview_image_location", video.getPreviewImageLocation())
      .setUuid("userid", video.getUserid());
      
    //----------------------------------------------------------------
    // Build a BatchStatement:
    //----------------------------------------------------------------
    BatchStatement batch = BatchStatement.newInstance(DefaultBatchType.LOGGED,
      insertVideoStatementBound, insertLatestVideoStatementBound);

    //----------------------------------------------------------------
    // Execute the statement here:
    //----------------------------------------------------------------
    session.execute(batch);
  }

  public static void init(DseSession session) {
    // Insert into videos (from a previous exercise)
    SimpleStatementBuilder insertVideoBuilder = 
      QueryBuilder.insertInto("killrvideo", "videos")
        .value("videoId", QueryBuilder.bindMarker())
        .value("userId", QueryBuilder.bindMarker())
        .value("name", QueryBuilder.bindMarker())
        .value("description", QueryBuilder.bindMarker())
        .value("location", QueryBuilder.bindMarker())
        .value("location_type", QueryBuilder.bindMarker())
        .value("preview_image_location", QueryBuilder.bindMarker())
        .value("tags", QueryBuilder.bindMarker())
        .value("added_date", QueryBuilder.bindMarker())
        .builder();
        
    // Insert into latest_videos (following the same pattern)
    SimpleStatementBuilder insertLatestVideoBuilder = 
      QueryBuilder.insertInto("killrvideo", "latest_videos")
        .value("bucket", QueryBuilder.bindMarker())
        .value("added_date", QueryBuilder.bindMarker())
        .value("videoid", QueryBuilder.bindMarker())
        .value("name", QueryBuilder.bindMarker())
        .value("preview_image_location", QueryBuilder.bindMarker())
        .value("userid", QueryBuilder.bindMarker())
        .builder();    
                       
    SimpleStatement insertVideoStatement = insertVideoBuilder.build();
    SimpleStatement insertLatestVideoStatement = insertLatestVideoBuilder.build();
        
    insertVideoStatementPrepared = session.prepare(insertVideoStatement);
    insertLatestVideoStatementPrepared = session.prepare(insertLatestVideoStatement);
  }

  public static void main(String[] args) {
    LOGGER.info("Starting main()...");
    try {
      SessionManagement.initSession();
      DseSession session = SessionManagement.getSession();
      init(session);
    
      // Here's a Video instance for the Funny Cat video
      Video video = new Video(UUID.randomUUID(), UUID.randomUUID(), "Funny Cat", 
        "Funny Cat is Funny", "5yFfCAaedgA",
         0, "https://img.youtube.com/vi/_u28VT47vmc/hqdefault.jpg", 
         null, Instant.now());
            
      // Here's the test call
      addNewVideo(session, video);
    }
    catch (Exception e) { e.printStackTrace(); }
    finally { SessionManagement.closeSession(); }
    System.exit(0);
  }
}

9) Run the code and check the tables. Run your scratchpad test code. Back in cqlsh, query the tables to see if your code inserted the rows as expected.

Need help with the CQL? Click here.
SELECT * FROM killrvideo.videos;
SELECT * FROM killrvideo.latest_videos;

10) Clean out the tables one more time. Back in cqlsh TRUNCATE the tables one final time.

Need the CQL commands to truncate the tables? Click here
TRUNCATE killrvideo.videos;
TRUNCATE killrvideo.latest_videos;

11) Move your addNewVideo() code to your killrvideo project. You know the drill, we need to copy the code from ScratchPad.java into VideoAccess.java (killrvideo/src/main/java/killrvideo/dataLayer/VideoAccess.java). Specifically, copy these three parts replacing existing code where applicable:

  • init()
  • addNewVideo()
  • The static class level variable declaration of insertLatestVideoStatementPrepared
Did you get lost and need the final VideoAccess.java code? Click here.
package killrvideo.dataLayer;

import java.io.File;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import com.datastax.oss.driver.api.core.cql.BatchStatement;
import com.datastax.oss.driver.api.core.cql.BoundStatement;
import com.datastax.oss.driver.api.core.cql.DefaultBatchType;
import com.datastax.oss.driver.api.core.cql.PreparedStatement;
import com.datastax.oss.driver.api.core.cql.ResultSet;
import com.datastax.oss.driver.api.core.cql.Row;
import com.datastax.oss.driver.api.core.cql.SimpleStatement;
import com.datastax.oss.driver.api.core.cql.SimpleStatementBuilder;
import com.datastax.dse.driver.api.core.DseSession;
import com.datastax.oss.driver.api.core.DefaultConsistencyLevel;
import com.datastax.oss.driver.api.querybuilder.QueryBuilder;
import com.datastax.oss.driver.api.querybuilder.insert.RegularInsert;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import killrvideo.entity.LatestVideos;
import killrvideo.entity.Video;

public class VideoAccess {
  private static final Logger LOGGER = LoggerFactory.getLogger(UserAccess.class);
  
  static PreparedStatement insertVideoStatementPrepared;
  static PreparedStatement insertLatestVideoStatementPrepared;

  public static void addNewVideo(DseSession session, Video video) throws Exception {
    LOGGER.debug("------Start add new video------");

    Instant now = Instant.now();

    BoundStatement insertVideoStatementBound = insertVideoStatementPrepared.bind()
      .setUuid("videoid", video.getVideoid())
      .setUuid("userid", video.getUserid()) 
      .setString("name", video.getName())
      .setString("description", video.getDescription())
      .setString("location", video.getLocation())
      .setInt("location_type", 0)
      .setString("preview_image_location", video.getPreviewImageLocation())
      .setInstant("added_date", Instant.now())
      .setSet("tags", video.getTags(), String.class);

    BoundStatement insertLatestVideoStatementBound = insertLatestVideoStatementPrepared.bind()
      .setInt("bucket", 0)
      .setInstant("added_date", now)
      .setUuid("videoid", video.getVideoid())
      .setString("name", video.getName())
      .setString("preview_image_location", video.getPreviewImageLocation())
      .setUuid("userid", video.getUserid());
      
    //----------------------------------------------------------------
    // Build a BatchStatement:
    //----------------------------------------------------------------
    BatchStatement batch = BatchStatement.newInstance(DefaultBatchType.LOGGED,
      insertVideoStatementBound, insertLatestVideoStatementBound);

    //----------------------------------------------------------------
    // Execute the statement here:
    //----------------------------------------------------------------
    session.execute(batch);
  }
  
  public static Video getVideo(DseSession session, UUID videoId) throws Exception {
    LOGGER.debug("------Start get video------");
    return null;  
  }

  public static List<LatestVideos> getLatestVideos(DseSession session) throws Exception {
    LOGGER.debug("------Start get latest videos------");
    return new ArrayList<LatestVideos>();
  } 
  
  public static void init(DseSession session) {
    // Insert into videos (from a previous exercise)
    SimpleStatementBuilder insertVideoBuilder = 
      QueryBuilder.insertInto("killrvideo", "videos")
        .value("videoId", QueryBuilder.bindMarker())
        .value("userId", QueryBuilder.bindMarker())
        .value("name", QueryBuilder.bindMarker())
        .value("description", QueryBuilder.bindMarker())
        .value("location", QueryBuilder.bindMarker())
        .value("location_type", QueryBuilder.bindMarker())
        .value("preview_image_location", QueryBuilder.bindMarker())
        .value("tags", QueryBuilder.bindMarker())
        .value("added_date", QueryBuilder.bindMarker())
        .builder();
        
    // Insert into latest_videos (following the same pattern)
    SimpleStatementBuilder insertLatestVideoBuilder = 
      QueryBuilder.insertInto("killrvideo", "latest_videos")
        .value("bucket", QueryBuilder.bindMarker())
        .value("added_date", QueryBuilder.bindMarker())
        .value("videoid", QueryBuilder.bindMarker())
        .value("name", QueryBuilder.bindMarker())
        .value("preview_image_location", QueryBuilder.bindMarker())
        .value("userid", QueryBuilder.bindMarker())
        .builder();    
                       
    SimpleStatement insertVideoStatement = insertVideoBuilder.build();
    SimpleStatement insertLatestVideoStatement = insertLatestVideoBuilder.build();
        
    insertVideoStatementPrepared = session.prepare(insertVideoStatement);
    insertLatestVideoStatementPrepared = session.prepare(insertLatestVideoStatement);
  }
}

12) Bounce the web app. Restart your killrvideo project.

13) Add a new video via the web app. Jump over to your web app tab in your browser and add a new video. If you need help remembering how to do this, click here and follow the first four steps of the exercise.

Don't want to hunt for a YouTube URL? Click here

https://www.youtube.com/watch?v=XyNlqQId-nk

14) Verify the BATCH command worked. Back in cqlsh, verify the app updated videos and latest_videos tables correctly by querying all the rows in those tables.

Need help with the CQL? Click here.
SELECT * FROM killrvideo.videos;
SELECT * FROM killrvideo.latest_videos;

Once you see the correct rows in the tables, take a few minutes and celebrate!

END OF EXERCISE

No FAQs.

Batching data queries: use BATCH statements to either commit data to both tables or not commit at all.

Comments are closed.