Amazon Redshift의 차원 모델링 | 아마존 웹 서비스

Amazon Redshift의 차원 모델링 | 아마존 웹 서비스

타임 스탬프 : 19 년 2023 월 3 일 오후 34:XNUMX
소스 노드 : 2778508

아마존 레드 시프트 수만 명의 고객이 매일 엑사바이트 규모의 데이터를 처리하여 분석 워크로드를 강화하는 데 사용하는 완전히 관리되는 페타바이트 규모의 클라우드 데이터 웨어하우스입니다. 차원 모델을 사용하여 데이터를 구조화하고, 비즈니스 프로세스를 측정하고, 귀중한 통찰력을 빠르게 얻을 수 있습니다. Amazon Redshift는 차원 모델에서 모델링, 오케스트레이션 및 보고 프로세스를 가속화하는 내장 기능을 제공합니다.

이 게시물에서는 차원 모델, 특히 킴볼 방법론. Amazon Redshift 내에서 구현 차원과 팩트에 대해 논의합니다. 모델링을 수행하기 위해 데이터 레이크의 원시 데이터를 준비 계층으로 가져오는 데 중점을 둔 통합 프로세스인 ELT(추출, 변환 및 로드)를 수행하는 방법을 보여줍니다. 전반적으로 게시물은 Amazon Redshift에서 차원 모델링을 사용하는 방법에 대한 명확한 이해를 제공합니다.

솔루션 개요

다음 다이어그램은 솔루션 아키텍처를 보여줍니다.

다음 섹션에서는 먼저 차원 모델의 주요 측면에 대해 논의하고 시연합니다. 그런 다음 차원 및 팩트 테이블을 포함하는 차원 데이터 모델과 함께 Amazon Redshift를 사용하여 데이터 마트를 생성합니다. 데이터는 다음을 사용하여 로드되고 준비됩니다. 복사 명령을 실행하면 차원의 데이터가 다음을 사용하여 로드됩니다. 합병 진술과 사실은 통찰이 도출되는 차원에 결합될 것입니다. 다음을 사용하여 차원 및 사실의 로드를 예약합니다. Amazon Redshift 쿼리 편집기 V2. 마지막으로 우리는 아마존 퀵 사이트 QuickSight 대시보드의 형태로 모델링된 데이터에 대한 통찰력을 얻습니다.

이 솔루션의 경우 이벤트 티켓 판매를 위해 Amazon Redshift에서 제공하는 샘플 데이터 세트(정규화됨)를 사용합니다. 이 게시물에서는 단순성과 데모 목적으로 데이터 세트를 좁혔습니다. 다음 표는 티켓 판매 및 장소에 대한 데이터의 예를 보여줍니다.

이에 따르면 Kimball 차원 모델링 방법론, 차원 모델 설계에는 네 가지 주요 단계가 있습니다.

  1. 비즈니스 프로세스를 식별합니다.
  2. 데이터의 입자를 선언합니다.
  3. 차원을 식별하고 구현합니다.
  4. 사실을 확인하고 구현하십시오.

또한 비즈니스 이벤트를 보고하고 분석하는 데모 목적의 다섯 번째 단계를 추가합니다.

사전 조건

이 연습에서는 다음과 같은 전제 조건이 있어야합니다.

비즈니스 프로세스 식별

간단히 말해서 비즈니스 프로세스를 식별하는 것은 조직 내에서 데이터를 생성하는 측정 가능한 이벤트를 식별하는 것입니다. 일반적으로 회사에는 원시 형식으로 데이터를 생성하는 일종의 운영 소스 시스템이 있습니다. 이는 비즈니스 프로세스의 다양한 소스를 식별하기 위한 좋은 출발점입니다.

그런 다음 비즈니스 프로세스는 데이터 마트 차원과 사실의 형태로. 앞에서 언급한 샘플 데이터 세트를 보면 비즈니스 프로세스가 주어진 이벤트에 대한 판매라는 것을 분명히 알 수 있습니다.

일반적인 실수는 회사의 부서를 비즈니스 프로세스로 사용하는 것입니다. 데이터(비즈니스 프로세스)는 여러 부서에 걸쳐 통합되어야 하며, 이 경우 마케팅은 판매 데이터에 액세스할 수 있습니다. 올바른 비즈니스 프로세스를 식별하는 것이 중요합니다. 이 단계를 잘못 수행하면 전체 데이터 마트에 영향을 미칠 수 있습니다(결국이 중복되고 최종 보고서에서 잘못된 지표가 될 수 있음).

데이터의 입자 선언

