AWS Athena(presto sql) - url 데이터 가져오기(url_decode, url_extract_host, url_extract_parameter, url_extract_path, url_extract_protocol, url_extract_query)

아테나에서 url 데이터를 가져오는 법에 대해서 배워보도록 합시다.

 

기본적인 url 구조는 아래처럼 되어 있습니다. 여기서 데이터를 하나씩 가져오도록 하겠습니다.

[protocol:][//host[:port]][path][?query][#fragment]

 

얘 시를 봅시다. 이건 네이버에서 디아블로를 검색한 url입니다.

https://search.naver.com/search.naver?where=nexearch&sm=top_hty&fbm=0&ie=utf8&query=%EB%94%94%EC%95%84%EB%B8%94%EB%A1%9C

url을 쪼개면 이렇게 볼 수 있다.
-- host : search.naver.com
-- path : /search.naver
-- query : ?where=nexearch&sm=top_hty&fbm=0&ie=utf8&query=%EB%94%94%EC%95%84%EB%B8%94%EB%A1%9C
-- protocol : https

여기서 query안에서 변수의 형태로 띄고있는것이 parameter인데
- ?where=nexearch&sm=top_hty&fbm=0&ie=utf8&query=%EB%94%94%EC%95%84%EB%B8%94%EB%A1%9C
- 위의 url에서 where, sm, ie, query 등이 parameter라고 할 수 있다.

 

각 함수별로 예시를 보겠습니다.

with dataset as (SELECT 'https://search.naver.com/search.naver?where=nexearch&sm=top_hty&fbm=0&ie=utf8&query=%EB%94%94%EC%95%84%EB%B8%94%EB%A1%9C' as url)

select -- utf-8로 url을 인코딩
       url_decode(url) as url,
       
       -- url의 frgment 반환
       url_extract_fragment(url) as fragment,
       
       -- url의 host 반환
       url_extract_host(url) as host,
       
       -- url의 query에서 sm 변수의 value를 반환
       url_extract_parameter(url,'sm') as sm,
       
       -- url의 path 반환
       url_extract_path(url) as path,
       
       -- url의 protocol 반환
       url_extract_protocol(url) as protocol,
       
       -- url의 query 반환
       url_extract_query(url) as query
from dataset

 

1. url_decode

query=%EB%94%94%EC%95%84%EB%B8%94%EB%A1%9C 이런 아스키 문자를 한글로 바꾼 url을 리턴합니다.

 

url_decode 함수

 

2. url_extract_fragment

fragment는 #뒤에 있는 값을 리턴합니다.

with dataset as (SELECT 'https://www.google.com/index.html#image' as url)

select url_extract_fragment(url) as fragment
from dataset

url_extract_fragment 함수

 

3. url_extract_protocol, url_extract_host, url_extract_path, url_extract_query

[protocol:][//host[:port]][path][?query][#fragment]

위에 패턴에서 각각의 값을 가져옵니다.

 

with dataset as (SELECT 'https://search.naver.com/search.naver?where=nexearch&sm=top_hty&fbm=0&ie=utf8&query=%EB%94%94%EC%95%84%EB%B8%94%EB%A1%9C' as url)

select url_extract_protocol(url) as protocol,
       url_extract_host(url) as host,
       url_extract_path(url) as path,
       url_extract_query(url) as query
from dataset

 

각각 위치의 값을 리턴함

 

4. url_extract_parameter

 

query안에 필요한 파라미터 값을 가져옵니다.

with dataset as (SELECT 'https://search.naver.com/search.naver?where=nexearch&sm=top_hty&fbm=0&ie=utf8&query=%EB%94%94%EC%95%84%EB%B8%94%EB%A1%9C' as url)

-- sm=top_hty 에서 top_hty를 빼와보자
select url_extract_parameter(url,'sm') as sm
from dataset

위 sql은 url에서 sm의 벨류값을 가져온 것입니다.

 

sm의 벨류값인 top_hty가 리턴되었다.