Cassandra Database(5)Update Version and DataStax

Cassandra Database(5)Update Version and DataStax

1. Install and Update Version
There is no wget on MAC, so I use this command to get the latest binary file.
>curl -O http://apache.mirror.quintex.com/cassandra/2.0.4/apache-cassandra-2.0.4-bin.tar.gz

Or I can install the wget first.
>curl -O http://ftp.gnu.org/gnu/wget/wget-1.15.tar.gz

>tar -zxvf wget-1.15.tar.gz
>cd wget-1.15
>./configure --with-ssl=openssl
>make
>sudo make install

Verify the installation
>wget —version

Or 
>wget —help

>wget http://apache.mirror.quintex.com/cassandra/2.0.4/apache-cassandra-2.0.4-bin.tar.gz
>tar zxvf apache-cassandra-2.0.4-bin.tar.gz
>mv apache-cassandra-2.0.4 /Users/carl/tool/
>sudo ln -s /Users/carl/tool/apache-cassandra-2.0.4 /opt/cassandra-2.0.4
>sudo ln -s /opt/cassandra-2.0.4 /opt/cassandra

>sudo mkdir /var/lib/cassandra
>sudo chown -R carl /var/lib/cassandra

Try to start the cassandra DB
>cassandra -f ./conf/cassandra.yaml

2. Using cqlsh
I can use this command
>cqlsh

Alternatively
>cqlsh host port

It will show as follow:
Connected to Test Cluster at localhost:9160. [cqlsh 4.1.0 | Cassandra 2.0.4 | CQL spec 3.1.1 | Thrift protocol 19.39.0] Use HELP for help

cqlsh>help

It will give me all the command list.

Create and use a demo keyspace
cqlsh> create keyspace mykeyspace with replication = { 'class': 'SimpleStrategy', 'replication_factor':1};

cqlsh> use mykeyspace;

cqlsh:mykeyspace>

The latest version of cassandra, since we have CQLSH, it is supporting something like SQL, that is cool.

Create table in the keyspace
cqlsh:mykeyspace> create table users ( user_id int primary key, firstname text, lastname text );

Directly use the SQL statement is fine here.
cqlsh:mykeyspace> insert into users(user_id, firstname, lastname) values (1,'carl','luo');

cqlsh:mykeyspace> insert into users(user_id, firstname, lastname) values (2,'ray','luo');

cqlsh:mykeyspace> insert into users(user_id, firstname, lastname) values (3,'kiko','kang');

cqlsh:mykeyspace> select * from users;  user_id | firstname | lastname ---------+-----------+----------      

1 |      carl |      luo      

2 |       ray |      luo      

3 |      kiko |     kang

Build the index
create index on users (lastname);

Then we can use the where clause
select * from users where lastname = 'luo';

User_ID is primary key
cqlsh:mykeyspace> select * from users where user_id = 1;

Here is more detail about the SQL 
http://cassandra.apache.org/doc/cql3/CQL.html
http://www.datastax.com/documentation/cql/3.0/webhelp/index.html

3. Writing our Application
https://github.com/datastax
https://github.com/datastax/java-driver
http://www.datastax.com/documentation/developer/java-driver/1.0/webhelp/index.html

Download the java driver and take a look at it.
>git clone https://github.com/datastax/java-driver.git
>mvn clean install
>mvn eclipse:eclipse

And I import these project with my Eclipse, there are 3 projects cassandra-driver-core, cassandra-driver-dse, cassandra-driver-examples-stress.

I am using the latest driver in the build.sbt
"com.datastax.cassandra" % "cassandra-driver-core" % "2.0.0-rc2"

There is one thing need to pay attention. There are 2 ports, one is old RPC port 9160 which support cassandra-cli and cqlsh.
Another port is New binary port 9042 which support the java library datastax.

Schema Demo

Here is the sample codes for connect to the DB and insert data
package com.sillycat.easycassandraserver.apps

import com.datastax.driver.core.Cluster
import com.datastax.driver.core.exceptions.AlreadyExistsException
import com.datastax.driver.core._
import org.slf4j.LoggerFactory
import scala.collection.JavaConversions._

object CassandraDataStaxSchemaApp extends App{

  val log = LoggerFactory.getLogger("CassandraConfig")

  val hosts = “carl.sillycat.com"
  val nativePort = 9042  //9042, 9160

  val keyspaceName = "books"
  val columnFamilyName = "books"
  val replicationStrategy = "SimpleStrategy"
  val replicationFactor = 2

  val compactionStrategy = "LeveledCompactionStrategy"

  val keyspaceCql = s"""
         create keyspace $keyspaceName
         with replication = { 'class': '$replicationStrategy', 'replication_factor': $replicationFactor }
         """

