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

대용량 데이터를 관계형 DB나 HDFS 파일로 저장해놓고 필요한 대상만 데이터 전처리, 샘플링해서 R에서 통계분석, 기계학습을 하는 경우가 많이 있습니다. 

이전 포스팅에서 R로 Hive와 PostgreSQL, MySQL 등에 DB connect 해서 사용하는 방법을 소개했었습니다. 

이번 포스팅에서는 

 - R로 Oracle DB connect 하고 데이터 query 하는 방법

 - R로 Presto DB connect 하고 데이터 query 하는 방법

을 소개하겠습니다. 

- rJava, DBI, RJDBC 패키지는 의존성이 있으므로 순서대로 설치하시기 바랍니다. (순서가 바뀌면 에러 발생)

- 혹시 Java 가 설치 안되어 있거나, 버전이 안맞아서 새로 설치해야 하면 이곳 참조 => http://rfriend.tistory.com/232

- Oracle DB의 SQL query문 마지막에 세미콜론(';')을 사용하면 에러가 나므로 유의하세요. 

Presto는 Facebook에서 개발해서 오픈소스로 푼 분산 SQL 쿼리 엔진(distributed SQL query engine for big data)입니다. 메모리 기반으로 실행이 되다 보니 Hive 보다 속도가 많이 빠르기도 하구요, 또 Hive, phoenix, mysql 등 이기종 DB에 접속해서 데이터를 취합하고 가공하는 작업을 할 수 있어서 매우 편리하기도 합니다. 

많은 도움이 되었기를 바랍니다. 

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

지난번 포스팅에서는 웹으로 간단하게 SQL 을 연습할 수 있는 온라인 사이트로서 

w3schools.com/sql 과 테이블을 쿼리해서 데이터 항목을 알아보았습니다. 

주문, 주문상세 테이블과 고객, 상품, 상품카테고리, 상품제공업체, 배송업체, 종업원의 기준정보 테이블이 있는 것으로 봐서 유통업체의 데이터임을 알 수 있습니다. 이벤트나 프로모션 정보 테이블, 온라인이나 모바일 등의 채널 이용 정보 테이블, 고객등급/고객세분화 정보 테이블,  결제수단 정보 테이블 등... 뭐, 유통업체라면 더 많은 테이블이 있어야 겠지만서도, SQL 연습하라고 만든 가상의 약식 데이터 DB 테이블이므로 '이 정도도 어디야'하고 감사하면 사용하면 좋겠습니다. 

ERD (Entity Relationship Diagram)이 없어서 테이블, 데이터 간의 관계를 한 눈에 파악하는 것이 어려웠는데요, 시간 좀 내서 아래처럼 ERD 그려보았습니다. 

[ 유통업체 ERD (Entity Relationship Diagram) ]

* https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all  에 있는 테이블별 칼럼을 보고 추측해서 ERD 그린 것임.  SQL 연습하려는 분은 이 ERD 참고해서 테이블 간 join 해서 분석하면 됨. 

테이블을 여러개 Join 해서 통계량 집계하고 정렬하는 예를 들어보겠습니다. 

위의 문제가 너무 복잡하고, SQL Query도 SUM(), COUNT(), AVG() 등의 aggregation 함수, FROM 절에 Sub Query 랑 INNER JOIN, LEFT JOIN 이 들어가 있고, WHERE 조건절, GROUP BY, HAVING, ORDER BY 등 어지간한 SQL 기능이 망라되어 있어서 복잡하긴 합니다. 

Query가 잘 이해가 안되면 Sub Query를 하나씩 순차적으로 실행시켜보면서 결과를 확인해보면 한결 이해하기가 쉽습니다. 

예를 들어보자면, 위의 Query를 가장 안에 위치한 Sub Query 부터 하나씩 아래에 풀어보겠습니다. 

=========================================================================

[ sub query 1]

