[HIVE] 데이터 정의 언어 (Data Definition Language) - 데이터베이스(database), 테이블(table), 컬럼(column)

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