  val tableCql = s"""
    create table $columnFamilyName (
      brandCode text,
      deviceId text,
      unixtime bigint,
      notes text,
      primary key ((brandCode, deviceId), unixtime)
    ) with compact storage
      and compression = { 'sstable_compression' : '' }
      and compaction = { 'class' : '$compactionStrategy', 'sstable_size_in_mb' : 10 }
      and clustering order by (unixtime desc)
        """

  lazy val cluster: Cluster = {
    val b = Cluster.builder().
      addContactPoints(hosts.split(","): _*).
      withPort(nativePort)
    val option = new SocketOptions()
    option.setReadTimeoutMillis(12000)
    b.withSocketOptions(option)
    val c = b.build()
    val s = c.connect()
    try {
      s.execute(keyspaceCql)
    } catch {
      case x: AlreadyExistsException => log.info(x.getMessage)
    }
    c
  }

  lazy val session: Session = {
    val s = cluster.connect(keyspaceName)
    try {
      s.execute(tableCql)
    } catch {
      case x: AlreadyExistsException => log.info(x.getMessage)
    }
    s
  }

  //insert
  val insertSQL = session.prepare(
    """
      | insert into books ( brandCode, deviceId, unixtime, notes) values ( ?, ?, ?, ? )
    """.stripMargin)
  session.execute(insertSQL.bind("sillycat","iphone5", 1L:java.lang.Long, "There is a book there."))
  session.execute(insertSQL.bind("sillycat","iphone5", 2L:java.lang.Long, "I update the os to 6.0"))
  session.execute(insertSQL.bind("sillycat","iphone5", 3L:java.lang.Long, "I update the os to 7.0"))
  session.execute(insertSQL.bind("sillycat","android", 1L:java.lang.Long, "I update the os 2.1"))
  session.execute(insertSQL.bind("sillycat","itouch", 2L:java.lang.Long, "I update the os 2.2"))

  cluster.shutdown()
}

The Query Demo

package com.sillycat.easycassandraserver.apps

import org.slf4j.LoggerFactory
import com.datastax.driver.core.{Session, SocketOptions, Cluster}
import com.datastax.driver.core.exceptions.AlreadyExistsException
import scala.collection.JavaConversions._

object CassandraDataStaxQueryApp extends App{

  val log = LoggerFactory.getLogger("CassandraConfig")

  val hosts = "carl.sillycat.com"
  val nativePort = 9042  //9042, 9160

  val keyspaceName = "books"
  val columnFamilyName = "books"
  val replicationStrategy = "SimpleStrategy"
  val replicationFactor = 2

  val compactionStrategy = "LeveledCompactionStrategy"

  val keyspaceCql = s"""
         create keyspace $keyspaceName
         with replication = { 'class': '$replicationStrategy', 'replication_factor': $replicationFactor }
         """

  val tableCql = s"""
    create table $columnFamilyName (
      brandCode text,
      deviceId text,
      unixtime bigint,
      notes text,
      primary key ((brandCode, deviceId), unixtime)
    ) with compact storage
      and compression = { 'sstable_compression' : '' }
      and compaction = { 'class' : '$compactionStrategy', 'sstable_size_in_mb' : 10 }
      and clustering order by (unixtime desc)
        """

  lazy val cluster: Cluster = {
    val b = Cluster.builder().
      addContactPoints(hosts.split(","): _*).
      withPort(nativePort)
    val option = new SocketOptions()
    option.setReadTimeoutMillis(12000)
    b.withSocketOptions(option)
    val c = b.build()
    val s = c.connect()
    try {
      s.execute(keyspaceCql)
    } catch {
      case x: AlreadyExistsException => log.info(x.getMessage)
    }
    c
  }

  lazy val session: Session = {
    val s = cluster.connect(keyspaceName)
    try {
      s.execute(tableCql)
    } catch {
      case x: AlreadyExistsException => log.info(x.getMessage)
    }
    s
  }

  //query1
  val querySQL1 = session.prepare("""
      select notes from books where brandCode = ? and deviceId = ?
      limit 1
      """)
  val result1 = session.execute(querySQL1.bind("sillycat", "iphone5"))
  val resultString1 = result1.one().getString("notes")
  println("resultString1 = " + resultString1)

  //query2
  val querySQL2 = session.prepare("""
      select notes from books where brandCode = ? and deviceId = ?
                                  """)
  val result2 = session.execute(querySQL2.bind("sillycat", "iphone5"))

  result2.all().foreach{ row=>
    println("resultString2 = " + row.getString("notes"))
  }

  cluster.shutdown()
}



References:
http://cassandra.apache.org/doc/cql/CQL.html
http://cassandra.apache.org/download/
http://cassandra.apache.org/doc/cql3/CQL-1.2.html
http://cassandra.apache.org/doc/cql3/CQL.html

Install wget
http://osxdaily.com/2012/05/22/install-wget-mac-os-x/

Cassandra 1 
http://sillycat.iteye.com/blog/1870661

http://stackoverflow.com/questions/16783725/error-while-connecting-to-cassandra-using-java-driver-for-apache-cassandra-1-0-f