이번 포스팅에서는 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 설정은 하둡 엔지니어에게 물어서 사용하시기 바랍니다.

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

 

# setting class path

hive.class.path = list.files(path=c("/usr/lib/hive/lib"), pattern="jar", full.names=T);
hadoop.lib.path = list.files(path=c("/usr/lib/hadoop/lib"), pattern="jar", full.names=T);
hadoop.class.path = list.files(path=c("/usr/lib/hadoop"), pattern="jar", full.names=T); 
class.path = c(hive.class.path, hadoop.lib.path, hadoop.class.path);
.jinit(classpath = class.path)

 

# setting driver

drv <- JDBC("org.apache.hive.jdbc.HiveDriver")

 

# setting dbConnect : driver, ip, port, userID, password

conn <- dbConnect(drv, "jdbc:hive2://xxx/xxx", "userID", "password")

 

 

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

 

 

 

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

 

 

# Hive Query

db_qry <- dbGetQuery(conn,
      "SELECT *
          FROM db.table
          WHERE ymd = '2016-09-03'
          LIMIT 100;")

 

db_qry # query result

 

 

 

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

 

 

 

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

 

 


 

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

 

 

# installation and loading RPostgreSQL package

install.packages("RPostgreSQL")
library(RPostgreSQL)

 

# setting Driver : dbDriver()

drv <- dbDriver("PostgreSQL")

 

# setting dbConnect
con <- dbConnect(drv, dbname = "dbname",
                               host = "xx.xx.xxx.xx",
                               port = xxxx,
                               user = "userID",
                               password = "password")

 

# DB SQL Query

r_sql <- c("SELECT var1, sum(var2) as sum_var2
    FROM db.table
    WHERE var3 = 'xxx' AND var4 = 'xxx'
    GROUP BY var1
    ORDER BY var1;")

 

sql_result <- dbGetQuery(con, r_sql)

 

sql_result # query result

 

dbDisconnect(conn = con)

 

 

 


 

 

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

위의 RPostgreSQL과 비슷해요.

 

 

# install and loading RMySQL package

install.packages("RMySQL")
library(RMySQL)

 

# setting dbDriver, dbConnect()

drv <- dbDriver("MySQL")

con <- dbConnect(drv,
                 dbname = "dbname",
     user = "userID",
     host = "xx.xx.xxx.xx",
     port = xxxx,
     password = "password")

 

# MySQL query : dbGetQuery

r_sql <- c("SELECT var1, sum(var2) as sum_var2
    FROM db.table
    WHERE var3 = 'xxx' AND var4 = 'xxx'
    GROUP BY var1
    ORDER BY var1;")
 
test.table <- dbGetQuery(con, r_sql)

 

# disconnect MySQL DB : dbDisconnect()

dbDisconnect(conn = con)

 

 

 

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

 

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by R Friend R_Friend

 

 

 

 

--------------------------------
-- 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&lt;string,string&gt;)
  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; 
 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by R Friend R_Friend

빅데이터 환경에서 사이즈가 어마무시하게 큰 테이블 간 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)에 대해서 알아보겠습니다.

 

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

 

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by R Friend R_Friend

이번 포스팅에서는 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에 대해서 알아보겠습니다.

 

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

 

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by R Friend R_Friend

이번 포스팅에서는 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)

 

반환 데이터형

내장 함수 

설명 

 BIGINT

round(double a)

double 데이터형 a의 반올림한 값을

BIGINT 데이터형으로 반환

예) round(4.8) = 5

 DOUBLE

round(double a, N) 

double 데이터형 a의 N개 소수점 이하의 값까지를

반올림하여 double 데이터형으로 반환

예) round(4.816, 2) = 4.82

 DOUBLE

floor(double a)

double 데이터형 a보다 같거나 작은 값 중

가장 큰 BIGINT 값을 반환

예) floor(4.8) = 4

 BIGINT

ceil(double a)

double 데이터형 a보다 크거나 같은 값 중

가장 작은 BIGINT 값을 반환

예) ceil(4.8) = 5

 DOUBLE

rand(),

rand(int seed)

