JIRA adatok adattárházba töltése Custom Field kezelés

🔧 1. Custom Field kezelés – JIRA saját mezők rugalmas tárolása

🎯 Probléma

A JIRA REST API „fields” objektuma gyakran tartalmaz egyedi mezőket ilyen néven:

---json
"customfield_10021": "High",
"customfield_10200": {
  "value": "Strategic"
}

Ezek:

projektfüggőek
JSON típusuk változó lehet (string, object, list)
nem fix struktúrájúak

✅ Javasolt modell

Külön „kulcs-érték” alapú tábla:

---sql
CREATE TABLE stg_jira_custom_fields (
    issue_id     VARCHAR2(20),
    field_key    VARCHAR2(100),
    field_type   VARCHAR2(20),    -- STRING / OBJECT / ARRAY
    field_value  CLOB
);

🐍 Python kód – egyedi mezők kinyerése:

---python
custom_fields = [
    "customfield_10021",
    "customfield_10200",
    "customfield_11000"
]

for issue in issues:
    fields = issue["fields"]
    issue_id = issue["id"]

    for field_key in custom_fields:
        if field_key in fields:
            value = fields[field_key]
            if isinstance(value, dict):
                field_type = "OBJECT"
                field_value = json.dumps(value)
            elif isinstance(value, list):
                field_type = "ARRAY"
                field_value = json.dumps(value)
            else:
                field_type = "STRING"
                field_value = str(value)

            cursor.execute("""
                INSERT INTO stg_jira_custom_fields (issue_id, field_key, field_type, field_value)
                VALUES (:1, :2, :3, :4)
            """, (issue_id, field_key, field_type, field_value))

👉 A field_value értéket akár Oracle-ben is tovább feldolgozhatod JSON_TABLE segítségével.

📥 2. JSON Parsing Oracle oldalon – `JSON_TABLE` mély strukturákhoz

Ha Pythonban csak betöltöd a teljes JSON-t az stg_jira_raw_json táblába, Oracle SQL-ben is feldolgozhatod.

📘 Táblastruktúra

---sql
CREATE TABLE stg_jira_raw_json (
    issue_id VARCHAR2(20),
    json_data CLOB,
    load_date DATE DEFAULT SYSDATE
);

🧩 Példa – egy `customfield_10200.value` kinyerése SQL-ben:

---sql
SELECT issue_id, jt.custom_val
FROM stg_jira_raw_json s,
     JSON_TABLE(
       s.json_data, '$'
       COLUMNS (
         custom_val VARCHAR2(400) PATH '$.fields.customfield_10200.value'
       )
     ) jt;

Ha customfield_10200 egy komplex objektum, így lehet objektum teljes értékét lekérni:

---sql
COLUMNS (
   custom_raw CLOB PATH '$.fields.customfield_10200'
)

🧪 3. Unit tesztelés – Python + Oracle PL/SQL ETL-hez

🎯 Miért fontos?

biztosítja a JSON → SQL konverzió helyességét
segít elkapni változásokat (pl. új JIRA mező struktúra)

✅ Python oldal – `pytest` + mock JSON

A. Tesztelhető függvény:

---python
def extract_custom_fields(issue: dict, custom_fields: list):
    extracted = []
    for key in custom_fields:
        value = issue["fields"].get(key)
        if value is not None:
            if isinstance(value, dict):
                ftype = "OBJECT"
                fvalue = json.dumps(value)
            elif isinstance(value, list):
                ftype = "ARRAY"
                fvalue = json.dumps(value)
            else:
                ftype = "STRING"
                fvalue = str(value)
            extracted.append((issue["id"], key, ftype, fvalue))
    return extracted

B. Unit teszt:

---python
import pytest

def test_extract_custom_fields():
    issue = {
        "id": "10001",
        "fields": {
            "customfield_10021": "High",
            "customfield_10200": {"value": "Strategic"},
            "customfield_10500": ["A", "B"]
        }
    }
    result = extract_custom_fields(issue, ["customfield_10021", "customfield_10200", "customfield_10500"])
    assert len(result) == 3
    assert result[0][2] == "STRING"
    assert result[1][2] == "OBJECT"
    assert result[2][2] == "ARRAY"

🧪 PL/SQL oldalon – tesztelhető betöltő eljárások

