수천 명의 고객이 의존하고 있습니다. 아마존 레드 시프트 데이터 웨어하우스를 구축하여 빠르고 간단하며 안전한 규모의 분석을 통해 통찰력을 얻는 시간을 단축하고 복잡한 분석 쿼리를 실행하여 테라바이트에서 페타바이트에 이르는 데이터를 분석합니다. 조직 생성 데이터 마트, 데이터 웨어하우스의 하위 집합이며 일반적으로 비즈니스 단위 또는 팀과 관련된 분석 통찰력을 얻는 데 중점을 둡니다. 그만큼 스타 스키마 데이터 마트 구축에 널리 사용되는 데이터 모델입니다.

이 게시물에서는 Amazon Redshift에서 천천히 변화하는 유형 2 차원으로의 데이터 로드를 단순화하는 방법을 보여줍니다.

스타 스키마 및 천천히 변화하는 차원 개요

스타 스키마는 가장 단순한 유형의 스키마입니다. 차원 모델, 별의 중심은 하나를 가질 수 있습니다 사실 테이블 그리고 관련된 여러 치수 테이블. 차원은 관련 계층과 함께 참조 데이터를 캡처하는 구조인 반면 팩트 테이블은 차원별로 집계할 수 있는 다양한 값과 메트릭을 캡처합니다. 차원은 최종 사용자가 친숙한 SQL 명령을 사용하여 다양한 방식으로 데이터를 조각화할 수 있도록 하여 탐구적인 비즈니스 질문에 대한 답변을 제공합니다.

운영 소스 시스템에는 최신 버전의 마스터 데이터, 스타 스키마를 사용하면 시간 여행 쿼리가 팩트 트랜잭션 또는 이벤트가 실제로 발생한 과거 날짜의 차원 특성 값을 재현할 수 있습니다. 별 모양 스키마 데이터 모델을 사용하면 분석 사용자가 시간 경과에 따라 메트릭을 해당 차원 속성 값에 연결하는 기록 데이터를 쿼리할 수 있습니다. 차원 테이블에는 서로 다른 시간 범위에서 연결된 속성의 정확한 버전이 포함되어 있기 때문에 시간 여행이 가능합니다. 매일 또는 시간 단위로 계속 변경되는 지표 데이터에 비해 차원 속성은 덜 자주 변경됩니다. 따라서 시간이 지남에 따라 변경 사항을 추적하는 스타 스키마의 차원을 차원이라고 합니다. 천천히 변화하는 차원 (SCD).

데이터 로드는 데이터 웨어하우스 유지 관리의 핵심 측면 중 하나입니다. 스타 스키마 데이터 모델에서 중앙 팩트 테이블은 주변 차원 테이블에 종속됩니다. 이는 기본 키-외래 키 관계의 형태로 캡처되며 차원 테이블 기본 키는 팩트 테이블의 외래 키에 의해 참조됩니다. Amazon Redshift의 경우 고유성, 기본 키 및 외래 키 제약 조건 시행되지 않는다. 그러나 이를 선언하면 데이터 로드 프로세스가 무결성을 적용하는 경우 옵티마이저가 최적의 쿼리 계획에 도달하는 데 도움이 됩니다. 데이터 로드의 일부로 SCD 테이블을 포함한 차원 테이블이 먼저 로드된 다음 팩트 테이블이 로드됩니다.

SCD 인구 도전

SCD 차원 테이블 채우기에는 일반적으로 여러 소스 테이블의 데이터 병합이 포함됩니다. 정규화. SCD 테이블에는 레코드의 유효 날짜 범위를 나타내는 한 쌍의 날짜 열(유효 날짜 및 만료 날짜)이 포함되어 있습니다. 변경 사항은 데이터 로드 날짜부터 유효한 새 활성 레코드로 삽입되는 동시에 전날의 현재 활성 레코드를 만료합니다. 각 데이터 로드 중에 들어오는 변경 레코드는 기존 활성 레코드와 일치하고 각 속성 값을 비교하여 기존 레코드가 변경되었는지 삭제되었는지 또는 새 레코드가 들어오는지 확인합니다.

