Blog| Matillion ETL for Snowflake

Using the JIRA Query Component in Matillion ETL for Snowflake

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 Snowflake. 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 Snowflake. 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 Snowflake - Data extraction

 

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

  1. JIRA connects and executes SQL against the JIRA.
  2. Data is read from the JIRA instance and streamed to S3 Staging buckets. (Data is never written to disk).
  3. Matillion copies the data from S3 Stage buckets to Snowflake 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. You have provisioned and are running a Snowflake Data Warehouse on the AWS platform.

Using the JIRA Query Component in Matillion ETL for Snowflake

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

 

JIRA Query Component in Matillion ETL for Snowflake - Data Properties

 

  • 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 Snowflake.
  • For the Schema, you can identify the staging table on Snowflake 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.

This JIRA Query component retrieves data on JIRA and loads it into a Snowflake 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 Snowflake and then copied the data to persistent table using the transformation jobs.

 

JIRA Query Component in Matillion ETL for Snowflake - Orchestration Job

 

Conclusion

In conclusion, we hope that gave you a good overview of the JIRA Query component in Matillion ETL for Snowflake. 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 Matillino ETL for Snowflake
Component Data Model
Integration Information

 

Want to try the JIRA Query component in Matillion ETL for Snowflake? Arrange a free 1-hour training session now, or start a free 14-day trial.