행에서 행으로 변하는 무작위 숫자를 반환

 string

concat(string A,

string B, ...)

string A와 string B를 연속으로 붙여서 반환

 string

concat_ws(seperator, string A, string B, ...) 

string A와 string B를 구분자(seperator)를 두어서

연속으로 붙여서 반환

 string

substr(string A,

int start)

string A를 시작 색인 지점부터 끝까지 잘라서 반환.

* 색인은 1부터 시작함.

예) substr('20160625', 5) = '0625'

 string

substr(string A,

int start, int length)

string A를 시작 색인 지점부터 int length만큼 잘라서 반환.

* 색인은 1부터 시작함.

예) substr('20160625', 5, 2) = '06'

 ARRAY

<string>

split(string A, pattern)

pattern이 나타날 때마다 string A를 분할하여

문자열의 배열을 반환.

 string

repeat(string A, n) 

 string A를 n회 반복하여 반환.

 string

upper(string A)

or ucase(string A)

string A의 모든 문자를 대문자로 반환.

예) upper('Love') = 'LOVE'

 string

lower(string A)

or lcase(string A)

string A의 모든 문자를 소문자로 반환.

예) lower('Love') = 'love'

 string

trim(string A)

string A의 양끝의 공백을 제거하여 반환.

예) trim('  Love  ') = 'Love'

 string

ltrim(string A)

string A의 왼쪽 시작부분의 공백을 제거하여 반환.

예) ltrim('  Love  ') = 'Love  '

 string

rtrim(string A)

string A의 오른쪽 끝부분의 공백을 제거하여 반환.

예) rtrim('  Love  ') = Love'

 string space(n) 

n개의 공백을 반환.

 string

regexp_replace(string A, string B, string C)

string A에서 string B의 부분 문자열(substring)과 일치하는

Java 정규표현식을 string C로 교체하여 반환.

예) regexp_replace('Love', 'oe', 'i') => returns 'Livi'

 int length(string A) 

string A의 길이를 반환.

예) length('Love') = 4

 int

size(Map<K.V>)

Map<K.V> 의 요소 개수를 반환.

 int

size(Array<T>)

Array<T> 의 요소 개수를 반환.

value of <type>

cast(<expr> as <type>)

표현 <expr>을 <type>으로 형변환하며,

변환에 실패하면 NULL을 반환함.

예) cast('1' as BIGINT) => 문자열 '1'을 BIGINT로 형변환

 string

from_unixtime(int unixtime)

Unix 기준시간(1970-01-01 00:00:00 UTC)을

=> 현재 시스템 시간대의 초단위 시간 표현식 포맷

     ("1970-01-01 00:00:00")으로 변환.

 string

to_date(string timestamp)

timestamp 문자열에서 년/월/날짜를 반환.

예) to_date("1970-01-01 00:00:00") = "1970-01-01"

 int

year(string date)

timestamp 문자열에서 년(year)을 반환.

예) year("1970-01-01 00:00:00") = 1970,

     year("1970-01-01") = 1970

 int

month(string date)

timestamp 문자열에서 월(month)을 반환.

예) month("1970-11-01 00:00:00") = 11,

     month("1970-11-01") = 11

 int

day(string date)

timestamp 문자열에서 일(day)을 반환.

예) day("1970-11-01 00:00:00") = 1,

     day("1970-11-01") = 1

 string

get_json_object(string json_string, string path)

json string으로부터 json 객체를 추출하며,

추출한 json 객체로부터 json string을 반환함.

json string이 유효하지 않으면 NULL을 반환함.

 MAP<string, string>

str_to_map(string A, delim1, delim2)

delim1을 Key-Value 쌍의 구분자로 사용하고 

delim2를 Key와 Value의 구분자로 사용하여

string A를 parsing 한 후에 Map을 생성함.

 ARRAY<ARRAY

<string>>

sentences(string A, lang, locale)

string A를 단어의 배열로 이루어진 문장의 배열로 반환.

lang, locale 옵션을 설정하지 않을 경우 기본 locale 사용.

 BOOLEAN

in_file(string A,

file B) 

