R, Python 분석과 프로그래밍의 친구 (by R Friend)

이번 포스팅에서는 R로 Hive에 바로 접속(dbConnect)해서 바로 Hive 내의 데이터를 추출해서 R의 데이터 프레임으로 만드는 방법에 대해서 소개하겠습니다.

dbConnect 를 사용하지 않을 때와 사용할 때를 비교해보면 아무래도 dbConnect를 하는 것이 편합니다. 

그리고 혹시 R로 Hive table의 데이터를 사용해서 자동화하는 프로그램을 짰다고 할 경우 관리 측면에서도 직접 Hive에 연동해서 batch job 실행시키는 것이 관리 측면에서도 용이합니다.

------------------------------------------- 

[ R 따로, Hive 따로 사용할 경우 Hive에서 데이터 추출 및 R에서 사용하기 절차]

  1) Hive 로 데이터 처리/추출하여 text file, 또는 csv file 로 내리기

    => 2) R 에서 read.table() 혹은 read.csv() 함수로 데이터 불러오기

 -------------------------------------------

vs.

------------------------------------------- 

[ R로 Hive에 dbConnect 할 경우 Hive에서 데이터 추출 및 R에서 사용하기 절차 ]

  1) R에서 Hive Query로 직접 데이터 처리하여 R로 바로 data.frame으로 내리기 (끝!)
     (별도로 불러오기 필요 없음!)

-------------------------------------------

대신에 사용환경 설정해주는게 처음에 좀 번거로운게 있습니다. ^^;

한번 고생하고 나중에 두고 두고 편할 거냐, 아니면 당장의 수고스러움을 피하고 조금 불편한거 감수할거냐의 선택인데요, 이왕 이번 글 읽기 시작하셨으니 아래 설명 참고해서 R로 Hive 접근할 수 있는 환경 세팅해보심이 어떨런지요. 

차근차근 설명해보겠습니다.

(1) Java 최신 버전 설치하기

R로 Hive 연동해서 쓸 때 Java를 사용하므로 먼저 Java를 설치해야 합니다.  이미 Java를 설치했다고 하더라도 최신 버전이 아니면 에러가 나더라구요.

Java 설치 확인하는 방법은 cmd 창에서 아래 처럼 'where /R C:\ java.exe' 라고 입력했는데 '정보 : 제공된 패턴에 해당되는 파일을 찾지 못했습니다'라는 메시지가 뜨면 설치가 안되어 있는 겁니다.

설치를 하려면 아래의 링크된 주소로 접속해서 Java SE download 를 합니다.

http://www.oracle.com/technetwork/java/javase/downloads/index.html

아래 그림이 화면에 나오면 Java DOWNLOAD 이미지 클릭하세요.

그러면 아래의 화면이 나오는데요, 각자의 OS, bit 체계 확인해서 본인의 것에 맞는거 다운로드 하시면 됩니다.   (참고로, 저는 window7 OS 64bix 운영체제에 R x64 3.3.1 version 사용하고 있습니다. )

중간에 ( ) Accept License Agreement 에 체크하고 다운로드 받으세요.

[참고 : Windows 운영체제 확인하는 방법]

(방법 1) 제어판 > 시스템  선택

(방법 20) 시작메뉴 오른쪽의) 컴퓨터 > (마우스 오른쪽 누르고) 속성 선택

하면 아래의 화면 나옵니다. 

jdk-8u101-windows-x64.exe 파일을 다운로드 완료하였다면 클릭해서 설치해주세요.

(계속'Next' 누르시면 됩니다)

C:\Program Files\Java\jre1.8.0_101  폴더에 Java가 설치되었을 겁니다.

cmd 창에서 확인해보니 잘 설치가 되었군요.

(2) 메모리 늘리기(increase heap size of rJava) : options(java.parameters = "-Xmx8g")

Java는 virtual machine을 사용하므로, 만약 메모리가 부족하면 'java.lang.OutOfMemoryError: Java heap space' 에러 메시지가 뜹니다. 

