Amazon Redshift'te boyutsal modelleme | Amazon Web Hizmetleri

Amazon Kırmızıya Kaydırma analitik iş yüklerini güçlendirmek için her gün eksabaytlarca veriyi işlemek için on binlerce müşteri tarafından kullanılan, tam olarak yönetilen ve petabayt ölçeğinde bir bulut veri ambarıdır. Boyutlu bir model kullanarak verilerinizi yapılandırabilir, iş süreçlerini ölçebilir ve hızlı bir şekilde değerli içgörüler elde edebilirsiniz. Amazon Redshift, boyutlu bir modelden modelleme, düzenleme ve raporlama sürecini hızlandırmak için yerleşik özellikler sağlar.

Bu yazıda, boyutlu bir modelin nasıl uygulanacağını, özellikle de Kimball metodolojisi. Amazon Redshift'te boyutları ve gerçekleri uygulamayı tartışıyoruz. Modellemeyi gerçekleştirmek için ham verileri bir veri gölünden bir hazırlama katmanına almaya odaklanan bir entegrasyon süreci olan ayıklama, dönüştürme ve yüklemenin (ELT) nasıl gerçekleştirileceğini gösteriyoruz. Genel olarak gönderi, Amazon Redshift'te boyutsal modellemenin nasıl kullanılacağına dair net bir anlayış sağlayacaktır.

Çözüme genel bakış

Aşağıdaki şemada çözüm mimarisi gösterilmektedir.

Aşağıdaki bölümlerde, öncelikle boyutsal modelin temel özelliklerini tartışıyor ve gösteriyoruz. Ardından, Amazon Redshift'i kullanarak boyut ve olgu tablolarını içeren boyutlu bir veri modeliyle bir data mart oluşturuyoruz. Veriler kullanılarak yüklenir ve hazırlanır. KOPYA komutu kullanılarak boyutlardaki veriler yüklenir. BİRLEŞTİRMEK ifade ve gerçekler, içgörülerin türetildiği boyutlarla birleştirilecektir. Kullanarak boyutların ve gerçeklerin yüklenmesini planlıyoruz. Amazon Redshift Sorgu Düzenleyicisi V2. Son olarak, kullandığımız Amazon QuickSight QuickSight panosu biçiminde modellenen veriler hakkında içgörüler elde etmek için.

Bu çözüm için, etkinlik bileti satışları için Amazon Redshift tarafından sağlanan örnek bir veri kümesi (normalleştirilmiş) kullanıyoruz. Bu gönderi için, basitlik ve tanıtım amacıyla veri kümesini daralttık. Aşağıdaki tablolar, bilet satışları ve mekanlara ilişkin veri örneklerini göstermektedir.

Göre Kimball boyutlu modelleme metodolojisi, boyutlu bir model tasarlamanın dört temel adımı vardır:

1. İş sürecini tanımlayın.
2. Verilerinizin tanesini beyan edin.
3. Boyutları belirleyin ve uygulayın.
4. Gerçekleri belirleyin ve uygulayın.

Ek olarak, gösteri amaçlı olarak iş olaylarını raporlamak ve analiz etmek için beşinci bir adım ekledik.

Önkoşullar

Bu izlenecek yol için aşağıdaki ön koşullara sahip olmalısınız:

İş sürecini tanımlayın

Basit bir ifadeyle, iş sürecini tanımlamak, bir kuruluş içinde veri üreten ölçülebilir bir olayı tanımlamaktır. Genellikle şirketler, verilerini ham biçiminde üreten bir tür operasyonel kaynak sistemine sahiptir. Bu, bir iş süreci için çeşitli kaynakları belirlemek için iyi bir başlangıç ​​noktasıdır.

İş süreci daha sonra bir mart tarihi boyutlar ve gerçekler şeklinde. Daha önce bahsedilen örnek veri kümemize baktığımızda, iş sürecinin belirli bir etkinlik için yapılan satışlar olduğunu açıkça görebiliriz.

Yapılan yaygın bir hata, bir şirketin departmanlarını iş süreci olarak kullanmaktır. Verilerin (iş süreci) çeşitli departmanlar arasında entegre edilmesi gerekir, bu durumda pazarlama, satış verilerine erişebilir. Doğru iş sürecini belirlemek çok önemlidir; bu adımı yanlış yapmak, tüm veri pazarını etkileyebilir (tanenin yinelenmesine ve nihai raporlarda yanlış ölçümlere neden olabilir).

Verilerinizin tanesini beyan edin

Taneyi bildirmek, veri kaynağınızdaki bir kaydı benzersiz bir şekilde tanımlama eylemidir. Tahıl, verileri doğru bir şekilde ölçmek ve daha fazla toparlama yapmanızı sağlamak için olgu tablosunda kullanılır. Örneğimizde bu, satış iş sürecindeki bir kalem olabilir.