filen B에 string A가 들어 있으면 TRUE를 반환.

 

 

 

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

 

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

 

 반환 데이터형

수학함수

설명 

 DOUBLE

exp(double A) 

 double A의 지수(exponential) 함수값을 반환

 DOUBLE

ln(double A) 

 double A의 자연로그(natural logarithm)값을 반환

* 자연로그 : e로 밑으로 하는 로그 (e=2.71828182846)

 DOUBLE

log10(double A) 

 double A의 상용로그(common logarithm) 값을 반환

* 상용로그 : 10을 밑으로 하는 로그

 DOUBLE

log2(double A) 

 double A의 밑을 2로 하는 로그값을 반환

 DOUBLE

log(base, double A) 

 double A의 밑을 base로 하는 로그값을 반환

(base는 double 데이터형)

 DOUBLE

pow(double A, p)

or

power(double A, p)

 double A의 p 거듭제곱(power)한 값을 반환

(p는 double 데이터형)

 DOUBLE

sqrt(double A)

 double A의 제곱근(square root)을 반환

 string

bin(BIGINT i)

 BIGINT i 의 이진수(binary)값을 문자열로 반환

 string

hex(BIGINT i) 

 BIGINT i 의 16진수(hexadecimal)값을 문자열로 반환

 string

hex(string S) 

 string S의 각 문자를 16진수값으로 변환하여 문자열로 반환

 string unhex(i) 

 hex(string S) 함수와 반대로 동작

 string

conv(BIGINT i, from_base, to_base)

 BIGINT i 를 from_base 진수에서 to_base 진수로 변환하여

문자열로 반환

(from_base와 to_base는 정수형)

 string

conv(string S, from_base, to_base) 

 string S 를 from_base 진수에서 to_base 진수로 변환하여

문자열로 반환

(from_base와 to_base는 정수형)

 DOUBLE

