Extract all Jira issues to a data warehouse

[Giovanni Winkels]

Hi all,

Since there is a Jira Connector in Azure Data Factory v2, I am trying to extract all the issues(open, resolved, etc.) to a data warehouse.

Is there a possibility to get all the issues into one table in SQL. I tried to join several Jira tables regarding issues to each other but I keep getting the wrong number of issues.

I.E.:

Select Issues_key,  Issues_Fields_Summary, Issues_Fields_Priority_Name from "Agile"."Agile_Board_Sprint_Issue"
union
select Issues_Key, Issues_Fields_Summary, Issues_Fields_Priority_Name from "Agile"."Agile_Board_Backlog_Issues"

etc.

Thanks in advance,

Giovanni

[Nic Brough -Adaptavist-]

Heh.  No.

Extracting the full issue data for one issue could be hundreds of joins on SQL.  The database is the worst possible way to try to ask Jira for data in most cases!

Consider scraping it via REST instead - you can ask for issues (or a set of issues) and get a nice clean simple JSON representation of each issue in one standard format, easy to process into another database or system.

[Giovanni Winkels]

Hi,

Thanks for your response.

I don't need the full issue data, for every issue in Jira I need the following columns: Issue_Key, Fields_Summary, Priority_Name, Assignee_key/name, Fields_created and the reporter_name.

This information can all be found in just one table. The problem is that issues are spread over multiple Jira tables like backlog issues and sprint issues.

[Nic Brough -Adaptavist-]

No, that's not one table, you need at least three joins for those from just the plain issue data.

Seriously, go do this with REST, it's safer, faster, better and most importantly, easier.

[Giovanni Winkels]

Hi,

To be fair, I have no idea how REST works in ADFv2. I have created a linked service which is the new Jira connector. In this linked service I can't write JSON.

Do I need to use another linked service to accomplish this or can this be done by using the Jira connector as a linked service.

Regards,

Giovanni

[Nic Brough -Adaptavist-]

You don't need any "linked service" - just issue REST calls to Jira.

[Giovanni Winkels]

I created a pipeline with a web activity where I can fill in my Jira URL, REST method, headers etc. This is the only option I found where I don't have to use a linked service.

Is this the correct method to use?

[Nic Brough -Adaptavist-]

Yes, that is right.

[Glyn Thomas]

So what's the actual point of the linked service in azure data factory if not to connect through to the jira objects and download data. The data factory is setup to ingest data this way hence why it has a specific connector through to the objects via the linked service, it seems a bit backwards to have to use a web activity which isn't really there for this task.

[Nic Brough -Adaptavist-]

The linked service you mention is a REST based connection, not a database connection.  You don't get a database connection in Cloud.  It's not "backwards", it's the only way to do it.

[Glyn Thomas]

So why doesn't the linked service work? Or how do you actually pull data from jira using it? As I said we can see the objects but not actually pull anything from them, and people have been asking this question a few times on this site and I've yet to see anyone actually explain how it works.

[Nic Brough -Adaptavist-]

This is something you'd need to ask of the service provider - if you don't think it "works", the question needs to be asked of why Azure is not providing what you want, not Jira.

[Jason Selby]

Hi Nic,

Thank you for answering above, i am not technically minded so please forgive my simple approach and my questions. 

What we are trying to achieve?

Our business is a data driven business and we have numerous sources of data, we want a central repository of data so that we can analyse this data and drive improvements. Our current solution is the Azure datafactory where we ingest data and present that data to our business to highlight trends.

We want to ingest the data from Jira Service Management into the same repository so we can give our business a single customer view and highlight what technology issues we have been having which get logged in JSM

Is this possible ? If so is there documentation ?

[Nic Brough -Adaptavist-]

It is possible, but how you do it all depends on the system that is going to scrape Jira.  It's going to need to do it via the REST API, and I don't know of any docs for Azure data factory that show us how to do it.

The other option for Jira Cloud is in beta at present (the data lake), but when its released, you'll find its built for direct reporting, not scraping.

[Jason Selby]

Thank you. 

I sense frustration in your response and i apologise for my ineptitude. I am looking for a solution to a problem, i am not fully educated on how REST API works. And was trying to explain how we used the system. 

Our assumption was that the connector (the Jira connector) was developed to allow the differing technologies the chance to work together.

Any help or documentation you could send my way to give me more of an understanding would be fantastic, and i appreciate you giving your time to read my responses.

[Nic Brough -Adaptavist-]

No, there's no frustration, I am sorry to have written it in a way that gave you that impression!  

You are right on the connector - it was developed to allow the systems to work together, I was just trying to explain how it does it, so you know where to look and what to question.

For most of us, there are only two ways to get data from Jira Cloud - the UI and the REST API.  There's no concept of databases or tables in either of those, there is just a display or response of the data you ask for.   (For some of us, there's also the data-lake, if we signed up to beta test it, but that also is designed for direct reporting and not scraping)

The connector uses the REST API, but what it asks for is not something I've ever looked up.