이 게시물에서는 다음 방법을 사용하여 차원 테이블에 데이터 로드를 단순화하는 방법을 보여줍니다.

• 사용 아마존 단순 스토리지 서비스 (Amazon S3) 소스 시스템 테이블의 초기 및 증분 데이터 파일 호스팅
• 다음을 사용하여 S3 객체에 액세스 아마존 레드시프트 스펙트럼 Amazon Redshift 내에서 기본 테이블을 로드하기 위한 데이터 처리 수행
• Amazon Redshift 내에서 각 테이블의 소스 시스템 버전을 복제하는 창 기능으로 보기 생성
• 차원 테이블 스키마와 일치하는 프로젝트 속성에 소스 테이블 보기 조인
• 증분 데이터를 차원 테이블에 적용하여 소스 측 변경으로 최신 상태로 유지

솔루션 개요

실제 시나리오에서 소스 시스템 테이블의 레코드는 Amazon Redshift의 스타 스키마 테이블에 로드되기 전에 주기적으로 Amazon S3 위치로 수집됩니다.

이 데모에서는 두 소스 테이블의 데이터, customer_master 과 customer_address, 결합되어 대상 차원 테이블을 채웁니다. dim_customer, 이는 고객 차원 테이블입니다.

소스 테이블 customer_master 과 customer_address 동일한 기본 키를 공유하고 customer_id, 당 하나의 레코드를 가져오기 위해 동일하게 조인됩니다. customer_id 두 테이블의 속성과 함께. row_audit_ts 특정 소스 레코드가 삽입되었거나 마지막으로 업데이트된 최신 타임스탬프를 포함합니다. 이 열은 마지막 데이터 추출 이후 변경 레코드를 식별하는 데 도움이 됩니다.

rec_source_status 해당 소스 레코드가 삽입, 업데이트 또는 삭제되었는지 여부를 나타내는 선택적 열입니다. 이것은 소스 시스템 자체가 변경 사항을 제공하고 채우는 경우에 적용됩니다. rec_source_status 적절하게.

다음 그림은 원본 및 대상 테이블의 스키마를 제공합니다.

대상 테이블의 스키마를 자세히 살펴보겠습니다. dim_customer. 여기에는 다양한 범주의 열이 포함됩니다.

• 키 – 두 가지 유형의 키가 포함되어 있습니다.
  • customer_sk 이 테이블의 기본 키입니다. 그것은 또한 대리 키 단조롭게 증가하는 고유한 값을 가집니다.
  • customer_id 소스 기본 키이며 소스 시스템 레코드에 대한 참조를 다시 제공합니다.
• SCD2 메타데이터 - rec_eff_dt 과 rec_exp_dt 레코드의 상태를 나타냅니다. 이 두 열은 함께 레코드의 유효성을 정의합니다. 의 값 rec_exp_dt 로 설정됩니다 ‘9999-12-31’ 현재 활성 레코드의 경우.
• Attributes – 포함 first_name, last_name, employer_name, email_id, city및 country.

SCD 테이블로의 데이터 로드에는 최초 대량 데이터 로드가 포함됩니다. 초기 데이터 로드. 그런 다음 연속 또는 정기적인 데이터 로드가 이어집니다. 증분 데이터 로드, 소스 테이블의 변경 사항으로 레코드를 최신 상태로 유지합니다.

솔루션을 시연하기 위해 초기 데이터 로드(1–7) 및 증분 데이터 로드(8–12)에 대해 다음 단계를 수행합니다.

