비디오 URL 정규화 파이프라인 구축하기
운영 중인 SQLite 데이터베이스에 쿼리를 실행했다가 엄청난 문제를 발견했습니다.
비디오 테이블에는 41,283개의 행이 있었지만, 고유한 제목은 29,000개뿐이었습니다. 동일한 비디오를 여러 번 저장하고 있었던 것입니다.
왜 이런 일이 발생했을까요? 동일한 YouTube 비디오가 서로 다른 URL 형식으로 들어왔기 때문입니다:
• Short links: youtu.be/ID • Desktop links: youtube.com/watch?v=ID • Mobile links: m.youtube.com/watch?v=ID • Shorts: youtube.com/shorts/ID
비디오 하나가 데이터베이스 행 4개와 거의 동일한 웹 페이지 4개를 의미했습니다. 이는 크롤링 예산을 낭비하고 구글 서치 콘솔(Google Search Console)에 오류를 발생시켰습니다.
저는 TrendVidStream을 운영하고 있습니다. 8개의 서로 다른 지역에서 트렌드 데이터를 가져옵니다. 각 지역에서 동일한 바이럴 비디오가 서로 다른 URL 형태로 나타날 수 있습니다.
저는 PHP 8.4와 SQLite를 사용하여 이를 해결하기 위한 파이프라인을 구축했습니다.
파이프라인의 작동 방식은 다음과 같습니다:
- 추출(Extract): 모든 URL 변형을 안정적인 11자리 비디오 ID로 변환합니다.
- 정규화(Normalize): 해당 ID로부터 단 하나의 캐노니컬(canonical) URL을 생성합니다.
- 검증(Validate): 데이터베이스를 수정하기 전에 빠른 확인 과정을 거칩니다.
- 업서트(Upsert): 새로운 행을 추가하는 대신 SQLite UPSERT를 사용하여 데이터를 병합합니다.
- 방출(Emit): 검색 엔진이 하나의 URL을 찾을 수 있도록 캐노니컬 태그와 301 리다이렉트를 추가합니다.
주요 기술적 결정 사항:
• 정확한 호스트 허용 목록(allowlist)을 사용하세요. 접미사 매칭(suffix matching)을 사용하지 마세요. 이는 보안 위험을 방지합니다. • ID를 절대 소문자로 변환하지 마세요. YouTube ID는 대소문자를 구분합니다. 소문자로 변환하면 서로 다른 비디오가 하나로 합쳐질 수 있습니다. • video_id에 UNIQUE 제약 조건을 사용하세요. 이는 중복을 방지하는 가장 강력한 방어 수단입니다. • SQLite WAL 모드를 사용하세요. 이를 통해 쓰기 작업이 빠르고 안정적으로 이루어집니다.
결과:
비디오 테이블의 행이 41,283개에서 28,094개로 줄었습니다. 비디오 손실은 전혀 없었습니다. 구글 서치 콘솔 오류는 412개에서 9개로 감소했습니다. 동일한 비디오를 네 번씩 보여주는 일이 중단되면서 검색 결과가 더 깔끔해졌습니다.
교훈은 간단합니다. 여러 소스에서 데이터를 수집할 때는 시스템에 먼저 '정체성(identity)'을 구축해야 합니다. 안정적인 ID를 추출하고, 데이터베이스 제약 조건으로 이를 강제하며, 모든 쓰기 작업을 멱등(idempotent)하게 만드세요.
복잡한 도구는 필요 없습니다. PHP와 SQLite만으로도 충분합니다.