Exposing Cassandra as a Data Service

This tutorial will guide you on how to expose data stored in Cassandra as a data service. Also, see the samples in Data Integration Samples.

Prerequisites

Set up the Cassandra server with a keyspace and table for this tutorial:

Installing and starting Cassandra

A Cassandra server of version 3.0 should be already running in the default port. For instructions, go to Apache Cassandra Documentation.

Creating a keyspace and a table

Execute the SQL commands as given below.

• Create the keyspace named UsersKS:

  CREATE KEYSPACE UsersKS WITH replication = {'class':'SimpleStrategy', 'replication_factor':3};

• Create the table named Users in the UsersKS keyspace:

  CREATE TABLE UsersKS.Users (id uuid, name text, country text, age int, PRIMARY KEY (id));

Creating a data service

Now, let's start creating the data service from scratch:

1. Download the product installer from here, and run the installer.
   Let's call the installation location of your product the <EI_HOME> directory.

   If you installed the product using the installer, this is located in a place specific to your OS as shown below:

   OS	Home directory
   Mac OS	/Library/WSO2/EnterpriseIntegrator/6.5.0
   Windows	C:\Program Files\WSO2\EnterpriseIntegrator\6.5.0\
   Ubuntu	/usr/lib/wso2/EnterpriseIntegrator/6.5.0
   CentOS	/usr/lib64/EnterpriseIntegrator/6.5.0

2. Start the ESB profile:
3. Log in to the management console of ESB profile of WSO2 EI using the following URL on your browser:  "https://localhost:9443/carbon/".
4. Click Create under the Data Service menu to open the Create Data Service window.

5. Enter the following data service name.

   Data Service Name	CassandraDataService

6. Leave the default values for the other fields.
7. Click Next to go to the Datasources screen

Connecting to the datasource

1. Click Add New Datasource. and enter the following details:

   Datasource ID	Cassandra
   Datasource Type	Select Cassandra as the datasource.
   Cassandra Server	Enter localhost.

   The rest of the datasource properties are optional. See the descriptions given below.

    Properties for a Cassandra datasource

   The following describes the properties supported by the Cassandra datasource. 

   Data Source Property	Description
   Cassandra Servers*	Host names of Cassandra servers in a comma-separated list.
   Keyspace	The default key space used by the Cassandra session.
   Port	The port of Cassandra servers.
   Cluster Name	The Cassandra cluster name.
   Compression	Compression used in communication.
   User Name	The authenticating user's username.
   Password	The authenticating user's password.
   Load Balancing Policy	The client load balancing policy on how calls should be made to the provided servers.
   Enable JMX Reporting	Enable JMX statistics for the connector.
   Enable Metrics	Enable metrics for the connector.
   Local Core Connections Per Host	Connection pooling: Local core connections per server host.
   Remote Core Connections Per Host	Connection pooling: Remote core connections per server host.
   Local Max.Connections Per Host	Connection pooling: Maximum local connections per server host.
   Remote Max.Connections Per Host	Connection pooling: Remote max connections per server host.
   Local New Connection Threshold	This property determines the threshold in the connection pool, which will trigger the creation of a new connection when the connection pool has not reached the maximum capacity for local hosts. Generally, it will not be required to change the default value for this parameter.
   Remote New Connection Threshold	This property determines the threshold in the connection pool, which will trigger the creation of a new connection when the connection pool has not reached the maximum capacity for remote hosts. Generally, it will not be required to change the default value for this parameter.
   Local Max Requests Per Connection	This property allows you to throttle the number of concurrent requests per connection for local hosts.
   Remote Max Requests Per Connection	This property allows you to throttle the number of concurrent requests per connection for remote hosts.
   Protocol Version	The native protocol version to use. By default, it auto connects. "2" for Cassandra versions 2.0 and upwards, and "1" for Cassandra version 1.2.x.
   Consistency Level	The consistency level used for queries.
   Fetch Size	Fetch size used by queries.
   Serial Consistency Level	The serial consistency level used for queries.
   Reconnection Policy	The reconnection policy used for the cluster.
   Constant Reconnection Policy Delay	If "ConstantReconnectionPolicy" is used for Reconnection Policy, this represents the constant wait time between reconnection attempts in milliseconds.
   Exponential Reconnection Policy Base Delay	If "ExponentialReconnectionPolicy" is used for Reconnection Policy, this represents the base delay in milliseconds.
   Exponential Reconnection Policy Max. Delay	If "ExponentialReconnectionPolicy" is used for Reconnection Policy, this represents the maximum delay in milliseconds.
   Retry Policy	Configured the retry policy in this cluster.
   Connection Timeout	The socket connection timeout in milliseconds.
   Keep Alive	Set if socket keeps alive.
   Read Timeout	Set per host socket read timeout in milliseconds.
   Receive Buffer Size	The socket receive buffer size.
   Send Buffer Size	Thesocketsendbuffersize.
   Reuse Address	The socket re-use address.
   So Linger	The socket linger on value.
   TCP no Delay	Set socket TCP to no delay.
   Enable SSL	Enable SSL.
   Enable OData	In WSO2 EI OData protocol version 4 (OASIS standards) is supported, which mainly provides support for CRUD operations. You can easily expose databases as an odata service by selecting this check box. Currently, Odata service feature support is available for RDBMS datasources and Cassandra datasources. If you have enabled Odata for your data service, you can complete the data service creation process. That is, you are not required to define queries or operations for the service. This Odata service will now be accessible from the following endpoints: For super tenant: http://localhost:<port>/odata/{dataserviceName}/{datasourceId}/ For normal tenants: http://localhost:<port>/odata/t/{tenantId}/{dataserviceName}/{datasourceId}/
   Disable Native Batch Requests	Disables native Cassandrabatchrequests,and reverts to emulated batch requests.

   * Mandatory fields.

