Teradata NORMALIZE 임시 기능을 Amazon Redshift로 변환 SQL

Po Hong에서 생성(AWS)

소스: Teradata 데이터 웨어하우스	대상: Amazon Redshift	R 타입: 리아키텍트
환경: 프로덕션	기술: 분석, 데이터베이스, 마이그레이션	워크로드: 기타 모든 워크로드
AWS 서비스: Amazon Redshift

요약

NORMALIZE 는 ANSI SQL 표준의 Teradata 확장입니다. SQL 테이블에 PERIOD 데이터 형식이 있는 열이 포함된 경우 는 해당 열에서 충족되거나 겹치는 값을 NORMALIZE 결합하여 여러 개별 기간 값을 통합하는 단일 기간을 형성합니다. 를 사용하려면 SQL SELECT 목록에서 NORMALIZE하나 이상의 열이 Teradata의 시간 데이터 PERIOD 유형이어야 합니다. 에 대한 자세한 내용은 Teradata 설명서 섹션을 NORMALIZE참조하세요.

Amazon Redshift는 를 지원하지 NORMALIZE않지만 Amazon Redshift에서 SQL 기본 구문과 LAG 창 함수를 사용하여 이 기능을 구현할 수 있습니다. 이 패턴은 가장 인기 있는 형식인 ON MEETS OR OVERLAPS 조건에서 Teradata NORMALIZE 확장을 사용하는 데 중점을 둡니다. 이 기능은 Teradata에서 작동하는 방식과 Amazon Redshift SQL 네이티브 구문으로 변환할 수 있는 방법을 설명합니다.

사전 조건 및 제한 사항

사전 조건

기본 Teradata SQL 지식 및 경험
Amazon Redshift에 대한 지식과 경험

아키텍처

소스 기술 스택

Teradata 데이터 웨어하우스

대상 기술 스택

Amazon Redshift

대상 아키텍처

Teradata 데이터베이스를 Amazon Redshift로 마이그레이션하기 위한 고급 아키텍처는 AWS SCT 데이터 추출 에이전트를 사용하여 Teradata 데이터베이스를 Amazon Redshift로 마이그레이션하는 패턴을 참조하세요. 마이그레이션은 Teradata NORMALIZE 구문을 Amazon Redshift 로 자동 변환하지 않습니다SQL. 이 패턴의 지침에 따라 이 테라데이타 확장을 변환할 수 있습니다.

도구

코드

의 개념과 기능을 설명하려면 Teradata에서 다음 테이블 정의를 NORMALIZE고려하세요.


CREATE TABLE systest.project
     (    emp_id        INTEGER,
          project_name  VARCHAR(20),
          dept_id       INTEGER,
          duration      PERIOD(DATE)
     );

다음 SQL 코드를 실행하여 샘플 데이터를 테이블에 삽입합니다.


BEGIN TRANSACTION;

INSERT INTO systest.project VALUES (10, 'First Phase', 1000,  PERIOD(DATE '2010-01-10', DATE '2010-03-20') );
INSERT INTO systest.project VALUES (10, 'First Phase', 2000,  PERIOD(DATE '2010-03-20', DATE '2010-07-15') );

INSERT INTO systest.project VALUES (10, 'Second Phase', 2000,  PERIOD(DATE '2010-06-15', DATE '2010-08-18') );
INSERT INTO systest.project VALUES (20, 'First Phase', 2000,  PERIOD(DATE '2010-03-10', DATE '2010-07-20') );

INSERT INTO systest.project VALUES (20, 'Second Phase', 1000,  PERIOD(DATE '2020-05-10', DATE '2020-09-20') );

END TRANSACTION;

결과:


select * from systest.project order by 1,2,3;
 
 *** Query completed. 4 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.
 
     emp_id  project_name              dept_id  duration
-----------  --------------------  -----------  ------------------------
         10  First Phase                  1000  ('10/01/10', '10/03/20')        
         10  First Phase                  2000  ('10/03/20', '10/07/15')
         10  Second Phase                 2000  ('10/06/15', '10/08/18')
         20  First Phase                  2000  ('10/03/10', '10/07/20')
         20  Second Phase                 1000  ('20/05/10', '20/09/20')

Teradata NORMALIZE 사용 사례

이제 SELECT 문에 Teradata NORMALIZE SQL 절을 추가합니다.


SELECT NORMALIZE ON MEETS OR OVERLAPS emp_id, duration 
FROM systest.project 
ORDER BY 1,2;

이 NORMALIZE 작업은 단일 열(emp_id)에서 수행됩니다. emp_id=10의 경우 다음과 같이 기간에서 겹치는 세 개의 기간 값이 단일 기간 값으로 합쳐집니다.


     emp_id  duration
-----------  ------------------------
         10  ('10/01/10', '10/08/18')
         20  ('10/03/10', '10/07/20')
         20  ('20/05/10', '20/09/20')

다음 SELECT 문은 project_name 및 dept_id에서 NORMALIZE 작업을 수행합니다. SELECT 목록에는 하나의 PERIOD 열, 기간만 포함됩니다.


SELECT NORMALIZE project_name, dept_id, duration 
FROM systest.project;

출력:


project_name              dept_id  duration
--------------------  -----------  ------------------------
First Phase                  1000  ('10/01/10', '10/03/20')
Second Phase                 1000  ('20/05/10', '20/09/20')
First Phase                  2000  ('10/03/10', '10/07/20')
Second Phase                 2000  ('10/06/15', '10/08/18')

Amazon Redshift 동급 SQL

