Zephyrnet-logo

Versnel uw datawarehouse-migratie naar Amazon Redshift – deel 5

Datum:

Dit is de vijfde in een reeks berichten. We zijn verheugd om tientallen nieuwe functies te delen om uw schemaconversie te automatiseren; behoud uw investering in bestaande scripts, rapporten en applicaties; queryprestaties versnellen; en vereenvoudig mogelijk uw migraties van verouderde datawarehouses naar Amazon roodverschuiving.

Bekijk alle berichten in deze serie:

Amazon Redshift is het toonaangevende datawarehouse in de cloud. Geen enkel ander datawarehouse maakt het zo eenvoudig om nieuwe inzichten uit uw data te halen. Met Amazon Redshift kun je exabytes aan gegevens opvragen in je datawarehouse, operationele datastores en data lake met behulp van standaard SQL. U kunt ook andere AWS-services integreren, zoals: Amazon EMR, Amazone Athene, Amazon Sage Maker, AWS lijm, AWS Lake-formatie en Amazon Kinesis om alle analytische mogelijkheden in de AWS Cloud te gebruiken.

Tot nu toe was het migreren van een datawarehouse naar AWS een complexe onderneming, met veel handmatige inspanning. U moet handmatig syntaxisverschillen herstellen, code injecteren om eigen functies te vervangen en de prestaties van query's en rapporten op het nieuwe platform handmatig afstemmen.

Verouderde workloads kunnen afhankelijk zijn van niet-ANSI, eigen functies die niet rechtstreeks worden ondersteund door moderne databases zoals Amazon Redshift. Veel Teradata-toepassingen gebruiken bijvoorbeeld SET-tabellen, die uniciteit van volledige rijen afdwingen: er kunnen geen twee rijen in een tabel zijn die identiek zijn in al hun kenmerkwaarden.

Als je een Amazon Redshift-gebruiker bent, wil je misschien SET-semantiek implementeren, maar je kunt niet vertrouwen op een native databasefunctie. U kunt de ontwerppatronen in dit bericht gebruiken om SET-semantiek in uw SQL-code te emuleren. Als u een werklast naar Amazon Redshift migreert, kunt u ook de AWS Schema Conversie Tool (AWS SCT) om de ontwerppatronen automatisch toe te passen als onderdeel van uw codeconversie.

In dit bericht beschrijven we de SQL-ontwerppatronen en analyseren we hun prestaties, en laten we zien hoe AWS SCT dit kan automatiseren als onderdeel van uw datawarehouse-migratie. Laten we beginnen met te begrijpen hoe SET-tabellen zich gedragen in Teradata.

Teradata SET-tabellen

Op het eerste gezicht lijkt een SET-tabel misschien op een tabel met een primaire sleutel die in al zijn kolommen is gedefinieerd. Er zijn echter enkele belangrijke semantische verschillen met traditionele primaire sleutels. Overweeg de volgende tabeldefinitie in Teradata:

CREATE SET TABLE testschema.sales_by_month ( sales_dt DATE
, amount DECIMAL(8,2)
);

We vullen de tabel als volgt met vier rijen gegevens:

select * from testschema.sales_by_month order by sales_dt; *** Query completed. 4 rows found. 2 columns returned. *** Total elapsed time was 1 second. sales_dt amount
-------- ----------
22/01/01 100.00
22/01/02 200.00
22/01/03 300.00
22/01/04 400.00

Merk op dat we geen UNIEKE PRIMAIRE INDEX (vergelijkbaar met een primaire sleutel) in de tabel hebben gedefinieerd. Wanneer we nu proberen een nieuwe rij in de tabel in te voegen die een duplicaat is van een bestaande rij, mislukt het invoegen:

INSERT IGNORE INTO testschema.sales_by_month values (20220101, 100); *** Failure 2802 Duplicate row error in testschema.sales_by_month. Statement# 1, Info =0 *** Total elapsed time was 1 second.

Evenzo, als we proberen een bestaande rij bij te werken zodat deze een duplicaat wordt van een andere rij, mislukt de update:

UPDATE testschema.sales_by_month SET sales_dt = 20220101, amount = 100
WHERE sales_dt = 20220104 and amount = 400; *** Failure 2802 Duplicate row error in testschema.sales_by_month. Statement# 1, Info =0 *** Total elapsed time was 1 second.

Met andere woorden, eenvoudige INSERT-VALUE- en UPDATE-instructies mislukken als ze dubbele rijen in een Teradata SET-tabel introduceren.

