CSV files provide (Comma separated Text file) provides an easy way of storing simple tabular data. The sample csv file used in this example is hosted by USGS, the science organization which hosts latest earthquake information in CSV files online.
Follow the instructions below to create a data service using a sample CSV file as the data source.
You can find the sample CSV file we discuss here in here: DSS_HOME/samples/resources/Products.csv. It contains information on products manufactured in an automobile company. The sample table has ID, Name, Classification and Price as columns.
First, copy the JDBC driver to:
- DSS_HOME/repository/components/lib directory
- DSS_HOME/repository/components/dropins
For example, cp mysql-connector-java-5.XX-bin.jar /home/user/wso2ds/wso2-dataservices-XX/repository/components/lib.
1. Log on to the Data Services Server Management Console.
2. Click the"Main" tab and select the "Create" link under " Data Services."
3. The "Create Data Service" page appears. Fill in the fields and click " Next." The Data Service name is mandatory.
| Info |
|---|
Info: |
4. The "Data Sources" page appears. Click on the "Add New Data Source" link and add your Data Source details. Then click "Save."For a RDBMS data source, select Select the data source type as RDBMS. We use a MySQL database as an example.CSV.
For example,
| Info |
|---|
Note: Before saving, you can click on the 'Test Connection' button. If the database connection is successful, you will get the following message: |
...
We use a sample file hosted online but you can also use one in your local machine. |
Column Separator Values
- Single Character = "X" (X is any character), e.g. ","
- Tab = "\t"
- Space = "\s"
- Any Unicode Character = "\uXXXX" (XXXX is the hexadecimal unicode value)
5. Click Save. The added data source page appears. You can edit or delete the Data Source. To proceed click "Next."
6. The "Queries" page appears.
Initially your service does not include any queries. Click on 'Add New Query' to add a new query, input/output mappings, events etc. to your Data Service. Enter query details using which values will be extracted. Specify how the SQL response is mapped to output XML.
Example 1:
Query ID : customersInBostonSQL
SQL Statement : select *, from Customers where city = 'Boston' and country ='USA'
Example 2:
Query ID : customerAddressSQL
SQL Statement : select *, from Customers where contactLastName = ? and contactFirstName = ?
The following query needs two parameters for execution. The Input Mapping section is used to specify these input parameters.
according to the structure of the response you want.
In the following example, we have used Grouped By Element, Row Name and Row namespace.
For information on adding validations to input mappings in the query, if any, refer to section Input Validators.
7. Once a new query is added, it will be listed as follows:
You have added one query to your data service configuration.
You can edit or delete your query. To continue, click " Next."
8. The "Operations" page appears. Click the "Add new operation" link to create an operation. Select a query to be executed when this operation is called.
Click "Save" once done. For example,
9. The operation will be listed in the "operations" page. You can edit or delete your operation. To continue click "Next."
10. The "Resources" page appears. Click "Add new resource" and give your resource details. Click "Save" once done. For example,
11. The resource will be listed in the "Resources" page. You can edit or delete your resource.
The
10. The Data Service configuration is done. Click " Finish. "
12(You can continue to add resources by clicking the Next button if you like)
11. You will be navigated to the "Deployed services" page.
From here, you can manage your service.
For a demonstration on the usage of a CSV file as a data source in the WSO2 Data Services Server, refer to the CSV Sample.