R에서는 default memory setting 값이 512 MB 입니다.  Hive에서 512 MB 넘는 파일 추출하기라도 하면 에러납니다.  메모리 문제를 피하기 위해서 아래처럼 options(java.parameters = "-Xmx4g" 라는 명령문으로 메모리를 4G 까지 쓸 수 있도록 설정해보겠습니다.

(만약 컴퓨터의 메모리가 빠방하다면 options(java.parameters = "-Xmx8g" 로 해서 8G 로 설정할 수도 있습니다)

> ##----------------------------
> ## R - Hive connect
> ##----------------------------
> 
> # R: Increase heap size for rJava
> # : assign 4 gigabytes of heap space to the Java environment 
> #   (<-> default setting : 512 MB)
> 
> options(java.parameters = "-Xmx4g" )

(3) rJava, RJDBC 패키지 설치 및 로딩 (rJava, RJDBC RJDBC package installation and loading)

RJDBC가 rJava에 의존적이므로 반드시 library(rJava)를 실행한 후에 library(RJDBC) 순서로 로딩을 해야 합니다.

> # rJava, RJDDB package installation and loading
> install.packages("rJava")
trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.3/rJava_0.9-8.zip'
Content type 'application/zip' length 713501 bytes (696 KB)
downloaded 696 KB

package ‘rJava’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
	C:\Users\Administrator\AppData\Local\Temp\Rtmpc9SiL3\downloaded_packages

> install.packages("RJDBC")
trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.3/RJDBC_0.2-5.zip'
Content type 'application/zip' length 66405 bytes (64 KB)
downloaded 64 KB

package ‘RJDBC’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
	C:\Users\Administrator\AppData\Local\Temp\Rtmpc9SiL3\downloaded_packages

> library(DBI)
> library(rJava)
> library(RJDBC)

(4) R에 Hive Class Path, Dirver, dbConnect 설정

Hive/Hadoop Class Path, dbConnect 설정은 하둡 엔지니어에게 물어서 사용하시기 바랍니다.

(아래는 그냥 예시임...)

(* 주의 : dbConnect 할 때요, Hive 설치된 것이 jdbc:hive인지 jdbc:hive2 인지 확인해서 사용하세요.)

(5) R에서 Hive Query 날리기 : dbGetQuery()

이렇게 R에서 Hive로 Query를 날리면 R에 데이터 프레임(data.frame) 이 생성됩니다.

[참고] hive 2.0.1 설치 및 metastore 설정 =>  http://www.gooper.com/ss/bigdata/271507 

참고로, RPostgreSQL package를 사용해서 R로 PostgreSQL 사용하는 방법도 아래에 소개합니다.  

RMySQL package를 사용해서 R로 MySQL 접속해서 사용하는 방법은 아래와 같습니다. 

위의 RPostgreSQL과 비슷해요.

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요.

--------------------------------
-- HiveQL : View
--------------------------------

-- (Before) 중첩 구조를 가지는 복잡한 구조 (complicated multi-layer structure without VIEW)

FROM (
 SELECT *
  FROM table_1 a JOIN table_2 b ON (a.prd_id = b.prd_id)
  WHERE year = '2016' AND month = '06'
) ab
SELECT ab.prd_id, prd_cnt WHERE ab.price >= 10000;

-- (After) View를 사용해 간소화한 구조 (simplified structure with VIEW (encapsulation))
CREATE VIEW table_view AS
SELECT *
 FROM table_1 a JOIN table_2 b ON (a.prd_id = b.prd_id)
 WHERE year = '2016' AND month = '06';

SELECT prd_id, prd_cnt FROM table_view
 WHERE price >= 10000;

-- 보안을 위해 조건을 기반으로 데이터를 제한하는 view (condition-based view for security)
CREATE TABLE prd_master (prd_id string, prd_nm string, prd_price INT);

CREATE VIEW prd_master_view AS
SELECT prd_id, prd_nm, prd_price FROM prd_master
 WHERE prd_nm = 'ABC';

-- MAP 데이터형과 VIEW

 -- 데이터 형태
ts=1298598378404/code=403/message=bad referrer: bizo.com/...

 -- MAP 테이블 생성
CREATE EXTERNAL TABLE api_logs(d map<string,string>)
  PARTITIONED BY (...)
  ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\004'
    COLLECTION ITEMS TERMINATED BY '\001'
    MAP KEYS TERMINATED BY '\002'
  STORED AS TEXTFILE;

 -- reorder view 생성
CREATE VIEW IF NOT EXISTS api_errors(ts, code, message)
COMMENT 'api error messages'
TBLPROPERTIES ('creator' = 'HongKilDong')
AS SELECT d["ts"], d["code"], d["message"]
    FROM api_logs
    WHERE d["code"] >= 400;

-- VIEW 확인
SHOW TABLES; -- 'SHOW VIEWS;' is NOT WORKING
DESCRIBE api_errors;

-- VIEW 삭제
DROP VIEW IF EXISTS api_errors; 
 

빅데이터 환경에서 사이즈가 어마무시하게 큰 테이블 간 JOIN은 부하가 매우 크므로 되도록이면 데이터 스키마 설계를 할 때 JOIN을 하지 않고도 분석을 할 수 있도록 요건을 반영하곤 합니다. ARRAY, MAP, STRUCT 등의 컬렉션 데이터형이 정규화를 깨더라도 빅데이터 환경에서 사용되는 이유입니다.

하지만, 아무리 스키마 설계를 잘 한다고 해도 분석이 복잡해지면 JOIN을 안하고 모든 것을 처리하기가 힘든 것도 사실입니다.

이번 포스팅에서는 두 개 이상의 테이블을 좌, 우로 서로 연결해서 분석할 때 사용하는 Hive 조인 문으로

 - INNER JOIN

 - LEFT OUTER JOIN

 - RIGHT OUTER JOIN

 - FULL OUER JOIN

 - CROSS JOIN(CARTESIAN PRODUCT JOIN)

 - MAP-SIDE JOIN  과

두 개 이상의 테이블을 상, 하로 서로 붙여서 분석할 때 사용하는

 - UNION ALL

문에 대해서 알아보겠습니다.

기존에 관계형 DB를 사용했던 분이라면 아마 쉽게 이해할 수 있을 내용들입니다.

• 내부 조인 (INNER JOIN)

[쿼리 예시]

SELECT a.var1, a.var2, b.var2
    FROM mytable_1 a JOIN mytable_2 b 
    ON a.var1 = b.var1 -- EQUI-JOIN 만 가능하며, NON-EQUI JOIN은 불가
    WHERE a.prd_cd = 'ABC' AND b.prd_cd = 'DEF';

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2 -- 2 JOIN keys
    WHERE a.prd_cd = 'ABC';

SELECT a.var1, a.var2, b.var3, c.var4
    FROM mytable_1 a JOIN mytable_1 b ON a.var1 = b.var1
                            JOIN mytable_1 c ON a.var1 = c.var1
    WHERE (a.prd_cd = 'ABC' AND b.prd_cd = 'DEF')

              AND (c.prd_cd = 'GHI');

• 왼쪽 세미 조인 (LEFT SEMI-JOIN)

--   : 오른쪽 테이블에서 ON의 술어를 만족하는 레코드를 찾으면 왼쪽 테이블의 레코드를 반환

--   : 내부 조인(INNER JOIN)을 최적화한 특수한 형태로, 내부 조인보다 빠름

--   : 왼쪽 세미 조인만 지원하며, 오른쪽 세미 조인은 미지원

SELECT a.var1, a.var2, a.var3
    FROM mytable_1 a LEFT SEMI JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2;

• 왼쪽 외부 조인 (LEFT OUTER JOIN)

[ 쿼리 예시 ]

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a LEFT OUTER JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2
    WHERE a.prd_cd = 'ABC';

• 오른쪽 외부 조인 (RIGHT OUTER JOIN)

[ 쿼리 예시 ]

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a RIGHT OUTER JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2
    WHERE a.prd_cd = 'ABC';

• 완전 외부 조인 (FULL OUTER JOIN)

[ 쿼리 예시 ]

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a FULL OUTER JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2
    WHERE a.prd_cd = 'ABC';

• 교차 조인 (CROSS JOIN (CARTESIAN PRODUCT JOIN))

[ 쿼리 예시]

SELECT * FROM mytable_1 JOIN mytable_2;

쿼리 문이 JOIN 문 중에서 제일 간단합니다.  그런데 속도는 제일 느리고 또 위험하기도 합니다. ^^;

A*B 개의 ROW를 반환(가령 A 테이블이 100개 row, B 테이블이 1000개 row를 가지고 있다면 A JOIN B 는 100 * 1,000 = 100,000 개 row 출력)하므로 출력량도 제일 많습니다. 뿐만 아니라 다른 JOIN문과 달리 CORSS JOIN (CARTESIAN PRODUCT JOIN)은 병렬처리도 안되므로 느리기까지 합니다.

SELECT 문을 가지고 시스템을 다운시키기가 쉽지 않은데요, SELECT 문으로 혹시 HIVE가 다운되거나 성능이 현격히 저하되었다면 JOIN 걸 때 ON, WHERE 절을 빼먹고 CROSS JOIN (Cartesian Product JOIN)을 실수로 쿼리를 날린게 아닌가 하고 범인 후보로 의심을 해볼 수 있습니다.

CROSS JOIN 걸 때는 '내가 원하는 output/분석 목적이 CROSS JOIN 맞나?'를 재차 확인하고 쿼리를 날리기 바랍니다.  특히 'BIG~ 데이터' 환경에서는 말이지요.

• Map-Side JOIN

[ 쿼리 예시 ]

hive> set hive.auto.convert.join=true; -- default is false

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a JOIN mytable_2 b -- 큰 테이블은 왼쪽, 크기가 작은 테이블은 오른쪽!
    ON a.var1 = b.var1 AND a.var2 = b.var2
    WHERE a.prd_cd = 'ABC';

맵 사이드 조인을 하려면 먼저 set hive.auto.convert.join=true; 로 설정을 바꿔줘야 합니다.

맵 사이드 조인은 이름에서 알 수 있는 것처럼, 크기가 작은 테이블을 메모리에 캐시하고, 큰 테이블은 맵퍼로 보내 후에, 캐시한 작은 테이블로부터 일치하는 모든 것을 찾아낼 수 있기 때문에 맵에서 모든 조인을 할 수 있습니다. 

  => 일반 JOIN 과 달리 리듀스 단계를 건너뛸 수 있어서 속도가 빠른 장점이 있습니다. 성능 최적화를 위해 알아두면 좋은 JOIN 방법입니다.  

이상으로 JOIN 문을 마칩니다.

두 개 이상의 테이블을 합칠 때 사용하는 UNION ALL 입니다.

• UNION ALL

-- 두 개 이상의 테이블을 위, 아래로 합치기
-- subquery 는 같은 수의 칼럼, 각 칼럼의 Data Type 동일해야 함

[ 쿼리 예시 ]

SELECT a.var1, a.var2, a.var3
    FROM (
        SELECT a1.var1, a1.var2, a1.var3, 'A1' as source
        FROM mytable a1
    UNION ALL
        SELECT a2.var1, a2.var2, a2.var3, 'A2' as source
        FROM mytable a2
    ) a
SORT BY a.var1 ASC, a.var2 DESC;

이상으로 HIVE에서 테이블 연결하거나 합쳐서 분석할 때 사용하는 JOIN문, UNION ALL에 대해서 알아보았습니다.

다음번 포스팅에서는 하이브 뷰(VIEW)에 대해서 알아보겠습니다.

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요.

이번 포스팅에서는 HiveQL의 

 - (1) 중첩 SELECT 문

 - (2) LIKE

       : substring 문자열 일치 여부 확인

 - (3) CASE ... WHEN ... THEN ... ELSE ... END

       : 범주형 변수 생성

 - (4) GROUP BY, HAVING 절

       : 집계 (aggregation), 그룹 내 조건절(HAVING)

 - (5) ORDER BY

       : 전체 정렬 (total ordering)

 - (6) SORT BY, DISTRIBUTE BY 

       : 부분 정렬 (local ordering)

에 대해서 알아보겠습니다.

--------------------------------
-- HiveQL : 중첩 SELECT 문
--------------------------------

FROM (
    SELECT var_1, var_2, (var_1*var_2) AS var_1_2_multi 
    FROM my_table
) a
SELECT a.var_1, a.var_2
    WHERE a.var_1_2_multi > 100

    LIMIT 10; -- 반환하는 행 상한치 10개로 제한

---------------------------------------------------
-- HiveQL : LIKE
---------------------------------------------------
-- substring 문자열 일치 여부 확인

SELECT var_1, var_2, var_3
    FROM my_table
    WHERE var_2 LIKE '%CC'; -- var_2에서 'CC'로 끝나는 문자열이 들어있는 row 선택
 
 
SELECT var_1, var_2, var_3
    FROM my_table
    WHERE var_2 LIKE 'AA%'; -- var_2에서 'AA'로 시작하는 문자열이 들어있는 row 선택
 
 
SELECT var_1, var_2, var_3
    FROM my_table
    WHERE var_2 LIKE '%BB%'; -- var_2에서 'BB'를 포함하는 문자열이 들어있는 row 선택

--------------------------------------------------------------
-- HiveQL : CASE ... WHEN ... THEN ... ELSE ... END

--------------------------------------------------------------

-- 범주형 변수 생성

SELECT var_1, var_2, 
    CASE
        WHEN var_3 < 100.0 THEN 'L'
        WHEN var_3 >= 100 AND var_3 < 1000 THEN 'M'
        ELSE 'H'
    END AS var_3_cd
    FROM my_table;

-----------------------------------------
-- HiveQL : GROUP BY, HAVING
-----------------------------------------

-- 집계 (aggregation), 그룹 내 조건절(HAVING)

SELECT prd_cd, max(prd_price) AS prd_max_price
    FROM prd_tr
    WHERE prd_cd = 'ABC'
    GROUP BY prd_cd -- aggregation by group
    HAVING max(prd_price) >= 10000; -- GROUP BY에 의해 생성된 그룹에 대한 SUBQUERY
 

---------------------------------------------------------
-- HiveQL : ORDER BY
---------------------------------------------------------

-- => 쿼리 결과 집합에 대한 전체 정렬(TOTAL ORDERING), 오랜 시간 소요

SELECT prd_cd, prd_id, sum(prd_rev) AS prd_rev_sum
    FROM prd_tr
    WHERE prd_cd IN ('ABC', 'DEF', 'GHI')
    GROUP BY prd_cd, prd_id
    ORDER BY prd_cd ASC, prd_id DESC; -- total ordering, ASC 오름차순, DESC 내림차순

---------------------------------------------------
-- HiveQL : SORT BY, DISTRIBUTE BY
---------------------------------------------------

-- => 각 REDUCER에서 데이터 정렬(LOCAL ORDERING), 상대적으로 빠름

SELECT a.prd_cd, a.prd_id, sum(a.prd_price) AS prd_rev_sum
    FROM prd_tr a
    WHERE prd_cd IN ('ABC', 'DEF', 'GHI')
    DISTRIBUTE BY a.prd_cd -- 같은 prd_cd 를 가진 데이터를 같은 reducer로 보냄
    GROUP BY a.prd_cd, a.prd_id
    SORT BY a.prd_cd ASC, a.prd_id DESC; -- local ordering

다음번 포스팅에서는 테이블 JOIN에 대해서 알아보겠습니다.

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요.

이번 포스팅에서는 Hive에 내장되어 있는

 - (1) 내장함수(Hive built-in functions)

 - (2) 수학함수(Hive arithmetic functions)

 - (3) 집계함수(Hive aggregate functions)

에 대해서 알아보겠습니다.

기본 SELECT ... FROM ... 에 더해서 지난번 포스팅에서 다루었던 연산자와 함께 이번 포스팅에서 다룰 다양한 함수를 잘 알고 있으면 "Hive 좀 하네" 소리 들을 수 있을 겁니다. 

다행스러운 것은 SQL에서 사용하는 함수와 매우 비슷하다는 점입니다. ( <= 이점이 Hive 진입 장벽을 낮추어서 Hive가 많이 사용되는 이유이지요)

자주 쓰는 함수 외에는 다 외우기 힘들 수 있으므로, 필요할 때마다 검색해서 사용하시기 바랍니다.

먼저 다양한 내장 함수 먼저 살펴보겠습니다.

(1) 내장 함수 (Hive Built-in functions)

(2) 수학 함수 (Hive Arithmetic functions)

아래의 수학 함수들의 full name을 알고 있는 분들이라면 축약어 형식의 함수이름을 보고 '아, 이거 뭐겠구나...'하고 짐작할 수 있을 것입니다.

(3) 집계 함수 (Hive Aggregate functions)

특정 열을 기준으로 그룹 별로 행의 값들을 요약, 집계해서 하나의 값으로 반환해주는 함수가 집계 함수입니다.  GRUOP BY 와 함께 쓰여서

hive > SET hive.map.aggr = true; -- 집계 처리 성능 향상

SELECT count(DISTINCT var_1) as var_1_dist_cnt, avg(var_2) as var_2_avg 

FROM my_table 

GROUP BY var_group

형식으로 아주 아주 아주 많이 사용하는 함수들입니다.  R 학습할 때 초반부에 기초 요약통계량에서 다루는 함수들에 해당합니다.

(4) 테이블 생성 함수 (Hive table creation functions)

[예제]

SELECT parse_rul_tuple(url, 'HOST', 'PATH', 'QUERY') as (host, path, query)

FROM url_table;

[Reference]

1) Programing Hive, Edward Capriolo, Dean Wampler, Jason Rutherglen, O'REILLY

2) http://www.tutorialspoint.com/hive/hive_built_in_functions.htm

