Data Federation With WSO2 Data Service Server

What's Data?

If you're asked the question "What's Data?", what could possibly be your answer? Though it's somewhat a confusing question to answer, I would call data as a form of representing knowledge, experience, observations, statistics, facts, concepts, etc that could further be formatted or presented in an orderly manner to be used in decision making, pattern identification, etc. As per the definition given to it, it's clear that data could be one of the most obvious things out of the stuff that we deal with, in our day to day life and it also implies the fact that every bit of moment that we spend in our lives can be converted into some form of data.

Though the term "data" has such a generic meaning, we often use it to refer a small subset of data which is constrained by some context associated with it. For example, if we consider some organization engaged in some form of a business, that particular organization would mostly be interested in "data" related to the context of the business that they are involved in. Not only the organizations but also the individuals, applications and various other entities spanned across the world amass data of their own contexts to analyse and find certain patterns, keep track of the older activities, or do whatever the tasks they are interested in carrying out. There comes the need of having a mechanism to store those data in a meaningful manner together with the ease of access. The myriad data storage mechanisms ranging from Relational Database Management Systems to NoSQL databases, data ware houses, legacy systems, document repositories, google and Excel spreadsheets, CSV, etc which are currently available in the world, were originated to cater that requirement. And almost all the enterprise bodies use  one or a combination of the aforementioned data storage mechanisms to fulfill their various data manipulation needs.

What's Data Federation?

As the term itself implies, Data Federation usually refers to the integration of data scattered across numerous types of data sources into some sort of form which makes it easy to access. Before the introduction of the concept of Data Federation, the most commonly used practice was to, first copy the relevant data into some other additional storage space and then carry out the integration based on the previously described copied chunk of data. But the bottlenecks encountered while doing so, such as copyright infringements when copying data, the need of additional storage space, led the way out to find better alternatives that possess the potential to avoid such bottlenecks. Among such alternatives, the concept of Data Federation could be considered the most advanced and efficient solution which makes it possible for various organizations to collect and process data scattered across their various data sources efficiently.

How does the WSO2 DSS fit in?

If we delve into the enterprise data solutions that are currently available in the market which offers its users with Data Federation functionality, WSO2 Data Services Server comes handy with its capabilities over the Data Federation as it supports a wide range of data source types to be federated varying from Relational Database Management Systems (RDBMS) such as Mysql, Oracle, MSSql, Postgres, H2, Derby to tabular data sources such as Google Spread Sheets, Excel Spread Sheets, CSV, etc. In WSO2 Data Services Server, the users are provided with the functionality of manipulating data stored in multiple types of data sources and present them to the user with an unified format.

In WSO2 DSS, this is implemented by using two main functionalities, namely,

1. Multiple data source support.
2. Nested query support.
3. Export parameter support.

1. Multiple data source support

Multiple data source support is another enticing feature available via WSO2 Data Services Server which enables users to define multiple database configurations within the same data-service descriptor. The following diagram depicts how it's done using a sample descriptor. There, each database configuration is given an id to uniquely identify the data source and this particular id will be later used in the process of integrating the data extracted out from various types of data sources together.

2. Nested Queries

Nested queries can also be considered another vital feature used in the process of data federation which carries out the real integration of the data queried from different types of data sources together. In other words, this makes it possible for a particular data service query to feed the result obtained after the execution of that particular query, as an input to some other query and eventually integrate both results to an unified format before presenting it to the user. The following diagram depicts the configurations of such sample data service queries and how they are integrated together to from a nested query which could be used in the process of data federation.

 3. Export parameter support

With this particular feature, the user is given the ability to export values of the output parameters of a particular query to be used in another query.

Having discussed about the bits and pieces of the Data Federation implementation of WSO2 DSS, let's delve into some practical use cases where you can actually make use of this feature in the real production environment.

Sample use cases:

Usecase 1: Let's consider a hypothetical usecase where a particular organization has the data related to its employees and offices in two RDBMSs' of the type MySQL and Oracle. further imagine the MySQL database contains a table named "Employee" and the Oracle database contains a table named "Office" to store the relevant data. Here,  the user needs to present both those data sets queried from different databases merged as a list of offices which nests its employees under each listed office.

