[데이터 전처리2] URL에서 요소 추출하기1 : 호스트 단위로 추출하기

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

 

---

기록된 원래 URL에서 호스트 단위로 추출하기

 

 

SELECT * FROM access_log;

access_log 테이블

 

---

MySQL

SUBSTRING_INDEX(컬럼명. 기준(구분)문자, 보여질 위치)

✔ 위치 기준 음수(-)는 뒷부분 부터, 양수(+)는 앞부분 부터

 

 

① SUBSTRING_INXDEX(referrer, '/', 3)  로 추출하면  '/' 기준으로 세번째 구분까지 출력된다.

SELECT
stamp,
SUBSTRING_INDEX(referrer, '/', 3) AS referrer_host
FROM access_log;

 

 

위 결과에 SUBSTRING_INDEX(①, '/' , -1) 을 한번 더 적용하면 데이터 뒷부분부터 첫번째 자리까지가 출력된다.

SELECT
stamp,
SUBSTRING_INDEX(SUBSTRING_INDEX(referrer, '/', 3), '/', -1) AS referrer_host
FROM access_log;

 

---

 

Postgre

SUBSTRING(string FROM 추출시작위치) SUBSTRING(string FOR 추출글자개수)

✔ 컬럼 내 데이터 길이가 다양할 수 있으므로 추출시작위치를 기준으로 SUBSTRING하는 문법이 더 유용할 것 같다.

 

SELECT
stamp,
SUBSTRING(referrer FROM 'https?://([^/]*)') AS referrer_host
FROM access_log;

https?

http에 s가 있는 경우 없는 경우를 모두 포함하겠다

 

([^/]*) 

다음 /까지 뭐가 들어있든지 다 가져오겠다.

---

 

Pyspark

import findspark
findspark.init() 

from pyspark.sql import SparkSession

spark = SparkSession\
    .builder\
    .appName("spark-sql")\
    .config("spark.driver.extraClassPath", "./Connector J 8.0/mysql-connector-java-8.0.27.jar")\
    .getOrCreate()

df_spark = spark.read\
    .format("jdbc")\
    .option("url", "jdbc:mysql://localhost/webmarket")\
    .option("driver", "com.mysql.jdbc.Driver")\
    .option("dbtable", "access_log").option("user", "root")\
    .option("password", "1234").load()

print(df_spark.columns)
# ['stamp', 'referrer', 'url']

df_spark.show()

df_spark.registerTempTable("access_log")
df_spark
# DataFrame[stamp: string, referrer: string, url: string]

 

URL 요소 추출하기

spark.sql("""
SELECT
	stamp
    , PARSE_URL(referrer, 'HOST') AS referrer_host
FROM access_log
""").show()

 

 

---

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

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

 

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

보통 host, path, query, ref 요소를 많이 추출한다.

 

 

<참고>

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 요소 추출하기

 

(방법1)

df_access_log['referrer_host'] = df_access_log['referrer'].str.split('/').str[2]
df_access_log[['stamp', 'referrer_host']]

 df_access_log['referrer'].str.split('/').str[2]

referrer 컬럼을 문자형 변환해서 '/' 기준으로 쪼개고 str[2]를 추출한다.

파이썬은 0부터 시작

 

 

(방법2)

from urllib.parse import urlparse
df_access_log['referrer_host'] = df_access_log['referrer'].apply(lambda x: urlparse(x).netloc)
df_access_log[['stamp', 'referrer_host']]

 

---

PANDAS함수 : netloc

 

모듈설명 ▼

urlparse

URL 문자열을 구성 요소(주소 지정 체계, 네트워크 위치, 경로 등)로 분리/결합하고,
“상대 URL”을 주어진 “기본 URL”에 따라 절대 URL로 변환하는 표준 인터페이스를 정의