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

폴더에 자잘하게 쪼개진 여러개의 파일들이 있을 때, 그리고 이 파일들을 일일이 R로 불러오기 해야 할 때, 더그리고 이들 불러온 파일을 한개의 데이터셋을 합쳐야 할 때 (이쪽 동네 전문용어로) 노가다를 하지 않고 좀더 스마트하게 하는 방법을 소개하겠습니다.

순서는 다음과 같습니다.

• (1) 폴더 경로 객체로 만들기
• (2) 폴더 내 파일들 이름을 list-up 하여 객체로 만들기
• (3) 파일 개수 객체로 만들기
• (4) 폴더 내 파일들을 LOOP 돌려서 불러오기 : read.table()
• (5) 파일을 내보내면서 합치기 : write.table(dataset, APPEND = TRUE)
• (6) 데이터프레임으로 불러오기, 칼럼 이름 넣기 : read.table(dataset_all, col.names = c())

자, 예를 들면서 순서대로 R script 설명하겠습니다.

아래의 화면캡쳐 예시처럼 MyDocuments > R > FILES 폴더 아래에 daily로 쪼개진 10개의 text 파일들이 들어있다고 해봅시다.  (10개 정도야 일일이 불어올 수도 있겠지만, 100개, 1,000개 파일이 들어있다면?)

• (1) 폴더 경로 객체로 만들기

## cleaning up environment
rm(list=ls())

## making directory as an object 
src_dir <- c("C:/Users/Owner/Documents/R/FILES") # 경로 구분 : '\'를 '/'로 바꿔야 함 

src_dir 
#[1] "C:/Users/Owner/Documents/R/FILES"

• (2) 폴더 내 파일들 이름을 list-up 하여 객체로 만들기 : list.files()

# listing up name of files in the directory => object 
src_file <- list.files(src_dir) # list 

src_file 
#[1] "day_20160701.txt" "day_20160702.txt" "day_20160703.txt" "day_20160704.txt" 
#[5] "day_20160705.txt" "day_20160706.txt" "day_20160707.txt" "day_20160708.txt" 
#[9] "day_20160709.txt" "day_20160710.txt"

"C:/Users/Owner/Documents/R/FILES" 디렉토리에 들어있는 파일들을 열어보면 아래와 같은 데이터들이 들어있습니다. (가상으로 만들어 본 것임)  daily로 집계한 데이터들이 들어있네요.

• (3) 파일 개수 객체로 만들기 : length(list)

# counting number of files in the directory => object 
src_file_cnt <- length(src_file)

src_file_cnt 
#[1] 10

여기까지 R을 실행하면 아래와 같이 environment 창에 객체들이 생겼음을 확인할 수 있습니다.

• (4) 폴더 내 파일들을 LOOP 돌려서 불러오기
  => (5) 파일을 내보내면서 합치기 : write.table(dataset, APPEND = TRUE)
  
  : for(i in 1:src_file_cnt) {read.table()
                                   write.table(dataset, append = TRUE)}

## write.table one by one automatiically, using loop program 
for(i in 1:src_file_cnt) {
	# write.table one by one automatiically, using loop program 
    dataset <- read.table(
    	paste(src_dir, "/", src_file[i], sep=""), 
        sep=",", 
        header=F, 
        stringsAsFactors = F) 
        
    # dataset exporting with 'APPEND = TREU' option, filename = dataset_all.txt
    write.table(dataset, 
    	paste(src_dir, "/", "dataset_all.txt", sep=""), 
        sep = ",", 
        row.names = FALSE, 
        col.names = FALSE, 
        quote = FALSE, 
        append = TRUE) # appending dataset (stacking)
        
    # delete seperate datasets
    rm(dataset) 
    
    # printing loop sequence at console to check loop status
    print(i)
} 

#[1] 1 
#[1] 2 
#[1] 3 
#[1] 4 
#[1] 5 
#[1] 6 
#[1] 7 
#[1] 8 
#[1] 9 
#[1] 10

여기까지 실행을 하면 아래처럼 MyDocuments>R>FILES 폴더 아래에 'dataset_all.txt' 라는 새로운 텍스트 파일이 하나 생겼음을 확인할 수 있습니다. 

