본문 바로가기

Study/class note

하둡 / 영화평점에 대한 큰 데이터를 내려받아 hive 에서 분석하기

44 영화평점에 대한 큰 데이터를 내려받아 hive 에서 분석하기

1. 먼저 대용량 텍스트 파일의 압축파일을 다운로드 받습니다.(putty에서 oracle유저로 수행하시오)

(모바텀으로 올리면 됩니다.)

 

2. 압축파일이 잘 올라갔는지 확인합니다.

(base) [oracle@centos ~]$ ls -l *.zip
-rw-rw-r--. 1 oracle oracle 5917549  3월 24 11:45 ml-1m.zip

 

 

3. 위의 압축파일의 압축을 해제합니다.

(base) [oracle@centos ~]$ unzip ml-1m.zip
Archive:  ml-1m.zip
   creating: ml-1m/
  inflating: ml-1m/movies.dat
  inflating: ml-1m/ratings.dat
  inflating: ml-1m/README
  inflating: ml-1m/users.dat
(base) [oracle@centos ~]$ cd ml-1m
(base) [oracle@centos ml-1m]$
(base) [oracle@centos ml-1m]$ ls
README  movies.dat  ratings.dat  users.dat

 

 

4. movies.dat파일의 head 10만 열어어 확인하시오.

(base) [oracle@centos ml-1m]$ head -10 movies.dat
1::Toy Story (1995)::Animation|Children's|Comedy
2::Jumanji (1995)::Adventure|Children's|Fantasy
3::Grumpier Old Men (1995)::Comedy|Romance
4::Waiting to Exhale (1995)::Comedy|Drama
5::Father of the Bride Part II (1995)::Comedy
6::Heat (1995)::Action|Crime|Thriller
7::Sabrina (1995)::Comedy|Romance
8::Tom and Huck (1995)::Adventure|Children's
9::Sudden Death (1995)::Action
10::GoldenEye (1995)::Action|Adventure|Thriller

 

 

5. movies.dat 파일의 총 라인수가 어떻게 되는지 확인하시오.

(base) [oracle@centos ml-1m]$ wc -l movies.dat
3883 movies.dat

 

 

6. ratings.dat 파일의 총 라인수가 어덯게 되는지 확인하시오.

(base) [oracle@centos ml-1m]$ wc -l ratings.dat
1000209 ratings.dat

 

 

7. ratings.dat 파일의 head 5만 열어서 확인하시오.

(base) [oracle@centos ml-1m]$ head -5 ratings.dat
1::1193::5::978300760
1::661::3::978302109
1::914::3::978301968
1::3408::4::978300275
1::2355::5::978824291

유저id::영화id::평점::날짜와 시간

UserID::MovieID::Rating::Timestamp

 

(base) [oracle@centos ml-1m]$ more README

README파일에서 컬럼정보를 확인할 수 있음. (q 누르면 more 빠져나올 수 있음)

 

 

8. movie.dat 파일의 위의 5줄만 출력하시오.

(base) [oracle@centos ml-1m]$ head -5 movies.dat
1::Toy Story (1995)::Animation|Children's|Comedy
2::Jumanji (1995)::Adventure|Children's|Fantasy
3::Grumpier Old Men (1995)::Comedy|Romance
4::Waiting to Exhale (1995)::Comedy|Drama
5::Father of the Bride Part II (1995)::Comedy

MovieID::Title::Genres

 

 

9. (데이터 전처리) movies.dat파일의 구분자 :: 을 ,(콤마)로 변경하시오.

(base) [oracle@centos ml-1m]$ sed s/::/,/g movies.dat

sed만 하면 변경되어 보이기만 할 뿐 실제 데이터 파일이 변경된 것이 아님.

 

(base) [oracle@centos ml-1m]$ sed s/::/,/g movies.dat >> movies_comm.csv

리다이렉션으로 다른이름으로 저장하면 원본 파일을 그대로 남겨둔 채 변경시킬 수 있음.

 

 

10. (데이터 전처리) (점심시간 문제)

