Amazon Redshift gematerialiseerde weergaven stelt u in staat om de prestaties van complexe query's die vaak worden uitgevoerd als onderdeel van uw extract, load en transform (ELT), business intelligence (BI) of dashboarding-applicaties aanzienlijk te verbeteren. Gematerialiseerde weergaven berekenen de resultatensets van de SQL-query vooraf en slaan deze op in de weergavedefinitie. Gematerialiseerde weergaven versnellen de toegang tot gegevens, omdat de query de berekening niet elke keer dat de query wordt uitgevoerd opnieuw hoeft uit te voeren, wat ook het resourceverbruik vermindert.

Amazon roodverschuiving heeft de mogelijkheid om uw SQL-query's die niet expliciet verwijzen naar bestaande gerealiseerde weergaven automatisch te herschrijven om een ​​bestaande gerealiseerde weergave te gebruiken als dit de prestaties verbetert. Deze functie is waardevol en in sommige gevallen de enige optie voor prestatie-optimalisatie. Overweeg verpakte ISV-apps of zelfs alleen rapporten: gebruikers hebben vaak geen toegang tot de SQL om te optimaliseren. In sommige gevallen, zelfs als ze wel toegang hebben, is de code of het script zo oud dat niemand ermee bekend is en je niet weet welke regressies, zelfs een kleine verandering, kan veroorzaken.

In dit bericht beschrijven we hoe de functie voor het automatisch herschrijven van query's werkt en enkele scenario's waarin u van deze functie kunt profiteren. Raadpleeg voor informatie over de gematerialiseerde weergavefunctie zelf: Versnel uw ELT- en BI-query's met gematerialiseerde weergaven van Amazon Redshift en Gematerialiseerde weergaven maken in Amazon Redshift.

Alle voorbeelden in dit bericht worden uitgevoerd op een 8 node ra3.4xlarge cluster met de 3 TB TPC-DS cloud-benchmarkdataset.

Laten we eens kijken naar drie verschillende scenario's waarbij de functie voor het automatisch herschrijven van query's zou kunnen helpen: het optimaliseren van joins tussen twee grote tabellen, het optimaliseren van joins voor tabellen met meerdere join-paden en het optimaliseren van tabelscans.

Koppelingen tussen twee grote tabellen optimaliseren

Er zijn veel situaties waarin u twee grote tabellen hebt die vaak worden samengevoegd. In dit geval kan het maken van een gerealiseerde weergave die deze twee tabellen samenvoegt, de prestaties van die query's helpen verbeteren. Gematerialiseerde views berekenen de join vooraf en slaan de resultaten op, zodat volgende runs alleen de opgeslagen resultaten hoeven op te halen; het is niet nodig om elke keer de dure JOINs uit te voeren. Met automatisch herschrijven van query's hoeft geen van de query's van eindgebruikers te worden gewijzigd om te verwijzen naar de gerealiseerde weergave. Bij het maken van het uitlegplan voor de query vervangt Amazon Redshift de join tussen de twee tabellen door de gerealiseerde weergave.

Standaard gebruikt het automatisch herschrijven van query's alleen een gerealiseerde weergave als deze up-to-date is en alle wijzigingen uit de basistabellen weerspiegelt. Dit betekent dat de query niet wordt herschreven om de gerealiseerde weergave te gebruiken als de basistabellen recentere updates hebben die nog niet worden weergegeven in de gerealiseerde weergave.

Beschouw bijvoorbeeld de volgende SQL-query. De query voegt twee tabellen samen: store_sales (8,639,936,081 rijen) en customer (30,000,000 rijen):

SELECT cust.c_customer_id FROM store_sales sales
INNER JOIN customer cust
ON sales.ss_customer_sk = cust.c_customer_sk
GROUP BY cust.c_customer_id;

De query wordt uitgevoerd in 545,520 milliseconden; het volgende is het plan voor de uitleg van de query:

XN HashAggregate (cost=9602679386653.98..9602679386653.98 rows=29705556 width=20) -> XN Hash Join DS_BCAST_INNER (cost=375000.00..9602659714194.54 rows=7868983773 width=20) Hash Cond: (""outer"".ss_customer_sk = ""inner"".c_customer_sk) -> XN Seq Scan on store_sales sales (cost=0.00..86399365.12 rows=8245454518 width=4) Filter: (ss_customer_sk IS NOT NULL) -> XN Hash (cost=300000.00..300000.00 rows=30000000 width=24) -> XN Seq Scan on customer cust (cost=0.00..300000.00 rows=30000000 width=24)

Laten we een gematerialiseerde weergave maken die de verbinding tussen de store_sales en customer tabellen met behulp van de volgende SQL-instructie:

CREATE MATERIALIZED VIEW cust_store_sales
AS SELECT cust.c_customer_id
, cust.c_first_name
, cust.c_last_name
, sales.ss_item_sk
, sales.ss_quantity
, cust.c_current_addr_sk
FROM store_sales sales
INNER JOIN customer cust
ON sales.ss_customer_sk = cust.c_customer_sk;

