Condividi tramite

Generare identificatori univoci in una tabella del magazzino in Microsoft Fabric

  • Articolo

Si applica a✅: warehouse in Microsoft Fabric

Assegnare un identificatore univoco a ogni riga di una tabella è un requisito comune nei data warehouse. Negli ambienti basati su SQL Server questa operazione viene in genere eseguita creando una colonna Identity in una tabella, ma attualmente questa funzionalità non è supportata in un warehouse in Microsoft Fabric. È invece necessario usare una tecnica alternativa. Presenteremo due alternative.

Questo articolo descrive le tecniche alternative per generare identificatori univoci in una tabella warehouse.

Metodo 1

Questo metodo è più applicabile quando è necessario creare valori Identity, ma l'ordine dei valori non è importante (i valori non sequenziali sono accettabili).

I valori univoci vengono generati nel codice che inserisce i dati nella tabella.

  1. Per creare dati univoci usando questo metodo, creare una tabella che include una colonna che archivia i valori di identificatore univoco. Il tipo di dati della colonna deve essere impostato su bigint. È inoltre necessario definire la colonna come NOT NULL per assicurarsi che a ogni riga venga assegnato un identificatore.

    Nell'esempio di codice T-SQL seguente viene creata una tabella di esempio denominata Orders_with_Identifier nello dbo schema, in cui la Row_ID colonna funge da chiave univoca.

    --Drop a table named 'Orders_with_Identifier' in schema 'dbo', if it exists
IF OBJECT_ID('[dbo].[Orders_with_Identifier]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Orders_with_Identifier];
GO

CREATE TABLE [dbo].[Orders_with_Identifier] (
    [Row_ID] BIGINT NOT NULL,
    [O_OrderKey] BIGINT NULL,
    [O_CustomerKey] BIGINT NULL,
    [O_OrderStatus] VARCHAR(1) NULL,
    [O_TotalPrice] DECIMAL(15, 2) NULL,
    [O_OrderDate] DATE NULL,
    [O_OrderPriority] VARCHAR(15) NULL,
    [O_Clerk] VARCHAR (15) NULL,
    [O_ShipPriority] INT NULL,
    [O_Comment] VARCHAR (79) NULL
);

  2. Quando si inseriscono righe nella tabella, tramite script T-SQL o codice dell'applicazione o in caso contrario, generare dati univoci per Row_ID con la NEWID() funzione . Questa funzione genera un valore univoco di tipo uniqueidentifier che può quindi essere eseguito il cast e archiviato come bigint.

    Il codice seguente inserisce righe nella dbo.Orders_with_Identifier tabella. I valori per la Row_ID colonna vengono calcolati convertendo i valori restituiti dalla newid() funzione . La funzione non richiede una ORDER BY clausola e genera un nuovo valore per ogni record.

    --Insert new rows with unique identifiers
INSERT INTO [dbo].[Orders_with_Identifier]
SELECT
    CONVERT(BIGINT, CONVERT(VARBINARY, CONCAT(NEWID(), GETDATE()))) AS [Row_ID],
    [src].[O_OrderKey],
    [src].[O_CustomerKey],
    [src].[O_OrderStatus],
    [src].[O_TotalPrice],
    [src].[O_OrderDate],
    [src].[O_OrderPriority],
    [src].[O_Clerk],
    [src].[O_ShipPriority],
    [src].[O_Comment]
FROM [dbo].[Orders] AS [src];

Metodo 2

Questo metodo è più applicabile quando è necessario creare valori identity sequenziali, ma deve essere usato con cautela nei set di dati di dimensioni maggiori, in quanto può essere più lento rispetto ai metodi alternativi. È consigliabile prendere in considerazione anche più processi che inseriscono dati contemporaneamente, in quanto ciò potrebbe causare valori duplicati.

  1. Per creare dati univoci usando questo metodo, creare una tabella che include una colonna che archivia i valori di identificatore univoco. Il tipo di dati della colonna deve essere impostato su int o bigint, a seconda del volume di dati che si prevede di archiviare. È inoltre necessario definire la colonna come NOT NULL per assicurarsi che a ogni riga venga assegnato un identificatore.

    Nell'esempio di codice T-SQL seguente viene creata una tabella di esempio denominata Orders_with_Identifier nello dbo schema, in cui la Row_ID colonna funge da chiave univoca.

    --Drop a table named 'Orders_with_Identifier' in schema 'dbo', if it exists
IF OBJECT_ID('[dbo].[Orders_with_Identifier]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Orders_with_Identifier];
GO

CREATE TABLE [dbo].[Orders_with_Identifier] (
    [Row_ID] BIGINT NOT NULL,
    [O_OrderKey] BIGINT NULL,
    [O_CustomerKey] BIGINT NULL,
    [O_OrderStatus] VARCHAR(1) NULL,
    [O_TotalPrice] DECIMAL(15, 2) NULL,
    [O_OrderDate] DATE NULL,
    [O_OrderPriority] VARCHAR(15) NULL,
    [O_Clerk] VARCHAR (15) NULL,
    [O_ShipPriority] INT NULL,
    [O_Comment] VARCHAR (79) NULL
);
GO

  2. Prima di inserire righe nella tabella, è necessario determinare l'ultimo valore dell'identificatore archiviato nella tabella. A tale scopo, è possibile recuperare il valore massimo dell'identificatore. Questo valore deve essere assegnato a una variabile in modo che sia possibile farvi riferimento quando si inseriscono righe di tabella (nel passaggio successivo).

    Il codice seguente assegna l'ultimo valore dell'identificatore a una variabile denominata @MaxID.

    --Assign the last identifier value to a variable
--If the table doesn't contain any rows, assign zero to the variable
DECLARE @MaxID AS BIGINT;

IF EXISTS(SELECT * FROM [dbo].[Orders_with_Identifier])
    SET @MaxID = (SELECT MAX([Row_ID]) FROM [dbo].[Orders_with_Identifier]);
ELSE
    SET @MaxID = 0;

  3. Quando si inseriscono righe nella tabella, i numeri univoci e sequenziali vengono calcolati aggiungendo il valore della variabile @MaxID ai valori restituiti dalla funzione ROW_NUMBER. Questa funzione è una funzione window che calcola un numero di riga sequenziale a partire da 1.

    Il codice T-SQL seguente, eseguito nello stesso batch dello script nel passaggio 2, inserisce righe nella Orders_with_Identifier tabella. I valori per la colonna Row_ID vengono calcolati aggiungendo la variabile @MaxID ai valori restituiti dalla funzione ROW_NUMBER. La funzione deve avere una clausola ORDER BY che definisca l'ordine logico delle righe all'interno del set di risultati. Tuttavia, quando è impostato su SELECT NULL, non viene imposto alcun ordine logico, ovvero i valori dell'identificatore vengono assegnati arbitrariamente. Questa clausola ORDER BY comporta tempi di esecuzione più rapidi.

    --Insert new rows with unique identifiers
INSERT INTO [dbo].[Orders_with_Identifier]
SELECT
    @MaxID + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [Row_ID],
    [src].[O_OrderKey],
    [src].[O_CustomerKey],
    [src].[O_OrderStatus],
    [src].[O_TotalPrice],
    [src].[O_OrderDate],
    [src].[O_OrderPriority],
    [src].[O_Clerk],
    [src].[O_ShipPriority],
    [src].[O_Comment]
FROM [dbo].[Orders] AS [src];

Contenuto correlato