Bizim kullanım durumumuzda, bir satış, satışın gerçekleştiği işlem zamanına bakılarak benzersiz bir şekilde tanımlanabilir; bu en atomik seviye olacak.

Boyutları tanımlayın ve uygulayın

Boyut tablonuz, olgu tablonuzu ve niteliklerini açıklar. İş sürecinizin tanımlayıcı bağlamını belirlerken, olgu tablosunu göz önünde bulundurarak metni ayrı bir tabloda saklarsınız. Boyutlar tablosunu olgu tablosuyla birleştirirken, olgu tablosuyla ilişkili yalnızca tek bir satır olmalıdır. Örneğimizde, bir boyut tablosuna ayırmak için aşağıdaki tabloyu kullanıyoruz; bu alanlar ölçeceğimiz gerçekleri tanımlar.

Boyutlu modelin (şema) yapısını tasarlarken, bir star or kar taneciği şema. Yapı, iş süreciyle yakından uyumlu olmalıdır; bu nedenle, bir yıldız şeması örneğimiz için en uygun olanıdır. Aşağıdaki şekilde Varlık İlişki Şemamız (ERD) gösterilmektedir.

Aşağıdaki bölümlerde, boyutları uygulama adımlarını ayrıntılarıyla açıklıyoruz.

Kaynak verileri hazırlama

Boyutlar tablosunu oluşturmadan ve yüklemeden önce kaynak verilere ihtiyacımız var. Bu nedenle, kaynak verileri bir aşamalandırma veya geçici tabloya yerleştiririz. Bu genellikle şu şekilde adlandırılır: evreleme katmanı, kaynak verilerin ham kopyasıdır. Amazon Redshift'te bunu yapmak için KOPYALA komutu Amazon redshift'te boyutsal modelleme genel S3 klasöründeki verileri yüklemek için us-east-1 Bölge. COPY komutunun bir AWS Kimlik ve Erişim Yönetimi (IAM) rolü ile Amazon S3'e erişim. Rol olması gereken küme ile ilişkili. Kaynak verileri hazırlamak için aşağıdaki adımları tamamlayın:

1. oluşturmak venue kaynak tablosu:

CREATE TABLE public.venue ( venueid bigint, venuename character varying(100), venuecity character varying(30), venuestate character(2), venueseats bigint
) DISTSTYLE AUTO SORTKEY (venueid);

2. Mekan verilerini yükleyin:

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

3. oluşturmak sales kaynak tablosu:

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;

4. Satış kaynağı verilerini yükleyin:

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

5. oluşturmak calendar tablosu:

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);

6. Takvim verilerini yükleyin:

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

Boyutlar tablosunu oluşturun

Boyutlar tablosunu tasarlamak, iş gereksinimlerinize bağlı olabilir; örneğin, zaman içinde verilerde meydana gelen değişiklikleri izlemeniz gerekiyor mu? Var yedi farklı boyut türü. Örneğimiz için, kullandığımız 1 yazın çünkü tarihsel değişiklikleri takip etmemize gerek yok. Tip 2 hakkında daha fazla bilgi için bkz. Amazon Redshift'te yavaşça değişen boyutlar olan Tip 2'ye veri yüklemeyi basitleştirin. Boyutlar tablosu, tablodaki değişiklikleri belirtmek için bir birincil anahtar, vekil anahtar ve birkaç ek alanla normalleştirilecektir. Aşağıdaki koda bakın:

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;

Boyut tablosu oluşturma hakkında birkaç not:

• Alan adları iş dostu adlara dönüştürülür
• Birincil anahtarımız VenueID, satışın gerçekleştiği yeri benzersiz bir şekilde tanımlamak için kullandığımız
• Bir kaydın ne zaman eklendiğini ve güncellendiğini gösteren iki ek satır eklenecektir (değişiklikleri izlemek için)
• biz kullanıyoruz OTO dağıtım stili dağıtım stilini seçme ve düzenleme sorumluluğunu Amazon Redshift'e vermek

Boyutsal modellemede dikkate alınması gereken bir diğer önemli faktör, yedek anahtarlar. Yedek anahtarlar, bir boyut tablosundaki her bir kaydı benzersiz şekilde tanımlamak için boyutsal modellemede kullanılan yapay anahtarlardır. Tipik olarak sıralı bir tamsayı olarak üretilirler ve iş alanında herhangi bir anlamları yoktur. Tipik olarak doğal anahtarlardan daha küçük olduklarından ve vekil anahtarlar olarak zamanla değişmediklerinden, birleştirmelerde benzersizliği sağlamak ve performansı artırmak gibi çeşitli avantajlar sunarlar. Bu, tutarlı olmamızı ve gerçekleri ve boyutları daha kolay birleştirmemizi sağlar.