그레인 선언은 데이터 소스의 레코드를 고유하게 식별하는 작업입니다. 그레인은 데이터를 정확하게 측정하고 추가로 롤업할 수 있도록 팩트 테이블에서 사용됩니다. 이 예에서 이는 판매 비즈니스 프로세스의 라인 항목일 수 있습니다.

사용 사례에서 판매가 발생한 거래 시간을 확인하여 판매를 고유하게 식별할 수 있습니다. 이것은 가장 원자 수준이 될 것입니다.

차원 식별 및 구현

차원 테이블은 팩트 테이블과 해당 속성을 설명합니다. 비즈니스 프로세스의 설명 컨텍스트를 식별할 때 팩트 테이블 그레인을 염두에 두고 별도의 테이블에 텍스트를 저장합니다. 차원 테이블을 팩트 테이블에 조인할 때 팩트 테이블에 연결된 행은 하나만 있어야 합니다. 이 예에서는 다음 테이블을 사용하여 차원 테이블로 분리합니다. 이러한 필드는 우리가 측정할 사실을 설명합니다.

차원 모델(스키마)의 구조를 설계할 때 별 or 눈송이 개요. 구조는 비즈니스 프로세스와 밀접하게 일치해야 합니다. 따라서 스타 스키마가 이 예제에 가장 적합합니다. 다음 그림은 엔터티 관계 다이어그램(ERD)을 보여줍니다.

다음 섹션에서는 차원을 구현하는 단계를 자세히 설명합니다.

소스 데이터 준비

치수 테이블을 생성하고 로드하려면 소스 데이터가 필요합니다. 따라서 원본 데이터를 스테이징 또는 임시 테이블로 준비합니다. 이것은 종종 다음과 같이 불립니다. 준비 계층, 원본 데이터의 원시 복사본입니다. Amazon Redshift에서 이를 수행하기 위해 다음을 사용합니다. 복사 명령 에 위치한 dimensional-modeling-in-amazon-redshift 퍼블릭 S3 버킷에서 데이터를 로드합니다. us-east-1 지역. COPY 명령은 AWS 자격 증명 및 액세스 관리 (IAM) 역할 Amazon S3에 대한 액세스. 역할은 클러스터와 관련된. 소스 데이터를 준비하려면 다음 단계를 완료하십시오.

  1. 만들기 venue 소스 테이블:
CREATE TABLE public.venue ( venueid bigint, venuename character varying(100), venuecity character varying(30), venuestate character(2), venueseats bigint
) DISTSTYLE AUTO SORTKEY (venueid);

  1. 장소 데이터를 로드합니다.
COPY public.venue
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/venue.csv'
IAM_ROLE '<Your IAM role arn>'
DELIMITER ','
REGION 'us-east-1'
IGNOREHEADER 1

  1. 만들기 sales 소스 테이블:
CREATE TABLE public.sales (
    salesid integer,
    venueid character varying(256),
    saletime timestamp without time zone,
    qtysold BIGINT,
    commission numeric(18,2),
    pricepaid numeric(18,2)
) DISTSTYLE AUTO;

  1. 판매 소스 데이터를 로드합니다.
COPY public.sales
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/sales.csv'
IAM_ROLE '<Your IAM role arn>'
DELIMITER ','
REGION 'us-east-1'
IGNOREHEADER 1

  1. 만들기 calendar 표:
CREATE TABLE public.DimCalendar(
    dateid smallint,
        caldate date,
        day varchar(20),
        week smallint,
        month varchar(20),
        qtr varchar(20),
        year smallint,
        holiday boolean
) DISTSTYLE AUTO
SORTKEY
    (dateid);

  1. 캘린더 데이터를 로드합니다.
COPY public.DimCalendar
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/date.csv'
IAM_ROLE '<Your IAM role arn>'
DELIMITER ',' 
REGION 'us-east-1'
IGNOREHEADER 1

차원 테이블 만들기

차원 테이블 디자인은 비즈니스 요구 사항에 따라 달라질 수 있습니다. 예를 들어 시간 경과에 따른 데이터 변경 사항을 추적해야 합니까? 있다 XNUMX가지 차원 유형. 이 예에서는 다음을 사용합니다. 1을 입력 역사적 변화를 추적할 필요가 없기 때문입니다. 유형 2에 대한 자세한 내용은 다음을 참조하십시오. Amazon Redshift에서 유형 2의 천천히 변화하는 차원으로의 데이터 로드 간소화. 차원 테이블은 기본 키, 서로게이트 키 및 테이블 변경 사항을 나타내는 몇 가지 추가 필드로 비정규화됩니다. 다음 코드를 참조하십시오.

