Mô hình hóa thứ nguyên trong Amazon Redshift | Dịch vụ web của Amazon

Mô hình hóa thứ nguyên trong Amazon Redshift | Dịch vụ web của Amazon

Dấu thời gian: 19:2023 chiều ngày 3 tháng 34 năm XNUMX
Nút nguồn: 2778508

Amazon RedShift là một kho dữ liệu đám mây quy mô petabyte và được quản lý hoàn toàn, được hàng chục nghìn khách hàng sử dụng để xử lý hàng exabyte dữ liệu mỗi ngày nhằm cung cấp năng lượng cho khối lượng công việc phân tích của họ. Bạn có thể cấu trúc dữ liệu của mình, đo lường quy trình kinh doanh và nhận thông tin chuyên sâu có giá trị một cách nhanh chóng bằng cách sử dụng mô hình thứ nguyên. Amazon Redshift cung cấp các tính năng tích hợp để đẩy nhanh quá trình lập mô hình, điều phối và báo cáo từ mô hình thứ nguyên.

Trong bài đăng này, chúng tôi thảo luận về cách triển khai mô hình thứ nguyên, cụ thể là Phương pháp Kimball. Chúng tôi thảo luận về việc triển khai các tham số và sự kiện trong Amazon Redshift. Chúng tôi trình bày cách thực hiện trích xuất, biến đổi và tải (ELT), một quy trình tích hợp tập trung vào việc đưa dữ liệu thô từ hồ dữ liệu vào lớp dàn dựng để thực hiện mô hình hóa. Nhìn chung, bài đăng sẽ giúp bạn hiểu rõ về cách sử dụng mô hình thứ nguyên trong Amazon Redshift.

Tổng quan về giải pháp

Sơ đồ sau minh họa kiến ​​trúc giải pháp.

Trong các phần tiếp theo, trước tiên chúng ta thảo luận và chứng minh các khía cạnh chính của mô hình thứ nguyên. Sau đó, chúng tôi tạo một siêu thị dữ liệu bằng cách sử dụng Amazon Redshift với mô hình dữ liệu thứ nguyên bao gồm các bảng thứ nguyên và dữ kiện. Dữ liệu được tải và dàn dựng bằng cách sử dụng COPY lệnh, dữ liệu trong kích thước được tải bằng cách sử dụng MERGE tuyên bố và các sự kiện sẽ được kết hợp với các thứ nguyên mà từ đó những hiểu biết sâu sắc được bắt nguồn. Chúng tôi lên lịch tải các kích thước và sự kiện bằng cách sử dụng Trình soạn thảo truy vấn Amazon Redshift V2. Cuối cùng, chúng tôi sử dụng Amazon QuickSight để hiểu rõ hơn về dữ liệu được mô hình hóa dưới dạng bảng điều khiển QuickSight.

Đối với giải pháp này, chúng tôi sử dụng tập dữ liệu mẫu (đã chuẩn hóa) do Amazon Redshift cung cấp để bán vé sự kiện. Đối với bài đăng này, chúng tôi đã thu hẹp tập dữ liệu cho mục đích trình diễn và đơn giản. Các bảng sau đây cho thấy các ví dụ về dữ liệu bán vé và địa điểm.

Theo Phương pháp mô hình chiều Kimball, có bốn bước chính trong việc thiết kế một mô hình chiều:

  1. Xác định quy trình kinh doanh.
  2. Khai báo hạt dữ liệu của bạn.
  3. Xác định và thực hiện các kích thước.
  4. Xác định và thực hiện các sự kiện.

Ngoài ra, chúng tôi thêm bước thứ năm cho mục đích trình diễn, đó là báo cáo và phân tích các sự kiện kinh doanh.

Điều kiện tiên quyết

Đối với hướng dẫn này, bạn nên có các điều kiện tiên quyết sau:

Xác định quy trình kinh doanh

Nói một cách đơn giản, xác định quy trình kinh doanh là xác định một sự kiện có thể đo lường để tạo ra dữ liệu trong một tổ chức. Thông thường, các công ty có một số loại hệ thống nguồn hoạt động tạo dữ liệu của họ ở định dạng thô. Đây là một điểm khởi đầu tốt để xác định các nguồn khác nhau cho quy trình kinh doanh.

