Connecting JIRA to MariaDB: 2 Easy Methods

Do you want to move your data from JIRA to MariaDB? Are you finding difficulty in connecting JIRA to MariaDB? If yes, then this blog will answer all your queries. Follow this step-by-step solution of transferring your data from JIRA to MariaDB. By the end of this blog, you will be able to connect these platforms and transfer the data seamlessly. Read along to learn more about these technologies and set up your JIRA to MariaDB integration!

Prerequisites

• Keep your Atlassian instance and credentials handy.
• You should have a MariaDB instance and user credentials with table creation permission.
• Basic understanding of Rest APIs.

Introduction to JIRA

JIRA is issue management and tracking system that has become the standard project management tool in software development. JIRA is downloadable and deployed as a server on-premise. It can also be used based on a subscription-based service as part of the Atlassian cloud. JIRA pricing is very economical for small teams but increases exponentially as the number of users goes up.

Organizations that use JIRA for software management often need the data to be exported to their on-premises databases for a wide range of use cases – from evaluating their engineering performance to predicting delays and problems in project outcomes. 

To learn more about JIRA, visit here.

Introduction to MariaDB

MariaDB is a popular on-premise database and is one of the most recommended databases by open-source aficionados. MariaDB is a fork of MySQL and now follows a different development process, while maintaining the same querying standards. This post is about moving data from JIRA to MariaDB.

To learn more about MariaDB, visit here.

Methods to Connect JIRA to MariaDB

Moving data from JIRA to MariaDB can be done in the following ways. There are mainly two approaches that can be followed to move data from JIRA to MariaDB:

• Method 1: Using Rest APIs to Connect JIRA to MariaDB
• Method 2: Using Hevo Data to Connect JIRA to MariaDB

Method 1: Using Rest APIs to Connect JIRA to MariaDB

Accessing JIRA APIs needs the user to first generate an API token from the account section. You will begin the integration process with this step.

• Step 1:  In the account section, go to API-tokens and generate the required authentication token. While accessing the API, you will need this API token along with your email address. 
• Step 2: You will now use the CURL command-line utility to form a GET request to your JIRA instance.

   curl -D- 
   -X GET 
   -u email:api-token
   -H "Content-Type: application/json" 
   'https://your-domain.atlassian.com/rest/api/3/search?jql=<project_name>'
>> output.txt

The above query will return all the issues of the specified project and write to the output.txt file. JSON contains a large amount of data as we did not specify any fields.

• Step 3: You will now use a command-line utility called “jq” to process this JSON and extract only the relevant details. In this case, you will be extracting the issue identifier, start time, original estimate, and remaining estimate.
  
  Use the following command to extract this information and write it into a CSV file.

cat jira.json | jq -r '.issues | map(.id, .fields.worklog[0].started,.fields.timetracking.originalEstimate,.fields.timetracking.remainingEstimate) | @csv' >> issue_data.csv

• Step 4: Now, create a table in MariaDB to import this data. 

CREATE TABLE issues(
issue_id INT,
started VARCHAR(100),
orig_estimate VARCHAR(100),
rem_estimate VARCHAR(100)
);

• Step 5: Import the issue_data.csv file into MariaDB using the below command.

LOAD DATA LOCAL INFILE issue_data.csv' INTO target_db.issues FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'rn';

That concludes the steps involved in moving data from JIRA to MariaDB. Let’s discuss some limitations of using this method.

Limitations of Using JIRA REST APIs

Even though this appears a simple method, implementing it as a reliable process can be challenging. Let us look into what makes this more difficult than it appears in real life.

• JIRA JSON format is extremely complex with a lot of nesting. Anything other than the top-level fields will need some elaborate JSON processing. Some of the key fields, like the status of an issue, can only be obtained by subsequent requests using the issue links in the result. 
• The above method explains only the issue search API. To get the complete JIRA data, you will need to learn the structure and process the outputs for a large number of APIs.
• JIRA APIs return only 50 results in one go. You will need to implement pagination in your script using the max_results field and started_at field in the JSON output.
• Issues will get updated every time, and you will need a reliable duplicate merging process to ensure the correct version of data is there in your MariaDB.
• Data from such sources are inserted into data warehouses after a lot of cleanup and transformations to mold it to a form specific to the use case. The above approach offers limited opportunities for such transformations other than basic JSON processing.
• While executing it as a recurring process, additional logic for scheduling and error handling has to be built.

Method 2: Using Hevo Data to Connect JIRA to MariaDB

Hevo Data, a No-code Data Pipeline, helps you directly transfer data from JIRA and 100+ other data sources to Databases such as MariaDB, Data Warehouses, BI tools, or a destination of your choice in a completely hassle-free & automated manner.

Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Hevo Data takes care of all your data preprocessing needs and lets you focus on key business activities and draw a much powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability.

It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. 

Using the following 2 steps you can connect JIRA to MariaDB using Hevo Data:

• Authenticate and connect JIRA as your data source as shown in the below image. To learn more about configuring JIRA with Hevo, visit here.

• Connect the MariaDB as a destination to load your data. For this first Whitelist Hevo’s IP Addresses in your MariaDB account and grant privileges to the user. To learn more about this, visit here.

More Reasons to Choose Hevo Data

• Fully Managed: It requires no maintenance as Hevo is a fully automated platform.
• Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
• Fault-Tolerant: Hevo is capable of detecting anomalies in the incoming data and informs you instantly. All the affected rows are kept aside for correction so that it doesn’t hamper your workflow.
• Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
• Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
• Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within pipelines.
• Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Conclusion

In this blog, you have learned about JIRA, MariaDB, and steps to connect JIRA to MariaDB using 2 different methods. You can use JIRA REST APIs to connect the platforms but if you want to automate your data flow, then try the Hevo method.

Visit our Website to Explore Hevo

Hevo Data is a No-code Data Pipeline. It is a completely managed cloud-based ETL solution that can handle data movement from JIRA along with 100+ free data sources to a destination of your choice. It has excelled scheduling capabilities and can transform data on the fly. Configuring Hevo to move data from JIRA to MariaDB can be done in a few clicks. 

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of connecting JIRA to MariaDB in the comment section below.