Tabellen ontwerpen met synapse SQL-pool

In dit artikel worden de belangrijkste concepten voor het ontwerpen van tabellen met toegewezen SQL-pool en serverloze SQL-pool in Azure Synapse Analytics uitgelegd.

• Serverloze SQL-pool is een queryservice die werkt via de gegevens in uw data lake. Er is geen lokale opslag voor gegevensopname.
• Toegewezen SQL-pool vertegenwoordigt een verzameling analytische resources die worden ingericht bij het gebruik van Synapse SQL. De grootte van een toegewezen SQL-pool wordt bepaald door DWU’s (Data Warehousing Unit).

De volgende onderwerpen zijn relevant voor een toegewezen SQL-pool versus een serverloze SQL-pool:

Onderwerp	Toegewezen SQL-pool	Serverloze SQL-pool
Tabelcategorie	Ja	Nr.
Schemanamen	Ja	Ja
Tabelnamen	Ja	Nr.
Tabelpersistentie	Ja	Nr.
Normale tabel	Ja	Nr.
Tijdelijke tabel	Ja	Ja
Externe tabel	Ja	Ja
Data types	Ja	Ja
Gedistribueerde tabellen	Ja	Nr.
Round robin-tabellen	Ja	Nr.
Met hash gedistribueerde tabellen	Ja	Nr.
Gerepliceerde tabellen	Ja	Nr.
Algemene distributiemethoden voor tabellen	Ja	Nr.
Partities	Ja	Ja
Columnstore-indexen	Ja	Nr.
statistieken	Ja	Ja
Primaire sleutel en unieke sleutel	Ja	Nr.
Opdrachten voor het maken van tabellen	Ja	Nr.
Brongegevens uitlijnen op het datawarehouse	Ja	Nr.
Niet-ondersteunde tabelfuncties	Ja	Nr.
Query's voor tabelgrootte	Ja	Nr.

Tabelcategorie

Een stervormig schema ordent gegevens in feiten- en dimensietabellen. Sommige tabellen worden gebruikt voor integratie- of faseringsgegevens voordat u naar een feiten- of dimensietabel gaat. Wanneer u een tabel ontwerpt, bepaalt u of de tabelgegevens deel uitmaken van een feiten-, dimensie- of integratietabel. Deze beslissing informeert de juiste tabelstructuur en -verdeling.

• Feitentabellen bevatten kwantitatieve gegevens die vaak worden gegenereerd in een transactioneel systeem en vervolgens in het datawarehouse worden geladen. Een retailbedrijf genereert bijvoorbeeld elke dag verkooptransacties en laadt de gegevens vervolgens in een feitentabel van een datawarehouse voor analyse.

• Dimensietabellen bevatten kenmerkgegevens die mogelijk worden gewijzigd, maar meestal onregelmatig worden gewijzigd. De naam en het adres van een klant worden bijvoorbeeld opgeslagen in een dimensietabel en worden alleen bijgewerkt wanneer het profiel van de klant wordt gewijzigd. Als u de grootte van een grote feitentabel wilt minimaliseren, hoeft de naam en het adres van de klant zich niet in elke rij van een feitentabel te bevinden. In plaats daarvan kunnen de feitentabel en de dimensietabel een klant-id delen. Een query kan de twee tabellen samenvoegen om het profiel en de transacties van een klant te koppelen.

• Integratietabellen bieden een plaats voor het integreren of faseren van gegevens. U kunt een integratietabel maken als een gewone tabel, een externe tabel of een tijdelijke tabel. U kunt bijvoorbeeld gegevens laden in een faseringstabel, transformaties uitvoeren op de gegevens in fasering en de gegevens vervolgens invoegen in een productietabel.

Schemanamen

Schema's zijn een goede manier om objecten te groeperen die op een vergelijkbare manier worden gebruikt. Met de volgende code maakt u een door de gebruiker gedefinieerd schema met de naam wwi.

CREATE SCHEMA wwi;

Tabelnamen

