CQL for client applications

Exclusive offer: get 50% off this eBook here
Instant Cassandra Query Language [Instant]

Instant Cassandra Query Language [Instant] — Save 50%

A practical, step-by-step guide for quickly getting started with Cassandra Query Language with this book and ebook

$14.99    $7.50
by Amresh Singh | October 2013 | Open Source

In this article, by Amresh Singh, the author of Instant Cassandra Query Language discusses about how CQL can be used for client applications.

CQL 3 is being pushed as a standard for building applications over Cassandra. Running CQL queries on the shell is fine for viewing and modifying the data for testing purpose, but the actual use of CQL lies with building applications just the way SQL is used with JDBC.

Next, we are going to explain the three ways using which you can build Java applications over Cassandra (it's possible to build applications in other programming languages as well via appropriate drivers and clients).

(For more resources related to this topic, see here.)

Using the Thrift API

The Thrift library is based on the Thrift RPC protocol. High-level clients built over it have been a standard way of building an application for a long time. In this section, we'll explain how to write a client application using CQL as the query language and thrift as the Java API.

When we start Cassandra, by default it listens to Thrift clients (start_rpc: true property in the CASSANDRA_HOME/conf/cassandra.yaml file enables this). Let's build a small program that connects to Cassandra using the Thrift API, and runs CQL 3 queries for reading/writing data in the UserProfiles table we created for the facebook application. The program can be built by performing the following steps:

  1. For downloading the Thrift Library, you need to enter apache-assandra-thrift-1.2.x.jar (which is to be found in the CASSANDRA_HOME/lib folder) into your classpath. If your Java project is mavenized, you need to insert the following entry in pom.xml under the dependency section (version will vary depending upon your Cassandra server installation):

    <dependency> <groupId>org.apache.cassandra</groupId> <artifactId>cassandra-thrift</artifactId> <version>1.2.5</version> </dependency>

  2. For connecting to the Cassandra server on a given host and port, you need to open org.apache.thrift.transport.TTransport to the Cassandra node and create an instance of org.apache.cassandra.thrift.Cassandra.Client as follows:

    TTransport transport = new TFramedTransport(new TSocket("localhost", 9160)); TProtocol protocol = new TBinaryProtocol(transport); Cassandra.Client client = new Cassandra.Client(protocol); transport.open(); client.set_cql_version("3.0.0");

    The default CQL version for Thrift is 2.0.0. You must set it to 3.0.0 if you are writing CQL 3 queries and don't want to see any version related errors.

    After you are done with transport, close it gracefully (usually at the end of read/write operations) as follows:

    transport.close();

  3. Creating a schema : The executeQuery() utility method accepts String CQL 3 query and runs it:

    CqlResult executeQuery(String query) throws Exception { return client.execute_cql3_query(ByteBuffer.wrap(query.getBytes("UTF-8")), Compression.NONE, ConsistencyLevel.ONE); }

    Now, create keyspace and the table by directly executing CQL 3 query:

    //Create keyspace executeQuery("CREATE KEYSPACE facebook WITH replication = "{'class':'SimpleStrategy','replication_factor':3};"); executeQuery("USE facebook;"); //Create table executeQuery("CREATE TABLE UserProfiles(" +"email_id text," + "password text,"+ "name text," + "age int," + "profile_picture blob," + "PRIMARY KEY(email_id)" + ");" );

  4. Reading/writing data: A couple of records can be inserted as follows:

    executeQuery("USE facebook;"); executeQuery("INSERT INTO UserProfiles(email_id, password, name, age, profile_picture) VALUES('john.smith@example.com','p4ssw0rd',' John Smith',32,0x8e37);"); executeQuery("INSERT INTO UserProfiles(email_id, password, name, age, profile_picture) VALUES('david.bergin@example.com','guess1t',' David Bergin',42,0xc9f1);");

    Executing the SELECT query returns CQLResult, on which we can iterate easily to fetch records:

    CqlResult result = executeQuery("SELECT * FROM facebook.UserProfiles " + "WHERE email_id = 'john.smith@example.com';"); for (CqlRow row : result.getRows()) { System.out.println(row.getKey(); }

Using the Datastax Java driver

The Datastax Java driver is based on the Cassandra binary protocol that was introduced in Cassandra 1.2, and works only with CQL 3.

The Cassandra binary protocol is specifically made for Cassandra in contrast to Thrift, which is a generic framework and has many limitations.

Now, we are going to write a Java program that uses the Datastax Java driver for reading/writing data into Cassandra, by performing the following steps:

  • Downloading the driver library : This driver library JAR file must be in your classpath in order to build an application using it. If you have a maven-based Java project, you need to insert the following entry into the pom.xml file under the dependeny section:

    <dependency> <groupId>com.datastax.cassandra</groupId> <artifactId>cassandra-driver-core</artifactId> <version>1.0.1</version> </dependency>

    This driver project is hosted on Github: (https://github.com/datastax/java-driver). It makes sense to check and download the latest version.

  • Configuring Cassandra to listen to native clients : In the newer version of Cassandra, this would be enabled by default and Cassandra will listen to clients using binary protocol. But the earlier Cassandra installations may require enabling this. All you have to do is to check and enable the start_native_transport property into the CASSANDRA_HOME/conf/Cassandra.yaml file by inserting/uncommenting the following line:

    start_native_transport: true

    The port that Cassandra will use for listening to native clients is determined by the native_transport_port property.

    It is possible for Cassandra to listen to both Thrift and native clients simultaneously. If you want to disable Thrift, just set the start_rpc property to false in CASSANDRA_HOME/conf/Cassandra.yaml.

  • Connecting to Cassandra : The com.datastax.driver.core.Cluster class is the entry point for clients to connect to the Cassandra cluster:

    Cluster cluster = Cluster.builder().addContactPoint("127.0.0.1").build();

    After you are done with using it (usually when application shuts down), close it gracefully:

    cluster.shutdown();

  • Creating a session : An object of com.datastax.driver.core.Session allows you to execute a CQL 3 statement. The following line creates a Session instance:

    Session session = cluster.connect();

  • Creating a schema : Before reading/writing data, let's create a keyspace and a table similar to UserProfiles in the facebook application we built earlier:

    // Create Keyspace session.execute("CREATE KEYSPACE facebook WITH replication = " + "{'class':'SimpleStrategy','replication_factor':1};"); session.execute("USE facebook"); // Create table session.execute("CREATE TABLE UserProfiles(" + "email_id text," + "password text,"+ "name text," + "age int," + "profile_picture blob," + "PRIMARY KEY(email_id)" + ");" );

  • Reading/writing data : We can insert a couple of records as follows:

    session.execute("USE facebook"); session.execute("INSERT INTO UserProfiles(email_id, password, name, age, profile_picture) VALUES('john.smith@example.com','p4ssw0rd','John Smith',32,0x8e37);"); session.execute("INSERT INTO UserProfiles(email_id, password, name, age, profile_picture) VALUES('david.bergin@example.com','guess1t','David Bergin',42,0xc9f1);");

  • Finding and printing records : A SELECT query returns an instance of com.datastax.driver.core.ResultSet. You can fetch individual rows by iterating over it using the com.datastax.driver.core.Row object:

    ResultSet results = session.execute ("SELECT * FROM facebook.UserProfiles " + "WHERE email_id = 'john.smith@example.com';"); for (Row row : results) { System.out.println ("Email: " + row.getString("email_id") + "\tName: " + row.getString("name")+ "\t Age : " + row.getInt("age")); }

  • Deleting records : We can delete a record as follows:

    session.execute("DELETE FROM facebook.UserProfiles WHERE email_id='john.smith@example.com';");

Using high-level clients

In addition to the libraries based on Thrift and binary protocols, some high-level clients are built with the purpose to ease development and provide additional services, such as connection pooling, load balancing, failover, secondary indexing, and so on. Some of them are listed here:

Summary

From this article, we basically learn about using CQL in queries using three different preceding methods.

Resources for Article :


Further resources on this subject:


Instant Cassandra Query Language [Instant] A practical, step-by-step guide for quickly getting started with Cassandra Query Language with this book and ebook
Published: September 2013
eBook Price: $14.99
See more
Select your format and quantity:

About the Author :


Amresh Singh

Amresh Singh is an engineering graduate and has more than nine years of software development experience in Java SE/EE technologies. He has vast industry experience in serverside web development using Struts, JSF, Spring, Hibernate, RDBMS, and so on. He is core committer in the open source project, Kundera, which is a JPA 2.0-based object-data store mapping library for NoSQL databases, such as Cassandra, HBase, MongoDB, and OracleNoSQL.

He has worked with reputed development and consulting IT organizations in business domains ranging from telecom to finance, and insurance. He is currently working as Lead Developer in Innovation Labs of Impetus Infotech (India) Pvt. Ltd.

He has undertaken speaking engagements in Cloud Camp. He loves writing, and is a DZone Most Valuable Blogger. You can follow his blog at http://xamry.wordpress.com/.

Books From Packt


Instant Apache Cassandra for Developers Starter
Instant Apache Cassandra for Developers Starter

Getting Started with NoSQL
Getting Started with NoSQL

Apache Tomcat 7 Essentials
Apache Tomcat 7 Essentials

Cassandra High Performance Cookbook: Second Edition
Cassandra High Performance Cookbook: Second Edition

Cassandra High Performance Cookbook
Cassandra High Performance Cookbook

Microsoft SQL Server 2008 R2 Administration Cookbook
Microsoft SQL Server 2008 R2 Administration Cookbook

Mastering Apache Cassandra
Mastering Apache Cassandra

Getting Started with Oracle Event Processing 11g
Getting Started with Oracle Event Processing 11g


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Z
D
5
u
K
W
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software