create schema SalesMart;

CREATE TABLE SalesMart.DimVenue( 
    "VenueSkey" int IDENTITY(1,1) primary key
    ,"VenueId" VARCHAR NOT NULL
    ,"VenueName" VARCHAR NULL
    ,"VenueCity" VARCHAR NULL
    ,"VenueState" VARCHAR NULL
    ,"VenueSeats" INT NULL
    ,"InsertedDate" DATETIME NOT NULL
    ,"UpdatedDate" DATETIME NOT NULL
) 
diststyle AUTO;

차원 테이블 생성 생성에 대한 몇 가지 참고 사항:

  • 필드 이름은 비즈니스 친화적인 이름으로 변환됩니다.
  • 우리의 기본 키는 VenueID, 판매가 발생한 장소를 고유하게 식별하는 데 사용합니다.
  • 레코드가 삽입되고 업데이트된 시기(변경 사항 추적을 위해)를 나타내는 두 개의 추가 행이 추가됩니다.
  • 우리는 자동 배포 스타일 Amazon Redshift에게 배포 스타일을 선택하고 조정할 책임을 부여합니다.

차원 모델링에서 고려해야 할 또 다른 중요한 요소는 대리 키. 서로게이트 키는 차원 테이블의 각 레코드를 고유하게 식별하기 위해 차원 모델링에 사용되는 인공 키입니다. 일반적으로 순차 정수로 생성되며 비즈니스 도메인에서는 의미가 없습니다. 일반적으로 자연 키보다 작고 시간이 지나도 변경되지 않는 대리 키이기 때문에 조인의 고유성 보장 및 성능 향상과 같은 여러 이점을 제공합니다. 이를 통해 일관성을 유지하고 사실과 차원을 더 쉽게 결합할 수 있습니다.

Amazon Redshift에서 서로게이트 키는 일반적으로 IDENTITY 키워드를 사용하여 생성됩니다. 예를 들어 앞의 CREATE 문은 차원 테이블을 생성합니다. VenueSkey 대리 키. 그만큼 VenueSkey 새 행이 테이블에 추가되면 열이 자동으로 고유한 값으로 채워집니다. 그런 다음 이 열을 사용하여 장소 테이블을 FactSaleTransactions 테이블.

대리 키 설계를 위한 몇 가지 팁:

  • 대리 키에 작은 고정 너비 데이터 유형을 사용하십시오. 이렇게 하면 성능이 향상되고 저장 공간이 줄어듭니다.
  • IDENTITY 키워드를 사용하거나 순차적 또는 GUID 값을 사용하여 서로게이트 키를 생성합니다. 이렇게 하면 대리 키가 고유하고 변경할 수 없습니다.

MERGE를 사용하여 Dim 테이블 로드

딤 테이블을 로드하는 방법에는 여러 가지가 있습니다. 예를 들어 성능, 데이터 볼륨 및 SLA 로드 시간과 같은 특정 요소를 고려해야 합니다. 와 더불어 합병 명령문을 사용하면 여러 삽입 및 업데이트 명령을 지정할 필요 없이 upsert를 수행합니다. 다음을 설정할 수 있습니다. 합병 진술 저장 프로 시저 데이터를 채울 수 있습니다. 그런 다음 쿼리 편집기를 통해 프로그래밍 방식으로 실행되도록 저장 프로시저를 예약합니다. 이에 대해서는 게시물 뒷부분에서 설명합니다. 다음 코드는 라는 저장 프로시저를 만듭니다. SalesMart.DimVenueLoad:

CREATE OR REPLACE PROCEDURE SalesMart.DimVenueLoad()
AS $$
BEGIN
MERGE INTO SalesMart.DimVenue USING public.venue as MergeSource
ON SalesMart.DimVenue.VenueId = MergeSource.VenueId
WHEN MATCHED
THEN
UPDATE
SET VenueName = ISNULL(MergeSource.VenueName, 'Unknown')
, VenueCity = ISNULL(MergeSource.VenueCity, 'Unknown')
, VenueState = ISNULL(MergeSource.VenueState, 'Unknown')
, VenueSeats = ISNULL(MergeSource.VenueSeats, -1)
, UpdatedDate = GETDATE()
WHEN NOT MATCHED
THEN
INSERT (
VenueId
, VenueName
, VenueCity
, VenueState
, VenueSeats
, UpdatedDate
, InsertedDate
)
VALUES (
ISNULL(MergeSource.VenueId, -1)
, ISNULL(MergeSource.VenueName, 'Unknown')
, ISNULL(MergeSource.VenueCity, 'Unknown')
, ISNULL(MergeSource.VenueState, 'Unknown')
, ISNULL(MergeSource.VenueSeats, -1)
, ISNULL(GETDATE() , '1900-01-01')
, ISNULL(GETDATE() , '1900-01-01')
);
END;
$$
LANGUAGE plpgsql;