Als u meerdere databases migreert van een on-premises oplossing naar een toegewezen SQL-pool, kunt u het beste alle feiten-, dimensie- en integratietabellen migreren naar één SQL-poolschema. U kunt bijvoorbeeld alle tabellen opslaan in het WideWorldImportersDW-voorbeelddatawarehouse in één schema met de naam wwi.

Als u de organisatie van de tabellen in een toegewezen SQL-pool wilt weergeven, kunt u de dimtabelnamen gebruiken facten int als voorvoegsels gebruiken. In de volgende tabel ziet u enkele schema- en tabelnamen voor WideWorldImportersDW.

WideWorldImportersDW-tabel	Tabeltype	Toegewezen SQL-pool
City	Dimensie	wwi. DimCity
Order	Feit	wwi. FactOrder

Tabelpersistentie

Tabellen slaan gegevens permanent op in Azure Storage, tijdelijk in Azure Storage of in een gegevensarchief buiten het datawarehouse.

Normale tabel

In een normale tabel worden gegevens opgeslagen in Azure Storage als onderdeel van het datawarehouse. De tabel en de gegevens blijven behouden of een sessie al dan niet is geopend. In het volgende voorbeeld wordt een gewone tabel met twee kolommen gemaakt.

CREATE TABLE MyTable (col1 int, col2 int );  

Tijdelijke tabel

Er bestaat alleen een tijdelijke tabel voor de duur van de sessie. U kunt een tijdelijke tabel gebruiken om te voorkomen dat andere gebruikers tijdelijke resultaten zien. Het gebruik van tijdelijke tabellen vermindert ook de noodzaak van opschonen. Tijdelijke tabellen maken gebruik van lokale opslag en kunnen in toegewezen SQL-pools snellere prestaties bieden.

Een serverloze SQL-pool ondersteunt tijdelijke tabellen, maar het gebruik ervan is beperkt omdat u een tijdelijke tabel kunt selecteren, maar deze niet kunt koppelen aan bestanden in de opslag.

Zie Tijdelijke tabellen voor meer informatie.

Externe tabel

Externe tabellen verwijzen naar gegevens in Azure Storage-blob of Azure Data Lake Storage.

U kunt gegevens uit externe tabellen importeren in toegewezen SQL-pools met behulp van de instructie CREATE TABLE AS SELECT (CTAS). Zie De gegevensset Taxicab van New York laden voor een zelfstudie over laden.

Voor een serverloze SQL-pool kunt u CREATE EXTERNAL TABLE AS SELECT (CETAS) gebruiken om het queryresultaat op te slaan in een externe tabel in Azure Storage.

Data types

Toegewezen SQL-pool ondersteunt de meest gebruikte gegevenstypen. Zie het gegevenstype in de verwijzing CREATE TABLE voor een lijst met ondersteunde gegevenstypen. Zie Tabelgegevenstypen in Synapse SQL voor meer informatie over het gebruik van gegevenstypen.

Gedistribueerde tabellen

Een fundamentele functie van een toegewezen SQL-pool is de manier waarop deze tabellen in distributies kan opslaan en gebruiken. Toegewezen SQL-pool ondersteunt drie methoden voor het distribueren van gegevens:

• Round robin-tabellen (standaard)
• Met hash gedistribueerde tabellen
• Gerepliceerde tabellen

Round robin-tabellen

Een round robin-tabel distribueert tabelrijen gelijkmatig over alle distributies. De rijen worden willekeurig gedistribueerd. Het laden van gegevens in een round robin-tabel is snel, maar query's kunnen meer gegevensverplaatsing vereisen dan de andere distributiemethoden.

Zie Ontwerprichtlijnen voor gedistribueerde tabellen voor meer informatie.

Met hash gedistribueerde tabellen

Een gedistribueerde hashtabel distribueert rijen op basis van de waarde in de distributiekolom. Een gedistribueerde hashtabel is ontworpen voor hoge prestaties voor query's in grote tabellen. Er zijn verschillende factoren waarmee u rekening moet houden bij het kiezen van een distributiekolom.

Zie Ontwerprichtlijnen voor gedistribueerde tabellen voor meer informatie.

Gerepliceerde tabellen

