Mõõtmete modelleerimine rakenduses Amazon Redshift | Amazoni veebiteenused

Amazoni punane nihe on täielikult hallatav ja petabaitide mastaabis pilvandmeladu, mida kasutavad kümned tuhanded kliendid iga päev eksabaitide kaupa andmete töötlemiseks, et oma analüütika töökoormust suurendada. Dimensioonimudeli abil saate oma andmeid struktureerida, äriprotsesse mõõta ja väärtuslikku teavet kiiresti saada. Amazon Redshift pakub sisseehitatud funktsioone, mis kiirendavad mõõtmete mudeli modelleerimist, orkestreerimist ja aruandlust.

Selles postituses arutame, kuidas rakendada mõõtmete mudelit, täpsemalt Kimballi metoodika. Arutame mõõtmete ja faktide rakendamist Amazon Redshiftis. Näitame, kuidas teostada ekstrakti, teisendust ja laadimist (ELT) – integreerimisprotsessi, mis keskendub andmejärvest toorandmete saamisele modelleerimise läbiviimiseks etapikihti. Üldiselt annab postitus teile selge ülevaate mõõtmete modelleerimise kasutamisest Amazon Redshiftis.

Lahenduse ülevaade

Järgnev diagramm illustreerib lahenduse arhitektuuri.

Järgmistes osades käsitleme esmalt mõõtmemudeli põhiaspekte ja demonstreerime neid. Pärast seda loome Amazon Redshifti abil andmemargi koos mõõtmete andmemudeliga, mis sisaldab mõõtmete ja faktitabeleid. Andmed laaditakse ja lavastati kasutades COPY käsku, laaditakse mõõtmete andmed kasutades ÜHENDAB avaldus ja faktid liidetakse dimensioonidega, millest arusaamad pärinevad. Ajastame mõõtmete ja faktide laadimise, kasutades Amazon Redshift Query Editor V2. Lõpuks kasutame Amazon QuickSight et saada ülevaadet modelleeritud andmetest QuickSighti armatuurlaua kujul.

Selle lahenduse jaoks kasutame sündmuste piletite müügiks Amazon Redshifti pakutavat näidisandmestikku (normaliseeritud). Selle postituse jaoks oleme andmestikku lihtsuse ja tutvustamise eesmärgil kitsendanud. Järgmistes tabelites on näiteid piletimüügi ja toimumiskohtade andmetest.

Vastavalt Kimballi mõõtmete modelleerimise metoodika, on mõõtmetega mudeli kujundamisel neli peamist sammu:

1. Tuvastage äriprotsess.
2. Deklareerige oma andmete tera.
3. Tehke kindlaks ja rakendage mõõtmed.
4. Tuvastage faktid ja rakendage neid.

Lisaks lisame tutvustamise eesmärgil viienda sammu, milleks on ärisündmuste aruandlus ja analüüsimine.

Eeldused

Selle ülevaate jaoks peaksid teil olema järgmised eeltingimused.

Tuvastage äriprotsess

Lihtsamalt öeldes tähendab äriprotsessi tuvastamine organisatsioonis andmeid genereeriva mõõdetava sündmuse tuvastamist. Tavaliselt on ettevõtetel mingi toimiv lähtesüsteem, mis genereerib nende andmed töötlemata kujul. See on hea lähtepunkt äriprotsessi erinevate allikate tuvastamiseks.

Seejärel jätkatakse äriprotsessi kui a andmed mart mõõtmete ja faktide näol. Vaadates meie varem mainitud näidisandmestikku, näeme selgelt, et äriprotsess on antud sündmuse jaoks tehtud müük.

Levinud viga on ettevõtte osakondade kasutamine äriprotsessina. Andmed (äriprotsess) tuleb integreerida erinevate osakondade vahel, sel juhul pääseb turundus müügiandmetele ligi. Õige äriprotsessi tuvastamine on kriitilise tähtsusega – selle sammu valesti sooritamine võib mõjutada kogu andmeturgu (see võib põhjustada tera dubleerimist ja valesid mõõdikuid lõpparuannetes).

Deklareerige oma andmete tera

Teravilja deklareerimine on teie andmeallika kirje kordumatu tuvastamine. Tera kasutatakse faktitabelis, et mõõta andmeid täpselt ja võimaldada teil edasi kerida. Meie näites võib see olla müügiäriprotsessi reaartikkel.

