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; } } }

BI tool such as Talend compared to JIRA import utility for data import ?

satyakam panigr

can we use BI tool such as Talend for data import instead of JIRA import utility(CSV import)

we are migrating data from clearDDTS to JIRA, where data exported from DDTS is in the form of CSV file, and the volume of records is 1 lacs apx , so is it recomended to use CSV import utlity or using any BI tool such as Talend is preferable ? please advice..

Accepted answers

All comments

Udo Brand

I recommend the csv importer, because using a ETL-tool has a couple of challenges.

you need to know the database scheme of your target in detail
you need to know how IDs in your target tables are generated (I guess it is handled by the application and not by the database)

All this is already handled by the csv importer, and I have not heard of a limit (even if I don't know what "1 lacs apx" means).

Cheers,

Udo

satyakam panigr

one additional point, when i have huge number of records to import, do u feel using ETL is error free/quicker ?

satyakam panigr

Thanks Brand,

(1 lac= 100000), hwever can u please give little explanatin on your second point ?

satya

Udo Brand

You're welcome Satya.

The problem is when you insert values in a table you need to set the ID. I have not seen an after insert trigger which does that, so my guess is the generation of the ids are handled by JIRA itself. So you can't simply call a sequence while inserting records in a table. JIRA might still want to use Ids that you have inserted already.

Udo

Peter Van de Voorde

Hi Satyakam,

To give you a simple answer to your question : Using ETL does only have disadvantages compared to using the build in JIRA import utility. So don't do it.

Udo clearly explained why.

Best regards,

Peter

satyakam panigr

Yeah correct, but i still not having a clear understanding regarding the second issue mentioned by Udo , i.e. problem when inserting a value to a table we need to set the ID"

does something related while mapping the field of source CSV to the corresponding column of the target JIRA table" ??

curious to know ...

Udo Brand

Example: when using csv importer you fill table JIRAISSUE the field ID (PK) gets a value somehow by the application. When using ETL Tool you would need to get a source for this Field (it is not nullable), so how you want you fill it?

Nic Brough -Adaptavist-

I would avoid an ETL, because you really do need to know the database in a LOT of detail to be able to import data.

To run you through a single issue import in a generic way, you will need to:

Stop Jira and get a full backup. (NEVER never never write to an active Jira database, and if you insist on writing to a shut-down one ALWAYS get a backup first)
Import the core data into jiraissues (reporter, assignee, summary, key, project etc)
Update the project issue counter to make sure the next issue to be created falls after the new one
Increment the issue-id counter in the sequence table (apologies, forgotton the exact name of this table)
For each custom field, look up names, match the id and insert one or many lines into customfieldvalue
Increment the ID counter for customfieldvalue in the sequence table
For versions, watchers, components, voters, insert lines into nodeassociation
insert records into the OS_workflow tables to get the workflow to be valid for the issue
Restart Jira and immediately re-index it.

Note that for an absolutely minimal import you have to hit four tables, and not just write, you need to lookup several pieces of data dynamically so you can get your data consistent.

I'm pretty sure I've forgotton quite a lot of other things you need to do as well - there are definitely tables missing from that.

Nic Brough -Adaptavist-

No. You will break your Jira. You need to work out the next free value from existing data. For every issue.

RAMANIGANTH_VELUR

First of all many thanks Nic for your detailed reply,

1- From your first reply "You need to work out next free value from existing data" i could not understand exactly what we need to do here ?

suppose we are importing thousand of records in one attempt then how feasible is the solution ?>

2- From your 2nd reply i understood, we need to update all these tables(mentioned by you) after each import..

Note: is issue-id counter different from ID of jiraissues table ?

Thanks,

Satyakam

satyakam panigr

First of all many thanks Nic for your detailed reply,

1- From your first reply "You need to work out next free value from existing data" i could not understand exactly what we need to do here ?

suppose we are importing thousand of records in one attempt then how feasible is the solution ?>

2- From your 2nd reply i understood, we need to update all these tables(mentioned by you) after each import..

is issue-id counter different from ID of jiraissues table ?

3- "For each custom field, look up names, match the id and insert one or many lines into customfieldvalue" ,

"For versions, watchers, components, voters, insert lines into nodeassociation"

Are you saying here that we manually need to insert lines for these two tables ?

Note: Are you saying all these steps need to be done to import one single issue ??

Thanks,Satyakam

Nic Brough -Adaptavist-

Yes, sorry, there's quite a lot there!

1. You need to replicate what Jira would do if you were using the importer. For each issue you raise, there is a unique ID in the database. Jira has a table saying "next numer is xxxxxx" and for each issue, it grabs a number and increments the counter so the next issue has a new number.

So... if your counter is currently (random number here for examples only) 1024, and you've got 100 issues to import, you need to number the issues 1025, 1026, 1027... 1124, and then set the counter to 1125 (or higher)

As well as the issue counter, you also need to increase the project Key counter. If you are importing these 100 issues into project ABC and your current issue list goes up to ABC-42, then you need to change the project key counter to ABC-142 to make sure the next issue created is higher than your imported issue numbers. Again, you'll need to number your issues based on the highest existing issue key too.

2. Possibly. Each issue has TWO keys as I've hinted above. One key is virtually invisible to the user and is simply a number, stored in the "ID" column in Jiraissues and it does not change. It's also the key you need for reading other issue related tables. The other is the more human project key of format <project-sequence> and that can change if you move an issue from one project to another.

3. Probably. If you are ONLY inserting really simple issue data, then you only need to touch jiraissue, the sequence table, and the os_workflow tables. If you want to import component, version, watcher or voter data on an issue, then you need to add lines to nodeassociation. If you want to import custom field data, then you need to add lines to customfieldvalue. Each table you touch requires you to increment counters in the sequence table.

So, in short, yes. If you want to insert Jira data into the database with an ETL or even raw SQL, you MUST insert data into a LOT of tables. It's absolutelycritical that you get every table right, and update the sequence data appropriately too.

Or the really short version - forget it. Use the importers. Please, please, please, use the importers.

satyakam panigr

Thanks a lot Nic , now i have much better understanding,

However just need coulple of clarification based on your explanation,i.e

I checked the schema of my Test set up but could not find any table called "sequence" ,

I guess when you are saying "Jira has a table saying "next numer is xxxxxx" ,

You are referring to "sequence table" ??

Also i could not find any table having cloumn "Project couneter" ?/

Regards,

Satyakam

Udo Brand

I think Nic is referring to the table SEQUENCE_VALUE_ITEM.

satyakam panigr

but "SEQUENCE_VALUE_ITEM" table is not linked to any other table ,not even to jiraissue table ?>

And as per Nic, what understood is the sequence table should have entry like "next numer is xxxxxx" ..

Peter Van de Voorde

Satyakam,

Why do you keep on pushing this ETL solution when 3 people already explained you in detail that this is not the way to go?

Both Nic and Udo know what they are talking about, when they suggest not to do it with an ETL I would listen.

Best regards,

Peter

Peter Van de Voorde

Satyakam,

Then I wish you good luck on your endeavour.

Maybe this documentation might help you too :

https://developer.atlassian.com/display/JIRADEV/Database+Schema

Best regards,

Peter

satyakam panigr

Peter,

Definitely i would go with the suggestion of not going with ETL,

However my questions are now more related to know the JIRA schema better ..

Satyakam

satyakam panigr

Thanks everybody, especially Nic/Brand for providing such a good understanding with respect to JIRA schema.

Welcome!

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

Quick Links

Find more posts tagged with

jira

import

data