이번 주차에서는 Hive(하이브) 환경과 연결한 DB에서의 다양한 SQL 쿼리문 실습과 짧은 미니 팀 프로젝트를 수행했습니다. 프로젝트의 주제의 경우 주어진 주식 데이터를 이용해 투자할 주식을 정하고 주어진 알고리즘을 통해 매수 매도 타임을 정하고 얼마나 벌 수 있을 지에 대한 탐구였습니다.

이외에도 자유 시간이 주어졌을 때 Docker(도커) 환경에서 사용된 hive_hadoop_start.sh 에 대해 학습하고 korean_encoding.sh 와 auto_start.sh 파일을 리눅스 환경에서 작성하는 등의 추가 학습을 진행했었습니다.

---

✅ 이번 주의 학습활동✍

• hadoop_hive_dockerfile.txt 를 수정한 파일을 통해 새로운 도커 환경에서 수업을 진행했습니다.

• mysql_5_7_select.sh, mysql_5_7_install.sh, hadoop_hive_start.sh 파일들을 설치하는 과정이 포함되었기 때문에
  도커 빌드 후 해당 환경에서 ./hadoop_hive_start.sh 명령어를 통해 간편히 하둡과 하이브를 시작할 수 있게 되었습니다.

• 하둡에 저장된 데이터를 하이브를 통해 테이블을 생성하고 DBeaver 프로그램을 통해서 "데이터 집계, 조인, 서브쿼리, 고급 쿼리" 구문 등을 실행하는 학습을 진행했습니다.

  1. 날짜 조건 처리 : "0000-00-00" 과 같은 형식으로 가능

  2. GROUP BY 함수 : SELECT 문에서 같은 값의 데이터를 모으는 기능

  3. 집계 함수 : SUM, AVG, COUNT, MIN/MAX 등의 그룹별 분석을 위한 기능

  4. INNER JOIN 절 : 두 테이블을 ON 뒤에 정의한 조건에 맞춰서 결합하는 기능

     • ✅ 조인한 값에서 WHERE 절을 다르게 하여 다른 조건에서의 같은 컬럼 값을 보고 싶다면 여러 번 JOIN 을 통해서 가능.

  5. 여러 "FIlter 조건" 과 함께 다양한 INNER JOIN 작성

  6. JOIN 과 GROUP BY 동시에 사용

  7. 집계함수, ROUND 함수 등을 이용해 컬럼 데이터 값을 계산하고 새로운 데이터 값을 조회

  8. 서브쿼리의 한 종류인 "인라인 뷰(Inline view)" 를 작성해 기존에 작성한 테이블이 아닌 SELECT 문을 이용한 새로운 테이블에서 데이터를 조회하는 듯이 쿼리문을 작성.

  9. WITH ~ AS ~ 구문을 작성했습니다. 이는 인라인 뷰와 비슷한 기능이지만, 쿼리문 안에 작성하는 인라인 뷰와는 달리, 가장 앞쪽에 배치해서 단독으로 작성되고 마치 새로운 테이블을 선언한 것과 같으며 이어지는 쿼리문에서 사용될 수 있음.

  10. Limit 구문을 이용해 TOP-N 등의 값을 조회 가능.

  11. RANK() 함수 : 순위를 구하는 분석함수, OVER() 절과 함께 쓰이며 OVER 절 안에 반드시 ORDER BY 절을 사용해 순위의 기준을 지정

  12. PARTITION BY 절 : 분석함수를 적용할 대상을 나누는 역할, OVER 절 안에서 사용

📖 중복된 값을 제거하고 조회결과를 만드는 GROUP BY 절과 달리,
PARTITION BY 는 이미 만들어진 결과에 분석함수 대상만 나누는 역할만 한다.
따라서 GROUP BY 는 조회 결과 건수의 변화를 가져오지만,
PARTITION BY 는 조회 결과 건수 변화에 전혀 영향을 미치지 않는다. 그저 분석함수를 적용할 대상을 나눌 뿐이다. 📕

📖 ORDER BY 에서 default는 ASC
내림차순으로 보고싶다면 DESC 를 반드시 적어야함. 📕

🔝 위와 같은 내용들의 여러 쿼리문들을 작성하는 실습을 진행했습니다. 💪

• 📌 "쿼리문의 실행 순서" 이미지 :

  

• Hive(하이브) 와 Python(파이썬) 을 연동하여 파이썬으로 하이브 내의 데이터를 조회하고 봉차트와 막대 그래프를 그리는 학습을 진행했습니다.

  • finance_y 테이블을 새롭게 추가하고
  • JOIN절을 통해 원하는 데이터를 조회한 후
  • DataFrame 으로 담는 과정을 진행했습니다.
  • 또한, matplotlib 와 plotly 를 통해 양음봉차트, 막대 그래프 등을 그리는 실습을 진행했습니다.

• 📖 pyhive 모듈 을 통해 하이브와 연결이 가능하며 hive.connect('localhost') 와 같이 연결 구문을 실행하면 가능하다.

