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 star-schema ordent gegevens in feiten- en dimensietabellen. Sommige tabellen worden gebruikt voor integratie of fasering van gegevens voordat deze worden verplaatst naar een feiten- of dimensietabel. 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 de toegewezen SQL-pool worden geladen. Een detailhandel genereert bijvoorbeeld elke dag verkooptransacties en laadt de gegevens vervolgens in een feitentabel van een toegewezen SQL-pool 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.
Schema- en tabelnamen
Schema's zijn een goede manier om tabellen, die op een vergelijkbare manier worden gebruikt, samen te groeperen. Als u meerdere databases migreert van een on-premises oplossing naar een toegewezen SQL-pool, werkt het het beste om alle feiten-, dimensie- en integratietabellen te migreren naar één schema in een toegewezen SQL-pool.
U kunt bijvoorbeeld alle tabellen in de toegewezen SQL-pool WideWorldImportersDW opslaan 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 | Tabletype | Toegewezen SQL-pool |
|---|---|---|
| Plaats | Dimensie | wwi. DimCity |
| Volgorde | Fact | wwi. Feitenvolgorde |
Tabelpersistentie
Tabellen slaan gegevens permanent op in Azure Storage, tijdelijk in Azure Storage of in een gegevensarchief buiten de 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 normale 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. Bij gebruik met de INSTRUCTIE CREATE TABLE AS SELECT worden gegevens in een externe tabel geïmporteerd in een toegewezen SQL-pool.
Daarom zijn externe tabellen handig voor het laden van gegevens. Zie PolyBase gebruiken om gegevens te laden vanuit Azure Blob Storage voor een zelfstudie over het laden.
Gegevenstypen
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 hulp bij het gebruik van gegevenstypen.
Gedistribueerde tabellen
Een fundamentele functie van een toegewezen SQL-pool is de manier waarop deze tabellen in verschillende 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 met hash gedistribueerde tabel verdeelt 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. 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 moet worden gekozen voor het distribueren van de tabel.
| Tabelcategorie | Aanbevolen distributieoptie |
|---|---|
| Fact | Gebruik hash-distributie met geclusterde columnstore-index. De prestaties worden verbeterd wanneer twee hash-tabellen zijn gekoppeld aan 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. |
Notitie
Zie de Azure Synapse SQL Distribution Advisor voor aanbevelingen over de beste tabeldistributiestrategie die u kunt gebruiken op basis van uw workloads.
Tabelpartities
In een gepartitioneerde tabel worden 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 door over te schakelen tussen partities. Omdat de gegevens in de SQL-pool al zijn gedistribueerd, kunnen te veel partities de queryprestaties vertragen. Zie Richtlijnen voor partitionering voor meer informatie. Wanneer de partitie overschakelt naar tabelpartities die niet leeg zijn, kunt u overwegen de optie TRUNCATE_TARGET in uw ALTER TABLE-instructie te gebruiken als de bestaande gegevens moeten worden afgekapt. Met de onderstaande code worden de getransformeerde dagelijkse gegevens overgeschakeld naar de SalesFact die bestaande gegevens overschrijft.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
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 heap-tabel 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 de rijgroep 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. Statistieken worden automatisch gemaakt.
Het bijwerken van statistieken vindt niet automatisch plaats. Statistieken bijwerken nadat een aanzienlijk aantal rijen is toegevoegd of gewijzigd. Bijvoorbeeld statistieken bijwerken na het laden. Zie 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. UNIQUE-beperking wordt alleen ondersteund met NIET AFGEDWONGEN wordt gebruikt. Controleer tabelbeperkingen voor toegewezen SQL-pool.
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 te exporteren naar een externe locatie. De locatie is Azure Blob Storage of Azure Data Lake Store. |
Brongegevens uitlijnen met een toegewezen SQL-pool
Toegewezen SQL-pooltabellen worden gevuld door gegevens uit een andere gegevensbron te laden. Als u het laden 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 onderdeel 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 de 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 door andere databases worden aangeboden. In de volgende lijst ziet u enkele tabelfuncties die niet worden ondersteund in een toegewezen SQL-pool:
- Refererende sleutel, tabelbeperkingen controleren
- 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
Notitie
Voor nauwkeurige tellingen van query's in deze sectie moet u ervoor zorgen dat indexonderhoud regelmatig en na grote gegevenswijzigingen plaatsvindt.
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 beperkend zijn. 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
;
Overzicht van tabelruimte
Deze query retourneert de rijen en spaties per tabel. Hiermee kunt u zien welke tabellen uw grootste tabellen zijn en of ze round robin, gerepliceerd of hash-gedistribueerd zijn. Voor met hash gedistribueerde tabellen geeft de query de distributiekolom weer.
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 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 toegewezen SQL-pool hebt gemaakt, is de volgende stap het laden van gegevens in de tabel. Zie Gegevens laden in toegewezen SQL-pool voor een zelfstudie over het laden van gegevens en bekijk Strategieën voor het laden van gegevens voor toegewezen SQL-pools in Azure Synapse Analytics.