Blog| Matillion ETL for Amazon Redshift

Using the JIRA Query Component in Matillion ETL for Amazon Redshift

matillion-etl-integrations-jiraMatillion uses the Extract-Load-Transform (ELT) approach to delivering quick results for a wide range of data processing purposes: everything from customer behaviour analytics, financial analysis, and even reducing the cost of synthesising DNA.

The JIRA Query component presents an easy-to-use graphical interface, enabling you to pull data from JIRA and load it into Amazon Redshift. Many of our customers are using this component to get all their data in one place so they can then combine this data with additional data for further analysis.

The connector is completely self-contained: no additional software installation is required. It’s within the scope of an ordinary Matillion license, so there is no additional cost for using the features.

Data Extraction

This component runs a SQL Query on JIRA and copies the result to a table, via a S3 staging bucket.This component is for data-staging – getting data into a table in order to perform further processing and transformations on it in Amazon Redshift. You should consider the target table temporary, as it will either truncate or recreate each time the components runs.

 

JIRA Query Component in Matillion ETL for Amazon Redshift - Data extraction

 

Above is a typical workflow for importing data from a JIRA server into Amazon Redshift.

  1. JIRA connects and executes SQL against the JIRA.
  2. Data is read from the JIRA and streamed to S3 Staging buckets.(Data is never written to disk).
  3. Matillion copies the data from S3 Stage buckets to Amazon Redshift using “Copy” command.

Your Matillion instance can reach the service by JIRA endpoint URL available in your AWS console.

Authentication

Before you can use the JIRA Query component there are a couple of prerequisites:

  1. The JIRA URL and User Credentials have been setup and is accessible from the Matillion instance.
  2. Amazon Redshift Data Warehouse is provisioned and running on AWS platform.

Using the JIRA Query Component in Matillion ETL for Amazon Redshift

The JIRA Query Component can be found under the “Load/Unload” folder in the Components panel. The following image shows the properties for a configured component:

 

 

  • The options ‘URL’ , ‘UserName’ and ‘Password’ cover the details required to establish the connection to your JIRA Server.
  • You will also need to specify a S3 Staging Area which is the S3 bucket that will hold the results prior to loading into Amazon Redshift.
  • For the Schema, you can identify the staging table on Amazon Redshift you want to load the data into. Each time you run the component, the table will drop and rebuild. You should therefore treat this table as a Staging table and subsequently move the data into a Persistent table as soon as possible.

The JIRA Query component retrieves data on JIRA and loads it into a Amazon Redshift table. You will use a S3 bucket to stage the data. So each time you run the component, the table will truncate and reload. You may then use transformation job to enrich and manage the data in permanent tables.

Warning: This component is destructive as it truncates or recreates its target table on each run. Do not modify the target table structure manually.

Connection Options

The component has additional JDBC parameters supported by the Database Driver. The available parameters are determined automatically from the driver, and may change from version to version. Connection Options, however, are usually not required as sensible defaults are assumed.

Advanced Mode

Finally, you can use Advanced Mode to issue SQL Query against JIRA. You can therefore replace any variable definitions with the appropriate values and then issue the query against JIRA.

Orchestration Job

The following image shows a orchestration job that pulls data from a JIRA Server into a target table in Amazon Redshift and then copied the data to persistent table using the transformation jobs.

 

JIRA Query Component in Matillion ETL for Amazon Redshift - Orchestration Job

 

Conclusion

In conclusion, we hope that gave you a good overview of the JIRA Query component in Matillion ETL for Amazon Redshift. Give it a go and let us know (support@matillion.com) if you need any support with this component.

Useful Links

JIRA Query Component in Matillion ETL for Amazon Redshift
Component Data Model
Integration information

 

Want to try the JIRA Query component in Matillion ETL for Amazon Redshift? Arrange a free demo, or start a free 14-day trial.