Quá trình kinh doanh sau đó được duy trì như một dữ liệu mart dưới dạng các kích thước và sự kiện. Nhìn vào tập dữ liệu mẫu của chúng tôi đã đề cập trước đó, chúng tôi có thể thấy rõ quy trình kinh doanh là doanh số bán hàng được thực hiện cho một sự kiện nhất định.

Một sai lầm phổ biến là sử dụng các phòng ban của công ty làm quy trình kinh doanh. Dữ liệu (quy trình kinh doanh) cần được tích hợp giữa các bộ phận khác nhau, trong trường hợp này, bộ phận tiếp thị có thể truy cập dữ liệu bán hàng. Việc xác định đúng quy trình kinh doanh là rất quan trọng—việc thực hiện sai bước này có thể ảnh hưởng đến toàn bộ kho dữ liệu (điều này có thể khiến hạt giống bị trùng lặp và số liệu không chính xác trên các báo cáo cuối cùng).

Khai báo hạt dữ liệu của bạn

Khai báo hạt là hành động xác định duy nhất một bản ghi trong nguồn dữ liệu của bạn. Hạt được sử dụng trong bảng thực tế để đo lường chính xác dữ liệu và cho phép bạn tăng thêm. Trong ví dụ của chúng tôi, đây có thể là một mục hàng trong quy trình kinh doanh bán hàng.

Trong trường hợp sử dụng của chúng tôi, việc bán hàng có thể được xác định duy nhất bằng cách xem thời gian giao dịch khi việc bán hàng diễn ra; đây sẽ là cấp độ nguyên tử nhất.

Xác định và triển khai các kích thước

Bảng thứ nguyên của bạn mô tả bảng thực tế của bạn và các thuộc tính của nó. Khi xác định bối cảnh mô tả của quy trình kinh doanh của bạn, bạn lưu trữ văn bản trong một bảng riêng biệt, lưu ý đến bảng dữ kiện. Khi nối bảng kích thước với bảng thực tế, chỉ nên có một hàng duy nhất được liên kết với bảng thực tế. Trong ví dụ của chúng tôi, chúng tôi sử dụng bảng sau đây để được tách thành một bảng kích thước; các trường này mô tả các sự kiện mà chúng tôi sẽ đo lường.

Khi thiết kế cấu trúc của mô hình chiều (lược đồ), bạn có thể tạo một ngôi sao or bông tuyết lược đồ. Cơ cấu phải gắn chặt với quy trình nghiệp vụ; do đó, lược đồ hình sao là phù hợp nhất cho ví dụ của chúng ta. Hình dưới đây cho thấy Sơ đồ mối quan hệ thực thể (ERD) của chúng tôi.

Trong các phần sau, chúng tôi trình bày chi tiết các bước để thực hiện các kích thước.

Giai đoạn dữ liệu nguồn

Trước khi có thể tạo và tải bảng kích thước, chúng tôi cần dữ liệu nguồn. Do đó, chúng tôi sắp xếp dữ liệu nguồn thành một bảng tạm thời hoặc tạm thời. Điều này thường được gọi là lớp dàn dựng, là bản sao thô của dữ liệu nguồn. Để làm điều này trong Amazon Redshift, chúng tôi sử dụng Lệnh COPY để tải dữ liệu từ bộ chứa S3 công khai mô hình hóa chiều trong amazon-redshift nằm trên us-east-1 Vùng đất. Lưu ý rằng lệnh COPY sử dụng một Quản lý truy cập và nhận dạng AWS (IAM) vai trò với truy cập vào Amazon S3. Vai trò cần phải được liên kết với cụm. Hoàn thành các bước sau để tạo giai đoạn dữ liệu nguồn:

  1. tạo venue bảng nguồn:
