Use cqlsh terminal to browse cassandra | WSO2 ES 1.0.0

We have used WSO2-BAM’s event stream architecture to store data on Cassandra for social aspects of the WSO2 Enterprise Store.

I have to do few direct Cassandra querying while adding a new feature to the social application within ES distribution. Find following steps I followed in this activity.

1. Download Apache Cassandra 1.1.3 from here[1].
2. Unzip and browse apache-cassandra-1.1.3/bin directory using terminal.
3. Give following command to get the cqlsh terminal. Since I'm using default configuration in my local deployment I'm using admin as the both username and password, localhost as the hostname and 9160 as the port.

./cqlsh localhost 9160 -u admin -p admin

udara@thinkPad:~/Downloads/apache-cassandra-1.1.3/bin$ ./cqlsh localhost 9160 -u admin -p admin
Connected to Test Cluster at localhost:9160.
[cqlsh 2.2.0 | Cassandra 1.1.3 | CQL spec 2.0.0 | Thrift protocol 19.32.0]
Use HELP for help.
cqlsh>

4. Select key-space, in my deployment "EVENT_KS".

cqlsh> use EVENT_KS;

5. Select data from the column family "org_wso2_social_activity".

cqlsh:EVENT_KS> SELECT * FROM org_wso2_social_activity;

To learn more CQL follow this official document[2].

[1]. http://archive.apache.org/dist/cassandra/1.1.3/
[2]. http://cassandra.apache.org/doc/cql/CQL.html

PostgreSQL- Basics

I'm compiling this post to create a reference which any postgreSQL-newbie (like myself) can use to get the basic things right !

You need a postgreSQL server up and running before moving further.

sudo apt-get install postgresql

1. Update postgres user password.In a terminal run following and give the password when prompted.

sudo -u postgres psql postgres
\password postgres

2.  Create Database with the name wso2bam. In a terminal run,

sudo -u postgres createdb wso2bam

3.  Connect to psql- the terminal based frontend,

sudo -u postgres psql postgres

 after you run the above command you will get a terminal with postgres=#.

root@thinkPad:~# sudo -u postgres psql postgres
psql (8.4.19)
Type "help" for help.

postgres=#

Note -: Here onwards I'm going to execute all commands within this psql terminal.

4. List down databases(similar to MySQL show databases.

\list

5. Select database wso2bam.

\c wso2bam

6. List tables within wso2bam current database.

\dt

If you are not interested in remembering and typing all these commands, pgAdmin3 might make your life easy. You can install this tool by running,

sudo apt-get install pgadmin3

You can use following official documentation to try out basic queries[1].

Please note that I have tested and verified above steps on a Ubuntu 12.10 machine, using  both PostgreSQL 8.4 and 9.1 servers.

[1]. http://www.postgresql.org/docs/8.4/static/tutorial-select.html

Install PostgreSQL 8.X on Ubuntu 12.10

If you try to install PostgreSQL on ubuntu using apt-get install postgresql command it will automatically fetch and setup PostgreSQL 9.X.

If following methods are not any option to install PostgreSQL you may continue this blog post.

1. Build using the source code.
2. Downloading deb package and install.

Lets configure our system by updating source list so we can simply install  PostgreSQL using apt-get install postgresql-8.X :)

First create pgdg.list file inside /etc/apt/sources.list.d/ directory with the following content.

deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main

Next import sigining key for this repository by executing following command in terminal.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

You are done, simply run "sudo apt-get update" before installing
PostgreSQL 8.X.

Set up WSO2 BAM with Cassandra cluster

First blog post for year 2014, in this post I'm going to set up a single WSO2-BAM node to work with a cluster of three Cassandra nodes. To better understand the setup please refer following diagram.

I have 3VM's with Ubuntu up and running inside my virtual box, VM1 with IP address 192.168.56.101, VM2 & VM3 with 192.168.56.102,192.168.56.103.

Lets download and set up our Cassandra cluster first. You can find latest version of Cassandra from here[1]. Note that I'm going to use Cassandra 1.1.3 for this exercise. Lets assume we have apache-cassandra-1.1.3 directory within /home/udara/cassandra/ directory on all three nodes.

First im going to configure Cassandra inside node1. Lets browse apache-cassandra-1.1.3/conf/ directory and open cassandra.yaml in a text editor. Make sure to update following parameters according to your environment. Before updating we need to generate tokens for our Cassandra ring using the tokengentool here[2].

cluster_name: Test Cluster
initial_token: 0
seed_provider:        
- seeds: "192.168.56.101" 
listen_address: 192.168.56.101
rpc_address: 192.168.56.101
rpc_port: 9160