abs(double A

 double A의 절대값(absolute value)을

DOUBLE 데이터형으로 반환

 INT

pmod(INT 1, INT 2) 

 INT 1을 INT 2로 모듈러 연산(modular arithmetic)한 후

양수(positive) INT 값을 반환 (음이 아닌 나머지를 반환)

 DOUBLE pmod(double 1, double 2

 double 1을 double 2로 모듈러 연산한 후

양수 DOUBLE 값을 반환

 DOUBLE

sin(double A) 

 double A의 sine 값을

DOUBLE 데이터형의 radian 값으로 반환

 

* 1 radian : 원둘레 위에서 반지름의 길이(r)와 같은 길이를 가지는 호(s)에 대응하는 중심각의 크기, 약 57.2958˚)

 DOUBLE

asin(double A) 

 double A의 arc-sine 값을

DOUBLE 데이터형의 radian 값으로 반환

 

* arc-sine : sine 함수의 역함수(inverse trigonometric function)

 DOUBLE cos(double A) 

 double A의 cosine 값을

DOUBLE 데이터형의 radian 값으로 반환

 DOUBLE acos(double A) 

 double A의 arc-cosine 값을

DOUBLE 데이터형의 radian 값으로 반환

 DOUBLE

tan(double A) 

 double A의 tangent 값을

DOUBLE 데이터형의 radian 값으로 반환

 DOUBLE atan(double A) 

 double A의 arc-tangent 값을

DOUBLE 데이터형의 radian 값으로 반환

 DOUBLE

degrees(double A) 

 double A radian 값을 각도로 변환하여 반환

 DOUBLE radians(double A) 

 double A 각도 값을 radian 값으로 변환하여 반환

 INT positive(INT i) 

 INT i의 양의 INT 데이터형 값을 반환

 INT

negative(INT i) 

 INT i의 음의 INT 데이터형 값을 반환

 DOUBLE positive(double A) 

 double A의 양의 DOUBLE 데이터형 값을 반환

 DOUBLE negative(double A)   double A의 음의 DOUBLE 데이터형 값을 반환

 FLOAT

sign(double A) 

 double A가 양수이면 부동소수점형 1.0을 반환,

                음수이면 부동소수점형 -1.0을 반환,

                모두 아니면 부동소수점형 0.0을 반환

 DOUBLE e() 

 지수형 상수값 2.718281828459045를

DOUBLE 데이터형으로 반환

 DOUBLE pi 

 파이 상수값 3.141592653589793을

DOUBLE 데이터형으로 반환

 

 

 

 

(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 학습할 때 초반부에 기초 요약통계량에서 다루는 함수들에 해당합니다.

 

 반환 데이터형

집계 함수

설명 

 BIGINT

count(*)

NULL값을 포함한 행의 총 개수를 반환

 BIGINT

count(expr)

주어진 표현식이 NULL이 아닌 행의 개수를 반환

 BIGINT

count(DISTINCT expr[, expr_.]) 

주어진 표현식의 값이 유일하면서 NULL이 아닌

행의 개수를 반환

 DOUBLE

sum(col)

그룹 내 요소들의 합을 반환

 

 DOUBLE

sum(DISTINCT col)

그룹 내 열의 고유한 값들의 합을 반환

 DOUBLE

avg(col)

그룹 내 요소들의 평균을 반환

 DOUBLE avg(DISTINCT col)

그룹 내 열의 고유한 값들의 평균을 반환

 DOUBLE

min(col)

그룹 내 열의 최소값을 반환

 DOUBLE

max(col)

그룹 내 열의 최대값을 반환

 DOUBLE

variance(col)

or var_pop(col)

특정 열의 모집단 분산을 반환

 DOUBLE

var_samp(col)

특정 열의 표본 분산을 반환

 DOUBLE

stddev_pop(col)

특정 열의 모집단 표준편차를 반환

 DOUBLE

stddev_samp(col)

특정 열의 표본 표준편차를 반환

 DOUBLE

covar_pop(col1, col2)

두개 열(col1, col2)의 공분산을 반환

 DOUBLE

covar_samp(col1, col2)

두개 열(col1, col2)의 표본 공분산을 반환

 DOUBLE

corr(col1, col2)

두개 열(col1, col2)의 상관계수를 반환

 DOUBLE

percentile(int_expr, p)

p percent에 해당하는 int_expr 백분위수 값을 반환.

p는 0과 1사이의 DOUBLE 데이터형 값임.  

예) p가 0.25이면 전체 값을 크기 순으로 오름차순 정렬하였을 때 25%에 해당하는 값을 반환

 

참고) 백분위수(percentile)는 총값의 백분율이 해당 값보다

        작거나 같은 측정치임

 ARRAY(DOUBLE)

percentile(int_expr, [p1, ...])

p1, p2, ... percent에 해당하는 int_expr 백분위수 값을

ARRAY 데이터형으로 반환함

 DOUBLE

percentile_approx(int_expr, p, NB)

p percent에 해당하는 int_expr 의 근사 백분위수 값을 반환.

NB는 추정을 위한 Histogram bins 의 수임

(NB default 값 : 10,000)

 DOUBLE

percentile_approx

(int_expr, [p1, ...], NB) 

p1, p2, ... percent에 해당하는 int_expr 의

근사 백분위수 값을 반환

 

 

 

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

 

 반환 데이터형

테이블 생성 함수 

설명 

 N개의 row

explode(ARRAY)

 ARRAY 의 각 요소별로 한 개의 행(row)을 만들며,

0개 이상의 row를 반환

 N개의 row

explode(MAP)

 MAP의 Key와 Value에 대해 각각 하나의 열(field)을 생성.

각 Key-Value 쌍에 대해 하나의 행(row)이 생성되며,

0개 이상의 row를 반환

 TUPLE

json_tuple(jsonStr,
p1, p2, ..., pn)

 get_json_object 와 같은 기능,

반면 여러 이름을 받아들이고 하나의 tuple을 반환.

(모든 입력 인자와 출력 행의 데이터형은 문자열)

 TUPLE

N>=1 일 때 parse_url_tuple(url, part name1,

part name2, ..,

part nameN)

 한 URL로부터 N개의 part를 추출하고,

1, 2, ..., N개의 part 이름을 부여함.

 

