Tabellen ontwerpen met Synapse SQL in Azure Synapse Analytics

Artikel
06/01/2023

Dit document bevat belangrijke concepten voor het ontwerpen van tabellen met een toegewezen SQL-pool en een serverloze SQL-pool.

Serverloze SQL-pool is een queryservice voor 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 tabel bevat de onderwerpen die relevant zijn voor een toegewezen SQL-pool versus een serverloze SQL-pool:

Onderwerp	toegewezen SQL-pool	serverloze SQL-pool
Tabelcategorie bepalen	Ja	Nee
Schemanamen	Ja	Ja
Tabelnamen	Ja	Nee
Tabelpersistentie	Ja	Nee
Normale tabel	Ja	Nee
Tijdelijke tabel	Ja	Ja
Externe tabel	Ja	Ja
Gegevenstypen	Ja	Ja
Gedistribueerde tabellen	Ja	Nee
Met hash gedistribueerde tabellen	Ja	Nee
Gerepliceerde tabellen	Ja	Nee
Round robin-tabellen	Ja	Nee
Algemene distributiemethoden voor tabellen	Ja	Nee
Partities	Ja	Ja
Columnstore-indexen	Ja	Nee
statistieken	Ja	Ja
Primaire sleutel en unieke sleutel	Ja	Nee
Opdrachten voor het maken van tabellen	Ja	Nee
Brongegevens uitlijnen met het datawarehouse	Ja	Nee
Niet-ondersteunde tabelfuncties	Ja	Nee
Query's voor tabelgrootte	Ja	Nee

Tabelcategorie bepalen

Een stervormig schema organiseert gegevens in feiten- en dimensietabellen. Sommige tabellen worden gebruikt voor integratie of fasering van gegevens voordat ze naar een feiten- of dimensietabel gaan. Wanneer u een tabel ontwerpt, bepaalt u of de tabelgegevens deel uitmaken van een feiten-, dimensie- of integratietabel. Deze beslissing is van toepassing op de juiste tabelstructuur en -distributie.

Feitentabellen bevatten kwantitatieve gegevens die doorgaans worden gegenereerd in een transactioneel systeem en vervolgens in het datawarehouse worden geladen. Een detailhandel genereert bijvoorbeeld elke dag verkooptransacties en laadt de gegevens vervolgens in een datawarehouse-feitentabel voor analyse.
Dimensietabellen bevatten kenmerkgegevens die mogelijk worden gewijzigd, maar die meestal niet vaak worden gewijzigd. De naam en het adres van een klant worden bijvoorbeeld opgeslagen in een dimensietabel en alleen bijgewerkt wanneer het profiel van de klant wordt gewijzigd. Als u de grootte van een grote feitentabel wilt minimaliseren, hoeven de naam en het adres van de klant niet in elke rij van een feitentabel te staan. 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 in het voorbeelddatawarehouse WideWorldImportersDW opslaan binnen één schema met de naam wwi.

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

WideWorldImportersDW-tabel	Tabletype	toegewezen SQL-pool
Plaats	Dimensie	wwi. DimCity
Volgorde	Fact	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, ongeacht of een sessie is geopend. In het onderstaande voorbeeld wordt een gewone tabel met twee kolommen gemaakt.

CREATE TABLE MyTable (col1 int, col2 int );

Tijdelijke tabel

Een tijdelijke tabel bestaat alleen 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.

Serverloze SQL-pool ondersteunt tijdelijke tabellen. Het gebruik ervan is echter 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.

Importeer gegevens uit externe tabellen in toegewezen SQL-pools met behulp van de instructie CREATE TABLE AS SELECT . Zie PolyBase gebruiken om gegevens uit Azure Blob Storage te laden voor een zelfstudie over het laden.

Voor een serverloze SQL-pool kunt u CETAS gebruiken om het queryresultaat op te slaan in een externe tabel in Azure Storage.

Gegevenstypen

Een toegewezen SQL-pool ondersteunt de meest gebruikte gegevenstypen. Zie gegevenstypen in CREATE TABLE-verwijzing in de instructie CREATE TABLE voor een lijst met ondersteunde gegevenstypen. Zie Gegevenstypen 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 (standaard)
Hash
Gerepliceerd

Met hash gedistribueerde tabellen

Een met hash gedistribueerde tabel distribueert rijen op basis van de waarde in de distributiekolom. Een met hash gedistribueerde tabel is ontworpen om hoge prestaties te bereiken voor query's op grote tabellen. Er zijn verschillende factoren om rekening mee te 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.

