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