다음 포스팅에서는 중첩 SELECT 문, LIKE, CASE... WHEN ... THEN 문, GROUP BY, HAVING절, ORDER BY, SORT BY, DISTRIBUTE BY에 대해서 알아보겠습니다.

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요. ^^

이번 포스팅에서는 Hive 에 내장되어 있는

 - (1) 프로젝션 연산자 SELECT ... FROM 

 - (2) 비교 연산자 (Relational operators)

 - (3) 산술 연산자 (Arithmetic operators)

 - (4) 논리 연산자 (Logical operators)

 - (5) 복합 연산자 (Complex operators)

에 대해서 알아보겠습니다.

[ HiveQL Operators : 프로젝션 연산자, 비교 연산자, 산술 연산자, 논리 연산자, 복합 연산자 ]

(1) 프로젝션 연산자 SELECT ... FROM

아래와 같은 테이블을 생성했다고 했을 때,

CREATE TABLE IF NOT EXISTS mydb.my_table (
    var_1 STRING COMMENT 'var_1 is this',
    var_2 FLOAT COMMENT 'var_2 is that',
    var_3 INT COMMENT 'var_3 is those',
    var_4 ARRAY<STRING> COMMENT 'arrary is...',
    var_5 MAP<STRING, FLOAT> COMMENT 'map is...',
    var_6 STRUCT<aa:STRING, bb:FLOAT, cc:INT> COMMENT 'structure is...'
COMMENT 'my_table in mydb'
TBLPROPERTIES ('creator'='HongKilDong', 'data'='2016-06-17', ...)
--LOCATION '/user/hive/warehouse/mydb.db/my_table'
)
PARTITIONED BY (year INT, month INT, day INT);