(모든 입력 인자와 출력 행의 데이터형은 문자열.

유효한 part 이름은 대소문자를 구분, 공백은 하나만 포함)

 N개의 row

stack(n, col1, ..., colM)

 M개의 행을 N개의 열로 반환.

각 행은 M/N의 크기를 가짐.

 

[예제]

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에 대해서 알아보겠습니다.

 

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

 

 

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by R Friend R_Friend

 

 

이번 포스팅에서는 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)

 

연산자

데이터형

설명 

 A = B

all primitive types

 A와  B가 같으면 TRUE, 그렇지 않으면 FALSE

A != B

all primitive types

 A와 B가 같지 않으면 TRUE, 그렇지 않으면 FALSE

A < B

all primitive types

 A가 B보다 작으면 TRUE, 그렇지 않으면 FALSE

A <= B

all primitive types

 A가 B보다 작거나 같으면 TRUE, 그렇지 않으면 FALSE

A > B

all primitive types

 A가 B보다 크면 TRUE, 그렇지 않으면 FALSE

A >= B

all primitive types

 A가 B보다 크거나 같은면 TRUE, 그렇지 않으면 FALSE

A IS NULL

all types

 A가 NULL 이면 TRUE, 그렇지 않으면 FALSE

A IS NOT NULL

all types

 A가 NULL이 아니면 TRUE, 그렇지 않으면 FALSE

A LIKE B

Strings

 String 패턴 A가 B와 동일하면 TRUE, 그렇지 않으면 FALSE

A RLIKE B

(A REGEXP B)

Strings

 A 또는 B가 NULL이면 NULL,

 A의 어떤 substring이라도 B와 동일하면 TRUE,

 그렇지 않으면 FALSE

 

 

 

-- 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)

 

 연산자

데이터형 

설명 

 A + B

all number types

 더하기 (adding A and B)

 A - B

all number types 

 빼기 (substracting B from A)

 A*B

all number types

 곱하기 (multiplying A and B)

 A/B

all number types

 나누기 (dividing B from A)

 A%B

all number types

 나눈 후의 나머지 반환

 (reminder resulting from diving A by B)

A&B 

all number types

 비트 AND 연산 (bitwise AND of A and B)

A|B 

all number types

 비트 OR 연산 (bitwise OR of A and B)

A^B 

all number types

 비트 XOR 연산 (bitwise XOR of A and B)

~A 

all number types

 비트 NOT 연산 (bitwise NOT of A)

 

 

 

-- 산술 연산자

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)

 

 연산자

데이터형

설명 

 A AND B

(A && B)

boolean 

 A와 B 모두 TRUE이면 TRUE, 그렇지 않으면 FALSE

 A OR B

(A || B)

boolean

 A 또는 B, 또는 A와 B가 모두 TRUE이면 TRUE,

 그렇지 않으면 FALSE

 NOT A

(!A)

boolean

 A가 FALSE이면 TRUE, 그렇지 않으면 FALSE

 

 

-- 논리 연산자

 

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

 

 

 

 

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

 

 연산자

데이터형

설명 

 A[n]

A : Array

n : int 

 Array A에서 n번째 값을 출력.

 첫번째 값은 index 0 부터 시작함.

 (* 참고 : R은 index 1 부터 시작함)

 M[key]

 M : Map<K, V>

Key has type K

 Key에 해당하는 Value를 출력함

 S.x

S : STRUCT

 STRUCT의 x field를 출력함

 

 


-- 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

 

 

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

 

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by R Friend R_Friend