Javasolt: külön betöltő, ellenőrző és naplózó modulok PL/SQL-ben

Egyszerű teszt PL/SQL-ből:

---sql
BEGIN
  -- Tesztadat
  INSERT INTO stg_jira_custom_fields(issue_id, field_key, field_type, field_value)
  VALUES ('10001', 'customfield_10200', 'OBJECT', '{"value":"Strategic"}');

  COMMIT;

  -- Eljárás hívása
  pkg_jira_etl.p_load_custom_fields;

  -- Ellenőrzés
  DECLARE
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count
    FROM dw_jira_custom_fields
    WHERE issue_id = '10001' AND field_key = 'customfield_10200';
    
    IF v_count = 0 THEN
      raise_application_error(-20001, 'Custom field betöltés sikertelen');
    END IF;
  END;
END;

🧠 1. Mi az a custom field a JIRA-ban?

A JIRA alapértelmezett mezői: summary, status, assignee, created, stb.

A custom mezők (pl. customfield_10021) viszont:

adminisztrátor által hozzáadott extra mezők,
lehetnek szöveg, szám, lista, felhasználó, dátum, választó mezők,
gyakran projektenként más-más értelmet hordoznak,
a REST API-ban customfield_* néven jelennek meg.

Példák:

---json
"customfield_10021": "Kritikus",
"customfield_10112": {
  "value": "Közepes"
},
"customfield_10500": ["Finance", "Legal"]

🧬 2. Custom field típusok és specialitások

Típus	API példa	Feldolgozás típusa
Text (string)	`"customfield_10021": "Kritikus"`	STRING
Select list	`"customfield_10112": {"value": "Magas"}`	OBJECT
Multi-select	`"customfield_10500": [{"value":"IT"},...]`	ARRAY of OBJECT
Number	`"customfield_10900": 7`	NUMBER
Date	`"customfield_11000": "2024-12-01T00:00:00.000Z"`	DATE
User	`"customfield_11111": {"displayName": "Anna"}`	OBJECT
Cascading	`"customfield_11200": {"parent":{"value":"A"},"value":"B"}`	OBJECT (nested)

🧰 3. Táblamodellezés Oracle-ben

🔸 A rugalmas, jövőálló modell:

`stg_jira_custom_fields`

---sql
CREATE TABLE stg_jira_custom_fields (
    issue_id      VARCHAR2(20),
    field_key     VARCHAR2(100),   -- customfield_10021
    field_type    VARCHAR2(20),    -- STRING, OBJECT, ARRAY, DATE
    field_value   CLOB,            -- nyers JSON string vagy egyszerű érték
    extract_dt    DATE DEFAULT SYSDATE
);

`dw_jira_custom_fields`

---sql
CREATE TABLE dw_jira_custom_fields (
    issue_id      VARCHAR2(20),
    field_key     VARCHAR2(100),
    field_type    VARCHAR2(20),
    field_value   VARCHAR2(4000),
    valid_from    DATE,
    valid_to      DATE
);

dw_jira_custom_fields: akár SCD (Slowly Changing Dimension) modellben is megvalósítható, ha változáskövetés kell.

🐍 4. Python feldolgozás – custom mezők dinamikus szedése

Python kódrészlet:

---python
CUSTOM_FIELDS = [
    "customfield_10021",
    "customfield_10112",
    "customfield_10500"
]

def extract_custom_fields(issue, field_keys):
    result = []
    issue_id = issue["id"]
    fields = issue["fields"]

    for key in field_keys:
        if key in fields:
            val = fields[key]
            if isinstance(val, dict):
                ftype = "OBJECT"
                fvalue = json.dumps(val)
            elif isinstance(val, list):
                ftype = "ARRAY"
                fvalue = json.dumps(val)
            elif isinstance(val, (int, float)):
                ftype = "NUMBER"
                fvalue = str(val)
            elif isinstance(val, str):
                ftype = "STRING"
                fvalue = val
            else:
                ftype = "UNKNOWN"
                fvalue = str(val)

            result.append((issue_id, key, ftype, fvalue))
    return result

🔁 Insertálás Oracle-be:

---python
for r in extract_custom_fields(issue, CUSTOM_FIELDS):
    cursor.execute("""
        INSERT INTO stg_jira_custom_fields(issue_id, field_key, field_type, field_value)
        VALUES (:1, :2, :3, :4)
    """, r)

