Utforma tabeller med Synapse SQL i Azure Synapse Analytics

Det här dokumentet innehåller viktiga begrepp för att utforma tabeller med dedikerad SQL-pool och serverlös SQL-pool.

Serverlös SQL-pool är en frågetjänst för data i din datasjö. Den har inte lokal lagring för datainmatning. Dedikerad SQL-pool representerar en samling analysresurser som etableras när du använder Synapse SQL. Storleken på en dedikerad SQL-pool bestäms av DWU (Data Warehousing Units).

I följande tabell visas de ämnen som är relevanta för dedikerad SQL-pool jämfört med serverlös SQL-pool:

Avsnitt	dedikerad SQL-pool	serverlös SQL-pool
Fastställa tabellkategori	Ja	Inga
Schemanamn	Ja	Ja
Tabellnamn	Ja	Inga
Tabellpersistens	Ja	Inga
Vanlig tabell	Ja	Inga
Tillfällig tabell	Ja	Ja
Extern tabell	Ja	Ja
Datatyper	Ja	Ja
Distribuerade tabeller	Ja	Inga
Hash-distribuerade tabeller	Ja	Inga
Replikerade tabeller	Ja	Inga
Resursallokeringstabeller	Ja	Inga
Vanliga distributionsmetoder för tabeller	Ja	Inga
Partitioner	Ja	Ja
Kolumnlagringsindex	Ja	Inga
Statistik	Ja	Ja
Primärnyckel och unik nyckel	Ja	Inga
Kommandon för att skapa tabeller	Ja	Inga
Justera källdata med informationslagret	Ja	Inga
Tabellfunktioner som inte stöds	Ja	Inga
Frågor om tabellstorlek	Ja	Inga

Fastställa tabellkategori

Ett star-schema organiserar data i fakta- och dimensionstabeller. Vissa tabeller används för integrering eller mellanlagring av data innan de flyttas till en fakta- eller dimensionstabell. När du utformar en tabell ska du bestämma om tabelldata ska tillhöra en fakta-, dimensions- eller integrationstabell. Detta beslut informerar lämplig tabellstruktur och fördelning.

• Faktatabeller innehåller kvantitativa data som vanligtvis genereras i ett transaktionssystem och sedan läses in i informationslagret. Ett detaljhandelsföretag genererar till exempel försäljningstransaktioner varje dag och läser sedan in data i en faktatabell för informationslager för analys.

• Dimensionstabeller innehåller attributdata som kan ändras men vanligtvis ändras sällan. Till exempel lagras en kunds namn och adress i en dimensionstabell och uppdateras bara när kundens profil ändras. För att minimera storleken på en stor faktatabell behöver kundens namn och adress inte finnas i varje rad i en faktatabell. Faktatabellen och dimensionstabellen kan i stället dela ett kund-ID. En fråga kan koppla de två tabellerna för att associera en kunds profil och transaktioner.

• Integreringstabeller är en plats för integrering eller mellanlagring av data. Du kan skapa en integrationstabell som en vanlig tabell, en extern tabell eller en tillfällig tabell. Du kan till exempel läsa in data till en mellanlagringstabell, utföra transformeringar på data i mellanlagringen och sedan infoga data i en produktionstabell.

Schemanamn

Scheman är ett bra sätt att gruppera objekt som används på liknande sätt. Följande kod skapar ett användardefinierat schema med namnet wwi.

CREATE SCHEMA wwi;

Tabellnamn

Om du migrerar flera databaser från en lokal lösning till en dedikerad SQL-pool är bästa praxis att migrera alla fakta-, dimensions- och integrationstabeller till ett SQL-poolschema. Du kan till exempel lagra alla tabeller i exempelinformationslagret WideWorldImportersDW i ett schema med namnet wwi.

Om du vill visa organisationen av tabellerna i en dedikerad SQL-pool kan du använda fakta, dim och int som prefix för tabellnamnen. Tabellen nedan visar några av schema- och tabellnamnen för WideWorldImportersDW.

WideWorldImportersDW-tabell	Tabelltyp	dedikerad SQL-pool
City	Dimension	Wwi. DimCity
Beställning	Fakta	Wwi. FactOrder

Tabellpersistens

Tabeller lagrar data antingen permanent i Azure Storage, tillfälligt i Azure Storage eller i ett datalager utanför informationslagret.

Vanlig tabell

En vanlig tabell lagrar data i Azure Storage som en del av informationslagret. Tabellen och data bevaras oavsett om en session är öppen eller inte. I exemplet nedan skapas en vanlig tabell med två kolumner.

CREATE TABLE MyTable (col1 int, col2 int );  

Tillfällig tabell

