Specifying the data to retrieve from a Cassandra scripted data source
To specify what data rows to retrieve from a Cassandra database system, you write a query using CQL. In the BIRT Report Designer interface, you can specify the following types of clauses to create your CQL query:
open
describe
fetch
close
beforeOpen
beforeClose
onFetch
afterOpen
afterClose
How to specify what data to retrieve from a Cassandra data source
In this procedure, use the following open clause in a CQL query:
importPackage(Packages.java.util);
importPackage(Packages.me.prettyprint.cassandra.serializers);
importPackage(Packages.me.prettyprint.cassandra.service);
importPackage(Packages.me.prettyprint.hector.api);
importPackage(Packages.me.prettyprint.hector.api.beans);
importPackage(Packages.me.prettyprint.hector.api.factory);
importPackage(Packages.me.prettyprint.hector.api.query);
importPackage(Packages.me.prettyprint.cassandra.model);
var cluster = HFactory.getOrCreateCluster("Test Cluster",new CassandraHostConfigurator("192.168.218.246:9160"));
var keyspace = HFactory.createKeyspace("users", cluster);
var cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get());
cqlQuery.setQuery("select * from User");
var resultCQL = cqlQuery.execute();
rowsIterator = resultCQL.get().iterator();
Also use the following fetch clause:
if (rowsIterator.hasNext()) {
var myrow = rowsIterator.next();
var cols = myrow.getColumnSlice().getColumns();
for( ii=0; ii < cols.size(); ii++ ){
row[cols.get(ii).getName()] = cols.get(ii).getValue();
}
return true;
}else{
return false;
}
1 In Data Explorer, right-click Data Sets, then choose New Data Set.
2 In New Data Set, specify the following information:
1 In Data Source Selection, select the Cassandra data source to use. Data Set Type displays HQL Select Query.
2 In Data Set Name, type a name for the data set.
3 Choose Next.
3 In Output columns, choose Add and add the necessary output columns to retrieve from the data source. For each output column, you can specify the following values on New Script Data Set Column, as shown in
Figure 6‑10:
Name
Type
Alias
Analysis type
Display Name
Display Name Key
When finished specifying the data set column properties, choose OK.
Figure 6‑10 Adding an output column
The specified output columns appear in New Data Set, as shown in
Figure 6‑11. In this example, the output columns are first, last, and age.
Figure 6‑11 Specifying output columns to retrieve
4 Choose Finish.
5 In Script, select a type of clause from the drop-down menu, and specify the necessary CQL clause. For example:
1 In Script, select open, and specify a CQL clause similar to the following one, as shown in
Figure 6‑12:
importPackage(Packages.java.util);
importPackage(Packages.me.prettyprint.cassandra.serializers);
importPackage(Packages.me.prettyprint.cassandra.service);
importPackage(Packages.me.prettyprint.hector.api);
importPackage(Packages.me.prettyprint.hector.api.beans);
importPackage(Packages.me.prettyprint.hector.api.factory);
importPackage(Packages.me.prettyprint.hector.api.query);
importPackage(Packages.me.prettyprint.cassandra.model);
var cluster = HFactory.getOrCreateCluster("Test Cluster",new CassandraHostConfigurator("192.168.218.246:9160"));
var keyspace = HFactory.createKeyspace("users", cluster);
var cqlQuery = new CqlQuery(keyspace, StringSerializer.get(), StringSerializer.get(), StringSerializer.get());
cqlQuery.setQuery("select * from User");
var resultCQL = cqlQuery.execute();
rowsIterator = resultCQL.get().iterator();
Figure 6‑12 Specifying the open clause
2 In Script, select fetch, and specify a CQL clause similar to the following one, as shown in
Figure 6‑13:
if (rowsIterator.hasNext()) {
var myrow = rowsIterator.next();
var cols = myrow.getColumnSlice().getColumns();
for( ii=0; ii < cols.size(); ii++ ){
row[cols.get(ii).getName()] = cols.get(ii).getValue();
}
return true;
}else{
return false;
}
Figure 6‑13 Specifying the fetch clause
3 Choose Preview Results to view the data rows returned by the data set.
