How to Connect JIRA To Snowflake Easily in Minutes?

Easily move your data from Jira To Snowflake to enhance your analytics capabilities. With Hevo’s intuitive pipeline setup, data flows in real-time—check out our 1-minute demo below to see the seamless integration in action!

Looking for a quick and easy way to connect JIRA to Snowflake? You’re in the right place. This guide breaks down the process of integrating JIRA with Snowflake, making your data integration smooth and stress-free. These simple steps will make managing your data connection more effortless than ever.

What is JIRA?

Jira is a platform that effortlessly helps teams plan, release, track, and report various components of a software development lifecycle. It also helps the team set up multiple workflows to streamline and optimize the project’s development.

What is Snowflake?

Snowflake platform is an analytical, flexible, intuitive data warehouse service provided as SaaS. It provides a data warehouse that is quicker, simpler to use, and far more flexible than normal data warehouse offerings.

Prerequisites

1. The Snowflake platform should be set up. An account should be set up before starting. 
2. Python needs to be installed and set up in the system as the blog uses Python to shift data from JIRA to the Snowflake data warehouse manually. 
3. You need to set up an account on the JIRA platform connected to one of your projects, from which the components of your data will be retrieved.

Method 1: Connect JIRA to Snowflake using Hevo [Recommended]

Connect JIRA to Snowflake with the help of Hevo Data Pipelines. This is the fastest way to connect your JIRA account to Snowflake. 

With Hevo, bringing data from JIRA to Snowflake data warehouse becomes a cakewalk. Here are the steps to be followed: 

Step 1: Configure JIRA

• Connect Hevo to JIRA by providing the Pipeline Name, API Key, User Email ID, and website name.

Step 2: Configure Snowflake

• Complete JIRA to Snowflake migration by providing information about your Snowflake database and its credentials, such as database name, username, and password, along with information about the Schema associated with your Snowflake database.

You have successfully connected your JIRA account with Snowflake. Hevo also supports integrations from JIRA to various destinations like Snowflake, AWS Redshift, Google Bigquery, etc, and supports 150+ free data sources. 

Why Connect Automatically using Hevo Data?

1. Auto-Mapping: Hevo automatically maps all your data to relevant tables in the Snowflake data warehouse, giving you access to analysis-ready JIRA data in real-time.
2. Simplicity: Hevo is an extremely intuitive and easy-to-use platform that does not require prior technical knowledge. With Hevo, you can start pushing data from JIRA to the Snowflake data warehouse in just a few clicks.
3. Real-time Data: The real-time streaming design structure ensures that you move data from JIRA to the Snowflake data warehouse immediately, without any delay. In this way, meaningful analysis in real-time can be derived.
4. Reliable Data Load: The fault-tolerant architecture ensures that your data is loaded consistently and reliably without any loss of data.
5. Scalability: Hevo can handle any data of any scale. Additionally, data from multiple sources can be loaded into the Snowflake data warehouse. These Hevo features can help you scale your data infrastructure according to your needs. 

Method 2: Connect JIRA to Snowflake Manually

1. Connect to JIRA API. 

from jira import JIRA

# JIRA server URL
jira_options = {"server": "https://your-domain.atlassian.net"}

# Authentication (Use your email and API token)
jira = JIRA(options=jira_options, basic_auth=('your-email@example.com', 'your-api-token'))

# Fetch a specific issue (replace with a valid issue key)
issue = jira.issue('PROJECT-123')

# Print issue summary
print(f"Issue Summary: {issue.fields.summary}")

2. Read the data from the JIRA platform.

3. Push the data values to the Snowflake data warehouse table.

The below code performs the mentioned steps in Python:

JIRA Setup

# Import JIRA library and logging
from jira import JIRA
import re
import logging

# Set up logging
logging.basicConfig(
    filename='/tmp/snowflake_python_connector.log',
    level=logging.INFO)

# Configure JIRA connection (using anonymous mode)
options = {"server": "https://jira.atlassian.com"}
jira = JIRA(options)

# Get all projects viewable by anonymous users
projects = jira.projects()

# Sort available project keys and return a few of them
keys = sorted([project.key for project in projects])[2:5]

# Get a specific issue
issue = jira.issue("JRA-1330")

# Find all comments made by Atlassian employees on this issue
atl_comments = [
    comment
    for comment in issue.fields.comment.comments
    if re.search(r"@atlassian.com$", comment.author.emailAddress)
]

# Add a comment to the issue
jira.add_comment(issue, "Comment text")

# Change the issue's summary and description
issue.update(summary="I'm different!", description="Changed the summary to be different.")
issue.update(notify=False, description="Quiet summary update.")

# Update the entire labels field
issue.update(fields={"labels": ["AAA", "BBB"]})

# Add a new label to the existing list of labels
issue.fields.labels.append(u"new_text")
issue.update(fields={"labels": issue.fields.labels})

# Delete the issue
issue.delete()

Snowflake Connection

# Import Snowflake connector and OS module for environment variables
import snowflake.connector
import os

# Snowflake credentials
ACCOUNT = '<your_account_name>'
USER = '<your_login_name>'
PASSWORD = '<your_password>'

