4. HIVE

728x90

■ 4. HIVE

$ cd .bash_profile

열고

맨 아래에

export HIVE_HOME=/home/oracle/hive-0.12.0

export PATH=$HIVE_HOME/bin:$PATH

추가하면

$ cd /home/oracle/hive-0.12.0/bin

$ ./hive

이렇게 접속했던 hive 를

[orcl:~]$ . .bash_profile

[orcl:~]$ hive

문제9. hive 로 접속해서 dept 테이블을 생성하시오

hive> load data inpath '/user/oracle/dept2.csv'

overwrite into table dept;

hive> select * from dept;

문제10. dept2.csv , emp2.csv 로드하시오

select * from emp

select * from dept

문제11. hive 에서 월급이 3000 인 사원들의 이름,월급을 출력하시오!

select ename

from emp

where sal = 3000;

FORD 3000

SCOTT 3000

문제12. 직업, 직업별 토탈월급을 출력하시오

select job, sum(sal)

from emp

group by job;

ANALYST 6000

CLERK 4150

MANAGER 8275

PRESIDENT 5000

SALESMAN 5600

문제13. 위의 결과를 다시 출력하는데 토탈월급이 높은것 부터 출력하시오

자꾸 에러뜨는 경우

리눅스에서 jsp 누르면

RunJar 라고 나옴 (컨트롤 Z 눌러서 비정상종료 시킨 사람들 나오는 특징)

kill -9 (숫자아이디)

그리고 hive 돌리면 됨

order by 절에서 alias 로 지정해줘야 한다.

select job, sum(sal) S

from emp

group by job

order by S desc;

MANAGER 8275

ANALYST 6000

SALESMAN 5600

PRESIDENT 5000

CLERK 4150

문제14. 부서번호, 부서번호별 평균월급을 출력하는데

부서번호별 평균월급이 낮은것 부터 출력하시오

select deptno, round(avg(sal)) AVG_S

from emp

group by deptno

order by AVG_S asc;

문제15. emp 와 dept 를 조인해서 이름,부서위치를 출력하시오

※ hive 에서는 1999 ansi 조인문법만 지원된다. (on 절)

hive> select e.ename, d.loc

> from emp e join dept d

> on (e.deptno=d.deptno);

문제16. 직업, 직업별 토탈월급을 출력하는데 직업이 SALESMAN 은 제외하고,

직업별 토탈월급이 5000 이상인것만 출력하시오

※ hive 에서는 having 절이 지원 안된다.

※ hive 에서는 from 절의 서브쿼리 사용할 때는 별칭을 사용해야 한다.

hive> select job, sumsal

> from (select job, sum(sal) as sumsal

from emp

where job!='SALESMAN' group by job) a

> where sumsal>=5000;

ANALYST 6000

MANAGER 8275

PRESIDENT 5000

문제17. 부서위치, 부서위치별 토탈월급을 출력하시오 !

hive> select d.loc, sum(e.sal)

> from emp e join dept d

> on (e.deptno=d.deptno)

> group by d.loc;

문제18. full outer 조인 이 가능한지 확인해보시오

****컨트롤 C 자꾸 누르니까 RunJar 생긴다

드래그 해도 복사되니까 드래그만 할 것.

hive> select e.ename, d.loc

> from emp e full outer join dept d

> on (e.deptno=d.deptno);

■ hive 에서 data 분석함수 사용하는 방법

문제19. 이름,월급, 순위를 출력하는데 순위가 월급이 높은 사원부터 출력되게 하시오

hive> select ename, sal,

> dense_rank()over(order by sal desc)

> from emp;

KING 5000 1

FORD 3000 2

SCOTT 3000 2

JONES 2975 3

BLAKE 2850 4

CLARK 2450 5

ALLEN 1600 6

TURNER 1500 7

MILLER 1300 8

WARD 1250 9

MARTIN 1250 9

ADAMS 1100 10

JAMES 950 11

SMITH 800 12

문제20. 부서번호, 이름, 월급, 순위를 출력하는데 순위가 부서번호별로 각각 월급이 높은

사원순으로 순위를 부여하시오

hive> select deptno, ename, sal, dense_rank()over(partition by deptno order by sal desc) as rn

> from emp;

10 KING 5000 1

10 CLARK 2450 2

10 MILLER 1300 3

20 FORD 3000 1

20 SCOTT 3000 1

20 JONES 2975 2

20 ADAMS 1100 3

20 SMITH 800 4

30 BLAKE 2850 1

30 ALLEN 1600 2

30 TURNER 1500 3

30 MARTIN 1250 4

