Tuesday, February 22, 2022

Trigger in Cassandra

 Please refer to the below previous post before starting

http://siddharathadhumale.blogspot.com/2022/02/running-cassandra-in-windows-quering.html

Inserting:-
INSERT INTO sid_emp (emp_id, emp_name, emp_houseno) VALUES(1,’siddhu’, 601);\

cqlsh:siddhu> INSERT INTO sid_emp (emp_id, emp_name, emp_houseno) VALUES(1,’siddhu’, 601);
cqlsh:siddhu> select * from sid_emp
… ;

emp_id | emp_houseno | emp_name
——–+————-+———-
1 | 601 | siddhu

(1 rows)

Update:-
UPDATE sid_emp SET emp_houseno=602 WHERE emp_id=1;

cqlsh:siddhu> UPDATE sid_emp SET emp_houseno=602 WHERE emp_id=1;
cqlsh:siddhu> select * from sid_emp;

emp_id | emp_houseno | emp_name
——–+————-+———-
1 | 602 | siddhu

(1 rows)
cqlsh:siddhu>

Delete:-
DELETE FROM sid_emp WHERE emp_id=1;

cqlsh:siddhu> DELETE FROM sid_emp WHERE emp_id=1;
cqlsh:siddhu> select * from sid_emp;

emp_id | emp_houseno | emp_name
——–+————-+———-

(0 rows)
cqlsh:siddhu>

Now lets try to create trigger so that on insert of one table will also do the insert in another table.

for that lets create a new table sid_emp2 with same column.

1- CREATE TABLE sid_emp1(
emp_id int PRIMARY KEY,
emp_name text,
emp_houseno varint
);

you see both tables are empty now

cqlsh:siddhu> select * from sid_emp;

emp_id | emp_houseno | emp_name
——–+————-+———-

(0 rows)
cqlsh:siddhu> select * from sid_emp1;

emp_id | emp_houseno | emp_name
——–+————-+———-

(0 rows)
cqlsh:siddhu>

Before writing the trigger lets first try to insert the record in cassandra table using java. As we are going to use the java jar after executing trigger.

1- Lets first create a maven project with belwo screen shot we are using STS IDE.

In our case, we want a simple maven project so we will enter org.apache.maven.archetypes
in filter and then select row with Artifact-id as maven-archetype-quickstart.

Lets add maven entry

1
2
3
4
5
<dependency>
  <groupId>com.datastax.cassandra</groupId>
  <artifactId>cassandra-driver-core</artifactId>
  <version>2.1.6</version>
</dependency>

complete pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
<?xml version="1.0" encoding="UTF-8"?>
 
    <modelVersion>4.0.0</modelVersion>
 
    <groupId>com.siddhu</groupId>
    <artifactId>SiddhuCassandraJavaExample</artifactId>
    <version>0.0.1-SNAPSHOT</version>
 
    <name>SiddhuCassandraJavaExample</name>
    <!-- FIXME change it to the project's website -->
 
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
 
    <dependencies>
        <dependency>
            <groupId>com.datastax.cassandra</groupId>
            <artifactId>cassandra-driver-core</artifactId>
            <version>3.8.0</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
 
    <build>
        <pluginManagement><!-- lock down plugins versions to avoid using Maven
                defaults (may be moved to parent pom) -->
            <plugins>
                <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
                <plugin>
                    <artifactId>maven-clean-plugin</artifactId>
                    <version>3.1.0</version>
                </plugin>
                <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
                <plugin>
                    <artifactId>maven-resources-plugin</artifactId>
                    <version>3.0.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.8.0</version>
                </plugin>
                <plugin>
                    <artifactId>maven-surefire-plugin</artifactId>
                    <version>2.22.1</version>
                </plugin>
                <plugin>
                    <artifactId>maven-jar-plugin</artifactId>
                    <version>3.0.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-install-plugin</artifactId>
                    <version>2.5.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-deploy-plugin</artifactId>
                    <version>2.8.2</version>
                </plugin>
                <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
                <plugin>
                    <artifactId>maven-site-plugin</artifactId>
                    <version>3.7.1</version>
                </plugin>
                <plugin>
                    <artifactId>maven-project-info-reports-plugin</artifactId>
                    <version>3.0.0</version>
                </plugin>
            </plugins>
        </pluginManagement>
    </build>
</project>

2- App.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
package com.siddhu.SiddhuCassandraJavaExample;
 
import com.datastax.driver.core.Cluster;
import com.datastax.driver.core.ResultSet;
import com.datastax.driver.core.Row;
import com.datastax.driver.core.Session;
 