CREATE TABLE public.venue ( venueid bigint, venuename character varying(100), venuecity character varying(30), venuestate character(2), venueseats bigint
) DISTSTYLE AUTO SORTKEY (venueid);

  1. Tải dữ liệu địa điểm:
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. tạo sales bảng nguồn:
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. Tải dữ liệu nguồn bán hàng:
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. tạo calendar bàn:
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. Tải dữ liệu lịch:
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

Tạo bảng kích thước

Việc thiết kế bảng tham số có thể phụ thuộc vào yêu cầu kinh doanh của bạn—ví dụ: bạn có cần theo dõi các thay đổi đối với dữ liệu theo thời gian không? Có bảy loại kích thước khác nhau. Ví dụ của chúng tôi, chúng tôi sử dụng loại 1 bởi vì chúng tôi không cần phải theo dõi những thay đổi lịch sử. Để biết thêm về loại 2, hãy tham khảo Đơn giản hóa việc tải dữ liệu vào các kích thước thay đổi chậm của Loại 2 trong Amazon Redshift. Bảng kích thước sẽ không được chuẩn hóa bằng khóa chính, khóa thay thế và một vài trường được thêm vào để biểu thị các thay đổi đối với bảng. Xem đoạn mã sau:

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;

Một số lưu ý khi tạo bảng kích thước:

  • Tên trường được chuyển thành tên thân thiện với doanh nghiệp
  • Khóa chính của chúng tôi là VenueID, mà chúng tôi sử dụng để xác định duy nhất một địa điểm diễn ra việc bán hàng
  • Hai hàng bổ sung sẽ được thêm vào, cho biết thời điểm một bản ghi được chèn và cập nhật (để theo dõi các thay đổi)
  • chúng tôi đang sử dụng một kiểu phân phối AUTO để trao cho Amazon Redshift trách nhiệm lựa chọn và điều chỉnh kiểu phân phối

Một yếu tố quan trọng khác cần xem xét trong mô hình chiều là việc sử dụng chìa khóa thay thế. Khóa thay thế là khóa nhân tạo được sử dụng trong mô hình thứ nguyên để xác định duy nhất từng bản ghi trong bảng thứ nguyên. Chúng thường được tạo dưới dạng số nguyên tuần tự và chúng không có bất kỳ ý nghĩa nào trong lĩnh vực kinh doanh. Chúng mang lại một số lợi ích, chẳng hạn như đảm bảo tính duy nhất và cải thiện hiệu suất khi kết hợp, vì chúng thường nhỏ hơn khóa tự nhiên và là khóa thay thế, chúng không thay đổi theo thời gian. Điều này cho phép chúng tôi nhất quán và tham gia các sự kiện và kích thước dễ dàng hơn.

Trong Amazon Redshift, các khóa thay thế thường được tạo bằng từ khóa IDENTITY. Ví dụ: câu lệnh CREATE trước đó tạo một bảng thứ nguyên với một VenueSkey phím thay thế. Các VenueSkey cột được tự động điền các giá trị duy nhất khi các hàng mới được thêm vào bảng. Cột này sau đó có thể được sử dụng để nối bảng địa điểm với FactSaleTransactions bảng.

Một số mẹo để thiết kế khóa thay thế:

  • Sử dụng loại dữ liệu nhỏ, có chiều rộng cố định cho khóa thay thế. Điều này sẽ cải thiện hiệu suất và giảm không gian lưu trữ.
  • Sử dụng từ khóa IDENTITY hoặc tạo khóa thay thế bằng cách sử dụng giá trị tuần tự hoặc GUID. Điều này sẽ đảm bảo rằng khóa thay thế là duy nhất và không thể thay đổi.

Tải bảng mờ bằng MERGE

Có rất nhiều cách để tải bảng mờ của bạn. Một số yếu tố cần được xem xét—ví dụ: hiệu suất, khối lượng dữ liệu và có thể là thời gian tải SLA. với MERGE câu lệnh, chúng tôi thực hiện một upert mà không cần chỉ định nhiều lệnh chèn và cập nhật. Bạn có thể thiết lập các MERGE tuyên bố trong một thủ tục lưu trữ để điền dữ liệu. Sau đó, bạn lên lịch cho thủ tục được lưu trữ để chạy theo chương trình thông qua trình chỉnh sửa truy vấn mà chúng tôi sẽ trình bày ở phần sau của bài viết. Đoạn mã sau tạo một thủ tục được lưu trữ có tên là 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;

