TIL5 : 데이터 가공의 필요성, 여러 미들웨어로 데이터 가공하기, postgreSQL 오류 해결하기

김시은·2022년 7월 28일

PostgreSQL Today I learned mysql

Today I Learned

목록 보기

3/9

데이터 가공

데이터 가공을 해야하는 이유

데이터 수집 → 데이터 가공 → 데이터 분석. raw data로 바로 분석이 가능한 경우는 거의 없으므로 분석할 수 있도록 데이터를 가공해주어야 함.

다룰 데이터가 분석 용도로 되어 있지 않은 경우
- 예) 고객의 주문정보가 저장된 테이블을 예로 들면, customerId, itemId는 숫자코드로 되어있음. 그 코드를 그대로 분석하거나 리포팅할 수 없을 것.
- 이렇게 코드로 되어 있는 것을 레이블로 변경해주어야 함.
데이터 형식이 일치하지 않아 비교가 불가능한 경우
- 예) 로그 데이터의 URL : URL 자체로는 비교가 어려움. URL의 정보를 추출해서 어떤 컨텐츠를 조회했는지, 어떤 경로로 들어왔는지 등을 분석할 수 있음
오류 회피하기
- NULL값이 있다면 이것을 어떻게 핸들링할지를 정해서 가공해주어야 함

데이터 가공의 경우들

아래 각각의 경우에 대해 MySQL, postgreSQL, pySpark, pandas 네 개의 미들웨어에서 어떻게 진행할 수 있는지 모두 경험해보았다.
아직은 네 개 미들웨어의 문법이 다 익숙하지는 않기에 일단 mySQL위주로 정리, 추후 복습해서 동일한 경우가 어떻게 다른지 따로 정리해봐야겠다.

1. 코드값을 레이블로 바꾸기

CASE문

1,2,3으로만 되어 있던 디바이스 정보를, 식별할 수 있는 문자로 변경해준다.

SELECT
	user_id
    , CASE
		WHEN register_device = 1 THEN '데스크톱'
        WHEN register_device = 2 THEN '스마트폰'
        WHEN register_device = 3 THEN '애플리케이션'
	END AS devise_name
FROM mst_users
;

2. URL에서 요소 추출하기 - URL에서 호스트 단위로 추출하기

MYSQL SUBSTRING
substring(string, 추출이 시작되는 곳, 추출글자개수)
SELECT
stamp,
SUBSTRING_INDEX(SUBSTRING_INDEX(referrer, '/', 3), '/', -1) AS referrer_host
FROM access_log;

3.URL에서 요소 추출하기 - 경로와 매개변수값 추출하기

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
;
워낙 복잡하기에 MySQL에서는 이렇게 많이 쓰지 않고 판다스나 스파크를 주로 사용한다고 함

4. 문자열을 분해하기 - URL계층 추출하기

MYSQL
split_part(string,분할 기준 문자,위치)
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
;
보는 것처럼 매우 복잡! 역시 계층 추출은 MySQL으로는 잘 하지 않음

5. 날짜 다루기 - 오늘 날짜 추출하기

MYSQL (postgreSQL도 동일)

SELECT
CURRENT_DATE AS dt,
CURRENT_TIMESTAMP AS stamp
;

6. 날짜 다루기 - 지정 날짜 추출하기

#1 postgreSQL 가능, MySQL 불가능
SELECT
CAST('2022-07-27' AS date) AS dt,
CAST('2022-07-27 12:00:00' AS timestamp) AS stamp
;

#2 postgreSQL 가능, MySQL 가능
SELECT
date '2022-07-27' AS dt,
timestamp '2022-07-27 12:00:00' AS stamp
;

#3 postgreSQL 가능, MySQL 불가능
SELECT 
'2022-07-27'::date AS dt,
'2022-07-27 12:00:00'::timestamp AS stamp
;

두 번째 방식이 가장 공통적으로 많이 가능하기 때문에 이걸로 기억해두면 좋음

7. 날짜 다루기 - 날짜/시각에서 연월일 등 추출하기

MYSQL
#EXTRACT 함수로
SELECT
stamp,
EXTRACT(YEAR FROM stamp) AS year,
EXTRACT(MONTH FROM stamp) AS month,
EXTRACT(DAY FROM stamp) AS day,
EXTRACT(HOUR FROM stamp) AS hour
FROM
(SELECT timestamp '2022-07-27 12:00:00' AS stamp) AS t
;
#substring 함수로
SELECT
stamp,
substring(stamp, 1, 4) AS year,
substring(stamp, 6, 2) AS month,
substring(stamp, 9, 2) AS day,
substring(stamp, 12, 2) AS hour,
substring(stamp, 1, 7) AS 'year_month'
FROM
(SELECT '2022-07-27 12:00:00' AS stamp) AS t
;
year_month와 같이 실무에서는 연월을 붙여서 분석하는 경우가 많음. 이 경우에는 timestamp는 연 월을 따로 추출해서 합치는 작업까지 해주어야 하지만, 저렇게 substring으로 문자열 형태로 추출하는게 더 빠르고 간편함

PostgreSQL 에러 고치기

pgAdmin으로 postgreSQL을 접속하려고 하면 password authentication 에러가 계속 발생했다. 구글링해서 여러 해결방법을 시도해봤지만 결국 모두 실패, 완전히 삭제하고 brew로 설치하는 방법으로 해결했다.
아래 노션 링크로 우선 이 경험을 정리해보았다. 추후 시간되면 velog로 다시 정리해봐야지.
https://www.notion.so/PostgreSQL-b9a94861112e45f199c013a0c303cb2b