[Hive] Hive on Hadoop cluster 실습 + yarn ui 확인하기

이번 포스트에는 fully distributed hadoop cluster에 HQL파일을 생성하여 job을 제출하는 실습을 해보겠습니다.

 

하둡 및 하이브를 설치하지 못하신 분은 아래의 링크를 참조해주세요.

2021.08.08 - [BigData] - [BigData] 완전 분산 하둡 클러스터(hadoop cluster)구축하기(4개 노드) 1. 4개의 가상 노드 띄우기

[BigData] 완전 분산 하둡 클러스터(hadoop cluster)구축하기(4개 노드) 1. 4개의 가상 노드 띄우기

 

2021.10.11 - [BigData] - [BigData] 완전 분산 하둡 클러스터(hadoop cluster)(4개 노드) 에 "Hive" 설치 및 실습 하기

[BigData] 완전 분산 하둡 클러스터(hadoop cluster)(4개 노드) 에 "Hive" 설치 및 실습 하기

---

실습용 데이터
https://data.cityofnewyork.us/Public-Safety/Motor-Vehicle-Collisions-Crashes/h9gi-nx95

Motor Vehicle Collisions - Crashes | NYC Open Data

뉴욕시티에서 일어난 자동차,오토바이 등 탈 것에 의한 충돌횟수에 대한 데이터

실습 시나리오

1. local에 newyork city crash tsv파일을 다운로드 한다.
2-1. hdfs에 source data가 들어갈 directory를 생성한다.
2-2. local에 있는 tsv파일을 2-1번에서 생성한 directory에 put한다.
3. HQL 파일을 작성한다.

1. source table이 있으면 source table을 DROP
2. source table이 없다면 source table을 CREATE
3. target table이 있다면 target table을 DROP
4. target table이 없다면 target table을 CREATE
5. source table로 부터 target table로 INSERT OVERWRITE

4. HQL을 실행 및 결과 확인

조건

source table은 tsv파일을 읽는 textfile format의 테이블이다.
target table은 orc file format의 테이블이다.
HQL파일은 멱등성(여러번 실행해도 같은 결과가 나오게끔)이 가능하도록 구성한다.

---

1. local에 tsv 파일 다운로드하기

위의 사진에서 TSV for excel을 우클릭 후 링크 주소 복사를 통해 URL을 복사합니다.

1) 하둡 계정의 root디렉토리에서 wget url

wget이 없다면 wget을 다운 받아야합니다.
wget https://data.cityofnewyork.us/api/views/h9gi-nx95/rows.tsv?accessType=DOWNLOAD&bom=true

2) 생성된 파일 확인 및 이름변경

mv rows.tsv?accessType=DOWNLOAD NYCRASH.tsv

---

2-1. HDFS 디렉토리 생성

1) 하둡을 실행

start-all.sh

2) hdfs에 NY_CRASH 디렉토리 생성

hdfs dfs -mkdir /user/hive/NY_CRASH

3) 생성된 NY_CRASH 디렉토리 확인

hdfs dfs -ls /user/hive

---

2-2. HDFS 디렉토리에 tsv파일 PUT

1) PUT

hdfs dfs -put NYCRASH.tsv /user/hive/NY_CRASH

2) 확인하기

hdfs dfs -ls /user/hive/NY_CRASH

---

3. HQL 파일 작성하기

1) ny_crash.hql 파일 생성 및 편집하기

touch ny_crash.hql
nano ny_crash.hql (nano가 안되시면 다운받아야합니다.)
아래의 hql내용을 작성하여 ctrl+x y enter로 저장

2) target 테이블로 넣고자 하는 데이터

구하고자 하는 데이터

00:00~06:00 시간내에 맨해튼에서 날짜별, 시간별 부상자가 가장 많거나, 사망자가 가장 많은 데이터 구하기
CRASH_DATE
CRASH_TIME
NUMBER_OF_PERSONS_INJURED
NUMBER_OF_PERSONS_KILLED
컬럼을 사용한다.

---

처리방법

CRASH_DATE 는 MM/dd/yyyy형태, CRASH_TIME은 H:mm 혹은 HH:mm형태
따라서,
1) 오름차순 정렬을 위한, CRASH_DATE를 yyyy/MM/dd형태로 바꾸기위해 unix_timestamp, from_unixtime을 사용

2) 조건으로 00:00과 06:00사이에 있는 데이터를 대상으로 하기때문에, CRASH_TIME을 HH:mm형태로 만들기 위해 LPAD를 사용

3) BOROUGH의 값이 "MANHATTAN"인 것만 추출

4)날짜별, 시간별 부상자수와 사망 수를 구하기

5)날짜별 부상자수를 row_number를 통해 내림차순 등수 구하기, 사망자수를 row_number를 통해 내림차순 등수 구하기

6)날짜별 부상자수가 1위이거나, 사망자수가 1위인 조건을 걸어 날짜,시간,부상자 수, 사망자 수를 구하기

7)날짜, 시간순으로 오름차순 정렬

3) ny_crash.hql 내용

--NYCRASH.hql
DROP TABLE IF EXISTS NY_CRASH;
--NY_CRASH 테이블이 있다면 삭제(멱등성을 위해 존재)
 
CREATE EXTERNAL TABLE IF NOT EXISTS NY_CRASH (
CRASH_DATE STRING,
CRASH_TIME STRING,
BOROUGH STRING,
ZIP_CODE STRING,
LATITUDE DOUBLE,
LONGITUDE DOUBLE,
LOCATION STRING,
ON_STREET_NAME STRING,
CROSS_STREET_NAME STRING,
OFF_STREET_NAME STRING,
NUMBER_OF_PERSONS_INJURED INT,
NUMBER_OF_PERSONS_KILLED INT,
NUMBER_OF_PEDESTRIANS_INJURED INT,
NUMBER_OF_PEDESTRIANS_KILLED INT,
NUMBER_OF_CYCLIST_INJURED INT,
NUMBER_OF_CYCLIST_KILLED INT,
NUMBER_OF_MOTORIST_INJURED INT,
NUMBER_OF_MOTORIST_KILLED INT,
CONTRIBUTING_FACTOR_VEHICLE_1 STRING,
CONTRIBUTING_FACTOR_VEHICLE_2 STRING,
CONTRIBUTING_FACTOR_VEHICLE_3 STRING,
CONTRIBUTING_FACTOR_VEHICLE_4 STRING,
CONTRIBUTING_FACTOR_VEHICLE_5 STRING,
COLLISION_ID INT,
VEHICLE_TYPE_CODE_1 STRING,
VEHICLE_TYPE_CODE_2 STRING,
VEHICLE_TYPE_CODE_3 STRING,
VEHICLE_TYPE_CODE_4 STRING,
VEHICLE_TYPE_CODE_5 STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION 'hdfs://mycluster/user/hive/NY_CRASH'
tblproperties ("skip.header.line.count"="1");
--source table인 ny_crash 테이블 생성
-- tsv파일은 \t단위로 구분하여 textfile로 읽는다. 데이터파일의 첫번째 row는 컬럼정보이므로 header를 설정
-- csv파일로 읽지않는 이유는 한 개의 컬럼 내에 값이 ,이 포함되어 있어서 ,을 구분자로 쓰면 값이 안맞는다.
 
DROP TABLE IF EXISTS NY_CRASH_MAX_INJURED_KILLED;
--NY_CRASH_MAX_INJURED_KILLED이 존재하면 삭제(멱등성을 위해 존재)
 
CREATE EXTERNAL TABLE IF NOT EXISTS NY_CRASH_MAX_INJURED_KILLED (
CRASH_DATE_FORMATTED STRING,
CRASH_TIME_HH STRING,
TOTAL_INJURED INT,
TOTAL_KILLED INT
)
STORED AS ORC
LOCATION 'hdfs://mycluster/user/hive/NY_CRASH/NY_CRASH_MAX_INJURED_KILLED'
;
--target table인 ny_crash_max_injured_killed 테이블 생성
 
INSERT OVERWRITE TABLE NY_CRASH_MAX_INJURED_KILLED
SELECT C.CRASH_DATE_FORMATTED
,C.CRASH_TIME_HH
,C.TOTAL_INJURED
,C.TOTAL_KILLED
FROM (
      SELECT B.*
            ,ROW_NUMBER() OVER(PARTITION BY B.CRASH_DATE_FORMATTED ORDER BY TOTAL_INJURED DESC) AS MAX_INJURED__DESC_RN
            ,ROW_NUMBER() OVER(PARTITION BY B.CRASH_DATE_FORMATTED ORDER BY TOTAL_KILLED DESC) AS MAX_KILLED__DESC_RN
        FROM (
              SELECT A.CRASH_DATE_FORMATTED
                    ,A.CRASH_TIME_HH
                    ,MAX(A.NUMBER_OF_PERSONS_INJURED) AS TOTAL_INJURED
                    ,MAX(A.NUMBER_OF_PERSONS_KILLED) AS TOTAL_KILLED
                FROM (
                      SELECT A.*
                            ,from_unixtime(unix_timestamp(CRASH_DATE,"MM/dd/yyyy"),"yyyy/MM/dd") as CRASH_DATE_FORMATTED
                            ,LPAD(CRASH_TIME,5,"0") as CRASH_TIME_HH
                        FROM ny_crash A
                      ) A
               WHERE ((A.CRASH_TIME_HH BETWEEN "00:00" AND "06:00")
               AND (A.BOROUGH = "MANHATTAN"))
               GROUP BY A.CRASH_DATE_FORMATTED
                       ,A.CRASH_TIME_HH
             ) B
      ) C
WHERE ((C.MAX_INJURED__DESC_RN <= 1)
OR (C.MAX_KILLED__DESC_RN <= 1))
ORDER BY C.CRASH_DATE_FORMATTED
   ,C.CRASH_TIME_HH
; 
--source table -> target table

hive_실습_on_cluster.hql

0.00MB

---

4. HQL 실행 및 결과확인

1) HQL 실행하기

