Partage via

Générer des identificateurs uniques dans une table d'entrepôt dans Microsoft Fabric

  • Article

S'applique à :✅ Entrepôt dans Microsoft Fabric

Il est courant dans les entrepôts de données d'attribuer un identificateur unique à chaque ligne d'une table. Dans les environnements basés sur SQL Server, cela se fait généralement en créant une colonne d’identité dans une table. Toutefois, cette fonctionnalité n’est actuellement pas prise en charge dans un entrepôt dans Microsoft Fabric. Au lieu de cela, vous devez utiliser une technique de contournement. Nous présentons deux alternatives.

Cet article décrit des techniques de contournement pour générer des identifiants uniques dans une table d'entrepôt.

Méthode 1

Cette méthode est particulièrement applicable lorsque vous devez créer des valeurs d'identité, mais que l'ordre des valeurs n'est pas important (les valeurs non séquentielles sont acceptables).

Des valeurs uniques sont générées dans le code qui insère des données dans la table.

  1. Pour créer des données uniques à l’aide de cette méthode, créez une table qui inclut une colonne qui stocke des valeurs d’identifiant uniques. Le type de données de la colonne doit être défini sur bigint. Vous devez également définir la colonne sur NOT NULL pour vous assurer que chaque ligne reçoit un identificateur.

    L'exemple de code T-QL suivant crée un exemple de table nommée Orders_with_Identifier dans le schéma dbo, où la colonne Row_ID sert de clé unique.

    --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. Lorsque vous insérez des lignes dans la table, via des scripts T-SQL ou du code d'application ou autrement, générez des données uniques Row_ID avec la fonction NEWID(). Cette fonction génère une valeur unique de type uniqueidentifier qui peut ensuite être convertie et stockée sous la forme d'un bigint.

    Le code suivant insère des lignes dans la table dbo.Orders_with_Identifier. Les valeurs de la colonne Row_ID sont calculées en convertissant les valeurs renvoyées par la fonction newid(). La fonction ne nécessite pas de clause ORDER BY et génère une nouvelle valeur pour chaque enregistrement.

    --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];

Méthode 2

Cette méthode est particulièrement adaptée lorsque vous devez créer des valeurs d’identité séquentielles, mais elle doit être utilisée avec prudence sur des ensembles de données plus volumineux, car elle peut être plus lente que les méthodes alternatives. Il convient également de tenir compte de la possibilité que plusieurs processus insèrent des données simultanément, car cela pourrait entraîner des valeurs en double.

  1. Pour créer des données uniques à l’aide de cette méthode, créez une table qui inclut une colonne qui stocke des valeurs d’identifiant uniques. Le type de données de la colonne doit être défini sur int ou bigint, selon le volume de données que vous prévoyez de stocker. Vous devez également définir la colonne sur NOT NULL pour vous assurer que chaque ligne reçoit un identificateur.

    L'exemple de code T-QL suivant crée un exemple de table nommée Orders_with_Identifier dans le schéma dbo, où la colonne Row_ID sert de clé unique.

    --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. Avant d'insérer des lignes dans la table, vous devez déterminer la dernière valeur d'identificateur stockée dans la table. Pour ce faire, récupérez la valeur d'identificateur maximale. Cette valeur doit être affectée à une variable afin de pouvoir y faire référence lorsque vous insérez des lignes de table (à l'étape suivante).

    Le code suivant affecte la dernière valeur d'identificateur à une variable nommée @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. Lorsque vous insérez des lignes dans la table, des nombres uniques et séquentiels sont calculés en ajoutant la valeur de la variable @MaxID aux valeurs retournées par la fonction ROW_NUMBER. Cette fonction est une fonction de fenêtre qui calcule un numéro de ligne séquentiel commençant par 1.

    Le code T-SQL suivant, exécuté dans le même lot que le script de l’étape 2, insère des lignes dans la table Orders_with_Identifier. Les valeurs de la colonne Row_ID sont calculées en ajoutant la variable @MaxID aux valeurs retournées par la fonction ROW_NUMBER. La fonction doit avoir une clause ORDER BY, qui définit l'ordre logique des lignes dans le jeu de résultats. Cependant, lorsqu'il est défini sur SELECT NULL, aucun ordre logique n'est imposé, ce qui signifie que les valeurs d'identifiant sont attribuées arbitrairement. Cette clause ORDER BY entraîne une durée d'exécution plus rapide.

    --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];

Contenu connexe