Amazon Redshift'te yedek anahtarlar genellikle IDENTITY anahtar sözcüğü kullanılarak oluşturulur. Örneğin, önceki CREATE deyimi, bir boyut tablosu oluşturur. VenueSkey Vekil anahtarı. bu VenueSkey sütun, tabloya yeni satırlar eklendikçe benzersiz değerlerle otomatik olarak doldurulur. Bu sütun daha sonra mekan tablosunu aşağıdakilerle birleştirmek için kullanılabilir: FactSaleTransactions tablo.

Vekil anahtarlar tasarlamak için birkaç ipucu:

• Yedek anahtar için küçük, sabit genişlikli bir veri türü kullanın. Bu, performansı artıracak ve depolama alanını azaltacaktır.
• KİMLİK anahtar sözcüğünü kullanın veya bir sıralı ya da GUID değeri kullanarak vekil anahtarı oluşturun. Bu, vekil anahtarın benzersiz olmasını ve değiştirilememesini sağlayacaktır.

MERGE kullanarak dim tablosunu yükleyin

Dim tablonuzu yüklemenin çok sayıda yolu vardır. Performans, veri hacmi ve belki de SLA yükleme süreleri gibi belirli faktörlerin dikkate alınması gerekir. İle BİRLEŞTİRMEK ifadesinde, birden çok ekleme ve güncelleme komutu belirtmeye gerek kalmadan bir upsert gerçekleştiriyoruz. ayarlayabilirsiniz BİRLEŞTİRMEK bir ifadede saklı yordam verileri doldurmak için. Daha sonra saklı yordamı, gönderide daha sonra göstereceğimiz sorgu düzenleyici aracılığıyla programlı olarak çalışacak şekilde planlarsınız. Aşağıdaki kod, adlı bir saklı yordam oluşturur. 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;

Boyut yüklemesi hakkında birkaç not:

• İlk kez bir kayıt girildiğinde, girilen tarih ve güncellenen tarih doldurulacaktır. Herhangi bir değer değiştiğinde, veriler güncellenir ve güncellenen tarih değiştirildiği tarihi yansıtır. Girilen tarih kalır.
• Veriler iş kullanıcıları tarafından kullanılacağından, varsa NULL değerleri iş için daha uygun değerlerle değiştirmemiz gerekir.

Gerçekleri belirle ve uygula

Tahılımızın belirli bir zamanda gerçekleşen bir satış olayı olduğunu ilan ettiğimize göre, olgu tablomuz iş sürecimiz için sayısal gerçekleri saklayacaktır.

Ölçmek için aşağıdaki sayısal gerçekleri belirledik:

• Satış başına satılan bilet miktarı
• satış komisyonu

Gerçeği Uygulamak

Var üç tür bilgi tablosu (işlem olgu tablosu, periyodik anlık görüntü olgu tablosu ve biriken anlık görüntü olgu tablosu). Her biri, iş sürecinin farklı bir görünümüne hizmet eder. Örneğimiz için, bir işlem olgu tablosu kullanıyoruz. Aşağıdaki adımları tamamlayın:

1. Olgu tablosunu oluşturun

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;

Bir kaydın yüklenip yüklenmediğini ve ne zaman yüklendiğini gösteren, varsayılan değere sahip bir girilen tarih eklenir. Yinelemeleri önlemek için önceden yüklenmiş verileri kaldırmak için olgu tablosunu yeniden yüklerken bunu kullanabilirsiniz.

Olgu tablosunu yüklemek, ilişkili boyutlarınızı birleştiren basit bir ekleme ifadesinden oluşur. dan katılıyoruz DimVenue gerçeklerimizi açıklayan oluşturulan tablo. En iyi uygulamadır, ancak sahip olmak isteğe bağlıdır takvim tarihi son kullanıcının olgu tablosunda gezinmesine izin veren boyutlar. Veriler, yeni bir satış olduğunda veya günlük olarak yüklenebilir; girilen tarihin veya yükleme tarihinin kullanışlı olduğu yer burasıdır.

Olgu tablosunu bir saklı yordam kullanarak yüklüyoruz ve bir tarih parametresi kullanıyoruz.

2. Saklı yordamı aşağıdaki kodla oluşturun. Boyut yükünde uyguladığımız aynı veri bütünlüğünü korumak için, varsa NULL değerleri daha iş için uygun değerlerle değiştiririz:

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;
$$;

3. Aşağıdaki komutla yordamı çağırarak verileri yükleyin:

call SalesMart.FactSaleTransactionsLoad(getdate())

Veri yüklemesini planlayın

Artık Amazon Redshift Query Editor V2'de saklı yordamları planlayarak modelleme sürecini otomatik hale getirebiliriz. Aşağıdaki adımları tamamlayın:

1. Önce Dimension Load diyoruz ve Dimension Load başarılı bir şekilde çalıştıktan sonra, fact load başlıyor:

