[Hive] 뷰(View), 갭슐화(encapsulation)

 

 

 

 

--------------------------------
-- HiveQL : View
--------------------------------

 

-- (Before) 중첩 구조를 가지는 복잡한 구조 (complicated multi-layer structure without VIEW)

FROM (
 SELECT *
  FROM table_1 a JOIN table_2 b ON (a.prd_id = b.prd_id)
  WHERE year = '2016' AND month = '06'
) ab
SELECT ab.prd_id, prd_cnt WHERE ab.price >= 10000;

 

-- (After) View를 사용해 간소화한 구조 (simplified structure with VIEW (encapsulation))
CREATE VIEW table_view AS
SELECT *
 FROM table_1 a JOIN table_2 b ON (a.prd_id = b.prd_id)
 WHERE year = '2016' AND month = '06';

SELECT prd_id, prd_cnt FROM table_view
 WHERE price >= 10000;

 

-- 보안을 위해 조건을 기반으로 데이터를 제한하는 view (condition-based view for security)
CREATE TABLE prd_master (prd_id string, prd_nm string, prd_price INT);

CREATE VIEW prd_master_view AS
SELECT prd_id, prd_nm, prd_price FROM prd_master
 WHERE prd_nm = 'ABC';

 

 

 

-- MAP 데이터형과 VIEW

 -- 데이터 형태
ts=1298598378404/code=403/message=bad referrer: bizo.com/...

 -- MAP 테이블 생성
CREATE EXTERNAL TABLE api_logs(d map<string,string>)
  PARTITIONED BY (...)
  ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\004'
    COLLECTION ITEMS TERMINATED BY '\001'
    MAP KEYS TERMINATED BY '\002'
  STORED AS TEXTFILE;

 -- reorder view 생성
CREATE VIEW IF NOT EXISTS api_errors(ts, code, message)
COMMENT 'api error messages'
TBLPROPERTIES ('creator' = 'HongKilDong')
AS SELECT d["ts"], d["code"], d["message"]
    FROM api_logs
    WHERE d["code"] >= 400;

 

 

 

-- VIEW 확인
SHOW TABLES; -- 'SHOW VIEWS;' is NOT WORKING
DESCRIBE api_errors;

 

-- VIEW 삭제
DROP VIEW IF EXISTS api_errors;