Một số lưu ý khi tải kích thước:

  • Khi một bản ghi được chèn lần đầu tiên, ngày được chèn và ngày cập nhật sẽ được điền. Khi bất kỳ giá trị nào thay đổi, dữ liệu được cập nhật và ngày cập nhật phản ánh ngày thay đổi. Ngày đã chèn vẫn còn.
  • Vì dữ liệu sẽ được người dùng doanh nghiệp sử dụng nên chúng tôi cần thay thế các giá trị NULL, nếu có, bằng các giá trị phù hợp với doanh nghiệp hơn.

Xác định và thực hiện các sự kiện

Bây giờ chúng tôi đã tuyên bố ngũ cốc của mình là sự kiện bán hàng diễn ra vào một thời điểm cụ thể, bảng thực tế của chúng tôi sẽ lưu trữ các sự kiện số cho quy trình kinh doanh của chúng tôi.

Chúng tôi đã xác định các sự kiện số sau đây để đo lường:

  • Số lượng vé bán ra mỗi lần bán
  • Hoa hồng bán hàng

Thực hiện sự thật

Có ba loại bảng thực tế (bảng dữ kiện giao dịch, bảng dữ kiện ảnh chụp nhanh định kỳ và bảng dữ kiện ảnh chụp nhanh tích lũy). Mỗi cái phục vụ một cái nhìn khác nhau về quy trình kinh doanh. Ví dụ của chúng tôi, chúng tôi sử dụng bảng thực tế giao dịch. Hoàn thành các bước sau:

  1. Tạo bảng thực tế
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;

Ngày đã chèn với giá trị mặc định được thêm vào, cho biết bản ghi có được tải hay không và khi nào. Bạn có thể sử dụng điều này khi tải lại bảng thực tế để xóa dữ liệu đã được tải để tránh trùng lặp.

Tải bảng thực tế bao gồm một câu lệnh chèn đơn giản nối các thứ nguyên được liên kết của bạn. Chúng tôi tham gia từ DimVenue bảng đã được tạo, mô tả sự thật của chúng tôi. Đó là cách thực hành tốt nhất nhưng tùy chọn để có lịch ngày kích thước, cho phép người dùng cuối điều hướng bảng thực tế. Dữ liệu có thể được tải khi có đợt giảm giá mới hoặc hàng ngày; đây là lúc ngày được chèn hoặc ngày tải có ích.

Chúng tôi tải bảng thực tế bằng thủ tục được lưu trữ và sử dụng tham số ngày.

  1. Tạo thủ tục được lưu trữ với mã sau đây. Để giữ nguyên tính toàn vẹn của dữ liệu mà chúng tôi đã áp dụng trong tải thứ nguyên, chúng tôi thay thế các giá trị NULL, nếu có, bằng các giá trị phù hợp với doanh nghiệp hơn:
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. Tải dữ liệu bằng cách gọi thủ tục bằng lệnh sau:
call SalesMart.FactSaleTransactionsLoad(getdate())

Lên lịch tải dữ liệu

Giờ đây, chúng tôi có thể tự động hóa quy trình lập mô hình bằng cách lên lịch cho các thủ tục được lưu trữ trong Amazon Redshift Query Editor V2. Hoàn thành các bước sau:

  1. Trước tiên, chúng tôi gọi tải kích thước và sau khi tải kích thước chạy thành công, tải thực tế bắt đầu:
BEGIN;
----Insert Dim Loads
call SalesMart.DimVenueLoad(); ----Insert Fact Loads. They will only run if the DimLoad is successful
call SalesMart.FactSaleTransactionsLoad(getdate());
END;