-- SELECT var_nm FROM table

SELECT var_1, var_2 FROM my_table;

-- ARRAY : ["aaaaaaa", "bbbbbbbb"]

SELECT var_4 FROM my_table;

-- MAP : {"key1":xx, "key2":yy}

SELECT var_5 FROM my_table;

-- STRUCT : JSON map {"aa":"xxx", "bb":yy.y, "cc":zzz}
SELECT var_6 FROM my_table;

(2) HIVE 비교 연산자 (Relational Operators)

-- Retrieve details from DB using relational operators

SELECT * FROM my_table WHERE var_1='HongKilDong';
SELECT * FROM my_table WHERE var_3>=100;
SELECT * FROM my_table WHERE BETWEEN var_2 >= 0.1 and var_2 <= 2.0;
SELECT * FROM my_table WHERE var_1 IS NOT NULL;

(3) HIVE 산술 연산자 (Arithmetic Operators)

-- 산술 연산자

SELECT (var_1 + var_2) as var_1_2_sum FROM my_table;

SELECT var_1*var_2 as var_1_2_multi FROM my_table;

(4) HIVE 논리 연산자 (Logical Operators)

-- 논리 연산자

SELECT * FROM my_table WHERE var_3>=100 && var_2 <= 2.0;

(5) HIVE 복합 연산자 (Complex Operators)

