Day: March 29, 2022

  • 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 หรือวิธีการอื่นๆ ที่ใช้ในการประมวลผลเฉพาะรายการที่เพิ่มใหม่ หรือประมวลผลตามรอบเวลา (ไม่เรียลไทม์) โดยใช้คำสั่งดังกล่าวช่วยก็ได้เช่นกัน หวังว่าจะเป็นประโยชน์สำหรับท่านที่มีโจทย์ประมาณนี้นะครับ