Laten we nu de oorspronkelijke query opnieuw uitvoeren:

SELECT cust.c_customer_id FROM store_sales sales
INNER JOIN customer cust
ON sales.ss_customer_sk = cust.c_customer_sk
GROUP BY cust.c_customer_sk;

De query wordt veel sneller uitgevoerd (46,493 milliseconden). Dit komt door de functie voor het automatisch herschrijven van query's, die de voorgaande query heeft herschreven om de nieuw gemaakte gerealiseerde weergave te gebruiken in plaats van beide tabellen samen te voegen. Het uitlegplan voor deze query toont deze wijziging:

XN HashAggregate (cost=103138905.60..103138905.60 rows=29705556 width=20) -> XN Seq Scan on mv_tbl__cust_store_sales__0 derived_table1 (cost=0.00..82511124.48 rows=8251112448 width=20)

De oorspronkelijke query-uitvoering verbruikte ook 1,263 CPU-seconden en las 45,013 datablokken, terwijl de query die werd uitgevoerd na het maken van de gerealiseerde view slechts 898 CPU-seconden verbruikte en 29,256 blokken las. Dat is een vermindering van 29% in CPU-verbruik en 35% in gelezen blokken.

De optimizer kan ook de volgende query herschrijven om de eerder gemaakte gerealiseerde weergave te gebruiken, inclusief de extra join naar de customer_address tafel:

SELECT
cust.c_customer_id
,addr.ca_state
FROM store_sales sales
INNER JOIN customer cust
ON sales.ss_customer_sk = cust.c_customer_sk
INNER JOIN customer_address addr
ON cust.c_current_addr_sk = addr.ca_address_sk
GROUP BY cust.c_customer_id, addr.ca_state; XN HashAggregate (cost=30242919089.37..30242919089.37 rows=1544688912 width=26) -> XN Hash Join DS_BCAST_INNER (cost=542661.20..30201663527.13 rows=8251112448 width=26) Hash Cond: ("outer".c_current_addr_sk = "inner".ca_address_sk) -> XN Seq Scan on mv_tbl__cust_store_sales_1__0 derived_table1 (cost=0.00..82511124.48 rows=8251112448 width=24) -> XN Hash (cost=150000.00..150000.00 rows=15000000 width=10) -> XN Seq Scan on customer_address addr (cost=0.00..150000.00 rows=15000000 width=10)

Optimaliseer joins voor tabellen met meerdere join-paden

Voor grote tabellen op Amazon Redshift is de ideale distributiestijl 'KEY', waarbij de distributiesleutel de kolom is die het meest wordt gebruikt in de JOIN-clausule. Er zijn situaties waarin sommige grote tabellen meerdere join-paden hebben. 50% van de zoekopdrachten kan een bepaalde kolom gebruiken om deel te nemen aan de tabel, en de andere 50% van de zoekopdrachten kan een andere kolom gebruiken om deel te nemen aan de tabel. Beide typen query's zijn belangrijk en hebben strenge prestatie-eisen. In dit geval kunt u één kolom kiezen als verdeelsleutel voor de tabel en vervolgens een gerealiseerde weergave maken met de tweede kolom als verdeelsleutel. Dit is mogelijk omdat gematerialiseerde weergaven hun eigen distributie- en sorteersleutels kunnen hebben.

Hier is een voorbeeld om te illustreren hoe dit werkt.

De web_sales tabel (2,159,968,881 rijen) heeft de distributiesleutel ws_order_number. Dit helpt bij het optimaliseren van een meerderheid van de zoekopdrachten (70% van de joins naar deze tabel wordt gebruikt) ws_order_number als de join-kolom). De overige 30% gebruikt de kolom ws_bill_customer_sk om deel te nemen aan de tabel, zoals weergegeven in de volgende SQL-instructie. Deze query duurde 12,790 milliseconden om uit te voeren.

SELECT c_customer_id
, c_email_address FROM web_sales ws
INNER JOIN customer cs
ON ws.ws_bill_customer_sk=cs.c_customer_sk;

We kunnen de gerealiseerde weergave maken om de prestaties van de resterende 30% van de zoekopdrachten te verbeteren. Let op het DISTKEY-sleutelwoord in de volgende code. We hebben een nieuwe verdeelsleutel gedefinieerd voor de gematerialiseerde weergave (ws_bill_customer_sk):

CREATE MATERIALIZED VIEW web_sales_cust_dist
DISTKEY (ws_bill_customer_sk)
AS
SELECT * FROM web_sales;

Als u de volgende query opnieuw uitvoert, worden rijen veel sneller geretourneerd dan voorheen (7,715 milliseconden versus 12,790 milliseconden):

SELECT c_customer_id
, c_email_address FROM web_sales ws
INNER JOIN customer cs
ON ws.ws_bill_customer_sk=cs.c_customer_sk;