hive -f hql파일이 있는 디렉토리/hql파일명.hql

hive -f /home/hdoop/ny_crash.hql

2) NY_CRASH_MAX_INJURED_KILLED 디렉토리 생성 확인

hdfs dfs -ls /user/hive/NY_CRASH/

3) NY_CRASH_MAX_INJURED_KILLED 데이터가 쓰여졌는지 확인

hdfs dfs -ls /user/hive/NY_CRASH/NY_CRASH_MAX_INJURED_KILLED/

4) hive 접속 및 테이블 조회, 데이터 조회

hive
show create table ny_crash;

show create table ny_crash_max_injured_killed;

select * from ny_crash_max_injured_killed;

---

5. Yarn UI확인하기

• 3개의 노드에서 각각 컨테이너를 띄워 분산처리를 하는 것을 확인 할 수 있습니다.

---

알게 된 것 정리하기

1.guava version(11.0 version)이낮으면 select시에 Exception in thread "main" java.lang.IncompatibleClassChangeError: Class com.google.common.base.Suppliers 와 같은 에러를 낸다 27.0 version에서는 안난다.

 

2. hive의 엔진은 tez를 쓰지않으면 yarn의 application type에는 mapreduce로 기록된다.

 

3.running container 수에 따른 working datanode 수?

->

container는 작업의 단위 그리고 datanode는 yarn에 의해 분산처리가 됨. 3개가 구성되어있으므로 3개가 각각 컨테이너를 띄움

 

4.workload가 heavy하지 않을 시 2개의 노드만으로 돌리는지?

->

workload heavy와 관련없이 yarn은 가용자원을 최대한 사용하려고 한다.

 

5. datanode가 일한 흔적은 어디서 볼 수 있는지

->

yarn ui 혹은 job historyserver 19888에서 container를 띄운 노드 이름 확인 or mapreduce작업을 한 숫자 확인

---

이것으로 간단한 예제를 통한 HQL파일로 하둡에 job제출하기를 실습 했습니다.