Een gerepliceerde tabel heeft een volledige kopie van de tabel die beschikbaar is op elk rekenknooppunt. Query's worden snel uitgevoerd op gerepliceerde tabellen omdat joins in gerepliceerde tabellen geen gegevensverplaatsing vereisen. Replicatie vereist echter extra opslag en is niet praktisch voor grote tabellen.

Zie Ontwerprichtlijnen voor gerepliceerde tabellen voor meer informatie.

Algemene distributiemethoden voor tabellen

De tabelcategorie bepaalt vaak de optimale optie voor tabeldistributie.

Tabelcategorie	Aanbevolen distributieoptie
Feit	Gebruik hash-distributie met geclusterde columnstore-index. De prestaties worden verbeterd wanneer twee hashtabellen aan dezelfde distributiekolom zijn gekoppeld.
Dimensie	Gebruik gerepliceerd voor kleinere tabellen. Als tabellen te groot zijn om op elk rekenknooppunt op te slaan, gebruikt u hash-gedistribueerd.
Staging	Gebruik round robin voor de faseringstabel. De belasting met CTAS is snel. Zodra de gegevens zich in de faseringstabel bevinden, kunt INSERT...SELECT u de gegevens naar productietabellen verplaatsen.

Partities

In toegewezen SQL-pools worden in een gepartitioneerde tabel bewerkingen op de tabelrijen opgeslagen en uitgevoerd op basis van gegevensbereiken. Een tabel kan bijvoorbeeld worden gepartitioneerd op dag, maand of jaar. U kunt de queryprestaties verbeteren door partities te verwijderen, waardoor een queryscan wordt beperkt tot gegevens binnen een partitie.

U kunt de gegevens ook onderhouden via het schakelen tussen partities. Omdat de gegevens in een toegewezen SQL-pool al zijn gedistribueerd, kunnen te veel partities de queryprestaties vertragen. Zie De richtlijnen voor partitionering voor meer informatie.

Tip

Wanneer de partitie overschakelt naar tabelpartities die niet leeg zijn, kunt u overwegen de optie in de TRUNCATE_TARGETinstructie ALTER TABLE te gebruiken als de bestaande gegevens moeten worden afgekapt.

Met de volgende code worden de getransformeerde dagelijkse gegevens overgeschakeld naar een SalesFact-partitie en worden alle bestaande gegevens overschreven.

ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);  

In een serverloze SQL-pool kunt u de bestanden of mappen (partities) beperken die door uw query worden gelezen. Partitioneren op pad wordt ondersteund met behulp van de filepath functies die fileinfo worden beschreven in Het uitvoeren van query's op opslagbestanden. In het volgende voorbeeld wordt een map met gegevens voor het jaar 2017 gelezen:

SELECT
    nyc.filepath(1) AS [year],
    payment_type,
    SUM(fare_amount) AS fare_total
