How to Load Data from Jira to MariaDB

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. 

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.

Note: Hevo Data does not currently support MariaDB as a destination. However, you can seamlessly connect and ingest data from MariaDB as a source to various supported destinations.

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.

Using Hevo Data to Connect JIRA and MariaDB

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

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 steps, you can connect JIRA and MariaDB using Hevo Data:

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

• Connect  MariaDB as a source to load your data in your desired destination. For this first Whitelist Hevo’s IP Addresses in your MariaDB account and grant privileges to the user. 

• Hevo allows you to seamlessly connect Jira and MariaDB as sources and migrate data from them to a variety of databases and data warehouses, including Snowflake, BigQuery, Redshift, PostgreSQL, and MySQL. This ensures a hassle-free, automated data flow tailored to your needs.

Conclusion

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

Hevo Data is a No-code Data Pipeline. It is a completely managed cloud-based ETL solution that can handle data movement from JIRA and various other 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 or MariaDB can be done in a few clicks. Sign up for Hevo’s 14-day free trial and experience seamless data migration.

FAQs