-- subset selection from ARRAY : [index number]

SELECT var_1, var_4[0] FROM my_table; -- index starts from '0' (not from '1')

-- subset selection from MAP : ["key"]

SELECT var_1, var_5["key1"] FROM my_table;

-- subset selection from STRUCT : dot notation

SELECT var_1, var_6.aa FROM my_table;

다음 포스팅에서는 Hive에 내장되어 있는 수학 함수와 집계함수에 대해서 알아보겠습니다.

[Reference]

1) Programing Hive, Edward Capriolo, Dean Wampler, Jason Rutherglen, O'REILLY

2) http://www.tutorialspoint.com/hive/hive_built_in_operators.htm

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요.

이번 포스팅에서는

 - (1) 테이블에 데이터를 넣기 (data loading into table)

 - (2) 쿼리 결과를 테이블에 넣기 (data loading from query result)

 - (3) 동적으로 파티션 해서 데이터 넣기

 - (4) 테이블 생성 & 데이터 로딩 한번에 하기

 - (5) 데이터를 Local로 내보내기

 - (6) 데이터를 동시에 다수의 디렉터리로 로딩하기

등에 사용하는 Hive 데이터 조작 언어 (HiveQL Data Manipulation Language)에 대해서 알아보겠습니다.

-- 데이터 로딩 (Data loading into managed table)
LOAD DATA LOCAL INPATH '${env:HOME}/2017-06'
OVERWRITE INTO TABLE my_table
PARTITION (year = '2016', month = '6');