FROM  
    OPENROWSET(
        BULK 'https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS nyc
WHERE
    nyc.filepath(1) = 2017
GROUP BY
    nyc.filepath(1),
    payment_type
ORDER BY
    nyc.filepath(1),
    payment_type

Columnstore-indexen

Standaard slaat toegewezen SQL-pool een tabel op als een geclusterde columnstore-index. Deze vorm van gegevensopslag zorgt voor hoge gegevenscompressie en queryprestaties voor grote tabellen. De geclusterde columnstore-index is meestal de beste keuze, maar in sommige gevallen is een geclusterde index of een heap de juiste opslagstructuur.

Tip

Een heap-tabel kan met name handig zijn voor het laden van tijdelijke gegevens, zoals een faseringstabel, die wordt omgezet in een uiteindelijke tabel.

Zie Wat is er nieuw in columnstore-indexen voor een lijst met columnstore-functies. Zie Rijgroepkwaliteit maximaliseren voor columnstore-indexen om de prestaties van columnstore-indexen te verbeteren.

statistieken

De queryoptimalisatie maakt gebruik van statistieken op kolomniveau wanneer het plan voor het uitvoeren van een query wordt gemaakt. Om de queryprestaties te verbeteren, is het belangrijk dat u statistieken hebt over afzonderlijke kolommen, met name kolommen die worden gebruikt in querydeelnames. Synapse SQL biedt ondersteuning voor het automatisch maken van statistieken.

Statistische updates worden niet automatisch uitgevoerd. U kunt statistieken bijwerken nadat een aanzienlijk aantal rijen is toegevoegd of gewijzigd. Werk bijvoorbeeld statistieken bij na een belasting. Zie Statistieken in Synapse SQL voor meer informatie.

Primaire sleutel en unieke sleutel

Voor een toegewezen SQL-pool PRIMARY KEY wordt alleen ondersteund wanneer NONCLUSTERED en NOT ENFORCED beide worden gebruikt. UNIQUE beperking wordt alleen ondersteund wanneer NOT ENFORCED deze wordt gebruikt. Zie de primaire sleutel, refererende sleutel en unieke sleutel met behulp van een toegewezen SQL-pool voor meer informatie.

Opdrachten voor het maken van tabellen

Voor een toegewezen SQL-pool kunt u een tabel maken als een nieuwe lege tabel. U kunt ook een tabel maken en vullen met de resultaten van een select-instructie. Hier volgen de T-SQL-opdrachten voor het maken van een tabel.

T-SQL-instructie	Beschrijving
CREATE TABLE	Hiermee maakt u een lege tabel door alle tabelkolommen en -opties te definiëren.
CREATE EXTERNAL TABLE	Hiermee maakt u een externe tabel. De definitie van de tabel wordt opgeslagen in een toegewezen SQL-pool. De tabelgegevens worden opgeslagen in Azure Blob Storage of Azure Data Lake Storage.
CREATE TABLE AS SELECT	Hiermee wordt een nieuwe tabel gevuld met de resultaten van een select-instructie. De tabelkolommen en gegevenstypen zijn gebaseerd op de resultaten van de select-instructie. Als u gegevens wilt importeren, kunt u deze instructie selecteren uit een externe tabel.
CREATE EXTERNAL TABLE AS SELECT	Hiermee maakt u een nieuwe externe tabel door de resultaten van een select-instructie naar een externe locatie te exporteren. De locatie is Azure Blob Storage of Azure Data Lake Storage.

Brongegevens uitlijnen op het datawarehouse

Toegewezen SQL-pooltabellen worden gevuld door gegevens uit een andere gegevensbron te laden. Als u een geslaagde belasting wilt bereiken, moeten het aantal en de gegevenstypen van de kolommen in de brongegevens worden afgestemd op de tabeldefinitie in het datawarehouse.

Notitie

Het ophalen van de gegevens die moeten worden uitgelijnd, is mogelijk het moeilijkste deel van het ontwerpen van uw tabellen.

Als gegevens afkomstig zijn uit meerdere gegevensarchieven, kunt u de gegevens overzetten naar het datawarehouse en opslaan in een integratietabel. Zodra gegevens zich in de integratietabel bevinden, kunt u de kracht van een toegewezen SQL-pool gebruiken om transformatiebewerkingen te implementeren. Zodra de gegevens zijn voorbereid, kunt u deze invoegen in productietabellen.

Niet-ondersteunde tabelfuncties

Toegewezen SQL-pool ondersteunt veel, maar niet alle tabelfuncties die worden aangeboden door andere databases. De volgende lijst bevat enkele tabelfuncties die niet worden ondersteund in een toegewezen SQL-pool.

• Refererende sleutel, tabelbeperkingen controleren
• Berekende kolommen
• Geïndexeerde weergaven
• Volgorde
• Sparse-kolommen
• Surrogaatsleutels, implementeren met identiteit
• Synoniemen
• Triggers
• Unieke indexen
• Door de gebruiker gedefinieerde typen

Query's voor tabelgrootte

In een toegewezen SQL-pool kunt u eenvoudig ruimte en rijen identificeren die door een tabel in elk van de 60 distributies worden gebruikt, door DBCC-PDW_SHOWSPACEUSED te gebruiken.

DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Houd er rekening mee dat het gebruik van DBCC-opdrachten behoorlijk beperkt kan zijn. Dynamische beheerweergaven (DMV's) geven meer details weer dan DBCC-opdrachten. Begin met het maken van de volgende weergave.

CREATE VIEW dbo.vTableSizes
AS
WITH base
AS
(
SELECT
 GETDATE()                                                             AS  [execution_time]
, DB_NAME()                                                            AS  [database_name]
, s.name                                                               AS  [schema_name]
, t.name                                                               AS  [table_name]
, QUOTENAME(s.name)+'.'+QUOTENAME(t.name)                              AS  [two_part_name]
, nt.[name]                                                            AS  [node_table_name]
, ROW_NUMBER() OVER(PARTITION BY nt.[name] ORDER BY (SELECT NULL))     AS  [node_table_name_seq]
, tp.[distribution_policy_desc]                                        AS  [distribution_policy_name]
, c.[name]                                                             AS  [distribution_column]
, nt.[distribution_id]                                                 AS  [distribution_id]
, i.[type]                                                             AS  [index_type]
, i.[type_desc]                                                        AS  [index_type_desc]
, nt.[pdw_node_id]                                                     AS  [pdw_node_id]
, pn.[type]                                                            AS  [pdw_node_type]
, pn.[name]                                                            AS  [pdw_node_name]
, di.name                                                              AS  [dist_name]
, di.position                                                          AS  [dist_position]
, nps.[partition_number]                                               AS  [partition_nmbr]
, nps.[reserved_page_count]                                            AS  [reserved_space_page_count]
, nps.[reserved_page_count] - nps.[used_page_count]                    AS  [unused_space_page_count]
, nps.[in_row_data_page_count]
    + nps.[row_overflow_used_page_count]
    + nps.[lob_used_page_count]                                        AS  [data_space_page_count]
, nps.[reserved_page_count]
 - (nps.[reserved_page_count] - nps.[used_page_count])
 - ([in_row_data_page_count]
         + [row_overflow_used_page_count]+[lob_used_page_count])       AS  [index_space_page_count]
, nps.[row_count]                                                      AS  [row_count]
from
    sys.schemas s
INNER JOIN sys.tables t
    ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.indexes i
    ON  t.[object_id] = i.[object_id]
    AND i.[index_id] <= 1
INNER JOIN sys.pdw_table_distribution_properties tp
    ON t.[object_id] = tp.[object_id]
INNER JOIN sys.pdw_table_mappings tm
    ON t.[object_id] = tm.[object_id]
INNER JOIN sys.pdw_nodes_tables nt
    ON tm.[physical_name] = nt.[name]
INNER JOIN sys.dm_pdw_nodes pn
    ON  nt.[pdw_node_id] = pn.[pdw_node_id]
INNER JOIN sys.pdw_distributions di
    ON  nt.[distribution_id] = di.[distribution_id]
INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps
    ON nt.[object_id] = nps.[object_id]
    AND nt.[pdw_node_id] = nps.[pdw_node_id]
    AND nt.[distribution_id] = nps.[distribution_id]
LEFT OUTER JOIN (select * from sys.pdw_column_distribution_properties where distribution_ordinal = 1) cdp
    ON t.[object_id] = cdp.[object_id]
LEFT OUTER JOIN sys.columns c
    ON cdp.[object_id] = c.[object_id]
    AND cdp.[column_id] = c.[column_id]
WHERE pn.[type] = 'COMPUTE'
)
, size
AS
(
SELECT
   [execution_time]
,  [database_name]
,  [schema_name]
,  [table_name]
,  [two_part_name]
,  [node_table_name]
,  [node_table_name_seq]
,  [distribution_policy_name]
,  [distribution_column]
,  [distribution_id]
,  [index_type]
,  [index_type_desc]
,  [pdw_node_id]
,  [pdw_node_type]
,  [pdw_node_name]
,  [dist_name]
,  [dist_position]
,  [partition_nmbr]
,  [reserved_space_page_count]
,  [unused_space_page_count]
,  [data_space_page_count]
,  [index_space_page_count]
,  [row_count]
,  ([reserved_space_page_count] * 8.0)                                 AS [reserved_space_KB]
,  ([reserved_space_page_count] * 8.0)/1000                            AS [reserved_space_MB]
,  ([reserved_space_page_count] * 8.0)/1000000                         AS [reserved_space_GB]
,  ([reserved_space_page_count] * 8.0)/1000000000                      AS [reserved_space_TB]
,  ([unused_space_page_count]   * 8.0)                                 AS [unused_space_KB]
,  ([unused_space_page_count]   * 8.0)/1000                            AS [unused_space_MB]
,  ([unused_space_page_count]   * 8.0)/1000000                         AS [unused_space_GB]
,  ([unused_space_page_count]   * 8.0)/1000000000                      AS [unused_space_TB]
,  ([data_space_page_count]     * 8.0)                                 AS [data_space_KB]
,  ([data_space_page_count]     * 8.0)/1000                            AS [data_space_MB]
,  ([data_space_page_count]     * 8.0)/1000000                         AS [data_space_GB]
,  ([data_space_page_count]     * 8.0)/1000000000                      AS [data_space_TB]
,  ([index_space_page_count]  * 8.0)                                   AS [index_space_KB]
,  ([index_space_page_count]  * 8.0)/1000                              AS [index_space_MB]
,  ([index_space_page_count]  * 8.0)/1000000                           AS [index_space_GB]
,  ([index_space_page_count]  * 8.0)/1000000000                        AS [index_space_TB]
FROM base
)
SELECT *
FROM size
;

Samenvatting van tabelruimte

Deze query retourneert de rijen en ruimte per tabel. Met een overzicht van de tabelruimte kunt u zien welke tabellen de grootste tabellen zijn. U kunt ook zien of ze round robin, gerepliceerd of hash-gedistribueerd zijn. Voor door hash gedistribueerde tabellen toont de query de distributiekolom.

SELECT
     database_name
,    schema_name
,    table_name
,    distribution_policy_name
,      distribution_column
,    index_type_desc
,    COUNT(distinct partition_nmbr) as nbr_partitions
,    SUM(row_count)                 as table_row_count
,    SUM(reserved_space_GB)         as table_reserved_space_GB
,    SUM(data_space_GB)             as table_data_space_GB
,    SUM(index_space_GB)            as table_index_space_GB
,    SUM(unused_space_GB)           as table_unused_space_GB
FROM
    dbo.vTableSizes
GROUP BY
     database_name
,    schema_name
,    table_name
,    distribution_policy_name
,      distribution_column
,    index_type_desc
ORDER BY
    table_reserved_space_GB desc
;

Tabelruimte per distributietype

SELECT
     distribution_policy_name
,    SUM(row_count)                as table_type_row_count
,    SUM(reserved_space_GB)        as table_type_reserved_space_GB
,    SUM(data_space_GB)            as table_type_data_space_GB
,    SUM(index_space_GB)           as table_type_index_space_GB
,    SUM(unused_space_GB)          as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY distribution_policy_name
;

Tabelruimte op indextype

SELECT
     index_type_desc
,    SUM(row_count)                as table_type_row_count
,    SUM(reserved_space_GB)        as table_type_reserved_space_GB
,    SUM(data_space_GB)            as table_type_data_space_GB
,    SUM(index_space_GB)           as table_type_index_space_GB
,    SUM(unused_space_GB)          as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY index_type_desc
;

Samenvatting van distributieruimte

SELECT
    distribution_id
,    SUM(row_count)                as total_node_distribution_row_count
,    SUM(reserved_space_MB)        as total_node_distribution_reserved_space_MB
,    SUM(data_space_MB)            as total_node_distribution_data_space_MB
,    SUM(index_space_MB)           as total_node_distribution_index_space_MB
,    SUM(unused_space_MB)          as total_node_distribution_unused_space_MB
FROM dbo.vTableSizes
GROUP BY     distribution_id
ORDER BY    distribution_id
;

Gerelateerde inhoud

Nadat u een tabel voor uw datawarehouse hebt gemaakt, is de volgende stap het laden van gegevens in de tabel.

• Zelfstudie: De gegevens laden in SQL-pool