1. 원본 테이블당 하나의 하위 폴더를 사용하여 원본 데이터 파일을 Amazon S3 위치에 배치합니다.
2. 사용 AWS 접착제 데이터 파일을 구문 분석하고 AWS Glue 데이터 카탈로그에 테이블을 등록하는 크롤러.
3. 이러한 테이블이 포함된 AWS Glue 데이터베이스를 가리키도록 Amazon Redshift에서 외부 스키마를 생성합니다.
4. Amazon Redshift에서 소스 테이블당 하나의 보기를 생성하여 각 기본 키(customer_id) 값.
5. 만들기 dim_customer 모든 관련 소스 테이블의 속성을 포함하는 Amazon Redshift의 테이블.
6. 차원 테이블에서 모델링된 속성을 투영하기 위해 4단계의 소스 테이블 보기를 조인하는 Amazon Redshift에서 보기를 생성합니다.
7. 6단계에서 만든 보기의 초기 데이터를 dim_customer 테이블, 생성 customer_sk.
8. 각각의 Amazon S3 위치에 각 소스 테이블에 대한 증분 데이터 파일을 배치합니다.
9. Amazon Redshift에서 변경 전용 레코드를 수용할 임시 테이블을 생성합니다.
10. 6단계에서 보기에 참여하고 dim_customer 속성의 결합된 해시 값을 비교하여 변경 레코드를 식별합니다. 변경 레코드를 임시 테이블에 I, U및 D 표시.
11. 업데이트 rec_exp_dt in dim_customer 모두에게 U 과 D 임시 테이블의 레코드.
12. 다음에 레코드 삽입 dim_customer, 모두 쿼리 I 과 U 임시 테이블의 레코드.

사전 조건

시작하기 전에 다음 전제 조건을 충족하는지 확인하십시오.

소스 테이블의 토지 데이터

S3 버킷의 각 소스 테이블에 대해 별도의 하위 폴더를 생성하고 각 하위 폴더 내에 초기 데이터 파일을 배치합니다. 다음 이미지에서 초기 데이터 파일은 customer_master 과 customer_address 두 개의 서로 다른 하위 폴더 내에서 사용할 수 있습니다. 솔루션을 사용해 보려면 다음을 사용할 수 있습니다. 고객_마스터_with_ts.csv 과 고객_주소_with_ts.csv 초기 데이터 파일로.

감사 타임스탬프(row_audit_ts) 각 레코드가 삽입되거나 마지막으로 업데이트된 시기를 나타내는 열. 증분 데이터 로드의 일부로 동일한 기본 키 값(customer_id) 두 번 이상 도착할 수 있습니다. 그만큼 row_audit_ts 열은 해당 레코드의 최신 버전을 식별하는 데 도움이 됩니다. customer_id 추가 처리에 사용됩니다.

AWS Glue 데이터 카탈로그에 소스 테이블 등록

AWS Glue 크롤러를 사용하여 이 게시물에 사용된 CSV 파일과 같이 구분된 데이터 파일에서 메타데이터를 추론합니다. AWS Glue 크롤러 시작에 대한 지침은 다음을 참조하십시오. 자습서: AWS Glue 크롤러 추가.

AWS Glue 크롤러를 생성하고 연결된 데이터 파일이 배치된 원본 테이블 하위 폴더가 포함된 Amazon S3 위치를 가리킵니다. AWS Glue 크롤러를 생성할 때 이름이 지정된 새 데이터베이스를 생성합니다. rs-dimension-blog. 다음 스크린샷은 데이터 파일에 대해 선택된 AWS Glue 크롤러 구성을 보여줍니다.

참고로 출력 및 일정 설정 섹션에서 고급 옵션은 변경되지 않습니다.

이 크롤러를 실행하면 다음 테이블이 생성되어야 합니다. rs-dimension-blog 데이터 베이스:

• customer_address
• customer_master

Amazon Redshift에서 스키마 생성

먼저, 생성 AWS 자격 증명 및 액세스 관리 (IAM) 역할 이름 지정 rs-dim-blog-spectrum-role. 지침은 다음을 참조하십시오. Amazon Redshift에 대한 IAM 역할 생성.

IAM 역할에는 신뢰할 수 있는 엔터티로 Amazon Redshift가 있으며 권한 정책에는 다음이 포함됩니다. AmazonS3ReadOnlyAccess 과 AWSGlueConsoleFullAccess, AWS Glue 데이터 카탈로그를 사용하고 있기 때문입니다. 그 다음에 IAM 역할 연결 Amazon Redshift 클러스터 또는 엔드포인트와

