Publish Data From WSO2 ESB to WSO2 DAS.

Prerequisites

• WSO2 ESB (Enterprise Service Bus - 4.9.0).
• WSO2 DAS (Data Analytic Server - 3.0.0) .

The purpose of writing this blog post is to demonstrate how to publish a data stream from WSO2 ESB to WSO2 DAS and persist the data in a MySQL database.

  

First you need login to the WSO2 ESB management console and add an event sink to the ESB. To do that select configure tag and click on Event Sinks. Now you can see the event Sinks Configuration console. Click on Add Event Sink then you will receive a console to enter details about the event sink as shown in the figure 2.

Figure 1

Enter a proper name to the event sink. Then enter the user name and the password.

Receiver URL : tcp:// <your IP address > <thrift port of DAS>

Authenticator URL : ssl:// <your  IP address><authentication port of DAS>

By default the thrift port and the authentication port will be configured as 7611 and 7711 respectively. However, since DAS server was started with an offset, those ports will be opened on 7613 and 7713 from the DAS server.

Create a custom Proxy service and edit the proxy service as shown in figure 3.

How to create custom proxy service to access the Redmine REST API through Redmine Connector ?? Click Here

                                          Figure 3

Edit the already created proxy service in design view add a Publish Event mediator to the proxy service as shown in figure 5 .

Click on design view of the proxy service.

Figure 4

Figure 5

Enter the Stream definition to the publish event mediator as shown in figure 6.

Stream Name :  Name of the data stream.

Stream Version : Version of the data stream.

Event Sink : Select a proper event sink from the drop down list.

Add the correlated attributes of the data stream. Here I added two attributes called id and name. For each attribute you have to select the attribute value from the drop down list.

If you define an attribute for a static value you need to select the value option and if you define an attribute for a dynamic value you need to select expression option from the drop down list.

For the Value/Expression field you need to give the Xpath if the attribute is dynamic other wise you need to add a static value if the attribute is static. Then Select the type of the attribute.

Now save the all settings of the proxy service and go to the proxy service list. Then click on Try this service as shown in the figure 7.

Figure 6

Figure 7

  Now login to the DAS and go to the main tag and select streams . Now you should be able to see the data stream which was published from the ESB in the stream list. Now you need  to persist this data stream . To do that click on edit . Click on  Next[Persist Event] button at the bottom of the Edit Event Stream Console  as shown in the figure 9.

Figure 8

                                                                         

Figure 9 

Then put a tick to the Persist Event Stream option and to the Persist Attribute option as highlighted in figure 10 and then click on Save Event Stream .

                                                               Figure 10                                                                     

Now you need to add an event receiver to listen  this data stream. To add an event receiver select Main tag and click on Receivers then click on Add Event Receiver. Then you have to enter the details of the event receiver as shown in figure 12.

Figure 11

  Enter a proper name for the event receiver .Select wso2event form the drop down list for the input event adapter type. Select the data stream name form the drop down list for the event stream . Select wso2event as the message format. Then click on Add Event receiver.

Figure 12

Now you are done with persisting the data stream definition and adding the listener to  the data stream. Again you need to run the ESB proxy service to publish the data.

Now select the Main tag and click on Data Explorer. Then select a table from the drop down list and   click on Search. Then you should be able to view the data published from the ESB as shown in figure 14.

Figure 13

                                                          

Figure 14

How Store the published data in a MySQL database?

 Create a MySQL database.

Create a table in the database.

Download MySQL JDBC connector .jar  and put it in <wso2 DAS Server>/repository/components/lib .

Create a data source in DAS. Select configure tag and click on Datasources and then click on Add Datasource as shown in figure 15.

Figure 15

   

Figure 16

                                

·         Datasource type : RDBMS

·         Name : Proper Name

·         Driver : com.mysql.Driver

·         URL : jdbc:mysql://localhost:3306/<database name>

·         User Name : User Name of the MySQL

·         Password : password of MySQL

Then click on Test Connection and then you will receive a success message as shown in figure 17.

Figure 17

  Now select Main tag and click on Scripts . Then you will be able to see the Available Analytics Scripts console as shown in figure 18. Click on Add New Analytics Script to add a new script.

Figure 18

Now you will receive the Add new Analytics Script  console . Enter a proper name for the Script name. Enter the Spark SQL Queries in the text editor .

Here is an example format for the Spark SQL query.

create temporary table xxx using CarbonAnalytics options (tableName "<data stream name>", schema  "attribute data type");

select * from xxx limit 20;

create temporary table yyy using CarbonJDBC options (datasource "<data source name>", tableName "<table name >");

insert into table yyy select * from xxx       

Figure 19

Then click on Execute Script.  Then you can see the data table under the Execution Results as shown in figure 20 .

Figure 20

 Now go to command prompt and enter the following SQL query.

Select * from newtable;

Then you should be able to see the data which were published from ESB stored in the MySQL table as shown in figure 21.

                   Figure 21