public class App {
    public static void main(String[] args) {
        // Connect to the cluster and keyspace "devjavasource"
        final Cluster cluster = Cluster.builder().addContactPoint("127.0.0.1")
                .build();      
        final Session session = cluster.connect("siddhu");
 
        try
        {
            System.out.println("*********Cluster Information *************");
            System.out.println(" Cluster Name is: " + cluster.getClusterName() );
            System.out.println(" Driver Version is: " + cluster.getDriverVersion() );
            System.out.println(" Cluster Configuration is: " + cluster.getConfiguration() );
            System.out.println(" Cluster Metadata is: " + cluster.getMetadata() );
            System.out.println(" Cluster Metrics is: " + cluster.getMetrics() );       
 
            // Retrieve all User details from Users table
            System.out.println("*********Retrive User Data Example *************");     
            getUsersAllDetails(session);
 
            // Insert new User into users table
            System.out.println("*********Insert User Data Example *************");     
            session.execute("INSERT INTO sid_emp (emp_id, emp_name, emp_houseno) VALUES (1, 'siddhu', 601)");
            getUsersAllDetails(session);
 
            // Update user data in users table
            System.out.println("*********Update User Data Example *************");     
            session.execute("UPDATE sid_emp SET emp_houseno=602 WHERE emp_id=1;");
            getUsersAllDetails(session);
 
            // Delete user from users table
            System.out.println("*********Delete User Data Example *************");     
            session.execute("DELETE FROM sid_emp WHERE emp_id=1;");
            getUsersAllDetails(session);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            // Close Cluster and Session objects
            System.out.println("nIs Cluster Closed :" + cluster.isClosed());
            System.out.println("Is Session Closed :" + session.isClosed());    
            cluster.close();
            session.close();
            System.out.println("Is Cluster Closed :" + cluster.isClosed());
            System.out.println("Is Session Closed :" + session.isClosed());
        }
 
    }
 
    private static void getUsersAllDetails(final Session inSession){       
        // Use select to get the users table data
        ResultSet results = inSession.execute("SELECT * FROM sid_emp");
        for (Row row : results) {
            System.out.println("emp name:"+row.getString("emp_name"));
            System.out.println("emp id:"+row.getInt("emp_id"));
            System.out.println("emp houseno:"+row.getVarint("emp_houseno"));
 
        }
    }
}

Now lets execute below example and perform all the CRUD operations.

Output:-

SLF4J: Failed to load class “org.slf4j.impl.StaticLoggerBinder”.
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Cluster Information ****
Cluster Name is: cluster1
Driver Version is: 3.8.0
Cluster Configuration is: com.datastax.driver.core.Configuration@7c9d8e2
Cluster Metadata is: com.datastax.driver.core.Metadata@20d525
Cluster Metrics is: com.datastax.driver.core.Metrics@6200f9cb
***Retrive User Data Example *** nInsert User Data Example
emp name:siddhu
emp id:1
emp houseno:601
***Update User Data Example *** emp name:siddhu emp id:1 emp houseno:602 Delete User Data Example
Is Cluster Closed :false
Is Session Closed :false
Is Cluster Closed :true
Is Session Closed :true

2- Create a trigger in java and create a jar

Now lets create a another java maven project that will act as a trigger for us. we are going to create a jar files from this project and will keep it in trigger folder of cassandra. Please follow below steps to create cassandra maven project for trigger.

In our case, we want a simple maven project so we will enter org.apache.maven.archetypes
in filter and then select row with Artifact-id as maven-archetype-quickstart.

Add following dependecies

1
2
3
4
5
6
7
8
9
10
11
12
<dependency>
    <groupId>com.datastax.cassandra</groupId>
    <artifactId>cassandra-driver-core</artifactId>
    <version>3.8.0</version>
</dependency>
<dependency>
    <groupId>org.apache.cassandra</groupId>
    <artifactId>cassandra-all</artifactId>
    <version>3.11.5</version>
</dependency>

 1-Pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