Er is een opmerkelijke uitzondering op deze regel. Beschouw de volgende faseringstabel, die dezelfde kenmerken heeft als de doeltabel:

CREATE MULTISET TABLE testschema.sales_by_month_stg ( sales_dt DATE
, amount DECIMAL(8,2)
);

De verzameltabel is een MULTISET-tabel en accepteert dubbele rijen. We vullen drie rijen in de verzameltabel. De eerste rij is een duplicaat van een rij in de doeltabel. De tweede en derde rij zijn duplicaten van elkaar, maar dupliceren geen van de doelrijen.

select * from testschema.sales_by_month_stg; *** Query completed. 3 rows found. 2 columns returned. *** Total elapsed time was 1 second. sales_dt amount
-------- ----------
22/01/01 100.00
22/01/05 500.00
22/01/05 500.00

Nu voegen we de staging-gegevens met succes in de doeltabel (wat een SET-tabel is):

INSERT IGNORE INTO testschema.sales_by_month (sales_dt, amount)
SELECT sales_dt, amount FROM testschema.sales_by_month_stg; *** Insert completed. One row added. *** Total elapsed time was 1 second.

Als we de doeltabel onderzoeken, kunnen we zien dat een enkele rij voor (2022-01-05, 500) is ingevoegd en dat de dubbele rij voor (2022-01-01, 100) is weggegooid. In wezen negeert Teradata alle dubbele rijen stil wanneer het een INSERT-SELECT-instructie uitvoert. Dit omvat duplicaten in de verzameltabel en duplicaten die worden gedeeld tussen de staging- en doeltabellen.

select * from testschema.sales_by_month order by sales_dt; *** Query completed. 6 rows found. 2 columns returned. *** Total elapsed time was 1 second. sales_dt amount
-------- ----------
22/01/01 100.00
22/01/02 200.00
22/01/03 300.00
22/01/03 200.00
22/01/04 400.00
22/01/05 500.00

In wezen gedragen SET-tabellen zich anders, afhankelijk van het type bewerking dat wordt uitgevoerd. Een INSERT-VALUE- of UPDATE-bewerking mislukt als deze een dubbele rij in het doel introduceert. Een INSERT-SELECT-bewerking mislukt niet als de verzameltabel een dubbele rij bevat, of als een dubbele rij wordt gedeeld tussen de staging- en tabeltabellen.

In dit bericht gaan we niet in detail in op het converteren van INSERT-VALUE- of UPDATE-statements. Deze instructies hebben meestal betrekking op een of enkele rijen en hebben minder impact op de prestaties dan INSERT-SELECT-instructies. Voor INSERT-VALUE- of UPDATE-instructies kunt u de rij (of rijen) die wordt gemaakt, materialiseren en die set aan de doeltabel koppelen om op duplicaten te controleren.

INSERT-SELECTIE

In de rest van dit bericht analyseren we INSERT-SELECT-statements zorgvuldig. Klanten hebben ons verteld dat INSERT-SELECT-bewerkingen tot 78% van de INSERT-werkbelasting kunnen omvatten ten opzichte van SET-tabellen. Het gaat ons om uitspraken met de volgende vorm:

INSERT into <target table> SELECT * FROM <staging table>

Het schema van de verzameltabel is kolomsgewijs identiek aan de doeltabel. Zoals we eerder vermeldden, kan een dubbele rij onder twee verschillende omstandigheden verschijnen:

  • De staging-tabel is niet set-uniek, wat betekent dat er twee of meer dubbele rijen in de staging-gegevens zijn
  • Er is een rij x in de verzameltabel en een identieke rij x in de doeltabel

Omdat Amazon Redshift multiset-tabelsemantiek ondersteunt, is het mogelijk dat de staging-tabel duplicaten bevat (de eerste omstandigheid die we hebben vermeld). Daarom moet elke automatisering beide gevallen aanpakken, omdat beide een duplicaat kunnen introduceren in een Amazon Redshift-tabel.

Op basis van deze analyse hebben we de volgende algoritmen geïmplementeerd:

  • MIN – Dit implementeert de volledige set logische deduplicatie met behulp van SQL MINUS. MINUS werkt in alle gevallen, ook wanneer de verzameltabel niet uniek is ingesteld en wanneer het snijpunt van de verzameltabel en de doeltabel niet leeg is. MINUS heeft ook het voordeel dat NULL-waarden geen speciale vergelijkingslogica vereisen om NULL-naar-NULL-vergelijkingen te omzeilen. MINUS heeft de volgende syntaxis:
    INSERT IGNORE INTO <target table> (<column list>)
    SELECT <column list> FROM <staging table> MINUS
    SELECT <column list> FROM <target table>;

  • MIN-MIN-MAX – Dit is een optimalisatie op MINUS die een filter bevat om de scan van de doeltabel te beperken op basis van de waarden in de werktafeltabel. Met de min/max-filters kan de query-engine grote aantallen blokken overslaan tijdens tabelscans. Zie je wel Werken met sorteersleutels voor meer details.
    INSERT IGNORE INTO <target table>(<column list>)
    SELECT <column list> FROM <staging table> MINUS
    SELECT <column list> FROM <target table>
    WHERE <target table>.<sort key> >= (SELECT MIN(<sort key>) FROM <staging table>) AND <target table>).<sort key> <= (SELECT MAX(<sort key>) FROM <staging table>)
    );

We hebben ook andere algoritmen overwogen, maar we raden u af deze te gebruiken. U kunt bijvoorbeeld een GROUP BY uitvoeren om duplicaten in de verzameltabel te verwijderen, maar deze stap is niet nodig als u de operator MINUS gebruikt. U kunt ook een left (of right) outer join uitvoeren om gedeelde duplicaten tussen de staging- en doeltabellen te vinden, maar dan is extra logica nodig om rekening te houden met NULL = NULL-voorwaarden.

Performance

We hebben de MINUS- en MINUS-MIN-MAX-algoritmen getest op Amazon Redshift. We hebben de algoritmen uitgevoerd op twee Amazon Redshift-clusters. De eerste configuratie bestond uit 6 x ra3.4xlarge nodes. De tweede bestond uit 12 x ra3.4xgrote knooppunten. Elk knooppunt bevatte 12 CPU's en 96 GB geheugen.

We hebben de werkgebied- en doeltabellen gemaakt met identieke sorteer- en distributiesleutels om gegevensverplaatsing te minimaliseren. We hebben dezelfde doeldataset in beide clusters geladen. De doeldataset bestond uit 1.1 miljard rijen gegevens. Vervolgens hebben we staging-gegevenssets gemaakt die varieerden van 20 miljoen tot 200 miljoen rijen, in stappen van 20 miljoen rijen.

De volgende grafiek toont onze resultaten.

De testgegevens zijn kunstmatig gegenereerd en er was enige scheeftrekking in de waarden van de verdeelsleutel. Dit komt tot uiting in de kleine afwijkingen van lineariteit in de uitvoering.

U kunt echter de prestatieverbetering waarnemen die wordt geboden door het MINUS-MIN-MAX-algoritme ten opzichte van het basis-MINUS-algoritme (door oranje lijnen of blauwe lijnen met zichzelf te vergelijken). Als u SET-tabellen in Amazon Redshift implementeert, raden we u aan MINUS-MIN-MAX te gebruiken, omdat dit algoritme een gelukkige convergentie van eenvoudige, leesbare code en goede prestaties biedt.

Automatisering

Alle Amazon Redshift-tabellen staan ​​dubbele rijen toe, dat wil zeggen dat het standaard MULTISET-tabellen zijn. Als u een Teradata-workload converteert om op Amazon Redshift te draaien, moet u de SET-semantiek buiten de database afdwingen.

We zijn blij om te delen dat AWS SCT automatisch uw SQL-code converteert die werkt met SET-tabellen. AWS SCT zal INSERT-SELECT herschrijven die SET-tabellen laadt om de herschrijfpatronen op te nemen die we hierboven hebben beschreven.

Laten we eens kijken hoe dit werkt. Stel dat u de volgende doeltabeldefinitie in Teradata hebt:

CREATE SET TABLE testschema.fact ( id bigint NOT NULL
, se_sporting_event_id INTEGER NOT NULL
, se_sport_type_name VARCHAR(15) NOT NULL
, se_home_team_id INTEGER NOT NULL
, se_away_team_id INTEGER NOT NULL
, se_location_id INTEGER NOT NULL
, se_start_date_time DATE NOT NULL
, se_sold_out INTEGER DEFAULT 0 NOT NULL
, stype_sport_type_name varchar(15) NOT NULL
, stype_short_name varchar(10) NOT NULL
, stype_long_name varchar(60) NOT NULL
, stype_description varchar(120)
, sd_sport_type_name varchar(15) NOT NULL
, sd_sport_league_short_name varchar(10) NOT NULL
, sd_short_name varchar(10) NOT NULL
, sd_long_name varchar(60)
, sd_description varchar(120)
, sht_id INTEGER NOT NULL
, sht_name varchar(30) NOT NULL
, sht_abbreviated_name varchar(10)
, sht_home_field_id INTEGER , sht_sport_type_name varchar(15) NOT NULL
, sht_sport_league_short_name varchar(10) NOT NULL
, sht_sport_division_short_name varchar(10)
, sat_id INTEGER NOT NULL
, sat_name varchar(30) NOT NULL
, sat_abbreviated_name varchar(10)
, sat_home_field_id INTEGER , sat_sport_type_name varchar(15) NOT NULL
, sat_sport_league_short_name varchar(10) NOT NULL
, sat_sport_division_short_name varchar(10)
, sl_id INTEGER NOT NULL
, sl_name varchar(60) NOT NULL
, sl_city varchar(60) NOT NULL
, sl_seating_capacity INTEGER
, sl_levels INTEGER
, sl_sections INTEGER
, seat_sport_location_id INTEGER
, seat_seat_level INTEGER
, seat_seat_section VARCHAR(15)
, seat_seat_row VARCHAR(10)
, seat_seat VARCHAR(10)
, seat_seat_type VARCHAR(15)
, pb_id INTEGER NOT NULL
, pb_full_name varchar(60) NOT NULL
, pb_last_name varchar(30)
, pb_first_name varchar(30)
, ps_id INTEGER NOT NULL
, ps_full_name varchar(60) NOT NULL
, ps_last_name varchar(30)
, ps_first_name varchar(30)
)
PRIMARY INDEX(id)
;

De fasetabel is identiek aan de doeltabel, behalve dat deze is gemaakt als een MULTISET-tabel in Teradata.

Vervolgens maken we een procedure om de feitentabel uit de fasetabel te laden. De procedure bevat een enkele INSERT-SELECT-instructie:

REPLACE PROCEDURE testschema.insert_select() BEGIN INSERT IGNORE INTO testschema.test_fact SELECT * FROM testschema.test_stg;
END;

Nu gebruiken we AWS SCT om de opgeslagen procedure van Teradata naar Amazon Redshift te converteren. Selecteer eerst de opgeslagen procedure in de brondatabasestructuur, klik vervolgens met de rechtermuisknop en kies Schema converteren.

AWS SCT converteert de opgeslagen procedure (en ingebedde INSERT-SELECT) met behulp van het MINUS-MIN-MAX herschrijfpatroon.

En dat is het! Momenteel voert AWS SCT alleen herschrijvingen uit voor INSERT-SELECT omdat deze instructies intensief worden gebruikt door ETL-workloads en de meeste impact hebben op de prestaties. Hoewel het voorbeeld dat we gebruikten was ingebed in een opgeslagen procedure, kunt u AWS SCT ook gebruiken om dezelfde instructies te converteren als ze zich in BTEQ-scripts, macro's of toepassingsprogramma's bevinden. Download de nieuwste versie van AWS SCT en probeer het eens!

Conclusie

In dit bericht hebben we laten zien hoe u SET-tabelsemantiek implementeert in Amazon Redshift. U kunt de beschreven ontwerppatronen gebruiken om nieuwe toepassingen te ontwikkelen die SET-semantiek vereisen. Of, als u een bestaande Teradata-workload converteert, kunt u AWS SCT gebruiken om uw INSERT-SELECT-instructies automatisch te converteren, zodat de SET-tabelsemantiek behouden blijft.

We komen snel terug met het volgende deel in deze serie. Kom later terug voor meer informatie over het automatiseren van uw migraties van Teradata naar Amazon Redshift. In de tussentijd kunt u meer te weten komen over Amazon roodverschuiving en AWS SCT. Veel succes met migreren!


Over de auteurs

Michaël Soo is een Principal Database Engineer bij het AWS Database Migration Service-team. Hij bouwt producten en diensten die klanten helpen hun databaseworkloads naar de AWS-cloud te migreren.

Po Hong, PhD, is een Principal Data Architect van de Modern Data Architecture Global Specialty Practice (GSP), AWS Professional Services. Hij heeft een passie voor het helpen van klanten om innovatieve oplossingen te adopteren en te migreren van grootschalige MPP-datawarehouses naar de AWS moderne data-architectuur.

spot_img

Laatste intelligentie

spot_img