Meie kasutusjuhul saab müügi üheselt tuvastada, vaadates tehingu aega, millal müük toimus; see on kõige atomaarsem tase.

Tehke kindlaks ja rakendage mõõtmed

Teie dimensioonitabel kirjeldab teie faktitabelit ja selle atribuute. Äriprotsessi kirjeldava konteksti tuvastamisel salvestate teksti eraldi tabelisse, pidades silmas faktitabelit. Dimensioonide tabeli ühendamisel faktitabeliga peaks faktitabeliga olema seotud ainult üks rida. Meie näites kasutame mõõtmete tabeliks eraldamiseks järgmist tabelit; need väljad kirjeldavad fakte, mida me mõõdame.

Mõõtmemudeli (skeemi) struktuuri kavandamisel saate luua a täht or lumehelves skeem. Struktuur peaks olema tihedalt kooskõlas äriprotsessiga; seetõttu sobib meie näite jaoks kõige paremini täheskeem. Järgmine joonis näitab meie olemisuhete diagrammi (ERD).

Järgmistes jaotistes kirjeldame üksikasjalikult mõõtmete rakendamise samme.

Lavastage lähteandmed

Enne mõõtmete tabeli loomist ja laadimist vajame lähteandmeid. Seetõttu lavastame lähteandmed etapiviisiliseks või ajutiseks tabelisse. Seda nimetatakse sageli lavastuskiht, mis on lähteandmete töötlemata koopia. Selleks kasutame Amazon Redshiftis COPY käsk andmete laadimiseks dimensioonide modelleerimine amazon-redshift avalikust S3 ämbrist, mis asub us-east-1 Piirkond. Pange tähele, et käsk COPY kasutab a AWS-i identiteedi- ja juurdepääsuhaldus (IAM) rolli koos juurdepääs Amazon S3-le. Roll peab olema klastriga seotud. Lähteandmete etapistamiseks tehke järgmised sammud.

1. Loo venue lähtetabel:

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

2. Laadige koha andmed:

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. Loo sales lähtetabel:

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. Laadige müügi lähteandmed:

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. Loo calendar tabelis:

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. Laadige kalendri andmed:

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

Loo mõõtmete tabel

Dimensioonide tabeli kujundamine võib sõltuda teie ettevõtte vajadustest – näiteks kas peate jälgima andmete muutumist aja jooksul? Seal on seitse erinevat mõõtmetüüpi. Meie näiteks kasutame tüüp 1 sest me ei pea jälgima ajaloolisi muutusi. Lisateavet tüübi 2 kohta leiate artiklist Lihtsustage andmete laadimist 2. tüüpi aeglaselt muutuvatesse mõõtmetesse Amazon Redshiftis. Mõõtmete tabel denormaliseeritakse primaarvõtme, asendusvõtme ja mõne lisatud väljaga, mis näitavad tabeli muudatusi. Vaadake järgmist koodi:

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õned märkused mõõtmete tabeli loomise kohta:

• Väljanimed muudetakse ärisõbralikeks nimedeks
• Meie esmane võti on VenueID, mida kasutame müügi toimumise koha unikaalseks tuvastamiseks
• Lisatakse kaks täiendavat rida, mis näitavad, millal kirje sisestati ja seda värskendati (muudatuste jälgimiseks)
• Me kasutame an AUTOMAATNE levitamise stiil et anda Amazon Redshiftile vastutus levitamisstiili valimise ja kohandamise eest

Teine oluline tegur, mida mõõtmete modelleerimisel arvestada, on selle kasutamine asendusvõtmed. Asendusvõtmed on tehisvõtmed, mida kasutatakse mõõtmete modelleerimisel, et identifitseerida unikaalselt iga kirje dimensioonitabelis. Tavaliselt genereeritakse need järjestikuste täisarvudena ja neil pole ärivaldkonnas mingit tähendust. Need pakuvad mitmeid eeliseid, näiteks unikaalsuse tagamine ja liitumiste jõudluse parandamine, kuna need on tavaliselt väiksemad kui loomulikud võtmed ja asendusvõtmetena need aja jooksul ei muutu. See võimaldab meil olla järjepidev ning fakte ja mõõtmeid hõlpsamini ühendada.