🔍 5. Oracle oldali feldolgozás JSON_TABLE-lel

Példa: `customfield_10112.value` kiszedése SQL-ben:

---sql
SELECT issue_id,
       jt.value
FROM stg_jira_custom_fields s,
     JSON_TABLE(s.field_value, '$'
        COLUMNS (value VARCHAR2(100) PATH '$.value')
     ) jt
WHERE s.field_key = 'customfield_10112';

Tömbös mezők (pl. több választás):

---sql
SELECT issue_id, jt.value
FROM stg_jira_custom_fields s,
     JSON_TABLE(s.field_value, '$[*]'
        COLUMNS (value VARCHAR2(100) PATH '$.value')
     ) jt
WHERE s.field_type = 'ARRAY';

🧩 6. Értelmező tábla – jelentésekhez

Mivel a customfield_10021 jelentése projektfüggő, célszerű fenntartani egy értelmező táblát:

---sql
CREATE TABLE jira_custom_field_dictionary (
    field_key      VARCHAR2(100),
    field_name     VARCHAR2(200),
    project_key    VARCHAR2(50),
    description    VARCHAR2(400),
    business_meaning VARCHAR2(400)
);

Ebben tárolhatod:

hogy mi a mező valódi neve,
milyen jelentést hordoz (pl. "Ügyfél prioritás"),
mely projekt(ek)ben aktív

✅ 7. Legjobb gyakorlatok – Custom Field kezelése

Téma	Gyakorlat
Ismeretlen mezők	Naplózd és figyeld az új `customfield_*` megjelenését
Mezőtípusok	Tárold a típust (STRING/OBJECT/ARRAY)
Dokumentáció	Vezess `jira_custom_field_dictionary` táblát
Változások kezelése	Archiváld a korábbi értékeket (valid_from / valid_to)
Tömb kezelés	Több sorba szedd szét a JSON listákat

🎯 Cél

Példa: a customfield_10021 nevű mezőt szeretnénk az adattárházban egy konkrét oszlopba pl. customer_priority nevű oszlopba leképezni.

🧰 Megközelítések

🔹 1. Statikus leképezés (hardcoded)

Használható, ha a JIRA mezőstruktúra stabil és kevés mezőt kell leképezni.

Példa: staging → DW tábla leképezés

---sql
INSERT INTO dw_jira_issues (
    issue_id,
    summary,
    customer_priority
)
SELECT
    i.issue_id,
    i.summary,
    cf.field_value
FROM stg_jira_issues i
LEFT JOIN stg_jira_custom_fields cf
    ON i.issue_id = cf.issue_id
   AND cf.field_key = 'customfield_10021';

📝 Megjegyzés:

cf.field_value itt egyszerű szövegként van elmentve.
Ha JSON objektum, használj JSON_VALUE() vagy JSON_TABLE().

🔹 2. Dinamikus értelmezés dictionary tábla alapján

`jira_custom_field_mapping` – leképezési metaadat

---sql
CREATE TABLE jira_custom_field_mapping (
    field_key          VARCHAR2(100),    -- customfield_10021
    dw_column_name     VARCHAR2(100),    -- customer_priority
    is_active          CHAR(1),          -- 'Y'
    expected_type      VARCHAR2(20),     -- STRING / OBJECT / ARRAY
    extract_json_path  VARCHAR2(200),    -- $.value vagy $[*].value
    default_value      VARCHAR2(4000)
);

Példatartalom:

field_key	dw_column_name	extract_json_path	expected_type	default_value
customfield_10021	customer_priority	$.value	OBJECT	Unknown

🧠 3. Feldolgozó PL/SQL eljárás – dinamikus JSON parsing

JSON → oszlop leképezés betöltés

---sql
MERGE INTO dw_jira_issues d
USING (
  SELECT
      i.issue_id,
      jt.value AS customer_priority
  FROM stg_jira_issues i
  LEFT JOIN stg_jira_custom_fields cf
    ON cf.issue_id = i.issue_id
   AND cf.field_key = 'customfield_10021'
  LEFT JOIN JSON_TABLE(cf.field_value, '$'
     COLUMNS (value VARCHAR2(100) PATH '$.value')
  ) jt
) s
ON (d.issue_id = s.issue_id)
WHEN MATCHED THEN UPDATE SET d.customer_priority = s.customer_priority;