Det finns bara en tillfällig tabell under hela sessionen. Du kan använda en tillfällig tabell för att förhindra att andra användare ser tillfälliga resultat. Att använda temporära tabeller minskar också behovet av rensning. Temporära tabeller använder lokal lagring och kan i dedikerade SQL-pooler erbjuda snabbare prestanda.

Serverlös SQL-pool stöder temporära tabeller. Men användningen är begränsad eftersom du kan välja från en tillfällig tabell men inte koppla den till filer i lagringen.

Mer information finns i Temporära tabeller.

Extern tabell

Externa tabeller pekar på data som finns i Azure Storage-bloben eller Azure Data Lake Storage.

Importera data från externa tabeller till dedikerade SQL-pooler med instruktionen CREATE TABLE AS SELECT . En självstudiekurs om inläsning finns i Använda PolyBase för att läsa in data från Azure Blob Storage.

För serverlös SQL-pool kan du använda CETAS för att spara frågeresultatet i en extern tabell i Azure Storage.

Datatyper

Dedikerad SQL-pool stöder de vanligaste datatyperna. En lista över de datatyper som stöds finns i datatyper i CREATE TABLE-referensen i CREATE TABLE-instruktionen. Mer information om hur du använder datatyper finns i Datatyper.

Distribuerade tabeller

En grundläggande funktion i en dedikerad SQL-pool är hur den kan lagra och arbeta med tabeller mellan distributioner. Dedikerad SQL-pool har stöd för tre metoder för att distribuera data:

• Resursallokering (standard)
• Hash
• Replikerad

Hash-distribuerade tabeller

En hash-distribuerad tabell distribuerar rader baserat på värdet i distributionskolumnen. En hash-distribuerad tabell är utformad för att uppnå höga prestanda för frågor på stora tabeller. Det finns flera faktorer att tänka på när du väljer en distributionskolumn.

Mer information finns i Designvägledning för distribuerade tabeller.

Replikerade tabeller

En replikerad tabell har en fullständig kopia av tabellen som är tillgänglig på varje beräkningsnod. Frågor körs snabbt på replikerade tabeller eftersom kopplingar i replikerade tabeller inte kräver dataflytt. Replikering kräver dock extra lagringsutrymme och är inte praktiskt för stora tabeller.

Mer information finns i Designvägledning för replikerade tabeller.

Resursallokeringstabeller

En resursallokeringstabell distribuerar tabellrader jämnt över alla distributioner. Raderna distribueras slumpmässigt. Det går snabbt att läsa in data i en resursallokeringstabell. Men frågor kan kräva mer dataflytt än de andra distributionsmetoderna.

Mer information finns i Designvägledning för distribuerade tabeller.

Vanliga distributionsmetoder för tabeller

Tabellkategorin avgör ofta det optimala alternativet för tabelldistribution.

Tabellkategori	Rekommenderat distributionsalternativ
Fakta	Använd hash-distribution med grupperat kolumnlagringsindex. Prestanda förbättras när två hash-tabeller är kopplade till samma distributionskolumn.
Dimension	Använd replikerad för mindre tabeller. Om tabellerna är för stora för att lagras på varje beräkningsnod använder du hash-distribuerad.
Mellanlagring	Använd resursallokering för mellanlagringstabellen. Belastningen med CTAS går snabbt. När data finns i mellanlagringstabellen använder du INSERT... VÄLJ för att flytta data till produktionstabeller.

Partitioner

I dedikerade SQL-pooler lagrar och kör en partitionerad tabell åtgärder på tabellraderna enligt dataintervall. En tabell kan till exempel partitioneras efter dag, månad eller år. Du kan förbättra frågeprestandan genom partitionseliminering, vilket begränsar en frågegenomsökning till data i en partition.

Du kan också underhålla data via partitionsväxling. Eftersom data i en dedikerad SQL-pool redan är distribuerade kan för många partitioner göra frågeprestanda långsammare. Mer information finns i Partitioneringsvägledning.

Tips

När partitionen växlar till tabellpartitioner som inte är tomma bör du överväga att använda alternativet TRUNCATE_TARGET i ALTER TABLE-instruktionen om befintliga data ska trunkeras.

Koden nedan växlar omvandlade dagliga data till en SalesFact-partition och skriver över befintliga data.

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

I en serverlös SQL-pool kan du begränsa vilka filer/mappar (partitioner) som ska läsas av din fråga. Partitionering efter sökväg stöds med hjälp av funktionerna filepath och fileinfo som beskrivs i Köra frågor mot lagringsfiler. I följande exempel läss en mapp med data för år 2017:

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-index

Som standard lagrar en dedikerad SQL-pool en tabell som ett grupperat kolumnlagringsindex. Den här typen av datalagring ger hög datakomprimering och frågeprestanda för stora tabeller. Det klustrade kolumnlagringsindexet är vanligtvis det bästa valet, men i vissa fall är ett grupperat index eller en heap lämplig lagringsstruktur.