대신 다음을 설정할 수도 있습니다. IAM 역할을 기본값으로 Amazon Redshift 클러스터 또는 엔드포인트용. 그렇게 하면 다음에서 create external schema 명령, 통과 iam_role 매개 변수 iam_role default.

이제 Amazon Redshift Query Editor V2를 열고 새로 생성된 IAM 역할을 전달하고 데이터베이스를 다음과 같이 지정하는 외부 스키마를 생성합니다. rs-dimension-blog. 데이터베이스 이름 rs-dimension-blog 이전 섹션에서 크롤러 구성의 일부로 Data Catalog에서 생성된 것입니다. 다음 코드를 참조하십시오.

create external schema spectrum_dim_blog from data catalog database 'rs-dimension-blog' iam_role 'arn:aws:iam::<accountid>:role/rs-dim-blog-spectrum-role';

이전 섹션에서 Data Catalog에 등록된 테이블이 Amazon Redshift 내에서 보이는지 확인합니다.

select * from spectrum_dim_blog.customer_master limit 10; select * from spectrum_dim_blog.customer_address limit 10;

이러한 각 쿼리는 각각의 Data Catalog 테이블에서 10개의 행을 반환합니다.

Amazon Redshift에서 다른 스키마를 생성하여 테이블을 호스팅합니다. dim_customer:

create schema rs_dim_blog;

각 소스 테이블에서 최신 레코드를 가져오는 보기 만들기

다음에 대한 보기를 만듭니다. customer_master 테이블, 이름 지정 vw_cust_mstr_latest:

create view rs_dim_blog.vw_cust_mstr_latest as with rows_numbered as ( select customer_id, first_name, last_name, employer_name, row_audit_ts, row_number() over( partition by customer_id order by row_audit_ts desc ) as rnum from spectrum_dim_blog.customer_master
) select customer_id, first_name, last_name, employer_name, row_audit_ts, rnum from rows_numbered where rnum = 1 with no schema binding;

앞의 쿼리는 다음을 사용합니다. 행 번호, Amazon Redshift에서 제공하는 윈도우 함수입니다. 창 함수를 사용하면 분석 비즈니스 쿼리를 보다 효율적으로 만들 수 있습니다. 창 함수는 결과 집합의 파티션에서 작동하고 해당 창의 모든 행에 대한 값을 반환합니다. 그만큼 row_number 창 함수는 OVER 절의 ORDER BY 식에 따라 1부터 세는 행 그룹 내 현재 행의 서수를 결정합니다. PARTITION BY 절을 다음과 같이 포함함으로써 customer_id, 각 값에 대해 그룹이 생성됩니다. customer_id 각 그룹에 대해 서수가 재설정됩니다.

다음에 대한 보기를 만듭니다. customer_address 테이블, 이름 지정 vw_cust_addr_latest:

create view rs_dim_blog.vw_cust_addr_latest as with rows_numbered as ( select customer_id, email_id, city, country, row_audit_ts, row_number() over( partition by customer_id order by row_audit_ts desc ) as rnum from spectrum_dim_blog.customer_address
) select customer_id, email_id, city, country, row_audit_ts, rnum from rows_numbered where rnum = 1 with no schema binding;

두 보기 정의 모두 row_number Amazon Redshift의 창 기능, 내림차순으로 레코드 정렬 row_audit_ts 열(감사 타임스탬프 열). 조건 rnum=1 각각의 최신 레코드를 가져옵니다. customer_id 값.

Amazon Redshift에서 dim_customer 테이블 생성

만들기 dim_customer 내 Amazon Redshift의 내부 테이블로 rs_dim_blog 개요. 차원 테이블에는 열이 포함됩니다. customer_sk, 대리 키 열 역할을 하며 각 고객 레코드의 시간에 민감한 버전을 캡처할 수 있습니다. 각 레코드의 유효 기간은 열로 정의됩니다. rec_eff_dt 과 rec_exp_dt, 각각 레코드 유효 날짜 및 레코드 만료 날짜를 나타냅니다. 다음 코드를 참조하십시오.