30 WARD 1250 4

30 JAMES 950 5

문제21. 사원번호, 이름, 월급, 월급의 누적치가 출력되게 하시오

hive> select empno, ename, sal, sum(sal)over(order by empno asc)

> from emp;

7369 SMITH 800 800

7499 ALLEN 1600 2400

7521 WARD 1250 3650

7566 JONES 2975 6625

7654 MARTIN 1250 7875

7698 BLAKE 2850 10725

7782 CLARK 2450 13175

7788 SCOTT 3000 16175

7839 KING 5000 21175

7844 TURNER 1500 22675

7876 ADAMS 1100 23775

7900 JAMES 950 24725

7902 FORD 3000 27725

7934 MILLER 1300 29025

문제22. 부서번호, 사원번호, 이름, 월급, 월급의 누적치가 출력되게 하는데

부서번호별로 각각 월급의 누적치가 출력되게 하시오 !

hive> select deptno,empno,ename,sal,sum(sal)over(partition by deptno order by sal asc)

> from emp;

10 7934 MILLER 1300 1300

10 7782 CLARK 2450 3750

10 7839 KING 5000 8750

20 7369 SMITH 800 800

20 7876 ADAMS 1100 1900

20 7566 JONES 2975 4875

20 7902 FORD 3000 10875

20 7788 SCOTT 3000 10875

30 7900 JAMES 950 950

30 7654 MARTIN 1250 3450

30 7521 WARD 1250 3450

30 7844 TURNER 1500 4950

30 7499 ALLEN 1600 6550

30 7698 BLAKE 2850 9400

■ 오라클과 hive 의 함수 비교

오라클 vs hive

1. 문자함수

upper upper

lower lower

initcap X

substr substr

concat concat

trim trim

ltrim ltrim

rtrim rtrim

lpad X

rpad X

2. 숫자함수

round round

trunc X

power power

3. 날짜함수

monthbetween monthbetween

add_months add_months

next_day next_day

last_day last_day

4. 변환함수

to_date to_date

to_char cast

to_number cast

year

month

day

문제23. 이름을 출력하는데 이름의 첫글자만 출력하고 그 첫글자를

소문자로 출려하시오

select lower(substr(ename,1,1)) from emp;

문제24. 이름, 입사한 년도 4자리를 출력하시오

hive> select ename, year(hiredate) from emp;

KING 1981

BLAKE 1981

CLARK 1981

JONES 1981

MARTIN 1981

ALLEN 1981

TURNER 1981

JAMES 1981

WARD 1981

FORD 1981

SMITH 1980

SCOTT 1982

ADAMS 1983

MILLER 1982

문제25. 이름, 입사한 달을 출력하시오

hive> select ename, month(hiredate) from emp;

문제26. 부서번호, 부서번호별 토탈월급을 가로로 출력하시오

오라클에서는

SQL> select sum(decode(deptno,10,sal)) "10"

sum(decode(deptno,20,sal))"20"

sum(decode(deptno,30,sal))"30"

from emp;

※ hive 에서는 case 문을 사용해야 한다

hive> set hive.cli.print.header=true; <--이걸 해줘야 별칭도 출력된다

hive> select sum(case when deptno=10 then sal end) detp10,

> sum(case when deptno=20 then sal end) dept20,

> sum(case when deptno=30 then sal end) dept30

> from emp;

detp10 dept20 dept30

8750 10875 9400

문제27.(오늘의 마지막 문제)

아래의 SQL 을 hive 로 구현하시오

SQL>select job,

count(decode(deptno,10,empno))"dept10"

hive> select job, count(case when deptno=10 then empno end) dept10,

> count(case when deptno=20 then empno end) depe20,

> count(case when deptno=30 then empno end) dept30

> from emp

> group by job;

job dept10 depe20 dept30

ANALYST 0 2 0

CLERK 1 2 1

MANAGER 1 1 1

PRESIDENT 1 0 0

SALESMAN 0 0 4

**sum으로 하는 방법

hive> select job,

> sum(case when deptno=10 then 1 else 0 end) dept10,

> sum(case when deptno=20 then 1 else 0 end) dept20,

> sum(case when deptno=30 then 1 else 0 end) dept30

> from emp

> group by job;

문제28. 사원 테이블에서 월급의 순위가 1등인 사원의 이름과 월급과 순위를 출력하시오

hive> select *

> from ( select ename, sal, dense_rank()over(order by sal desc) rn

> from emp) aaa

> where aaa.rn=1;

KING 5000 1

문제29. 아래의 오라클 SQL 을 hive 로 구현하시오

