[Data]Web log에서 유저별 페이지 방문 통계

문제

웹 로그 데이터를 분석하여 각 페이지마다 유저들이 방문했는지 통계를 도출하고,

유저의 나이와 성별을 기반으로 상세 통계도 구하시오.

 

주어진 웹 로그 데이터와 두 개의 테이블을 이용하여

방문 페이지별로 유저 방문 수를 계산하고,

유저의 나이대를 그룹화하여 각 페이지에서의 성별 통계를 포함한 결과를 출력해야 합니다.

 

 

요구사항

- 각 페이지별로 유저 방문 수를 계산하시오.

- 유저 정보를 기반으로 나이와 성별에 따라 그룹화하여, 유저 방문 수 통계를 구하시오.

- 10대에서 20살인 경우는 10~20으로, 30대는 30~40으로, 50세 이상은 50~으로 표시합니다.

- 유저 정보가 없는 경우, 유저 나이와 성별은 "-"로 표시합니다.

- 로그는 hdfs에 저장되어 있고, 테이블은 mysql이며, Hive Table에 저장합니다.

 

예외 처리

- 웹 로그 데이터에는 유저 정보가 없는 항목이 있을 수 있습니다. (단, 유저ID는 반드시 존재)

- 테이블 A에는 이전 페이지 정보가 없을 수 있으니, 해당 항목이 없을 경우에는 Null 또는 None으로 처리합니다.

 

 

입력데이터

 

 

출력데이터 예시

 

 

풀이 코드

필요 라이브러리

pip install pandas pyhive sqlalchemy mysql-connector-python hdfs

 

python code

import pandas as pd
from sqlalchemy import create_engine
from pyhive import hive
from hdfs import InsecureClient

# MySQL 연결 설정 함수
def get_mysql_connection():
    engine = create_engine('mysql+mysqlconnector://<username>:<password>@<host>/<database>', echo=False)
    return engine

# Hive 연결 설정 함수
def get_hive_connection():
    conn = hive.Connection(host='<hive_host>', port=10000, username='<hive_username>', database='<hive_database>')
    return conn

# HDFS에서 웹 로그 데이터 읽기 함수
def read_web_log_from_hdfs(hdfs_path: str) -> pd.DataFrame:
    client = InsecureClient('http://<hdfs_host>:<port>', user='<hdfs_user>')
    with client.read(hdfs_path, encoding='utf-8') as reader:
        web_log = pd.read_csv(reader)
    return web_log
    
# 페이지 방문 통계 분석 함수
def analyze_user_visits(web_log: pd.DataFrame, table_a: pd.DataFrame, table_b: pd.DataFrame) -> pd.DataFrame:
    # 1. 웹 로그와 페이지 메타 정보를 조인 (페이지URL을 기준으로)
    merged_df = pd.merge(web_log, table_a, left_on='방문한페이지URL', right_on='페이지URL', how='left')

    # 2. 유저 정보와 병합 (유저ID를 기준으로)
    merged_df = pd.merge(merged_df, table_b, on='유저ID', how='left')

    # 3. 나이대 그룹화 (10년 단위로 그룹화, 나이가 없는 경우 '-')
    def age_grouping(age):
        if pd.isnull(age):
            return "-"
        age = int(age)
        if age < 10:
            return "0~10"
        elif age < 20:
            return "10~20"
        elif age < 30:
            return "20~30"
        elif age < 40:
            return "30~40"
        elif age < 50:
            return "40~50"
        else:
            return "50~"

    # 나이대 추가
    merged_df['유저나이그룹'] = merged_df['유저나이'].apply(age_grouping)

    # 4. 각 페이지별로 유저 방문 수 통계를 계산 (유저 성별과 나이대별 그룹화)
    grouped = merged_df.groupby(['페이지명', '페이지URL', '유저나이그룹', '유저성별']).size().reset_index(name='유저방문수')

    # 5. 유저 정보가 없는 경우 성별과 나이대를 "-"로 처리
    grouped['유저나이그룹'] = grouped['유저나이그룹'].fillna('-')
    grouped['유저성별'] = grouped['유저성별'].fillna('-')

    return grouped

# Hive 테이블에 데이터 저장 함수
def save_to_hive(data: pd.DataFrame, hive_table: str):
    conn = get_hive_connection()
    cursor = conn.cursor()

    # DataFrame의 데이터를 Hive 테이블에 삽입 (혹은 덮어쓰기)
    cursor.execute(f"DROP TABLE IF EXISTS {hive_table}")
    cursor.execute(f"CREATE TABLE {hive_table} (페이지명 STRING, 페이지URL STRING, 유저방문수 INT, 유저나이그룹 STRING, 유저성별 STRING)")

    # Hive에 데이터를 INSERT
    for index, row in data.iterrows():
        insert_query = f"INSERT INTO {hive_table} VALUES ('{row['페이지명']}', '{row['페이지URL']}', {row['유저방문수']}, '{row['유저나이그룹']}', '{row['유저성별']}')"
        cursor.execute(insert_query)
    
    conn.close()

# 메인 함수
def main(hdfs_path: str, hive_table: str):
    # MySQL 연결 설정
    mysql_engine = get_mysql_connection()

    # HDFS에서 웹 로그 읽기
    web_log = read_web_log_from_hdfs(hdfs_path)

    # MySQL에서 테이블 A, B 읽기
    table_a_query = "SELECT 페이지URL, 페이지명, `이전 페이지` FROM table_a"
    table_b_query = "SELECT 유저ID, 유저명, 유저나이, 유저성별 FROM table_b"

    table_a = pd.read_sql(table_a_query, con=mysql_engine)
    table_b = pd.read_sql(table_b_query, con=mysql_engine)

    # 페이지 방문 통계 분석
    result = analyze_user_visits(web_log, table_a, table_b)

    # Hive 테이블에 저장
    save_to_hive(result, hive_table)

if __name__ == "__main__":
    hdfs_path = "/user/hdfs/web_log.csv"
    hive_table = "page_visit_statistics"
    main(hdfs_path, hive_table)

 

설명

[ read_web_log_from_hdfs 함수 ]
# InsecureClient는 인증 없이 HDFS 클러스터에 연결할 때 사용됩니다.
client = InsecureClient('http://<hdfs_host>:<port>', user='<hdfs_user>')


[ analyze_user_visits 함수 ]
# merged_df에 log + tableA + tableB 를 join 합니다.
   유저ID  방문한페이지URL             방문시간   페이지URL    페이지명  이전 페이지  유저명  유저나이 유저성별
0  1001          /home  2024-01-01 12:00:00    /home      Home      None  Alice    25     F
1  1002          /home  2024-01-01 12:05:00    /home      Home      None    Bob    35     M
2  1003      /products  2024-01-01 12:10:00  /products  Products     /home Charlie    45     M

# pandas.apply를 적용하여 유저나이를 그룹화하여 치환합니다.
merged_df['유저나이그룹'] = merged_df['유저나이'].apply(age_grouping)

# size를 통해 그룹화된 행수를 집계하고 이를 '유저방문수'라는 새로운 컬럼으로 변환합니다.
grouped = merged_df.groupby(['페이지명', '페이지URL', '유저나이그룹', '유저성별']).size().reset_index(name='유저방문수')