-- 쿼리 결과로 부터 데이터 로딩 (inserting data from query result)

    -- INTO : adding data
INSERT INTO TABLE my_table
PARTITION (year = '2016', month = '6')
SELECT * FROM another_table a
WHERE a.year = '2016' AND a.month = '6';

    -- OVERWRITE : replacing data
INSERT OVERWRITE TABLE my_table
PARTITION (year = '2016', month = '6')
SELECT * FROM another_table a
WHERE a.year = '2016' AND a.month = '6';

    -- reading data once and replacing data at several tables
FROM another_table a
INSERT OVERWRITE TABLE my_table
    PARTITION (year = '2016', month = '6')
    SELECT * WHERE a.year = '2016' and a.month = '6'
INSERT OVERWRITE TABLE my_table
    PARTITION (year = '2016', month = '7')
    SELECT * WHERE a.year = '2016' and a.month = '7'
INSERT OVERWRITE TABLE my_table
    PARTITION (year = '2016', month = '8')
    SELECT * WHERE a.year = '2016' and a.month = '8';

    -- dynamic partition insert
set hive.exec.dynamic.partition = true; -- to use dynamic partition
set hive.exec.dynamic.partition.mode = nonstrict; -- for all partition column
set hive.exec.max.dynamic.partition.pernode = 500; -- maximum file number