💡 Tipp: JSON_TABLE segítségével bármilyen custom mezőt kinyerhetsz precízen, különösen, ha field_type = 'OBJECT'.

🧪 4. Validáció: típus és értékellenőrzés

Ha a mező típusa nem az elvárt (pl. ARRAY jön STRING helyett), akkor logold hibának:

---sql
INSERT INTO etl_error_log(message, issue_id, field_key, field_value)
SELECT 'Típuseltérés: nem STRING', issue_id, field_key, field_value
FROM stg_jira_custom_fields
WHERE field_key = 'customfield_10021'
  AND field_type != 'OBJECT';

🗃️ 5. Hasznos: leképező nézet (view)

Készíthetsz egy nézetet, amely előfeldolgozott custom mezőket mutat normál mezőként:

---sql
CREATE OR REPLACE VIEW vw_jira_issues_with_custom AS
SELECT
  i.issue_id,
  i.summary,
  (
    SELECT jt.value
    FROM stg_jira_custom_fields cf,
         JSON_TABLE(cf.field_value, '$'
           COLUMNS (value VARCHAR2(100) PATH '$.value')
         ) jt
    WHERE cf.issue_id = i.issue_id
      AND cf.field_key = 'customfield_10021'
  ) AS customer_priority
FROM stg_jira_issues i;

Így egyszerűbb lesz riportokat vagy betöltést írni.

📌 Összefoglalva

Lépés	Leírás
1.	Azonosítsd a `customfield_xxxx` mező jelentését
2.	Döntsd el: statikus vagy dinamikus leképezést használsz
3.	Tárold a típust (STRING/OBJECT), és ha kell, JSON path-t
4.	Használj `JSON_TABLE` / `JSON_VALUE` függvényt a pontos kivételhez
5.	Készíts nézetet vagy ETL eljárást, ami leképezi a DW oszlopokat

🧭 Cél

A customfield_10021 mező "Magas" értéke az adattárházban jelenjen meg pl. "Customer Critical" néven.
A JIRA-ban szereplő értékek gyakran lokalizáltak vagy informálisak, de az adattárház standardizált kódokat vár (pl. CRITICAL, MEDIUM, LOW).

🧱 1. Értelmező dictionary tábla létrehozása

---sql
CREATE TABLE jira_custom_field_dictionary (
    field_key        VARCHAR2(100),   -- pl. customfield_10021
    jira_value       VARCHAR2(400),   -- pl. 'Magas'
    dw_code          VARCHAR2(100),   -- pl. 'CRITICAL'
    dw_description   VARCHAR2(400),   -- pl. 'Customer Critical'
    valid_from       DATE DEFAULT SYSDATE,
    valid_to         DATE
);

📌 Ez a tábla azt mondja meg:
customfield_10021 = "Magas" → CRITICAL / "Customer Critical"

🧪 2. Minta adat

field_key	jira_value	dw_code	dw_description
customfield_10021	Alacsony	LOW	Customer Low Risk
customfield_10021	Közepes	MEDIUM	Customer Medium
customfield_10021	Magas	CRITICAL	Customer Critical
customfield_10021	Üres	NULL	No Priority Set

🔄 3. Betöltés értelmezéssel (Oracle SQL)

🔹 Adattárház tábla célstruktúrája:

---sql
CREATE TABLE dw_jira_issues (
    issue_id             VARCHAR2(20),
    summary              VARCHAR2(500),
    customer_priority_cd VARCHAR2(50),
    customer_priority_tx VARCHAR2(100),
    load_dt              DATE DEFAULT SYSDATE
);

🔹 Betöltés dictionary alapján:

---sql
MERGE INTO dw_jira_issues d
USING (
  SELECT
      i.issue_id,
      i.summary,
      dict.dw_code AS customer_priority_cd,
      dict.dw_description AS customer_priority_tx
  FROM stg_jira_issues i
  LEFT JOIN stg_jira_custom_fields cf
    ON cf.issue_id = i.issue_id
   AND cf.field_key = 'customfield_10021'
  LEFT JOIN JSON_TABLE(cf.field_value, '$'
     COLUMNS (value VARCHAR2(100) PATH '$.value')
  ) jt
    ON 1=1
  LEFT JOIN jira_custom_field_dictionary dict
    ON dict.field_key = cf.field_key
   AND dict.jira_value = jt.value
) s
ON (d.issue_id = s.issue_id)
WHEN MATCHED THEN UPDATE SET
    d.customer_priority_cd = s.customer_priority_cd,
    d.customer_priority_tx = s.customer_priority_tx;

✅ A JSON-ből kihámozott "Magas" érték itt "CRITICAL"-ra van leképezve.

🧩 4. Python oldalon is megvalósítható

Ha Pythonban szeretnéd értelmezni JSON alapján, készíthetsz egy dictionary mappinget:

---python
CUSTOMFIELD_10021_MAP = {
    "Alacsony": ("LOW", "Customer Low Risk"),
    "Közepes": ("MEDIUM", "Customer Medium"),
    "Magas": ("CRITICAL", "Customer Critical")
}

value = parsed_json["fields"]["customfield_10021"]["value"]
code, desc = CUSTOMFIELD_10021_MAP.get(value, ("UNKNOWN", "Unknown Priority"))

📈 5. Alternatíva: Nézet (view) dictionary csatlakoztatással

---sql
CREATE OR REPLACE VIEW vw_jira_issues_with_priority AS
SELECT
  i.issue_id,
  i.summary,
  dict.dw_code AS customer_priority_cd,
  dict.dw_description AS customer_priority_tx
FROM stg_jira_issues i
LEFT JOIN stg_jira_custom_fields cf
  ON cf.issue_id = i.issue_id
 AND cf.field_key = 'customfield_10021'
LEFT JOIN JSON_TABLE(cf.field_value, '$'
  COLUMNS (value VARCHAR2(100) PATH '$.value')
) jt
  ON 1=1
LEFT JOIN jira_custom_field_dictionary dict
  ON dict.field_key = cf.field_key
 AND dict.jira_value = jt.value;

🧠 Tipp: Dictionary fenntartása

Szkripttel naponta ellenőrizheted, ha új jira_value érték jelenik meg, amit nem tudtok még leképezni.
Példa:

---sql
SELECT DISTINCT cf.field_key, jt.value AS jira_value
FROM stg_jira_custom_fields cf,
     JSON_TABLE(cf.field_value, '$'
        COLUMNS (value VARCHAR2(100) PATH '$.value')
     ) jt
WHERE cf.field_key = 'customfield_10021'
  AND NOT EXISTS (
    SELECT 1 FROM jira_custom_field_dictionary d
    WHERE d.field_key = cf.field_key
      AND d.jira_value = jt.value
);

✅ Összefoglalás

Lépés	Leírás
1.	Hozz létre dictionary táblát, ami leképezi a `jira_value`-t DW kódra
2.	A staging mezőből (`customfield_x`) JSON segítségével vedd ki az értéket
3.	Csatlakoztasd a `jira_custom_field_dictionary` táblát
4.	Töltsd az adattárházat a leképezett `dw_code` és `dw_description` értékekkel
5.	Monitorozd az új, nem leképezett értékek megjelenését naponta

🧩 Példa üzleti mező: `customfield_10021` – "Ügyfél prioritás"

A JIRA REST API JSON mezője így néz ki:

---json
"customfield_10021": {
  "self": "https://jira.example.com/rest/api/2/customFieldOption/10000",
  "value": "Magas",
  "id": "10000"
}

🔶 1. Python: JSON parsing és CSV staging export

---python
import json
import csv

CUSTOMFIELD_MAP = {
    "Alacsony": ("LOW", "Customer Low Risk"),
    "Közepes": ("MEDIUM", "Customer Medium"),
    "Magas": ("CRITICAL", "Customer Critical"),
}

def extract_issue_data(issue_json):
    fields = issue_json["fields"]
    issue_id = issue_json["id"]
    summary = fields.get("summary", "")
    cf_val = fields.get("customfield_10021", {})
    raw_value = cf_val.get("value", None)

    dw_code, dw_desc = CUSTOMFIELD_MAP.get(raw_value, ("UNKNOWN", "Unknown Priority"))

    return {
        "issue_id": issue_id,
        "summary": summary,
        "jira_value": raw_value,
        "dw_code": dw_code,
        "dw_description": dw_desc,
    }

# Fájl beolvasás és staging fájl írás
with open("jira_export.json") as f:
    data = json.load(f)

issues = data["issues"]

with open("jira_stage_issues.csv", "w", newline="") as csvfile:
    fieldnames = ["issue_id", "summary", "jira_value", "dw_code", "dw_description"]
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
    writer.writeheader()

    for issue in issues:
        row = extract_issue_data(issue)
        writer.writerow(row)

🔽 Ez a CSV-t állítja elő a staging betöltéshez (Oracle external table vagy SQL*Loader segítségével).

🔶 2. Oracle staging tábla

---sql
CREATE TABLE stg_jira_issues (
    issue_id            VARCHAR2(20),
    summary             VARCHAR2(500),
    jira_value          VARCHAR2(100),
    dw_code             VARCHAR2(50),
    dw_description      VARCHAR2(200),
    load_dt             DATE DEFAULT SYSDATE
);

🔶 3. Oracle dictionary tábla

---sql
CREATE TABLE jira_custom_field_dictionary (
    field_key       VARCHAR2(100),   -- 'customfield_10021'
    jira_value      VARCHAR2(100),   -- 'Magas'
    dw_code         VARCHAR2(50),    -- 'CRITICAL'
    dw_description  VARCHAR2(200),   -- 'Customer Critical'
    valid_from      DATE DEFAULT SYSDATE
);

-- Tartalom:
INSERT INTO jira_custom_field_dictionary (field_key, jira_value, dw_code, dw_description)
VALUES ('customfield_10021', 'Magas', 'CRITICAL', 'Customer Critical');

-- és hasonlók: Közepes, Alacsony stb.

🔶 4. Oracle adattárház cél tábla

---sql
CREATE TABLE dw_jira_issues (
    issue_id            VARCHAR2(20),
    summary             VARCHAR2(500),
    customer_priority_cd VARCHAR2(50),
    customer_priority_tx VARCHAR2(200),
    load_dt             DATE DEFAULT SYSDATE
);

🔶 5. Oracle betöltés stagingből dictionary-vel

---sql
MERGE INTO dw_jira_issues d
USING (
  SELECT
      s.issue_id,
      s.summary,
      COALESCE(dict.dw_code, s.dw_code) AS customer_priority_cd,
      COALESCE(dict.dw_description, s.dw_description) AS customer_priority_tx
  FROM stg_jira_issues s
  LEFT JOIN jira_custom_field_dictionary dict
    ON dict.field_key = 'customfield_10021'
   AND dict.jira_value = s.jira_value
) src
ON (d.issue_id = src.issue_id)
WHEN MATCHED THEN UPDATE SET
    d.summary = src.summary,
    d.customer_priority_cd = src.customer_priority_cd,
    d.customer_priority_tx = src.customer_priority_tx
WHEN NOT MATCHED THEN INSERT (
    issue_id, summary, customer_priority_cd, customer_priority_tx
) VALUES (
    src.issue_id, src.summary, src.customer_priority_cd, src.customer_priority_tx
);

📊 Eredmény

issue_id	summary	customer_priority_cd	customer_priority_tx
10001	Email bug	CRITICAL	Customer Critical
10002	Login failure	MEDIUM	Customer Medium

🧪 További javaslat: automatizált validáció (ellenőrzés)

---sql
SELECT DISTINCT jira_value
FROM stg_jira_issues
WHERE jira_value NOT IN (
    SELECT jira_value FROM jira_custom_field_dictionary
    WHERE field_key = 'customfield_10021'
);

Ezzel kiszűröd azokat az új értékeket, amelyeket még nem map-peltél le.

✅ Összefoglalás

Lépés	Eszköz	Mit csinálunk?
1.	Python	JSON API fájlt parse-olunk és CSV staging állományt írunk
2.	Oracle	staging tábla létrehozása
3.	Oracle	dictionary tábla létrehozása (üzleti jelentés)
4.	Oracle	cél DW tábla létrehozása
5.	Oracle	stagingből betöltés + értelmezés (MERGE vagy INSERT SELECT)
6.	Oracle	ellenőrzés új, nem leképezett értékekre

IT, BI, DWH, DM, AI