nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Accepted answers

All comments

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

Glyn Thomas_nx771x

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_nx771x

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"?

Glyn Thomas_nx771x

@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_nx771x

@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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

cloud

jira-cloud