# AWS S3 credentials (if copying from S3)
AWS_ACCESS_KEY_ID = os.getenv('AWS_ACCESS_KEY_ID')
AWS_SECRET_ACCESS_KEY = os.getenv('AWS_SECRET_ACCESS_KEY')

# Establish a connection to Snowflake
con = snowflake.connector.connect(
    user=USER,
    password=PASSWORD,
    account=ACCOUNT,
)

# Logging connection
logging.info("Connected to Snowflake")

Snowflake Database, Schema, and Warehouse Setup

# Create warehouse, database, and schema if they don't exist
con.cursor().execute("CREATE WAREHOUSE IF NOT EXISTS tiny_warehouse")
con.cursor().execute("CREATE DATABASE IF NOT EXISTS testdb")
con.cursor().execute("CREATE SCHEMA IF NOT EXISTS testschema")

# Use the created warehouse, database, and schema
con.cursor().execute("USE WAREHOUSE tiny_warehouse")
con.cursor().execute("USE DATABASE testdb")
con.cursor().execute("USE SCHEMA testdb.testschema")

# Logging setup
logging.info("Database, schema, and warehouse setup complete")

Inserting and Copying Data into Snowflake

# Create a table and insert data into it
con.cursor().execute(
    "CREATE OR REPLACE TABLE testtable(col1 integer, col2 string)")
con.cursor().execute(
    "INSERT INTO testtable(col1, col2) VALUES(123, 'Test data')")

# Copy data from a local file to Snowflake table
con.cursor().execute("PUT file:///tmp/data0/file* @%testtable")
con.cursor().execute("COPY INTO testtable")

# Copy data from S3 bucket to Snowflake table
con.cursor().execute("""
COPY INTO testtable FROM s3://<your_s3_bucket>/data/
     STORAGE_INTEGRATION = 'myint'
     FILE_FORMAT=(field_delimiter=',')
""")

# Logging data insertion
logging.info("Data inserted and copied into Snowflake table")

Querying, Error Handling, and Closing the Connection

# Query data from the testtable
cur = con.cursor()
try:
    cur.execute("SELECT col1, col2 FROM testtable")
    for (col1, col2) in cur:
        print('{0}, {1}'.format(col1, col2))
finally:
    cur.close()

# Inserting data using bindings
con.cursor().execute(
    "INSERT INTO testtable(col1, col2) "
    "VALUES(%(col1)s, %(col2)s)", {
        'col1': 789,
        'col2': 'Another test string'
    })

# Retrieve and print column names from the table
cur = con.cursor()
cur.execute("SELECT * FROM testtable")
print(','.join([col[0] for col in cur.description]))

# Catch syntax errors
cur = con.cursor()
try:
    cur.execute("SELECT * FROM testtable")
except snowflake.connector.errors.ProgrammingError as e:
    print(e)  # Print the default error message
    print('Error {0} ({1}): {2} ({3})'.format(e.errno, e.sqlstate, e.msg, e.sfqid))
finally:
    cur.close()

# Retrieve the Snowflake query ID
cur = con.cursor()
cur.execute("SELECT * FROM testtable")
print("Query ID: ", cur.sfqid)

# Close the Snowflake connection
con.close()
logging.info("Snowflake connection closed")

The above code successfully loads the component data of JIRA to Snowflake data warehouse. You can choose what columns and what data that you require, or you can get the whole data itself by creating the corresponding columns in the data warehouse.

PROJECT	PROJECT_COMPONENT	PROJECT_ROLES	PROJECT_VERSIONS
JIRA	Accessibility	Administrators	5.1.1

The above is one of the example outputs of a Snowflake data warehouse table, which contains the data received from the JIRA API account. The table can have any number of columns of data that are required from the JIRA account. Accordingly, the data table can be changed to reflect the required columns.

Why is it not recommended?

1. Effort Intensive: Using custom code to move data from JIRA to Snowflake data warehouse requires you to learn and bring together many different technologies. Given the learning curve involved, your data projects’ timelines can be affected.
2. Not Real-Time: The process mentioned above does not help you bring data in real-time. You would have to develop a Cron job and write extra code to bring data in real-time.
3. No Data Transformation: At times, you would encounter use cases where you need to standardize time zones to perform efficient analytics. The mentioned approach does not cover that.
4. Constant Monitoring and Maintenance: In the event, there are some changes in the API at JIRA’s end or Snowflake’s end, that will result in irretrievable data loss. Hence, this approach requires constant monitoring and maintenance of the systems involved.

Conclusion

• Snowflake is a great data warehouse platform that is very versatile and can be used to aggregate structured data and derive valuable insights.
• JIRA is a great platform for various users to get valuable analytics for their business needs. 
• Depending on the particular use case and data requirement, you may choose to replicate data from JIRA to Snowflake data warehouse table using one of the approaches detailed in this article. 
• You may build a custom code-based data pipeline to transfer data from JIRA to Snowflake data warehouse.
• Alternatively, you may use an automated ETL tool like Hevo to quickly move data for analysis. Sign up for a free 14-day trial to give Hevo a try.

Let us know your experience of connecting JIRA to Snowflake in the comment section below. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

FAQs