새로 생긴 'dataset_all.txt' 파일을 클릭해서 열어보면 아래와 같이 'day_20160701.txt' ~ 'day_20160710.txt'까지 10개 파일에 흩어져있던 데이터들이 차곡차곡 쌓여서 합쳐져 있음을 확인할 수 있습니다.

• (6) 데이터 프레임으로 불러오기 : read.table()
       칼럼 이름 붙이기 : col.names = c("var1", "var2", ...)

# reading dataset_all with column names 
dataset_all_df <- read.table(
	paste(src_dir, "/", "dataset_all.txt", sep=""), 
    sep = ",", 
    header = FALSE, # no column name in the dataset 
    col.names = c("ymd", "var1", "var2", "var3", "var4", "var5", + "var6", "var7", "var8", "var9", "var10"), # input column names 
    stringsAsFactor = FALSE, 
    na.strings = "NA") # missing value : "NA"

우측 상단의 environment 창에서 'dataset_all_df' 데이터 프레임이 새로 생겼습니다.

클릭해서 열어보면 아래와 같이 'day_20160701.txt' ~ 'day_20160710.txt'까지 데이터셋이 합쳐져있고, "ymd", "var1" ~ "var10" 까지 칼럼 이름도 생겼습니다.

프로그래밍을 통한 자동화가 중요한 이유, 우리의 시간은 소중하니깐요~! ^^

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

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

(2018.03.14일 내용 추가)

댓글 질문에 '폴더에 있는 개별 파일을 하나씩 읽어와서 하나씩 DataFrame 객체로 메모리상에 생성하는 방법에 대한 질문이 있어서 코드 추가해서 올립니다. 위에서 소개한 방법과 전반부는 동일하구요, 마지막에 루프 돌릴 때 assign() 함수로 파일 이름을 할당하는 부분만 조금 다릅니다.

#========================================================= # read all files in a folder and make a separate dataframe #========================================================= rm(list=ls()) # clear all # (1) directory src_dir <- c("D:/admin/Documents/R/R_Blog/326_read_all_files") # (2) make a file list of all files in the folder src_file <- list.files(src_dir) src_file # (3) count the number of files in the directory => object src_file_cnt <- length(src_file) src_file_cnt # 5 # (4) read files one by one using looping #     => make a dataframe one by one using assign function for (i in 1:src_file_cnt){   assign(paste0("day_", i),           read.table(paste0(src_dir, "/", src_file[i]),                     sep = ",",                     header = FALSE))   print(i) # check progress } rm(src_dir, src_file, src_file_cnt, i) # delete temp objects ls() # list-up all dataframes

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

(2021.08.24일 추가)

댓글에 "여러개의 파일을 하나로 합칠 때 "파일 이름을 데이터 프레임의 새로운 칼럼에 값으로 추가한 후"에 합치는 방법"에 대한 문의가 있었습니다.  댓글란에 코드 블락을 복사해 넣으면 들여쓰기가 무시되어서 보기가 힘들므로 본문에 예제 코드 추가해 놓습니다. 

간단한 샘플 텍스트 파일 3개 만들어서 for loop 순환문으로 각 파일 읽어온 후, 파일 이름을 새로운 칼람 'z'의 값으로 할당 해주고, blank data.frame 인 'day_all' 에 순차적으로 rbind 해주었습니다. 

##--------------------------------------------------------
## add new column with file name and append all dataframes
##--------------------------------------------------------

## blank data.frame to save all files later
day_all <- data.frame()

## file list
src_dir <- c("/Users/lhongdon/Documents/day")
src_file <- list.files(src_dir)
src_file
# [1] "day_20160701" "day_20160702" "day_20160703"


for (i in 1:length(src_file)){
  # read dataset 1 by 1 sequentially
  day_temp <- read.table(
    paste0(src_dir, "/", src_file[i]), 
    sep=",", 
    header=T, 
    stringsAsFactors=F)
  
  # add filename as a new column
  day_temp$z <- src_file[i]
  
  # rbind day_temp to day_all data.frame
  day_all <- rbind(day_all, day_temp)
  
  #print(i) # for progress check
}

print(day_all)
# x y            z
# 1 a e day_20160701
# 2 b f day_20160701
# 3 c g day_20160701
# 4 q w day_20160702
# 5 e r day_20160702
# 6 t y day_20160702
# 7 u i day_20160703
# 8 o p day_20160703
# 9 k l day_20160703

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

