JSONB 타입 다뤄보기 - 1 (24.05.22)

PostgreSQL의 경우 다른 RDB와 달리, 특별한 타입의 데이터를 저장할 수 있다.

Attribute로 bigint가 아니고 bigint[]의 배열 형태나, JSONB 등의 조금 더 자유로운 데이터 타입으로 저장할 수 있다는 점이다.

이것은 실제 DB에서 JDBC를 통해 넘어오는 데이터 중 일부이다.

[
  {
    "id": 592492460020000000,
    "scope_name": "io.opentelemetry.spring-scheduling-3.1",
    "scope_version": "2.1.0-alpha",
    "trace_id": "E'\\\\\\\\x32058E6FA0A913CCA9D567F003A0AD2A'",
    "span_id": "E'\\\\\\\\x82A02E8383274438'",
    "parent_span_id": "",
    "flag": 1,
    "name": "RealtimeTask.sendRealtime",
    "kind": 1,
    "start_time_unix_nano": 1715924920000000000,
    "end_time_unix_nano": 1715924920000870856,
    "status_is_error": false,
    "attributes": {"thread.id": "70", "thread.name": "pool-8-thread-1", "code.function": "sendRealtime", "code.namespace": "com.sysone.eumaiwacs.task.RealtimeTask"},
    "has_event": false
  }
]

(무언가 비범한 데이터 스키마. Oracle이나 MySQL에서는 보지 못했다.)

create table tbl_apm_trace_span
(
    id                   BIGINT PRIMARY KEY,
    scope_name           VARCHAR,
    scope_version        VARCHAR,
    trace_id             BYTEA,
    span_id              BYTEA,
    parent_span_id       BYTEA,
    flag                 INT,
    name                 VARCHAR,
    kind                 INT,
    start_time_unix_nano BIGINT,
    end_time_unix_nano   BIGINT,
    status_is_error       boolean, --Error check, Message / Code 배제
    attributes           jsonb,
    has_event            boolean
);
CREATE INDEX span_span_id ON tbl_apm_trace_span (span_id);
CREATE INDEX span_trace_id ON tbl_apm_trace_span (trace_id);
CREATE INDEX span_parent_span_id ON tbl_apm_trace_span (parent_span_id);
-- 복합 인덱스 들어갈 자리

그 외에 Double[] 타입 등도 지원한다.

CREATE TABLE tbl_apm_metric_histogram
(
    id bigint PRIMARY KEY,
    agent_id BIGINT,
    scope_name varchar,
    scope_version varchar,
    name varchar,
    unit varchar,
    aggregation_temporality_value int,
    attributes jsonb,
    start_time_unix_nano bigint,
    time_unix_nano bigint,
    count int,
    sum double precision,
    bucket_counts bigint[],
    explicit_bounds double precision[],
    flag int,
    min double precision,
    max double precision,
    has_exemplar boolean
);
CREATE INDEX tbl_apm_metric_histogram_agent_id ON tbl_apm_metric_histogram(agent_id);

사실 정규화를 강하게 집어넣는다면 이러한 타입의 데이터를 자주 쓸 일이 없을 것이다. 어쩌면 비정규화에 특화된 데이터 타입이 아닐까? 라는 생각이 든다.

무튼, 중요한 것은 이것이다.

 

“우리는 Spring 등에서 ‘JPA’를 사용해서 JDBC 쿼리 결과를 매핑해야 한다.”

 

그런데, JPA에서는 배열 타입(double[], long[]..)이나, Json을 지원하지 않는다.

@Entity
@Table(name = "tbl_apm_metric_gauge")
public class Gauge {
    @Id
    private long id;
    private long agentId;
    private String scopeName;
    private String scopeVersion;
    private String name;
    private String unit;
    }
 }

 

우리가 아는 일반적인 Entity이다. premitive type 정도만 지원하고, 실제로 이렇게 자주 쓰인다.

그렇기에, psql에서 제공하는 특별한 타입 데이터를 받으려면 이런 식으로 받아야 한다. 혹은 UserType을 다시 받던지..

 

package com.sysone.apm.entity.metric;

import com.sysone.apm.util.converter.JsonbToMapConverter;
import org.hibernate.annotations.Type;

import javax.persistence.*;
import java.util.List;
import java.util.Map;

@Entity
@Table(name = "tbl_apm_metric_histogram")
public class Histogram {
    @Id
    private long id;
    private long agentId;
    private String scopeName;
    private String scopeVersion;
    private String name;
    private String unit;
    private int aggregationTemporalityValue;
    @Convert(converter = JsonbToMapConverter.class)
    private Map<String, String> attributes;
    private long startTimeUnixNano;
    private long timeUnixNano;
    private int count;
    private double sum;

    @Type(type = "com.sysone.apm.util.converter.LongArrayType")
    private List<Long> bucketCounts;

    @Type(type = "com.sysone.apm.util.converter.DoubleArrayType")
    private List<Double> explicitBounds;

    private int flag;
    private double min;
    private double max;
    private boolean hasExemplar;
}

 

 

JSONB 쿼리 함수 사용해보기

select span_trace_id from tbl_apm_trace limit 1; -- 32058e6f-a0a9-13cc-a9d5-67f003a0ad2a
select * from tbl_apm_trace_span where trace_id = (select span_trace_id from tbl_apm_trace limit 1);

