JIRA-to-Database Import

Extracts issues from a JIRA instance via the JIRA REST API, transforms the data, and loads data to a database.

To derive epic-based information from tickets, the script creates an JiraEpic table as well as a JiraIssue table. The JiraIssue table is joined with the former table to easily perform analysis on aggregated epic data.

Accompanying tutorial can be found here: https://hackersandslackers.com/jira-to-sql-etl/

Installation

Installation via requirements.txt:

$ git clone https://github.com/toddbirchard/jira-database-etl.git
$ cd jira-database-etl
$ python3 -m venv myenv
$ source myenv/bin/activate
$ pip3 install -r requirements.txt
$ flask run

Installation via Pipenv:

$ git clone https://github.com/toddbirchard/jira-database-etl.git
$ cd jira-database-etl
$ pipenv shell
$ pipenv update
$ flask run

Configuration

The following environment variables are needed to run this script:

• SQLALCHEMY_DATABASE_URI: A URI for the database intended to store these tables (ie: mysql+pymysql://[USER]:[PASSWORD]@d[DATABASE_HOST]:[PORT]/[DATABASE_NAME])
• SQLALCHEMY_EPIC_TABLE: Name of database table to store epics.
• SQLALCHEMY_JIRA_TABLE: Name of database table to store JIRA issues.
• JIRA_ENDPOINT: Your JIRA Cloud API endpoint for JQL searching (such as https://mydomain.atlassian.net/rest/api/3/search)
• JIRA_USERNAME: Your JIRA username.
• JIRA_API_KEY: An API key associated with the JIRA user.
• JIRA_ISSUES_JQL: JQL to get JIRA issues.
• JIRA_ISSUES_FIELDS: Specific fields to retrieve from the JIRA query.
• JIRA_EPICS_JQL: JQL to get JIRA epics.
• JIRA_EPICS_FIELDS: Specific fields to retrieve from the epics query.

Troubleshooting

Make sure your database contains tables named JiraEpic and JiraIssue prior to running this script (columns/schema don't matter, these will be overridden).

---

This project and all publically-visible repositories are free of charge. If you've found this project to be helpful, a small donation would be greatly appreciated to keep us in business. All proceeds go towards coffee, and all coffee goes towards improving these projects.