JIRA to BigQuery: 2 Methods to Move Data Instantly

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.

Introduction to JIRA

JIRA is a product developed by Atlassian for proprietary issue tracking. It’s used for agile project management and bug tracking. It allows teams to manage their work and offers different products and deployment options that are built purposely for software, business, IT, Ops, and more teams. 

JIRA platform has four products. They include JIRA Service Desk, JIRA Software, JIRA Ops, and JIRA Core. There are built-in templates for each of the products. These templates are for different use cases and integrate seamlessly allowing organizations to work better together. JIRA Core is intended for generic project management, JIRA Software includes the base software (including agile project management features), JIRA Service Desk is intended for business service desks or for IT use, and JIRA Ops is intended for incident management.  

Introduction to BigQuery

BigQuery is a data warehouse that supports RESTful web services and works in conjunction with Google Cloud Storage. It is scalable, fast, and cost-effective. It is a SaaS product and allows fast analysis of big data and can be used complementarily with MapReduce. It is fully managed with different features such as built-in machine learning capabilities.  Other features include data management, querying, integration, and access control. The data management helps in creating and deleting tables based on the JSON schema. The data queries are expressed in the SQL dialect and return the results in JSON format with an approximate 128 MB maximum reply length.

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 custom ETL scripts to load data from JIRA to BigQuery
• Method 2: Using Hevo Data, a No-code Data Pipeline

Method 1: 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. You can read more on the data types here. 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 of loading data into BigQuery. You can find it here. The bq load command is used to upload the data. The syntax for bq command can be found here. 

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.

Method 2: 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.

Sign up here for a 14-Day Free Trial!

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.

Visit our Website to Explore Hevo

Hevo Data, a No-Code Data Pipeline helps you seamlessly transfer data from a vast sea of sources like JIRA into a Data Warehouse such as Google BigQuery or a destination of your choice for free. It is a reliable, secure and completely automated service that doesn’t require you to write any code!

If you are using JIRA in your enterprise and looking for a No-fuss alternative to Manual Data Integration, then Hevo can efficiently automate this for you. Hevo, with its strong integration with 100+ sources & BI tools(Including 30+ Free Sources like Jira), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiff.

Want to try Hevo? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. Have a look at our unbeatable pricing, which will help you choose the right plan for you.

What are your thoughts on moving data from JIRA to BigQuery? Let us know in the comments.