[Hive] select * from table 에서 필드 일부만 제외하는 방법? - 블랙리스트 방식

select 문을 사용할때 특정 필드만 선택할 경우는 필드명을 나열하면 된다. 근데 필드가 어마어마하게 많은데 1개 필드만 제외해서 select 하고 싶은 경우 어떻게 해야할지 고민이 된다. (쉽게 말해서 화이트 리스트로 필드를 골라내는게 아니라, 블랙 리스트로 필드를 골라내고 싶다는말)

 

예를 들어, 모든 필드를 조회할때는 select * from t_sample_users 로 간소화가 가능하다.

하지만 여기서 tel_num, ymd 만 제외해서 조회하려면 어떻게 해야할까? 가장 기본적인 방법은 아래 방법처럼 필요한 필드를 모두 나열하는 방법이다. (=화이트 리스트 방식)

 

하지만 필드가 너무 많다면 이건 너무 비효율적이다. 그럼 블랙리스트 방식으로 안쓰는 필드만 발라내는 방법은 어떻게 해야할까???

hive> CREATE TABLE IF NOT EXISTS t_sample_users (
  id string,
  name string,
  address string,  
  age int,
  telnum string,
  gender string
)
PARTITIONED BY (ymd string)
STORED AS ORC
TBLPROPERTIES ("orc.compress"="ZLIB");

hive> INSERT INTO t_sample_users VALUES ('1', '홍길동', '서울', 17, '02-111-2222', '남자', '2021-01-01'), ('2', '둘리', '제주', 110000, '010-111-2222', '남자', '2021-01-01') ;
No rows affected (0.004 seconds)

hive>  select * from t_sample_users;
+--------------------+----------------------+-------------------------+---------------------+------------------------+------------------------+---------------------+
| t_sample_users.id  | t_sample_users.name  | t_sample_users.address  | t_sample_users.age  | t_sample_users.telnum  | t_sample_users.gender  | t_sample_users.ymd  |
+--------------------+----------------------+-------------------------+---------------------+------------------------+------------------------+---------------------+
| 1                  | 홍길동                  | 서울                      | 17                  | 02-111-2222            | 남자                     | 2021-01-01          |
| 2                  | 둘리                   | 제주                      | 110000              | 010-111-2222           | 남자                     | 2021-01-01          |
+--------------------+----------------------+-------------------------+---------------------+------------------------+------------------------+---------------------+
2 rows selected (0.376 seconds)

--------------------------------------
-- telnum, ymd 필드를 제외하고 조회하려면?
-- 아래와 같이 필드를 모두 나열해야하는데 더 간결하게 안될까?
--------------------------------------

hive> select id, name, address, age, telnum, gender from t_sample_users;
+-----+-------+----------+---------+---------+
| id  | name  | address  |   age   | gender  |
+-----+-------+----------+---------+---------+
| 1   | 홍길동   | 서울       | 17      | 남자      |
| 2   | 둘리    | 제주       | 110000  | 남자      |
+-----+-------+----------+---------+---------+
2 rows selected (0.352 seconds)

내가 간소화된 방법이 필요했던 이유는, 파티션 필드를 제외해서 select 를 해야하는데 매번 필드를 나열하는게 너무 단순반복되었기 때문이다. (파티션필드는 보통 ymd 같은걸로 고정인데)

해결방법

hive 에서는 hive.support.quoted.identifiers 옵션을 활용하면 된다.

기본값은 "column" 인데 이 값은 `` 사이의 값을 "문자열 그대로 사용하는 형태"로 인식하는 형태로 동작한다. 

하지만,  "none" 으로 선언하면 `` 사이의 값은 정규식 표현식으로 해석하게 되고, 화이트 리스트 방식뿐 아니라 블랙리스트 형태로 특정 필드만 제외해서 조회하는게 가능하다.

 

이해를 돕기위해 위의 예시에서 telnum 과 ymd 필드를 제외하여 조회하는걸 예시정리했다.

hive> set hive.support.quoted.identifiers=none;
No rows affected (0.004 seconds)

hive> select `(telnum|ymd)?+.+` from t_sample_users;
+--------------------+----------------------+-------------------------+---------------------+------------------------+
| t_sample_users.id  | t_sample_users.name  | t_sample_users.address  | t_sample_users.age  | t_sample_users.gender  |
+--------------------+----------------------+-------------------------+---------------------+------------------------+
| 1                  | 홍길동                  | 서울                      | 17                  | 남자                     |
| 2                  | 둘리                   | 제주                      | 110000              | 남자                     |
+--------------------+----------------------+-------------------------+---------------------+------------------------+
2 rows selected (0.334 seconds)

좀더 자세한 내용이 보고싶다면 아래 링크를 참고하자

https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties#ConfigurationProperties-hive.support.quoted.identifiers 

Configuration Properties - Apache Hive - Apache Software Foundation

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select  (REGEX Column Specification 절)

LanguageManual Select - Apache Hive - Apache Software Foundation