This documentation is work in progress and will be released with the next WSO2 EI version.
Exposing CSV Data as a Data Service - WSO2 Enterprise Integrator 6.x.x - WSO2 Documentation

All docs This doc
||
Skip to end of metadata
Go to start of metadata

This tutorial guides you on how to expose the data in a CSV file as a data service using WSO2 Enterprise Integrator (WSO2 EI) . We will create a data service that can search for data in the file.

Note!

You can only read data from CSV files. The ESB profile of WSO2 EI does not support inserting, updating, or modifying data in a CSV 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/motorcycles) 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. Download the product installer from here, and run the installer.
    Let's call the installation location of your product the <EI_HOME> directory. This is located in a place specific to your OS as shown below:

    OSHome directory
    Mac OS/Library/WSO2/EnterpriseIntegrator/6.5.0
    WindowsC:\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:

    Open a terminal and execute the following command:

    wso2ei-6.4.0-integrator
    Go to Start Menu -> Programs -> WSO2 -> Enterprise Integrator 6.4.0 Integrator. This will open a terminal and start the ESB profile.
  3. Access the management console of the ESB profile: https://localhost:9443/carbon/ .
  4. Log in using admin as the username and password.
  5. Click Data Service > Create to open the Create Data Service window.
  6. Enter the following data service name.

    Data Service NameCSV
  7. Leave the default values for the other fields.
  8. 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 IDCSV
    Datasource TypeCSV
    CSV File Location./samples/data-services/resources/Products.csv .
    In this tutorial, we are using a sample CSV file that is stored in the above location of your product distribution.
    Column Separatorcomma
    Start Reading From Row2
    You enter 2 because the 1st row of the CSV file is the header. The data is available from row 2 onwards.
    Contains Column Header Rowtrue
    Header row1
    You enter 1 because the 1st row of the CSV file includes the header.
  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 to search for data in the CSV datasource. The query specifies 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 NameQ1
    DatasourceCSV
  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 four columns: ID, Name, Classification, and Price. We will create an output mapping for each of these columns.  
    1. Start by giving the following information:

      Output typeSelect XML.
      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 elementEnter Products.
      This will be the XML element that will group the query result.
      Row NameEnter 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:

      Mapping TypeDatasource TypeOutput Field NameDatasource Column NameParameter TypeSchema Type
      ElementColumn IDIDSCALARstring

    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 TypeDatasource TypeOutput Field NameDatasource Column NameParameter TypeSchema Type
      ElementColumnClassificationClassificationSCALARstring
      ElementColumnPricePriceSCALARstring
      ElementColumnNameNameSCALARstring
  3. Click Main Configuration to return to the Query screen.
  4. Click Save to save the query.
  5. 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 NameGetProductsOp
    Query IDQ1
  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 PathProducts
    Resource MethodGET
    Query IDQ1
  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 GetProductOp operation that you created previously and click Send.
  4. The result of the Q1 query will be published.

Example:

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.

Example:

<Products xmlns="http://ws.wso2.org/dataservice"><Product><ID>S10_1678</ID><Category>Motorcycles</Category><Price>1000</Price><Name>1969 Harley Davidson
 Ultimate Chopper</Name></Product><Product><ID>S10_1949</ID><Category>Classic Cars</Category><Price>600</Price><Name>1952 Alpine Renault 1300</Name>
</Product><Product><ID>S10_2016</ID><Category>Motorcycles</Category><Price>456</Price><Name>1996 Moto Guzzi 1100i</Name></Product><Product><ID>S10_4698</ID>
<Category>Motorcycles</Category><Price>345</Price><Name>2003 Harley-Davidson Eagle Drag Bike</Name></Product><Product><ID>S10_4757</ID><Category>Classic Cars
</Category><Price>230</Price><Name>1972 Alfa Romeo GTA</Name></Product><Product><ID>S10_4962</ID><Category>Classic Cars</Category><Price>890</Price>
<Name>1962 LanciaA Delta 16V</Name></Product><Product><ID>S12_1099</ID><Category>Classic Cars</Category><Price>560</Price><Name>1968 Ford Mustang</Name>
</Product><Product><ID>S12_1108</ID><Category>Classic Cars</Category><Price>900</Price><Name>2001 Ferrari Enzo</Name></Product></Products>
  • No labels