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 READMEREADME파일에서 컬럼정보를 확인할 수 있음. (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)::ComedyMovieID::Title::Genres
9. (데이터 전처리) movies.dat파일의 구분자 :: 을 ,(콤마)로 변경하시오.
(base) [oracle@centos ml-1m]$ sed s/::/,/g movies.datsed만 하면 변경되어 보이기만 할 뿐 실제 데이터 파일이 변경된 것이 아님.
(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 Nonemovieid 로 조인!
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 판다스 데이터 프레임으로 만들어서 막대 그래프로 시각화 하시오.
'Study > class note' 카테고리의 다른 글
| 하둡 / 스파크에서 스파크 SQL 사용하기 (0) | 2022.03.28 |
|---|---|
| 하둡 / 스파크 설명, 스파크 설치 (0) | 2022.03.28 |
| 하둡 / NoSQL 하이브 SQL 사용하기2 (0) | 2022.03.24 |
| 하둡 / NoSQL 하이브 SQL 사용하기 (0) | 2022.03.24 |
| 하둡 / 하이브 설치 (0) | 2022.03.23 |
