Hive sql Partition

잠깐 근무했던 곳에서 Hive sql를 경험을 했습니다.

당시 사용하면서 지적받았던 문법을 정리할겸 작성합니다.

 

테이블 생성

CREATE TABLE '데이터베이스이름'.'테이블이름'(
    column1 STRING COMMENT '컬럼1'
    ,column2 INT COMMENT '컬럼2'
    ,colmun3 BIGINT COMMENT '컬럼3'
)
PARTITIONED BY(
    search_col STRING COMMENT '검색컬럼1'
    ,search_col2 STRING COMMENT '검색컬림2'
)

당시 일할 때는 그냥 선배들이 작성한 코드를 붙여 넣는 형식으로 테이블을 생성하고 삭제했습니다.

생성하는 부분에서는 다른 sql언어와 다를바가 없었는데, 저는 처음 PARTITIONED BY를 봤습니다.

알고보니 일반적으로 검색을 할때는 처음부터 끝까지 검색을 해서 용량이 커지면 속도가 느려진다고

PARTITION을 나눠서 저장을 하는 것이 였습니다.

이렇게하면 정해진 파티션만 검색하면 나온다고 했습니다.

INDEX는 따로 조회용 테이블을 만들고, PARTITION은 하나의 테이블이라고 배웠네요.

그리고 데이터베이스의 점(.)을 찍으면 해당 데이터베이스안에서 테이블을 생성하겠다고 하는 부분도 처음봤습니다.

다음 문장을 작성할때, 저는 항상 문법이 끝나는 부분에 콤마(,)를 작성하는게 편하다고 생각했는데

알고보니 엔터 후 다음에 작성하는 것이 훨씬 보기가 좋더라구요ㅎㅎ

 

 

이렇게 파티셔닝이 된 테이블들에게 데이터를 넣으려면 문법을 조금 수정했어야 하는데, 잘 모르던 저는 3시간 정도 헤맸던걸로 기억하네요..ㅎㅎ

 

데이터 입력

 

INSERT INTO database.tablename PARTITION (search_col,search_col2)

INSERT OVERWRITE TABLE database.tablename PARTITION (search_col,search_col2)

INSERT IGNORE INTO database.tablename PARTITION (search_col,search_col2)

 

처음 insert를 배웠을땐, INTO VALUES 만 있는 줄 알았다..ㅋㅋㅋ

알고보니 INTO는 기존 데이터가 있을때 추가하는 형식이지만 중복된 데이터가 들어가면 오류가나고,

OVERWRITE는 말 그대로 있는 데이터를 밀고서 다시 넣는 방식이였고,

IGNORE INTO는 중복된 데이터는 건너뛰고 데이터를 입력하는 것이였다.

 

혼자서 찾아 공부하니, 탐험하는 것 같아서 재밌었다..ㅋㅋㅋ

 

INSERT OVERWRITE TABLE database.testtable3 PARTITION (search_col,search_col2)
select
    column1
    ,CAST(column2 as int) as column2
    ,REPLACE(column3,'.','-') as column3
    ,substring(column4,1,4) as column4
    ,substring(column5,5,2) as column5
    ,trim(column6) as column6
    ,CASE
        WHEN trim(column7) like 'b%' THEN 'OK'
        ELSE 'NO' END as column7
from 
(select * from testtable2) as testtable2

 

간략하게 적으려고하니 이상하게 보이는 스크립트가 되었네요..ㅎㅎ

자주 사용했던 것이 서브쿼리문이라고 해서 from 뒤에 나와있는 문장입니다.

대략 서브쿼리를 10~20개 정도 사용했으니, 나중에 가서는 복잡해서 일주일만 지나도 까먹곤 했습니다..ㅋㅋㅋ

속도는 딱히 중요하지 않았어서 UNION ALL이나 UNION 같은 병합도 자주 사용했었네요..

 

---

---

간단하게 설명을 적겠습니다.

CAST는 데이터 형식 변환 컬럼뒤에 as를 붙여서 사용합니다.

REPLACE는 문자열을 변경하는 것 '.' -> '-'

substring은 컬럼뒤에 자를 문장 처음 순번부터 그 다음 몇개까지 자를건지를 묻는 것입니다.

trim은 양쪽 공백을 지워주는 함수

CASE는 조건문입니다. 위에 예시는 공백없는 column7이 b로 시작하는 단어이면 OK로 반환, 아니면 NO로 반환입니다.

 

그 외로

UNION은 테이블끼리 병합할때 위아래로 붙이는 함수입니다.

단 각 행들이 중복되면 삭제하고서 병합이 됩니다.

중복이 있더라도 괜찮으면 UNION ALL을 사용하시면 됩니다.

 

Join도 굉장히 많이 쓰입니다. 이 부분은 따로 작성하겠습니다.

 

SQL은 한번 알아두면 까먹기 어려운 언어같네요.

다른 언어들은 알아듣기 어려운 반면 SQL은 단순하게 적어나가는거라 그런지 애정이 갑니다..ㅎㅎ

감사합니다.