Best practices voor serverloze SQL-pool in Azure Synapse Analytics

  • Artikel

In dit artikel vindt u een verzameling aanbevolen procedures voor het gebruik van een serverloze SQL-pool. Een serverloze SQL-pool is een resource in Azure Synapse Analytics. Als u met een toegewezen SQL-pool werkt, raadpleegt u best practices voor toegewezen SQL-pools voor specifieke richtlijnen.

Met een serverloze SQL-pool kunt u query's uitvoeren op bestanden in uw Azure Storage-accounts. Er zijn geen lokale opslag- of opnamemogelijkheden. Alle bestanden waarop het querydoel is gericht, zijn extern voor een serverloze SQL-pool. Alles met betrekking tot het lezen van bestanden uit de opslag kan van invloed zijn op de prestaties van query's.

Enkele algemene richtlijnen zijn:

  • Zorg ervoor dat uw clienttoepassingen worden gebruikt in combinatie met een serverloze SQL-pool.
    • Als u clienttoepassingen buiten Azure gebruikt, moet u ervoor zorgen dat u een serverloze SQL-pool gebruikt in een regio die zich dicht bij uw clientcomputer bevindt. Voorbeelden van clienttoepassingen zijn Power BI Desktop, SQL Server Management Studio en Azure Data Studio.
  • Zorg ervoor dat de opslag- en serverloze SQL-pool zich in dezelfde regio bevinden. Voorbeelden van opslag zijn Azure Data Lake Storage en Azure Cosmos DB.
  • Probeer de opslagindeling te optimaliseren met behulp van partitionering en het bewaren van uw bestanden in het bereik tussen 100 MB en 10 GB.
  • Als u een groot aantal resultaten retourneert, moet u ervoor zorgen dat u SQL Server Management Studio of Azure Data Studio gebruikt en niet Azure Synapse Studio. Azure Synapse Studio is een webhulpprogramma dat niet is ontworpen voor grote resultatensets.
  • Als u resultaten filtert op tekenreekskolom, probeert u een BIN2_UTF8 sortering te gebruiken. Raadpleeg sorteringstypen die worden ondersteund voor Synapse SQL voor meer informatie over het wijzigen van sorteringen.
  • Overweeg de resultaten aan de clientzijde in de cache op te slaan met behulp van de importmodus van Power BI of Azure Analysis Services en deze periodiek te vernieuwen. Serverloze SQL-pools kunnen geen interactieve ervaring bieden in de Power BI Direct Query-modus als u complexe query's gebruikt of een grote hoeveelheid gegevens verwerkt.
  • Maximale gelijktijdigheid is niet beperkt en is afhankelijk van de complexiteit van de query en de hoeveelheid gescande gegevens. Een serverloze SQL-pool kan gelijktijdig 1000 actieve sessies verwerken die lichtgewicht query's uitvoeren. De getallen nemen af als de query's complexer zijn of een grotere hoeveelheid gegevens scannen, dus in dat geval kunt u overwegen om gelijktijdigheid te verlagen en query's uit te voeren gedurende een langere periode, indien mogelijk.

Clienttoepassingen en netwerkverbindingen

Zorg ervoor dat uw clienttoepassing is verbonden met de dichtstbijzijnde mogelijke Azure Synapse-werkruimte met de optimale verbinding.

  • Plaats een clienttoepassing op basis van de Azure Synapse-werkruimte. Als u toepassingen zoals Power BI of Azure Analysis Service gebruikt, moet u ervoor zorgen dat deze zich in dezelfde regio bevinden als waar u uw Azure Synapse-werkruimte hebt geplaatst. Maak indien nodig de afzonderlijke werkruimten die zijn gekoppeld aan uw clienttoepassingen. Het plaatsen van een clienttoepassing en de Azure Synapse-werkruimte in verschillende regio's kan leiden tot grotere latentie en tragere streaming van resultaten.
  • Als u gegevens uit uw on-premises toepassing leest, controleert u of de Azure Synapse-werkruimte zich in de regio bevindt die zich dicht bij uw locatie bevindt.
  • Zorg ervoor dat er geen problemen zijn met de netwerkbandbreedte tijdens het lezen van een grote hoeveelheid gegevens.
  • Gebruik Azure Synapse Studio niet om een grote hoeveelheid gegevens te retourneren. Azure Synapse Studio is een webhulpprogramma dat gebruikmaakt van het HTTPS-protocol om gegevens over te dragen. Gebruik Azure Data Studio of SQL Server Management Studio om een grote hoeveelheid gegevens te lezen.