Amazon Redshiftis luuakse asendusvõtmed tavaliselt märksõna IDENTITY abil. Näiteks eelnev CREATE-lause loob dimensioonitabeli koos a-ga VenueSkey asendusvõti. The VenueSkey veerg täidetakse automaatselt kordumatute väärtustega, kui tabelisse lisatakse uusi ridu. Seda veergu saab seejärel kasutada toimumiskoha tabeli ühendamiseks FactSaleTransactions tabelis.

Mõned näpunäited asendusvõtmete kujundamiseks:

• Kasutage asendusvõtme jaoks väikest fikseeritud laiusega andmetüüpi. See parandab jõudlust ja vähendab salvestusruumi.
• Kasutage märksõna IDENTITY või genereerige asendusvõti, kasutades järjestikust või GUID-väärtust. See tagab, et asendusvõti on kordumatu ja seda ei saa muuta.

Laadige hämardamise tabel, kasutades MERGE

Hämarate tabeli laadimiseks on mitmeid viise. Arvesse tuleb võtta teatud tegureid, näiteks jõudlust, andmemahtu ja võib-olla SLA laadimisaegu. Koos ÜHENDAB avaldusega, teostame upsert ilma, et peaksime määrama mitut sisestamis- ja värskendamiskäsku. Saate seadistada ÜHENDAB avaldus a salvestatud protseduur andmete täitmiseks. Seejärel plaanite salvestatud protseduuri programmiliselt käivitada päringuredaktori kaudu, mida me hiljem postituses demonstreerime. Järgmine kood loob salvestatud protseduuri nimega 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õned märkused mõõtmete laadimise kohta:

• Kui kirje sisestatakse esimest korda, sisestatakse sisestatud kuupäev ja värskendatud kuupäev. Kui mis tahes väärtused muutuvad, värskendatakse andmeid ja värskendatud kuupäev kajastab nende muutmise kuupäeva. Sisestatud kuupäev jääb alles.
• Kuna andmeid kasutavad ärikasutajad, peame asendama NULL väärtused, kui neid on, ärile sobivamate väärtustega.

Tuvastage faktid ja rakendage neid

Nüüd, kui oleme kuulutanud oma teravilja konkreetsel ajal toimunud müügisündmuseks, salvestab meie faktitabel meie äriprotsessi numbrilised faktid.

Oleme mõõtmiseks tuvastanud järgmised arvulised faktid:

• Müüdud piletite kogus müügi kohta
• Vahendustasu müügi eest

Fakti rakendamine

Seal on kolme tüüpi faktitabeleid (tehingu faktitabel, perioodiline hetktõmmise faktitabel ja akumuleeruv hetktõmmise faktitabel). Igaüks neist pakub äriprotsessist erinevat vaadet. Meie näites kasutame tehingute faktide tabelit. Tehke järgmised sammud.

1. Looge faktitabel

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;

Lisatakse vaikeväärtusega sisestatud kuupäev, mis näitab, kas ja millal kirje laaditi. Saate seda kasutada faktitabeli uuesti laadimisel, et eemaldada juba laaditud andmed, et vältida dubleerimist.

Faktitabeli laadimine koosneb lihtsast sisestuslausest, mis ühendab teie seotud mõõtmed. Liitume alates DimVenue loodud tabel, mis kirjeldab meie fakte. See on parim tava, kuid see on vabatahtlik kalendri kuupäev dimensioonid, mis võimaldavad lõppkasutajal faktitabelis navigeerida. Andmeid saab laadida uue müügi korral või iga päev; siin tuleb kasuks sisestatud kuupäev või laadimiskuupäev.

Laadime faktitabeli salvestatud protseduuri abil ja kasutame kuupäeva parameetrit.

2. Looge salvestatud protseduur järgmise koodiga. Sama andmete terviklikkuse säilitamiseks, mida kasutasime dimensioonide laadimisel, asendame NULL väärtused, kui neid on, ärile sobivamate väärtustega.

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. Laadige andmed, kutsudes protseduuri järgmise käsuga:

call SalesMart.FactSaleTransactionsLoad(getdate())

Ajastage andmete laadimine

Nüüd saame modelleerimisprotsessi automatiseerida, ajastades salvestatud protseduurid Amazon Redshift Query Editor V2-s. Tehke järgmised sammud.

