What table to use to export issues on my board via REST API?

[Lana Koprivica]

I am trying to connect Azure Data Factory to Jira to ingest some data into SQL Database.

I've created API token and connected to Jira via it. I can see bunch of tables in Agile schema. Example: Agile.Agile_Board. I am having problems seeing and accessing the data in the tables. I can see table schema but when I try to preview the data I am getting Error 401. Unauthorized access.

Also what tables will have list of my Jira issues in it?

[Glyn Thomas]

@Price, Neil 

We've managed to get it working. I'll detail the steps below:

Create a user in Jira

Create an api token in jira under that account.

The user must have the role of Service Desk Team assigned to the project you are trying to access. We were trying to get the PIH tickets so we needed the user to have these perms assigned to that project.

Go into adf and create a new jira linked service

Host: http://myorg.atlassian.net/

User Name: Email address of the account

Password: API Key that was created (it's better to set up a key vault secret for this)

Test the connection then save it.

Go to the copy activity wizard in adf.

Select the new jira linked service as the source.

When the list of "Tables"/Objects appears the issues for us resided in "Platform"."Api_Issue"

Continue with the wizard and setup a sink etc.

Run the pipeline (Hopefully you should now have the data).

[Price, Neil]

Nice one, thanks for the info.

I wasn't able to get the tables to appear and that was down to my permissions as you mention. I was using a Microsoft account to login not a Jira account.. but I presume it would work regardless.

I have actually had to go a different route anyway because requirements have changed so I am using the REST API.

[Glyn Thomas]

@Price, Neil 

Out of interest, what paging settings did you use for the api? We were playing around with the rest api connector as well and we were stuck on how to set the paging up when creating a copy activity in the wizard. We thought it was something like declaring the AbsoluteURL as $._links.next to get it to page, but it just kept coming back with 50 results, obviously because it was only returning one page.

I'm just interested to know how you set that bit up as it might be useful in the future.

[Price, Neil]

As an example for search rest api here

https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-issue-search/#api-rest-api-3-search-get

The paging params are

• startAt
• maxResults (default 50)
  • It also mentions "To manage page size, Jira may return fewer items per page where a large number of fields are requested. The greatest number of items returned per page is achieved when requesting id or key only.

The json response includes the below:

• startAt
• maxResults
• total

I added a "startAt" string parameter to my dataset and did this sort of request in the dataset's "relative url"

/rest/api/3/search?startAt=@{dataset().startAt}&maxResults=50

I then put my copy activity inside an Until container and set variables for the totalPages and currentPage.

When you get the response back you can check

• @{activity('copy').output.startAt}
• @{activity('copy').output.total}
• @{activity('copy').output.maxResults}

And set the variables in the loop to use these values i.e. totalPages = (total / maxResults) rounded up.

startAt=0 i.e. first page and the 2nd page is startAt=1.. etc

Once startAt increments to (totalPages -1) the until loop stops.

You can also pass jql to this but if the querystring gets too long you need to do a POST instead of a GET 

[Price, Neil]

I am however now looking at using the pagination support in the dataset in ADF instead.

https://docs.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory#pagination-support

As you have already mentioned with the AbsoluteUrl

[Price, Neil]

Yea the ADF Pagination doesn't look like it can support this type of paging that jira does.. so I am sticking with an until loop 

[Glyn Thomas]

Yeah I couldn't get it to do it either, I'm going to stick with the connector at the moment as it seems to do what I want now. If I have issues i'll go with with your solution.

[Prateek Fulsundar]

Hello @Glyn Thomas  - Thanks for the detailed steps. I followed your steps, but instead of "table" option, I would like to use "query" to extract data. Any idea on how to find values for fields like "Fields_Assignee_AccountID " ? Thank you!

[Glyn Thomas]

Hi,

I'm also having this extact issue. I can gain access to the jira tables, however I can only preview some tables, the others don't give me a preview at all and if I try to load them I get that same error message. I also need to know where the issues table is.

Did this ever get resolved?

Kind Regards

Glyn

[Nic Brough -Adaptavist-]

This question was about Cloud, where you do not have any access to the database.  If you're using Server, then you need to be looking at docs/questions for Server, not Cloud.

[Glyn Thomas]

We also use cloud so it's the same issue. I can see all the tables through the data factory linked service, however I can only see the data in some of them. The linked service for jira is there to ingest data, but it doesn't seem to work properly.

[Nic Brough -Adaptavist-]

The REST API for Cloud does not expose any "tables", it serves up data in a structured list format.  

If your "data factory" application talks about tables, that's fine, but it's not a term that applies to Atlassian Cloud data interfaces. 

You'll need to stop thinking in terms of database type tables - that's not what you are using. 

As an example, the most simple Jira issue is smeared across several actual tables in the database (12ish from memory, adding  with three to seven more for any given custom field, or sometimes absolutely none for some types of field).  The REST API that your data factory is connecting to would return the whole issue in one single hit.

So I think the question becomes "what do you think you are missing from the REST API"?

[Price, Neil]

@Nic Brough -Adaptavist-  The question is related to the Jira linked service connector that is available in Azure Data Factory.

The Microsoft documentation for it is here: https://docs.microsoft.com/en-us/azure/data-factory/connector-jira?tabs=data-factory

We want to use the "copy activity" and for that it mentions to provide a "tableName" property or "query" but there is no documentation on how to use this linked service that is built into ADF?

This may or may not use the JIRA REST API under the hood but there is no info.

This linked service seems old and the password is also deprecated as you have to use an API token to authenticate the "linked service".

This is why they are thinking in terms of database tables... 

[Nic Brough -Adaptavist-]

And that's pretty much my whole point.

There is no SQL access to Jira Cloud.  There are no "tables" to look at, certainly nothing that supports "select * from <table>".  The connector is using the REST API to get some data, but it's not a table, and it's not using SQL.

Whatever you're thinking of as a "table" in here, it is in the connector app, it has no direct connection to Jira and you need to be asking the connector what the tableName is.  I suspect it's building its own representation of the Jira data from the REST calls it makes, and hence creating tables internally, but that's nothing to do with Jira (directly), it's entirely up to the connector app.

[Price, Neil]

I know it isn't a table, unfortunately the ADF built in Jira support seems useless and literally has a property called "Table Name" on the "JiraObject" dataset... that doesn't list anything for me in dropdown when I try it out.

The MS documentation is also misleading because it gives examples of using custom SQL queries to get data from Jira, which makes little sense when using an API.

I am instead using an ADF Rest linked service and dataset.

It just would have been nice if the built in stuff was actually useful.

[Nic Brough -Adaptavist-]

I think you're missing the point here.

There is nothing built into Jira to support this.  It has a REST API, that's what ADF is using (there's nothing else it can talk to Jira with).  The REST API has nothing to do with tables, jiraobjects and the rest.  It returns the data remote systems ask it to (or does things they ask it to, permissions allowing)

