JIRA to BigQuery: 2 Methods to Move Data Instantly

Unlock the full potential of your JIRA data by integrating it seamlessly with BigQuery. With Hevo’s automated pipeline, get data flowing effortlessly—watch our 1-minute demo below to see it in action!

Without the right architecture, storing and querying large amounts of data can be time-consuming and expensive. So, you need a data warehouse like BigQuery to solve this problem. BigQuery uses Google’s processing power that enables super-fast SQL queries. In this blog, we are going to discuss two popular methods of transferring data from JIRA To BigQuery for deeper analysis. Before we get into that, let’s understand these two applications.

Jira Overview

Jira is a project management tool designed for agile teams to plan and track work across every team. Key features include:

• Customizable fields, workflows, and more.
• Global and project-level configuration.
• Rich APIs
• Permissions and privacy controls.
• Data residency, data security & compliance.

BigQuery Overview

BigQuery is Google’s fully managed, serverless data warehouse for large-scale analysis. Key Features include:

• Faster querying
• Real-time analytics
• Built-in machine learning capabilities
• Seamless integration with Google Cloud services

Prerequisites

Here is a list of prerequisites to get you started with the data migration:

• JIRA account with admin access.
• GCP (Google Cloud Platform) account with billing enabled.
• Google Cloud SDK installed on your CLI.
• BigQuery API enabled.
• BigQuery.admin permissions.

Methods to connect JIRA to BigQuery

There are multiple methods that can be used to connect JIRA to BigQuery and load data easily:

• Method 1: Using Hevo Data, a No-code Data Pipeline
• Method 2: Using custom ETL scripts to load data from JIRA to BigQuery

Method 1: Using Hevo Data, a No-code Data Pipeline

Hevo Data, a No-code Data Pipeline can help you move data from JIRA (among other free sources) swiftly to BigQuery. 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.

It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using BI tools. 

Steps to use Hevo Data

Hevo Data focuses on two simple steps to get you started:

• Configure Source: Connect Hevo Data with JIRA by providing the API Key and User Email for your authorised Atlassian/Jira account, along with your Atlassian Site Name and a unique name for your pipeline.
• Integrate Data: Load data from JIRA to BigQuery by providing your BigQuery database credentials. Enter the authorised account associated with your data and Id of the project, whose data you want to transfer along with a name for your dataset to connect in a matter of minutes.

Advantages of using Hevo Data Platform:

• Minimal Setup – You will require minimal setup and bandwidth to load data from JIRA to BigQuery using Hevo platform. 
• No Data Loss – Hevo architecture is fault-tolerant and allows easy, reliable, and the seamless transfer of data from JIRA to BigQuery without data loss. 
• 100’s of Out of the Box Integrations – Hevo platform brings data from other sources such as SDKs, Cloud Applications, Databases, and so on into BigQuery. So, Hevo is the right partner for all your growing data needs.
• Automatic Schema Detection and mapping – The schema of incoming  JIRA data is scanned automatically.  If there are changes detected, they are handled seamlessly and the changes are incorporated into BigQuery. 
• Exceptional Support – Hevo has 24×7 Technical support through emails, calls, and chat.

Method 2: Using custom ETL scripts to load data from JIRA to BigQuery

The steps involved in moving data from JIRA to BigQuery manually are as follows:

Step 1: Extracting Data from JIRA using REST APIs
Step 2: Preparing and Transforming JIRA Data for BigQuery
Step 3: Loading JIRA Data into BigQuery

Step 1: Extracting Data from JIRA using REST APIs

JIRA’s REST API allows you to get data from JIRA. The API offers comments, access to issues, and other numerous endpoints. For example, you can invoke the below command to get all comments for an issue.

GET /rest/api/2/issue/{issueIdOrKey}/comment

Below is the response in JSON format.  The response returns all the comments for an issue.  The response consists of StartAt, MaxResults, order by and expand parameters. 

{
    "startAt": 0,
    "maxResults": 1,
    "total": 1,
    "comments": [
        {
            "self": "http://www.example.com/jira/rest/api/2/issue/10010/comment/10000",
            "id": "10000",
            "author": {
                "self": "http://www.example.com/jira/rest/api/2/user?username=fred",
                "name": "fred",
                "displayName": "Fred F. User",
                "active": false
            },
            "body": "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Pellentesque eget venenatis elit. Duis eu justo eget augue iaculis fermentum. Sed semper quam laoreet nisi egestas at posuere augue semper.",
            "updateAuthor": {
                "self": "http://www.example.com/jira/rest/api/2/user?username=fred",
                "name": "fred",
                "displayName": "Fred F. User",
                "active": false
            },
            "created": "2020-02-10T15:30:13.041+0000",
            "updated": "2020-02-10T15:30:13.041+0000",
            "visibility": {
                "type": "role",
                "value": "Administrators"
            }
        }
    ]
}

Source: https://docs.atlassian.com/software/jira/docs/api/REST/8.7.1/#api/2/issue-getComments

Step 2: Preparing and Transforming JIRA Data for BigQuery

Now that you have a JSON response data, create a data schema to store JIRA data. Different data types such as INTEGER, DATETIME, BOOLEAN are supported by Google BigQuery. Here you can read more on the data types. Build a table to receive the API response data. If the response data is in JSON format, load the JIRA data directly into BigQuery.

Step 3: Loading JIRA Data into BigQuery

GCP (Google Cloud Platform) offers a guide on loading data into BigQuery. The bq load command is used to upload the data. Find here the syntax for bq command. 

Supply a table, a partition schema, or use a schema auto-detection for all the supported data types. Load the data into BigQuery by iterating the above process until all the data is loaded. 

Limitations of using custom ETL scripts to connect JIRA to BigQuery

• Accessing JIRA Data in Real-time:  You have successfully created a program that loads data from JIRA to BigQuery. The challenge of loading new and updated data is not solved yet. You may decide to replicate data when a new and updated record is created. This is not recommended because it is resource-intensive and slows down the process. 
• Infrastructure Maintenance: Things may go wrong when transferring data from JIRA to BigQuery. For example, JIRA may update their APIs. These changes will stop the flow of data leading to massive data loss. So, a team will always be required to monitor and maintain the infrastructure continuously.

Jira BigQuery Integration Benefits

Now, the question is, why are we integrating Jira and BigQuery? Jira BigQuery integration has following benefits:

• Real-time data synchronization: Load your Jira data directly to bigQuery in real-time for faster analysis. 
• Advanced analytics: BigQuery offers SQL-based querying and machine learning capabilities that help in advanced analysis of your data. 
• Comprehensive reports and dashboards: After loading data to BigQuery, you can connect this to your visualization tools for creating comprehensive reports and dashboards. 
• Scalability: BigQuery is designed to handle enormous amounts of datasets, allowing you to accommodate your growing data needs.

Conclusion

In this article, you learned how to effectively transfer data from JIRA to BigQuery using 2 different techniques. You can manually write custom scripts to extract the data from Jira using Rest APIs , prepare the data by creating a schema and then finally load it into BigQuery. Regulary extracting and loading the data manually can be a time-consuming task. You would have to invest a section of your engineering bandwidth to Integrate, Clean, Transform and Load your data to your desired destination so that you can perform business analysis in real-time. All of this can be easily solved by a Cloud-Based ETL Tool like Hevo Data.

Frequently Asked Questions