하둡 / NoSQL 하이브 SQL 사용하기

43 NoSQL 하이브 SQL 사용하기

 

문제249. 직업이 SALESMAN인 사원들의 이름과 직업과 월급을 출력하시오.

hive> select ename, job, sal
    > from emp
    > where job='SALESMAN';

MARTIN  SALESMAN        1250
ALLEN   SALESMAN        1600
TURNER  SALESMAN        1500
WARD    SALESMAN        1250
Time taken: 4.25 seconds, Fetched: 4 row(s)

 

 

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

hive> select job, sum(sal)
    > from emp
    > group by job;

ANALYST 6000
CLERK   4150
MANAGER 8275
PRESIDENT       5000
SALESMAN        5600
Time taken: 12.412 seconds, Fetched: 5 row(s)

 

 

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

hive> select job, sum(sal) as sumsal
    > from emp
    > group by job
    > order by sumsal desc;

MANAGER 8275
ANALYST 6000
SALESMAN        5600
PRESIDENT       5000
CLERK   4150
Time taken: 23.574 seconds, Fetched: 5 row(s)

하이브에서는 order by절 사용할 때 숫자를 쓰게 되면 정렬되지 않습니다.

하이브에서는 group함수의 결과를 정렬할 때 컬럼별칭을 사용해야 합니다.

 

 

문제252. 부서번호, 부서번호별 평균월급을 출력하는데 평균월급을 출력할 때 소수점 이하는 출력되지 않게 반올림해서 출력하고 부서번호별 평균월급이 높은 것부터 출력되게 하시오.

hive> select deptno, round(avg(sal)) as avgsal
    > from emp
    > group by deptno
    > order by avgsal desc;

10      2917.0
20      2175.0
30      1567.0
Time taken: 23.599 seconds, Fetched: 3 row(s)

반올림해도 .0은 출력됩니다.

 

 

ㅇ오라클과 하이브 변환함수의 차이

오라클	하이브
to_char(hiredate,'RRRR')	year(to_date(hiredate))
to_char(hiredate,'MM')	month(to_date(hiredate))
to_char(hiredate,'DD')	day(to_date(hiredate))

 

문제253. 이름, 입사년도(4자리)를 출력하시오.

