• Products
  • Documentation
  • Resources

Data share schema for Jira family of products

The Jira family of products share many of the same tables. In other words, data for the available Jira products in the Atlassian Data Lake are aggregated into the same set of tables.

Tables for issue data

Jira issue enhanced table

The jira_issue_enhanced_table table contains information about all your Jira issues.

The table contains the following columns:

Assignee account ID

The unique identifier of the person assigned to the issue.

SQL name

assignee_account_id

Data type

String

Foreign key

Links to a record in the atlassian_account table in the data share schema for organization data.

Created at

The date and time (UTC time zone) when the issue was created.

SQL name

created_at

Data type

Timestamp

Creator account ID

The unique identifier of the person who created the issue.

SQL name

creator_account_id

Data type

String

Foreign key

Links to a record in the atlassian_account table in the data share schema for organization data.

Description

The description of the issue.

SQL name

description

Data type

String

Due date

The due date (UTC time zone) of the issue.

SQL name

due_date

Data type

Timestamp

Environment

A short description of the environment in which the issue occurred.

SQL name

environment

Data type

String

Issue ID

The unique identifier of the issue. Use this as the primary key for this table.

SQL name

issue_id

Data type

String

Issue key

The number of the issue within the project.

SQL name

issue_key

Data type

String

Example value

TEST-12

Issue ref

The identifier of the issue within a Jira site.

It can be used to associate this record with the issue identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name

issue_ref

Data type

String

Issue type

The type of issue.

SQL name

issue_type

Data type

String

Original time estimate secs

This column requires time-tracking to be enabled.

The original time estimate (in seconds) that was set.

SQL name

original_time_estimate_secs

Data type

Float

Parent issue ID

The unique identifier of the immediate parent of the issue.

SQL name

parent_issue_id

Data type

String

Foreign key

Links to another record in the jira_issue_enhanced_table table.

Priority

The current issue priority.

SQL name

priority

Data type

String

Priority sort order

The sequence order of the priority, matching its order of appearance in the Jira product.

SQL name

priority_sort_order

Data type

Int

Project ID

The unique identifier of the project that the issue is in.

SQL name

project_id

Data type

String

Foreign key

Links to a record in the jira_project table.

Reporter account ID

The unique identifier of the person who reported the issue.

SQL name

reporter_account_id

Data type

String

Foreign key

Links to a record in the atlassian_account table in the data share schema for organization data.

Resolution

The current issue resolution.

SQL name

resolution

Data type

String

Resolution at

The date and time (UTC time zone) when the issue was resolved.

SQL name

resolution_at

Data type

Timestamp

Resolution sort order

The sequence order of the resolution, matching its order of appearance in the Jira product.

SQL name

resolution_sort_order

Data type

Int

Status

The current issue status.

SQL name

status

Data type

String

Status category

The status category of the current issue status.

SQL name

status_category

Data type

String

Status sort order

The sequence order of the status, matching its order of appearance in the Jira product.

SQL name

status_sort_order

Data type

Int

Story points

Only holds data for issues in team-managed projects. To get story points for issues in company-managed projects, use the “Issue field” table.

The number of story points assigned to the issue.

SQL name

story_points

Data type

Float

Summary

The summary of the issue.

SQL name

summary

Data type

String

Team ID

The unique identifier of the team associated with the issue.

SQL name

team_id

Data type

String

Time estimate secs

This column requires time-tracking enabled.

The estimated time (in seconds) remaining from the original estimate.

SQL name

time_estimate_secs

Data type

Float

Time spent secs

This column requires time-tracking enabled.

The amount of logged work (in seconds). The field is NULL if there is no time spent.

SQL name

time_spent_secs

Data type

Float

Updated at

The date and time (UTC time zone) when the issue was last updated.

SQL name

updated_at

Data type

Timestamp

URL

The URL of the issue.

SQL name

url

Data type

String

Jira issue field

The jira_issue_field table lists the contents of Jira fields, including custom fields.

The table contains the following columns:

Field ID

The unique identifier of the Jira issue field. Use this as the primary key for this table.

SQL name

field_id

Data type

String

Foreign key

Links to a record in the jira_issue_field table.

Field type key

Indicates the type of field.

SQL name

field_type_key

Data type

String

Issue ID

The unique identifier of an issue.

SQL name

issue_id

Data type

String

Foreign key

Links to a record in the jira_issue_enhanced_table table.

Project ID

The unique identifier of a project.

SQL name

project_id

Data type

String

Foreign key

Links to a record in the jira_project table.

Value

The contents of the Jira field for this issue. The type of data represented by this string will depend on the field.

SQL name

value

Data type

String

Jira issue field metadata

The jira_issue_field_metadata table holds additional information about each Jira issue field.

The table contains the following columns:

Field ref

