Data, What?/SQL

[BigQuery] 하나의 String을 여러 row로 분리

GrilleDream 2022. 4. 19. 19:27

테이블 생성 단계부터 참여하는 게 아니라면 기존의 것을 뜯어고치는 건 쉬운 일이 아니다

특히나 모든 권한을 다 보유하지 않았다면? 어떻게든 비틀어야지 방법이 없다...

 

아래는 그 흔적이다

Admin 페이지
DB 테이블

트래픽이 오가는 플랫폼의 경우 파트너와 파트너 사이를 연결하고 끊는 과정이 일어나기 마련이다.

자연히 언제 연결했고, 중간중간 어떤 History가 있었는지를 알고 싶고, 알아야만 한다.

그런데, 이미 구성해놓기를 데이터에 변화가 있으면 그냥 덮어쓰고 그 일자만 mod date(수정일자)로 저장하게 만들었다면?

아예 갈아엎고 새로 구성한다?

 

"좋아, 말한 사람이 책임 지고 만들면 되겠다!!"

택도 없는 소리다

DB와 웹페이지 등 해야 할 일거리는 많은데 비해, 얻을 수 있는 효과는 크지 않으므로 우선순위에 밀릴 게 뻔하다

(경험담은 아니다, 아무튼 아님)

 

이게 바로 위 memo 컬럼의 등장 배경이다.

임시방편으로 어드민 페이지에 항목을 추가하여 테이블과 연동, 동일한 형태로 저장하도록 만들어졌다.

cf. type이 varchar(255)로 되어있어 얼마안가 Long으로 변경했다

 

하지만 여전히 저장만 될 뿐이지, 이걸 보기 좋게 정리하려면 결국 덩어리로 된 String을 각각의 Row로 쪼개줘야 한다

SELECT
crt_ymd,
mod_ymd,
CAST(regexp_extract(B, r'^([0-9]{4}\-[0-9]{2}\-[0-9]{2})') AS DATE) ymd,
B memo
FROM(SELECT
    date(crt_date) crt_ymd,
    date(mod_date) mod_ymd,
    ssp_id,
    dsp_id,
    split(memo, '\n') memo
    FROM EXTERNAL_QUERY("projects/프로젝트id/locations/국가/connections/컨넥터이름", 
    '''가져올 쿼리''')
    WHERE memo IS NOT NULL OR memo <> ""
) A, UNNEST(A.memo) B
WHERE mod_ymd >= DATE_SUB(CURRENT_DATE("Asia/Seoul"), INTERVAL 7 DAY)
GROUP BY crt_ymd, mod_ymd, ssp_id, dsp_id, ymd, memo
HAVING crt_ymd <> mod_ymd
AND ymd >= DATE_SUB(CURRENT_DATE("Asia/Seoul"), INTERVAL 7 DAY)

 

위 쿼리의 핵심은 split(), UNNEST(), 그리고 regexp_extract() 이다.

 

일단 해당 테이블은 빅쿼리에 따로 저장돼있지 않아 미리 연결된 커넥터를 활용, external_query로 불러와주었다

왜 MySQL DB에서 바로 작업하지 않고 굳이 빅쿼리로 가져왔냐고 물으면 음... 이게 더 편하니까?

버전 신경쓸 필요 없어, 내장 함수 많지, 속도도 빠른데 이걸 안 쓰면 바보겠지. 빅쿼리 최고닷!!

 

아무튼 split 함수는 다른 프로그래밍 언어에서도 쓰이는 걸로 아는데, 문자열을 seperator를 이용해 쪼개 주는 역할을 한다.

memo의 경우 엔터를 기준으로 날짜별로 나눠져 있었으므로

split(memo, '\n')

위와 같이 작성해주었다.

 

이렇게 되면 빅쿼리에서는 디폴트로 array 형태의 변환이 이뤄지는데, 우리는 날짜별로 row가 나눠지길 바라므로 UNNEST로 풀어주었다.

 

마지막으로 update 될 때마다 truncate되는 mod_ymd 대신에 memo내의 날짜 정보를 새로운 ymd 컬럼으로 가져와주면 일자별로 파트너 간에 히스토리를 볼 수 있다