• 기존의 사용한 도커 환경에서는 hive_hadoop_start.sh 파일을 통해 "하둡-하이브-MySQL" 등이 설치 되고 실행되어 사용했습니다. 그러나 한번 Exited 된 도커 컨테이너를 재실행하기 위해서는 여러 줄의 명령어를 입력하거나 새로운 컨테이너를 만들어야만 했습니다. 따라서 해당 .sh(쉘 스크립트) 파일을 참고해 "사전 세팅이 완료된 컨테이너" 에서 하둡-하이브 재실행이 가능한 쉘 스크립트를 작성하고자 했습니다. 또한 한글 인코딩 명령어를 포함한 쉘 스크립트도 작성했습니다.

  • 다양한 테스트를 통해 최종적으로 korean_encoding.sh 과 auto_start.sh 파일 두 개를 완성했습니다.
  • 하나의 쉘 스크립트로 하고 싶었으나 한글 인코딩 이후의 진행이 되지 않았습니다.
  • 아마 LC_ALL=ko_KR.UTF-8 bash 의 bash 로 인해 그 다음 진행이 되지 않는 것 같았습니다.
  • 또한 서브 스크립트로 source ./korean_encoding.sh 로 활용하고 싶었으나
  • 이것도 한글 인코딩 이후의 명령어들이 실행되지 않아 어려움을 겪었습니다.
  • 따라서, 하나의 쉘 스크립트로 진행하는 것은 포기하고 두 개의 스크립트로 진행하는 것으로 끝마쳤습니다.

• korean_encoding.sh

#!/bin/bash
#한글 다운로드
# apt-get install locales - 도커 빌드 단계에서 설치됨

# 아래 명령어를 통해 hive에서 데이터 조회 시 한글 인코딩이 잘 됨.
## 가장 먼저 실행되어야 함.

#한글 출력 설정
localedef -f UTF-8 -i ko_KR ko_KR.UTF-8 &
sleep 3

#하이브 한글 출력 설정
export LC_ALL=ko_KR.UTF-8
sleep3

#한글 입력 설정
LC_ALL=ko_KR.UTF-8 bash

• auto_start.sh

#!/bin/bash
# 하둡과 하이브 세팅이 사전에 완료된 환경에서
## docker를 다시시작 했을 때 불필요한 작업 없이 하둡과 하이브를
### 실행시키기 위한 것. (사전 세팅이 없다면 사용X)

#### 기존의 nohup &으로 실행되었던 명령어들을 &(백그라운드)로만 하여
#### docker 실행창을 끄면 해당 프로세스들이 일부러 죽게 만듦
##### => 실시간으로 CLI 환경창에서 실행되는 내용을 표시하기 위함.

# 디버그 모드 활성화 => 실행창에 작업기록이 남겨짐.
set -x

/etc/init.d/ssh restart &
sleep 3

# Hadoop 클러스터의 모든 구성 요소를 다시 시작
/root/hadoop/sbin/start-all.sh &
sleep 15

# MySQL 서비스를 다시 시작
service mysql restart &
sleep 10

# HiveServer2를 백그라운드에서 실행
hive --service hiveserver2 &

👥 미니 팀 프로젝트

• Hive(하이브) 에 저장되어 있는 stock, history_dt, finance_y 테이블들을 이용해 "주식 데이터 분석" 을 하는 미니 팀 프로젝트를 시작 했습니다.

• '2019-01-01 ~ 2020-03-19' 까지의 주식의 변화와 기업의 재무정보를 통해 하이브 테이블의 데이터들을 SQL 을 통해 투자하고자 하는 종목을 선정하고,
  시각화 예시 코드를 활용해서 '2020-03-20 ~ 2020-12-30' 까지의 매수 매도 타임을 잡고 큰 수익을 내고자 하는 것이 이번 미니 프로젝트의 목적입니다.

• 시각화 코드는 예시대로 사용하면 되기에 투자할 주식 종목을 고르는 것에 집중했습니다.

• 처음에는 history_dt 테이블을 이용해 일별 주가, 거래량 등의 데이터를 임의의 기준으로 나누어 종목을 선택하고자 하였고, 이후 참고 자료를 보면서 finance_y 테이블의 기업 재무정보를 통해 1차적으로 '돈을 잘버는 기업' (=PER 항목이 낮은) 들을 필터링하고 이전에 진행한 주가의 등락률, 거래량등의 데이터들을 확인하면서 투자할 주식 종목의 후보들을 조회할 수 있었습니다.

😄 학습 기간 동안 느낀 점 😭

• 그동안 SQL 쿼리문을 이렇게까지 많이 써본 적 없어서 불안점이 있었지만 이번 기회에 많이 경험할 수 있어서 좋았습니다. JOIN과 같이 평소 어렵다고 생각되었던 부분도 같이 학습할 수 있어서 좋은 경험이었습니다. 또한 WITH ~ AS, PARTITION BY 절은 처음 봤는데 설명이 잘 되어 이해하는데 쉬웠고 반복 실행하면서 익숙해져서 만족했습니다.