Nogmaals, het uitlegplan van de query is gewijzigd; het verwijst nu naar de gerealiseerde weergave, hoewel de SQL-instructie niet expliciet verwijst naar de gerealiseerde weergave:

XN Hash Join DS_DIST_NONE (cost=375000.00..696964927.69 rows=2159968768 width=74) Hash Cond: (""outer"".ws_bill_customer_sk = ""inner"".c_customer_sk) -> XN Seq Scan on mv_tbl__web_sales_cust_dist__0 derived_table1 (cost=0.00..21599687.68 rows=2159968768 width=4) -> XN Hash (cost=300000.00..300000.00 rows=30000000 width=78) -> XN Seq Scan on customer cs (cost=0.00..300000.00 rows=30000000 width=78)

Tabelscans optimaliseren

Tabelscans op Amazon Redshift worden efficiënt gemaakt door het gebruik van sorteersleutels. Sorteersleutels bepalen de volgorde waarin de kolommen in de datablokken worden opgeslagen. Als u een kolom kiest die vaak voorkomt in uw filtervoorwaarden als sorteersleutel, kan dit de queryprestaties aanzienlijk verbeteren.

Samengestelde sorteersleutels met meerdere kolommen kunnen in uw tabel worden gedefinieerd voor het geval meerdere kolommen goede kandidaten zijn voor sorteersleutels. Maar in sommige situaties waarin twee of meer kolommen met een hoge kardinaliteit sorteersleutelkandidaten zijn, levert de samengestelde sorteersleutel mogelijk onvoldoende prestaties. In deze gevallen kan een gerealiseerde weergave worden gemaakt met een andere sorteersleutel om die gegevens in een alternatieve gesorteerde volgorde te houden om tegemoet te komen aan een subset van de zoekopdrachten.

In de volgende voorbeeldquery is de web_sales tabel gebruikt de kolom ws_sold_date_sk voor de sorteersleutel, omdat dit de kolom is die gewoonlijk wordt gebruikt voor het filteren van rijen. Een kleinere set query's gebruiken ws_sales_price voor het filteren van rijen. Aangezien beide ws_sold_date_sk en ws_sales_price zijn kolommen met een hoge kardinaliteit met veel unieke waarden, een samengestelde sorteersleutel met beide kolommen is mogelijk niet geschikt voor alle querypatronen.

SELECT *
FROM web_sales WHERE ws_sales_price BETWEEN 50 AND 100;

Laten we de volgende gerealiseerde weergave maken en kijken hoe deze de prestaties van de voorgaande query kan helpen verbeteren:

CREATE MATERIALIZED VIEW web_sales_sort_on_price
SORTKEY (ws_sales_price)
AS
SELECT * FROM web_sales;

Als u de volgende query uitvoert, worden rijen veel sneller geretourneerd (5 milliseconden versus 3,548 milliseconden) omdat de automatische herschrijving van de query de gerealiseerde weergave gebruikt:

SELECT *
FROM web_sales WHERE ws_sales_price BETWEEN 50 AND 100;

Het volgende is het nieuwe uitlegplan:

XN Seq Scan on mv_tbl__web_sales_cust_dist__0 derived_table1 (cost=0.00..32399531.52 rows=10799844 width=260) Filter: ((ws_sales_price <= 100.00) AND (ws_sales_price >= 50.00))

Conclusie

Gematerialiseerde weergaven op Amazon Redshift kunnen een krachtige optimalisatietool zijn als ze op de juiste manier worden gebruikt. Met automatisch herschrijven van query's kunt u query's optimaliseren zonder gevolgen voor eindgebruikers of hun query's. Hierdoor kunt u gematerialiseerde views creëren nadat de applicatie live is gegaan. Sommige klanten plannen dit als onderdeel van hun strategie voor prestatieoptimalisatie bij het bouwen van nieuwe apps. De echte waarde is dat u query's en workloads kunt optimaliseren zonder de broncode of scripts te hoeven wijzigen, en u kunt zelfs profiteren van een gedeeltelijke overeenkomst.

Over de auteurs

Harshida Patel is een Specialist Sr. Solutions Architect, Analytics bij AWS.

Jeetesh Srivastva is Sr. Manager, Specialist Solutions Architect bij AWS. Hij is gespecialiseerd in Amazon Redshift en werkt samen met klanten om schaalbare oplossingen te implementeren met behulp van Amazon Redshift en andere AWS Analytic-services. Hij heeft gewerkt aan het leveren van on-premises en cloudgebaseerde analytische oplossingen voor klanten in de bancaire en financiële sector en de horecasector.

Sain Das is een Analytics Specialist Solutions Architect bij AWS en helpt klanten schaalbare cloudoplossingen te bouwen die gegevens helpen omzetten in bruikbare inzichten.

Somdeb Bhattacharjee is Enterprise Solutions Architect bij AWS.

Bron: https://aws.amazon.com/blogs/big-data/optimize-your-analytical-workloads-using-the-automatic-query-rewrite-feature-of-amazon-redshift-materialized-views/