create table rs_dim_blog.dim_customer ( customer_sk bigint, customer_id bigint, first_name varchar(100), last_name varchar(100), employer_name varchar(100), email_id varchar(100), city varchar(100), country varchar(100), rec_eff_dt date, rec_exp_dt date
) diststyle auto;

소스 레코드의 최신 버전을 통합하는 보기 만들기

보기 만들기 vw_dim_customer_src, 다음을 사용하여 두 원본 테이블의 최신 레코드를 통합합니다. left outer join, Amazon Redshift 차원 테이블에 채울 수 있도록 준비합니다. 이 보기는 "각 원본 테이블에서 최신 레코드를 가져오기 위한 보기 만들기" 섹션에 정의된 최신 보기에서 데이터를 가져옵니다.

create view rs_dim_blog.vw_dim_customer_src as select m.customer_id, m.first_name, m.last_name, m.employer_name, a.email_id, a.city, a.country from rs_dim_blog.vw_cust_mstr_latest as m left join rs_dim_blog.vw_cust_addr_latest as a on m.customer_id = a.customer_id order by m.customer_id with no schema binding;

이 시점에서 이 뷰는 로드할 초기 데이터를 가져옵니다. dim_customer 우리가 만들려는 테이블. 사용 사례에서 유사한 접근 방식을 사용하여 필요한 소스 테이블 보기를 만들고 조인하여 대상 차원 테이블을 채웁니다.

dim_customer에 초기 데이터 채우기

초기 데이터를 dim_customer 뷰를 쿼리하여 테이블 vw_dim_customer_src. 이것은 초기 데이터 로드이므로 row_number 창 함수는 고유한 값을 채우기에 충분합니다. customer_sk 1부터 시작하는 열:

insert into rs_dim_blog.dim_customer select row_number() over() as customer_sk, customer_id, first_name, last_name, employer_name, email_id, city, country, cast('2022-07-01' as date) rec_eff_dt, cast('9999-12-31' as date) rec_exp_dt from rs_dim_blog.vw_dim_customer_src;

이 쿼리에서 지정했습니다. ’2022-07-01’ 값으로 rec_eff_dt 모든 초기 데이터 레코드에 대해. 사용 사례의 경우 상황에 맞게 이 날짜 값을 수정할 수 있습니다.

이전 단계는 초기 데이터 로드를 완료합니다. dim_customer 테이블. 다음 단계에서는 증분 데이터 채우기를 진행합니다.

Amazon S3에 지속적인 변경 데이터 파일 저장

초기 로드 후 소스 시스템은 신규 및 변경 레코드만 포함하거나 특정 테이블에 대한 모든 레코드를 포함하는 전체 추출을 포함하는 데이터 파일을 지속적으로 제공합니다.

샘플 파일을 사용할 수 있습니다. customer_master_with_ts_incr.csv 과 고객_주소_with_ts_incr.csv, 새 레코드뿐만 아니라 변경된 레코드도 포함합니다. 이러한 증분 파일은 초기 데이터 파일이 배치된 Amazon S3의 동일한 위치에 배치되어야 합니다. 섹션을 참조하십시오.소스 테이블의 토지 데이터". 그러면 해당 Redshift Spectrum 테이블이 자동으로 추가 행을 읽게 됩니다.

샘플 파일을 사용한 경우 customer_master증분 파일을 추가한 후 다음 쿼리는 초기 레코드와 증분 레코드를 보여줍니다.

select customer_id, first_name, last_name, employer_name, row_audit_ts from spectrum_dim_blog.customer_master order by customer_id;

전체 추출의 경우 이전 버전과 현재 버전을 비교하고 누락된 레코드를 찾아 소스 시스템 테이블에서 발생하는 삭제를 식별할 수 있습니다. 변경 전용 추출의 경우 rec_source_status 열이 있으면 해당 값은 삭제된 레코드를 식별하는 데 도움이 됩니다. 두 경우 모두 진행 중인 변경 데이터 파일을 각각의 Amazon S3 위치에 배치합니다.

이 예에서는 다음에 대한 증분 데이터를 업로드했습니다. customer_master 과 customer_address 몇 가지 소스 테이블 customer_id 업데이트를 수신하는 레코드와 몇 가지 새 레코드가 추가됩니다.

