Category Archives: cassandra

[DEPRECATED] CentOS 6: Install Datastax Cassandra OpsCenter Community Edition

Versions

CentOS 6.4
Oracle Java JDK 1.6
OpsCenter Community 3.0.2

Prerequisites

Install

1. Edit /etc/yum.repos.d/datastax.repo

[datastax]
name= DataStax Repo for Apache Cassandra
baseurl=http://rpm.datastax.com/community
enabled=1
gpgcheck=0

[datastax]

name= DataStax Repo for Apache Cassandra

baseurl=http://rpm.datastax.com/community

enabled=1

gpgcheck=0

2. Install Opscenter Free

sudo yum install opscenter-free

1	sudo yum install opscenter-free

Configure

Note: by default opscener will only accept connections from 127.0.0.1:8888. This guide won’t change that setting but it can be changed by editing /etc/opscenter/opscenterd.conf

1. Start opscenter:

sudo service opscenterd start

1	sudo service opscenterd start

4. Start cassandra on boot:

sudo chkconfig opscenterd on

1	sudo chkconfig opscenterd on

Test

1. Reconnect ssh session and tunnel the default opscenter port (8888):

ssh <hostname> -L 8888:127.0.0.1:8888

1	ssh <hostname> -L 8888:127.0.0.1:8888

2. Connect to 127.0.0.1:8888 with a browser
3. Click “Use Existing Cluster”

4. Enter a seed cluster node hostname or IP
Note: you can enter a new-line separated list of all nodes, but this is unnecessary

5. Click “Save cluster”

Sources

http://www.datastax.com/docs/opscenter/index

[DEPRECATED] When WSO2 Data Services Server 3.0.1 and Cassandra 1.1 CQL 2 JDBC driver — doesn’t support BLOB

Leave a reply

Attempted to use the following CQL 2 schema with two BLOBs:

CREATE KEYSPACE chf WITH strategy_class = 'SimpleStrategy' AND strategy_options\
:replication_factor = 1;

USE chf;

CREATE TABLE measurements (
  measurement_uuid text PRIMARY KEY,
  patient_uuid text,
  age_years double,
  answers_csv text,
  blood_glucose_milligrams_per_deciliter double,
  blood_pressure_diastolic_millimeters_mercury double,
  blood_pressure_systolic_millimeters_mercury double,
  electrocardiogram_1khz_16bit blob,
  height_inches double,
  oxygen_saturation_milligrams_per_litre double,
  pain_scale_0_to_10 int,
  photoplethysmogram_1khz_16bit blob,
  pulse_beats_per_minute double,
  pulse_oximetry_percent double,
  questions_csv text,
  respiratory_rate_breaths_per_minute double,
  weight_pounds double
);

CREATE INDEX ON measurements(patient_uuid);

CREATE TABLE patients (
  patient_uuid text PRIMARY KEY,
  external_id text,
  password_hash text
);

CREATE INDEX ON patients(external_id);

INSERT INTO patients (patient_uuid,external_id,password_hash) VALUES ('d73771e0\
-97eb-11e2-9e96-0800200c9a66','1','test');

INSERT INTO measurements (measurement_uuid,patient_uuid,age_years) VALUES ('f0c\
f1c70-97eb-11e2-9e96-0800200c9a66','d73771e0-97eb-11e2-9e96-0800200c9a66',99);

CREATE KEYSPACE chf WITH strategy_class = 'SimpleStrategy' AND strategy_options\

:replication_factor = 1;

USE chf;

CREATE TABLE measurements (

measurement_uuid text PRIMARY KEY,

patient_uuid text,

age_years double,

answers_csv text,

blood_glucose_milligrams_per_deciliter double,

blood_pressure_diastolic_millimeters_mercury double,

blood_pressure_systolic_millimeters_mercury double,

electrocardiogram_1khz_16bit blob,

height_inches double,

oxygen_saturation_milligrams_per_litre double,

pain_scale_0_to_10 int,

photoplethysmogram_1khz_16bit blob,

pulse_beats_per_minute double,

pulse_oximetry_percent double,

questions_csv text,

respiratory_rate_breaths_per_minute double,

weight_pounds double

);

CREATE INDEX ON measurements(patient_uuid);

CREATE TABLE patients (

patient_uuid text PRIMARY KEY,

external_id text,

password_hash text

);

CREATE INDEX ON patients(external_id);