<?xml version="1.0" encoding="UTF-8"?>
 
    <modelVersion>4.0.0</modelVersion>
 
    <groupId>com.siddhu</groupId>
    <artifactId>SiddhuCassandraTrigger</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>SiddhuCassandraTrigger</name>
 
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
 
    <dependencies>
        <dependency>
            <groupId>com.datastax.cassandra</groupId>
            <artifactId>cassandra-driver-core</artifactId>
            <version>3.8.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.cassandra</groupId>
            <artifactId>cassandra-all</artifactId>
            <version>3.11.5</version>
        </dependency>
 
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
 
    <build>
        <plugins>
 
 
            <!-- download source code in Eclipse, best practice -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-eclipse-plugin</artifactId>
                <version>2.9</version>
                <configuration>
                    <downloadSources>true</downloadSources>
                    <downloadJavadocs>false</downloadJavadocs>
                </configuration>
            </plugin>
 
            <!-- Set a compiler level -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.3.2</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>2.3</version>
                <executions>
                    <!-- Run shade goal on package phase -->
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <transformers>
                                <!-- add Main-Class to manifest file -->
                                <transformer
                                    implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                    <mainClass>com.siddhu.SiddhuCassandraTrigger.App</mainClass>
                                </transformer>
                            </transformers>
                            <filters>
                                <filter>
                                    <artifact>*:*</artifact>
                                    <excludes>
                                        <exclude>META-INF/*.SF</exclude>
                                        <exclude>META-INF/*.DSA</exclude>
                                        <exclude>META-INF/*.RSA</exclude>
                                    </excludes>
                                </filter>
                            </filters>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
 
    </build>
</project>

2- App.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
package com.siddhu.SiddhuCassandraTrigger;
 
import java.util.ArrayList;
import org.apache.cassandra.db.partitions.Partition;
import org.apache.cassandra.triggers.ITrigger;
import org.apache.cassandra.db.Mutation;
import org.apache.log4j.Logger;
 
import com.datastax.driver.core.Cluster;
import com.datastax.driver.core.ResultSet;
import com.datastax.driver.core.Row;
import com.datastax.driver.core.Session;
 
import java.util.Collection;
 
/**
 * Hello world!
 *
 */
public class App implements ITrigger {
 
    private Logger logger = Logger.getLogger(App.class);
 
    public App() {
        logger.info("Cassandra to Kafka trigger successfully initialized");
    }
 
    @Override
    public Collection<Mutation> augment(Partition update) {
        logger.info("Partition update received");
        try {
            process(update);
            logger.info("Processing partition update finished");
        } catch (Exception e) {
            logger.error("Processing partition update failed", e);
        }
        return new ArrayList<>();
    }
 
    private void process(Partition update) {
 
        logger.info("update.metadata().ksName:"+update.metadata().ksName);
        logger.info("update.metadata().toString():"+update.metadata().toString());
        final Cluster cluster = Cluster.builder().addContactPoint("127.0.0.1")
                .build();      
        final Session session = cluster.connect("siddhu");
 
        try
        {
            System.out.println("*********Cluster Information *************");
            System.out.println(" Cluster Name is: " + cluster.getClusterName() );
            System.out.println(" Driver Version is: " + cluster.getDriverVersion() );
            System.out.println(" Cluster Configuration is: " + cluster.getConfiguration() );
            System.out.println(" Cluster Metadata is: " + cluster.getMetadata() );
            System.out.println(" Cluster Metrics is: " + cluster.getMetrics() );       
 
            // Retrieve all User details from Users table
            System.out.println("*********Retrive User Data Example *************");     
            getUsersAllDetails(session);
 
            // Insert new User into users table
            System.out.println("*********Insert User Data Example *************");     
            session.execute("INSERT INTO sid_emp (emp_id, emp_name, emp_houseno) VALUES (1, 'siddhunew', 6011)");
            getUsersAllDetails(session);
 
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            // Close Cluster and Session objects
            System.out.println("nIs Cluster Closed :" + cluster.isClosed());
            System.out.println("Is Session Closed :" + session.isClosed());    
            cluster.close();
            session.close();
            System.out.println("Is Cluster Closed :" + cluster.isClosed());
            System.out.println("Is Session Closed :" + session.isClosed());
        }
 
 
 
    }
    private static void getUsersAllDetails(final Session inSession){       
        // Use select to get the users table data
        ResultSet results = inSession.execute("SELECT * FROM sid_emp");
        for (Row row : results) {
            System.out.println("emp name:"+row.getString("emp_name"));
            System.out.println("emp id:"+row.getInt("emp_id"));
            System.out.println("emp houseno:"+row.getVarint("emp_houseno"));
 
        }
    }
}
 
Now lets create the jar files using follwoing screen.

Lets put this jar in our trigger folder of cassandra.

C:\apache-cassandra-3.11.6\conf\triggers

Now restart the cassandra and create the trigger using below command

CREATE TRIGGER mySiddhuTrigger ON siddhu.sid_emp USING ‘com.siddhu.SiddhuCassandraTrigger.App’;

cqlsh:siddhu> CREATE TRIGGER mySiddhuTrigger ON siddhu.sid_emp USING ‘com.siddhu.SiddhuCassandraTrigger.App’;
cqlsh:siddhu>

Now you can see our trigger is register now lets insert the value in sid_emp table and check if it insert the row in another tables.
Once you enter the value in sid_emp table you will find a new entry is added

Download :-

https://github.com/shdhumale/SiddhuCassandraJavaExample.git

https://github.com/shdhumale/SiddhuCassandraTrigger.git

No comments: