Oracle: แปลงข้อมูล JSON เป็น Table หรือ View

ในการส่งข้อมูลระหว่างแอพพลิเคชัน ในปัจจุบันปฏิเสธไม่ได้ว่ารูปแบบ JSON ได้รับความนิยมสูงมาก วันนี้มีโจทย์ในการพัฒนาระบบอย่างนึงที่ผมเจอคือ ได้ข้อมูลจาก log มาจัดเก็บในฐานข้อมูลซึ่งอยู่ในรูปแบบ JSON แต่ต้องการแยกเก็บเป็นฟิลด์เพื่อนำไปประมวลผล และวิเคราะห์ต่อ

วิธีการแปลงข้อมูลที่ผมเลือกใช้ คือใช้ฟังก์ชัน JSON_TABLE โดยตัวอย่างข้อมูล JSON มีรูปแบบดังนี้

{
  "latencies": {
    "request": 232,
    "kong": 12,
    "proxy": 220
  },
  "service": {
    "host": "alist-demo.psu.ac.th",
    "created_at": 1641960693,
    "connect_timeout": 60000,
    "id": "c00d9805-c281-4283-ab68-54e99719634a",
    "protocol": "http",
    "name": "test",
    "read_timeout": 60000,
    "port": 80,
    "path": "/services/commonservice.asmx/GetSite",
    "updated_at": 1646117362,
    "ws_id": "4920834d-f36d-41f6-8e98-959b4ec18b1b",
    "tags": {},
    "retries": 5,
    "write_timeout": 60000
  },
  "request": {
    "querystring": {},
    "size": 226,
    "uri": "/alist/getsite",
    "url": "http://api-gateway.psu.ac.th:8000/alist/getsite",
    "headers": {
      "host": "api-gateway.psu.ac.th:8000",
      "postman-token": "c7ee397e-1570-4e5d-9930-eac24fd2ecbc",
      "user-agent": "PostmanRuntime/7.29.0",
      "accept": "*/*",
      "connection": "keep-alive",
      "accept-encoding": "gzip, deflate, br"
    },
    "method": "GET"
  },
  "client_ip": "192.168.3.174",
  "tries": [
    {
      "balancer_latency": 0,
      "port": 80,
      "balancer_start": 1648009515575,
      "ip": "192.168.100.177"
    }
  ],
  "upstream_uri": "/services/commonservice.asmx/GetSite",
  "response": {
    "headers": {
      "content-type": "text/xml; charset=utf-8",
      "x-powered-by": "ASP.NET",
      "x-kong-proxy-latency": "12",
      "cache-control": "private, max-age=0",
      "x-ratelimit-remaining-minute": "9",
      "date": "Wed, 23 Mar 2022 04:25:15 GMT",
      "via": "kong/2.2.2",
      "x-aspnet-version": "4.0.30319",
      "x-frame-options": "SAMEORIGIN",
      "ratelimit-reset": "45",
      "content-length": "2622",
      "x-ratelimit-limit-minute": "10",
      "server": "Microsoft-IIS/10.0",
      "ratelimit-remaining": "9",
      "ratelimit-limit": "10",
      "x-kong-upstream-latency": "220",
      "connection": "close"
    },
    "status": 200,
    "size": 3109
  },
  "route": {
    "id": "db8cc7a7-77bb-4a3a-a059-06b8de3aa549",
    "paths": [
      "/alist/getsite"
    ],
    "protocols": [
      "http",
      "https"
    ],
    "strip_path": true,
    "created_at": 1641960726,
    "ws_id": "4920834d-f36d-41f6-8e98-959b4ec18b1b",
    "request_buffering": true,
    "name": "test-getsite",
    "updated_at": 1647326716,
    "preserve_host": false,
    "regex_priority": 0,
    "response_buffering": true,
    "https_redirect_status_code": 426,
    "path_handling": "v1",
    "service": {
      "id": "c00d9805-c281-4283-ab68-54e99719634a"
    }
  },
  "started_at": 1648009515563
}

จะเห็นได้ว่ามีจำนวน Key และ Value จำนวนมากและอยู่ในรูปแบบ Parent Chid ด้วยแต่สามารถสร้าง View หรือ Table จากข้อมูลดังกล่าวด้วยคำสั่งเดียวดังนี้

  create or replace view v_log_traffic as 
  select t.*
  from log_traffic l,
    json_table(l.detail, '$'
    columns
      service_name path '$.service.name',
      service_path path '$.service.path',
      request_url path '$.request.url',
      client_ip path '$.client_ip',
      response_status path '$.response.status',
      response_size path '$.response.size',
      latencies_request path '$.latencies.request',
      latencies_kong path '$.latencies.kong',
      latencies_proxy path '$.latencies.proxy') t;

สังเกตุว่า จุดสำคัญคือคำสั่ง json_table ที่เราจะต้องระบุฟิลด์ที่เก็บข้อมูล JSON ของเราดังในตัวอย่างคือ l.detail ที่เดิมเก็บอยู่ในตาราง log_traffic โดยใช้ path ย่อเป็น ‘$’ จากนั้นระบุ Keyword columns และตั้งชื่อ columns สำหรับ view ที่เราจะสร้างเช่น service_name ซึ่งใช้เก็บข้อมูลที่อยู่ใน path ดังนี้ ‘$.service.name’ เป็นต้น ซึ่งเมื่อรันคำสั่งดังกล่าวจะได้ view ดังรูป

ในเรื่องของประสิทธิภาพพบว่าประมวลผลจำนวน 4000+ รายการใช้เวลาประมาณ 0.0008 วินาที ซึ่งถือว่ารวดเร็ว แต่ถ้าจำนวนข้อมูลมากขึ้น และประมวลผลช้าลงอาจปรับเป็น Materialize View หรือวิธีการอื่นๆ ที่ใช้ในการประมวลผลเฉพาะรายการที่เพิ่มใหม่ หรือประมวลผลตามรอบเวลา (ไม่เรียลไทม์) โดยใช้คำสั่งดังกล่าวช่วยก็ได้เช่นกัน หวังว่าจะเป็นประโยชน์สำหรับท่านที่มีโจทย์ประมาณนี้นะครับ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.