PostgreSQL - 도메인 추출 쿼리

로그를 저장한 테이블에서 도메인만 추출해야할 때가 있다.

access_log 테이블에 아래와 같은 데이터가 있다고 가정하자.

CREATE TABLE access_log (
    stamp    varchar(255)
  , referrer text
  , url      text
);

INSERT INTO access_log 
VALUES
    ('2017-08-26 12:02:00', 'http://www.korea.com/path1/index.php?k1=v1&k2=v2#Ref1', 'http://www.augustine.com/video/detail?id=001')
  , ('2017-08-26 12:02:01', 'http://www.germany.net/path1/index.php?k1=v1&k2=v2#Ref1', 'http://www.augustine.com/video#ref'          )
  , ('2017-08-26 12:02:01', 'https://www.china.com/'                               , 'http://www.augustine.com/book/detail?id=002' )
;

여기서 referrer 컬럼에서 http://www.korea.com, http://www.germany.net, https://www.china.com 와 같이 도메인만 추출해야 한다. 이럴 경우엔 substring함수와 정규 표현식을 사용해서 원하는 데이터 추출이 가능하다.

SELECT 
	stamp
	, substring(referrer from 'https?://([^/]*)') as refer_host
FROM access_log;

위 쿼리를 실행하면, 아래와 같은 결과가 나온다.

또한, 로그 데이터에서 도메인 이하의 URL 경로와 GET방식으로 전송되는 특정 키와 값을 추출이 필요할 때가 있다. 이러한 경우에는 split_part함수와 substring 함수와 정규 표현식을 활용해서 구현할 수 있다.

SELECT
	stamp
	, url
	, substring(url from '//[^/]+([^?#]+)') as path
	, substring(url from 'id=([^&]*)') as id
from access_log;	

위 쿼리를 실행하면 아래와 같은 결과가 나온다.

url 경로를 슬래시로 분할해서 추출하는 쿼리가 필요할 때가 있다. 예를 들어, http://www.augustine.com/video/detail?id=001 와 같은 url 데이터에서 path1은 video값을, path2는 detail 이라는 값을 추출하고 싶을 때, 역시 substring함수와 정규 표현식을 활용하면 된다.

SELECT
	stamp
	, url
	, split_part(substring(url from '//[^/]+([^?#]+)'), '/', 2) as path1
	, split_part(substring(url from '//[^/]+([^?#]+)'), '/', 3) as path2
FROM access_log;