1. Esmalt kutsume välja mõõtmekoormuse ja pärast mõõtmete koormuse edukat käivitamist algab faktikoormus:

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

Kui mõõtmete laadimine ebaõnnestub, faktikoormust ei käivitata. See tagab andmete järjepidevuse, sest me ei soovi laadida faktitabelit aegunud mõõtmetega.

2. Laadimise ajastamiseks valige Ajakava päringuredaktoris V2.

3. Ajastame päringu käivitumise iga päev kell 5.
4. Soovi korral saate lubades lisada tõrketeateid Amazoni lihtne teavitusteenus (Amazon SNS) teatised.

Teatage ja analüüsige andmeid Amazon Quicksightis

QuickSight on äriteabe teenus, mis muudab ülevaate edastamise lihtsaks. Täielikult hallatava teenusena võimaldab QuickSight hõlpsasti luua ja avaldada interaktiivseid armatuurlaudu, millele pääseb juurde mis tahes seadmest ja mis on manustatud teie rakendustesse, portaalidesse ja veebisaitidele.

Kasutame oma andmemarti faktide visuaalseks esitamiseks armatuurlaua kujul. QuickSighti alustamiseks ja seadistamiseks vaadake Andmestiku loomine andmebaasi abil, mida pole automaatselt tuvastatud.

Kui olete QuickSightis andmeallika loonud, ühendame modelleeritud andmed (data mart) meie asendusvõtme alusel kokku skey. Kasutame seda andmestikku andmeturu visualiseerimiseks.

Meie lõpu armatuurlaud sisaldab andmeturu teadmisi ja vastuseid kriitilistele äriküsimustele, nagu kogu komisjonitasu koha kohta ja suurima müügiga kuupäevad. Järgmine ekraanipilt näitab andmemargi lõpptoodet.

Koristage

Edaspidiste tasude vältimiseks kustutage kõik selle postituse raames loodud ressursid.

Järeldus

Oleme nüüd edukalt juurutanud andmemargi, kasutades meie DimVenue, DimCalendarja FactSaleTransactions tabelid. Meie ladu ei ole täielik; kui saame laiendada andmeturgu rohkemate faktidega ja juurutada rohkem marte, ning äriprotsesside ja nõuete kasvades aja jooksul kasvab ka andmeladu. Selles postituses andsime täieliku ülevaate mõõtmete modelleerimise mõistmisest ja rakendamisest Amazon Redshiftis.

Alustage oma Amazoni punane nihe mõõtmetega mudel tänapäeval.

Autoritest

Bernard Verster on kogenud pilveinsener, kes on aastaid kokku puutunud skaleeritavate ja tõhusate andmemudelite loomisega, andmete integreerimise strateegiate määratlemisega ning andmete haldamise ja turvalisuse tagamisega. Ta on kirglik andmete kasutamise vastu, et koguda teadmisi, ühildades samal ajal ärinõuete ja eesmärkidega.

Abhishek Pan on WWSO spetsialist SA-Analytics, kes töötab AWS India avaliku sektori klientidega. Ta suhtleb klientidega, et määratleda andmepõhise strateegia, korraldada analüütika kasutusjuhtude põhjalikke sukeldumise seansse ning kavandada skaleeritavaid ja tõhusaid analüütilisi rakendusi. Tal on 12-aastane kogemus ja ta on kirglik andmebaaside, analüütika ja AI/ML-i vastu. Ta on innukas reisija ja püüab jäädvustada maailma läbi oma kaameraobjektiivi.

• SEO-põhise sisu ja PR-levi. Võimenduge juba täna.
• PlatoData.Network Vertikaalne generatiivne Ai. Jõustage ennast. Juurdepääs siia.
• PlatoAiStream. Web3 luure. Täiustatud teadmised. Juurdepääs siia.
• PlatoESG. Autod/elektrisõidukid, Süsinik, CleanTech, Energia, Keskkond päikeseenergia, Jäätmekäitluse. Juurdepääs siia.
• BlockOffsets. Keskkonnakompensatsiooni omandi ajakohastamine. Juurdepääs siia.
• Allikas: https://aws.amazon.com/blogs/big-data/dimensional-modeling-in-amazon-redshift/