변경 기록을 캡처하기 위한 임시 테이블 만들기

임시 테이블 생성 temp_dim_customer 대상에 적용해야 하는 모든 변경 사항을 저장하기 위해 dim_customer 표:

create temp table temp_dim_customer ( customer_sk bigint, customer_id bigint, first_name varchar(100), last_name varchar(100), employer_name varchar(100), email_id varchar(100), city varchar(100), country varchar(100), rec_eff_dt date, rec_exp_dt date, iud_operation character(1)
);

신규 및 변경된 레코드로 임시 테이블 채우기

이는 복잡한 단일 SQL로 결합할 수 있는 다단계 프로세스입니다. 다음 단계를 완료하십시오.

1. 뷰를 쿼리하여 모든 고객 속성의 최신 버전을 가져옵니다. vw_dim_customer_src:

select customer_id, sha2( coalesce(first_name, '') || coalesce(last_name, '') || coalesce(employer_name, '') || coalesce(email_id, '') || coalesce(city, '') || coalesce(country, ''), 512 ) as hash_value, first_name, last_name, employer_name, email_id, city, country, current_date rec_eff_dt, cast('9999-12-31' as date) rec_exp_dt from rs_dim_blog.vw_dim_customer_src;

Amazon Redshift는 다음과 같은 해싱 기능을 제공합니다. sha2, 가변 길이 문자열 입력을 고정 길이 문자 출력으로 변환합니다. 출력 문자열은 지정된 비트 수를 가진 체크섬의 512진수 값을 텍스트로 표현한 것입니다. 이 경우 비트 수를 XNUMX로 지정하여 변경 사항을 추적하려는 연결된 고객 속성 집합을 전달합니다. 해시 함수의 출력을 사용하여 변경된 속성이 있는지 확인합니다. 이 데이터 세트는 newver (새로운 버전).

진행 중인 변경 데이터를 초기 데이터 파일과 동일한 위치에 배치했기 때문에 이전 쿼리에서 검색된 레코드( newver) 변경되지 않은 레코드를 포함한 모든 레코드를 포함합니다. 그러나 보기의 정의 때문에 vw_dim_customer_src, 우리는 고객 ID 당 하나의 레코드만 얻습니다. 이는 최신 버전입니다. row_audit_ts.

2. 유사한 방식으로 다음에서 모든 고객 레코드의 최신 버전을 검색합니다. dim_customer로 식별됩니다. rec_exp_dt=‘9999-12-31’. 그렇게 하는 동안 sha2 에서 사용 가능한 모든 고객 속성의 가치 dim_customer:

select customer_id, sha2( coalesce(first_name, '') || coalesce(last_name, '') || coalesce(employer_name, '') || coalesce(email_id, '') || coalesce(city, '') || coalesce(country, ''), 512 ) as hash_value, first_name, last_name, employer_name, email_id, city, country from rs_dim_blog.dim_customer where rec_exp_dt = '9999-12-31';

이 데이터 세트는 oldver (이전 또는 기존 버전).

3. 에서 현재 최대 대리 키 값을 식별합니다. dim_customer 표:

select max(customer_sk) as maxval from rs_dim_blog.dim_customer;

이 값(maxval)에 추가됩니다. row_number 로 사용되기 전에 customer_sk 삽입해야 하는 변경 레코드의 값입니다.

4. 레코드의 이전 버전에 대한 전체 외부 조인을 수행합니다(oldver) 및 새 버전(newver)에 대한 기록 customer_id 열. 그런 다음 생성된 이전 해시 값과 새 해시 값을 비교합니다. sha2 변경 레코드가 삽입, 업데이트 또는 삭제인지 확인하는 함수:

case when oldver.customer_id is null then 'I'
when newver.customer_id is null then 'D'
when oldver.hash_value != newver.hash_value then 'U'
else 'N' end as iud_op

다음과 같이 레코드에 태그를 지정합니다.

