Apache-Hive : 하이브QL(Hive QL) - 테이블 생성

1. 하이브QL (HiveQL)

하이브는 하이브QL 이라는 SQL문과 유사한 언어를 제공한다. 대부분의 기능은 SQL과 유사하지만 다음과 같은 차이점이 있다.

• 하이브에서 사용하는 데이터가 HDFS에 저장되는데, HDFS가 한 번 저장한 파일은 수정할 수 없기 때문에 UPDATE와 DELETE는 사용할 수 없다. 또한, INSERT도 비어 있는 테이블에 입력하거나 이미 입력된 데이터를 덮어 쓰는 경우에만 가능하다. 따라서 하이브QL은 "INSERT OVERWRITE"라는 키워드를 사용한다.
• SQL은 어떠한 절에서도 서브쿼리를 사용할 수 있지만 하이브QL은 FROM 절에서만 서브 쿼리를 사용할 수 있다.
• SQL의 뷰는 업데이트할 수 있고, 구체화된 뷰 또는 비구체화된 뷰를 지원하지만 하이브QL의 뷰는 읽기 전용이며, 비구체화된 뷰만 지원한다.
• SELECT 문을 사용할 때 HAVING 절을 사용할 수 없다.
• 저장 프로시저(stored procedure)를 지원하지 않지만 맵리듀스 스크립트를 실행할 수 있다.
  

1) 테이블 생성

교재에서는 미국 항공 운항 지연 데이터를 분석하기 위한 하이브QL 쿼리문을 작성하였다. 참고로 하이브QL은 대소문자를 구분하지 않는다.

hive> CREATE TABLE airline_delay(Year INT, Month INT,
    > DayofMont INT, DayOfWeek INT,
    > DepTime INT, CRSDepTime INT,
    > ArrTime INT, CRSArrTime INT,
    > UniqueCarrier STRING, FlightNum INT,
    > TaiNum STRING, ActualElapsedTime INT,
    > CRSElapsedTime INT, AirTime INT,
    > ArrDelay INT, DepDelay INT,
    > Origin STRING, Dest STRING,
    > Distance INT, TaxiIn INT,
    > TaxiOut INT, Cancelled INT,
    > CancellationCode STRING
    > COMMENT 'A=carrier, B=weather, C=NAS, D=security',
    > Diverted INT COMMENT '1=yes, 0=no',
    > CarrierDelay STRING, WeatherDelay STRING,
    > NASDelay STRING, SecurityDelay STRING,
    > LateAircraftDelay STRING)
    > COMMENT 'The data consitsts of filght arrival and departure details for all commercial flights within the USA, froom October 1987 to April 2008'
    > PARTITIONED BY (delayYear INT)
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY ','
    > LINES TERMINATED BY '\n'
    > STORED AS TEXTFILE;

OK
Time taken: 1.271 seconds

하이브QL(HiveQL)의 컬럼 타입은 다음 표와 같다.

[출처 :http://www.slideshare.net/madvirus/hive-29724108]

위의 airline_delay 테이블에서는 INT와 STRING타입만 사용했다.

• COMMENT 절은 테이블의 설명을 참고용으로 등록하는 부분이다.

• PARTITIONED BY (delayYear INT)절은 테이블의 파티션을 설정하는 부분이다. 하이브는 쿼리문의 수행속도를 향상시키기 위해 파티션을 설정할 수 있다. 파티션을 설정하면 해당 테이블의 데이터를 파티션별로 티렉터리를 생성해서 저장하게 된다. 여기서는 delayYear별로 디렉터리가 생성되게 된다.

• ROW FORMAT 절은 해당 테이블 내의 데이터가 어떠한 형식으로 저장되는지 설정한다. 이 쿼리문은 필드를 콤마 기준으로 구분하고, 행과 행은 '\n' 값으로 구분한다.

• SORTED AS 절은 데이터 저장 파일 포맷을 의미한다. 하이브는 텍스트 파일을 위한 TEXTFILE과 시퀀스파일을 저장하기 위한 SEQUENCEFILE을 지원한다.
  

2)테이블 목록 조회