OrderDetails 테이블(a)과 Products 테이블(b)을 ProductID key를 기준으로 INNER JOIN으로 교집합을 구해서 Products 테이블에서 상품의 가격과 카테고리ID 데이터를 가져왔습니다. (테이블 구분하기 편하라고 a, b 라는 alias name 별명을 부여해서 변수 앞에 b.Price 처럼 붙여서 사용합니다)  상위 5개만 예시로 가져오겠습니다. 

=========================================================================

[sub query 2]

위의 'sub query 1' 결과 테이블(c)에다가 CategoryID key를 기준으로 Categories 테이블(d)을 LEFT JOIN 하여 Categories 테이블에 있는 CategoryName 칼럼을 붙여서 가져왔습니다.  CategoryName 을 붙여 와야지 원래의 SQL Query 문제에 나와있는 'CategoryName별 판매상품 가격의 합계, 판매(주문)회수, 평균판매가격'을 구할 수 있겠지요?

========================================================================

아래 Query 는 CASE WHEN ~ THEN ... ELSE ... END 문으로 연속형 변수(continuous variable)를 범주형 변수(categorical variable) 로 변환하는 예제 Query 입니다.  아래처럼 '가격대(Price_grp)' 변수를 만든 후에 위에 'SQL Query' 문제에서 사용했던 Query 를 사용해서 다른 응용을 할 수 있습니다. 

많은 도움이 되었기를 바랍니다. 

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

집에서 개인 컴퓨터로 SQL 연습을 하고 싶은데 

- 상용 DBMS 평가판 혹은 오픈 소스 DBMS 설치하자니 힘들고

- DB, Table 생성하고, 데이터 파일을 구해서 import 하거나 

  혹은 건건이 insert 하기에 힘들고

할 때 아주 쉽고 빠르게, 간편하게 웹 상에서 SQL 연습할 수 있는 사이트가 있어서 소개합니다. 

w3schools.com 이라는 곳에서 다양한 언어의 튜토리얼을 제공하는데요, 그 중에서 SQL도 튜토리얼과 함께 연습할 수 있는 웹 환경도 제공하고 있습니다. MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres 등의 다양한 DB에 대한 SQL 튜토리얼을 제공하니 이곳만 잘 이용해도 특정 DB를 염두에 두고 쓰여진 SQL 책보다 더 유용할 수도 있겠습니다. Data 도 준비가 다 되어있어서 그냥 웹에 접속해서 연습하면 됩니다. 

단, Chrome, Safari, FireFox 브라우저만 지원하고, Internet Explorer 는 지원하지 않습니다. 

접속할 주소는요, 

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all 

이며, 아래와 같은 화면이 나타납니다. 

[ w3schools.com 의 SQL 연습할 수 있는 초기 화면 ]

순서대로 살펴보면 

(1) 왼쪽 상단에 SQL 을 입력할 수 있는 'SQL Statement: ' 창이 있습니다. 

(2) 왼쭉 중간에 'Run SQL >' 이라는 네모 단추가 있는데요, 이를 커서로 클릭하면 SQL이 실행됩니다. 

(3) 왼쪽 하단에 'Reslut: ' 란에 SQL 실행 결과가 나타납니다. 

(4) 우측 상단에 보면 Database에 들어있는 Table 이름과 Record 수가 나옵니다. 

아래의 이름으로 총 8개의 Table에 있는 데이터를 SQL 연습하는데 사용할 수 있습니다! 

ERD (Entity Relationship Diagram) 이 있으면 좋을 텐데요, 그게 없는지... 못 찾겠네요. 

각 Table 별로 상위 5개씩 Select 해서 조회를 해보면 아래와 같습니다. 

각 Table 이름을 봐도 그렇고, 상위 5개 records 조회를 해서 봐도 그렇고, 유통업체에서 사용하는 DB table 들을 예로 간단한 예제 DB를 제공한다고 보면 되겠습니다. 

다음번 포스팅에서는 ERD 한번 그려서 올려보겠습니다. 그리고 table 간 Join 도 해보고, aggregation 함수도 몇 개 예를 들어서 한번 더 포스팅해보겠습니다. 

SQL 집에서 간단하게 공부하시려는 분들에게 도움이 되었기를 바랍니다. 

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

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

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