Indeling voor opslag en inhoud

Hier volgen aanbevolen procedures voor opslag en inhoudsindeling in een serverloze SQL-pool.

Plaats de opslag en serverloze SQL-pool op elkaar

Als u de latentie wilt minimaliseren, moet u uw Azure Storage-account of analytische opslag van Azure Cosmos DB en het eindpunt van uw serverloze SQL-pool instellen. Opslagaccounts en eindpunten die zijn ingericht tijdens het maken van de werkruimte, bevinden zich in dezelfde regio.

Als u voor optimale prestaties toegang hebt tot andere opslagaccounts met een serverloze SQL-pool, moet u ervoor zorgen dat deze zich in dezelfde regio bevinden. Als ze zich niet in dezelfde regio bevinden, wordt de latentie voor de netwerkoverdracht van de gegevens tussen de externe regio en de regio van het eindpunt verhoogd.

Beperking van Azure Storage

Meerdere toepassingen en services hebben mogelijk toegang tot uw opslagaccount. Opslagbeperking treedt op wanneer de gecombineerde IOPS of doorvoer die wordt gegenereerd door toepassingen, services en serverloze SQL-poolworkloads de limieten van het opslagaccount overschrijdt. Als gevolg hiervan ondervindt u een aanzienlijk negatief effect op queryprestaties.

Wanneer beperking wordt gedetecteerd, heeft een serverloze SQL-pool ingebouwde verwerking om deze op te lossen. Serverloze SQL-pool doet aanvragen naar opslag in een trager tempo totdat beperking is opgelost.

Tip

Voor een optimale query-uitvoering moet u het opslagaccount niet belasten met andere workloads tijdens het uitvoeren van query's.

Bestanden voorbereiden voor het uitvoeren van query's

Indien mogelijk kunt u bestanden voorbereiden op betere prestaties:

  • Converteer grote CSV- en JSON-bestanden naar Parquet. Parquet is een kolomindeling. Omdat het bestand is gecomprimeerd, zijn de bestandsgrootten kleiner dan CSV- of JSON-bestanden die dezelfde gegevens bevatten. Serverloze SQL-pool slaat de kolommen en rijen over die niet nodig zijn in een query als u Parquet-bestanden leest. Serverloze SQL-pool heeft minder tijd nodig en minder opslagaanvragen om deze te lezen.
  • Als een query is gericht op één groot bestand, profiteert u van het splitsen in meerdere kleinere bestanden.
  • Probeer de GROOTTE van uw CSV-bestand tussen 100 MB en 10 GB te behouden.
  • Het is beter om bestanden met gelijke grootte te hebben voor één OPENROWSET-pad of een externe tabelLOCATIE.
  • Partitioneer uw gegevens door partities op te slaan in verschillende mappen of bestandsnamen. Zie Bestandsnaam- en bestandspadfuncties gebruiken om specifieke partities te targeten.

De analytische opslag en serverloze SQL-pool van Azure Cosmos DB colocateiseren