• 경우 customer_id 에 존재하지 않는다 oldver 데이터세트(oldver.customer_id is null) 삽입으로 태그가 지정됩니다(‘I').
• 그렇지 않으면 customer_id 에 존재하지 않는다 newver 데이터세트(newver.customer_id is null), 삭제(‘D').
• 그렇지 않으면 오래된 경우 hash_value 및 새로운 hash_value 다르면 이러한 레코드는 업데이트를 나타냅니다(‘U').
• 그렇지 않으면 레코드가 변경되지 않았으므로 무시하거나 처리되지 않음으로 표시할 수 있음을 나타냅니다(‘N').

소스 추출에 다음이 포함된 경우 앞의 논리를 수정해야 합니다. rec_source_status 삭제된 레코드를 식별합니다.

이기는하지만 sha2 output은 무한할 수 있는 입력 문자열 집합을 유한한 출력 문자열 집합에 매핑하므로 원래 행 값과 변경된 행 값에 대한 해시 값이 충돌할 가능성은 거의 없습니다. 각각의 열 값 전후를 개별적으로 비교하는 대신에 생성된 해시 값을 비교합니다. sha2 고객 레코드의 속성에 변경 사항이 있는지 결론을 내립니다. 사용 사례에 따라 다음을 선택하는 것이 좋습니다. 해시 함수 적절한 테스트 후 데이터 조건에 맞게 작동합니다. 대신 해시 함수가 기대에 부합하지 않는 경우 개별 열 값을 비교할 수 있습니다.

5. 이전 단계의 출력을 결합하여 변경 레코드만 캡처하여 임시 테이블을 채우는 INSERT 문을 만들어 보겠습니다.

insert into temp_dim_customer ( customer_sk, customer_id, first_name, last_name, employer_name, email_id, city, country, rec_eff_dt, rec_exp_dt, iud_operation
) with newver as ( select customer_id, sha2( coalesce(first_name, '') || coalesce(last_name, '') || coalesce(employer_name, '') || coalesce(email_id, '') || coalesce(city, '') || coalesce(country, ''), 512 ) as hash_value, first_name, last_name, employer_name, email_id, city, country, current_date rec_eff_dt, cast('9999-12-31' as date) rec_exp_dt from rs_dim_blog.vw_dim_customer_src
), oldver as ( select customer_id, sha2( coalesce(first_name, '') || coalesce(last_name, '') || coalesce(employer_name, '') || coalesce(email_id, '') || coalesce(city, '') || coalesce(country, ''), 512 ) as hash_value, first_name, last_name, employer_name, email_id, city, country from rs_dim_blog.dim_customer where rec_exp_dt = '9999-12-31'
), maxsk as ( select max(customer_sk) as maxval from rs_dim_blog.dim_customer
), allrecs as ( select coalesce(oldver.customer_id, newver.customer_id) as customer_id, case when oldver.customer_id is null then 'I' when newver.customer_id is null then 'D' when oldver.hash_value != newver.hash_value then 'U' else 'N' end as iud_op, newver.first_name, newver.last_name, newver.employer_name, newver.email_id, newver.city, newver.country, newver.rec_eff_dt, newver.rec_exp_dt from oldver full outer join newver on oldver.customer_id = newver.customer_id
) select (maxval + (row_number() over())) as customer_sk, customer_id, first_name, last_name, employer_name, email_id, city, country, rec_eff_dt, rec_exp_dt, iud_op from allrecs, maxsk where iud_op != 'N';

업데이트된 고객 기록 만료

와 더불어 temp_dim_customer 이제 변경 레코드만 포함하는 테이블(또는 ‘I’, ‘U’및 ‘D’) 대상에도 동일하게 적용할 수 있습니다. dim_customer 테이블.

먼저 값이 있는 모든 레코드를 가져오겠습니다. ‘U’ or ‘D’ FBI 증오 범죄 보고서 iud_op 열. 소스 시스템에서 삭제되거나 업데이트된 레코드입니다. 왜냐하면 dim_customer 천천히 변화하는 차원이므로 각 고객 레코드의 유효 기간을 반영해야 합니다. 이 경우 업데이트 또는 삭제된 현재 활성 기록을 만료시킵니다. 어제부로 이 기록을 만료합니다(설정하여 rec_exp_dt=current_date-1) 일치 customer_id 기둥:

update rs_dim_blog.dim_customer set rec_exp_dt = current_date - 1 where customer_id in ( select customer_id from temp_dim_customer as t where iud_operation in ('U', 'D') ) and rec_exp_dt = '9999-12-31';

새 레코드 및 변경된 레코드 삽입

마지막 단계로 모든 최초 삽입과 함께 최신 버전의 업데이트된 레코드를 삽입해야 합니다. 이들은 다음과 같이 표시됩니다. ‘U’ 과 ‘I’, 각각 iud_op 열 temp_dim_customer 표:

insert into rs_dim_blog.dim_customer ( customer_sk, customer_id, first_name, last_name, employer_name, email_id, city, country, rec_eff_dt, rec_exp_dt
) select customer_sk, customer_id, first_name, last_name, employer_name, email_id, city, country, rec_eff_dt, rec_exp_dt from temp_dim_customer where iud_operation in ('I', 'U');

SQL 클라이언트 설정에 따라 다음을 실행할 수 있습니다. commit transaction; 이전 변경 사항이 Amazon Redshift에서 성공적으로 유지되는지 확인하는 명령입니다.

최종 출력 확인

다음 쿼리를 실행하고 다음을 확인할 수 있습니다. dim_customer 이제 테이블에는 초기 데이터 레코드와 증분 데이터 레코드가 모두 포함되어 있으며 이에 대한 여러 버전을 캡처합니다. customer_id 증분 데이터 로드의 일부로 변경된 값입니다. 출력은 또한 각 레코드가 적절한 값으로 채워졌음을 나타냅니다. rec_eff_dt 과 rec_exp_dt 기록 유효 기간에 해당합니다.

select * from rs_dim_blog.dim_customer order by customer_id, customer_sk;

이 문서에서 제공하는 샘플 데이터 파일의 경우 앞의 쿼리는 다음 레코드를 반환합니다. 이 게시물에 제공된 샘플 데이터 파일을 사용하는 경우 customer_sk 다음 표에 표시된 것과 일치하지 않을 수 있습니다.

이 게시물에서는 중요한 SQL 문만 보여줍니다. 전체 SQL 코드는 다음에서 사용할 수 있습니다. load_scd2_sample_dim_customer.sql.

정리

생성한 리소스가 더 이상 필요하지 않은 경우 추가 요금이 발생하지 않도록 삭제할 수 있습니다.

결론

이 게시물에서는 Amazon Redshift의 Type-2 SCD 테이블로 데이터 로드를 단순화하는 방법을 배웠으며 초기 데이터 로드와 증분 데이터 로드를 모두 다룹니다. 이 접근 방식은 대상 차원 테이블을 채우는 여러 원본 테이블을 처리하여 각 실행에서 최신 버전의 원본 레코드를 캡처합니다.

인용하다 Amazon Redshift 데이터 로드 모범 사례 추가 자료 및 추가 모범 사례는 다음을 참조하십시오. 새 데이터 업데이트 및 삽입 업데이트 및 삽입 구현 지침

저자에 관하여

베이디 칼파시 AWS의 선임 데이터 랩 솔루션 아키텍트로서 고객이 데이터 플랫폼을 현대화하도록 돕고 데이터 수집, 변환, 보안, 시각화를 포함한 엔드 투 엔드 데이터 전략을 정의합니다. 그는 비즈니스 사용 사례에서 거꾸로 작업하여 고객이 AWS에서 데이터 분석 서비스를 사용하여 혁신할 수 있도록 확장 가능한 맞춤형 아키텍처를 만드는 데 열정적입니다.

• SEO 기반 콘텐츠 및 PR 배포. 오늘 증폭하십시오.
• 플라토 블록체인. Web3 메타버스 인텔리전스. 지식 증폭. 여기에서 액세스하십시오.
• 출처: https://aws.amazon.com/blogs/big-data/simplify-data-loading-into-type-2-slowly-changing-dimensions-in-amazon-redshift/