[BigQuery SQL] 특정 URL 데이터를 필터링하는 방법: regexp_contains

애널리틱스 데이터를 관리하거나 분석할 때, 테스트 URL이나 특정 패턴에 해당하는 데이터를 제외해야 하는 상황이 자주 발생한다. 이를 효율적으로 처리하기 위해 SQL의 regexp_contains 함수와 LEFT JOIN을 사용하는 방법을 알아본다. 특히, BigQuery 환경을 기준으로 실무적인 예제를 소개한다.

1. 실무에서의 필요성

예를 들어, 특정 웹사이트의 트래픽 데이터를 분석하고 있다고 가정하자. 하지만 다음과 같은 테스트 URL들이 데이터에 포함되어 있다:

• localhost 환경에서 발생한 트래픽 (https://localhost/admin)
• 스테이징 환경에서 발생한 트래픽 (https://staging.example.com)

이러한 테스트 데이터는 실제 사용자 행동을 분석하는 데 방해가 될 수 있으므로, 이를 제외해야 한다. BigQuery에서 이를 처리하는 방법을 살펴보자.

2. regexp_contains 함수란?

regexp_contains 함수는 특정 문자열이 정규 표현식(Regular Expression)과 일치하는지 확인할 때 사용된다. 이 함수는 다음과 같은 형식으로 사용한다:

regexp_contains(input_string, regex_pattern)

• input_string: 일치 여부를 확인할 문자열(예: 테이블 컬럼 값).
• regex_pattern: 정규 표현식으로, 일치 여부를 결정하는 패턴.

regexp_contains는 TRUE 또는 FALSE 값을 반환한다. 예를 들어, 다음과 같이 테스트할 수 있다:

SELECT regexp_contains("https://localhost/admin", ".*localhost.*") AS is_match;

결과는 TRUE이다. 왜냐하면 문자열에 localhost가 포함되어 있기 때문이다.

3. 특정 URL 패턴을 테이블로 관리하기

제거하고 싶은 URL 패턴을 CSV 파일이나 별도의 테이블로 관리하면, 유지보수가 용이하다. 예를 들어, 다음과 같은 패턴을 CSV 파일(traffic_patterns_to_exclude.csv)로 정의할 수 있다:

이 CSV 파일은 BigQuery에 업로드하여 테이블(traffic_patterns_to_exclude)로 사용할 수 있다.

4. LEFT JOIN을 사용한 특정 URL 제외

LEFT JOIN과 regexp_contains를 활용하면 특정 URL 패턴을 쉽게 제거할 수 있다. 아래는 Google Analytics 데이터를 필터링하는 예제이다:

예제 쿼리

WITH test_traffic_removed AS (
    SELECT t.*
    FROM `project.dataset.ga_table` AS t
    LEFT JOIN `project.dataset.traffic_patterns_to_exclude` AS patterns
        ON regexp_contains(t.page_path, patterns.regex)
    WHERE patterns.regex IS NULL
)
SELECT *
FROM test_traffic_removed;

동작 원리

1. ga_table의 모든 데이터를 가져온다.
2. traffic_patterns_to_exclude 테이블과 page_path를 기준으로 regexp_contains를 사용해 조인한다.
3. patterns.regex IS NULL 조건을 사용하여 정규 표현식과 일치하지 않는 데이터만 필터링한다.

5. 상세 예시: 필터링 전후 비교

(1) 필터링 전 테이블 (ga_table)

(2) 필터링 후 테이블 (test_traffic_removed)

위 결과는 page_path가 localhost나 staging 관련 정규 표현식과 일치하는 행을 제거한 것을 보여준다.

6. 요약 및 장점

• 유연성: 정규 표현식을 사용하여 다양한 URL 패턴을 탐지할 수 있다.
• 확장성: 제거할 패턴을 테이블로 관리하면 유지보수가 쉽다.
• 효율성: LEFT JOIN과 regexp_contains를 활용하면 대규모 데이터에서도 빠르게 필터링 가능하다.

SQL 초보자라도 위 방법을 따라 하면 실무에서 테스트 데이터를 효과적으로 제거할 수 있다. 정규 표현식 사용법을 익히고 데이터를 정리하여, 정확하고 신뢰할 수 있는 분석 결과를 얻는 데 도움이 되길 바란다.