이번 포스팅에서는

 

 - (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

 

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

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by R Friend R_Friend

대용량의 빅데이터를 분석해야 할 때 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


-- 데이터베이스 생성 (creating database)
CREATE DATABASE my_database;

CREATE DATABASE IF NOT EXISTS my_database_2;

CREATE DATABASE my_database_3
LOCATION '/another/directory'; -- changing directory


-- 테이블 삭제 (dropping table)
DROP TABLE IF EXISTS my_table;
DROP TABLE IF EXISTS my_table CASCADE; -- if there are tables in database


-- 데이터베이스 설명 추가, 설명 보기
CREATE DATABASE my_database_3
COMMENT 'this is my 3rd database'; -- adding comment for description

DESCRIBE DATABASE my_database_3;


-- 데이터베이스 키-값 속성 지정
CREATE DATABASE my_database_3
WITH DBPROPERTIES ('creator' = 'HongKilDong', 'date' = '2016-06-17');

DESCRIBE DATABASE EXTENDED my_database_3;


-- 작업 데이터베이스 설정
USE my_database_3;

 

-- 현재 데이터베이스 출력
set hive.cli.print.current.db = true;
set hive.cli.print.current.db = false;

 

 

-------------------
-- TABLE
-------------------

 

-- 테이블 조회
USE mydb;
SHOW TABLES;
SHOW TABLES LIKE 'a.*'; -- show any tables, starting with 'a' character

USE yourdb;
SHOW TABLES IN mydb; -- IN : when current database is different


-- 테이블 생성 (creating table)
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);


-- 테이블 설명 자세히 보기
DESCRIBE EXTENDED mydb.my_table;


-- 테이블 파티션 조회
SHOW PARTITIONS my_table;
SHOW PARTITIONS my_table PARTITION(year = '2016', month = '6', day = '17');

DESCRIBE EXTENDED my_table; -- detailed table information with partition Keys


-- 테이블 내 특정 칼럼의 스키마 보기
DESCRIBE mydb.my_table.var_1;


-- 테이블 스키마 복사 (copying table schema)
CREATE TABLE IF NOT EXISTS mydb.my_table_copy
LIKE mydb.my_table;


-- 테이블명 변경 (changing table name)
ALTER TABLE my_table RENAME TO my_table_2


-- 테이블 파티션 추가 (adding table partition)
ALTER TABLE my_table ADD IF NOT EXISTS
PARTITION (year = 2016, month = 6, day = 15) LOCATION '/para/2016/06/15'
PARTITION (year = 2016, month = 6, day = 16) LOCATION '/para/2016/06/16'
PARTITION (year = 2016, month = 6, day = 17) LOCATION '/para/2016/06/17';


-- 테이블 파티션 위치 변경 (changing table partition's location)
ALTER TABLE my_table PARTITION(year = 2016, month = 6, day = 17)
SET LOCATION 's3n://bucket/para/2016/06/17';


-- 테이블 파티션 삭제 (dropping table partition)
ALTER TABLE my_table DROP IF EXISTS PARTITION(year = 2016, month = 6, day = 17);

 

 

--------------------
-- COLUMN
--------------------

 

-- 컬럼명 변경, 주석 변경, 변수 위치 변경
ALTER TABLE my_table
CHANGE COLUMN var_1 var_1_1 INT -- changing column name
COMMENT 'change var_1 to var_1_1 after after_var' -- adding comment
AFTER var_2; -- changing variable's location, after var_2

 

-- 컬럼 추가 (adding column)
ALTER TABLE my_table
ADD COLUMNS (
mean FLOAT COMMENT 'mean of variable',
std_dev FLOAT COMMENT 'standard deviation',
coef_vari FLOAT COMMENT 'coefficient of variation'
);


-- 컬럼 삭제 및 교체 (dropping columns, replacing columns)
ALTER TABLE my_table REPLACE COLUMNS (
var_1_1 INT COMMENT 'changing column name from var_1 to var_1_1',
var_3 STRING COMMENT 'replacing; keeping var_3, dropping var_2'
);


-- 테이블 속성 변경 (changing table property)
ALTER TABLE my_table SET TBLPROPERTIES (
'note' = 'put note here; blah-blah..'
);


-- 테이블 삭제 방지 (guaranteeing no drop)
ALTER TABLE my_table
PARTITION(year = 2016, month = 6, day = 17) ENABLE NO_DROP; -- opposite: DISABLE NO_DROP

 

-- 테이블 쿼리 방지 (blocking query)
ALTER TABLE my_table
PARTITION(year = 2016, month = 6, day = 17) ENABLE OFFLINE; -- opposite : DISABLE OFFLINE


 

[Reference]

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

 

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by R Friend R_Friend


티스토리 툴바