Tabellen ontwerpen met behulp van een toegewezen SQL-pool in Azure Synapse Analytics
Dit artikel bevat belangrijke inleidende concepten voor het ontwerpen van tabellen in een toegewezen SQL-pool.
Tabelcategorie bepalen
Een stervormig schema ordent gegevens in feiten- en dimensietabellen. Sommige tabellen worden gebruikt voor integratie- of faseringsgegevens voordat deze naar een feiten- of dimensietabel worden verplaatst. 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 de toegewezen SQL-pool worden geladen. Een retailbedrijf genereert bijvoorbeeld elke dag verkooptransacties en laadt de gegevens vervolgens in een toegewezen feitentabel van een SQL-pool 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.
Schema- en tabelnamen
Schema's zijn een goede manier om tabellen te groeperen, die op vergelijkbare wijze worden gebruikt. 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 schema in een toegewezen SQL-pool.
U kunt bijvoorbeeld alle tabellen opslaan in de toegewezen SQL-pool van WideWorldImportersDW in één schema met de naam wwi. Met de volgende code maakt u een door de gebruiker gedefinieerd schema met de naam wwi.
CREATE SCHEMA 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 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 toegewezen SQL-pool.
Normale tabel
In een normale tabel worden gegevens opgeslagen in Azure Storage als onderdeel van een toegewezen SQL-pool. De tabel en de gegevens blijven behouden, ongeacht of een sessie 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 en om de noodzaak van opschonen te verminderen.
Tijdelijke tabellen maken gebruik van lokale opslag om snelle prestaties te bieden. Zie Tijdelijke tabellen voor meer informatie.
Externe tabel
Een externe tabel verwijst naar gegevens in Azure Storage-blob of Azure Data Lake Store. Wanneer u deze gebruikt met de instructie CREATE TABLE AS SELECT, importeert u gegevens uit een externe tabel in een toegewezen SQL-pool.
Daarom zijn externe tabellen handig voor het laden van gegevens. Zie PolyBase gebruiken om gegevens uit Azure Blob Storage te laden voor een zelfstudie over laden.
Data types
Toegewezen SQL-pool ondersteunt de meest gebruikte gegevenstypen. Zie de gegevenstypen in create TABLE-verwijzing in de instructie CREATE TABLE voor een lijst met ondersteunde gegevenstypen. Zie Gegevenstypen voor hulp bij 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 en gerepliceerd.
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.
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 gaat snel. Houd er rekening mee dat query's meer gegevensverplaatsing kunnen vereisen dan de andere distributiemethoden.
Zie Ontwerprichtlijnen voor gedistribueerde tabellen voor meer informatie.
Algemene distributiemethoden voor tabellen
De tabelcategorie bepaalt vaak welke optie u wilt kiezen voor het distribueren van de tabel.
| 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, gebruikt u INSERT... SELECT om de gegevens naar productietabellen te verplaatsen. |
Notitie
Zie de Azure Synapse SQL Distribution Advisor voor aanbevelingen over de beste distributiestrategie voor tabellen die u kunt gebruiken op basis van uw workloads.
Tabelpartities
In een gepartitioneerde tabel worden 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 de SQL-pool al zijn gedistribueerd, kunnen te veel partities de queryprestaties vertragen. Zie De richtlijnen voor partitionering voor meer informatie. 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. De onderstaande code schakelt over in de getransformeerde dagelijkse gegevens naar de SalesFact die bestaande gegevens overschrijft.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
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 voor columnstore-indexen voor een lijst met columnstore-functies. Zie De kwaliteit van de rijgroep 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. Het maken van statistieken gebeurt automatisch.
Het bijwerken van statistieken gebeurt niet automatisch. Statistieken bijwerken nadat een aanzienlijk aantal rijen is toegevoegd of gewijzigd. Werk bijvoorbeeld statistieken bij na een belasting. Zie de richtlijnen voor statistieken voor meer informatie.
Primaire sleutel en unieke sleutel
PRIMAIRE SLEUTEL wordt alleen ondersteund wanneer NIET-GECLUSTERD en NIET AFGEDWONGEN beide worden gebruikt. DE BEPERKING UNIQUE wordt alleen ondersteund met NOT ENFORCED. Controleer de beperkingen van toegewezen SQL-pooltabellen.
Opdrachten voor het maken van tabellen
U kunt 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 Store. |
| 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 Store. |
Brongegevens uitlijnen met toegewezen SQL-pool
Toegewezen SQL-pooltabellen worden gevuld door gegevens uit een andere gegevensbron te laden. Als u een geslaagde belasting wilt uitvoeren, moeten het aantal en de gegevenstypen van de kolommen in de brongegevens overeenkomen met de tabeldefinitie in de toegewezen SQL-pool. 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, laadt u de gegevens in de toegewezen SQL-pool en slaat u deze op in een integratietabel. Zodra gegevens zich in de integratietabel bevinden, kunt u de kracht van een toegewezen SQL-pool gebruiken om transformatiebewerkingen uit te voeren. 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
Notitie
Voor nauwkeurige tellingen van query's in deze sectie moet u ervoor zorgen dat indexonderhoud regelmatig plaatsvindt en nadat grote gegevens zijn gewijzigd.
Een eenvoudige manier om ruimte en rijen te identificeren die door een tabel in elk van de 60 distributies worden gebruikt, is het gebruik van DBCC-PDW_SHOWSPACEUSED.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
Het gebruik van DBCC-opdrachten kan echter behoorlijk beperken. Dynamische beheerweergaven (DMV's) geven meer details weer dan DBCC-opdrachten. Begin met het maken van deze 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]
AND i.[index_id] = nps.[index_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. Hiermee kunt u zien welke tabellen uw grootste tabellen zijn en 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 de tabellen voor uw toegewezen SQL-pool hebt gemaakt, is de volgende stap het laden van gegevens in de tabel. Zie Voor een zelfstudie over het laden van gegevens naar een toegewezen SQL-pool en bekijkt u strategieën voor het laden van gegevens voor een toegewezen SQL-pool in Azure Synapse Analytics.