Zorg ervoor dat uw analytische opslag van Azure Cosmos DB in dezelfde regio is geplaatst als een Azure Synapse-werkruimte. Query's tussen regio's kunnen enorme latenties veroorzaken. Gebruik de regio-eigenschap in het verbindingsreeks om expliciet de regio op te geven waar de analytische opslag is geplaatst (zie Query's uitvoeren op Azure Cosmos DB met behulp van een serverloze SQL-pool):account=<database account name>;database=<database name>;region=<region name>'

CSV-optimalisaties

Hier volgen aanbevolen procedures voor het gebruik van CSV-bestanden in een serverloze SQL-pool.

Gebruik PARSER_VERSION 2.0 om query's uit te voeren op CSV-bestanden

U kunt een voor prestaties geoptimaliseerde parser gebruiken wanneer u query's uitvoert op CSV-bestanden. Zie PARSER_VERSION voor meer informatie.

Handmatig statistieken maken voor CSV-bestanden

Serverloze SQL-pool is afhankelijk van statistieken om optimale uitvoeringsplannen voor query's te genereren. Statistieken worden automatisch gemaakt voor kolommen met behulp van steekproeven en in de meeste gevallen is het steekproefpercentage minder dan 100%. Deze stroom is hetzelfde voor elke bestandsindeling. Houd er rekening mee dat bij het lezen van CSV met parserversie 1.0 steekproeven niet wordt ondersteund en dat het automatisch maken van statistieken niet plaatsvindt met een steekproefpercentage van minder dan 100%. Voor kleine tabellen met geschatte lage kardinaliteit (aantal rijen) wordt het automatisch maken van statistieken geactiveerd met een steekproefpercentage van 100%. Dit betekent dat fullscan wordt geactiveerd en automatische statistieken worden gemaakt, zelfs voor CSV met parserversie 1.0. Als statistieken niet automatisch worden gemaakt, maakt u handmatig statistieken voor kolommen die u gebruikt in query's, met name die worden gebruikt in DISTINCT, JOIN, WHERE, ORDER BY en GROUP BY. Controleer statistieken in serverloze SQL-pool voor meer informatie.

Data types

Hier volgen aanbevolen procedures voor het gebruik van gegevenstypen in een serverloze SQL-pool.

De juiste gegevenstypen gebruiken

De gegevenstypen die u in uw query gebruikt, zijn van invloed op de prestaties en gelijktijdigheid. U kunt betere prestaties krijgen als u deze richtlijnen volgt:

  • Gebruik de kleinste gegevensgrootte die geschikt is voor de grootst mogelijke waarde.
    • Als de maximale lengte van de tekenwaarde 30 tekens is, gebruikt u een gegevenstype teken van lengte 30.
    • Als alle tekenkolomwaarden een vaste grootte hebben, gebruikt u teken of nchar. Gebruik anders varchar of nvarchar.
    • Als de maximale kolomwaarde voor gehele getallen 500 is, gebruikt u smallint omdat dit het kleinste gegevenstype is dat geschikt is voor deze waarde. Zie bereiken voor gehele getallen voor gegevenstypen voor meer informatie.
  • Gebruik indien mogelijk varchar en char in plaats van nvarchar en nchar.
    • Gebruik het varchar-type met een UTF8-sortering als u gegevens leest uit Parquet, Azure Cosmos DB, Delta Lake of CSV met UTF-8-codering.
    • Gebruik het varchar-type zonder UTF8-sortering als u gegevens leest uit CSV-niet-Unicode-bestanden (bijvoorbeeld ASCII).
    • Gebruik het type nvarchar als u gegevens leest uit een CSV UTF-16-bestand.
  • Gebruik indien mogelijk gegevenstypen op basis van een geheel getal. SORTEER-, JOIN- en GROUP BY-bewerkingen worden sneller uitgevoerd op gehele getallen dan op tekengegevens.
  • Als u schemadeductie gebruikt, controleert u de uitgestelde gegevenstypen en overschrijft u deze expliciet met de kleinere typen, indien mogelijk.

Uitgestelde gegevenstypen controleren

Schemadeductie helpt u snel query's te schrijven en gegevens te verkennen zonder bestandsschema's te kennen. De kosten van dit gemak zijn dat uitgestelde gegevenstypen mogelijk groter zijn dan de werkelijke gegevenstypen. Deze discrepantie treedt op wanneer er onvoldoende informatie in de bronbestanden is om ervoor te zorgen dat het juiste gegevenstype wordt gebruikt. Parquet-bestanden bevatten bijvoorbeeld geen metagegevens over de maximale lengte van tekenkolommen. Serverloze SQL-pool zorgt er dus voor dat deze wordt afgeleid als varchar(8000).

Houd er rekening mee dat de situatie kan verschillen in het geval van de deelbare beheerde en externe Spark-tabellen die in de SQL-engine als externe tabellen worden weergegeven. Spark-tabellen bieden andere gegevenstypen dan de Synapse SQL-engines. De toewijzing tussen gegevenstypen van spark-tabellen en SQL-typen vindt u hier.

U kunt de door het systeem opgeslagen procedure sp_describe_first_results_set gebruiken om de resulterende gegevenstypen van uw query te controleren.

In het volgende voorbeeld ziet u hoe u de uitgestelde gegevenstypen kunt optimaliseren. Deze procedure wordt gebruikt om de uitgestelde gegevenstypen weer te geven:

EXEC sp_describe_first_result_set N'
    SELECT
        vendor_id, pickup_datetime, passenger_count
    FROM  
        OPENROWSET(
            BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/*/*/*'',
            FORMAT=''PARQUET''
        ) AS nyc';

Dit is de resultatenset:

is_hidden column_ordinal name system_type_name max_length
0 1 vendor_id varchar(8000) 8000
0 2 pickup_datetime datetime2(7) 8
0 3 passenger_count geheel getal (int) 4

Nadat u de uitgestelde gegevenstypen voor de query hebt geweten, kunt u de juiste gegevenstypen opgeven:

SELECT
    vendorID, tpepPickupDateTime, passengerCount
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2018/puMonth=*/*.snappy.parquet',
        FORMAT='PARQUET'
    )  
    WITH (
        vendorID varchar(4), -- we used length of 4 instead of the inferred 8000
        tpepPickupDateTime datetime2,
        passengerCount int
    ) AS nyc;

Filteroptimalisatie

Hier volgen aanbevolen procedures voor het gebruik van query's in een serverloze SQL-pool.

Jokertekens pushen naar lagere niveaus in het pad

U kunt jokertekens in uw pad gebruiken om query's uit te voeren op meerdere bestanden en mappen. Serverloze SQL-pool bevat bestanden in uw opslagaccount, beginnend vanaf het eerste sterretje (*), met behulp van de opslag-API. Hiermee worden bestanden verwijderd die niet overeenkomen met het opgegeven pad. Het verminderen van de eerste lijst met bestanden kan de prestaties verbeteren als er veel bestanden zijn die overeenkomen met het opgegeven pad tot aan het eerste jokerteken.

Bestandsnaam- en bestandspadfuncties gebruiken om specifieke partities te targeten

Gegevens worden vaak ingedeeld in partities. U kunt een serverloze SQL-pool instrueren om query's uit te voeren op bepaalde mappen en bestanden. Dit vermindert het aantal bestanden en de hoeveelheid gegevens die de query moet lezen en verwerken. Een extra bonus is dat u betere prestaties krijgt.

Lees voor meer informatie over de bestands- en bestandspadfuncties en bekijk de voorbeelden voor het uitvoeren van query's op specifieke bestanden.

Tip

Cast altijd de resultaten van het bestandspad en de bestandsnaamfuncties naar de juiste gegevenstypen. Als u tekengegevenstypen gebruikt, moet u de juiste lengte gebruiken.

Functies die worden gebruikt voor het elimineren van partities, bestandspaden en bestandsnaam, worden momenteel niet ondersteund voor externe tabellen, behalve de functies die automatisch worden gemaakt voor elke tabel die in Apache Spark voor Azure Synapse Analytics is gemaakt.

Als uw opgeslagen gegevens niet zijn gepartitioneerd, kunt u overwegen deze te partitioneren. Op die manier kunt u deze functies gebruiken om query's te optimaliseren die op deze bestanden zijn gericht. Wanneer u een query uitvoert op gepartitioneerde Apache Spark voor Azure Synapse-tabellen uit een serverloze SQL-pool, is de query automatisch alleen gericht op de benodigde bestanden.

Gebruik de juiste sortering om predicaatpushdown te gebruiken voor tekenkolommen

Gegevens in een Parquet-bestand zijn ingedeeld in rijgroepen. Serverloze SQL-pool slaat rijgroepen over op basis van het opgegeven predicaat in het WHERE-component, waardoor IO wordt verminderd. Het resultaat zijn verbeterde queryprestaties.

Predicaatpushdown voor tekenkolommen in Parquet-bestanden wordt alleen ondersteund voor Latin1_General_100_BIN2_UTF8 sortering. U kunt de sortering voor een bepaalde kolom opgeven met behulp van een WITH-component. Als u deze sortering niet opgeeft met behulp van een WITH-component, wordt de databasesortering gebruikt.

Terugkerende query's optimaliseren

Hier volgen best practices voor het gebruik van CETAS in een serverloze SQL-pool.

CETAS gebruiken om queryprestaties en joins te verbeteren

CETAS is een van de belangrijkste functies die beschikbaar zijn in een serverloze SQL-pool. CETAS is een parallelle bewerking waarmee metagegevens van externe tabellen worden gemaakt en de SELECT-queryresultaten worden geëxporteerd naar een set bestanden in uw opslagaccount.

U kunt CETAS gebruiken om veelgebruikte onderdelen van query's, zoals gekoppelde referentietabellen, te materialiseren naar een nieuwe set bestanden. U kunt vervolgens aan deze ene externe tabel deelnemen in plaats van veelvoorkomende joins in meerdere query's te herhalen.

Terwijl CETAS Parquet-bestanden genereert, worden statistieken automatisch gemaakt wanneer de eerste query op deze externe tabel is gericht. Het resultaat is verbeterde prestaties voor volgende query's die zijn gericht op een tabel die is gegenereerd met CETAS.

Query's uitvoeren op Azure-gegevens

Met serverloze SQL-pools kunt u query's uitvoeren op gegevens in Azure Storage of Azure Cosmos DB met behulp van externe tabellen en de functie OPENROWSET. Zorg ervoor dat u over de juiste machtigingen beschikt voor uw opslag.

Query's uitvoeren op CSV-gegevens

Informatie over het opvragen van één CSV-bestand of -mappen en meerdere CSV-bestanden. U kunt ook query's uitvoeren op gepartitioneerde bestanden

Query's uitvoeren op Parquet-gegevens

Meer informatie over het uitvoeren van query's op Parquet-bestanden met geneste typen. U kunt ook query's uitvoeren op gepartitioneerde bestanden.

Query Delta Lake

Meer informatie over het uitvoeren van query's op Delta Lake-bestanden met geneste typen.

Query's uitvoeren op Azure Cosmos DB-gegevens

Meer informatie over het uitvoeren van query's op analytische opslag van Azure Cosmos DB. U kunt een onlinegenerator gebruiken om de WITH-component te genereren op basis van een Azure Cosmos DB-voorbeelddocument. U kunt weergaven maken boven op Azure Cosmos DB-containers.

Query's uitvoeren op JSON-gegevens

Informatie over het uitvoeren van query's op JSON-bestanden. U kunt ook query's uitvoeren op gepartitioneerde bestanden.

Weergaven, tabellen en andere databaseobjecten maken

Meer informatie over het maken en gebruiken van weergaven en externe tabellen of het instellen van beveiliging op rijniveau. Als u gepartitioneerde bestanden hebt, moet u gepartitioneerde weergaven gebruiken.

Gegevens kopiëren en transformeren (CETAS)

Meer informatie over het opslaan van queryresultaten in de opslag met behulp van de CETAS-opdracht.

Volgende stappen