Automating Jira → Snowflake Data Pipeline Using ScriptRunner

[Jira Cloud Projects] 
    ↓ (JQL + ScriptRunner Groovy)
ScriptRunner Automation Job
    ↓ (JSON Dataset)
HTTPS POST → Snowflake REST API (INSERT… SQL/PUT)
    ↓
Snowflake Table (RAW or STG Schema)
    ↓
Reporting / SLA Dashboard / BI Tools

CREATE OR REPLACE TABLE jira_issues_raw (
    issue_key      STRING,
    summary        STRING,
    status         STRING,
    project        STRING,
    issue_type     STRING,
    created_at     TIMESTAMP_TZ,
    updated_at     TIMESTAMP_TZ,
    assignee       STRING,
    reporter       STRING,
    priority       STRING,
    raw_payload    VARIANT
);

String jql = """
project in ("Service Desk", "Internal Software") 
AND statusCategory != Done 
ORDER BY updated DESC
"""
def issues = get("/rest/api/3/search")
    .queryString("jql", jql)
    .queryString("maxResults", "1000")
    .asObject(Map)
    .body.issues

import groovy.json.JsonOutput

def rows = issues.collect { it ->
    [
        issue_key : it.key,
        summary   : it.fields.summary,
        status    : it.fields.status.name,
        project   : it.fields.project.key,
        issue_type: it.fields.issuetype.name,
        created_at: it.fields.created,
        updated_at: it.fields.updated,
        assignee  : it.fields.assignee?.displayName,
        reporter  : it.fields.reporter?.displayName,
        priority  : it.fields.priority?.name,
        raw_payload: it // entire issue object
    ]
}
String jsonBody = JsonOutput.toJson(rows)

import javax.crypto.Mac
import javax.crypto.spec.SecretKeySpec
import java.util.Base64

// Snowflake credentials
def sfAccount   = "xy12345.eu-central-1"
def sfUser      = "JIRA_LOADER"
def sfPassword  = "StrongPassword!"
def sfWarehouse = "JIRA_WH"
def sfDatabase  = "JIRA_DB"
def sfSchema    = "PUBLIC"
def sfRole      = "JIRA_ROLE"

// Build INSERT SQL
def sql = """
INSERT INTO jira_issues_raw (
    issue_key, summary, status, project, issue_type,
    created_at, updated_at, assignee, reporter, priority, raw_payload
)
SELECT
    value:issue_key,
    value:summary,
    value:status,
    value:project,
    value:issue_type,
    TO_TIMESTAMP(value:created_at),
    TO_TIMESTAMP(value:updated_at),
    value:assignee,
    value:reporter,
    value:priority,
    parse_json(value:raw_payload)
FROM TABLE(FLATTEN(input => PARSE_JSON('$jsonBody')));
"""

def insertBody = [
    "sqlText": sql,
    "sequenceId": 1
]

def response = post("https://${sfAccount}.snowflakecomputing.com/api/v2/statements")
    .header("Content-Type", "application/json")
    .basicAuth(sfUser, sfPassword)
    .body(insertBody)
    .asString()

return response.body

SELECT * FROM jira_issues_raw ORDER BY updated_at DESC LIMIT 10;

CREATE VIEW jira_sla_summary AS
SELECT
    project,
    COUNT(*) AS open_issues,
    COUNT_IF(status = 'Done') AS completed,
    AVG(DATEDIFF('day', created_at, updated_at)) AS avg_resolution_days
FROM jira_issues_raw
GROUP BY project;