airline_delay 테이블을 생성하고 나면 다음과 같이 메타스토어 데이터베이스에 저장된 테이블 목록을 조회한다.

hive> SHOW TABLES;
OK
tab_name
airline_delay
Time taken: 0.218 seconds, Fetched: 1 row(s)

테이블을 생성할 때 CREATE 문 뒤에 EXTERNAL 키워드를 추가할 수 있다. EXTERNAL 키워드로 생성하는 테이블은 '외부테이블'이라고 하는데 외부테이블은 hive.metastore.warehouse.dir 속성이 가리키는 디렉터리에 데이터를 저장하지 않고, 테이블 생성 시 설정한 경로로 데이터를 저장한다. 사용자가 실수로 테이블을 DROP 했더라도 데이터가 보존되는 장점이 있다. 다음은 외부 테이블 생성 예이다.

hive> CREATE EXTERNAL TABLE airline_delay(Year INT, Month INT,
    > DayofMont INT, DayOfWeek INT,
    > DepTime INT, CRSDepTime INT,
    > ArrTime INT, CRSArrTime INT,
    > UniqueCarrier STRING, FlightNum INT,
    > TaiNum STRING, ActualElapsedTime INT,
    > CRSElapsedTime INT, AirTime INT,
    > ArrDelay INT, DepDelay INT,
    > Origin STRING, Dest STRING,
    > Distance INT, TaxiIn INT,
    > TaxiOut INT, Cancelled INT,
    > CancellationCode STRING
    > COMMENT 'A=carrier, B=weather, C=NAS, D=security',
    > Diverted INT COMMENT '1=yes, 0=no',
    > CarrierDelay STRING, WeatherDelay STRING,
    > NASDelay STRING, SecurityDelay STRING,
    > LateAircraftDelay STRING)
    > COMMENT 'The data consitsts of filght arrival and departure details for all commercial flights within the USA, froom October 1987 to April 2008'
    > PARTITIONED BY (delayYear INT)
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY ','
    > LINES TERMINATED BY '\n'
    > STORED AS TEXTFILE
    > LOCATION '/user/root/airline_delay';

3) DESCRIBE로 테이블의 컬럼 확인

테이블이 정상적으로 구성돼 있는지 DESCRIBE 명령어를 이용해 확인한다.

hive> DESCRIBE airline_delay;
OK
col_name	data_type	comment
year                	int                 	                    
month               	int                 	                    
dayofmont           	int                 	                    
dayofweek           	int                 	                    
deptime             	int                 	                    
crsdeptime          	int                 	                    
arrtime             	int                 	                    
crsarrtime          	int                 	                    
uniquecarrier       	string              	                    
flightnum           	int                 	                    
tainum              	string              	                    
actualelapsedtime   	int                 	                    
crselapsedtime      	int                 	                    
airtime             	int                 	                    
arrdelay            	int                 	                    
depdelay            	int                 	                    
origin              	string              	                    
dest                	string              	                    
distance            	int                 	                    
taxiin              	int                 	                    
taxiout             	int                 	                    
cancelled           	int                 	                    
cancellationcode    	string              	A=carrier, B=weather, C=NAS, D=security
diverted            	int                 	1=yes, 0=no         
carrierdelay        	string              	                    
weatherdelay        	string              	                    
nasdelay            	string              	                    
securitydelay       	string              	                    
lateaircraftdelay   	string              	                    
delayyear           	int                 	                    
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
	 	 
delayyear           	int                 	                    
Time taken: 0.174 seconds, Fetched: 35 row(s)

4) 테이블 수정

이미 생성된 테이블은 ALTER TABLE 을 이용해 수정할 수 있다. 예를 들어, 테이블 이름은 ALTER TABLE에 RENAME옵션을 설정해 변경할 수 있다.

hive> ALTER TABLE airline_delay RENAME TO delay_statistics;