Tips

En heap-tabell kan vara särskilt användbar för att läsa in tillfälliga data, till exempel en mellanlagringstabell, som omvandlas till en slutlig tabell.

En lista över columnstore-funktioner finns i Nyheter för kolumnlagringsindex. Information om hur du förbättrar prestanda för kolumnlagringsindex finns i Maximera radgruppskvaliteten för kolumnlagringsindex.

Statistik

Frågeoptimeraren använder statistik på kolumnnivå när den skapar planen för att köra en fråga. För att förbättra frågeprestanda är det viktigt att ha statistik för enskilda kolumner, särskilt kolumner som används i frågekopplingar. Synapse SQL stöder automatisk skapande av statistik.

Statistisk uppdatering sker inte automatiskt. Uppdatera statistik efter att ett stort antal rader har lagts till eller ändrats. Du kan till exempel uppdatera statistik efter en inläsning. Ytterligare information finns i vägledningsartikeln statistik .

Primärnyckel och unik nyckel

För dedikerad SQL-pool stöds PRIMÄRNYCKEL endast när både NONCLUSTERED och NOT ENFORCED används. UNIK begränsning stöds bara när NOT ENFORCED används. Mer information finns i artikeln begränsningar för dedikerade SQL-pooltabeller .

Kommandon för att skapa tabeller

För dedikerad SQL-pool kan du skapa en tabell som en ny tom tabell. Du kan också skapa och fylla i en tabell med resultatet av en select-instruktion. Följande är T-SQL-kommandona för att skapa en tabell.

T-SQL-instruktion	Beskrivning
CREATE TABLE	Skapar en tom tabell genom att definiera alla tabellkolumner och alternativ.
SKAPA EXTERN TABELL	Skapar en extern tabell. Definitionen av tabellen lagras i en dedikerad SQL-pool. Tabelldata lagras i Azure Blob Storage eller Azure Data Lake Storage.
CREATE TABLE AS SELECT	Fyller i en ny tabell med resultatet av en select-instruktion. Tabellkolumnerna och datatyperna baseras på select-instruktionsresultaten. Om du vill importera data kan den här instruktionen välja från en extern tabell.
SKAPA EXTERN TABELL SOM SELECT	Skapar en ny extern tabell genom att exportera resultatet av en select-instruktion till en extern plats. Platsen är antingen Azure Blob Storage eller Azure Data Lake Storage.

Justera källdata med informationslagret

Dedikerade SQL-pooltabeller fylls i genom att data läses in från en annan datakälla. För att belastningen ska lyckas måste antalet och datatyperna för kolumnerna i källdata överensstämma med tabelldefinitionen i informationslagret.

Anteckning

Att få data att justera kan vara den svåraste delen av att utforma dina tabeller.

Om data kommer från flera datalager kan du porta data till informationslagret och lagra dem i en integrationstabell. När data finns i integrationstabellen kan du använda kraften i en dedikerad SQL-pool för att implementera transformeringsåtgärder. När data har förberetts kan du infoga dem i produktionstabeller.

Tabellfunktioner som inte stöds

Den dedikerade SQL-poolen stöder många, men inte alla, tabellfunktioner som erbjuds av andra databaser. I följande lista visas några av tabellfunktionerna som inte stöds i en dedikerad SQL-pool.

• Sekundärnyckel, kontrollera tabellbegränsningar
• Beräknade kolumner
• Indexerade vyer
• Sequence
• Glesa kolumner
• Surrogatnycklar, implementera med identitet
• Synonymer
• Utlösare
• Unika index
• Användardefinierade typer

Frågor om tabellstorlek

I en dedikerad SQL-pool är ett enkelt sätt att identifiera utrymme och rader som används av en tabell i var och en av de 60 distributionerna att använda DBCC-PDW_SHOWSPACEUSED.

DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Tänk på att det kan vara ganska begränsande att använda DBCC-kommandon. Dynamiska hanteringsvyer (DMV:er) visar mer information än DBCC-kommandon. Börja med att skapa vyn nedan.

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
;

Sammanfattning av tabellutrymme

Den här frågan returnerar raderna och utrymmet efter tabell. Med sammanfattning av tabellutrymme kan du se vilka tabeller som är dina största tabeller. Du ser också om de är resursallokering, replikerade eller hash-distribuerade. För hash-distribuerade tabeller visar frågan distributionskolumnen.

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
;

Tabellutrymme efter distributionstyp

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
;

Tabellutrymme efter indextyp

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
;

Sammanfattning av distributionsutrymme

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
;

Nästa steg

När du har skapat tabellerna för ditt informationslager är nästa steg att läsa in data i tabellen. En inläsningsguide finns i Läsa in data i en dedikerad SQL-pool.