Connect JIRA REST API using Data Virtuality Web Service Connector
Using JIRA REST API as an endpoint in Data Virtuality Web Service connector, data virtualization architects and developers can add JIRA Web API as a new data source and access projects, issues, tasks, dashboard data, etc in tabular format using standard SQL codes. This Data Virtuality tutorial shows how to add your JIRA server as a Web Service data source and access to projects list as a sample case.
JIRA Issue and Project Tracking software is one of the most popular task and project tracking application especially for agile teams. All JIRA items like dashboards, projects, issues, tasks, task types, status details, etc which are accessible via JIRA API can be added into your data virtualization environment as a virtual view or by creating a virtual procedure easily on Data Virtuality software.
For Data Virtuality system administrators, the easiest solution will be installing the build-in Data Virtuality JIRA connector for accessing your JIRA server using REST API and integrate JIRA data.
In order to connect to JIRA Issue Tracker using JIRA API, following procedure can be implemented on Data Virtuality server via the Data Virtuality Studio.
All we need is an end point to the JIRA server Web API and a valid username and password that can connect to JIRA issue tracker application and query data.
First of all, launch Data Virtuality Studio and connect to the traget Data Virtuality server.
On Data Sources right click and select Add data source
When the data source type selection screen is displayed, filter Web service data source template for the JIRA API
When web service the data parameters are requested for the JIRA API, I selected Basic authentication and filled other requires values are seen in below screenshot. The JIRA server Web API endpoint is important and identifies the JIRA server which you are going to connect.
Following screen will let you identify whether statistics data about the data source will be collected or not as similar case for all data sources created on Data Virtuality.
Click Finish to complete adding JIRA REST API as a new Data Virtuality data source.
If all is fine and you can connect to the JIRA Web Service endpoint using the credentials provided in the connector template, following success message will be displayed.
Connection successful
Data source added.
As an administrator or data modeller, if you check the node by a drill-down on JIRA data source, you can see two procedures created by default for every web service: invoke() and invokeHttp()
Developers can call the REST API methods using invokeHttp procedure
For example, to get the list of projects defined on JIRA, the "project" action or API method can be called as follows
CALL "JIRA.invokeHttp" (
action => 'GET',
contentType => 'application/json',
endpoint => 'project',
requestContentType => 'application/json',
failOnHttpError => false
);;
Please note that to fetch the returned or response content which is in JSON format, we have to read the returned result output parameter of the invokeHttp procedure
Since the result parameter is in blob data type, we have to convert it into a readable format
This type conversion can be managed by using TO_CHARS() function on th returned result output parameter as follows
SELECT
to_chars(result, 'UTF-8') as result
FROM (
CALL "JIRA.invokeHttp" (
action => 'GET'
,contentType => 'application/json'
,endpoint => 'project'
,requestContentType => 'application/json'
,failOnHttpError => false
)
) as p;;
Programmers can format the returned JSON response data for more readability using an online JSON Viewer application. Here is what I got when I use http://jsonviewer.stack.hu/
Of course to convert the semi-structured JSON format data into a tabular structured data format, we need to apply a conversion. To parse the JSON into a tabular format, JSONTOXML and XMLTABLE functions can be used. This format change will enable data model developers easily consume the projects API method call.
SELECT
id,
key,
name
FROM (
SELECT
to_chars(result, 'UTF-8') as result
FROM (
CALL "JIRA.invokeHttp" (
action => 'GET'
,contentType => 'application/json'
,endpoint => 'project'
,requestContentType => 'application/json'
,failOnHttpError => false
)
) as p
) w,
XMLTABLE ('/root/root/.'
PASSING
JSONTOXML('root', w.result)
COLUMNS
id string,
key string,
name string
) x;;
This code can be converted and encapsulated as a virtual procedure too
CREATE VIRTUAL PROCEDURE JIRA_VS.Projects_List_Response (
)
RETURNS (
id string,
key string,
name string
)
AS
BEGIN
/*
SELECT
to_chars(result, 'UTF-8') as result
FROM (
CALL "JIRA.invokeHttp" (
action => 'GET'
,contentType => 'application/json'
,endpoint => 'project'
,requestContentType => 'application/json'
,failOnHttpError => false
)
) as p;
*/
SELECT
id,
key,
name
FROM (
SELECT
to_chars(result, 'UTF-8') as result
FROM (
CALL "JIRA.invokeHttp" (
action => 'GET'
,contentType => 'application/json'
,endpoint => 'project'
,requestContentType => 'application/json'
,failOnHttpError => false
)
) as p
) w,
XMLTABLE ('/root/root/.'
PASSING
JSONTOXML('root', w.result)
COLUMNS
id string,
key string,
name string
) x;
END;
And this procedure is called as follows
call "JIRA_VS.Projects_List_Response"();;
Of course for data virtualization layer SQL developers, it is also possible to create a view in a virtual schema in Data Virtuality which lists all projects created under connected JIRA server.
For the sake of simplicity, I gave the example of projects in this Data Virtuality Web Service connector tutorial for connecting JIRA Web API. Programmers can extend to other JIRA data objects including issues and more to build a complete data model in Data Virtuality.
In addition to Web Service connector, Data Virtuality shipped a build-in Data Virtuality JIRA connector for JIRA software as I mentioned before. It is possible to request and apply the new JIRA REST API connector in a few clicks on Data Virtuality server. For more details please refer to Data Virtuality Connectors: JIRA official documentation.