If you are transitioning from a relational database and SQL, you will find Cassandra Query Language (CQL) easy to use for accessing the Cassandra server. CQL has a syntax similar to SQL and can be used from a command line shell (cqlsh) or from client APIs such as the Hector API introduced in Chapter 1, “Using Cassandra with Hector.” Although Cassandra is a NoSQL database, Cassandra’s data model is similar to a relational database with a storage model based on column families, columns, and rows. Instead of querying a relational database table, you query a column family. Instead of querying relational database columns and rows, you query Cassandra’s columns and rows. This chapter introduces CQL using the Hector client API for running CQL statements. Another API that supports CQL may be used just as well for running the CQL statements.
CQL 3 is the latest version of CQL. Being a query language for a non-relational database, some constructs used in SQL are not supported in CQL—for example, JOINS
. CQL 3 identifiers are case-insensitive unless enclosed in double quotes. CQL 3 keywords are also case-insensitive. An identifier in CQL is a letter followed by any sequence of letters, digits, and the underscore. A string literal in CQL is specified with single quotes, and to use a single quotation mark in a query, it must be delimited, or escaped, with another single quote. CQL 3 data types were discussed in Chapter 1. The CQL 3 commands are discussed in Table 2.1.
For a complete syntax of CQL 3 commands, see http://cassandra.apache.org/doc/cql3/CQL.html.
Note that not all Java clients support CQL 3. For example, Hector does not support CQL 3, but supports CQL 2.0 (http://cassandra.apache.org/doc/cql/CQL.html). Subsequent sections discuss most CQL 2 statements with an example. Later in the chapter, we will discuss some of the new features in CQL 3. You will use the CQL 3 commands in Chapter 3, “Using Cassandra with DataStax Java Driver,” on the DataStax Java driver.
You will use Hector Java client to run CQL statements. Download the following software:
Apache Cassandra apache-cassandra-2.0.4-bin.tar.gz or a later version from http://cassandra.apache.org/download/
Hector Java client hector-core-1.1-4.jar or a later version from https://github.com/hector-client/hector/downloads
Eclipse IDE for Java EE developers from http://www.eclipse.org/downloads/
Java SE 7 from http://www.oracle.com/technetwork/java/javase/downloads/index-jsp-138363.html
Then follow these steps:
1. Install the Eclipse IDE.
2. Extract the Apache Cassandra TAR file to a directory (for example, C:Cassandraapache-cassandra-2.0.4).
3. Add the bin folder, C:Cassandraapache-cassandra-2.0.4in, to the PATH
environment variable.
4. Start Apache Cassandra server with the following command: cassandra
–f
The Cassandra server starts and begins listening for CQL clients on localhost:9042. Cassandra listens for Thrift clients on localhost:9160.
In this section, you will create a Java project in Eclipse for running CQL statements using a Hector client. Follow these steps:
1. Select File > New > Other in the Eclipse IDE.
2. In the New window select the Java Project wizard and click Next, as shown in Figure 2.1.
3. In the Create a Java Project screen, specify a project name (for example, CQL), select the directory location for the project (or choose Use Default Location), select the JRE, and click Next, as shown in Figure 2.2.
4. In the Java Settings screen, select the default settings and click Finish, as shown in Figure 2.3. A new Java project is created in Eclipse, as shown in the Package Explorer (see Figure 2.4).
5. You need to add the same JAR files to the project Java build path as for the Chapter 1 project. To begin, right-click the project node in the Package Explorer and select Properties.
6. In the Properties for CQL dialog box, select the Java Build Path node and click the Add External JARs button to add JAR files, as shown in Figure 2.5. Then click OK.
7. Add a Java class for the Hector client application. Select File > New > Other and, in the New window, select Java > Class, as shown in Figure 2.6. Then click Next.
8. In the New Java Class wizard, select the source folder (CQL/src), specify a package (cql), and specify a class name (CQLClient
). Then select the main
method stub to create and click Finish, as shown in Figure 2.7. The CQLClient
Java class is created and added to the Eclipse Java project, as shown in the Package Explorer in Figure 2.8.
In CQL 3, the syntax for creating a keyspace is as follows:
CREATE KEYSPACE <keyspace_name> WITH <property1> = {} AND <property2> = {};
The properties supported are discussed in Table 2.2.
If SimpleStrategy
is used as the replication strategy, an example of a command to create a keyspace is as follows:
CREATE KEYSPACE CQLKeyspace WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 1} AND durable_writes = false;
The 'replication_factor'
sub-option can be used only with SimpleStrategy
. If NetworkTopologyStrategy
is used, an example of a command to create a keyspace is as follows:
CREATE KEYSPACE CQLKeyspace WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1' : 1, 'DC2' : 1} AND durable_writes = true;
The DC1
and DC2
refer to the data centers DC1 and DC2. The sub-option values are the individual replication factors for each data center.
The CQL 2 syntax for creating a keyspace is as follows:
CREATE KEYSPACE <ks_name> WITH strategy_class = <value> [ AND strategy_options:<option> = <value> [strategy_options:<option> = <value>]];
For example:
CREATE KEYSPACE CQLKeyspace WITH strategy_class = 'SimpleStrategy' AND strategy_options:replication_factor = 1;
If Cassandra CLI (client interface utility) is used to create a keyspace, the syntax of the CREATE KEYSPACE
command is different than that discussed. Cassandra CLI does not completely support CQL, and the Thrift API is supported. To create a keyspace in Cassandra CLI, start Cassandra CLI with the following command:
cassandra-cli
Run the following command to create a keyspace with the name CQLKeyspace
, the replication strategy SimpleStrategy
, and a replication factor of 1:
CREATE KEYSPACE CQLKeyspace WITH placement_strategy= 'org.apache.cassandra. locator.SimpleStrategy' AND strategy_options={replication_factor:1};
A keyspace CQLKeyspace
is created and the output from the command is shown in Figure 2.9. To use the keyspace run the following command:
use CQLKeyspace;
As indicated by the message output in Figure 2.9, the CQLKeyspace
is authenticated.
You will use the Hector client to run the CQL statement to create a column family. To begin, add a createCF()
method to the CQLClient
class and invoke the method from the main
method. Hector provides the me.prettyprint.cassandra.model.CqlQuery
class to run CQL statements. The constructor for the class is CqlQuery(Keyspace k, Serializer<K>
keySerializer,Serializer<N> nameSerializer, Serializer<V> valueSerializer)
. But, before you may run CQL statements, you need to create a Cluster
instance and a Keyspace
instance as discussed in Chapter 1.
Cluster cluster = HFactory.getOrCreateCluster("cql-cluster", "localhost:9160"); Keyspace keyspace = HFactory.createKeyspace("CQLKeyspace", cluster);
Create a CQLQuery
instance using the class constructor with StringSerializer
instances for key, column name, and column value.
CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get());
Next, set the CQL query to create a column family. Set the comparator as UTF8Type
, which implies that columns are sorted based on UTF8Type
sorting and columns are displayed as UTF8Type
text. The other supported types are AsciiType
, BytesType
(the default), CounterColumnType
, IntegerType
, LexicalUUIDType
and LongType
. The default validation class is set using the default_validation
parameter set to UTF8Type
and is the validator to use for column values. The supported types and default setting are the same as for the comparator.
cqlQuery.setQuery("CREATE COLUMNFAMILY catalog (catalog_id text PRIMARY KEY, journal text,publisher text,edition text,title text,author text)WITH comparator=UTF8Type AND default_validation_class=UTF8Type");
Some of the other supported options are discussed in Table 2.3, all of the column family options being optional. Only the column family name is a required parameter.
To run the CQL query, invoke the execute()
method:
cqlQuery.execute();
The CQLClient application to create a column family catalog using the Hector client to run the CQL statement appears in Listing 2.1.
Listing 2.1 CQLClient
Application
package cql; import me.prettyprint.cassandra.model.CqlQuery; import me.prettyprint.cassandra.serializers.StringSerializer; import me.prettyprint.hector.api.Cluster; import me.prettyprint.hector.api.Keyspace; import me.prettyprint.hector.api.factory.HFactory; public class CQLClient { private static Cluster cluster; private static Keyspace keyspace; public static void main(String[] args) { cluster = HFactory.getOrCreateCluster("cql-cluster", "localhost:9160"); createKeyspace(); createCF(); } private static void createKeyspace() { keyspace = HFactory.createKeyspace("CQLKeyspace", cluster); } private static void createCF() { CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("CREATE COLUMNFAMILY catalog (catalog_id text PRIMARY KEY,journal text,publisher text,edition text,title text,author text) WITH comparator=UTF8Type AND default_validation=UTF8Type AND caching=keys_only AND replicate_on_write=true"); cqlQuery.execute(); } }
If it’s not already started, start Cassandra, right-click CQLClient, and select Run As > Java Application as shown in Figure 2.10.
The "catalog"
column family is created in the CQLKeyspace
keyspace. (This keyspace must be created prior to running the CQL statement to create the column family.) In subsequent sections, you will add other methods to the CQLClient
class to run CQL statements and invoke the methods from the main
method. The primary key column of the "catalog"
column family is named something other than KEY
, which makes it unsuitable for being specified in the WHERE
clause of CQL 2 queries, as you will see in a later section. To create a primary key column called KEY
, run the following CQL query:
cqlQuery.setQuery("CREATE COLUMNFAMILY catalog2 (KEY text PRIMARY KEY,journal text,publisher text,edition text,title text,author text)");
One of the columns must be a primary key column. If a primary key is not specified, the following exception is generated:
InvalidRequestException(why:You must specify a PRIMARY KEY)
INSERT
STATEMENTIn this section, you will run the INSERT
CQL statement. The syntax for the INSERT
statement with the required clauses is as follows:
INSERT INTO <tablename> (<column1>, <column2>, <column>) VALUES (<value1>, <value2>, <valueN>)
The number of values must match the number of columns or the following exception is generated:
InvalidRequestException(why:unmatched column names/values)
However, the number of columns/values may be less than in the schema for the column family. The primary key column must be specified, as the primary key identifies a row. Add an insert()
method to the CQLClient
class and invoke the method from the main
method. Create a CQLQuery
object as before.
CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get());
Set the query to add a row to the catalog table using the setQuery(String)
method:
cqlQuery.setQuery("INSERT INTO catalog (catalog_id, journal, publisher, edition, title,author) VALUES ('catalog1','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Engineering as a Service','David A. Kelly')");
Then run the query with the execute()
method:
cqlQuery.execute();
Similarly, add another row:
cqlQuery.setQuery("INSERT INTO catalog (catalog_id, journal, publisher, edition, title,author) VALUES ('catalog2','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Quintessential and Collaborative','Tom Haunert')"); cqlQuery.execute();
The INSERT
statement adds a new row if one does not exist and replaces the row if a row with the same primary key already exists. Run the CQLClient application to invoke the insert()
method and add data to the "catalog"
column family. Then add the rows in Cassandra CLI. To fetch the row with the "catalog1"
and "catalog2"
keys, run the following commands:
get catalog['catalog1']; get catalog['catalog2'];
The output from the command fetches the rows added with the INSERT
statement, as shown in Figure 2.11.
You add a row to a column family with the name KEY
in a similar manner:
cqlQuery.setQuery("INSERT INTO catalog2 (KEY, journal, publisher, edition,title, author) VALUES ('catalog1','Oracle Magazine', 'Oracle Publishing', 'November- December 2013', 'Engineering as a Service','David A. Kelly')"); cqlQuery.execute();
When a row is added, all the columns/values do not have to be specified. For example, the following CQL query adds a row without the journal
column. Flexible schema is one of the features of the Cassandra database and of NoSQL databases in general.
cqlQuery.setQuery("INSERT INTO catalog (catalog_id, publisher, edition,title, author) VALUES ('catalog4', 'Oracle Publishing', 'November-December 2013', 'Engineering as a Service','David A. Kelly')"); cqlQuery.execute();
SELECT
STATEMENTIn this section, you will query using the SELECT
statement. The SELECT
statement must have the following required clauses and keywords:
SELECT <select-clause> FROM <tablename>
The SELECT
statement queries one or more columns from one or more rows and returns the result as a rowset, with each row having the columns specified in the query. Even if a column name not defined in the column family schema is specified in the SELECT
statement’s <select-clause>
, the column value is returned—a null
value for a non-existent column. The columns whose values are to be selected are specified in the <select-clause>
as comma-separated column names. Alternatively, to select all columns, specify *
. The <tablename>
is the column family or table from which to select.
Add a method called select()
to the CQLClient application and invoke the method from the main
method. Then create a CQLQuery
object as before.
CqlQuery<String, String, String> cqlQuery = new CqlQuery<String, String, String> (keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get());
As an example, select all columns using *
:
cqlQuery.setQuery("select * from catalog");
Invoke the execute()
method to run the CQL statement. The result of the query is returned as a QueryResult<CqlRows<K, N, V>>
object.
QueryResult<CqlRows<String, String, String>> result = cqlQuery.execute();
Fetch the result using the get()
method and create an Iterator
over the result using the iterator()
method.
Iterator iterator = result.get().iterator();
Iterate over the result to fetch individual rows. A row is represented with the Row
interface, and a Row
instance consists of a key/column slice tuple. Get the key value using the getKey()
method and get the column slice represented with the ColumnSlice
interface using the getColumnSlice()
method. Fetch the collection of columns from the ColumnSlice
instance using the getColumns
method. Create another Iterator
over the list of columns and iterate over the columns to fetch individual HColumn
instances, which represent the columns in the column slice. Output the column name using the getName()
method from HColumn
and output the column value using the getValue()
method.
while (iterator.hasNext()) { Row row = (Row) iterator.next(); String key = (String) row.getKey(); ColumnSlice columnSlice = row.getColumnSlice(); List columnList = columnSlice.getColumns(); Iterator iter = columnList.iterator(); while (iter.hasNext()) { HColumn column = (HColumn) iter.next(); System.out.println("Column name: " + column.getName() + " "); System.out.println("Column Value: " + column.getValue()); System.out.println(" "); } }
Run the CQLClient application to fetch all the column values from the catalog table. The catalog1
row columns are output as shown in Figure 2.12.
The catalog2
row columns are output as shown in Figure 2.13.
The SELECT
statement also supports a WHERE
clause to filter a query based on the value of another column.
SELECT <select-clause> FROM <tablename> WHERE <where-clause>
CQL requires that the WHERE
clause with the =
comparison be used with the table key alone or an indexed column alone. The column in the =
comparison after WHERE
must either be the primary key column called KEY
or some other column that has a secondary index. Before we discuss how to filter a SELECT
query using the WHERE
clause, let’s add a secondary index on a column.
CQL provides the CREATE INDEX
command to create a secondary index on a column already defined in a column family. For example, the following command will add a secondary index called titleIndex
on column called title
in table called catalog
.
All existing data for the column is indexed asynchronously. When new data is added, it is indexed automatically at the time of insertion.
CREATE INDEX titleIndex ON catalog (title)
Add a createIndex()
method to the CQLClient
class to create a secondary index on a column. Then specify and run the preceding CQL query using a CQLQuery
instance.
cqlQuery.setQuery("CREATE INDEX titleIndex ON catalog (title)"); cqlQuery.execute();
Invoke the createIndex()
method in the main
method and run the CQLClient application to create a secondary index on the title
column in the catalog
table.
SELECT
STATEMENT WITH THE WHERE
CLAUSEAs mentioned, CQL requires the column in an = comparison specified in the WHERE
clause to be an indexed column or a primary key column called KEY
. If you run a CQL query using the WHERE
clause on a primary key column that is not called KEY
or on some other column that has not been indexed, the following exception is generated:
Caused by: InvalidRequestException(why:No indexed columns present in by-columns clause with "equals" operator)
The following CQLQuery
query would generate the preceding exception because catalog_id
used with the =
operator is not an indexed column, and even though it is a primary key column, it is not called KEY
.
cqlQuery.setQuery("SELECT catalog_id, journal, publisher, edition,title,author FROM catalog WHERE catalog_id='catalog1'");
The same goes for the following query because the journal column used in the =
comparison is not an indexed column.
cqlQuery.setQuery("SELECT KEY, journal, publisher, edition,title,author FROM catalog WHERE journal='Oracle Magazine'");
Because you created a secondary index on the title
column in the catalog
table, you can use the title
column in the =
comparison after the WHERE
clause:
cqlQuery.setQuery("SELECT catalog_id, journal, publisher, edition,title,author FROM catalog WHERE title='Engineering as a Service'");
For example, if catalog1
is the only column with the title “Engineering as a Service,” then the preceding query would generate the following result using the same iteration over the QueryResult<CqlRows<String, String, String>>
result returned by the query:
Column name: catalog_id Column Value: catalog1 Column name: journal Column Value: Oracle Magazine Column name: publisher Column Value: Oracle Publishing Column name: edition Column Value: November-December 2013 Column name: title Column Value: Engineering as a Service Column name: author Column Value: David A. Kelly
The SELECT
statement with the WHERE
clause may also be used with the KEY
column in the =
comparison—for example, to select the columns where KEY
is catalog1
.
cqlQuery.setQuery("SELECT KEY, journal, publisher, edition,title,author FROM catalog2 WHERE KEY='catalog1'");
The result of the query is shown in Figure 2.14.
UPDATE
STATEMENTThe UPDATE
statement is used to update the column values of row(s). You update a row using an UPDATE
CQL statement. The syntax of the UPDATE
statement is as follows:
UPDATE <tablename> ( USING <option> ( AND <option> )* )? SET <assignment1> ( ',' <assignmentN> )* WHERE <where-clause>;
Add a method called update()
to the CQLClient
class. Then set the CQL UPDATE
statement as the query in the CQLQuery
object.
cqlQuery.setQuery("UPDATE catalog USING CONSISTENCY ALL SET 'edition' = '11/12 2013', 'author' = 'Kelley, David A.' WHERE CATALOG_ID = 'catalog1'");
The column in the WHERE
clause to select the row must be the primary key column. If some other column is used, the following exception is generated:
Caused by: InvalidRequestException(why:Expected key 'CATALOG_ID' to be present in WHERE clause for 'catalog')
UPDATE
does not try to determine whether the row identified by the primary key column exists. If the row does not exist, a row is created. Run a SELECT
query after the UPDATE
statement. The result of the query indicates that the columns were updated, as shown in Figure 2.15.
BATCH
STATEMENTThe BATCH
statement is used to run a group of modification statements (insertions, updates, deletions) in a batch as a single statement. Only UPDATE
, INSERT
, and DELETE
statements may be grouped in a BATCH
statement. Running multiple statements as a single statement saves round trips between the client and the server. The syntax of the BATCH
statement is as follows:
BEGIN BATCH (USING <option> ( AND <option> )* )? <modification-stmt> ( ';' <modification-stmt> )* APPLY BATCH;
Add a method called batch()
to the CQLClient
class. Set a BATCH
statement in the CQLQuery
object. The BATCH
statement includes two INSERT
statements and two UPDATE
statements.
cqlQuery.setQuery("BEGIN BATCH USING CONSISTENCY QUORUM UPDATE catalog SET 'edition' = '11/12 2013', 'author' = 'Haunert, Tom' WHERE CATALOG_ID = 'catalog2' INSERT INTO catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog3','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','') INSERT INTO catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog4','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','') UPDATE catalog SET 'edition' = '11/12 2013' WHERE CATALOG_ID = 'catalog3' APPLY BATCH");
The consistency level cannot be set for individual statements within a BATCH
statement. If the consistency level is set on individual statements, the following error is generated:
Caused by: InvalidRequestException(why:Consistency level must be set on the BATCH, not individual statements)
Invoke the batch()
method from the main
method and run the CQLClient
class in the Eclipse IDE. All the statements grouped in the BATCH
statement are run and applied. Next, invoke the select()
method after the batch()
method to output all the columns in all the rows. The result of the query, shown here, indicates that the BATCH
statement has been applied.
Result took (38195us) for query (me.prettyprint.cassandra.model.CqlQuery@65b57dc c) on host: localhost(127.0.0.1):9160 Column name: catalog_id Column Value: catalog1 Column name: author Column Value: Kelley, David A. Column name: edition Column Value: 11/12 2013 Column name: journal Column Value: Oracle Magazine Column name: publisher Column Value: Oracle Publishing Column name: title Column Value: Engineering as a Service Column name: catalog_id Column Value: catalog2 Column name: author Column Value: Haunert, Tom Column name: edition Column Value: 11/12 2013 Column name: journal Column Value: Oracle Magazine Column name: publisher Column Value: Oracle Publishing Column name: title Column Value: Quintessential and Collaborative Column name: catalog_id Column Value: catalog3 Column name: author Column Value: Column name: edition Column Value: 11/12 2013 Column name: journal Column Value: Oracle Magazine Column name: publisher Column Value: Oracle Publishing Column name: title Column Value: Column name: catalog_id Column Value: catalog4 Column name: author Column Value: Column name: edition Column Value: November-December 2013 Column name: journal Column Value: Oracle Magazine Column name: publisher Column Value: Oracle Publishing Column name: title Column Value:
DELETE
STATEMENTThe DELETE
statement is used to delete columns and rows. The syntax of the DELETE
statement is as follows:
DELETE ( <selection> ( ',' <selection> )* )? FROM <tablename> WHERE <where-clause>
The <selection>
items refer to the columns to be deleted. The column in the WHERE
clause must be the primary key column. If no column is specified, all the columns are deleted. The row itself is not deleted because the primary key column is not deleted even if the primary column is specified in the <selection>
items. Add a method called delete()
to CQLClient
class. Then set a query to delete the journal
and publisher
columns from the catalog
table from the row with the primary key "catalog3"
.
cqlQuery.setQuery("DELETE journal, publisher from catalog WHERE catalog_id='catalog3'"); cqlQuery.execute();
Next, set a query to delete all columns from the catalog
table from the row with the primary key "catalog4"
.
cqlQuery.setQuery("DELETE from catalog WHERE catalog_id='catalog4'"); cqlQuery.execute();
To demonstrate that the primary key column cannot be deleted, include the catalog_id
column in the columns to delete:
cqlQuery.setQuery("DELETE catalog_id, journal, publisher, edition, title, author from catalog WHERE catalog_id='catalog4'"); cqlQuery.execute();
Invoke the delete()
method from the main
method and run the CQLClient
class in the Eclipse IDE. Then invoke the select()
method after the delete()
method to query the rows after deletion. As shown in the Eclipse IDE in Figure 2.16, the journal
and publisher
columns are deleted from the catalog3
row, and all the columns have been deleted from the catalog4
row. The primary key column is not deleted.
ALTER COLUMNFAMILY
STATEMENTThe ALTER COLUMNFAMILY
or ALTER TABLE
statement is used to alter the column family definitions to add columns, drop columns, change the type of existing columns, and update the table options. The syntax of the statement is as follows:
ALTER (TABLE | COLUMNFAMILY) <tablename> <instruction>
The <instruction>
supports the alterations using the keywords discussed in Table 2.4.
Add updateCF()
and updateCF2()
methods to the CQLClient
class. In the updateCF()
method, change the column type of the edition
column to int
in the catalog
table using statement ALTER COLUMNFAMILY catalog ALTER edition TYPE int
.
cqlQuery.setQuery("ALTER COLUMNFAMILY catalog ALTER edition TYPE int"); cqlQuery.execute();
Invoke the updateCF()
method in the main
method and run the CQLClient
class. The edition
column type gets changed to int
. The value in the edition
column is still of type text
. A subsequent select()
method returns the value of the edition
column as text
. In updateCF2()
, change the type of the edition
column back to text
.
cqlQuery.setQuery("ALTER COLUMNFAMILY catalog ALTER edition TYPE text"); cqlQuery.execute();
If a column type is modified, a column value that was previously addable becomes nonaddable. For example, set the column type of the journal
column to int
:
cqlQuery.setQuery("ALTER COLUMNFAMILY catalog ALTER journal TYPE int"); cqlQuery.execute();
Next, add a journal
column value of type text
:
cqlQuery.setQuery("INSERT INTO catalog (catalog_id, journal, publisher, edition, title,author) VALUES ('catalog5','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','')"); cqlQuery.execute();
The following exception is generated, indicating that the text
value cannot be added to an int
type column:
HInvalidRequestException: InvalidRequestException(why:unable to make int from 'Oracle Magazine')
The DROP TABLE
or DROP COLUMNFAMILY
statement may be used to drop a column family, including all the data in the column family. Add a dropCF()
method to the CQLClient
class. Then set the query on a CQLQuery
object to be DROP COLUMNFAMILY catalog
, which would drop the catalog
column family.
cqlQuery.setQuery("DROP COLUMNFAMILY catalog"); cqlQuery.execute();
Next, invoke the dropCF()
method from the main
method and run the CQLClient application. The catalog
column family gets dropped. If only the table data is to be removed but not the table, use the TRUNCATE
statement:
TRUNCATE <tablename>
You can use the DROP KEYSPACE
statement to drop a keyspace:
DROP KEYSPACE <identifier>
Add a dropKeyspace()
method to drop a keyspace. Drop the CQLKeyspace
by setting the CQLQuery
object query to DROP KEYSPACE CQLKeyspace
.
cqlQuery.setQuery("DROP KEYSPACE CQLKeyspace"); cqlQuery.execute();
Invoke the dropKeyspace()
method from the main
method and run the CQLClient application to drop the CQLKeyspace
. The execute()
method must be invoked after you set a query with setQuery()
. Queries do not get added to the CQLQuery
object so they can be run in a batch. If a keyspace is used after it has been dropped, the following error is generated:
Caused by: InvalidRequestException(why:Keyspace 'CQLKeyspace' does not exist)
The CQLClient application appears in Listing 2.2. Some of the method invocations in the main
method have been commented out and should be uncommented as required to run individually or in sequence.
Listing 2.2 The CQLClient Application
package cql; import java.util.Iterator; import java.util.List; import me.prettyprint.cassandra.model.CqlQuery; import me.prettyprint.cassandra.model.CqlRows; import me.prettyprint.cassandra.serializers.StringSerializer; import me.prettyprint.hector.api.Cluster; import me.prettyprint.hector.api.Keyspace; import me.prettyprint.hector.api.beans.ColumnSlice; import me.prettyprint.hector.api.beans.HColumn; import me.prettyprint.hector.api.beans.Row; import me.prettyprint.hector.api.factory.HFactory; import me.prettyprint.hector.api.query.QueryResult; public class CQLClient { private static Cluster cluster; private static Keyspace keyspace; public static void main(String[] args) { cluster = HFactory.getOrCreateCluster("cql-cluster", "localhost:9160"); /*Some of the method invocations in the main method have been commented out and should be uncommented as required to run individually or in sequence. */ createKeyspace(); createCF(); //insert(); //select(); //createIndex(); //selectFilter(); //update(); //select(); //batch(); //select(); //delete(); //update2(); //select(); //updateCF(); //select(); //updateCF2(); //dropCF(); //dropKeyspace(); } /*Creates a Cassandra keyspace*/ private static void createKeyspace() { keyspace = HFactory.createKeyspace("CQLKeyspace", cluster); } /*Drops a Cassandra keyspace*/ private static void dropKeyspace() { CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("DROP KEYSPACE CQLKeyspace"); cqlQuery.execute(); } /*Creates an index*/ private static void createIndex() { CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("CREATE INDEX titleIndex ON catalog (title)"); cqlQuery.execute(); } /*Creates a column family*/ private static void createCF() { CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("CREATE COLUMNFAMILY catalog (catalog_id text PRIMARY KEY,journal text,publisher text,edition text,title text,author text) WITH comparator=UTF8Type AND default_validation=UTF8Type AND caching=keys_only AND replicate_on_write=true"); cqlQuery.execute(); cqlQuery.setQuery("CREATE COLUMNFAMILY catalog2 (KEY text PRIMARY KEY,journal text,publisher text,edition text,title text,author text)"); cqlQuery.execute(); } /*Adds data to a column family*/ private static void insert() { CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("INSERT INTO catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog1','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Engineering as a Service','David A. Kelly')"); cqlQuery.execute(); cqlQuery.setQuery("INSERT INTO catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog2','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Quintessential and Collaborative','Tom Haunert')"); cqlQuery.execute(); cqlQuery.setQuery("INSERT INTO catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog3','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Engineering as a Service','David A. Kelly')"); cqlQuery.execute(); cqlQuery.setQuery("INSERT INTO catalog (catalog_id, publisher, edition,title,author) VALUES ('catalog4', 'Oracle Publishing', 'November- December 2013', 'Engineering as a Service','David A. Kelly')"); cqlQuery.execute(); cqlQuery.setQuery("INSERT INTO catalog2 (KEY, journal, publisher, edition,title,author) VALUES ('catalog1','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Engineering as a Service','David A. Kelly')"); cqlQuery.execute(); } /*Selects data from a column family*/ private static void select() { CqlQuery<String, String, String> cqlQuery = new CqlQuery<String, String, String>( keyspace, StringSerializer.get(), StringSerializer. get(), StringSerializer.get()); cqlQuery.setQuery("select * from catalog"); QueryResult<CqlRows<String, String, String>> result = cqlQuery .execute(); System.out.println(result); Iterator iterator = result.get().iterator(); while (iterator.hasNext()) { Row row = (Row) iterator.next(); String key = (String) row.getKey(); ColumnSlice columnSlice = row.getColumnSlice(); List columnList = columnSlice.getColumns(); Iterator iter = columnList.iterator(); while (iter.hasNext()) { HColumn column = (HColumn) iter.next(); System.out.println("Column name: " + column.getName() + " "); System.out.println("Column Value: " + column.getValue()); System.out.println(" "); } } } /*Selects data from a column family using a WHERE clause*/ private static void selectFilter() { CqlQuery<String, String, String> cqlQuery = new CqlQuery<String, String, String>( keyspace, StringSerializer.get(), StringSerializer. get(), StringSerializer.get()); //cqlQuery.setQuery("SELECT catalog_id, journal, publisher, edition,title,author FROM catalog WHERE title='Engineering as a Service'"); cqlQuery.setQuery("SELECT journal, publisher, edition,title, author FROM catalog2 WHERE KEY='catalog1'"); //cqlQuery.setQuery("SELECT catalog_id, journal, publisher, edition,title,author FROM catalog WHERE catalog_id='catalog1'");//Generates exception QueryResult<CqlRows<String, String, String>> result = cqlQuery .execute(); System.out.println(result); Iterator iterator = result.get().iterator(); while (iterator.hasNext()) { Row row = (Row) iterator.next(); String key = (String) row.getKey(); ColumnSlice columnSlice = row.getColumnSlice(); List columnList = columnSlice.getColumns(); Iterator iter = columnList.iterator(); while (iter.hasNext()) { HColumn column = (HColumn) iter.next(); System.out.println("Column name: " + column.getName() + " "); System.out.println("Column Value: " + column.getValue()); System.out.println(" "); } } } /*Updates a row or rows of data in a column family*/ private static void update() { CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("UPDATE catalog USING CONSISTENCY ALL SET 'edition' = '11/12 2013', 'author' = 'Kelley, David A.' WHERE CATALOG_ID = 'catalog1'"); cqlQuery.execute(); } /*Updates a row or rows of data in a column family*/ private static void update2() { CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("UPDATE catalog USING CONSISTENCY ALL SET 'edition' = 'November-December 2013', 'author' = 'Kelley, David A.' WHERE CATALOG_ID = 'catalog1'"); cqlQuery.execute(); } /*Deletes columns from a row or rows of data in a column family*/ private static void delete() { CqlQuery cqlQuery = new CqlQuery<String, String, String>(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("DELETE journal, publisher from catalog WHERE catalog_id='catalog3'"); cqlQuery.execute(); cqlQuery.setQuery("DELETE from catalog WHERE catalog_id='catalog4'"); cqlQuery.execute(); cqlQuery.setQuery("DELETE catalog_id, journal, publisher, edition, title, author from catalog WHERE catalog_id='catalog4'"); cqlQuery.execute(); } /*Runs multiple statements in a batch*/ private static void batch() { CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("BEGIN BATCH USING CONSISTENCY QUORUM UPDATE catalog SET 'edition' = '11/12 2013', 'author' = 'Haunert, Tom' WHERE CATALOG_ID = 'catalog2' INSERT INTO catalog (catalog_id, journal, publisher, edition,title, author) VALUES ('catalog3','Oracle Magazine', 'Oracle Publishing', 'November- December 2013', '','') INSERT INTO catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog4','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','') UPDATE catalog SET 'edition' = '11/12 2013' WHERE CATALOG_ID = 'catalog3' APPLY BATCH"); cqlQuery.execute(); } /*Updates a column family*/ private static void updateCF() { CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("ALTER COLUMNFAMILY catalog ALTER edition TYPE int"); cqlQuery.execute(); } /*Updates a column family*/ private static void updateCF2() { CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("ALTER COLUMNFAMILY catalog ALTER edition TYPE text"); cqlQuery.execute(); cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("ALTER COLUMNFAMILY catalog ALTER journal TYPE int"); cqlQuery.execute(); /* CF gets updated with column to a type different from column value*/ cqlQuery.setQuery("INSERT INTO catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog5','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','')"); cqlQuery.execute(); } /*Drops a column family*/ private static void dropCF() { CqlQuery cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get()); cqlQuery.setQuery("DROP COLUMNFAMILY catalog"); cqlQuery.execute(); } }
CQL 3 has added support for several new features and is backward-compatible. The keyword COLUMNFAMILY
has been replaced with TABLE
. Some of the salient new features are discussed next.
The CREATE TABLE
command has added a provision for a multiple column primary key, also called a compound primary key. The CREATE COLUMNFAMILY
example in this chapter makes use of a simple primary key—a primary key with only one column. A compound primary key for the catalog
table may be declared as follows:
CREATE TABLE catalog ( catalog_id text, journal text, edition text, title text, author text, PRIMARY KEY (catalog_id, journal) );
The preceding statement creates a table using the catalog_id
and journal
columns to form a compound primary key. A table that has a compound primary key must have at least one column that is not included in the primary key.
To run an INSERT
statement on a table with a compound primary key, each of the columns in the primary key must be specified. In addition, at least one of the non–primary key columns must be specified.
The WHERE
clause may specify each of the columns in the compound primary key using AND
as follows:
UPDATE catalog SET 'edition' = 'November-December 2013', 'author' = 'Kelley, David A.' WHERE CATALOG_ID = 'catalog1' AND journal='Oracle Magazine';
If a compound primary key is used in a WHERE
clause, key-component columns other than the first may have a >
(greater than) or <
(less than) comparison. If all the preceding key-component columns have been identified with an =
comparison, the last key-component may specify any kind of relation.
The CREATE
statements for KEYSPACE
, TABLE
, and INDEX
support an IF NOT EXISTS
condition. In CQL 2.0, the CREATE
statement for KEYSPACE
, TABLE
, and INDEX
throws an exception if the construct already exists.
CREATE KEYSPACE IF NOT EXISTS CQLKeyspace WITH replication = { 'class': 'SimpleStrategy','replication_factor' : 1 }; CREATE TABLE IF NOT EXISTS catalog (catalog_id text PRIMARY KEY,journal text, publisher text,edition text,title text,author text);
The DROP
statements support an IF EXISTS
condition:
DROP KEYSPACE IF EXISTS CQLKeyspace;
The INSERT
statement supports an IF NOT EXISTS
condition. CQL 3 has added the provision to add a new row only if a row by the same primary key value does not already exist. The CQL 3 clause to add conditionally is IF NOT EXISTS
. In CQL 2, the INSERT
statement was run even if a row by the same primary key was already defined. The following CQL 3 statement adds a row only if a row identified by catalog1
does not exist:
INSERT INTO catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog1','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Engineering as a Service','David A. Kelly') IF NOT EXISTS;
The UPDATE
statement supports an IF
condition:
UPDATE table_name USING option1 AND optionN SET assignment1 , assignmentN WHERE <where-clause> IF column_name1 = literal AND column_nameN = literal
The columns in the IF
clause may be different from the columns to be updated. The IF
condition incurs a negligible performance overhead, as Paxos is used internally. Paxos is a consensus protocol for a distributed system.
This chapter introduced Cassandra Query Language (CQL), including the CQL commands. You used CQL 2 queries with the Hector Java client to add, select, update, and delete data from a Cassandra column family. You also discovered the salient new features in CQL 3. The next chapter discusses the DataStax Java driver, which supports CQL 3.