위의 방법으로 ratings.dat도 전처리해서 ratings_comma.csv로 생성하시오.

users.dat도 위와 같이 전처리해서 users_comma.csv로 생성하시오.

(base) [oracle@centos ml-1m]$ mv movies_comm.csv movies_comma.csv
(base) [oracle@centos ml-1m]$ sed s/::/,/g ratings.dat >> ratings_comma.csv
(base) [oracle@centos ml-1m]$ sed s/::/,/g users.dat >> users_comma.csv
(base) [oracle@centos ml-1m]$ ls
README  movies.dat  movies_comma.csv  ratings.dat  ratings_comma.csv  users.dat  users_comma.csv

(base) [oracle@centos ml-1m]$ head -5 ratings_comma.csv
1,1193,5,978300760
1,661,3,978302109
1,914,3,978301968
1,3408,4,978300275
1,2355,5,978824291
(base) [oracle@centos ml-1m]$ head -5 users_comma.csv
1,F,1,10,48067
2,M,56,16,70072
3,M,25,15,55117
4,M,45,7,02460
5,M,25,20,55455

 

 

11. 생성한 3개의 파일을 전부 하둡 분산 파일 시스템에 올리시오.

(base) [oracle@centos ml-1m]$ hadoop fs -put movies_comma.csv /user/oracle/movies_comma.csv
(base) [oracle@centos ml-1m]$ hadoop fs -put ratings_comma.csv /user/oracle/ratings_comma.csv
(base) [oracle@centos ml-1m]$ hadoop fs -put users_comma.csv /user/oracle/users_comma.csv
(base) [oracle@centos ml-1m]$ hadoop fs -lsr /user/oracle
-rw-r--r--   3 oracle supergroup      11703 2022-03-23 14:01 /user/oracle/Case.csv
-rw-r--r--   3 oracle supergroup        102 2022-03-23 14:59 /user/oracle/dept2.csv
-rw-r--r--   3 oracle supergroup        679 2022-03-23 12:12 /user/oracle/emp.csv
-rw-r--r--   3 oracle supergroup        633 2022-03-23 13:49 /user/oracle/emp100.csv
-rw-r--r--   3 oracle supergroup     163542 2022-03-24 13:43 /user/oracle/movies_comma.csv
-rw-r--r--   3 oracle supergroup   21593504 2022-03-24 13:43 /user/oracle/ratings_comma.csv
-rw-r--r--   3 oracle supergroup     110208 2022-03-24 13:44 /user/oracle/users_comma.csv

 

 

12. 위의 3개의 파일들을 저장할 테이블을 하이브에서 각각 만드시오.

hive> create table users
    > (userid int,
    > gender string,
    > age int,
    > occupation int,
    > zipcode string)
    > row format delimited
    > fields terminated by ','
    > lines terminated by '\n'
    > stored as textfile;
OK
Time taken: 2.796 seconds
hive> create table ratings
    > (userid int,
    > movieid int,
    > rating int,
    > tstamp string)
    > row format delimited
    > fields terminated by ','
    > lines terminated by '\n'
    > stored as textfile;
OK
Time taken: 0.026 seconds
hive> create table movies
    > (movieid int,
    > title string,
    > genre string)
    > row format delimited
    > fields terminated by ','
    > lines terminated by '\n'
    > stored as textfile;
OK

 

 

13. 하둡 분산 파일 시스템에 올린 csv 파일 3개를 위의 테이블에 각각 입력하시오.

hive> load data inpath '/user/oracle/movies_comma.csv'
    > overwrite into table movies;

hive> load data inpath '/user/oracle/ratings_comma.csv'
    > overwrite into table ratings;

hive> load data inpath '/user/oracle/users_comma.csv'
    > overwrite into table users;

select count(*) 해서 데이터가 잘 들어갔는지 확인하시오.

 

 

14. movies 데이터의 위의 5건만 출력하시오.