Whatever ADF is doing, that's up to ADF.   You need to be questioning that, not Jira.

[Price, Neil]

I'm not missing the point I was clearly saying that you should refer to the link I provided and that the features that were being talked about were developed by Microsoft and are built into ADF. I never said "built into Jira" I said "ADF built in Jira support" I was talking about ADF's integration to getting data out of Jira.

There is no documentation about what they consider to be a "Table Name", they are obviously calling the REST API.. but that doesn't mean they couldn't have also written some middleware to combine some of the data together to something that resembles a table structure, so that it can be more easily used with ADF's copy activity.

The built in connector and dataset in ADF for using with Jira refers to tables and it is misleading, this forum is the wrong place to be discussing it since it is related to what Microsoft have implemented and the lack of documentation on how to actually use it.. the link I provided clearly shows that they expect you to give a "Table Name".

We have only come to this Atlassian community page to discuss it in the hopes that someone knew more about the ADF implementation. 

Instead it should be avoided and we should use the REST connector in ADF.

[Nic Brough -Adaptavist-]

OK, good, I can see that.  The way you were writing earlier gave me the impression that you were not understanding what it was doing (that ADF functions and docs are not telling you that all this stuff about tables and objects are entirely ADF things, nothing to do with Jira), but your last comment clearly shows that I have been misreading it. 