Nếu tải kích thước không thành công, tải thực tế sẽ không chạy. Điều này đảm bảo tính nhất quán trong dữ liệu vì chúng tôi không muốn tải bảng thực tế với các kích thước lỗi thời.

  1. Để lên lịch tải, hãy chọn Schedule trong Trình soạn thảo truy vấn V2.

  1. Chúng tôi lên lịch truy vấn để chạy hàng ngày vào lúc 5:00 sáng.
  2. Theo tùy chọn, bạn có thể thêm thông báo lỗi bằng cách bật Dịch vụ thông báo đơn giản của Amazon thông báo (Amazon SNS).

Báo cáo và phân tích dữ liệu trong Amazon Quicksight

QuickSight là một dịch vụ kinh doanh thông minh giúp bạn dễ dàng cung cấp thông tin chuyên sâu. Là một dịch vụ được quản lý hoàn toàn, QuickSight cho phép bạn dễ dàng tạo và xuất bản các bảng điều khiển tương tác mà sau đó có thể truy cập được từ bất kỳ thiết bị nào và được nhúng vào các ứng dụng, cổng thông tin và trang web của bạn.

Chúng tôi sử dụng siêu thị dữ liệu của mình để trình bày trực quan các sự kiện dưới dạng bảng điều khiển. Để bắt đầu và thiết lập QuickSight, hãy tham khảo Tạo tập dữ liệu bằng cơ sở dữ liệu không được tự động phát hiện.

Sau khi bạn tạo nguồn dữ liệu của mình trong QuickSight, chúng tôi kết hợp dữ liệu được lập mô hình (data mart) với nhau dựa trên khóa thay thế của chúng tôi skey. Chúng tôi sử dụng bộ dữ liệu này để trực quan hóa siêu thị dữ liệu.

Bảng điều khiển cuối cùng của chúng tôi sẽ chứa thông tin chi tiết về siêu thị dữ liệu và trả lời các câu hỏi kinh doanh quan trọng, chẳng hạn như tổng hoa hồng cho mỗi địa điểm và ngày có doanh số bán hàng cao nhất. Ảnh chụp màn hình sau đây cho thấy sản phẩm cuối cùng của data mart.

Làm sạch

Để tránh bị tính phí trong tương lai, hãy xóa mọi tài nguyên bạn đã tạo như một phần của bài đăng này.

Kết luận

Bây giờ chúng tôi đã triển khai thành công một siêu thị dữ liệu bằng cách sử dụng DimVenue, DimCalendarvà FactSaleTransactions những cái bàn. Nhà kho của chúng tôi chưa hoàn thành; vì chúng tôi có thể mở rộng kho dữ liệu với nhiều sự kiện hơn và triển khai nhiều siêu thị hơn, đồng thời khi quy trình kinh doanh và các yêu cầu phát triển theo thời gian, thì kho dữ liệu cũng vậy. Trong bài đăng này, chúng tôi đã cung cấp góc nhìn toàn diện về cách hiểu và triển khai mô hình hóa thứ nguyên trong Amazon Redshift.

Bắt đầu với Amazon RedShift mô hình chiều ngày nay.

Về các tác giả

Bernard Verster là một kỹ sư đám mây giàu kinh nghiệm với nhiều năm tiếp xúc trong việc tạo các mô hình dữ liệu hiệu quả và có thể mở rộng, xác định các chiến lược tích hợp dữ liệu cũng như đảm bảo quản trị và bảo mật dữ liệu. Anh đam mê sử dụng dữ liệu để thu hút thông tin chi tiết, đồng thời phù hợp với các yêu cầu và mục tiêu kinh doanh.

Abhishek Pan là Chuyên gia SA-Analytics của WWSO làm việc với các khách hàng thuộc khu vực Công của AWS Ấn Độ. Anh ấy tương tác với khách hàng để xác định chiến lược dựa trên dữ liệu, cung cấp các phiên phân tích chuyên sâu về các trường hợp sử dụng phân tích, đồng thời thiết kế các ứng dụng phân tích hiệu quả và có thể mở rộng. Anh ấy có 12 năm kinh nghiệm và đam mê cơ sở dữ liệu, phân tích và AI/ML. Anh ấy là một người đam mê du lịch và cố gắng ghi lại thế giới qua ống kính máy ảnh của mình.

Dấu thời gian:

Thêm từ Dữ liệu lớn AWS