Redshift - CTAS & CTE, 테이블(데이터) 품질 확인 방법

 

CTAS: SELECT를 가지고 테이블을 생성

데이터 분석을 함에 있어서 많이 사용되는 테이블(예를 들면 join된 테이블)을 미리 만들어 둘 때 흔히 사용됩니다.

 

DROP TABLE IF EXISTS adhoc.keeyong_session_summary;

CREATE TABLE adhoc.keeyong_session_summary AS
SELECT B.*, A.ts FROM raw_data.session_timestamp A
JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid;

 

 

Redshift는 스키마로 raw_data, analytics, adhoc 세 가지 폴더(DB)로 나뉜다고 했습니다. 그리고 각 폴더에 테이블들을 생성해 사용합니다.

 

만일 adhoc 폴더에 keeyong_session_summary가 존재한다면(IF EXISTS) 드랍합니다.

그 다음 B(user_session_channel)테이블의 모든 컬럼과 A(session_timestamp)테이블의 ts 컬럼을 inner join한 테이블을 adhoc 폴더에 keeyong_session_summary라는 테이블로 생성합니다.

 

이렇게 미리 join한 테이블을 만들었기 때문에

 

SELECT 
  TO_CHAR(ts, 'YYYY-MM') AS month,
  COUNT(DISTINCT userid) AS mau
FROM adhoc.keeyong_session_summary
GROUP BY 1 
ORDER BY 1 DESC;

별도의 join 구문 없이 더 깔끔한 쿼리문을 작성할 수 있게되었습니다.

 

 

 

 

 

 

항상 시도해봐야하는 데이터 품질 확인 방법들

새로운 테이블을 사용할 때마다 이 테이블 데이터의 품질이 좋은지 나쁜지 반드시 확인해야 한다고 했었습니다.

 

 

체크리스트 4가지

1. 중복된 레코드들 체크하기

2. 최근 데이터의 존재 여부 체크하기 (freshness)

    오랜 기간 새로운 레코드가 없었다면 문제가 있을 가능성이 높습니다.

3. Primary key uniquenesss가 지켜지는지 체크하기

    데이터 웨어하우스는 Primary key uniquenesss를 보장해주지 않으므로 별도로 확인해야 합니다.

4. 값이 비어있는 컬럼들이 있는지 체크하기

    Null이 있을 수도 있겠지만 중요한 컬럼(매출 등)이 Null이라면 문제가 있는 것입니다.

 

 

현재 실습에 사용하는 user_session_channel과 session_timestamp 테이블들은 위와 같은 이슈가 없이 잘 만들어진 테이블입니다.

 

하지만 앞으로 새로운 테이블들을 만나면 위와 같은 4가지 체크리스트로 테이블을 꼭 확인하는 습관을 들이길 바랍니다.

 

 

 

 

 

 

1. 중복된 레코드들 체크하기(1)

 

 

모든 레코드 수와

SELECT COUNT(1) FROM adhoc.keeyong_session_summary;

 

 

중복을 제거한 레코드의

SELECT COUNT(1)
FROM (
    SELECT DISTINCT userId, sessionId, ts, channel FROM adhoc.keeyong_session_summary
);

 

 

카운트수를 비교합니다. 두 방법의 카운트수가 같다면 중복이 없는 것이고 반대로 중복이 존재한다면 두번째 방법의 카운트수가 첫번째 방법의 카운트수보다 적게 나올 것입니다.

 

또 한가지 보시면 좋은 것은 FROM 안에 별도의 SELECT로 nesting을 해줄 수 있습니다. FROM 뒤에 꼭 개별적인 테이블이 아닌 SELECT 쿼리문이 붙어도 전혀 문제 없습니다.

 

 

 

 

1. 중복된 레코드들 체크하기(2)

CTE를 사용해서 중복 제거후 카운트 해보기

중복된 레코드들을 체크하는 김에 CTE(Common Table Expressions)에 대해 간략하게 살펴보겠습니다.

 

 

 

SELECT COUNT(1)
FROM (
    SELECT DISTINCT userId, sessionId, ts, channel FROM adhoc.keeyong_session_summary
);

 

위와 같이 FROM 안에 nesting 하는 방법 대신에

 

 

 

다음과 같이

With ds AS (
  SELECT DISTINCT userId, sessionId, ts, channel FROM adhoc.keeyong_session_summary
)
SELECT COUNT(1)
FROM ds;

 

위로 빼는 방법을 CTE라고 합니다.

With 다음 새로운 테이블명(ds)를 주고 AS 뒤에 괄호안에 SELECT 문을 제공합니다.

 

FROM의 nesting과 CTE의 차이점

CTE를 사용하면 With로 만든 새로운 테이블(ds)을 뒤에서 계속 재사용할 수 있어 편리합니다.

 

 

 

 

 

 

2. 최근 데이터의 존재 여부 체크하기 (freshness)

 

SELECT MIN(ts), MAX(ts)
FROM adhoc.keeyong_session_summary;

 

timestamp 타입의 컬럼을 찾아 MIN과 MAX 함수를 이용하면 최근 데이터 존재 여부를 확인할 수 있습니다.

과거의 데이터들을 살펴보는 목적이라면 크게 문제가 없겠지만 실시간으로 사용 중이거나 얼마전까지 사용했던 테이블임에도 최근데이터가 없다면 문제가 있는 것입니다.

 

 

 

 

 

 

 

3. Primary key uniqueness가 지켜지는지 체크하기

데이터 웨어하우스는 Primary key uniqueness를 보장하지 않습니다.

그러므로 별도의 체크를 해줘야한다고 말씀드렸습니다.

 

SELECT sessionId, COUNT(1)
FROM adhoc.keeyong_session_summary
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

 

Primary key 필드인 sessionId를 GROUP BY로 그룹핑합니다.

sessionId필드와 그 필드의 COUNT를 SELECT합니다.

COUNT 수별로 내림차순합니다.

LIMIT을 1로 둔 이유는 COUNT 내림차순이 최대인 경우만 확인하면 그만이기 때문입니다.

 

이 때 COUNT 컬럼이 1 이상이라면 중복된 값이 있다는 뜻입니다.

COUNT 내림차순시 LIMIT 1 즉, COUNT가 가장 높은 경우가 1이라면 그 아래는 더 볼 필요가 없습니다.

 

 

 

 

 

4. 값이 비어있는 컬럼들이 있는지 체크하기

 

SELECT
    COUNT(CASE WHEN sessionId is NULL THEN 1 END) sessionid_null_count,
    COUNT(CASE WHEN userId is NULL THEN 1 END) userid_null_count,
    COUNT(CASE WHEN ts is NULL THEN 1 END) ts_null_count,
    COUNT(CASE WHEN channel is NULL THEN 1 END) channel_null_count
FROM adhoc.keeyong_session_summary;

 

COUNT안의 CASE WHEN 구문은 필드가 NULL인 경우만 COUNT합니다.

예를 들어 (CASE WHEN sessionId is NULL THEN 1 END)은 sessionId가 NULL인 경우 1을 리턴하고 그렇지 않다면 (여기서는 ELSE를 주지 않았기 때문에) NULL을 리턴합니다. COUNT에서 NULL이 리턴될시 이를 계산하지 않으므로 sessionId가 NULL이 아닌 경우만 카운트한 값이 나옵니다.