hive> select * from movies limit 5;
OK
1       Toy Story (1995)        Animation|Children's|Comedy
2       Jumanji (1995)  Adventure|Children's|Fantasy
3       Grumpier Old Men (1995) Comedy|Romance
4       Waiting to Exhale (1995)        Comedy|Drama
5       Father of the Bride Part II (1995)      Comedy

 

 

15. 만들어져 있는 테이블 리스트를 확인하시오.

hive> show tables;
OK
movies
ratings
users

 

 

16. ratings 테이블의 구조를 확인하시오.

hive> describe ratings;
OK
userid                  int                     None
movieid                 int                     None
rating                  int                     None
tstamp                  string                  None
Time taken: 0.03 seconds, Fetched: 4 row(s)

 

 

17. ratings 테이블에서 평점, 평점별 건수를 출력하시오.

hive> select rating, count(*) as cnt
    > from ratings
    > group by rating;

1       56174
2       107557
3       261197
4       348971
5       226310

 

문제276. 마리아 디비에서 ratings 테이블을 생성하시오.

create table ratings
 (userid int(10),
movieid int(10),
rating int(10),
tstamp varchar(20));

load data local infile '/home/oracle/ml-1m/ratings_comma.csv'
replace
into table orcl.ratings
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(userid, movieid, rating, tstamp);

 

MariaDB [orcl]> select rating, count(*)
    -> from ratings
    -> group by rating;
+--------+----------+
| rating | count(*) |
+--------+----------+
|      1 |    56174 |
|      2 |   107557 |
|      3 |   261197 |
|      4 |   348971 |
|      5 |   226310 |
+--------+----------+
5 rows in set (0.381 sec)

 

문제277. 다시 hive에서 조인문장을 수행하는데 movies와 ratings 테이블을 조인해서 rating(평점)이 5점인 영화명을 중복제거해서 출력하시오.

hive> describe movies;
OK
movieid                 int                     None
title                   string                  None
genre                   string                  None
Time taken: 0.027 seconds, Fetched: 3 row(s)

hive> describe ratings;
OK
userid                  int                     None
movieid                 int                     None
rating                  int                     None
tstamp                  string                  None

movieid 로 조인!

 

hive>
    > select distinct m.title
    >  from movies m inner join ratings r
    >  on (m.movieid = r.movieid)
    >  where r.rating = 5;

하이브에서 조인할 때는 1999 ANSI 조인문법을 사용해야 합니다.

 

ㅇ1999ansi 조인문법의 종류

1. on절을 사용한 조인

2. using절을 사용한 조인

3. natural join

4. left, right, full outer join

5. cross join

 

 

문제278. 5점 평점을 받은 영화명과 그 건수를 위의 조인문을 가지고 출력하시오.

hive> select m.title, count(*) as cnt
    >  from movies m inner join ratings r
    >  on (m.movieid = r.movieid)
    >  where r.rating = 5
    >  group by m.title;

 

 

문제279. 5점 평점을 가장 많이 받은 영화명과 그 건수를 5개만 출력하시오.

hive> select m.title, count(*) as cnt
    > from movies m inner join ratings r
    > on (m.movieid = r.movieid)
    > where r.rating = 5
    > group by m.title
    > order by cnt desc
    > limit 5;

American Beauty (1999)  1963
Star Wars: Episode IV - A New Hope (1977)       1826
Raiders of the Lost Ark (1981)  1500
Star Wars: Episode V - The Empire Strikes Back (1980)   1483
Schindler's List (1993) 1475

 

 

문제280. 위의 결과를 저장할 테이블을 movie_rating이라는 이름으로 생성하시오.

hive> create table movie_rating
    > ( title string,
    > cnt int);
OK

위의 결과를 저장할 테이블을 생성했는데  view를 생성할 수도 있긴 함.

create view movie_view
as
 select m.title as title, count(*) as cnt
 from movies m inner join ratings r
 on (m.movieid = r.movieid)
 where r.rating = 5
 group by m.title
 order by cnt desc
 limit 5;

 

 

문제281. movie_rating 테이블에 문제279번의 결과를 insert 하시오.