SQL> select deptno, sum(sal)

from emp

group by rollup(deptno);

hive>select deptno, sum(sal)

from emp

group by deptno with rollup;

NULL 29025

10 8750

20 10875

30 9400

***큐브처럼 나온다. rollup 처럼 나오게 하려면????

hive>select deptno, sum(sal)

from emp

group by deptno with rollup

order by deptno asc; <<<<----이거 안됨. null이 계속 맨 위에 있음. desc 로 하면 맨 아래로 가긴 하나 다른 deptno 가 30부터 출력됨.

결국 rollup 처럼 뽑으려면 unioall 을 써야 한다.

union으로 하면

hive> select *

from ( select deptno, sum(sal)

from emp

group by deptno

union all

select null as deptno, sum(sal)

from emp) aaa;

10 8750

20 10875

30 9400

NULL 29025

※ union all 같은 집합 연산자를 hive 에서 사용할 때는

반드시 from 절의 서브쿼리에 사용해야 한다.

문제30. 아래의 오라클 SQL 을 hive 에서 구현하시오

SQL> select deptno, job, sum(sal)

from emp

group by rollup(deptno, job);

hive> select * from

(select deptno, job, sum(sal)

from emp

group by deptno, job

union all

select deptno, null as job, sum(sal)

from emp

group by deptno

union all

select null as deptno, null as job, sum(sal)

from emp) aaa;

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

10 NULL 8750

20 NULL 10875

30 NULL 9400

NULL NULL 29025

hive> select deptno, job, sum(sal)

from emp

group by deptno, job with rollup;

NULL NULL 29025

10 NULL 8750

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

20 NULL 10875

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

30 NULL 9400

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

문제31. 아래의 오라클 SQL 을 hive 로 구현하시오

SQL> select deptno, sum(sal)

from emp

group by grouping sets(deptno, () );

hive> select deptno, sum(sal)

from emp

group by deptno grouping sets(deptno, () );

hive> select deptno, sum(sal)

from emp

group by deptno grouping sets( (), deptno);

**결과 똑같음

NULL 29025

10 8750

20 10875

30 9400

문제32. 아래의 오라클 SQL 을 hive 로 구현하시오

SQL> select deptno, job, sum(sal)

from emp

group by grouping sets( (deptno) , (job), () );

hive> select deptno, job, sum(sal)

from emp

group by deptno, job grouping sets((deptno),(job),() );

문제33. 사원 테이블의 직업의 종류가 몇 가지 인지 출력하시오

SQL> select count(distinct job) from emp;

hive> select count(distinct job) from emp;

count 로 하면???

SQL>select count(*)

from emp

group by job;

***job 을 기준으로 누구누구가 나오고, 그걸 count 해서 5행 각각값이 나온것.

문제34. 오라클의 with절이 hive 에서도 가능한지 확인하시오

SQL> with q1 as (select job, sum(sal) sumsal

from emp

group by job)

select job, sumsal

from q1

hive> with aaa (select job, sum(sal) sumsal

from emp

group by job)

select job, sumsal

from aaa;

※ hive 에서는 update, delete 는 지원 안 함.

insert 는 append 는 가능하다.

문제35. scott의 월급을 9000으로 변경하시오

hive> update emp

set sal=9000

where ename=SCOTT;

에러난다

문제36. 사원 테이블을 전부 삭제 하시오(delete)

hive> delete from emp;

에러난다

문제37. emp 테이블을 emp_backup 으로 생성하시오

hive>create table emp_backup

select * from emp;

문제38. emp 테이블의 데이터를 emp_backup에 insert 하시오

hive> insert into table emp_backup #오라클은 table 안 붙임

select * from emp;

hive> select count(*) from emp_backup;

28개로 늘어난 걸 볼 수 있다.

문제39. emp_backup 의 내용을 emp 테이블의 내용으로 overwrite 하시오

hive> insert overwrite table emp_backup

select * from emp;

hive> select count(*) from emp_backup;

14개로 줄어들었다. 이게 delete 역할.

728x90

'hadoop' 카테고리의 다른 글

6. PIG설치, 설명 (0)	2019.01.11
5. TAJO설치, 설명 (0)	2019.01.11
3. 하둡 분산 파일 (0)	2019.01.11
2. 하둡설치, hive 설치 (0)	2019.01.11
1. 하둡을 배워야 하는 이유 (0)	2019.01.11

itopia

4. HIVE

'hadoop' 카테고리의 다른 글

티스토리툴바

4. HIVE

'hadoop' 카테고리의 다른 글

'hadoop' Related Articles

티스토리툴바