Update cassandra.yaml within our other nodes as well(VM2 and VM3).

cluster_name: Test Cluster
initial_token: 56713727820156410577229101238628035242
seed_provider:        
- seeds: "192.168.56.101" 
listen_address: 192.168.56.102
rpc_address: 192.168.56.102
rpc_port: 9160

cluster_name: Test Cluster
initial_token: 113427455640312821154458202477256070485
seed_provider:        
- seeds: "192.168.56.101" 
listen_address: 192.168.56.103
rpc_address: 192.168.56.103
rpc_port: 9160

Now we are good to init our cluster, just run bin/cassandra -f within  cassandra-1.1.3 directory in all three nodes. But in my setup I have to fulfill some other requirements as well.

1. Update JVM_OPTS="$JVM_OPTS -Xss280k" within conf/cassandra-env.sh.
2. Create /var/log/cassandra/system.log file & /var/lib/cassandra/ directories.

Lets check the status of our Cassandra cluster. Fulfill this task I'm going to use NodeTool by default shipped with Cassandra.

Run /nodetool -h 192.168.56.101 ring within apache-cassandra-1.1.3/bin directory,  and you will see something similar to this.

node1@node1-VirtualBox:~/apache-cassandra-1.1.3/bin$ ./nodetool -h 192.168.56.101 ring
Address         DC          Rack        Status State   Load            Effective-Ownership Token                                      
                                                                                                                                            6148914691236517205                        
192.168.56.101  datacenter1 rack1       Up     Normal  838.62 KB       100.00%             0                                          
192.168.56.102  datacenter1 rack1       Up     Normal  815.04 KB       100.00%             3074457345618258602                        
192.168.56.103  datacenter1 rack1       Up     Normal  823.84 KB       100.00%             6148914691236517205


Lets configure of BAM node inside VM1 now, download WSO2-BAM latest version from here[3]. I'm going to use WSO2 BAM 2.4.0 latest at the moment.

Update <BAM_HOME>/repository/conf/etc/cassandra-component.xml with the following content.Configure nodes correctly according to your environment.

<Cassandra>
    <Cluster>
       <Name>Test Cluster</Name>
       <DefaultPort>9160</DefaultPort>
       <Nodes>192.168.56.101:9160,192.168.56.102:9160,192.168.56.103:9160</Nodes>
       <AutoDiscovery disable="false" delay="1000"/>
    </Cluster>
</Cassandra>

Update <BAM_HOME>/repository/conf/advanced/streamdefn.xml with the following content.

<StreamDefinition>
    <NodeId>1</NodeId>

    <keySpaceName>EVENT_KS</keySpaceName>
    <eventIndexKeySpaceName>EVENT_INDEX_KS</eventIndexKeySpaceName>

    <ReplicationFactor>3</ReplicationFactor>
    <ReadConsistencyLevel>QUORUM</ReadConsistencyLevel>
    <WriteConsistencyLevel>QUORUM</WriteConsistencyLevel>
    <StrategyClass>org.apache.cassandra.locator.SimpleStrategy</StrategyClass>
</StreamDefinition>

For load-balancing purpose you can define WSO2BAM_CASSANDRA_DATASOURCE datasource as, check for comma separated JDBC URLs.

<datasource>
            <name>WSO2BAM_CASSANDRA_DATASOURCE</name>
            <description>The datasource used for Cassandra data</description>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:cassandra://192.168.56.101:9160/EVENT_KS,jdbc:cassandra://192.168.56.102:9160/EVENT_KS,jdbc:cassandra://192.168.56.103:9160/EVENT_KS</url>
                    <username>admin</username>
                    <password>admin</password>
                </configuration>
            </definition>
</datasource>

Make sure to update hostname localhost with correct IP address, 192.168.56.101 in my setup inside <BAM_HOME>/repository/conf/datasources/master-datasources.xml.

Lets start WSO2 BAM now. Since we need to point BAM to the external Cassandra cluster rather than using embedded Cassandra, run following

sh wso2server.sh -Ddisable.cassandra.server.startup=true

This disable.cassandra.server.startup=true parameter will disable embedded Cassandra during carbon server startup.

Lets run few samples available here[4] and see our setup works properly,

[1] http://cassandra.apache.org/download/
[2] http://www.datastax.com/docs/1.1/initialize/token_generation
[3] http://wso2.com/products/business-activity-monitor/
[4] http://docs.wso2.org/display/BAM240/KPI+Monitoring+Sample