如何使用CQL与Java创建物化视图（Materialized View）

Hey there! Let's dive into creating materialized views (MVs) in Cassandra using both CQL and Java. I'll cover each step with practical examples, so you can implement this in your own projects without confusion.

Prerequisites #

First, make sure you have:

• A running Cassandra cluster (local or remote)
• Access to cqlsh (Cassandra's command-line tool)

Step 1: Create a Base Table #

Materialized views depend on an existing base table. Let's create a sample user table that stores user profiles:

CREATE KEYSPACE IF NOT EXISTS demo_keyspace
WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};

USE demo_keyspace;

CREATE TABLE IF NOT EXISTS users (
    user_id UUID PRIMARY KEY,
    username TEXT,
    email TEXT,
    country TEXT,
    signup_date TIMESTAMP
);

Step 2: Define and Create the Materialized View #

Let's say we want to query users by their country and signup_date efficiently—something the base table's primary key doesn't support. We'll create an MV for this:

CREATE MATERIALIZED VIEW IF NOT EXISTS users_by_country
AS SELECT user_id, username, email, signup_date
FROM users
WHERE user_id IS NOT NULL AND country IS NOT NULL AND signup_date IS NOT NULL
PRIMARY KEY ((country), signup_date, user_id)
WITH CLUSTERING ORDER BY (signup_date DESC);

Key Notes:

• The WHERE clause must include all columns from the base table's primary key (user_id here) and all columns used in the MV's primary key. All these columns must be non-null (IS NOT NULL).
• The MV's primary key uses country as the partition key, with signup_date and user_id as clustering columns. This lets us quickly fetch users in a specific country, ordered by signup date.
• The CLUSTERING ORDER BY clause sets the default sort order for the clustering columns.

Step 3: Verify the Materialized View #

Check if the MV was created successfully:

DESCRIBE MATERIALIZED VIEW users_by_country;

You can also insert data into the base table and confirm it syncs to the MV:

-- Insert into base table
INSERT INTO users (user_id, username, email, country, signup_date)
VALUES (uuid(), 'johndoe', 'john@example.com', 'USA', toTimestamp(now()));

-- Query the MV
SELECT * FROM users_by_country WHERE country = 'USA';

You should see the same data appear in the MV automatically—Cassandra handles syncing changes to the base table to the MV.

We'll use the DataStax Java Driver 4.x (the official driver for Cassandra) for this. Let's walk through each step.

Prerequisites #

• Java 8+ installed
• Build tool (Maven or Gradle) set up

Step 1: Add Driver Dependency #

Maven

Add this to your pom.xml:

<dependency>
    <groupId>com.datastax.oss</groupId>
    <artifactId>java-driver-core</artifactId>
    <version>4.17.0</version>
</dependency>
<dependency>
    <groupId>com.datastax.oss</groupId>
    <artifactId>java-driver-query-builder</artifactId>
    <version>4.17.0</version>
</dependency>

Gradle

Add this to your build.gradle:

implementation 'com.datastax.oss:java-driver-core:4.17.0'
implementation 'com.datastax.oss:java-driver-query-builder:4.17.0'

Step 2: Initialize Cassandra Cluster Connection #

Create a session to connect to your Cassandra cluster:

import com.datastax.oss.driver.api.core.CqlSession;
import com.datastax.oss.driver.api.core.cql.ResultSet;
import com.datastax.oss.driver.api.core.cql.Row;

public class CassandraMVExample {
    public static void main(String[] args) {
        // Connect to local Cassandra instance (adjust contact points for remote clusters)
        try (CqlSession session = CqlSession.builder()
                .withKeyspace("demo_keyspace")
                .withLocalDatacenter("datacenter1")
                .build()) {
            
            // We'll add MV creation code here next
        }
    }
}

Step 3: Create Base Table via Java #

If the base table doesn't exist, create it using a CQL statement:

// Inside the try-with-resources block
String createBaseTableQuery = """
        CREATE TABLE IF NOT EXISTS users (
            user_id UUID PRIMARY KEY,
            username TEXT,
            email TEXT,
            country TEXT,
            signup_date TIMESTAMP
        )
        """;
session.execute(createBaseTableQuery);
System.out.println("Base table created (or already exists)");

Step 4: Create Materialized View via Java #

Use a CQL statement to create the MV, just like in cqlsh:

String createMVQuery = """
        CREATE MATERIALIZED VIEW IF NOT EXISTS users_by_country
        AS SELECT user_id, username, email, signup_date
        FROM users
        WHERE user_id IS NOT NULL AND country IS NOT NULL AND signup_date IS NOT NULL
        PRIMARY KEY ((country), signup_date, user_id)
        WITH CLUSTERING ORDER BY (signup_date DESC)
        """;
session.execute(createMVQuery);
System.out.println("Materialized view created (or already exists)");

Step 5: Test the Materialized View #

Insert data into the base table and query the MV to confirm sync:

// Insert data into base table
String insertQuery = """
        INSERT INTO users (user_id, username, email, country, signup_date)
        VALUES (uuid(), 'janedoe', 'jane@example.com', 'Canada', toTimestamp(now()))
        """;
session.execute(insertQuery);

// Query the MV
String mvQuery = "SELECT * FROM users_by_country WHERE country = 'Canada'";
ResultSet resultSet = session.execute(mvQuery);

for (Row row : resultSet) {
    System.out.printf("User ID: %s, Username: %s, Signup Date: %s%n",
            row.getUuid("user_id"),
            row.getString("username"),
            row.getTimestamp("signup_date"));
}

Step 6: Clean Up (Optional) #

If you want to drop the MV and base table when done:

session.execute("DROP MATERIALIZED VIEW IF EXISTS users_by_country");
session.execute("DROP TABLE IF EXISTS users");

• Limit MV Count: Each MV adds overhead to write operations (Cassandra has to update the base table and all MVs). Don't create more than you need.
• Avoid Complex Primary Keys: Keep MV primary keys simple to maintain performance.
• Test Sync: Always verify that writes to the base table are reflected in the MV—edge cases like null values can cause rows to not appear in the MV.
• Use IF NOT EXISTS: Prevent errors when running your code multiple times by including IF NOT EXISTS in CREATE statements.

内容的提问来源于stack exchange，提问作者Alex Kokorin