Connecting to Jira via SQL

You can use SQL to connect to Jira in the Data Virtuality Studio or the Code Editor in the Web UI.

Pre-requisites

You need the auth username, auth password (Basic auth for REST APIs).
You need the Client Id, Client Secret, Cloud Id, Refresh Token and Access Token (OAuth 2.0). Requests that use OAuth 2.0 (3LO) are made via api.atlassian.com (not https://your-domain.atlassian.net).

How to Connect

Before you start retrieving data, you need to run the connector script for Jira in the Data Virtuality Studio or the Web UI and input your credentials in the following line (Basic auth for REST APIs):

SQL

CALL SYSADMIN.createConnection (
    name => 'jira_rest_src'
    ,jbossCLITemplateName => 'ws'
    ,connectionOrResourceAdapterProperties => 'EndPoint=https://&#60;EndPoint&#62;/rest/api/3,SecurityType=HTTPBasic,AuthUserName=&#60;AuthUserName&#62;'
    ,encryptedProperties => 'AuthPassword=<AuthPassword>'
);;
CALL SYSADMIN.createDataSource (
    name => 'jira_rest_src'
    ,translator => 'jira_rest'
    ,modelProperties => 'cleanupMethod=<cleanupMethod>'
    ,encryptedModelProperties => ''
    ,translatorProperties => ''
    ,encryptedTranslatorProperties => ''
);;

(OAuth 2.0):

CODE

CALL SYSADMIN.createConnection (
    name => 'jira_rest_src'
    ,jbossCLITemplateName => 'ws'
    ,connectionOrResourceAdapterProperties => 'EndPoint=https://api.atlassian.com,ClientId=&#60;ClientId&#62;'
    ,encryptedProperties => 'AuthPassword=<AuthPassword>,ClientSecret=<ClientSecret,RefreshToken=<RefreshToken,AccessToken=<AccessToken>'
);;
CALL SYSADMIN.createDataSource (
    name => 'jira_rest_src'
    ,translator => 'jira_rest'
    ,modelProperties => 'cleanupMethod=<cleanupMethod>,CloudId=<CloudId>'
);;

You can also setup data source parameter cleanupMethod (it can have one of two values: DELETE or DROP) that will be used for cleaning up tables in storage when the target_table parameter is used for the procedures that support it.

Ensure the user account used for connecting to JIRA server has proper access rights. Endpoints of REST API may require proper access rights of the requesting user account.