INSERT INTO patients (patient_uuid,external_id,password_hash) VALUES ('d73771e0\

-97eb-11e2-9e96-0800200c9a66','1','test');

INSERT INTO measurements (measurement_uuid,patient_uuid,age_years) VALUES ('f0c\

f1c70-97eb-11e2-9e96-0800200c9a66','d73771e0-97eb-11e2-9e96-0800200c9a66',99);

With the following DSS configuration:

<data name="chf_insert">
   <config id="cassandra_chf">
      <property name="url">jdbc:cassandra://localhost:9160/chf</property>
      <property name="driverClassName">org.apache.cassandra.cql.jdbc.CassandraDriver</property>
   </config>
   <query id="insert_new_patient" useConfig="cassandra_chf">
      <sql>INSERT INTO patients (patient_uuid,external_id,password_hash) VALUES (?,?,?)</sql>
      <param name="patient_uuid" sqlType="STRING"/>
      <param name="external_id" sqlType="STRING"/>
      <param name="password_hash" sqlType="STRING"/>
   </query>
   <query id="insert_new_measurement" useConfig="cassandra_chf">
      <sql>INSERT INTO measurements (measurement_uuid,age_years,answers_csv,blood_glucose_milligrams_per_deciliter,blood_pressure_diastolic_millimeters_mercury,blood_pressure_systolic_millimeters_mercury,electrocardiogram_1khz_16bit,height_inches,oxygen_saturation_milligrams_per_litre,pain_scale_0_to_10,patient_uuid,photoplethysmogram_1khz_16bit,pulse_beats_per_minute,pulse_oximetry_percent,questions_csv,respiratory_rate_breaths_per_minute,weight_pounds) VALUES (:measurement_uuid,:age_years,:answers_csv,:blood_glucose_milligrams_per_deciliter,:blood_pressure_diastolic_millimeters_mercury,:blood_pressure_systolic_millimeters_mercury,:electrocardiogram_1khz_16bit,:height_inches,:oxygen_saturation_milligrams_per_litre,:pain_scale_0_to_10,:patient_uuid,:photoplethysmogram_1khz_16bit,:pulse_beats_per_minute,:pulse_oximetry_percent,:questions_csv,:respiratory_rate_breaths_per_minute,:weight_pounds)</sql>
      <param defaultValue="NaN" name="age_years" sqlType="DOUBLE"/>
      <param defaultValue="null" name="answers_csv" sqlType="STRING"/>
      <param defaultValue="NaN" name="blood_glucose_milligrams_per_deciliter" sqlType="DOUBLE"/>
      <param defaultValue="NaN" name="blood_pressure_diastolic_millimeters_mercury" sqlType="DOUBLE"/>
      <param defaultValue="NaN" name="blood_pressure_systolic_millimeters_mercury" sqlType="DOUBLE"/>
      <param defaultValue="0" name="electrocardiogram_1khz_16bit" sqlType="BLOB"/>
      <param defaultValue="NaN" name="height_inches" sqlType="DOUBLE"/>
      <param name="measurement_uuid" sqlType="STRING"/>
      <param defaultValue="NaN" name="oxygen_saturation_milligrams_per_litre" sqlType="DOUBLE"/>
      <param defaultValue="-1" name="pain_scale_0_to_10" sqlType="INTEGER"/>
      <param name="patient_uuid" sqlType="STRING"/>
      <param defaultValue="null" name="photoplethysmogram_1khz_16bit" sqlType="BLOB"/>
      <param defaultValue="NaN" name="pulse_beats_per_minute" sqlType="DOUBLE"/>
      <param defaultValue="NaN" name="pulse_oximetry_percent" sqlType="DOUBLE"/>
      <param defaultValue="0" name="questions_csv" sqlType="STRING"/>
      <param defaultValue="NaN" name="respiratory_rate_breaths_per_minute" sqlType="DOUBLE"/>
      <param defaultValue="NaN" name="weight_pounds" sqlType="DOUBLE"/>
   </query>
   <operation name="insert_new_patient" returnRequestStatus="true">
      <call-query href="insert_new_patient">
         <with-param name="patient_uuid" query-param="patient_uuid"/>
         <with-param name="external_id" query-param="external_id"/>
         <with-param name="password_hash" query-param="password_hash"/>
      </call-query>
   </operation>
   <operation name="insert_new_measurement" returnRequestStatus="true">
      <call-query href="insert_new_measurement">
         <with-param name="age_years" query-param="age_years"/>
         <with-param name="answers_csv" query-param="answers_csv"/>
         <with-param name="blood_glucose_milligrams_per_deciliter" query-param="blood_glucose_milligrams_per_deciliter"/>
         <with-param name="blood_pressure_diastolic_millimeters_mercury" query-param="blood_pressure_diastolic_millimeters_mercury"/>
         <with-param name="blood_pressure_systolic_millimeters_mercury" query-param="blood_pressure_systolic_millimeters_mercury"/>
         <with-param name="electrocardiogram_1khz_16bit" query-param="electrocardiogram_1khz_16bit"/>
         <with-param name="height_inches" query-param="height_inches"/>
         <with-param name="measurement_uuid" query-param="measurement_uuid"/>
         <with-param name="oxygen_saturation_milligrams_per_litre" query-param="oxygen_saturation_milligrams_per_litre"/>
         <with-param name="pain_scale_0_to_10" query-param="pain_scale_0_to_10"/>
         <with-param name="patient_uuid" query-param="patient_uuid"/>
         <with-param name="photoplethysmogram_1khz_16bit" query-param="photoplethysmogram_1khz_16bit"/>
         <with-param name="pulse_beats_per_minute" query-param="pulse_beats_per_minute"/>
         <with-param name="pulse_oximetry_percent" query-param="pulse_oximetry_percent"/>
         <with-param name="questions_csv" query-param="questions_csv"/>
         <with-param name="respiratory_rate_breaths_per_minute" query-param="respiratory_rate_breaths_per_minute"/>
         <with-param name="weight_pounds" query-param="weight_pounds"/>
      </call-query>
   </operation>
</data>

<property name="url">jdbc:cassandra://localhost:9160/chf</property>

<property name="driverClassName">org.apache.cassandra.cql.jdbc.CassandraDriver</property>

</config>

<sql>INSERT INTO patients (patient_uuid,external_id,password_hash) VALUES (?,?,?)</sql>

</query>

<sql>INSERT INTO measurements (measurement_uuid,age_years,answers_csv,blood_glucose_milligrams_per_deciliter,blood_pressure_diastolic_millimeters_mercury,blood_pressure_systolic_millimeters_mercury,electrocardiogram_1khz_16bit,height_inches,oxygen_saturation_milligrams_per_litre,pain_scale_0_to_10,patient_uuid,photoplethysmogram_1khz_16bit,pulse_beats_per_minute,pulse_oximetry_percent,questions_csv,respiratory_rate_breaths_per_minute,weight_pounds) VALUES (:measurement_uuid,:age_years,:answers_csv,:blood_glucose_milligrams_per_deciliter,:blood_pressure_diastolic_millimeters_mercury,:blood_pressure_systolic_millimeters_mercury,:electrocardiogram_1khz_16bit,:height_inches,:oxygen_saturation_milligrams_per_litre,:pain_scale_0_to_10,:patient_uuid,:photoplethysmogram_1khz_16bit,:pulse_beats_per_minute,:pulse_oximetry_percent,:questions_csv,:respiratory_rate_breaths_per_minute,:weight_pounds)</sql>

</query>

<call-query href="insert_new_patient">

<with-param name="patient_uuid" query-param="patient_uuid"/>

<with-param name="external_id" query-param="external_id"/>

<with-param name="password_hash" query-param="password_hash"/>

</call-query>

</operation>

<call-query href="insert_new_measurement">

<with-param name="age_years" query-param="age_years"/>

<with-param name="answers_csv" query-param="answers_csv"/>

<with-param name="blood_glucose_milligrams_per_deciliter" query-param="blood_glucose_milligrams_per_deciliter"/>

<with-param name="blood_pressure_diastolic_millimeters_mercury" query-param="blood_pressure_diastolic_millimeters_mercury"/>

<with-param name="blood_pressure_systolic_millimeters_mercury" query-param="blood_pressure_systolic_millimeters_mercury"/>

<with-param name="electrocardiogram_1khz_16bit" query-param="electrocardiogram_1khz_16bit"/>

<with-param name="height_inches" query-param="height_inches"/>

<with-param name="measurement_uuid" query-param="measurement_uuid"/>

<with-param name="oxygen_saturation_milligrams_per_litre" query-param="oxygen_saturation_milligrams_per_litre"/>

<with-param name="pain_scale_0_to_10" query-param="pain_scale_0_to_10"/>

<with-param name="patient_uuid" query-param="patient_uuid"/>

<with-param name="photoplethysmogram_1khz_16bit" query-param="photoplethysmogram_1khz_16bit"/>

<with-param name="pulse_beats_per_minute" query-param="pulse_beats_per_minute"/>

<with-param name="pulse_oximetry_percent" query-param="pulse_oximetry_percent"/>

<with-param name="questions_csv" query-param="questions_csv"/>

<with-param name="respiratory_rate_breaths_per_minute" query-param="respiratory_rate_breaths_per_minute"/>

<with-param name="weight_pounds" query-param="weight_pounds"/>

</call-query>

</operation>

</data>

But received the following error:

Caused by: javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQL\
Query.processNormalQuery'
DS Code: DATABASE_ERROR
Source Data Service:-
Name: chf_insert
Location: /chf_insert.dbs
Description: N/A
Default Namespace: http://ws.wso2.org/dataservice
Current Request Name: insert_new_measurement
Current Params: {age_years=13, patient_uuid=d73771e0-97eb-11e2-9e96-0800200c9a6\
6, measurement_uuid=48a69dc0-99b7-11e2-9e96-0800200c9a66}
Nested Exception:-
DS Fault Message: Error in 'createProcessedPreparedStatement'
DS Code: UNKNOWN_ERROR
Nested Exception:-
java.sql.SQLFeatureNotSupportedException: the Cassandra implementation does not\
 support this method

Caused by: javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQL\

Query.processNormalQuery'

DS Code: DATABASE_ERROR

Source Data Service:-

Name: chf_insert

Location: /chf_insert.dbs

Description: N/A

Default Namespace: http://ws.wso2.org/dataservice

Current Request Name: insert_new_measurement

Current Params: {age_years=13, patient_uuid=d73771e0-97eb-11e2-9e96-0800200c9a6\

6, measurement_uuid=48a69dc0-99b7-11e2-9e96-0800200c9a66}

Nested Exception:-

DS Fault Message: Error in 'createProcessedPreparedStatement'

DS Code: UNKNOWN_ERROR

Nested Exception:-

java.sql.SQLFeatureNotSupportedException: the Cassandra implementation does not\

support this method

[DEPRECATED] WSO2 Data Services Server 3.0.1 doesn’t work with Cassandra CQL 3

2 Replies

WSO2 Data Services Server 3.0.1 comes with support for an unspecified Cassandra version. However, in their documentation they call out 1.1.X. After much frustration and many NullPointerExceptions from WSO2, I discovered that while Cassandra 1.1 supports CQL 3, it defaults to CQL 2 in the JDBC driver. I also noticed that despite general JDBC support in DSS, the Cassandra JDBC driver is bundled with DSS 3.0.1 and appears to have been customized by WSO2 to support specifically Cassandra 1.1 and DSS 3.0.1.

Tonight I attempted to use the Cassandra JDBC 1.2 driver with WSO2 DSS’s support for “generic” JDBC data sources. I was able to get DSS to connect to the Cassandra server, but running even a simple SELECT query failed with a NullPointerException. To meet my current objectives, I’ve rolled back Cassandra to 1.1 to be compatible with WSO2 DSS 3.0.1 and reverted to using CQL 2. This is a bit of a disappointment since CQL 2 doesn’t seem to have any support for leveraging Cassandra’s “schema-free” features that CQL 3 does (map, set, list, etc).

[DEPRECATED] CentOS 6: Install Apache Cassandra using Datastax

Versions

CentOS 6.3
Oracle Java JDK 1.6
Note: if running with WSO2 Data Services Server 3.0.1, only Apache Cassandra 1.1 is supported
Apache Cassandra 1.2.3 or 1.1.9
JNA 3.5.1

Prerequisites

WARNING: Cassandra will fail with no or an erroneous error message if run with openjdk 1.6!
Install Oracle JDK

Install

1. Edit /etc/yum.repos.d/datastax.repo

[datastax]
name= DataStax Repo for Apache Cassandra
baseurl=http://rpm.datastax.com/community
enabled=1
gpgcheck=0

[datastax]

name= DataStax Repo for Apache Cassandra

baseurl=http://rpm.datastax.com/community

enabled=1

gpgcheck=0

2. Install Cassandra

For Apache Cassandra 1.2.3:

sudo yum install cassandra12

1	sudo yum install cassandra12

For Apache Cassandra 1.1.9:

sudo yum install apache-cassandra11

1	sudo yum install apache-cassandra11

3. Install a copy of JNA jar just for Cassandra
Note: JNA from base repo is 3.2.4 while Cassandra requires 3.2.7+

cd /usr/share/cassandra/lib
sudo wget https://maven.java.net/content/repositories/releases/net/java/dev/jna/jna/3.5.1/jna-3.5.1.jar
sudo chown cassandra:cassandra jna-3.5.1.jar
sudo chown 755 jna-3.5.1.jar

cd /usr/share/cassandra/lib

sudo wget https://maven.java.net/content/repositories/releases/net/java/dev/jna/jna/3.5.1/jna-3.5.1.jar

sudo chown cassandra:cassandra jna-3.5.1.jar

sudo chown 755 jna-3.5.1.jar

4. Fix permissions

sudo chown -R cassandra:cassandra /var/lib/cassandra
sudo chown -R cassandra:cassandra /var/log/cassandra

1 2	sudo chown -R cassandra:cassandra /var/lib/cassandra sudo chown -R cassandra:cassandra /var/log/cassandra

Configure

1. Edit /etc/profile.d/cassandra.sh

export CASSANDRA_HOME=/usr/share/cassandra

1	export CASSANDRA_HOME=/usr/share/cassandra

2. Load into current session:

source /etc/profile.d/cassandra.sh

1	source /etc/profile.d/cassandra.sh

3. Edit the following settings in /etc/cassandra/conf/cassandra.yaml:

cluster_name: '<your cluster name>'
...
          - seeds: "<first node hostname or ip>"
...
listen_address: <hostname or ip>
...
rpc_address: 0.0.0.0

cluster_name: '<your cluster name>'

...

- seeds: "<first node hostname or ip>"

...

listen_address: <hostname or ip>

...

rpc_address: 0.0.0.0

Note: for more details about configuring and optimizing a multi-node cluster, see sources.

Example:

cluster_name: 'Lances Cassandra Cluster'
...
          - seeds: "192.168.0.1"
...
listen_address: 192.168.0.1
...
rpc_address: 0.0.0.0

cluster_name: 'Lances Cassandra Cluster'

...

- seeds: "192.168.0.1"

...

listen_address: 192.168.0.1

...

rpc_address: 0.0.0.0

4. Open ports

7199 for JMX
9160 for Cassandra Thrift
7000 for Cassandra Gossip

See Open a port for iptables

5. Start cassandra:

sudo service cassandra start

1	sudo service cassandra start

6. Start cassandra on boot:

sudo chkconfig cassandra on

1	sudo chkconfig cassandra on

Test

Test First Node

Note: only use this test on the first node of the cluster

cassandra-cli

1	cassandra-cli

Enter at cassandra-cli:

create keyspace TEST;
use TEST;
create column family Users with key_validation_class = 'UTF8Type' and comparator = 'UTF8Type' and default_validation_class = 'UTF8Type';
set Users[1234][name] = lance;
set Users[1234][password] = password;
get Users[1234];

create keyspace TEST;

use TEST;

create column family Users with key_validation_class = 'UTF8Type' and comparator = 'UTF8Type' and default_validation_class = 'UTF8Type';

set Users[1234][name] = lance;

set Users[1234][password] = password;

get Users[1234];

Verify:

=> (column=name, value=lance, timestamp=1364348576638000)
=> (column=password, value=password, timestamp=1364348576659000)
Returned 2 results.
Elapsed time: 33 msec(s).

=> (column=name, value=lance, timestamp=1364348576638000)

=> (column=password, value=password, timestamp=1364348576659000)

Returned 2 results.

Elapsed time: 33 msec(s).

Test Other Nodes

nodetool ring

1	nodetool ring

Output:

Address         DC          Rack        Status State   Load            Effective-Ownership Token                                       
                                                                                           56249068152829558755232038006353068538      
XXX.XXX.XXX.XXX     datacenter1 rack1       Up     Normal  245.69 KB       77.37%              17740317962663376050438259070624145933      
XXX.XXX.XXX.XXX     datacenter1 rack1       Up     Normal  50.42 KB        22.63%              56249068152829558755232038006353068538

Address DC Rack Status State Load Effective-Ownership Token

56249068152829558755232038006353068538

XXX.XXX.XXX.XXX datacenter1 rack1 Up Normal 245.69 KB 77.37% 17740317962663376050438259070624145933

XXX.XXX.XXX.XXX datacenter1 rack1 Up Normal 50.42 KB 22.63% 56249068152829558755232038006353068538

See Open a port for iptables