In this blog I am going to describe how to create a new Data Service using Bindaas. For the purpose of this tutorial I will assume that Bindaas is running locally on port 9099 in unsecured mode. 

 

  1. Identify Data Source and create a Profile
  2. Formulate a Query to fetch  data
  3. Identify attributes from the Query that could be queried upon and create a Query Template
  4. Assign the Query Template to a RESTful URL
  5. Execute the RESTful URL corresponding to the query by supplying parameters in the request

Identify Data Source and create a Profile

The first step in creating any Data Service is identifying the source of Data . Your data could be anywhere :

  • Relational database like MySql , Oracle , DB2, Postgres , etc.
  • XML database like DB2 , Derby , etc .
  • NoSQL or document based databases like MongoDB , CouchDB  , etc
  • Spreadsheets
  • Other webservices , etc.

How you fetch your data really depends on the data format , data source and the query language. Lets assume our data is stored in a IBM DB2 Database in the EMPLOYEE table described below

Employee ID

Employee Name

Annual Salary

Title

Phone Number

1

Joe

45,000

Sales Executive

233-234-3345

2

Anna

101,000

Manager

233-344-4445

3

Graham

56,000

Sales Executive

233-344-4444

4

William

76,000

HR Admin

233-344-4443

5

Smith

50,000

Technician

233-344-4442

6Sally80,000Manager233-344-4492
7Duve78,000Technician233-344-4432
8Anand45,000Sales Executive233-344-4462
9Jake60,000HR Admin233-344-4422

 

Having identified the data source , we need to create a Profile on Bindaas that will allow it connect to the DB2 database and establish an association. The REST API described in this blog is documented in the following format

HTTP [METHOD] [URL]

[FORM PARAMETER NAME] = [FORM PARAMETER VALUE]

The JSON object describes all the ingredients that make up a Profile

PropertyDescription
profileName
Unique name for this profile. This name will be referenced by the Query Template
dataSrcName
Describes the type of database we are using as our data source. The name is case-sensitive. The list of all supported data sources can be found on project homepage
dataSrcConfig.url
This is the JDBC URL used to connect to the DB2 instance
dataSrcConfig.username
DB username
dataSrcConfig.password
DB password

Formulate a Query to fetch you data

Next , we want to extract the data from Employee Table . Let's say we have the following use cases

  1. Fetch all records , filtering data on Title having Salary more than some user specified value

    Here $TITLE$ and $SALARY$ are user supplied values. They will become part of the RESTful requests in form of query parameters.

  2. Fetch the Name of Employee having highest Salary

    In this example there are no query attributes.

  3. Fetch all records , filtering data on  Columns specified by the user . If no filter specified then select all data

    Here $EMPLOYEE_NAME$ , $TITLE$ and $PHONE_NUMBER$ are user supplied values.

Identify attributes from the Query that could be queried upon and create a Query Template

A Query Template is as the name indicates , template to describe a Query along with all query parameters ,  optionality and description. Following illustrates Query Template  created for each use case that we described in the previous section

  1. Fetch all records , filtering data on Title having Salary more than some user specified value

    Query Template

  2. Fetch the Name of Employee having highest Salary

    Query Template

    Notice there are no bindVariables for this Query Template.

  3. Fetch all records , filtering data on  Columns specified by the user . If no filter specified then select all data

    Query Template

    Notice that bindVariables.required = "N" and bindVariables.defaultValue = "" . When the bindVariable is optional , a default value must be specified. When the RESTful call is made , if the user does not specify an optional query parameter , default value specified here will be used to fulfill the request

Query Template property description
PropertyDescription
queryName
Unique name for this query template. This name will become part of the RESTful URL
profile
Refers to the Profile we created in Step 1
outputFormat

Describes the format in which we would like to format the results of the query. For relational databases output formats like CSV is one option. Each data source has its own list of compatible output formats. In this example the output format we choose is CSV (db2csv)

List of available data sources and supported output formats is available on project homepage.

queryTemplate

This is a template that describes the query to be executed except , place holders qualified by '$' are specified to describe the variables in the query. These variables are called 'bindVariables' . A Query Template is independent of the underlying query language of the data source.

Thus a Query Template can be constructed from a SQL , XPath , XQuery or any other query language in pretty much the same way. All bindVariables in the query must be prefixed and suffixed by '$' . For example :

$EMPLOYEE_NAME$
bindVariables.name
The name of bind variable. This name MUST match with the name used in the Query Template.
bindVariables.required
Allowed values are 'Y' or 'N' .
bindVariables.description
Description of this bind variable
bindVariables.defaultValue
In case when required='N' , defaultValue is used to fulfill the request

 

Assign the Query Template to a RESTful URL

So far we have  seen how to create a Query Template JSON request object . We need to send this information over to Bindaas

Bindaas will do all necessary pluming to make this query accessible via a RESTful URL .

 

Execute the RESTful URL corresponding to the Query

  1. Fetch all records , filtering data on Title having Salary more than some user specified value 

    returns  ( in csv format )

    Employee ID

    Employee Name

    Annual Salary

    Title

    Phone Number

    2

    Anna

    101,000

    Manager

    233-344-4445

    6Sally80,000Manager233-344-4492

    db2csv (csv) formats the data and renders output in application/csv MIME Type

  2. Fetch the Name of Employee having highest Salary

    returns

    Employee ID

    Employee Name

    Annual Salary

    Title

    Phone Number

    2

    Anna

    101,000

    Manager

    233-344-4445

     

     

     

  3. Fetch all records , filtering data on  Columns specified by the user . If no filter specified then select all data

    returns

    Employee ID

    Employee Name

    Annual Salary

    Title

    Phone Number

    1

    Joe

    45,000

    Sales Executive

    233-234-3345

    2

    Anna

    101,000

    Manager

    233-344-4445

    3

    Graham

    56,000

    Sales Executive

    233-344-4444

    4

    William

    76,000

    HR Admin

    233-344-4443

    5

    Smith

    50,000

    Technician

    233-344-4442

    6Sally80,000Manager233-344-4492
    7Duve78,000Technician233-344-4432
    8Anand45,000Sales Executive233-344-4462
    9Jake60,000HR Admin233-344-4422

     


     

     

    returns

    Employee ID

    Employee Name

    Annual Salary

    Title

    Phone Number

    1

    Joe

    45,000

    Sales Executive

    233-234-3345

    3

    Graham

    56,000

    Sales Executive

    233-344-4444

    8Anand45,000Sales Executive233-344-4462

     


     

     

    returns

    Employee ID

    Employee Name

    Annual Salary

    Title

    Phone Number

    1

    Joe

    45,000

    Sales Executive

    233-234-3345

 

The RESTful URLs are of the form

http://localhost:9099/workspace/default/query/execute/{queryName}?{queryParameters/bindVariables}

Screencast Demo