INSERT OVERWRITE TABLE my_table
PARTITION (year, month)
SELECT ..., a.year, a.month
FROM another_table a;

    -- partial dynamic partition insert
INSERT OVERWRITE TABLE my_table
PARTITION (year = '2016', month) -- static partition key first, dynamic partition key second
SELECT ..., a.year, a.month
FROM another_table
WHERE a.year = '2016'

-- 테이블 생성 & 데이터 로딩 한번에 하기 (** 분석 MART 구성 시 유용 **)
CREATE TABLE my_table_201606  -- table creation
AS SELECT var_1, var_2, var_5 -- and data selection simultaneously
FROM my_table
WHERE year = '2016' and month = '6';

-- 데이터를 Local로 내보내기

 -- (1) 데이터 파일 복사
hadoop fs -cp source_path target_path -- copy
hive> !ls /tmp/my_table_201606; -- file check
hive> !cat /tmp/my_table_201606/000000_0; -- data check

 -- (2) INSERT OVERWRITE LOCAL DIRECTORY ...
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/my_table_201606'
SELECT var_1, var_2, var_5
FROM my_table
WHERE year = '2016' and month = '6';

-- 데이터를 동시에 다수의 디렉터리로 로딩
FROM my_table a
INSERT OVERWRITE DIRECTORY '/tmp/my_table_201606'
     SELECT * WHERE year = '2016' and month = '6'
