Skip to content

toddbirchard/jira-database-etl

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

81 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

JIRA-to-Database Import

Python Pandas Requests SQLAlchemy PyMySQL GitHub Last Commit

JIRA SQL ETL

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.

Releases

No releases published

Packages

No packages published

Languages