Tuesday, December 25, 2012

Exposing a Cassandra data source as a web service using WSO2 Data Services Server

WSO2 Data Services Server not only support for the relational data sources but also it has inbuilt support for the NoSQL data sources like Cassandra as well. Cassandra data source support has been added using CQL JDBC driver. So creating the data service for Cassandra data source is same as the creating a data service for RDBMS type data source. Only difference is you need to use CQL instead of SQL for querying the data sources.

Let's write a simple data service to perform CRUD operations on a Cassandra data-source.

Latest Data Services Server can be downloaded from here.

Step 1:
Connect to the Cassandra server and create a keyspace and column family. This will be used later in data service.

create keyspace Students;
use Students;

create column family StudentID with comparator = 'UTF8Type' and key_validation_class = 'UTF8Type' and default_validation_class = 'UTF8Type' and column_metadata =
[{column_name : 'First Name', validation_class : UTF8Type},
{column_name : 'Last Name', validation_class : UTF8Type},
{column_name : 'Subjects', validation_class : UTF8Type},
{column_name : 'Class', validation_class : UTF8Type}];

Step 2:
Start the WSO2 Data Services Server and log on to the product management console. Click on 'Data Service' -> 'Create' link in the 'Main' menu section. This will redirect to the create data-service page. There, provide a name for service and click on 'Next'.


Step 3: Provide the connection details to connect Cassandra keyspace.

Step2 will redirect to the 'Add New Data Source' page. Click on 'Add New Data Source' link and provide the data source details as follows. Save the data source and click on 'Next'.


Step 4: Provide the query details.

Step3 will redirect to the 'Add New Query' page. In this page we can specify the  CQL queries and query parameters.  Click on 'Add New Query' link and provide the query details.

Defining Insert Student Query:
Query ID*: addStudentQuery
Data Source*: CassandraDS
CQL*: update StudentID set 'First Name'=?, 'Last Name'=?, 'Subjects'=?, 'Class'=? where 'key' = ?


Then specify the input/output parameters for the query. Click on the 'Add New Input Mapping' link to define the input parameters and define them one by one as follows. 



Once you entered the mapping details, click on 'Main Configuration' and then the 'Save' button to save the query. 

As the same way, you can define the queries and parameters for select, update, delete as well.

Defining Select Students Query:
Query ID*: getStudentsQuery
Data Source*: CassandraDS
CQL*: select * from StudentID

Defining Update Student Query:
Query ID*: updateStudentQuery
Data Source*: CassandraDS
CQL*: update StudentID set 'Class'=? where 'key'=?

Defining Delete Student Query:
Query ID*: deleteStudentQuery
Data Source*: CassandraDS
CQL*: delete from StudentID where 'key'=?

Step 5: Defining web service operations

After adding the queries to data service configuration as in the Step 4, click on 'Next' to define operations. This will redirect to the operations defining page. Click on 'Add New Operation' link. There, you can select a query for this operation that will execute when this operation get invoked. 

Following image shows the operation defines to use 'addStudentQuery' query. As the same way you can define the operations for other queries as well.


Step 5 is the final step of creating the data service. After adding operations click on 'Finish' to complete the data service. This will redirect to the service listing page and new service will be there.



Final xml configuration will be as follows.
 
<data name="CassandraStudentSample">
   <config id="CassandraDS">
      <property name="url">jdbc:cassandra://localhost:9160/Students</property>
      <property name="driverClassName">org.apache.cassandra.cql.jdbc.CassandraDriver</property>
   </config>
   <query id="addStudentQuery" useConfig="CassandraDS">
      <sql>update StudentID set 'First Name'=?, 'Last Name'=?, 'Subjects'=?, 'Class'=? where 'key' = ?</sql>
      <param name="FirstName" sqlType="STRING"/>
      <param name="LastName" sqlType="STRING"/>
      <param name="Subjects" sqlType="STRING"/>
      <param name="Class" sqlType="STRING"/>
      <param name="key" sqlType="STRING"/>
   </query>
   <query id="getStudentsQuery" useConfig="CassandraDS">
      <sql>select * from StudentID</sql>
      <result element="Students" rowName="Student">
         <element column="First Name" name="FirstName" optional="true" xsdType="string"/>
         <element column="Last Name" name="LastName" optional="true" xsdType="string"/>
         <element column="Subjects" name="Subjects" optional="true" xsdType="string"/>
         <element column="Class" name="Class" optional="true" xsdType="string"/>
      </result>
   </query>
   <query id="updateStudentQuery" useConfig="CassandraDS">
      <sql>update StudentID set 'Class'=? where 'key'=?</sql>
      <param name="Class" sqlType="STRING"/>
      <param name="key" sqlType="STRING"/>
   </query>
   <query id="deleteStudentQuery" useConfig="CassandraDS">
      <sql>delete from StudentID where 'key'=?</sql>
      <param name="key" sqlType="STRING"/>
   </query>
   <operation name="addStudent">
      <call-query href="addStudentQuery">
         <with-param name="FirstName" query-param="FirstName"/>
         <with-param name="LastName" query-param="LastName"/>
         <with-param name="Subjects" query-param="Subjects"/>
         <with-param name="Class" query-param="Class"/>
         <with-param name="key" query-param="key"/>
      </call-query>
   </operation>
   <operation name="getStudents">
      <call-query href="getStudentsQuery"/>
   </operation>
   <operation name="updateStudent">
      <call-query href="updateStudentQuery">
         <with-param name="Class" query-param="Class"/>
         <with-param name="key" query-param="key"/>
      </call-query>
   </operation>
   <operation name="deleteStudent">
      <call-query href="deleteStudentQuery">
         <with-param name="key" query-param="key"/>
      </call-query>
   </operation>
</data>


Click on 'Try This Service' link to test the data service operations. Using these four operations, you can insert, read, update and delete data from the StudentID column family that we created at the begining.