• 단순한 SQL 쿼리문이었지만 실행시간이 너무 오래 걸린 점이 아쉬웠습니다. 단일 컴퓨터로 많은 양의 데이터를 조회하다보니 도커 컨테이너에서 확인할 수 있듯이 상당히 많은 양의 CPU 부하(300% 이상의 사용량...)가 걸려 속도가 느렸습니다.😭

• 하이브의 쿼리 실행 속도를 높이기 위해 도커파일과 .sh 파일을 통해 환경에 apache-tez를 설치하는 것에 성공했으나, 컴퓨터 메모리 부족 현상으로 복잡한 쿼리문을 실행이 불가능했습니다. 따라서 다시 원래의 환경을 사용할 수 밖에 없어 아쉬웠습니다.

• 하이브와 파이썬을 연결한 후 SQL절을 통해 파이썬에서 데이터를 조회했는데 한글 인코딩이 깨져서 출력되어서 어려움을 겪었습니다.
  알고보니 하이브가 실행되고 있는 도커 환경에서 docker run 이후, SSH 서비스 시작전에

#한글 출력 설정
localedef -f UTF-8 -i ko_KR ko_KR.UTF-8
#하이브 한글 출력 설정
export LC_ALL=ko_KR.UTF-8
#한글 입력 설정
LC_ALL=ko_KR.UTF-8 bash 

위 와 같은 명령어를 실행시킨 다음 "ssh 실행 -> 하둡 실행 -> mysql 실행 -> 하이브 실행" 을 진행해서 다시 하이브에서 데이터를 가져와 파이썬에서 조회하면 한글 인코딩이 잘 되어 출력되었습니다. 처음 해당 문제를 해결하지 못해 아쉬웠습니다.
그래도 이를 바탕으로 korean_encoding.sh 파일을 작성할 수 있었습니다.

• 도커 실행 후 하둡과 하이브 실행을 위한 기존의 hadoop_hive_start.sh 파일의 내용을 보며 공부했고, 각 명령어들이 왜 사용되며 어떻게 사용되는 지를 파악하며 기존의 불필요한 작업들을 없애고 사전 세팅이 완료된 도커 컨테이너 환경에서 사용할 수 있는 auto_start.sh 작성한 것이 만족스러웠습니다.

  • 또한 앞서 언급한 korean_encoding.sh 과 auto_start.sh 파일 두 개를 완성 시키면서 쉘 스크립트에 대해 학습할 수 있었고, 또한 해당 파일들을 일부러 리눅스 환경에서 vi 명령어를 통해 작성해 리눅스에 익숙해지는 시간을 가져 만족스러웠습니다.

  • 앞으로도 주어진 것을 '따라하기' 보다는 '왜 이렇고, 어떻게 되는지?' 를 생각하면서 저만의 것을 만들어 나갈 생각입니다.

• 다음 수업 내용 진행을 위해 도커 환경에 airflow 를 설치하려고 했으나 설치가 잘되지 않아 아쉬웠습니다.
  이전 강의에서 에어플로우를 잠깐 경험한 적이 있어 이에 대한 기대감이 있었으나 첫 시작인 환경 설정이 되지 않아서 다음 주로 진행이 미루어졌습니다.
  이번 학습을 하면서 "여러 빅데이터 시스템을 다루는 것도 중요하지만 이를 위한 환경 설정, docker 파일, Linux 환경 활용 등이 매우 중요하다는 것" 을 알 수 있었습니다.

👥 미니 팀 프로젝트

• 처음 미니 팀 프로젝트 주제가 주어졌을 때, 저를 포함한 모든 팀원들이 전부 '주식' 에 대한 지식이 전혀 없어 막막했습니다.

• 팀원들과 상의해 임의의 정의한 주가, 거래량 등을 기준으로 투자 종목을 선택하고자 했으나, 이후 주식에 대한 참고 자료들을 보며 "기업의 재무정보의 중요성" 을 깨닫고 이전까지 한 테스트를 멈추고 새로운 SQL 쿼리를 작성하느라 시간이 많이 지체 되었습니다.

• 하이브 환경에서 SQL 쿼리를 실행하는데 시간이 오래 걸려 WHERE 절을 한번 잘못 다루면 약 3~4분의 시간이 날라가는 것을 경험했고 아쉬웠습니다.

🚩 마무리 및 단기 미래 계획 🚀

• 진행했던 프로젝트에서 투자 종목 후보군들의 주식 데이터를 시각화하면서 최종 3 개의 주식 데이터를 추리고, 각각의 '2020-03-20 ~ 2020-12-30' 까지의 매수 매도 타임을 정하고 큰 수익이 나는 지 확인하는 작업을 진행할 것 같습니다.

• 이번 미니 프로젝트를 하면서 도메인 지식의 중요성을 다시 한번 깨닫게 되었고, 이후에 있을 '파이널 프로젝트' 에서의 주제 선정하는데 많은 참고 경험이 되었습니다.

• 이전 학습의 복습을 하면서 취업 준비도 할 예정입니다.