(2021.08.25 일 추가)

댓글에 추가 질문이 달려서 요건에 맞게 코드를 더 추가하였습니다.

중첩 for loop 문에 조건절이 여러개 들어가다 보니 코드가 많이 복잡해졌네요. 

[데이터 전처리 요건 ]

1. 로컬 머신 폴더 내 여러개의 csv 파일을 읽어와서 한개의 R data.frame 으로 통합

2. 이때 개별 csv 파일로 부터 읽어들인 데이터를 특정 개수의 [행 * 열] data.frame 으로 표준화

    - 가령, 3 행 (rows) * 3 열 (columns) 의 data.frame 으로 표준화하기 원한다면 

    - 개별 csv 파일로 부터 읽어들인 데이터의 행(row)의 개수가 3보다 크면 1~3행까지만 가져와서 합치고 나머지는 버림. 반대로 3개 행보다 부족하면 'NA' 결측값으로 처리함. 

    - 개별 csv 파일로 부터 읽어들인 데이터의 열(column)이 타켓 칼럼 이름(가령, "x", "y", "z") 중에서 특정 칼럼이 없다면 그 칼럼의 값은 모두 'NA' 결측값으로 처리함.(가령, csv 파일 내에 "x", "y" 만 있고 "z" 칼럼은 없다면 "z" 칼럼을 만들어주고 대신 값은 모두 'NA' 처리해줌)

3. 'day' 라는 칼럼을 새로 만들어서 파일 이름(day 날짜가 들어가 있음)을 값으로 넣어줌

[ 예제 데이터 ]

##--------------------------------------------------------
## (1) 3 rows & 3 cols DataFrame
## (2) add new column with file name and append all dataframes
##--------------------------------------------------------

## blank data.frame to save all files later
day_all <- data.frame()

## file list
src_dir <- c("/Users/lhongdon/Documents/day")
src_file <- list.files(src_dir)
src_file
# [1] "day_20160701" "day_20160702" "day_20160703" "day_20160704"


## setting target rows & cols
row_num <- 3 # set your target number of rows
col_name <- c("x", "y", "z") # set your target name of columns

for (i in 1:length(src_file)){

  # read dataset 1 by 1 sequentially
  day_temp <- read.table(
    paste0(src_dir, "/", src_file[i]), 
    sep=",", 
    header=T, 
    stringsAsFactors=F)
  
  ##-- if the number of rows is less than 3 then 'NA', 
  ##-- if the number of rows is greater than 3 than ignore them
  ##-- if the name of columns is not in col_nm then 'NA'
  
  # blank temp dataframe with 3 rows and 3 columns
  tmp_r3_c3 <- data.frame(matrix(rep(NA, row_num*col_num), 
  								nrow=row_num, 
                                byrow=T))
  names(tmp_r3_c3) <- col_name
  
  tmp_row_num <-  nrow(day_temp)
  tmp_col_name <- colnames(day_temp)
  
  r <- ifelse(row_num > tmp_row_num, tmp_row_num, row_num)
  
  for (j in 1:r) {
    for (k in 1:length(tmp_col_name)) {
      tmp_r3_c3[j, tmp_col_name[k]] <- day_temp[j, tmp_col_name[k]]
    }
  }
  
  # add filename as a new column 'day'
  tmp_r3_c3$day <- src_file[i]
  
  # rbind day_temp to day_all data.frame
  day_all <- rbind(day_all, tmp_r3_c3)
  
  rm(tmp_r3_c3)
  print(i) # for progress check
}

print(day_all)
# x    y  z          day
# 1     a    e  1 day_20160701
# 2     b    f  3 day_20160701
# 3     c    g  5 day_20160701
# 4     q    w NA day_20160702
# 5     e    r NA day_20160702
# 6     t    y NA day_20160702
# 7     u    i  3 day_20160703
# 8     o    p  6 day_20160703
# 9  <NA> <NA> NA day_20160703
# 10    e    a  6 day_20160704
# 11    d    z  5 day_20160704
# 12    c    x  3 day_20160704

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

행복한 데이터 과학자 되세요! :-)

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