hive> select ename, year(to_date(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
Time taken: 4.252 seconds, Fetched: 14 row(s)

 

문제254. 입사년도, 입사년도별 토탈월급을 출력하시오.

hive> select year(to_date(hiredate)), sum(sal) as sumsal
    > from emp
    > group by year(to_date(hiredate));

1980    800
1981    22825
1982    4300
1983    1100
Time taken: 12.354 seconds, Fetched: 4 row(s)

 

 

ㅇ회사 데이터베이스 운영 서버 2가지

OLTP 업무용 DB(OnLine Transaction Processing) : 현재 발생하는 주문정보, 고객 데이터

   -> oracle, mariaDB, mysql

DW용 DB(Data Warehouse) : 테라급 데이터로 과거 데이터 존재(과거 저장 내역) 

   -> 1. 데이터 분석함수 

       2. with절

       3. rollup, cube, grouping sets

       같은 함수를 사용해 데이터를 뽑아냄

   -> 데이터 분석가, 데이터 엔지니어

   -> 하둡, 스파크, db2, mariadb

 

 

ㅇHive에서 사용하는 데이터 분석함수

1. rank

2. dense_rank

3. ntile

4. listagg(oracle) -------> concat_ws(hive)

5. 누적 데이터 출력

6. lag, lead

 

 

문제255. 자동화 스크립트를 이용해서 mariaDB에 scott으로 접속하세요.

[root@centos ~]# sh a.sh
1. mariaDB 시작시키기
2. mariaDB root유저로 접속하기
3. mariaDB scott유저로 접속하기
4. jupyter notebook 실행하기(oracle)
5. 파일 검색하기
6. 파일크기 100바이트 이상인 파일들 /root/size100으로 이동시키기
7. mysql workbench 실행하기
8. 하둡 상태 확인하기(oracle)
9. 하둡 시작시키기(oracle)
10. 자동화 스크립트 종료하기
번호를 입력하세요 3
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.24-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use orcl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [orcl]>

 

문제256. (mariaDB) 이름, 월급, 월급의 순위를 출력하시오.

MariaDB [orcl]> select ename, sal, rank() over (order by sal desc) as rnk
    -> from emp;
+--------+------+-----+
| ename  | sal  | rnk |
+--------+------+-----+
| KING   | 9500 |   1 |
| SCOTT  | 3000 |   2 |
| FORD   | 3000 |   2 |
| JONES  | 2975 |   4 |
| BLAKE  | 2850 |   5 |
| CLARK  | 2450 |   6 |
| ALLEN  | 1600 |   7 |
| TURNER | 1500 |   8 |
| MILLER | 1300 |   9 |
| MARTIN | 1250 |  10 |
| WARD   | 1250 |  10 |
| ADAMS  | 1100 |  12 |
| JAMES  |  950 |  13 |
| SMITH  |  800 |  14 |
+--------+------+-----+
14 rows in set (0.003 sec)

 

문제257. 위의 결과에서 순위 1등만 출력하시오.

MariaDB [orcl]> select *
    -> from ( select ename, sal, rank() over (order by sal desc) as rnk from emp)
    -> where rnk = 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where rnk = 1' at line 3

oracle은 서브쿼리 가능하지만 mariaDB와 mysql은 from 절의 서브쿼리를 사용할 수 없습니다.

MariaDB [orcl]> select ename, sal, rank() over (order by sal desc) as rnk
    -> from emp
    -> order by rnk fetch first 1 rows only;

fetch first 기능은 oracle에서만 사용 가능합니다.

 

MariaDB [orcl]> select ename, sal, rank() over (order by sal desc) as rnk from emp limit 1;
+-------+------+-----+
| ename | sal  | rnk |
+-------+------+-----+
| KING  | 9500 |   1 |
+-------+------+-----+
1 row in set (0.001 sec)

mariaDB에서는 limit으로 뽑을수도 있기는 합니다.

 

 

문제258. (mariaDB) 이름, 월급, 사원 테이블 전체의 평균 월급을 출력하시오.

MariaDB [orcl]> select ename, sal, avg(sal) over () as avgsal
    -> from emp;
+--------+------+-----------+
| ename  | sal  | avgsal    |
+--------+------+-----------+
| SMITH  |  800 | 2394.6429 |
| JAMES  |  950 | 2394.6429 |
| ADAMS  | 1100 | 2394.6429 |
| WARD   | 1250 | 2394.6429 |
| MARTIN | 1250 | 2394.6429 |
| MILLER | 1300 | 2394.6429 |
| TURNER | 1500 | 2394.6429 |
| ALLEN  | 1600 | 2394.6429 |
| CLARK  | 2450 | 2394.6429 |
| BLAKE  | 2850 | 2394.6429 |
| JONES  | 2975 | 2394.6429 |
| FORD   | 3000 | 2394.6429 |
| SCOTT  | 3000 | 2394.6429 |
| KING   | 9500 | 2394.6429 |
+--------+------+-----------+
14 rows in set (0.001 sec)

 

문제259. (mariaDB) 부서번호, 이름, 월급, 자기가 속한 부서번호의 평균월급을 출력하시오.

MariaDB [orcl]> select deptno, ename, sal, avg(sal) over (partition by deptno) as avgsal
    -> from emp;
+--------+--------+------+-----------+
| deptno | ename  | sal  | avgsal    |
+--------+--------+------+-----------+
|     10 | MILLER | 1300 | 4416.6667 |
|     10 | KING   | 9500 | 4416.6667 |
|     10 | CLARK  | 2450 | 4416.6667 |
|     20 | SMITH  |  800 | 2175.0000 |
|     20 | JONES  | 2975 | 2175.0000 |
|     20 | SCOTT  | 3000 | 2175.0000 |
|     20 | ADAMS  | 1100 | 2175.0000 |
|     20 | FORD   | 3000 | 2175.0000 |
|     30 | MARTIN | 1250 | 1566.6667 |
|     30 | TURNER | 1500 | 1566.6667 |
|     30 | JAMES  |  950 | 1566.6667 |
|     30 | ALLEN  | 1600 | 1566.6667 |
|     30 | WARD   | 1250 | 1566.6667 |
|     30 | BLAKE  | 2850 | 1566.6667 |
+--------+--------+------+-----------+
14 rows in set (0.000 sec)

 

문제260. 위의 결과를 다시 출력하는데 자기의 월급이 자기가 속한 부서번호의 평균월급보다 더 큰 사원들만 출력하시오.

MariaDB [orcl]> select *
    -> from (select deptno, ename, sal, avg(sal) over (partition by deptno) as avgsal
    -> from emp) aaa
    -> where sal > avgsal;
+--------+-------+------+-----------+
| deptno | ename | sal  | avgsal    |
+--------+-------+------+-----------+
|     10 | KING  | 9500 | 4416.6667 |
|     20 | JONES | 2975 | 2175.0000 |
|     20 | SCOTT | 3000 | 2175.0000 |
|     20 | FORD  | 3000 | 2175.0000 |
|     30 | ALLEN | 1600 | 1566.6667 |
|     30 | BLAKE | 2850 | 1566.6667 |
+--------+-------+------+-----------+
6 rows in set (0.001 sec)

 테이블 별칭을 줘야하지만 사용 가능합니다.

 

 

문제261. 위의 SQL을 hive에서 확인하시오.

hive> select *
    > from (select deptno, ename, sal, avg(sal) over (partition by deptno) as avgsal
    > from emp) aaa
    > where sal > avgsal;

10      KING    5000    2916.6666666666665
20      JONES   2975    2175.0
20      FORD    3000    2175.0
20      SCOTT   3000    2175.0
30      ALLEN   1600    1566.6666666666667
30      BLAKE   2850    1566.6666666666667
Time taken: 12.471 seconds, Fetched: 6 row(s)

 

 

※ hive와 mysql에서 from절 서브쿼리를 사용하려면 테이블 별칭을 줘야 사용가능합니다.

 

 

문제262. (hive) 이름, 월급, ntile 함수를 이용해서 월급을 4등급으로 나눠서 출력하시오.

hive> select ename, sal, ntile(4) over (order by sal desc) as grade
    > from emp;

KING    5000    1
FORD    3000    1
SCOTT   3000    1
JONES   2975    1
BLAKE   2850    2
CLARK   2450    2
ALLEN   1600    2
TURNER  1500    2
MILLER  1300    3
WARD    1250    3
MARTIN  1250    3
ADAMS   1100    4
JAMES   950     4
SMITH   800     4
Time taken: 12.339 seconds, Fetched: 14 row(s)

 

문제263. 위의 결과를 다시 출력하는데 grade가 3등급인 것만 출력하시오.

hive> select *
    > from (
    > select ename, sal, ntile(4) over (order by sal desc) as grade
    > from emp ) aaa
    > where grade = 3;

MILLER  1300    3
WARD    1250    3
MARTIN  1250    3
Time taken: 12.317 seconds, Fetched: 3 row(s)

 

문제264. (오늘의 마지막 문제) 위의 결과를 다시 출력하는데 grade 1등급과 3등급을 출력하시오.

hive> select *
    > from (
    >           select ename, sal, ntile(4) over (order by sal desc) as grade
    >             from emp ) aaa
    > where grade in (1,3) ;

KING    5000    1
FORD    3000    1
SCOTT   3000    1
JONES   2975    1
MILLER  1300    3
WARD    1250    3
MARTIN  1250    3
Time taken: 11.337 seconds, Fetched: 7 row(s)

 

 

+) 하이브에서 테이블 헤더(컬럼명) 보이게 하는 방법

hive> set hive.cli.print.header=true;