-------view test
drop view tbl_apm_test_view;
CREATE VIEW tbl_apm_test_view AS
    SELECT distinct jsonb_object_keys(attributes) AS attribute_keys
      FROM tbl_apm_trace_span;

select * from tbl_apm_test_view;

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'tbl_apm_metric_sum';

SELECT DISTINCT
    jsonb_object_keys(attributes) AS key
FROM tbl_apm_metric_sum;

-- 키 : value
select distinct tbl_apm_metric_sum.attributes from tbl_apm_metric_sum limit 500;

-- 키 : value
select distinct attributes from tbl_apm_trace_span limit 500;

-- 키 : value
select distinct attributes from tbl_apm_metric_histogram limit 500;

select distinct jsonb_object_keys(attributes) as key from tbl_apm_metric_sum;

SELECT
    jsonb_each_text(attributes) AS key_value_pair
FROM tbl_apm_metric_sum;

###
-- 고유한 키 세트를 추출하는 쿼리 : 키셋팅만
SELECT DISTINCT
    (SELECT array_agg(key ORDER BY key)
     FROM jsonb_each(attributes) as t(key, value)) AS key_set
FROM
    tbl_apm_metric_sum;

-- 키 : value
select distinct tbl_apm_metric_sum.attributes from tbl_apm_metric_sum limit 500;

-- histogram
-- 고유한 키 세트를 추출하는 쿼리
SELECT DISTINCT
    (SELECT array_agg(key ORDER BY key) --변환된 키-값 쌍에서 키를 추출하여 배열로 집계
     FROM jsonb_each(attributes) -- jsonb_each(attributes): JSONB 객체를 키-값 쌍의 행으로 변환
         as t(key, value)) AS key_set
FROM
    tbl_apm_metric_histogram limit 10;

select count(*) from tbl_apm_metric_histogram;
select distinct attributes from tbl_apm_metric_histogram limit 500;

-- 이건 단일 키임
SELECT jsonb_each(attributes) from tbl_apm_metric_histogram limit 10;

--개선
SELECT
    id,  -- 각 행을 식별할 수 있는 기본 키 또는 고유 식별자 (필요에 따라 수정)
    array_agg(key) AS keys
FROM (
         SELECT
             id,  -- 각 행을 식별할 수 있는 기본 키 또는 고유 식별자 (필요에 따라 수정)
             jsonb_object_keys(attributes) AS key
         FROM tbl_apm_metric_histogram
     ) AS keys_extracted
GROUP BY id;
-- LIMIT 10;

--2차 개선
SELECT
    distinct array_agg(key) AS keys
FROM (
         SELECT
             jsonb_object_keys(attributes) AS key,
             row_number() OVER () AS rn
         FROM tbl_apm_metric_histogram
     ) AS keys_extracted
GROUP BY rn
-- LIMIT 10;

최종

SELECT
    distinct name, array_agg(key) AS keys
FROM (
         SELECT
             name,
             jsonb_object_keys(attributes) AS key,
             row_number() OVER (PARTITION BY name ORDER BY name) AS rn
         FROM tbl_apm_metric_sum
     ) AS keys_extracted
GROUP BY name, rn
LIMIT 10;

 

최종적으로는 Jsonb String으로 변경했다.

--개선
SELECT
    id,  -- 각 행을 식별할 수 있는 기본 키 또는 고유 식별자 (필요에 따라 수정)
    array_agg(key) AS keys
FROM (
         SELECT
             id,  -- 각 행을 식별할 수 있는 기본 키 또는 고유 식별자 (필요에 따라 수정)
             jsonb_object_keys(attributes) AS key
         FROM tbl_apm_metric_histogram
     ) AS keys_extracted
GROUP BY id;

--2차 개선
select * from tbl_apm_metric_sum;
SELECT
    distinct name, array_agg(key) AS keys
FROM (
         SELECT
             name,
             jsonb_object_keys(attributes) AS key,
             row_number() OVER (PARTITION BY name ORDER BY name) AS rn
         FROM tbl_apm_metric_sum
     ) AS keys_extracted
GROUP BY name, rn
LIMIT 10;

--3차 개선 : String으로 변환 .. 14sec
select * from tbl_apm_metric_sum;
SELECT
    distinct name, array_to_string(array_agg(key), ',') AS keys
FROM (
         SELECT
             name,
             jsonb_object_keys(attributes) AS key,
             row_number() OVER (PARTITION BY name ORDER BY name) AS rn
         FROM tbl_apm_metric_histogram
     ) AS keys_extracted
GROUP BY name, rn
order by name;

--4차 개선 : 최적화 .. 7sec
WITH key_extraction AS (
    SELECT distinct
        name,
        jsonb_object_keys(attributes) AS key
    FROM tbl_apm_metric_histogram
)
SELECT
    name,
    array_to_string(array_agg(key), ',') AS keys
FROM key_extraction
GROUP BY name
order by name;

select * from tbl_apm_metric_histogram order by time_unix_nano desc limit 1;

--5차 개선 : where 조건 추가 .. agentid 추가시 4초, 시간 지정시 1초 이내 들어옴
explain analyse
WITH key_extraction AS (
    SELECT distinct
        name,
        jsonb_object_keys(attributes) AS key
    FROM tbl_apm_metric_histogram
    WHERE agent_id = 1 and time_unix_nano between 1716388593093536000 and 1716474993093536000
)
SELECT
    name,
    array_to_string(array_agg(key), ',') AS keys
FROM key_extraction
GROUP BY name
order by name;