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 dim
tabelnamen gebruiken fact
en 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_TARGET
instructie 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.