How to Build an ETL App for Jira Data in Python with CData Connect AI
The rich ecosystem of Python modules lets you get to work quickly and integrate your systems more effectively. With the CData Connect AI Python SDK and the petl framework, you can build Jira-connected applications and pipelines for extracting, transforming, and loading Jira data. This article shows how to connect to Connect AI and use petl to extract, transform, and load Jira data.
The Connect AI Python SDK (cdata-connect-ai) is a DB-API 2.0 (PEP 249) compliant client, so petl can read directly from the SDK connection with etl.fromdb. There is no driver to install per source: connect with a Personal Access Token and build your pipeline.
About Jira Data Integration
CData simplifies access and integration of live Jira data. Our customers leverage CData connectivity to:
- Gain bi-directional access to their Jira objects like issues, projects, and workflows.
- Use SQL stored procedures to perform functional actions like changing issues status, creating custom fields, download or uploading an attachment, modifying or retrieving time tracking settings, and more.
- Authenticate securely using a variety of methods, including username and password, OAuth, personal access token, API token, Crowd or OKTA SSO, LDAP, and more.
Most users leverage CData solutions to integrate Jira data with their database or data warehouse, whether that's using CData Sync directly or relying on CData's compatibility with platforms like SSIS or Azure Data Factory. Others are looking to get analytics and reporting on live Jira data from preferred analytics tools like Tableau and Power BI.
Learn more about how customers are seamlessly connecting to their Jira data to solve business problems from our blog: Drivers in Focus: Collaboration Tools.
Getting Started
Connect to Jira in Connect AI
CData Connect AI uses a straightforward, point-and-click interface to connect to data sources.
- Log into Connect AI, click Sources, and then click Add Connection
- Select "Jira" from the Add Connection panel
-
Enter the necessary authentication properties to connect to Jira.
To connect to JIRA, provide the User and Password. Additionally, provide the Url; for example, https://yoursitename.atlassian.net.
- Click Save & Test
- Navigate to the Permissions tab and update the user-based permissions.
Generate a Personal Access Token (PAT)
The Python SDK authenticates to Connect AI with your account email and a Personal Access Token (PAT). It is best practice to create a separate PAT for each application to maintain granularity of access.
- Click the Gear icon () at the top right of the Connect AI app to open the Settings page.
- On the Settings page, go to the Access Tokens section and click Create PAT.
- Give the PAT a name and click Create.
- The PAT is only visible at creation, so copy it and store it securely.
Install Required Modules
Install the SDK and the petl framework using the pip utility:
pip install cdata-connect-ai pip install petl
Build an ETL App for Jira Data in Python
Once the required modules are installed, you are ready to build the ETL app. Code snippets follow, but the full source code is available at the end of the article.
First, import the modules and connect to Connect AI with your account email and PAT:
import petl as etl
import cdata_connect_ai
conn = cdata_connect_ai.connect(
username="[email protected]",
password="<your_pat>",
)
Create a SQL Statement to Query Jira
Use SQL to create a statement for querying Jira. In this article, we read data from the Issues entity. Identifiers are three-part: <Connection>.<Schema>.<Table>, where the connection name defaults to the source name (for example, JIRA1).
sql = (
"SELECT Summary, TimeSpent "
"FROM [JIRA1].[JIRA].[Issues] "
"WHERE ReporterDisplayName = 'Bob'"
)
Extract, Transform, and Load the Jira Data
With a connection and query in hand, use petl to extract, transform, and load the Jira data. In this example, we extract Jira data, sort the data by the TimeSpent column, and load the data into a CSV file.
table1 = etl.fromdb(conn, sql) table2 = etl.sort(table1, 'TimeSpent') etl.tocsv(table2, 'issues_data.csv')
Jira is a read-only source in Connect AI, so this pipeline can extract and transform Jira data but not load rows back. Close the connection when the extract is complete:
conn.close()
With the CData Connect AI Python SDK, you can work with Jira data just like you would with any database, including direct access to data in ETL packages like petl.
More Information and Free Trial
Now you can pipe live Jira data through petl using the CData Connect AI Python SDK. For more information on connecting to Jira (and hundreds of other data sources), visit the Connect AI page. Sign up for a free trial and start building data pipelines for live Jira data in Python.
Full Source Code
import petl as etl
import cdata_connect_ai
conn = cdata_connect_ai.connect(
username="[email protected]",
password="<your_pat>",
)
sql = (
"SELECT Summary, TimeSpent "
"FROM [JIRA1].[JIRA].[Issues] "
"WHERE ReporterDisplayName = 'Bob'"
)
table1 = etl.fromdb(conn, sql)
table2 = etl.sort(table1, 'TimeSpent')
etl.tocsv(table2, 'issues_data.csv')
conn.close()