26Z10c7

Hybrid View 작성 시에도 slice() 함수가 남아 있어 동일한 시그니처 에러가 발생한 것입니다.

이전에 적용했던 substring_index() 방식을 Hybrid View 내의 CTE(today_changes)에도 동일하게 적용하면 배열 함수 타입 에러를 깔끔하게 해결할 수 있습니다.

---

수정된 Hybrid View 생성 DDL (v_realtime_prefix_summary)

prefix_summary 물리 테이블에 배치 수행 시각을 기록하는 last_updated 컬럼이 포함된 기준으로 작성된 쿼리입니다.

CREATE VIEW v_realtime_prefix_summary AS
WITH numbers AS (
    SELECT 1 AS pos UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL 
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
),
today_changes AS (
    SELECT 
        o.bucket,
        substring_index(o.prefix, '/', n.pos) AS prefix,
        -- 삭제건은 차감(-), 신규/갱신건은 가산(+)
        SUM(IF(o.__deleted = 1, -1, 1)) AS delta_count,
        SUM(IF(o.__deleted = 1, -o.size_bytes, o.size_bytes)) AS delta_size
    FROM object_current_state o
    CROSS JOIN numbers n
    WHERE o.event_time > (SELECT COALESCE(MAX(last_updated), '1970-01-01 00:00:00') FROM prefix_summary)
      AND o.prefix IS NOT NULL 
      AND o.prefix != ''
      AND n.pos <= (length(o.prefix) - length(replace(o.prefix, '/', '')) + 1)
    GROUP BY o.bucket, substring_index(o.prefix, '/', n.pos)
)
SELECT 
    COALESCE(base.bucket, chg.bucket) AS bucket,
    COALESCE(base.prefix, chg.prefix) AS prefix,
    COALESCE(base.total_object_count, 0) + COALESCE(chg.delta_count, 0) AS total_object_count,
    COALESCE(base.total_size_bytes, 0) + COALESCE(chg.delta_size, 0) AS total_size_bytes
FROM prefix_summary base
FULL OUTER JOIN today_changes chg 
  ON base.bucket = chg.bucket AND base.prefix = chg.prefix;

---

참고: prefix_summary 테이블 DDL 및 Python 갱신 쿼리

위 Hybrid View가 정상 동작하려면 prefix_summary 테이블에 last_updated 컬럼이 존재해야 합니다.

1. 물리 테이블 DDL

CREATE TABLE prefix_summary (
    bucket             VARCHAR(64) NOT NULL,
    prefix             VARCHAR(1024) NOT NULL,
    total_object_count BIGINT SUM DEFAULT "0",
    total_size_bytes   BIGINT SUM DEFAULT "0",
    last_updated       DATETIME MAX -- 가장 최근 배치 snapshot_time 저장
) ENGINE=OLAP
AGGREGATE KEY(bucket, prefix)
DISTRIBUTED BY HASH(bucket, prefix);

2. Python 스크립트 내 INSERT OVERWRITE 쿼리

refresh_sql = """
    INSERT OVERWRITE prefix_summary
    WITH numbers AS (
        SELECT 1 AS pos UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL 
        SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    )
    SELECT 
        o.bucket,
        substring_index(o.prefix, '/', n.pos) AS prefix,
        COUNT(1) AS total_object_count,
        SUM(o.size_bytes) AS total_size_bytes,
        %s AS last_updated
    FROM object_current_state o
    CROSS JOIN numbers n
    WHERE o.__deleted = 0 
      AND o.prefix IS NOT NULL AND o.prefix != ''
      AND n.pos <= (length(o.prefix) - length(replace(o.prefix, '/', '')) + 1)
    GROUP BY o.bucket, substring_index(o.prefix, '/', n.pos);
"""
cursor.execute(refresh_sql, (args.snapshot_time,))