Creating a query to ADD user information

Now let's write a query for adding user information to the Users table of the UsersKS keyspace in your Cassandra server.

1. Click Add New Query and enter the following details:

   Query ID	Enter addUsers as the query ID.
   Datasource	Select the datasource for which you are going to write a query. Select the Cassandra datasource that you created previously.
   SQL	In this field, enter the SQL statement describing the data that should be added to the Cassandra datasource. INSERT INTO UsersKS.Users (id, name, country, age) values (:id, :name, :country, :age)

2. Click Generate Input Mapping to create the input mappings.

   
   

3. Edit the id column and change the SQL Type to UUID.
4. Save the mapping.
5. Edit the age column and change the SQL Type to INTEGER.
6. Save the mapping and click Main Configuration to return to the query. You will now have the following input mappings:
   
   
7. Save the query.

Creating a query to GET data

Now let's start writing a query for getting data from the datasource. The query will specify the data that should be fetched by this query, and the format that should be used to display data when the query is invoked.

1. Click Add New Query and enter the following details:

   Query ID	Enter getUsersbyID as the query ID.
   Datasource	Select the datasource for which you are going to write a query. Select the Cassandra datasource that you created previously.
   SQL	In this field, enter the SQL statement describing the data that should be retrieved from the Cassandra datasource. SELECT id, age, country, name FROM UsersKS.Users WHERE id = :id

2. Click Generate Input Mapping to create the input mapping. The id is the input as shown below.

   
   

3. Edit the id column and change the SQL Type to UUID.
4. Save the mapping and click Main Configuration to return to the query.
   

5. Click Generate Response to create the output mapping. This defines how the employee details retrieved from the datasource will be presented in the result. Note that, by default, the output type is XML. 

   

6. Save the query.
7. Click Next to open the Operations screen.

Creating SOAP operationa to invoke the queries

1. Click Add New Operation and enter the following information.
   

   Operation Name	addUsersOp
   Query ID	addUsers

2. Save the operation.

3. Click Add New Operation and enter the following information.
   

   Operation Name	getUsersbyIDOp
   Query ID	getUsersbyID

4. Save the operation.

You can now invoke the data service query using SOAP.

Creating a REST resource to invoke the query

Now, let's create REST resources to invoke the query created above. Alternatively, you can create SOAP operations to invoke the queries. See the previous section for instructions.

1. Click Add New Resource and enter the following information.
   

   Resource Path	users
   Resource Method	POST
   Query ID	addUsers

2. Save the resource.

3. Click Add New Resource and enter the following information.
   

   Resource Path	users/{id}
   Resource Method	GET
   Query ID	getUsersbyID

4. Save the resource.
   

You can now invoke the data service query using REST.

curl -X GET http://localhost:8280/services/CassandraDataService.HTTPEndpoint/users/a05cdd7a-3d6a-11e8-b467-0ed5f89f718b