[ 살펴보기 ] SQL - View

View는 일종의 가상 테이블을 의미한다. 실제로 데이터를 저장하고 있지는 않기에 디스크 공간을 차지할 우려는 없다. 실제 테이블은 공개하지 않고 View table을 이용해 다른 테이블이나 다른 view에 저장되어 있는 데이터를 보여줄 수 있다.

예를 들어 address 테이블 중 address 칼럼에 저장된 데이터의 일부를 숨겨야 한다면 이를 위한 view 테이블을 생성하여 원본 테이블이 아닌 view 테이블을 통해 데이터에 접근하도록 조치할 수 있을 것이다. 다음 예제를 살펴보자.

CREATE VIEW address_vw
(
  address_id,
  address,
  district,
  city_id
)
AS 
SELECT
  address_id,
  concat('***', substr(address,3)),
  district,
  city_id
FROM address

위의 예제에서는 address 테이블을 기준으로 view 테이블을 만든다. view 테이블를 생성할 때 view 테이블의 이름은 원본 테이블과 동일할 수 없다. 이제 생성한 view table을 조회해보자.

SELECT * FROM address_vw

위의 예제에서 볼 수 있듯이 view 테이블의 모든 address column은 앞의 글자가 ***로 대체된다. 이와 같이 view table을 생성하여 원본 테이블이 아닌 view 테이블을 제공하면 데이터를 사용하는 유저는 우리가 의도한 대로 일부 데이터를 숨긴 가상 테이블을 통해 데이터를 조회하게 된다.

View table을 사용하는 입장에서 view table은 일반 table과 다를게 없다. 일반 table을 query를 하듯이 group by, having, order by 등의 statement를 통해 원하는 query 결과를 얻을 수 있다.

물론 where statement를 통해 특정 filter를 적용한 결과로 view table을 만들 수도 있다.

CREATE VIEW address_vw
(
  address_id,
  address_test,
  district,
  city_id
)
AS 
SELECT
  address_id,
  concat('***', substr(address,3)),
  district,
  city_id
FROM address
WHERE address_id > 20

위의 예제는 address_id가 20 이상인 데이터를 기준으로 view table를 생성한다.

View를 통한 데이터 보안

위에서 살펴 보았듯이 기존 테이블의 데이터 일부를 가리는 view table을 생성해서 원본 테이블 대신 view table을 제공함으로서 민감한 정보의 노출을 최소화 할 수 있다. 예를들어 회원의 전화번호 뒷 자리를 가리는 view table을 만들어 사용할 수 도 있을 것이다.

CREATE VIEW users_vw
(
  id,
  name,
  age,
  address,
  phone
)
AS 
SELECT
  id,
  name,
  age,
  address,
  concat(substr(phone,1,3), '****', substr(phone,-4)) AS phone
FROM users

위의 예제에서 생성한 view table을 조회 해보면 아래와 같이 phone 칼럼의 중간은 **** 가려져 보이는 것을 확인할 수 있다. 이렇게 데이터 일부를 가리거나 혹은 view table을 생성할 때 민감한 데이터를 관리하는 칼럼을 제외하는 방법을 통해 데이터 보안을 높일 수 있는 수단이 된다.

CREATE OR REPLACE

이미 동일한 view table이 있는 상태에서 Create를 하려고 하면 sql 서버에서 오류가 발생한다. 이때 기존에 존재하는 view table을 새로운 view table로 교체하고자 한다면 CREATE OR REPLACE 문법을 사용할 수 있다.

CREATE OR REPLACE VIEW users_vw
(
  id,
  name,
  age,
  address,
  phone
)
AS 
SELECT
  id,
  concat(substr(name,1,1), '*', substr(name,-1)) AS name,
  age,
  address,
  concat(substr(phone,1,3), '****', substr(phone,-4)) AS phone
FROM users

위의 query를 실행하면 users_vw가 존재하지 않으면 새로 생성하고 기존에 users_vw가 있으면 위의 내용으로 교체한다

복잡한 Query를 View로 단순화

만약 여러 table을 조인해서 집계를 해야 하는 경우에도 view table을 만들어 작업을 단순화할 수 있다.

CREATE VIEW payment_category_vw
AS
SELECT c.category_title AS category, 
sum(p.amount) AS sum_payment 
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id  = r.rental_id 
INNER JOIN category AS c ON c.category_id  = r.category_id
GROUP BY c.category_title

위의 예제와 같이 복합한 query를 view로 만들어 view table만 제공하면 불필요한 복잡성을 숨길 수 있고 view table 뒤에 가려진 복잡한 query를 간단하게 재사용할 수 있다.

View table 업데이트

view table을 생성할 때 사용한 기본 table을 통한 데이터 업데이트가 아닌 view table로 데이터를 업데이트 하고자 한다면 많은 제약 사항이 따른다.

MySQL 기준 view table을 업데이트 하기 위해선 다음 조건이 충족되어야 한다

• view는 union, union all을 사용하지 않았다.

• select 또는 from statement에 서브쿼리가 없다.

• from statement에는 최소 하나 이상의 테이블이 포함되었다.

• min(), max()와 같은 집계 함수가 사용되지 않았다.

• view에 group by 또는 having statement를 사용하지 않았다.