hive>
    > insert into table movie_rating
    > select m.title as title, count(*) as cnt
    > from movies m inner join ratings r
    > on (m.movieid = r.movieid)
    > where r.rating = 5
    > group by m.title
    > order by cnt desc
    > limit 5;
    
hive> select * from movie_rating;
OK
American Beauty (1999)  1963
Star Wars: Episode IV - A New Hope (1977)       1826
Raiders of the Lost Ark (1981)  1500
Star Wars: Episode V - The Empire Strikes Back (1980)   1483
Schindler's List (1993) 1475
Time taken: 0.025 seconds, Fetched: 5 row(s)

 

 

문제282. movie_rating 테이블의 결과를 하둡 파일 시스템에 /user/oracle밑에 test.csv로 저장하시오.

hive> insert overwrite directory '/user/oracle/test.csv'
    > select *
    >  from movie_rating;

 

문제283. 하둡 분산 파일 시스템 명령어로 /user/oracle/test.csv가 있는지 조회하시오.

(base) [oracle@centos ml-1m]$ hadoop fs -lsr /user/oracle
-rw-r--r--   3 oracle supergroup      11703 2022-03-23 14:01 /user/oracle/Case.csv
-rw-r--r--   3 oracle supergroup        102 2022-03-23 14:59 /user/oracle/dept2.csv
-rw-r--r--   3 oracle supergroup        679 2022-03-23 12:12 /user/oracle/emp.csv
-rw-r--r--   3 oracle supergroup        633 2022-03-23 13:49 /user/oracle/emp100.csv
drwxr-xr-x   - oracle supergroup          0 2022-03-24 15:25 /user/oracle/test.csv
-rw-r--r--   3 oracle supergroup        199 2022-03-24 15:25 /user/oracle/test.csv/000000_0

-> 현재 test.csv는 디렉토리명, 그 밑에 000000_0으로 파일명이 자동지정됨.

 

 

문제284. 하둡 분산파일 시스템에 있는 /user/oracle/test.csv/000000_0를 리눅스 로컬 시스템에 /home/oracle 밑으로 내리시오.

(base) [oracle@centos ml-1m]$ hadoop fs -get /user/oracle/test.csv/000000_0 /home/oracle/test.csv

(base) [oracle@centos ml-1m]$ cd
(base) [oracle@centos ~]$ cat test.csv
American Beauty (1999)1963
Star Wars: Episode IV - A New Hope (1977)1826
Raiders of the Lost Ark (1981)1500
Star Wars: Episode V - The Empire Strikes Back (1980)1483
Schindler's List (1993)1475

 

문제285. hive에서 조회한 결과를 csv파일로 내리기 위해서 os에서 아래와 같이 명령어를 수행하시오.

(base) [oracle@centos ~]$ hive -e 'select * from emp'

Logging initialized using configuration in jar:file:/home/oracle/hive-0.12.0/lib/hive-common-0.12.0.jar!/hive-log4j.properties
OK
7839    KING    PRESIDENT       NULL    1981-11-17      5000    NULL    10
7698    BLAKE   MANAGER 7839    1981-05-01      2850    NULL    30
7782    CLARK   MANAGER 7839    1981-05-09      2450    NULL    10
7566    JONES   MANAGER 7839    1981-04-01      2975    NULL    20
7654    MARTIN  SALESMAN        7698    1981-09-10      1250    1400    30
7499    ALLEN   SALESMAN        7698    1981-02-11      1600    300     30
7844    TURNER  SALESMAN        7698    1981-08-21      1500    0       30
7900    JAMES   CLERK   7698    1981-12-11      950     NULL    30
7521    WARD    SALESMAN        7698    1981-02-23      1250    500     30
7902    FORD    ANALYST 7566    1981-12-11      3000    NULL    20
7369    SMITH   CLERK   7902    1980-12-09      800     NULL    20
7788    SCOTT   ANALYST 7566    1982-12-22      3000    NULL    20
7876    ADAMS   CLERK   7788    1983-01-15      1100    NULL    20
7934    MILLER  CLERK   7782    1982-01-11      1300    NULL    10
Time taken: 2.362 seconds, Fetched: 14 row(s)
(base) [oracle@centos ~]$ hive -e 'select job, sum(sal) as sumsal from emp group by job'

