Hive-튜토리얼, 데이터 조작

17 May 2019 • Posts

테이블에 데이터 로딩하기

하이브는 로우 레벨에서 삽입, 갱신, 삭제를 지원하지 않는다.

데이터를 테이블에 넣는 유일한 방법은 벌크로 로딩하는 것 밖에 없다. 또는 테이블 디렉토리에 직접 파일을 복사해야 한다.

LOAD DATA INPATH '/path'
OVERWRITE INTO TABLE test
PARTITION (date = '2019-01-01', hour = '1')

파티셔닝된 테이블이 아니라면 PARTITION은 생략할 수 있다.

파일 위치는 개별 파일을 지정하지 않고 파일이 위치한 디렉토리를 지정하는 것이 일반적이다.

LOCAL 예약어는 파일 위치는 로컬 파일시스템에 존재하는 것으로 간주한다.

만약 LOCAL 예약어가 없다면 분산 파일시스템으로 인식하고 파일은 복사되는 것이 아니라 테이블 디렉토리로 이동한다.

OVERWIRTE 예약어를 사용하면 대상이 되는 테이블의 디렉토리 내의 모든 파일을 삭제한다.

만약 OVERWRITE 예약어가 없다면 새로운 파일을 대상 디렉토리 내에 추가한다.

쿼리 결과를 테이블에 넣기

INSERT OVERWRITE TABLE test
PARTITION (date = '2019-01-01', 'hour' = '1')
SELECT * FROM test_result
WHERE date = '2019-01-01' and hour = '1'

test_result 테이블에 특정 조건에 맞는 데이터를 test 테이블에 넣는다.

OVERWRITE를 통해 해당 파티션의 데이터를 교체한다.

동적 파티션 삽입

위는 많은 파티션을 넣고 싶을 때 매우 긴 SQL문을 작성해야한다.

그렇기에 하이브에서 제공하는 동적 파티션 삽입을 통해 쉽게 넣을 수 있다.

INSERT OVERWRITE TABLE test
PARTITION (date, hour)
SELECT ..., date, hour
FROM test_result

SELECT 절의 마지막 두 컬럼에서 파티션 키인 date, hour의 값을 알아서 결정한다.

즉, 파티션 하고 싶은 컬럼은 마지막에 기재하면 된다. 정적 파티션 키는 동적 파티션 키의 앞 쪽에 위치시키면 된다.

기본설정에서 동적 파티션 기능은 꺼져 있어 사용하려면 동적 파티션 모드를 on 해야한다.

  • hive.exec.dynamic.partition
    • 기본값: false
    • 설명: 동적 파티션 기능을 사용할지 말지 결정한다. true로 해야 동적 파티션 모드를 사용할 수 있다.
  • hive.exec.dynamic.partition.mode
    • 기본값: strict
    • 설명: 모든 파티션 컬럼이 동적으로 할당되길 원하면 nostrict로 설정한다.
  • hive.exec.max.dynamic.partitions.pernode
    • 기본값: 100
    • 설명: 각 맵퍼 혹 리듀서가 생성할 수 있는 동적 파티션의 최대 개수.
  • hive.exec.max.dynamic.partitions
    • 기본값: 1000
    • 설명: 동적 파티션을 사용하는 하나의 절이 만들 수 있는 동적 파티션의 최대 개수.
  • hive.exec.max.created.files
    • 기본값: 100,000
    • 설명: 하나의 쿼리 내에서 만들 수 있는 파일의 최대 개수.

테이블 생성과 동시에 데이터 로딩

CREATE TABLE test
AS SELECT id, name
FROM test_result

데이터 읽기

hadoop fs -cp source target

위처럼 하이브 테이블 디렉토리 내 데이터를 그대로 복사하면 된다.

INSERT OVERWRITE LOCAL DIRECTORY '/usr/test/data'
SELECT id, name
FROM test

아니면 위처럼 SELECT와 INSERT를 통해 LOCAL DIRECTORY로 복사할 수 있다.

쿼리

select .. from

hive> SELECT id, name FROM test
hive> SELECT t.id, t.name FROM test t // 별칭 사용

별칭은 여러 테이블을 join할 때 유용하게 사용한다.

정규표현식으로 컬럼 선택

hive> SELECT 'price.*' FROM test

이는 price로 시작하는 모든 컬럼을 선택한다.

컬럼 계산

대부분의 산술 연산자들을 지원한다.

  • A + B: A와 B를 더한다.

  • A - B: A에서 B를 뺀다.

  • A * B: A와 B를 곱한다.

  • A / B: A를 B로 나눈다.

  • A % B: A를 B로 나눈 나머지를 반환한다.

  • A & B: A와 B의 AND 연산을 수행한다.

  • A B: A와 B의 OR 연산을 수행한다.

  • A ^ B: A와 B의 XOR 연산을 수행한다.

  • ~ A: A의 NOT을 수행한다.

두 데이터형이 다를 경우 (Int, Flot) 더 작은 값을 더 넓은 데이터형으로 변환한다.

이 외에도 다양한 산술 함수들 (round(), rand(), sqrt(), abs() 등)과 집계 함수(count(), sum(), avg() 등) 를 제공한다.

Limit 절

hive> SELECT * FROM test LIMIT 10;

반환되는 로우의 개수의 상환치를 설정한다.

컬럼 별칭

hive> SELECT upper(name) as new_name sum(price) as price_sum FROM test LIMIT 10;

컬럼의 별칭을 넣고 싶을 때, as <별칭명>을 쓴다. 특히 컬럼 별칭은 중첩 SELECT 문을 쓸때 유용하다.

CASE … WHEN …. THEN

hive> SELECT id, name, price
    > CASE
    >  WHEN price < 10 THEN 'low'
    >  WHEN price > 10 THEN 'high'
    >  ELSE 'medium'
    > END AS result FROM test

WHERE 절

SELECT 할때 어떤 레코드를 반환할지 결정하는 필터 역할을 한다.

SELECT * FROM test
WHERE name = 'bang'

술어 연산자들은 아래와 같다.

A = B : A, B가 같다면 true, 아니면 false

A <> B, A != B : A, B가 다르면 true, 아니면 false

A < B, A <= B, A > B, A >= B : A, B를 각 비교해 true, false를 반환

A IS NULL : A가 NULL이면 true, 아니면 false

A IS NOT NULL : 위와 반대

A LIKE B : A가 단순화된 SQL 정규표현식인 B와 일치한다면 true, 아니면 false

A RLIKE B, A REGEXP B : A가 정규표현식인 B와 일치한다면 true, 아니면 false

LIKE와 RLIKE

LIKE는 단순화된 SQL 정규표현식을 나타내며, RLIKE는 자바 정규표현식이다.

hive> SELECT id, name FROM test
WHERE name LIKE '%bang'
 // result: jae bang
 
hive> SELECT id, name FROM test
WHERE name LIKE '0%'
 // result: 0ak Park
 
hive> SELECT id, name FROM test
WHERE name LIKE '%bang%'
 // result: jae bang geun

hive> SELECT id, name FROM test
WHERE name RLIKE '.*(Chicago|Ontario).*'
 // result: 100 Ontario St.
 // result: 200 Chicago Ave.
 hive

Comments

Related Posts