Round robin-tabellen

Een round robin-tabel verdeelt tabelrijen gelijkmatig over alle distributies. De rijen worden willekeurig verdeeld. Het laden van gegevens in een round robin-tabel gaat snel. Query's kunnen echter meer gegevensverplaatsing vereisen dan de andere distributiemethoden.

Zie Ontwerprichtlijnen voor gedistribueerde tabellen voor meer informatie.

Algemene distributiemethoden voor tabellen

De tabelcategorie bepaalt vaak de optimale optie voor tabeldistributie.

Tabelcategorie	Aanbevolen distributieoptie
Fact	Gebruik hash-distributie met geclusterde columnstore-index. De prestaties verbeteren wanneer twee hash-tabellen worden samengevoegd in dezelfde distributiekolom.
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, gebruikt u INSERT... SELECTEER om de gegevens naar productietabellen te 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 prestaties van query's verbeteren door partities te verwijderen, waardoor een queryscan wordt beperkt tot gegevens binnen een partitie.

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

Tip

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

Met de onderstaande code worden de getransformeerde dagelijkse gegevens omgezet in 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/mappen (partities) beperken die door uw query worden gelezen. Partitioneren op pad wordt ondersteund met behulp van de functies filepath en fileinfo die worden beschreven in Query's uitvoeren 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

In een toegewezen SQL-pool wordt een tabel standaard opgeslagen 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 heaptabel kan met name handig zijn voor het laden van tijdelijke gegevens, zoals een faseringstabel, die wordt omgezet in een definitieve tabel.

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

statistieken

Het queryoptimalisatieprogramma 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 om statistieken te hebben over afzonderlijke kolommen, met name kolommen die worden gebruikt in query-joins. Synapse SQL biedt ondersteuning voor het automatisch maken van statistieken.

Statistische updates worden niet automatisch uitgevoerd. Statistieken bijwerken nadat een aanzienlijk aantal rijen is toegevoegd of gewijzigd. Bijvoorbeeld statistieken bijwerken na het laden. Aanvullende informatie vindt u in het artikel Met betrekking tot statistieken .

Primaire sleutel en unieke sleutel

Voor een toegewezen SQL-pool wordt PRIMAIRE SLEUTEL alleen ondersteund wanneer NIET-GECLUSTERD en NIET AFGEDWONGEN beide worden gebruikt. DE BEPERKING UNIEK wordt alleen ondersteund wanneer NIET AFGEDWONGEN wordt gebruikt. Zie het artikel beperkingen voor toegewezen SQL-pooltabellen 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	Description
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.
EXTERNE TABEL MAKEN ALS SELECTEREN	Hiermee maakt u een nieuwe externe tabel door de resultaten van een select-instructie te exporteren naar een externe locatie. De locatie is Azure Blob Storage of Azure Data Lake Storage.

Brongegevens uitlijnen met het datawarehouse

Toegewezen SQL-pooltabellen worden gevuld door gegevens uit een andere gegevensbron te laden. Voor een geslaagde belasting moeten het aantal en de gegevenstypen van de kolommen in de brongegevens overeenkomen met de tabeldefinitie in het datawarehouse.

Notitie

Het ophalen van de gegevens die moeten worden uitgelijnd, is mogelijk het moeilijkste onderdeel 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 de 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

Een toegewezen SQL-pool ondersteunt veel, maar niet alle, tabelfuncties die door andere databases worden aangeboden. In de volgende lijst ziet u enkele tabelfuncties die niet worden ondersteund in een toegewezen SQL-pool.

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

Query's voor tabelgrootte

In een toegewezen SQL-pool is het gebruik van DBCC-PDW_SHOWSPACEUSED een eenvoudige manier om ruimte en rijen te identificeren die door een tabel in elk van de 60 distributies worden gebruikt.

DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Houd er rekening mee dat het gebruik van DBCC-opdrachten behoorlijk beperkend kan zijn. Dynamische beheerweergaven (DMV's) geven meer details weer dan DBCC-opdrachten. Begin met het maken van de onderstaande 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 spaties per tabel. Met een overzicht van de tabelruimte kunt u zien welke tabellen de grootste tabellen zijn. U ziet ook of ze round robin, gerepliceerd of hash-gedistribueerd zijn. Voor 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 op 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 per 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
;

Overzicht 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
;

Volgende stappen

Nadat u de tabellen voor uw datawarehouse hebt gemaakt, is de volgende stap het laden van gegevens in de tabel. Zie Gegevens laden in een toegewezen SQL-pool voor een zelfstudie over het laden.