I am sorry if I've annoyed or offended you with my mis-readings.

[Glyn Thomas]

I think the conclusion of all this is no one knows how to get the jira connector in data factory to work. I have a meeting with Azure today for them to look at it and help figure out how to get it to work, so if we manage to do so I'll post the solution on here.

[Glyn Thomas]

Price_Neil,

Did you manage to get it working using the rest api connector in the data factory instead of the jira one?

If so how did you configure it and what auth did you use? We've been trying with basic using the api key as the password. I would assume oauth is better and I would normally do this through a registered application but there's nothing to connect to jira through there.

Many Thanks

[Price, Neil]

@Glyn Thomas I just initially tested it with basic auth using the API key as the password.

I was then able to create a rest dataset with a base url of https://{domain}/rest/api/3/ to give it the api name and version that I was trying for cloud.

and "relative url" depends on what you need i.e. for getting 1 particular issue I tested using "issue/{issueId}" it was then able to import the ADF schema for the json response.  

I found these referenced blog posts to start trying oauth and copy activity.

https://www.alexvolok.com/2019/adfv2-rest-api-part1-oauth2

https://www.alexvolok.com/2019/adfv2-rest-api-part2-copy-activity/

As well as these useful Jira links:

https://developer.atlassian.com/server/jira/platform/oauth/

https://developer.atlassian.com/cloud/jira/software/rest/intro/

https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-issues/

[Glyn Thomas]

@Price, Neil 

Thank you, I have been doing basically the same but still had issues. I'll read through those docs. I think we have a permissions issue somewhere.

Did you use the account email address as the user name?

[Price, Neil]

I did use email address as the user name yes, and created my API token here: https://id.atlassian.com/manage-profile/security/api-tokens

[Glyn Thomas]

@Price, Neil 

Brilliant, thank you very much. That basically confirms everything I've done my side is correct, so there's a perms issue on the jira side. I've got the same setup as you have.

[Nic Brough -Adaptavist-]

I think you two should write this up as an article on using ADF with Jira Cloud, it would be useful for the community and probably should be added to the docs for ADF too!

[Glyn Thomas]

@Nic Brough -Adaptavist- 

That's not a bad idea. I'll try and get something together when I get some time later.

[Alexey Matveev]

Hello,

You need to provide user name and api token. Api token goes instead of your user password.

https://confluence.atlassian.com/cloud/api-tokens-938839638.html

[Lana Koprivica]

Hi Alexey,

I created token and used it in my Azure Data Factory pipeline to connect to Jira. That part worked well.

My next step where I have issues is:

1. What table to use to get list of my issues?

2. How to see the data in that table? Currently when I chose Agile.Agile_Board table I can see its schema but I am getting error 401 when trying to preview the data inside?

Can you please help with these?

Cheers,

Lana

[Alexey Matveev]

1. You should use the search rest api: https://developer.atlassian.com/cloud/jira/platform/rest/v3/?utm_source=%2Fcloud%2Fjira%2Fplatform%2Frest%2F&utm_medium=302#api-rest-api-3-search-get

2. You should provide your user name and api token as a basic authorization:

https://developer.atlassian.com/cloud/confluence/rest-api-examples/

[Lana Koprivica]

Hi Alexey,

I am not creating a code to do this I am using Copy Activity from Azure Data Factory to do it.

So I have option of providing jira host name with user and password (this part works when I use API token) and then I just get list of tables (I presume from Jira database, example of table name is what I mentioned above).

I need to select what table I want to import and copy activity would do it for me. 

Error happens when I try to preview the data in the table. I am assuming my API token doesn't have read permissions of the underlying jira tables. 

How can I get that ?

Cheers,

Lana

[Lana Koprivica]

Hi Alexey,

I am trying to set up rest connector. I need top put something in the box called: relative URL. I am assuming it is looking for something like https://<site-url>/rest/api/3/<resource-name>.

What should I put instead of <resource-name>?

I just want to read issues from my jira.

Cheers,

Lana