Amazon Redshift는 현재 테이블의 PERIOD 데이터 유형을 지원하지 않습니다. 대신 Teradata PERIOD 데이터 필드를 다음과 같이 start_date, end_date의 두 부분으로 나누어야 합니다.


CREATE TABLE systest.project
     (    emp_id        INTEGER,
          project_name  VARCHAR(20),
          dept_id       INTEGER,
          start_date  DATE,
          end_date    DATE
     );

다음 테이블에 샘플 데이터를 삽입합니다.


BEGIN TRANSACTION;
 
INSERT INTO systest.project VALUES (10, 'First Phase', 1000,  DATE '2010-01-10', DATE '2010-03-20' );
INSERT INTO systest.project VALUES (10, 'First Phase', 2000,  DATE '2010-03-20', DATE '2010-07-15');
 
INSERT INTO systest.project VALUES (10, 'Second Phase', 2000,  DATE '2010-06-15', DATE '2010-08-18' );
INSERT INTO systest.project VALUES (20, 'First Phase', 2000,  DATE '2010-03-10', DATE '2010-07-20' );
 
INSERT INTO systest.project VALUES (20, 'Second Phase', 1000,  DATE '2020-05-10', DATE '2020-09-20' );
 
END TRANSACTION;

출력:


 emp_id | project_name | dept_id | start_date |  end_date
--------+--------------+---------+------------+------------
     10 | First Phase  |    1000 | 2010-01-10 | 2010-03-20
     10 | First Phase  |    2000 | 2010-03-20 | 2010-07-15
     10 | Second Phase |    2000 | 2010-06-15 | 2010-08-18
     20 | First Phase  |    2000 | 2010-03-10 | 2010-07-20
     20 | Second Phase |    1000 | 2020-05-10 | 2020-09-20
(5 rows)

Teradata의 NORMALIZE 절을 다시 작성하려면 Amazon Redshift에서 LAG 창 함수를 사용할 수 있습니다. 이 함수는 파티션에서 현재 행 위(앞)의 지정 오프셋에 위치한 행의 값을 반환합니다.

LAG 함수를 사용하여 기간이 이전 기간(예인 경우 0, 아니요인 경우 1)과 만나거나 겹치는지 확인하여 새 기간을 시작하는 각 행을 식별할 수 있습니다. 이 플래그를 누적하여 합산하면 Amazon Redshift에서 원하는 결과에 도달하기 위해 외부 그룹별 절에서 사용할 수 있는 그룹 식별자가 제공됩니다.

다음은 LAG()를 사용하는 Amazon Redshift SQL 문 샘플입니다.


SELECT emp_id, start_date, end_date, 
            (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY emp_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag
FROM systest.project 
ORDER BY 1,2;

출력:


 emp_id | start_date |  end_date  | groupstartflag
--------+------------+------------+----------------
     10 | 2010-01-10 | 2010-03-20 |              1
     10 | 2010-03-20 | 2010-07-15 |              0
     10 | 2010-06-15 | 2010-08-18 |              0
     20 | 2010-03-10 | 2010-07-20 |              1
     20 | 2020-05-10 | 2020-09-20 |              1
(5 rows)

다음 Amazon Redshift SQL 문은 emp_id 열에서만 정규화됩니다.


SELECT T2.emp_id, MIN(T2.start_date) as new_start_date, MAX(T2.end_date) as new_end_date
FROM 
( SELECT T1.*, SUM(GroupStartFlag) OVER (PARTITION BY emp_id ORDER BY start_date ROWS UNBOUNDED PRECEDING) As GroupID
FROM ( SELECT emp_id, start_date, end_date, 
            (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY emp_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag
FROM systest.project ) T1
) T2
GROUP BY T2.emp_id, T2.GroupID
ORDER BY 1,2;

출력:


 emp_id | new_start_date | new_end_date
--------+----------------+------------------------------------
     10 | 2010-01-10     | 2010-08-18
     20 | 2010-03-10     | 2010-07-20
     20 | 2020-05-10     | 2020-09-20
(3 rows)

다음 Amazon Redshift SQL 문은 project_name 열과 dept_id 열 모두에서 정규화됩니다.


SELECT T2.project_name, T2.dept_id, MIN(T2.start_date) as new_start_date, MAX(T2.end_date) as new_end_date
FROM 
( SELECT T1.*, SUM(GroupStartFlag) OVER (PARTITION BY project_name, dept_id ORDER BY start_date ROWS UNBOUNDED PRECEDING) As GroupID
FROM ( SELECT project_name, dept_id, start_date, end_date, 
            (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY project_name, dept_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag
FROM systest.project ) T1
) T2
GROUP BY T2.project_name, T2.dept_id, T2.GroupID
ORDER BY 1,2,3;

출력:


 project_name | dept_id | new_start_date | new_end_date
--------------+---------+----------------+--------------
 First Phase  |    1000 | 2010-01-10     | 2010-03-20
 First Phase  |    2000 | 2010-03-10     | 2010-07-20
 Second Phase |    1000 | 2020-05-10     | 2020-09-20
 Second Phase |    2000 | 2010-06-15     | 2010-08-18
(4 rows)

에픽

작업	설명	필요한 기술
Teradata SQL 코드를 생성합니다.	필요에 따라 NORMALIZE 구문을 사용합니다.	SQL 개발자
코드를 Amazon Redshift 로 변환합니다SQL.	코드를 변환하려면 이 패턴의 ‘도구’ 섹션에 있는 지침을 따르세요.	SQL 개발자
Amazon Redshift에서 코드를 실행합니다.	테이블을 생성하고, 테이블에 데이터를 로드하고, Amazon Redshift에서 코드를 실행합니다.	SQL 개발자