ANALYST 6000
CLERK   4150
MANAGER 8275
PRESIDENT       5000
SALESMAN        5600

-e : hive에 접속해서 다음에 나오는 SQL을 수행해라~

 

 

문제286. 위의 결과를 csv파일로 내리기 위해 탭을 콤마(,)로 변경해서 출력되게 하시오.

(base) [oracle@centos ~]$ hive -e 'select job, sum(sal) as sumsal from emp group by job' | sed 's/\t/,/g'

ANALYST,6000
CLERK,4150
MANAGER,8275
PRESIDENT,5000
SALESMAN,5600

 

문제287. 위의 결과를 /home/oracle밑에 result.csv로 저장하시오.

(base) [oracle@centos ~]$ hive -e 'select job, sum(sal) as sumsal from emp group by job' | sed 's/\t/,/g' >> /home/oracle/result.csv

(base) [oracle@centos ~]$ cat result.csv
ANALYST,6000
CLERK,4150
MANAGER,8275
PRESIDENT,5000
SALESMAN,5600

 

문제288. 부서번호, 부서번호별 토탈월급을 하이브에서 뽑아서 /home/oracle/result2.csv로 저장하시오.

(base) [oracle@centos ~]$ hive -e 'select deptno, sum(sal) from emp group by deptno' | sed 's/\t/,/g' >> /home/oracle/result2.csv

(base) [oracle@centos ~]$ cat result2.csv
10,8750
20,10875
30,9400

 

 

문제289. 위에서 내려받은 result2.csv를 시각화하기 위해서 주피터 노트북을 켜시오.

 

문제290. /home/oracle 밑에 있는 result2.csv를 판다스 데이터 프레임으로 생성하시오.

import pandas as pd

df = pd.read_csv("/home/oracle/result2.csv", header = None)
df.columns = ['deptno','sumsal']
df

 

문제291. 위의 결과를 막대 그래프로 시각화하시오.

import pandas as pd

df = pd.read_csv("/home/oracle/result2.csv", header = None)
df.columns = ['deptno','sumsal']
df.set_index('deptno').plot(kind='bar')

 

 

hdfs -> mapreduce -> hive -> python, R

하둡에 있는 hive 테이블의 데이터에서 원하는 데이터를 뽑아내서 csv로 저장하고 저장된 csv를 파이썬에서 시각화 했습니다. 

 

 

하둡에서 테이블이 계속 사라진 이유.

리눅스 os에서 접속디렉토리 위치에 따라 달라짐.

(base) [oracle@centos ~]$ hive

Logging initialized using configuration in jar:file:/home/oracle/hive-0.12.0/lib/hive-common-0.12.0.jar!/hive-log4j.properties
hive> show tables;
OK
emp
Time taken: 2.049 seconds, Fetched: 1 row(s)
hive> exit;

(base) [oracle@centos ~]$ cd ml-1m
(base) [oracle@centos ml-1m]$ hive

Logging initialized using configuration in jar:file:/home/oracle/hive-0.12.0/lib/hive-common-0.12.0.jar!/hive-log4j.properties
hive> show tables;
OK
movie_rating
movies
ratings
users
Time taken: 2.218 seconds, Fetched: 4 row(s)
hive>

hive접속할 때 /home/oracle에서 hive로 접속하면 emp테이블만 보이고, /home/oracle/ml-1m 여기서 hive에 접속하면 movies, ratings, users 테이블이 보입니다. 

 

 

문제292. (오늘의 마지막 문제) 하이브에서 아래의 SQL로 뽑아낸 결과를 /home/oracle 밑에 result3.csv로 저장하고 주피터 노트북에서 result3.csv를 result3 판다스 데이터 프레임으로 만들어서 막대 그래프로 시각화 하시오.

 

반응형