데이터 값이 다른 값으로 바뀐 경우 (빅쿼리, UPDATE)

유튜브 데이터를 매일 수집하는 중..
데이터 하루에 딱 한번, 18시에 자동적으로 수집되고 버킷에 저장된 데이터를 대시보드에 나타내는 형식입니다. 그런데 만약 어제 받은 데이터의 영상제목이 수정된 경우, 최신 제목으로 바꿔줘야하는 수고가 있었습니다.

현재 goolge cloud storage 버킷에 csv 파일들이 차곡히 쌓여지고 있으며, 전처리를 python 에서 하고 있었는데요. 가능한 부분들을 빅쿼리안에서 전처리를 한번 거치고 storage에 저장되도록 흐름을 바꿔보도록 하려합니다. (일단 빅쿼리 부분만..!)

자, 바로 문제의 데이터입니다. 아래의 테이블에서 볼 수 있듯이 9월8일 이후로 영상제목이 조금 변경되었습니다.

'Rclny6yachY' 영상은 ALL:MUSIC , ISEGYE IDOL:MUSIC 재생목록에도 들어가 있는 영상입니다. 위 데이터를 가장 최근의 영상제목으로 모두 바꿔주어야합니다! 어차피 같은 video_id 이기 때문에 playlist_title은 신경쓰지 않고 down_at, title, video_id 컬럼에 집중하면 될듯 합니다.

SELECT playlist_title 
    , video_id
    , title
    , down_at
FROM my-project-72981.wak_data.test
WHERE video_id = 'Rclny6yachY' 
ORDER BY playlist_title

UPDATE

요약

가장 최신에 받은 video_id 와 title table을 만들어서 UPDATE 문을 이용해 가장 최근 title로 변경해줍니다.
t : 기존테이블
lt : 가장 최근에 받은 데이터 (down_at 기준)

전체 쿼리

UPDATE my-project-72981.wak_data.waktaverse  AS t
SET t.title = lt.title
FROM
    (SELECT distinct(video_id)
          , title
          , down_at
      FROM  my-project-72981.wak_data.waktaverse
      WHERE down_at = (SELECT CURRENT_DATE()) # 가장 최근에 데이터를 받은날짜
      GROUP BY video_id, title, down_at
      ORDER BY down_at DESC) AS lt
    
WHERE t.video_id = lt.video_id 
AND t.title <> lt.title

lt table

하나씩 풀어보겠습니다.
먼저, 저희는 가장 최신의 데이터가 필요합니다.
down_at 은 %Y-%m-%d DATE 형식이기 때문에 SELECT CURRENT_DATE() 를 이용하여 현재 날짜 에 해당하는 데이터를 조건으로 가져올 수 있습니다.

    (SELECT video_id
          , title
          , down_at
      FROM  my-project-72981.wak_data.waktaverse
      WHERE down_at = (SELECT CURRENT_DATE()) # 가장 최근에 데이터를 받은날짜
      GROUP BY video_id, title, down_at
      ORDER BY down_at DESC) AS lt

python 에서 datetime.now() 기능과 비슷하다고 보시면 됩니다.😀

now = datetime.datetime.now()
now_time = now.strftime('%Y-%m-%d') 
today = pd.to_datetime(now_time, format='%Y-%m-%d')

group by

이제 (작성 기준) 2023-09-11(가장 최신 날짜) 에 해당하는 데이터만 남고 video_id, title, down_at 별로 나타내기위해 groupby 하면 아래의 결과처럼 가장 최근의 title 정보를 나타내는 table이 만들어 집니다!

이렇게 만들어진 테이블이 lt 입니다.

이제 기존 테이블인 t 와 video_id는 같은데 title이 다른값들만 선택하여

WHERE
  t.video_id = lt.video_id
  AND t.title <> lt.title

기존의 타이틀을 가장 최신의 타이틀인 lt.title로 바꿔줍니다.

• UPDATE 'table'
• SET '기존값'= '적용할값'

UPDATE my-project-72981.wak_data.waktaverse  AS t
SET t.title= lt.title

완성된 쿼리입니다!

UPDATE my-project-72981.wak_data.waktaverse  AS t
SET t.title = lt.title
FROM
    (SELECT video_id
          , title
          , down_at
      FROM  my-project-72981.wak_data.waktaverse
      WHERE down_at = (SELECT CURRENT_DATE()) # 가장 최근에 데이터를 받은날짜
      GROUP BY video_id, title, down_at
      ORDER BY down_at DESC) AS lt      
WHERE t.video_id = lt.video_id 
AND t.title <> lt.title

쿼리결과📝

영상의 제목이 모두 가장 최신 (2023-09-11) 날짜에 맞게 바뀐것을 볼 수 있습니다!