ETL(Extract, Transform, Load)작업을 통해 수집한 데이터와 이를 기반으로 생성한 테이블들이 있을 때 데이터 분석을 위한 테이블을 생성하는 한 방법으로 CTAS(Crate Table AS select) 방식과 CTE(Common Table Expression) 방식이 있는데 이 둘의 방식을 비교해보고, 각 방식의 사용 방법을 간단한 예시를 들어 비교해보고자 한다.
CTAS (Create Table AS select)
CTAS는 기존 데이터를 기반으로 새로운 테이블을 생성하는 방법이다.
이방식을 사용하면 데이터의 복사본을 만들고, 이를 통해 새로운 분석용 테이블을 쉽게 만들어 낼 수 있다.
이러한 방식을 통해 원본 데이터를 훼손하지 않고 새로운 테이블을 만들기 때문에 분석용 테이블, 백업용 테이블을 만들어 놓는데 사용되는 방식이다.
기본적인 사용방식은 다음과 같다.
CREATE TABLE analytics_table as
(
SELECT *
FROM customer_info as CI
JOIN product_info as PI
ON CI.ID = PI.ID
);이처럼 Select 구문으로 특정 테이블들의 정보를 불러와 이를 통해 새로운 테이블을 생성하는 방법으로 사용한다.
만약에 자주 분석에 사용되는 테이블구조가 있다면 위와 같이 CTAS 방식을 사용하여 분석용 테이블을 생성해놓는다면 원본 테이블의 데이터 훼손 없이 생성한 분석용 테이블을 이용하여 데이터 분석이 가능하다.
CTAS 구문의 특징.
-
생성되는 테이블의 구조
새로 생성되는 테이블의 구조는 AS 뒤 따라오는 SELECT 구문에 의해 결정된다. 그렇기 때문에 SELECT 구문을 통해 가져올 컬럼의 정보를 col1,col2.. 와같이 지정하거나 * 모든컬럼을 가져올 수 있다. 또한 이렇게 가져온 컬럼의 타입은 기존 테이블 컬럼의 타입과 동일하게 가져온다는 점을 기억해야 한다. -
원본 데이터 유지
앞서 설명했듯이 새로 생성되는 테이블은 기존 테이블의 구조를 조건적으로 복사하여 사용하기 때문에 기존 테이블의 데이터를 훼손 없이 복사해와 사용한다.
3.인덱스 및 제약조건
CTAS 구문을 통해 생성되는 테이블은 NOT NULL 을 제외한 기존 테이블의 인덱스나 제약조건을 상속받지 않는다. 그렇기 때문에 필요한 경우 CTAS 구문을 사용하여 테이블을 생성한 뒤 인덱스 및 제약조건을 걸어야한다.
4.데이터 타입 제한
LONG, LONG RAW 타입의 컬럼을 복사할경우 오류가 발생할 수 있다. ( CLOB, BLOB 타입의 컬럼 복사는 지원. )
LONG : VARCHAR 타입을 확장한 데이터 타입으로, 대용량 문자열을 저장할 수 있지만 한 테이블에 하나의 컬럼에만 선언할 수 있다.
LONG RAW : RAQ 타입을 확장한 데이터 타입으로 대용량 바이너리 데이터를 저장할 수 있지만 한 테이블에 하나의 컬럼에만 선언할 수 있다.
BLOB : 대용량의 바이너리 데이터를 저장하는 데이터 타입으로 한 테이블에서 여러 컬럼에 대해 선언할 수 있다.
CLOB : 대용량의 문자열 데이터를 저장하는 데이터 타입으로 한 테이블에서 여러 컬럼에 대해 선언할 수 있다.
CTE (Common Table Expressions)
Common Table Expression 으로 WITH 절을 이용하여 쿼리 내에서 일시적인 결과 집합을 생성하는 방법이다.
이는 주로 복잡한 쿼리를 단순화하고, 재사용을 가능하게 만들고 싶을 때 사용하는 방식이다.
즉 복잡한 쿼리를 단계별로 분리하여 가독성을 높이고, 재사용성을 높일 때 사용할 때 적합한 방식라고 볼 수 있다.
CTE와 서브쿼리를 두고 비교하는 경우가 많다. 왜냐하면 서브쿼리 또한 메인쿼리 내에서 정의되어 사용되기 때문에 문법적인 측면에서 본다면 큰 차이점을 못느낄 수 있다 하지만 서브쿼리와 CTE의 차이점은 재사용이 가능한지 불가능한지의 차이점이 있다. (가독성 또한 차이가 있을 수 있다.) 이러한 차이점이 CTE의 큰 장점이라고 볼 수 있다.
또한 VIEW 처럼 임시용 테이블로 생각할 수 있지만, VIEW 생성은 그에 맞는 권한이 주어져야만 하지만 CTE의 경우 권한이 필요 없으며 쿼리문이 끝날 때 까지만 지속되는 임시 테이블 성격을 가진다.
또한 재귀적인 구현을 할 때 CTE를 사용한다면 매우 유용하게 처리할 수 있다.
CTE의 기본적인 사용 방식은 다음과 같다.
WITH 테이블 이름 AS
( SELECT 칼럼이름 FROM 테이블 이름 )만약 다음과 같이 CTE 를 구성해서 사용했다고 가정해보자.
WITH sales_summary AS (
SELECT
product_id,
SUM(sales_amount) AS total_sales
FROM
sales_data
GROUP BY
product_id
)
SELECT
product_id,
total_sales
FROM
sales_summary
WHERE
total_sales > 1000;sales_data 테이블에서 각 제품의 판매총액을 계산하는 CTE를 정의하고, 그 결과에서 판매 총액이 1000을 넘기는 제품만 선택해서 보여주는 방법이다.
만약에 이를 CTE 방식을 사용하지 않고 서브쿼리로 구성했다면 다음과 같이 쿼리가 작성될 수 있다.
SELECT
product_id,
total_sales
FROM (
SELECT
product_id,
SUM(sales_amount) AS total_sales
FROM
sales_data
GROUP BY
product_id
) AS sales_summary
WHERE
total_sales > 1000;이처럼 서브쿼리로도 구성할 수 있지만, 가독성이 떨어지고(작성한 예시의 경우 별차이가 없다고 생각할 수 있지만.. 더 복잡한 쿼리의 경우 이런 차이가 더 부각될 것이다..) 테이블의 재사용성이 떨어진다는 단점이 있어서 이러한 경우에 CTE로 쿼리를 구성하면 이러한 단점을 개선할 수 있다.
Recurcive CTE
재귀적인 방식을 구현할 때 CTE가 매우 유용하다고 하였다.
재귀적이다 라는 뜻은 자기 자신을 계속하여 참조한다는 뜻으로, CTE는 이런 재귀적 구조를 구현하는데 매우 유용하다.
재귀적 CTE는 주로 계층적 데이터 구조를 처리하거나, 반복적인 계산을 수행할 때 사용하기에 적합하다.
1) 계층적 데이터 구조?
조직도, 제품 카테고리, 파일 시스템 등과 같이 부모-자식 관계가 있는 데이터
2) 반복적인 계산?
피보나치 수열과 같이 수학적 계싼이나 특정 조건을 만족하는 데이터를 찾기 위한 경우 등이 이에 해당한다고 볼 수 있다.
다음과 같은 예시가 있다고 해보자
WITH RECURSIVE EmployeeHierarchy AS (
-- Anchor Member: 최상위 직원 선택
SELECT
employee_id,
employee_name,
supervisor_id
FROM
employees
WHERE
supervisor_id IS NULL -- 상사가 없는 최상위 직원
UNION ALL
-- Recursive Member: 하위 직원 선택
SELECT
e.employee_id,
e.employee_name,
e.supervisor_id
FROM
employees e
INNER JOIN
EmployeeHierarchy eh ON e.supervisor_id = eh.employee_id
)
SELECT
*
FROM
EmployeeHierarchy;- Anchor Member 부분 : supervisor_id 가 NULl인 상사가 없는 최상위 직원을 선택한다.
- Recursive Member 부분 : EmployeeHierarchy 라고 CTE로 생성한 테이블에서 이미 선택된 employee_id를 사용하여 하위 직원들을 계속해서 선택한다.
이처럼 재귀적으로 계층적인 데이터를 조회하거나, 반복적인 계산을 해야하는 경우 효과적으로 계산할 수 있는 유용한 도구이다.