Usecase 2: Assume a particular user has some data stored in the form of CSV files and he needs to get those data exported into a MySQL database.

Download the WSO2 Data Service Server and try those samples yourself!

WSO2 unveils its Relational Storage Solutions via WSO2 StratosLive.

Introduction

Nowadays, The concept of cloud computing is becoming increasingly popular. Many of the businesses spanned across various parts of the globe tend to adapt the cloud as it offers various means to provide the customers with highly scalable, agile, flexible service offerings at low capital, operational and maintenance costs. As a result, the customer attraction towards the cloud services has been increasing over the last couple of years and that has been one of the key driving forces which lies behind the emergence of various vendors who provide Cloud based services.

Among such vendors, WSO2 is considered one of the strongest competitors in the cloud space, recently introducing WSO2 StratosLive, the most complete and innovative Paas(Platform as a Service) which comes as a composition of various services representing its own unique product stack comprising ESB-as-a-Service, Data-as-a-Service, etc, which are completely built upon WSO2 Carbon, the most comprehensive enterprise middleware platform introduced by WSO2.

WSO2 RSS (Relational Storage Service)

Almost all the applications irrespective of whether they are run on the cloud or as a piece of standalone application, have got the requirement of storing and manipulating data associated with their numerious operations. That in turn brings forward the need of having a proper storage solution which caters all such data related requirements of those applications. Although there are diverse set of options available for standalone applications to be used locally to store data, not many options provide such storage capabilities and easy access to those stored data in the cloud space. WSO2 Relational Storage Service(RSS) which comes as a part of WSO2's the most complete PaaS (Platform as a service) offering, StratosLive, is capable of fulfilling all such data related requirements of applications running on the cloud. This particular post mainly aims at providing the community with a comprehensive understanding about WSO2 RSS and its offerings.

Delving into the storage solutions offered by WSO2 RSS, it gives you the total control over your data storage space, absolutely FREE of charge, by allowing you to create and manage your data storage space on your own via an easy to use user-interface. The users are completely given the ability to make their choice out of the two below mentioned options depending on the requirements.

1. Create and manage their data storage space within the WSO2 RSS.

2. Add user-defined RDS instances and manage them using WSO2 Data-as-a-Service user interface.

WSO2 RSS is implemented based on Amazon Relational Data Storage(RDS) System. Thus it inherits all the benefits offered by the Amazon RDS which could be thought of as an added advantage.

Having had a good insight into the storage solutions offered by WSO2 Data-as-a-Service, let's further explore how you can actually implement each of those aforementioned solutions with WSO2 RSS.

* Creating and managing your databases within WSO2 RSS.

Now, let's look at how we can create a database and perform related tasks such as creating database users and assigning them to databases, creating carbon datasources and dataservices using those databases, etc.

Step 1: Creating your database within the WSO2 RSS.

The first step in the process of consuming WSO2 Relational Storage Service is to create a database for your application in the WSO2 RSS storage space. As the figure below depicts, the WSO2_RSS option together with the set of your own RDS instances that are configured to be used to manipulate your application data, will be listed out in the select box which carries the label "Database Server Instance Name". There you can either use WSO2 Relational Storage Service or your own RDS instances to create and consume your database. Once you select your preferred storage solution, you can then proceed with the creation of your database. The user is given the ability to specify a preferred name for the database under "Database Name". Once you click on the create button after fulfilling the aforementioned requirements, you will be able to create the database of your own choice to store the application data manipulated within your own domain.

The first step in the process of consuming WSO2 Relational Storage Service is to create a database for your application in the WSO2 RSS storage space. As the figure below depicts, the WSO2_RSS option together with the set of your own RDS instances that are configured to be used to manipulate your application data, will be listed out in the select box which carries the label "Database Server Instance Name". There you can either use WSO2 Relational Storage Service or your own RDS instances to create and consume your database. Once you select your preferred storage solution, you can then proceed with the creation of your database. The user is given the ability to specify a preferred name for the database under "Database Name". Once you click on the create button after fulfilling the aforementioned requirements, you will be able to create the database of your own choice to store the application data manipulated within your own domain.