The identifier of the field within a Jira site.

It can be used to associate this record with the field identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name

field_ref

Data type

String

Issue field ID

The unique identifier of the issue field that’s associated with the metadata.

SQL name

issue_field_id

Data type

String

Foreign key

Links to a record in the jira_issue_field table.

Key

The key of the issue field. The key can hold a different value to the ID when it’s defined by plugins.

SQL name

key

Data type

String

Name

The name of the issue field.

SQL name

name

Data type

String

Type

The type of data stored in the issue field.

SQL name

type

Data type

String

Jira issue field option

The jira_issue_field_option table stores the selectable set of values for the following types of Jira issue fields: select lists, multi-select lists, cascading select lists, radio buttons, and multi-checkboxes.

The table contains the following columns:

Field ID

The unique identifier of the issue field.

SQL name

field_id

Data type

String

Foreign key

Links to a record in the jira_issue_field table.

Issue field option ID

The unique identifier of the issue field option. Use this as the primary key for this table.

SQL name

issue_field_option_id

Data type

String

Issue field option ref

The identifier for the issue field option within a Jira site.

SQL name

issue_field_option_ref

Data type

String

Value

The value that the issue field option contains.

SQL name

value

Data type

String

Jira issue fix version mapping

The jira_issue_fix_version_mapping table holds associations between issues and project versions that are fix versions for an issue.

The table contains the following columns:

Issue ID

The unique identifier of an issue.

SQL name

issue_id

Data type

String

Foreign key

Links to a record in the jira_issue_enhanced_table table.

Project ID

The unique identifier of a project.

SQL name

project_id

Data type

String

Foreign key

Links to a record in the jira_project table.

Version ID

The unique identifier of a project version, which is the fix version of the issue.

SQL name

version_id

Data type

String

Foreign key

Links to a record in the jira_project_version table.

Jira issue history change item

The jira_issue_history_change_item table provides details of each Jira issue’s changes, capturing information such as when a field was updated, what the previous and new values are, and who made the change. If multiple updates are made at the same time, these updates will share the same changelog ID.

The table contains the following columns:

Author ID

The unique identifier of the author.

SQL name

author_id

Data type

String

Changelog ID

The unique identifier of the changelog. Change items will share the same changelog ID if they were updated at the same time.

SQL name

changelog_id

Data type

String

Created at

The date and time (UTC time zone) when the change was made.

SQL name

created_at

Data type

Timestamp

Field

The name of the field that was changed.

SQL name

field

Data type

String

Field ID

The unique identifier of the field that was changed.

SQL name

field_id

Data type

String

Foreign key

Links to a record in the jira_issue_field table.

Field type

The type of the field that was updated.

It can be NULL if the field was removed since the history record was created.

SQL name

field_type

Data type

String

Issue history ID

The unique identifier of the issue history associated with the history change item.

SQL name

issue_history_id

Data type

String

Issue ID

The unique identifier of an issue that was updated.

SQL name

issue_id

Data type

String

Foreign key

Links to a record in the jira_issue_enhanced_table table.

New string

A string representing the value of the field after the change.

SQL name

new_string

Data type

String

New value

The value of the field after the change.

SQL name

new_value

Data type

String

Old string

A string representing the value of the field before the change.

SQL name

old_string

Data type

String

Old value

The value of the field before the change.

SQL name

old_value

Data type

String

Jira issue priority

The jira_issue_priority table holds information about each issue priority.

The table contains the following columns:

Name

The name of the issue priority.

SQL name

name

Data type

String

Priority ID

The unique identifier of the issue priority. Use this as the primary key for this table.

SQL name

priority_id

Data type

String

Priority ref

The identifier of the issue priority within a Jira site.

It can be used to associate this record with the priority identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name

priority_ref

Data type

String

Sort order

The rank of the issue priority.

SQL name

sort_order

Data type

BigInt

Jira issue resolution

The jira_issue_resolution table holds information about each issue resolution.

The table contains the following columns:

Issue resolution ID

The unique identifier of the issue resolution. Use this as the primary key for the table.

SQL name

issue_resolution_id

Data type

String

Name

The name of the issue resolution.

SQL name

name

Data type

String

Resolution ref

The identifier of the issue resolution within a Jira site.

It can be used to associate this record with the resolution identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name

resolution_ref

Data type

String

Sort order

Ranks the order of the issue resolutions.

SQL name

sort_order

Data type

BigInt

Jira issue status

The jira_issue_status table holds information about each issue status.

The table contains the following columns:

Category name

The name of the category that the issue status belongs to.

SQL name

category_name

Data type

String

Issue status ID

The unique identifier of the issue status. Use this as the primary key for the table.

SQL name

issue_status_id

Data type

String

Name

The name of the issue status.

SQL name

name