INSERT OVERWRITE DIRECTORY '/tmp/my_table_201607'
     SELECT * WHERE year = '2016' and month ='7'
INSERT OVERWRITE DIRECTORY '/tmp/my_table_201608'
     SELECT * WHERE year = '2016' and month = '8';

 [Reference]

- Programing Hive, Edward Capriolo, Dean Wampler, Jason Rutherglen, O'REILLY

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요.

대용량의 빅데이터를 분석해야 할 때 single machie 메모리 기반의 R은 고전을 하게 됩니다. 메모리 full 나서 아예 데이터를 load를 못하거나, 겨우 load를 했어도 분산병렬처리가 안되어서 연산 시간이 매우 오래걸리거나... 분석가 애를 태우는 경우가 있습니다. 

특히 개인용 노트북이나 PC쓰는 분들이라면 메모리 추가하는 것 만으로는 대용량 데이터 문제해결하는게 한계가 있기 마련이며, 샘플링해서 분석하는거 말고 전수의 대용량 데이터를 가지고 전처리하거나 분석해야할 필요가 생길 수 있습니다.

이럴 때 HDFS(Hadoop Distributed File System)에 데이터를 적재하고 MapReduce로 분산병렬처리를 하게 됩니다(batch 분석의 경우). 

Hadoop MapReduce가 Java 로 되어있다보니 Java를 모르는 분석가, 개발자라면 MapReduce 배우기가 녹록하지 않습니다.  업무 생산성이 확 떨어지는거죠. 

Hadoop 나오기 전에 이미 전산실, IT부서 (그리고 일부 마케터, CRM담당자...)에 보면 관계형DB, DW에 구조화된 데이터로 저장해놓고 SQL(Structured Query Language)를 사용해서 데이터를 저장, 관리, 분석할 수 있는 분들이 많이 계시지요.  Java는 모르지만 SQL은 잘 아는 개발자, 분석가를 위해 Hadoop MapReduce를 쓸 수 있도록 Facebook이 처음 개발하고 나중에 Apache Open Source 프로젝트로 더욱 발전한 툴이 바로 HIVE 입니다.

Hive를 쓸 줄 알면 대용량 데이터 전처리하는데 아주 요긴합니다.  SQL을 알고 있으면 배우기도 쉽기에 기존 DW를 Big Data로 porting할 때 Hive를 사용하면 생산성이 확 올라갑니다(Java로 코딩하는 것 대비... 생산성을 올라가고, 속도는 좀 떨어지고요).

방명록에 대용량 데이터 전처리에 대한 질문이 최근에 있었고, 답변하는 과정에서 Hive에 대한 언급을 하기도 했습니다.  겸사겸사 (이 블로그가 R을 주제로 하긴 하지만...) Hive 사용법에 대해서도 간략히 HQL(Hive Query Language) 예시를 들면서 몇 편에 나누어서 정리하겠습니다. 

--------------------------------------------------------------------
-- HIVE : 데이터 정의 언어(DATA DEFINITION LANGUAGE)
--------------------------------------------------------------------

Hive 설치, 설정 방법은 과감히 생략하겠으며, 먼저 Hive에서 데이터베이스, 테이블, 뷰, 함수, 색인을 생성하고 변경하고 삭제하는데 사용하는 데이터 정의 언어(data definition language) 부터 시작하겠습니다.

------------------
-- DATABASE
------------------

-- 데이터베이스 나열 (listing databases)
SHOW DATABASES;
SHOW DATABASES LIKE 'a.*'; -- show any database, starting with 'a' character