군집 간 거리를 측정하는 방법에 따라서 여러가지 알고리즘이 있는데요, 지난번 포스팅에서는 응집형 계층적 군집화(agglomerative hierarchical clustering) 알고리즘 중에서 그래프 기반(Graph-based)의 (1-1) 단일(최단) 연결법 (single linkage method, MIN), (1-2) 완전(최장) 연결법 (complete linkage method, MAX), (1-3) 평균 연결법 (average linkage method) 에 대해 소개하였습니다.

응집형 계층적 군집화 알고리즘 중에서 프로토타입 기반(Prototype-based) 모형은 미리 정해놓은 각 군집의 프로토타입에 터이터가 얼마나 가까운가로 군집의 형태를 결정합니다. 프로토타입 기반 유사성 측도로서 두가지 방법인 (1-4) 중심 연결법 (centroid linkage method)과 (1-5) Ward 연결법 (Ward linkage method) 중에서 이번 포스팅에서는 (1-4) 중심 연결법에 대해서 소개하겠습니다.

중심 연결법(Centroid Linkage method)은 두 군집 간의 거리를 측정할 때 각 군집의 중심(centroid) 간의 거리를 사용합니다.  아래 그림의 왼쪽의 이미지를 참고하시기 바랍니다.

[ 프로토타입 기반 유사성 측정 (Prototype-based maesure of proximity) ]

[표기 (denotation) 설명]

- d(i+j, k) : 군집 i와 j가 합쳐진 새로운 군집 i+j (cluster i U j)와 군집 k 간의 거리(distance)

- μi+j : 군집 i와 r군집 j의 데이터를 가중평균(weighted average)을 이용해 계산한 새로운 중심
- ni : 군집 i의 데이터 개수,  nj : 군집 j의 데이터 개수

- 빨간점 : 각 군집의 중심(centroid)

> ##--------------------------------------------
> ## (1) Agglomerative Hierarchical Clustering 
> ##   (b) Prototype-based
> ##    (1-4) Centroid Linkage
> ##--------------------------------------------
> 
> x <- c(1, 2, 2, 4, 5)
> y <- c(1, 1, 4, 3, 4)
> 
> xy <- data.frame(cbind(x, y))
> 
> xy
  x y
1 1 1
2 2 1
3 2 4
4 4 3
5 5 4
> 
> # scatter plot of xy
> plot(xy, pch = 19, xlab = c("x coordinate"), ylab = c("y coordinate"), 
+      xlim = c(0, 6), ylim = c(0, 6), 
+      main = "scatter plot of xy")
> 
> # adding student label
> text(xy[,1], xy[,2], labels = abbreviate(rownames(xy)), 
+      cex = 0.8, pos = 1, col = "blue") # pos=1 : at the bottom
> 
> 
> # adding dotted line
> abline(v=c(3), col = "gray", lty = 2) # vertical line
> abline(h=c(3), col = "gray", lty = 2) # horizontal line

> # proximity matrix : squares of euclidean distance matrix for 6 points
> dist(xy, method = "euclidean")^2
   1  2  3  4
2  1         
3 10  9      
4 13  8  5   
5 25 18  9  2

> # Agglomerative Hierarchical Clustering : Centroid Linkage method
> hc_cent <- hclust(dist(xy)^2, method="centroid")
> hc_cent

Call:
hclust(d = dist(xy)^2, method = "centroid")

Cluster method   : centroid 
Distance         : euclidean 
Number of objects: 5 

> # dendrogram
> my_par = par(no.readonly = TRUE)
> par(oma = c(0, 0, 1, 0))
> par(mfrow = c(1, 2))
> plot(hc_cent)
> plot(hc_cent, hang = -1) # hang = -1 : line from the bottom

> # custering information
> rev(hc_cent)
$dist.method
[1] "euclidean"

$call
hclust(d = dist(xy)^2, method = "centroid")

$method
[1] "centroid"

$labels
NULL

$order
[1] 1 2 3 4 5

$height
[1]  1.00000  2.00000  6.50000 11.80556

$merge
     [,1] [,2]
[1,]   -1   -2
[2,]   -4   -5
[3,]   -3    2
[4,]    1    3

