CREATE/DROP/TRUNCATE TABLE

작성 방법

영어 대문자는 작성할 때 필수로 작성해야하는 부분( ex : CREATE )이고, ( ) 안에 영어 대문자로 파이프라인 | 과 함께 있는 경우는 선택적으로 반드시 하나는 작성해야하는 부분( ex : DATABASE|SCHEMA )이고,  [ ] 안에 들어가는 부분은 선택적 요소로, 상황에 맞게 사용이 가능한 부분( ex : IF NOT EXISTS )이고, 영어 소문자는 사용자가 임의로 작성이 가능한 부분( database_name)입니다.

---

CRAETE TABLE

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
  [AS select_statement];

• TEMPORARY : 현재 세션에서만 볼수 있는 조건
  • 파티션 지원되지 않음
  • 인덱스 생성 지원되지 않음
• EXTERNAL : 외부 TABLE로 생략하면 MANAGED TABLE로 생성됨
• IF NOT EXISTS : '이미 존재하는 경우'로 이미 존재하는 TABLE명이 있어도 오류가 발생하지 않음
• db_name. : TABLE이 생성될 DATABASE를 지정. 생략하면 현재 사용중인 DATABASE에 생성됨
• COMMNET : 해당 부분에 대한 설명
• PARTITIONED BY : 파티션으로 사용할 COLUMN을 지정 ( 위에서 선언한 COLUMN과는 별개의 COLUMN을 지정 )
• CLUSTERED BY : TABLE이나 파티션을 버킷
• SORTED BY : 해당 버킷 내에서 정렬
• SKEWED BY : 하나 이상의 컬럼에 빈도 수가 많은 값을 지정 
• ROW FORMAT : row 형식을 지정
• STORED AS : file_format을 지정함 
• STORED BY : 스토리지 핸들러를 지정
• WITH SERDEPROPERTIES : 사용될 SERDE 속성을 지정
• LOCATION : TABLE에 반영될 파일 HDFS 경로를 지정
• TBLPROPERTIES : TABLE 속성을 설정
• AS select_statement : SELECT하여 TABLE을 생성

data_type

TYPE	NAME
data_type	PRIMITIVE TYPE, ARRAY TYPE, MAP TYPE, STRUCT TYPE, UNION TYPE
PRIMITIVE TYPE	TINYINT, SMALLINT, INT, BIGINT, BOOLEAN, FLOAT, DOUBLE, DOUBLE PRECISION, STRING, BINARY, TIMESTAMP, DECIMAL, DECIMAL(precision, scale), DATE, VARCHAR, CHAR
ARRAY	ARRAY<data_type>
MAP	MAP<PRIMITIVE TYPE, data_type>
STRUCT	STRUCT<col_name:data_type [COMMENT col_comment], ...>
UNION	UNIONTYPE<data_type, data_type, ... >

 

row_format

DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]  
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

 

file_format

• SEQUENCEFILE
• TEXTFILE
• RCFILE
• ORC
• PARQUET
• AVRO
• JSONFILE
• INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

 

column_constraint_specification

• PRIMARY KEY
• UNIQUE
• NOT NULL
• DEFAULT
  • LITERAL, CURRENT_USER(), CURRENT_DATE(), CURRENT_TIMESTAMP(), NULL

 

constraint_specification

• , PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY
• , CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE
• , CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY
• , CONSTRAINT constraint_name CHECK (col_name, ...) ENABLE|DISABLE NOVALIDATE RELY/NORELY

 

 

CREATE TABLE 예시

-- 가장 평범한 예시
CREATE TABLE hivetable (
  id		int,
  name		string);

-- external table with location
CREATE EXTERNAL TABLE hivetable (
  id		int,
  name		string)
LOCATION '/user/public/external';

-- partition
CREATE TABLE hivetable (
  id		int,
  name		string)
PARTITION BY (yr string, month string);

-- CTAS
CREATE TABLE hivetable AS
SELECT * FROM selecttable;

-- ROW FORMAT
CREATE TABLE hivetable (
  id		int		comment 'user id',
  name		string	comment 'user name')
COMMENT 'table commnect'
PARTITION BY (dt string)
ROW FORMATT DELIBITED
FILEDS TERMINATED BY ','
STORED AS TEXTFILE;

---

DROP TABLE

DROP TABLE [IF EXISTS] table_name [PURGE]; 

• IF NOT EXISTS : '이미 존재하는 경우'로 이미 삭제한 database_name이어도 오류가 발생하지 않음
• PURGE : 실제데이터는 .Trash로 이동되나 설정할 경우 완전히 삭제됨

 

DROP TABLE 예시

-- 가장 평범한 예시
DROP TABLE hivetable;

-- 이미 hivetable이라는 TABLE이 지웠더라도 오류가 발생하지 않음
DROP TABLE IF NOT EXISTS hivetable;

-- 쓰레기통으로 이동되지 않고 완전히 삭제됨
DROP TABLE hivetable PURGE;

---

TRUNCATE TABLE

TRUNCATE [TABLE] table_name [PARTITION partition_spec];

• TABLE : 생략가능하다
• PARTITION : 해당 파티션의 행을 제거

 

TRUNCATE TABLE 예시

-- 가장 평범한 예시
TRUNCATE hivetable;

-- dt파티션이 2020-01-01인 값의 행만 TRUNCATE
TRUNCATE hivetable PARTITION (dt='2020-01-01');

---

TABLE 관련 명령어

-- Table 조회
SHOW TABLES;

-- 특정 Table 조회
SHOW TABLES 'h*';

-- Table 정보 조회
DESCRIBE [EXTENDED|FORMATTED] 
  table_name[.col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];
  
DESCRIBE hivetable;

• EXTENDED : 모든 메타 데이터를 표시
• FORMATTED : 메타 데이터가 표 형식으로 표시

---

참조 : cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL