[데이터 전처리2] URL에서 요소 추출하기2 : 경로와 매개변수값 추출하기

[데이터 전처리2] URL에서 요소 추출하기2

 

---

경로와 매개변수값 추출하기

 

SELECT * 
 FROM access_log;

 

Mysql

SELECT 
stamp,
url,
SUBSTRING_INDEX(REGEXP_SUBSTR(url, '([^?#]+)'), SUBSTRING_INDEX(url, '/', 3), -1) AS path,
SUBSTRING_INDEX(REGEXP_SUBSTR(url, 'id=([^&]*)'), 'id=', -1) AS id
FROM access_log
;

---

 

Postgre

SELECT 
stamp,
url,
SUBSTRING(url FROM '//[^/]+([^?#]+)') AS path,
SUBSTRING(url FROM 'id=([^&]*)') AS id
FROM access_log
;

http://www.example.com/video/detail?id=001 

http://www.example.com/video#ref 

http://www.example.com /boook/detail?id=002 

 

//[^/] + ([^?#]+)

id = ([^&]*) 

id 뒤에 어떤 것이라도 가져오겠다.

---

 

Pyspark

① pyspark로 SQL 데이터 연동해오기

 

② access_log 테이블 확인하기

spark.sql("""
SELECT *
FROM access_log
""").show()

 

spark.sql("""
SELECT
	stamp
    , url
    , PARSE_URL(url, 'PATH') AS path
    , PARSE_URL(url, 'QUERY', 'id') AS id  
FROM access_log
""").show()

 

PARSE_URL(url, query, 매개변수) 

 쿼리 매개변수를 쪼개서 가져온다라는 의미로 query라는 키를 사용해준다.

---

SPARK함수 : PARSE_URL(url, partToExtract [, key])

문자열을 URL로 해석하고 URL의 구성요소에 맞게 연관배열을 생성해준다.

 

인수

• url: STRING 식
• partToExtract: STRING 식
• key: STRING 식

 

반환

STRING입니다. 

partToExtract는 다음 중 하나여야 한다.

• 'PATH'
• 'QUERY'
• 'REF'
• 'PROTOCOL'
• 'FILE'
• 'AUTHORITY'
• 'USERINFO'

key는 대/소문자를 구분합니다.

요청된 partToExtract 또는 key를 찾을 수 없는 경우에는 NULL이 반환됩니다.

url 문자열이 잘못된 경우 Databricks SQL에서 오류가 반환됩니다.

 

https://docs.microsoft.com/ko-kr/azure/databricks/sql/language-manual/functions/parse_url

parse_url 함수(Databricks SQL) - Azure Databricks - Databricks SQL

---

 

Pandas

① Python으로  MySQL 연동하기

데이터전처리1 설명 참조

sql = "SELECT * FROM access_log"
df_access_log = psql.read_sql(sql, con)
con.close()
df_access_log

 

URL 요소 추출하기

from urllib.parse import urlparse

df_access_log['path'] = df_access_log['url'].apply(lambda url: urlparse(url).path)
df_access_log['id'] = df_access_log['url'].apply(lambda url: urlparse(url).query).str.replace('id=', '')
df_access_log[['stamp', 'url', 'path', 'id']]

# urlparse 함수를 통해 .query  매개변수만 추출하고 id=는 없애주고 id값만 추출해준다.

 

---

<참고>

urllib 패키지 urlparse 모듈

https://docs.python.org/ko/3/library/urllib.parse.html

urllib.parse — URL을 구성 요소로 구문 분석 — Python 3.10.5 문서