> # comparison of height among linkage methods
> hc_sl <- hclust(dist(xy)^2, method="single")
> hc_cl <- hclust(dist(xy)^2, method="complete")
> hc_avg <- hclust(dist(xy)^2, method="average")
> hc_cent <- hclust(dist(xy)^2, method="centroid")
> 
> # dendrogram
> my_par = par(no.readonly = TRUE)
> par(oma = c(0, 0, 1, 0))
> par(mfrow = c(1, 4))
> plot(hc_sl, main = "Single Linkage")
> plot(hc_cl, main = "Complete Linkage")
> plot(hc_avg, main = "Average Linkage")
> plot(hc_cent, main = "Centroid Linkage")

이번 포스팅에서는 외부 텍스트 파일을 불러오기 할 때에

Error in scan(file = file, what = what, sep = sep, quote = quote, dec = dec, 

: line 3 did not have 5 elements

와 같은

(1) 에러 메시지가 뜨는 이유와

(2) 대처 방안

(3) 유의 사항

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

(1) 에러메시지 이유

Error in scan(file = file, what = what, sep = sep, quote = quote, dec = dec, 

: line 3 did not have 5 elements 

에러메시지가 뜨는 이유는 3번째 행(line 3)의 원소의 개수가 결측값(missing value) 때문에 총 5개가 안되기 때문입니다. (row name 1개, 행 4개 모두 포함해서 총 5개 원소)

아래에 간단한 예를 들어서 설명하겠습니다.

cat() 함수를 이용해서 V1, V2, V3, V4 라는 변수명을 가진 4개의 변수에 대해 First, Second, Third, Fourth라는 rowname을 가진 text 파일을 만들어보았습니다. 이때 의도적으로 3번째 행(3rd row, 3 line)에 원소를 row name 포함해서 4개만 만들어보았습니다. (다른 행은 모두 5개 원소, 3행만 4개 원소) 

MyDocument 폴더에 가보면 test.txt 라는 파일이 생성되어 있음을 확인할 수 있으며, 그 파일을 열어보면 아래 화면 캡쳐한 것 처럼 데이터가 들어가 있으며, 3행은 원소가 총 4개이며, 다른 행 대비 1개가 모자람을 알 수 있습니다.  

> ##-----------------------------------------------##
> ## Error in scan(file, what, nmax, sep, dec, quote, skip, nlines, na.strings,  
> ## :line 3 did not have 5 elements
> ##-----------------------------------------------##
> 
> # exmaple with missing value in line 3 at 5th element
> cat("V1 V2 V3 V4\nFirst 1 2 3 4\nSecond 5 6 7 8\nThird 9 10 11\nFourth 13 14 15 16", file="test.txt")

이렇게 생긴 데이터를 read.table() 함수를 써서 읽어들여보겠습니다.

> read.table("test.txt", header = TRUE)
Error in scan(file = file, what = what, sep = sep, quote = quote, dec = dec,  : 
  line 3 did not have 5 elements
In addition: Warning message:
In read.table("test.txt", header = TRUE) :
  'test.txt'에서 readTableHeader에 의하여 발견된 완성되지 않은 마지막 라인입니다

다른 행은 총 5개의 원소(row name 1개, 변수 4개)가 있는데 반해, 3번째 행은 5개 원소가 아니라는 에러 메시지가 떴습니다.  원소가 일부 모자란다는 뜻입니다.

(2) 대처 방안

첫번째 방법은 파일을 열어서 결측값 위치를 찾아가서 그 값에 제대로 된 값을 채워넣는 것입니다.  파일의 데이터 개수가 몇 개 안되고 육안으로 확인해서 채워넣을 수 있는 경우에는 적용가능 하겠지만, 만약 데이터 개수가 수천, 수만, 수십만 개라면 많이 힘들겠지요? ^^;

두번째 방법은, 만약 원소의 개수가 모자라는 이유가 제일 마지막 열에 결측값(missing value)가 있기 때문이라면 fill = TRUE 옵션을 부가하면 NA 표기가 부가되면서 읽어들이기를 간단하게 해결할 수 있습니다.

> # missing value => NA : fill = TRUE
> testfile <- read.table("test.txt", header = TRUE, fill = TRUE)
Warning message:
In read.table("test.txt", header = TRUE, fill = TRUE) :
  'test.txt'에서 readTableHeader에 의하여 발견된 완성되지 않은 마지막 라인입니다
> 

> testfile
       V1 V2 V3 V4
First   1  2  3  4
Second  5  6  7  8
Third   9 10 11 NA
Fourth 13 14 15 16

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