Is there a way to bring in parent field or epic link into the datalake?

[Luis Crespo]

Trying to display the parent field or epic link as a column into my chart but cannot see this as part of my schema.

[Talar Pavlovic · Accepted solution]

Hey there Luis, 

Thanks for writing into the Atlassian Analytics Community, I'm Talar from the Atlassian team, happy to help.

There absolutely is - let me direct you over to our how-to guide here: https://confluence.atlassian.com/analyticskb/query-parent-and-child-issues-from-jira-1206787653.html

Regards,
Talar 

[David Green]

Talar I followed the above instructions for linking parent and child but I have a issue with the first query. Namely that i can get a PARENT value for stories to Epics but I cannot get one for Epics to Initiatives. I have added the Initiative Project into my Projects drop down filter, and the initiative shows up in my SQL results, and the Epic I'm trying to find a parent for has a parent Initiative in JIRA, so where is that link stored in the Enterprise Data Lake?

Here's my SQL...

SELECT `Issue`.`issue_key` AS `Issue key`,

       `Issue field`.`name` AS `Issue field Name`,

       `Issue field`.`value` AS `Value`,

       `Project`.`name` AS `Project Name`,

       `Issue`.`issue_type` AS `Issue type`

FROM `jira_project` AS `Project`

INNER JOIN `jira_issue` AS `Issue` 

ON `Issue`.`project_id` = `Project`.`project_id`

LEFT JOIN  `jira_issue_field` AS `Issue field` 

ON `Issue field`.`issue_id` = `Issue`.`issue_id`

-- AND (`Issue field`.`name` = 'Parent')

WHERE {PROJECT_ID.IN('`Project`.`project_id`')}

AND {ISSUE_TYPE.IN('`Issue`.`issue_type`')}

AND `Issue`.`issue_key` = 'APPGG-999'

ORDER BY  `Issue field`.`name` ASC

LIMIT 100000;

Note that if I test this on a story I DO get the Epic parent value.

[David Green]

actually I just found this - https://support.atlassian.com/jira-software-cloud/docs/upcoming-changes-epic-link-replaced-with-parent/

When will this be done?

[Talar Pavlovic]

Hey there David, 

What you're seeing would be expected - we don’t support parent hierarchy data above Epics just yet, but there should be an update posted in Community in the next week or so on this. 

Regards,
Talar 

[David Green]

Thanks Talar. I'm loving Analytics BTW. I followed the instructions above for getting Parent but I found just doing everything in SQL is far easier than the Visual mode especially as I used to be a SQL Dev :-)

There are a few other things in the schema that I can't find ie I can't seem to find any way of getting to the 'Team' in an Issue. Are there more tables to be added to the lake at some point or am I missing something?

[Talar Pavlovic]

So very glad to hear you're enjoying Analytics!! And it sounds like a "welcome home" (back to SQL world) is in order haha 

Re Custom Fields, this doco should help you find what you're looking for: https://confluence.atlassian.com/analyticskb/query-jira-custom-fields-1188411074.html

If you're not seeing the data you should be (and the projects with this field are enabled for Analytics), please pop in a ticket so we can take a look at your data specifically and help out. 

Regards,
Talar

[Talar Pavlovic]

@David Green - a correction from my response yesterday. 

Although that doco does detail the way to get most custom fields, the "Team" field is not yet available in the data lake yet. We do have a Feature Request for it to be added - https://jira.atlassian.com/browse/ANALYTICS-27 

- Talar 

[David Green]

Thanks Talar,

The custom fields are easy - 

LEFT  JOIN  jira_issue_field AS  IFS 

                                           ON  IFS.issue_id = ISS.issue_id

                                           AND IFS.name = 'Sponsor'

history requires a aggregation to get the latest value - 

LEFT JOIN (SELECT

                       issue_id

                  ,    MAX(TRY_CAST(value AS DATE)) AS value 

                  FROM  jira_issue_history

                  WHERE field = 'Start date'

                  GROUP BY issue_id

                   ) AS IHS

                     ON  IHS.issue_id = ISS.issue_id

also interesting to note is that I have to use TRY_CAST because for some reason Start date is in the format YYYY-MM-DD whereas every other date field is the long DATETIME format :-|

And one last thing - history has a couple of extra fields that would be handy - like Tempo account and Tempo team. However these are only in history if someone actually changed the field (obviously). I notice that there is no history for these in an issue IF the issue was cloned from a previous issue that did have these values in history (which is fair enough given they were not changed in the clone).

I'm going to try recursion to go back down the chain of clones in history (using the Link name and searching for 'this issue clones' in value) until I got to the one where these values were added :-) if I can remember how!

[David Green]

No recursive CTEs in apache spark sql :-(

[Talar Pavlovic]

Hey there David, 

Tempo data in the Atlassian data lake is a feature request https://jira.atlassian.com/browse/ANALYTICS-70 

Re the recursive CTE's - is the goal here to use recursive CTEs to get the parent/child relationships? Is so, this may not be possible anyway until we get some more data / fields added to the tables. 

-Talar 

[David Green]

Yes. I want to find the Tempo 'account' field in an Epic (under the Tempo tab). This isn't doable in the jira_issue table because this field isn't in the datalake (yet).

I can however find it in Jira_issue_history table when it's been changed either by the user or the jira bot adding it in if the Epic was added to an account in Tempo. BUT... if an Epic is cloned then the account field may have been changed in the Epic the current Epic was cloned from. 

So I need to scan down the jira_issue_history table to get the previous epic that the current one was cloned from and see if account was changed in that Epic. That part is easy. But there are clones of clones of clones, so I need to do a recursive search until I get back to original epic that DOES have the account value change. Hence the need for recursion. 

I did it the hard way instead (up to 5 repeat clones) cumbersome but it works :-|

[David Green]

OK so I managed to get the desired result but there was a lot of jiggery-pokery. I ran it all past ChatGPT and he/she/they/them couldn't help either. We are stymied on a couple of things. The main one being unable to create temporary tables (which solves the problem of non-recursive CTEs). 

Is there a reason why we can't CREATE anything in Spark SQL in Analytics?

[Talar Pavlovic]

Hey David, 

Yes indeed - CREATE is one of the restricted SQL commands in Analytics. Take a look here to see the others: https://support.atlassian.com/analytics/docs/restricted-sql-commands/ 

Regards,
Talar 

[David Green]

OK I can understand the need to prevent users from creating objects and other things in Analytics that would damage the integrity of the data structures and/or the data, but unfortunately that limits the usability of the SQL because we can't use the CREATE TEMPORARY TABLE command, which would be very handy (and also can help with performance. 

Although I note that Spark SQL doesn't allow INSERT INTO so that limits the use of temporary tables somewhat. 

Is there a way we can run a python script against the Data Lake so I can do something like the following? - 

from pyspark.sql import SparkSession # Initialize Spark session spark = SparkSession.builder.appName("RecursiveExample").getOrCreate() # Register the DataFrame as a temporary view initial_df = spark.createDataFrame([("your_target_issue_key",)], ["Issue_key"]) initial_df.createOrReplaceTempView("InitialView")