BEGIN;
----Insert Dim Loads
call SalesMart.DimVenueLoad(); ----Insert Fact Loads. They will only run if the DimLoad is successful
call SalesMart.FactSaleTransactionsLoad(getdate());
END;

Boyut yüklemesi başarısız olursa, olgu yüklemesi çalışmaz. Olgu tablosunu eski boyutlarla yüklemek istemediğimizden bu, verilerde tutarlılık sağlar.

2. Yükü planlamak için seçin Çizelge Sorgu Düzenleyicisi V2'de.

3. Sorguyu her gün saat 5:00'te çalışacak şekilde planlıyoruz.
4. İsteğe bağlı olarak, etkinleştirerek arıza bildirimleri ekleyebilirsiniz. Amazon Basit Bildirim Servisi (Amazon SNS) bildirimleri.

Amazon Quicksight'ta verileri raporlayın ve analiz edin

QuickSight, içgörü sağlamayı kolaylaştıran bir iş zekası hizmetidir. Tamamen yönetilen bir hizmet olarak QuickSight, daha sonra herhangi bir cihazdan erişilebilen ve uygulamalarınıza, portallarınıza ve web sitelerinize yerleştirilebilen etkileşimli panoları kolayca oluşturmanıza ve yayınlamanıza olanak tanır.

Gerçekleri bir pano biçiminde görsel olarak sunmak için data mart'ımızı kullanıyoruz. QuickSight'ı başlatmak ve kurmak için bkz. Otomatik olarak keşfedilmemiş bir veritabanı kullanarak veri kümesi oluşturma.

QuickSight'ta veri kaynağınızı oluşturduktan sonra, yedek anahtarımıza dayalı olarak modellenen verileri (data mart) birleştiririz. skey. Data mart'ı görselleştirmek için bu veri setini kullanıyoruz.

Son kontrol panelimiz, veri pazarının içgörülerini içerecek ve mekan başına toplam komisyon ve en yüksek satışın olduğu tarihler gibi kritik işle ilgili soruları yanıtlayacaktır. Aşağıdaki ekran görüntüsü, data mart'ın nihai ürününü göstermektedir.

Temizlemek

İleride ücret ödemekten kaçınmak için bu gönderinin bir parçası olarak oluşturduğunuz tüm kaynakları silin.

Sonuç

Artık başarıyla bir data mart'ı uyguladık. DimVenue, DimCalendar, ve FactSaleTransactions tablolar. Depomuz tamamlanmadı; veri pazarını daha fazla gerçekle genişletebildiğimiz ve daha fazla pazar uygulayabildiğimiz için ve iş süreci ve gereksinimler zaman içinde büyüdükçe, veri ambarı da büyüyecektir. Bu gönderide, Amazon Redshift'te boyutsal modellemeyi anlama ve uygulamaya ilişkin uçtan uca bir bakış açısı sunduk.

Seninle başla Amazon Kırmızıya Kaydırma boyutlu model bugün.

Yazarlar Hakkında

bernard verster ölçeklenebilir ve verimli veri modelleri oluşturma, veri entegrasyon stratejilerini tanımlama ve veri yönetişimi ile güvenliğini sağlama konularında yıllarca deneyime sahip deneyimli bir bulut mühendisidir. İş gereklilikleri ve hedefleriyle uyumlu hale gelirken içgörüleri yönlendirmek için verileri kullanma konusunda tutkulu.

Abhishek Tavası AWS Hindistan Kamu sektörü müşterileriyle çalışan bir WWSO Uzmanı SA-Analytics'tir. Veriye dayalı stratejiyi tanımlamak, analitik kullanım durumlarıyla ilgili ayrıntılı oturumlar sağlamak ve ölçeklenebilir ve yüksek performanslı analitik uygulamalar tasarlamak için müşterilerle iletişim kurar. 12 yıllık deneyime sahiptir ve veritabanları, analitik ve AI/ML konusunda tutkuludur. Hevesli bir gezgin ve kamera merceğinden dünyayı yakalamaya çalışıyor.

• SEO Destekli İçerik ve Halkla İlişkiler Dağıtımı. Bugün Gücünüzü Artırın.
• PlatoData.Network Dikey Üretken Yapay Zeka. Kendine güç ver. Buradan Erişin.
• PlatoAiStream. Web3 Zekası. Bilgi Genişletildi. Buradan Erişin.
• PlatoESG. Otomotiv / EV'ler, karbon, temiz teknoloji, Enerji, Çevre, Güneş, Atık Yönetimi. Buradan Erişin.
• Blok Ofsetleri. Çevre Dengeleme Sahipliğini Modernleştirme. Buradan Erişin.
• Kaynak: https://aws.amazon.com/blogs/big-data/dimensional-modeling-in-amazon-redshift/