Exposing CSV Data as a Data Service

This tutorial will guide you on how to expose data in a CSV file as a data service by using the  Create New Data Service wizard. We will create a data service that can search for data on the file.

We will use the Products.csv  file  that is shipped with WSO2 EI by default.  The Products.csv  file stored in the  <EI_HOME>/samples/data-services/resources  folder contains data about products (cars/motorocyles) that are manufactured in an automobile company. The data table has the following columns: "ID", "Name", "Classification" and "Price".

Follow the steps given below to create a data service for this datasource. Also, see the samples in Data Integration Samples.

Creating the data service

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

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

3. Enter the following data service name.

   Data Service Name	CSV

4. Leave the default values for the other fields.
5. Click Next to go to the Datasources screen.

Connecting to the datasource

You can add a CSV file as the datasource as explained below.

1. Click Add New Datasource  and enter values as shown below. 
   

   Datasource ID	CSV
   Datasource Type	CSV
   CSV File Location	In this tutorial, we are using a sample CSV file that is stored in the following location of your product distribution: ./samples/data-services/resources/Products.csv.
   Column Separator	comma
   Start Reading From Row	2
   Contains Column Header Row	true
   Header row	1

2. Save the datasource. 
   
3. Click Next to go to the Queries screen.
   

Creating a query to GET data

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

1. Click Add New Query to open the Add New Query screen and enter the following details.

   Query Name	Q1
   Datasource	CSV

2. Now let's specify output mappings, which will determine how the result from your query will be presented when the query is invoked. The sample CSV  datasource  we are using contains three columns: ID, Name, Category and Price. We will create an output mapping for each of these columns.  
   

   1. Start by giving the following information:

      Output type	You can select XML, JSON or RDF. We will use XML for this tutorial. This specifies the format in which the query results should be presented.
      Grouped by element	Enter Products. This will be the XML element that will group the query result.
      Row Name	Enter Product. This is the XML element that should group each individual result.

   2. Click Add New Output Mapping to start creating the output mapping for the ID column. Enter values as shown below:
      
   3. Click Add to save the output mapping. You will now have one output mapping listed for the Q1 query.

   4. Create output mappings for the remaining columns given below.

      Mapping Type	Element Name	Datasource Type	Datasource Column Name	Parameter Type	Schema Type
      Element	Category	Column	Classification	SCALAR	string
      Element	Price	Column	Price	SCALAR	string
      Element	Name	Column	Name	SCALAR	string

3. Click Main Configuration to return to the Query screen.
4. Click Next to go to the Operations screen.

Creating a SOAP operation to invoke the query

To invoke the query, you need to define an operation.

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

   Operation Name	GetProductsOp
   Query ID	Q1

2. 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	Products
   Resource Method	GET
   Query ID	Q1

2. Save the resource.

You can now invoke the data service query using REST.

Finish creating the data service

Once you have defined the operation, click  Finish to complete the data service creation process. You will now be taken to the  Deployed Services screen, which shows all the data services deployed on the server.

Invoking your data service using SOAP

You can try the data service you created by using the TryIt tool that is in your product by default.

1. Go to the Deployed Services screen.
2. Click the Try this service link for the CSV data service. The TryIt tool will open with the CSV service.
   
3. Select the operation that you created previously and click Send.
4. The result of the Q1 query will be published.

Invoking your data service using REST

You can send an HTTP GET request to invoke the data service using cURL as shown below.

curl -X GET http://localhost:8280/services/CSV.HTTPEndpoint/Products

This will return the response in XML.