차원 로드에 대한 몇 가지 참고 사항:

  • 레코드가 처음 삽입되면 삽입된 날짜와 업데이트된 날짜가 채워집니다. 값이 변경되면 데이터가 업데이트되고 업데이트된 날짜는 변경된 날짜를 반영합니다. 삽입된 날짜가 남아 있습니다.
  • 데이터는 비즈니스 사용자가 사용하므로 NULL 값이 있는 경우 비즈니스에 더 적합한 값으로 대체해야 합니다.

사실 확인 및 구현

곡물을 특정 시간에 발생한 판매 이벤트로 선언했으므로 팩트 테이블은 비즈니스 프로세스에 대한 숫자 팩트를 저장할 것입니다.

측정할 다음과 같은 수치적 사실을 확인했습니다.

  • 판매당 판매된 티켓 수량
  • 판매 수수료

사실 구현

다음의 세 가지 유형의 팩트 테이블 (트랜잭션 팩트 테이블, 주기적 스냅샷 팩트 테이블 및 누적 스냅샷 팩트 테이블). 각각은 비즈니스 프로세스의 다른 보기를 제공합니다. 이 예에서는 트랜잭션 팩트 테이블을 사용합니다. 다음 단계를 완료하십시오.

  1. 팩트 테이블 생성
CREATE TABLE SalesMart.FactSaleTransactions( 
    CalendarDate date NOT NULL
    ,SaleTransactionTime DATETIME NOT NULL
    ,VenueSkey INT NOT NULL
    ,QuantitySold BIGINT NOT NULL
    ,SaleComission NUMERIC NOT NULL
    ,InsertedDate DATETIME DEFAULT GETDATE()
) diststyle AUTO;

기본값이 있는 삽입된 날짜가 추가되어 레코드가 로드되었는지 여부와 시기를 나타냅니다. 중복을 피하기 위해 이미 로드된 데이터를 제거하기 위해 팩트 테이블을 다시 로드할 때 이것을 사용할 수 있습니다.

팩트 테이블 로드는 연결된 차원을 결합하는 간단한 삽입 문으로 구성됩니다. 우리는 DimVenue 사실을 설명하는 생성된 테이블입니다. 모범 사례이지만 선택 사항입니다. 달력 날짜 최종 사용자가 팩트 테이블을 탐색할 수 있도록 하는 차원. 새로운 판매가 있을 때 또는 매일 데이터를 로드할 수 있습니다. 여기에 삽입된 날짜 또는 로드 날짜가 유용합니다.

저장 프로시저를 사용하여 사실 테이블을 로드하고 날짜 매개변수를 사용합니다.

  1. 다음 코드를 사용하여 저장 프로시저를 만듭니다. 차원 로드에 적용한 것과 동일한 데이터 무결성을 유지하기 위해 NULL 값(있는 경우)을 보다 비즈니스에 적합한 값으로 바꿉니다.
create or replace procedure SalesMart.FactSaleTransactionsLoad(loadate datetime)
language plpgsql
as
    $$
begin
--------------------------------------------------------------------
/*** Delete records loaded for the day, should there be any ***/
--------------------------------------------------------------------
Delete from SalesMart.FactSaleTransactions
where cast(InsertedDate as date) = CAST(loadate as date);
RAISE INFO 'Deleted rows for load date: %', loadate;
--------------------------------------------------------------------
/*** Insert records ***/
--------------------------------------------------------------------
INSERT INTO SalesMart.FactSaleTransactions (
CalendarDate    
,SaleTransactionTime    
,VenueSkey  
,QuantitySold  
,Salecomission
)
SELECT DISTINCT
    ISNULL(c.caldate, '1900-01-01') as CalendarDate
    ,ISNULL(a.saletime, '1900-01-01') as SaleTransactionTime
    ,ISNULL(b.VenueSkey, -1) as VenueSkey
    ,ISNULL(a.qtysold, 0) as QuantitySold
    ,ISNULL(a.commission, 0) as SaleComission
FROM
    public.sales as a
 
LEFT JOIN SalesMart.DimVenue as b
on a.venueid = b.venueid
 
LEFT JOIN public.DimCalendar as c
on to_char(a.saletime,'YYYYMMDD') = to_char(c.caldate,'YYYYMMDD');
--Optional filter, should you want to load only the latest data from source
--where cast(a.saletime as date) = cast(loadate as date);
  