Data type

String

Status rank

The rank of the status used to order the issue statuses.

SQL name

status_rank

Data type

BigInt

Status ref

The identifier of the issue status within a Jira site.

It can be used to associate this record with the status identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name

status_ref

Data type

String

Jira issue type

The jira_issue_type table holds information about each issue type.

The table contains the following columns:

Issue type ID

The unique identifier of the issue type. Use this as the primary key for the table.

SQL name

issue_type_id

Data type

String

Issue type ref

The identifier of the issue type within a Jira site.

It can be used to associate this record with the issue type identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name

issue_type_ref

Data type

String

Name

The name of the issue type.

SQL name

name

Data type

String

Project ID

The unique identifier of the project that the issue type belongs to.

SQL name

project_id

Data type

String

Foreign key

Links to a record in the jira_project table.


Tables for project data

Jira project

The jira_project table lists all your Jira projects.

The table contains the following columns:

Avatar ref

A reference to the icon of the project. You can use it to create the avatar URL.

SQL name

avatar_ref

Data type

String

Category ID

The unique identifier of the project category.

SQL name

category_id

Data type

String

Foreign key

Links to a record in the jira_project_category table.

Default assignee type

The default assignee setting for the project.

SQL name

default_assignee_type

Data type

String

Description

The description of the project.

SQL name

description

Data type

String

Key

The unique key of the project.

SQL name

key

Data type

String

Lead account ID

The unique identifier of the person defined as the project lead.

SQL name

lead_account_id

Data type

String

Foreign key

Links to a record in the atlassian_account table in the data share schema for organization data.

Name

The name of the project.

SQL name

name

Data type

String

Project ID

The unique identifier of the project. Use this as the primary key for this table.

SQL name

project_id

Data type

String

Project ref

The identifier of the project within a Jira site.

It can be used to associate this record with the project identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name

project_ref

Data type

BigInt

Type

The type of Jira project (in other words, Jira, Jira Service Management, and so on).

SQL name

type

Data type

String

Jira project category

The jira_project_category table lists your project categories.

The table contains the following columns:

Category ID

The unique identifier of the project category. Use this as the primary key for the table.

SQL name

category_id

Data type

String

Category ref

The identifier of the project category within a Jira site.

It can be used to associate this record with the category identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name

category_ref

Data type

String

Name

The name of the project category.

SQL name

name

Data type

String

Jira project component

The jira_project_component table lists your project components.

The table contains the following columns:

Assignee type

The type of assignee that is assigned to issues created with this component. The assignee type for the project component can be one of the following: project lead, unassigned, component lead, or project default.

SQL name

assignee_type

Data type

String

Example values

  • COMPONENT_LEAD

  • PROJECT_LEAD

  • PROJECT_DEFAULT

  • UNASSIGNED

Component ID

The unique identifier of the project component. Use this as the primary key for this table.

SQL name

component_id

Data type

String

Component lead ID

The unique identifier of the person designated as being responsible for issues that have the component.

SQL name

component_lead_id

Data type

String

Component ref

The identifier of the component within a Jira site.

SQL name

component_ref

Data type

BigInt

Name

The name of the project component.

SQL name

name

Data type

String

Project ID

The unique identifier of the project that contains the component.

SQL name

project_id

Data type

String

Foreign key

Links to a record in the jira_project table.

Jira project version

The jira_project_version table lists your project versions.

The table contains the following columns:

Description

The description of the project version.

SQL name

description

Data type

String

Name

The name of the project version.

SQL name

name

Data type

String

Project ID

The unique identifier of the project associated with the project version.

SQL name

project_id

Data type

String

Foreign key

Links to a record in the jira_project table.

Project version ID

The unique identifier of the project version. Use this as the primary key for the table.

SQL name

project_version_id

Data type

String

Released at

The release date of the project version.

SQL name

released_at

Data type

date

Sort order

Ranks the order of the project versions.

SQL name

sort_order

Data type

BigInt

Started at

The start date of the project version.

SQL name

started_at

Data type

Date

Status

The status of the project version.

SQL name

status

Data type

String

Version ref

The identifier of the project version within a Jira site.

SQL name

name

Data type

BigInt


Tables specific to Jira Service Management

JSM incident responder

The jsm_incident_responder table lists the responders to an incident, as defined in Jira Service Management.

The table contains the following columns:

Issue ID

The unique identifier of the Jira issue representing the incident.

SQL name

issue_id

Data type

String

Project ID

The unique identifier of the project containing the incident.

SQL name

project_id

Data type

String

Responder ID

The unique identifier of the responder. Use this as the primary key for the table.

SQL name

responder_id

Data type

String

Responder type

The type of incident responder.

SQL name

responder_type

Data type

String

Still need help?

The Atlassian Community is here for you.