Having created a database, let's delve into how you can create database users and assign them to the database created in the first-most step of the process.

Before we create such database users upon a particular database, we introduce the concept of database privilege groups which allow you to create a group of database privileges that are intended to grant a particular database user and then reuse that group of privileges to create users which would considerably reduce the time associated with that task. It is interesting to explore how we can create such privilege groups before we move towards our main task of creating database users.

Step 2: Creating permission templates to be assigned to database users.

In this step, the users are given contorol over coming up with their own permission schemes that will be granted to their database users later. As the figure below shows, it's simply a matter of selecting the permission just by clicking on the check boxes associated with a particular database permission which helps you avoid tedious tasks such as writing lengthy SQL queries and executing them to grant database privileges for a particular database user.

Once you are done creating a privilege group, it will be listed out in the page titled "Database user privilege groups" as shown in figure below.

After creating privilege groups, then use them in creating database users. This is discussed in step 3.

Step 3: Creating database users and attaching them to the created database.

The "Add new user" user interface allows you to specify a name and a password of your choice for a particular user. It also lists down the prvilege groups that you created under step 1 so that you can select the appropriate permission scheme which is intended to grant the database user. Once you successfully fulfil the above requirements and click the save button which can be located at the bottom of the image, a database user will be created to be used in the data manipulative tasks carried out in your tenant domain.

At the successful completion of the aforementioned task, you will be able to observe the database user that you've just created in the "Database Users" page as depicted in the figure below. Now you can use this user to carryout various database related tasks such as exploring the databases and configuring it with using your own schemas, creating carbon datasources to be used in the process of creating dataservices, etc.

Step 4: Configuring your database with your own schemas, etc.

Having successfully created a database and attached a database user, you now need to configure your database so that it suits your own requirements. This task is also made quite simple with the database explorer that we've integrated into the database management user interface by which you can execute your desired SQL schemas to configure the database the way you prefer. To get you redirected to the database explorer, click on the "Explore Database" option listed under the "Actions" category in the "Database Users" page.

Next, you can execute your SQL scrips in the console given to you by the database explorer page and configure your database as described earlier.

Although we're almost done with our task, let us now discuss a couple of other enticing functionalities offered to you via the management console of the WSO2 Relational Storage Service.

Step 5: Creating carbon datasources pointing your databases.

Carbon Datasources play a vital role in carbon as it provides means to reuse relational database connections in various data manipulative tasks carried out across the platform. Thus, the create datasource option offered as an administrative action can be a handy feature whenever a user wishes to create such a carbon datasource out of the database which we've created in the earlier steps. Just clicking on the "Create Datasource" will get this task done for you in a matter of seconds.

Now, you can observe the entry corresponding to the carbon datasource created in the previous step if you browse the Data Sources option which comes under the Configure menu of the management user interface as shown below.

The "Generate" data service option that comes as a part of WSO2 Data Services Server can now be used to create a dataservice upon the datasource created in the previous step and deploy in absolutely no time. As it is shown in figure below, you can select the desired datasource out of the listed data sources, the appropriate database name that is given to you at the completion of creating a database and proceed with creating a dataservice and eventually deploy it.

Step 6: Creating data services using those created carbon datasources.

* Adding user-defined RDS instances and managing them using WSO2 DSS user interface.

WSO2 Relational Storage Service not only gives you the ability to use the storage space offered to you by the service itself, but also offers you the ability to use your own RDS instances to be used in your data manipulative activities. Let's investigate how to achieve that functionality via the user interface of the WSO2 Data Services Server.

Step 1: Add your own RDS instances.

In order to add your own RDS instances to the system, you can simply use the "Database Server Instance" option which can be loacated under the "Configure" menu of the management console. You can fill up the fields depenending on your preference and add your RDS instance to the system to be used as a data store for the applications running under your tenant domain.

Step 2: Create your database within the user defined RDS instance instead of using WSO2 RSS.

Once you're done adding your own RDS instance to the system, the rest of the process of creating databases and managing them is as same as described in step 1 of the previous category.

This way you can select your preferred data storage solution and continue experiencing the awesome set of functionalities provided by StratosLive, the most complete PaaS offered by WSO2.

Try StratosLive NOW!