end;
$$;

  1. 다음 명령으로 프로시저를 호출하여 데이터를 로드합니다.
call SalesMart.FactSaleTransactionsLoad(getdate())

데이터 로드 예약

이제 Amazon Redshift Query Editor V2에서 저장 프로시저를 예약하여 모델링 프로세스를 자동화할 수 있습니다. 다음 단계를 완료하십시오.

  1. 먼저 차원 로드를 호출하고 차원 로드가 성공적으로 실행된 후 팩트 로드가 시작됩니다.
BEGIN;
----Insert Dim Loads
call SalesMart.DimVenueLoad(); ----Insert Fact Loads. They will only run if the DimLoad is successful
call SalesMart.FactSaleTransactionsLoad(getdate());
END;

차원 로드가 실패하면 팩트 로드가 실행되지 않습니다. 이는 팩트 테이블을 오래된 차원으로 로드하지 않기 때문에 데이터의 일관성을 보장합니다.

  1. 로드를 예약하려면 다음을 선택하십시오. 스케쥴 쿼리 편집기 V2에서.

  1. 쿼리가 매일 오전 5시에 실행되도록 예약합니다.
  2. 선택적으로 활성화하여 실패 알림을 추가할 수 있습니다. 아마존 단순 알림 서비스 (아마존 SNS) 알림.

Amazon Quicksight에서 데이터 보고 및 분석

QuickSight는 통찰력을 쉽게 제공할 수 있는 비즈니스 인텔리전스 서비스입니다. 완전 관리형 서비스인 QuickSight를 사용하면 모든 장치에서 액세스하고 애플리케이션, 포털 및 웹 사이트에 포함할 수 있는 대화형 대시보드를 쉽게 만들고 게시할 수 있습니다.

데이터 마트를 사용하여 사실을 대시보드 형식으로 시각적으로 표시합니다. QuickSight를 시작하고 설정하려면 다음을 참조하십시오. 자동 검색되지 않는 데이터베이스를 사용하여 데이터 세트 만들기.

QuickSight에서 데이터 소스를 생성한 후 대리 키를 기반으로 모델링된 데이터(데이터 마트)를 결합합니다. skey. 이 데이터 세트를 사용하여 데이터 마트를 시각화합니다.

당사의 최종 대시보드에는 데이터 마트에 대한 통찰력이 포함되어 있으며 장소당 총 수수료 및 가장 많이 판매된 날짜와 같은 중요한 비즈니스 질문에 대한 답변이 제공됩니다. 다음 스크린샷은 데이터 마트의 최종 제품을 보여줍니다.

정리

향후 비용이 발생하지 않도록 하려면 이 게시물의 일부로 생성한 모든 리소스를 삭제하십시오.

결론

이제 우리는 다음을 사용하여 데이터 마트를 성공적으로 구현했습니다. DimVenue, DimCalendar및 FactSaleTransactions 테이블. 창고가 완전하지 않습니다. 더 많은 사실로 데이터 마트를 확장하고 더 많은 마트를 구현할 수 있고 시간이 지남에 따라 비즈니스 프로세스와 요구 사항이 증가함에 따라 데이터 웨어하우스도 증가할 것입니다. 이 게시물에서는 Amazon Redshift에서 차원 모델링을 이해하고 구현하는 방법에 대한 종단 간 보기를 제공했습니다.

시작하기 아마존 레드 시프트 오늘의 입체모형.

저자에 관하여

버나드 베르스터 확장 가능하고 효율적인 데이터 모델을 만들고, 데이터 통합 ​​전략을 정의하고, 데이터 거버넌스 및 보안을 보장하는 데 수년간 노출된 숙련된 클라우드 엔지니어입니다. 그는 비즈니스 요구 사항 및 목표에 부합하면서 데이터를 사용하여 통찰력을 얻는 데 열정적입니다.

아비섹 판 AWS 인도 공공 부문 고객과 함께 일하는 WWSO 전문가 SA-Analytics입니다. 그는 고객과 협력하여 데이터 기반 전략을 정의하고, 분석 사용 사례에 대한 심층 분석 세션을 제공하고, 확장 가능하고 성능이 뛰어난 분석 애플리케이션을 설계합니다. 그는 12년의 경험을 가지고 있으며 데이터베이스, 분석 및 AI/ML에 열정적입니다. 그는 열렬한 여행자이며 카메라 렌즈를 통해 세상을 포착하려고 합니다.

타임 스탬프 :

더보기 AWS 빅 데이터