Comment indexer des données à partir d’Azure SQL dans Recherche Azure AI

  • Article

Dans cet article, découvrez comment configurer un indexeur qui importe du contenu à partir d’Azure SQL Database ou d’une instance gérée Azure SQL et le rend consultable dans Recherche Azure AI.

Cet article vient en complément de l’article Créer un indexeur avec des informations spécifiques sur l’indexation à partir d’Azure SQL. Il utilise les API REST pour illustrer un workflow en trois parties commun à tous les indexeurs : créer une source de données, créer un index, créer un indexeur.

Cet article fournit également ce qui suit :

  • Description des stratégies de détection des modifications prises en charge par l’indexeur Azure SQL pour configurer l’indexation incrémentielle.

  • Section Forum aux questions (FAQ) pour obtenir des réponses aux questions relatives à la compatibilité des fonctionnalités.

Remarque

La synchronisation des données en temps réel n’est pas possible avec un indexeur. Un indexeur peut réindexer votre table toutes les cinq minutes au maximum. Si les mises à jour de données doivent être reflétées dans l’index plus rapidement, nous vous recommandons d’envoyer (push) directement les lignes mises à jour.

Prérequis

  • Une base de données Azure SQL avec des données dans une table ou une vue unique, ou une SQL Managed Instance avec un point de terminaison public.

    Utilisez une table si vos données sont volumineuses ou si vous avez besoin d’une indexation incrémentielle utilisant des fonctionnalités de détection des modifications natives de SQL.

    Utilisez une vue si vous devez consolider les données de plusieurs tables. Les vues volumineuses ne sont pas idéales pour l’indexeur SQL. Une solution de contournement consiste à créer une table réservée à l’ingestion dans votre index Recherche Azure AI. Vous pourrez ainsi utiliser le suivi intégré des modifications SQL, plus facile à implémenter que High Water Mark.

  • Autorisations de lecture. Recherche Azure AI prend en charge l’authentification SQL Server, où le nom d’utilisateur et le mot de passe sont fournis dans la chaîne de connexion. Vous pouvez également configurer une identité managée et utiliser des rôles Azure.

Pour utiliser les exemples de cet article, vous avez besoin d’un client REST.

Parmi les autres approches de création d’un indexeur Azure SQL figurent les kits de développement logiciel (SDK) Azure ou l’Assistant Importation de données du portail Azure. Si vous utilisez le portail Azure, assurez-vous que l’accès à tous les réseaux publics est activé dans le pare-feu Azure SQL, et que le client dispose d’un accès via une règle de trafic entrant.

Définir la source de données

La définition de la source de données spécifie les données à indexer, les informations d’identification et les stratégies permettant d’identifier les changements de données. Une source de données est définie comme une ressource indépendante de manière à pouvoir être utilisée par plusieurs indexeurs.

  1. Créez une source de données ou mettez à jour une source de données pour fournir sa définition :

     POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
 Content-Type: application/json
 api-key: admin-key

 {
     "name" : "myazuresqldatasource",
     "description" : "A database for testing Azure AI Search indexes.",
     "type" : "azuresql",
     "credentials" : { "connectionString" : "Server=tcp:<your server>.database.windows.net,1433;Database=<your database>;User ID=<your user name>;Password=<your password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" },
     "container" : { 
         "name" : "name of the table or view that you want to index",
         "query" : null (not supported in the Azure SQL indexer)
         },
     "dataChangeDetectionPolicy": null,
     "dataDeletionDetectionPolicy": null,
     "encryptionKey": null,
     "identity": null
 }

  2. Fournissez à la source de données un nom unique conforme aux conventions de nommage de Recherche Azure AI.

  3. Définissez « type » sur "azuresql" (obligatoire).

  4. Définissez « credentials » sur la chaîne de connexion :

    • Vous pouvez obtenir cette chaîne de connexion d’accès complet à partir du portail Azure. Utilisez l'option ADO.NET connection string. Définissez le nom d'utilisateur et le mot de passe.

    • Vous pouvez également spécifier une chaîne de connexion d’identité managée qui n’inclut pas de secrets de base de données au format suivant : Initial Catalog|Database=<your database name>;ResourceId=/subscriptions/<your subscription ID>/resourceGroups/<your resource group name>/providers/Microsoft.Sql/servers/<your SQL Server name>/;Connection Timeout=connection timeout length;.

    Pour plus d’informations, consultez Se connecter à l’indexeur Azure SQL Database à l’aide d’une identité managée.

Ajouter des champs de recherche à un index

Dans un index de recherche, ajoutez les champs qui correspondent aux champs de la base de données SQL. Assurez-vous que le schéma d’index de recherche est compatible avec le schéma source en utilisant des types de données équivalents.

  1. Créez ou mettez à jour un index pour définir les champs de recherche qui stockeront les données :

    POST https://[service name].search.windows.net/indexes?api-version=2020-06-30
Content-Type: application/json
api-key: [Search service admin key]
{
    "name": "mysearchindex",
    "fields": [{
        "name": "id",
        "type": "Edm.String",
        "key": true,
        "searchable": false
    }, 
    {
        "name": "description",
        "type": "Edm.String",
        "filterable": false,
        "searchable": true,
        "sortable": false,
        "facetable": false,
        "suggestions": true
    }
  ]
}

  2. Créez un champ de clé de document ("key": true) qui identifie de façon unique chaque document de recherche. Il s’agit du seul champ requis dans un index de recherche. En règle générale, la clé primaire de la table est mappée au champ de clé d’index. La clé de document doit être unique et non null. Les valeurs peuvent être numériques dans les données sources, mais dans un index de recherche, une clé est toujours une chaîne.

  3. Créez d’autres champs pour ajouter du contenu avec possibilité de recherche. Pour obtenir des instructions, consultez Créer un index.

Mappage des types de données

Type de données SQL Types de champ Recherche Azure AI Notes
bit Edm.Boolean, Edm.String
int, smallint, tinyint Edm.Int32, Edm.Int64, Edm.String
bigint Edm.Int64, Edm.String
real, float Edm.Double, Edm.String
smallmoney, money decimal numeric Edm.String Recherche Azure AI ne prend pas en charge la conversion de types décimaux en Edm.Double, car elle entraîne une perte de précision
char, nchar, varchar, nvarchar Edm.String
Collection(Edm.String)		 Une chaîne SQL peut être utilisée pour remplir un champ Collection(Edm.String) si la chaîne représente un tableau JSON de chaînes : ["red", "white", "blue"]
smalldatetime, datetime, datetime2, date, datetimeoffset Edm.DateTimeOffset, Edm.String
uniqueidentifer Edm.String
géographie Edm.GeographyPoint Seules les instances Geography de type POINT avec SRID 4326 (valeur par défaut) sont prises en charge
rowversion Non applicable Les colonnes de version de ligne ne peuvent pas être stockées dans l'index de recherche, mais peuvent être utilisées pour le suivi des modifications
time, timespan, binary, varbinary, image, xml, geometry, types CLR Non applicable Non pris en charge

Configurer et exécuter l’indexeur Azure SQL

Une fois l’index et la source de données créés, vous êtes prêt à créer l’indexeur. La configuration de l’indexeur spécifie les entrées, les paramètres et les propriétés qui contrôlent les comportements d’exécution.

  1. Créez ou mettez à jour un indexeur en lui attribuant un nom, et en référençant la source de données et l’index cible :

    POST https://[service name].search.windows.net/indexers?api-version=2020-06-30
Content-Type: application/json
api-key: [search service admin key]
{
    "name" : "[my-sqldb-indexer]",
    "dataSourceName" : "[my-sqldb-ds]",
    "targetIndexName" : "[my-search-index]",
    "disabled": null,
    "schedule": null,
    "parameters": {
        "batchSize": null,
        "maxFailedItems": 0,
        "maxFailedItemsPerBatch": 0,
        "base64EncodeKeys": false,
        "configuration": {
            "queryTimeout": "00:04:00",
            "convertHighWaterMarkToRowVersion": false,
            "disableOrderByHighWaterMarkColumn": false
        }
    },
    "fieldMappings": [],
    "encryptionKey": null
}

  2. Sous paramètres, la section de configuration contient des paramètres spécifiques à Azure SQL :

    • Par défaut, le délai d’exécution d’une requête SQL est de 5 minutes, mais il peut être modifié.

    • « convertHighWaterMarkToRowVersion » optimise la stratégie de détection des modifications de limite supérieure. Les stratégies de détection des modifications sont définies dans la source de données. Si vous utilisez la stratégie de détection des modifications native, ce paramètre n’a aucun effet.

    • « disableOrderByHighWaterMarkColumn » indique que la requête SQL utilisée par la stratégie de limite supérieure doit omettre la clause ORDER BY. Si vous utilisez la stratégie de détection des modifications native, ce paramètre n’a aucun effet.

  3. Spécifiez les mappages de champs s’il existe des différences dans le nom ou le type du champ, ou si vous avez besoin de plusieurs versions d’un champ source dans l’index de recherche.

  4. Pour plus d’informations sur les autres propriétés, consultez Créer un indexeur.

Un indexeur s’exécute automatiquement quand il est créé. Vous pouvez l’éviter en définissant « disabled » sur true. Pour contrôler l’exécution de l’indexeur, exécutez un indexeur à la demande ou placez-le dans une planification.

Vérifier l’état de l’indexeur

Pour monitorer l’état de l’indexeur et l’historique d’exécution, envoyez une demande Obtenir l’état de l’indexeur :

GET https://myservice.search.windows.net/indexers/myindexer/status?api-version=2020-06-30
  Content-Type: application/json  
  api-key: [admin key]

La réponse comprend l’état et le nombre d’éléments traités. Le résultat doit ressembler à l’exemple suivant :

    {
        "status":"running",
        "lastResult": {
            "status":"success",
            "errorMessage":null,
            "startTime":"2022-02-21T00:23:24.957Z",
            "endTime":"2022-02-21T00:36:47.752Z",
            "errors":[],
            "itemsProcessed":1599501,
            "itemsFailed":0,
            "initialTrackingState":null,
            "finalTrackingState":null
        },
        "executionHistory":
        [
            {
                "status":"success",
                "errorMessage":null,
                "startTime":"2022-02-21T00:23:24.957Z",
                "endTime":"2022-02-21T00:36:47.752Z",
                "errors":[],
                "itemsProcessed":1599501,
                "itemsFailed":0,
                "initialTrackingState":null,
                "finalTrackingState":null
            },
            ... earlier history items
        ]
    }

L’historique d’exécution contient jusqu’à 50 exécutions les plus récentes, classées par ordre chronologique inversé, la dernière exécution apparaissant en premier.

Indexation de lignes nouvelles, modifiées et supprimées

Si votre base de données SQL prend en charge le suivi des modifications, un indexeur de recherche peut récupérer uniquement le contenu nouveau et mis à jour lors des exécutions ultérieures de l’indexeur.

Pour activer l’indexation incrémentielle, définissez la propriété « dataChangeDetectionPolicy » dans la définition de votre source de données. Cette propriété indique à l’indexeur quel mécanisme de suivi des modifications est utilisé sur votre table ou vue.

Pour les indexeurs Azure SQL, il existe deux stratégies de détection des modifications :

  • « SqlIntegratedChangeTrackingPolicy » (s’applique aux tables uniquement)

  • « HighWaterMarkChangeDetectionPolicy » (fonctionne pour les tables et les vues)

Stratégie de suivi intégré des modifications SQL

Nous vous recommandons d’utiliser « SqlIntegratedChangeTrackingPolicy » pour son efficacité et sa capacité à identifier les lignes supprimées.

Configuration requise pour la base de données :

  • SQL Server 2012 SP3 et versions ultérieures si vous utilisez SQL Server sur des machines virtuelles Azure.
  • Azure SQL Database ou SQL Managed Instance
  • Tables uniquement (aucune vue)
  • Dans la base de données, activez le suivi de la table
  • Aucune clé primaire composite (clé primaire contenant plusieurs colonnes) dans la table
  • Aucun index cluster sur la table. Comme solution de contournement, tout index cluster doit être supprimé et recréé en tant qu’index non cluster. Toutefois, les performances peuvent être affectées dans la source par rapport à un index cluster

Les stratégies de détection des modifications sont ajoutées aux définitions de la source de données. Pour utiliser cette stratégie, créez ou mettez à jour votre source de données comme suit :

POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
Content-Type: application/json
api-key: admin-key
    {
        "name" : "myazuresqldatasource",
        "type" : "azuresql",
        "credentials" : { "connectionString" : "connection string" },
        "container" : { "name" : "table name" },
        "dataChangeDetectionPolicy" : {
            "@odata.type" : "#Microsoft.Azure.Search.SqlIntegratedChangeTrackingPolicy"
    }

Lorsque vous utilisez la stratégie de suivi intégré des modifications SQL, ne spécifiez pas de stratégie de détection de suppression de données distincte. La stratégie de suivi intégré des modifications SQL offre une prise en charge intégrée de l’identification des lignes supprimées. Toutefois, pour les lignes supprimées détectées automatiquement, la clé de document de votre index de recherche doit être identique à la clé primaire de la table SQL.

Remarque

Lorsque vous utilisez TRUNCATE TABLE pour supprimer un grand nombre de lignes dans une table SQL, l'indexeur doit être reset pour réinitialiser l'état de suivi des modifications et récupérer les suppressions de lignes.

Stratégie de détection des modifications de limite supérieure

Cette stratégie de détection des modifications s’appuie sur une colonne « Limite supérieure » de votre table ou vue qui capture la version ou l’heure pour laquelle une ligne a été mise à jour. Si vous utilisez une vue, vous devez vous servir d’une stratégie de limite supérieure.

La colonne de limite supérieure doit remplir les conditions suivantes :

  • Toutes les insertions spécifient une valeur pour la colonne.
  • Toutes les mises à jour d'un élément modifient également la valeur de la colonne.
  • La valeur de cette colonne augmente à chaque insertion ou mise à jour.
  • Les requêtes utilisant les clauses WHERE et ORDER BY suivantes peuvent être exécutées efficacement : WHERE [High Water Mark Column] > [Current High Water Mark Value] ORDER BY [High Water Mark Column]

Remarque

Nous vous recommandons d’utiliser le type de données rowversion pour la colonne dédiée à la limite supérieure. Si un autre type de données est utilisé, il n’est pas garanti que le suivi des modifications capture toutes les modifications en présence de transactions qui s’exécutent en même temps qu’une requête de l’indexeur. Lorsque vous utilisez rowversion dans une configuration avec des réplicas en lecture seule, vous devez pointer l’indexeur sur le réplica principal. Seul un réplica principal peut être utilisé dans les scénarios de synchronisation de données.

Les stratégies de détection des modifications sont ajoutées aux définitions de la source de données. Pour utiliser cette stratégie, créez ou mettez à jour votre source de données comme suit :

POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
Content-Type: application/json
api-key: admin-key
    {
        "name" : "myazuresqldatasource",
        "type" : "azuresql",
        "credentials" : { "connectionString" : "connection string" },
        "container" : { "name" : "table or view name" },
        "dataChangeDetectionPolicy" : {
            "@odata.type" : "#Microsoft.Azure.Search.HighWaterMarkChangeDetectionPolicy",
            "highWaterMarkColumnName" : "[a rowversion or last_updated column name]"
        }
    }

Remarque

Si la table source n’a pas d’index dans la colonne de limite supérieure, les requêtes utilisées par l’indexeur SQL risquent d’expirer. En particulier, la clause ORDER BY [High Water Mark Column] a besoin d’un index pour pouvoir s’exécuter efficacement lorsque la table contient de nombreuses lignes.

convertHighWaterMarkToRowVersion

Si vous utilisez un type de données rowversion pour la colonne de limite supérieure, envisagez de définir la propriété convertHighWaterMarkToRowVersion dans le configuration de l’indexeur. L’affectation de la valeur true à cette propriété entraîne les comportements suivants :

  • Utilisez le type de données rowversion pour la colonne de limite supérieure dans la requête SQL de l’indexeur. L’utilisation du type de données correct améliore le niveau de performance de requête de l’indexeur.

  • Soustrayez un de la valeur rowversion avant l’exécution de la requête de l’indexeur. Les affichages comportant d’une à plusieurs jointures peuvent contenir des lignes avec des valeurs rowversion en double. Soustraire un garantit que la requête de l’indexeur n’ignore pas ces lignes.

Pour activer cette propriété, créez ou mettez à jour l’indexeur avec la configuration suivante :

    {
      ... other indexer definition properties
     "parameters" : {
            "configuration" : { "convertHighWaterMarkToRowVersion" : true } }
    }

queryTimeout

Si vous rencontrez des erreurs de temporisation, utilisez le paramètre de configuration d’indexeur queryTimeout pour une valeur d’expiration plus élevée que les 5 minutes par défaut. Par exemple, pour fixer un délai d’expiration de 10 minutes, créez ou mettez à jour l’indexeur avec la configuration suivante :

    {
      ... other indexer definition properties
     "parameters" : {
            "configuration" : { "queryTimeout" : "00:10:00" } }
    }

disableOrderByHighWaterMarkColumn

Vous pouvez également désactiver la clause ORDER BY [High Water Mark Column]. Toutefois, cette action est déconseillée car, si l’exécution de l’indexeur est interrompue par une erreur, l’indexeur doit traiter à nouveau toutes les lignes quand son exécution reprend, même s’il avait déjà traité la quasi-totalité des lignes au moment de l’interruption. Pour désactiver la clause ORDER BY, utilisez le paramètre disableOrderByHighWaterMarkColumn dans la définition de l’indexeur :

    {
     ... other indexer definition properties
     "parameters" : {
            "configuration" : { "disableOrderByHighWaterMarkColumn" : true } }
    }

Stratégie de détection des colonnes à suppression réversible

Lorsque des lignes sont supprimées de la table source, vous devez également supprimer ces lignes de l'index de recherche. Si vous utilisez la stratégie de suivi intégré des modifications SQL, cette opération est prise en charge à votre place. Mais la stratégie de suivi des modifications de limite supérieure ne vous est d’aucune aide pour les lignes supprimées. Que faire, alors ?

Si des lignes sont physiquement supprimées de la table, la Recherche Azure AI n’a aucun moyen de déduire la présence d’enregistrements qui n’existent plus. Toutefois, vous pouvez utiliser la technique de la « suppression réversible » pour supprimer des lignes logiquement sans les supprimer de la table. Ajoutez une colonne à votre table ou votre vue et marquez les lignes comme supprimées à l’aide de cette colonne.

Lorsque vous utilisez la technique de suppression réversible, vous pouvez spécifier cette stratégie réversible comme suit lors de la création ou de la mise à jour de la source de données :

    {
        …,
        "dataDeletionDetectionPolicy" : {
           "@odata.type" : "#Microsoft.Azure.Search.SoftDeleteColumnDeletionDetectionPolicy",
           "softDeleteColumnName" : "[a column name]",
           "softDeleteMarkerValue" : "[the value that indicates that a row is deleted]"
        }
    }

softDeleteMarkerValue doit être une chaîne dans la représentation au format chaîne de votre source de données. Utilisez la représentation sous forme de chaîne de votre valeur réelle. Par exemple, si vous avez une colonne d’entiers dans laquelle les lignes supprimées sont marquées avec la valeur 1, utilisez "1". Si vous avez une colonne BIT dans laquelle les lignes supprimées sont marquées avec la valeur booléenne True, utilisez le littéral de chaîne "True" ou "true", la casse ne comptant pas.

Si vous configurez une stratégie de suppression réversible à partir du portail Azure, n’ajoutez pas de guillemets autour de la valeur du marqueur de suppression réversible. Le contenu du champ est déjà interprété comme une chaîne et sera automatiquement converti en chaîne JSON. Dans les exemples ci-dessus, tapez simplement 1, True ou true dans le champ du portail.

FAQ

Q : Puis-je indexer les colonnes Always Encrypted ?

Nombre Les colonnes Always Encrypted ne sont pas actuellement prises en charge par les indexeurs de Recherche Azure AI.

Q. : Puis-je utiliser l’indexeur SQL Azure avec des bases de données SQL exécutées sur des machines virtuelles IaaS dans Azure ?

Oui. Toutefois, vous devez autoriser votre service de recherche à se connecter à votre base de données. Pour plus d’informations, consultez l’article Configurer une connexion d’un indexeur de Recherche Azure AI à SQL Server sur une machine virtuelle Azure.

Q. : Puis-je utiliser l’indexeur Azure SQL avec des bases de données SQL exécutées localement ?

Pas directement. La connexion directe n’est pas prise en charge, ni recommandée, car elle vous oblige à ouvrir vos bases de données au trafic Internet. Les clients ont réussi à l’aide de technologies de pont telles qu’Azure Data Factory. Pour plus d’informations, consultez Envoyer des données à un index Recherche Azure AI à l’aide d’Azure Data Factory.

Q : Puis-je utiliser un réplica secondaire dans un cluster de basculement comme source de données ?

Cela dépend. Pour l’indexation intégrale d’une table ou d’une vue, vous pouvez utiliser un réplica secondaire.

Pour une indexation incrémentielle, Recherche Azure AI prend en charge deux stratégies de détection des modifications : le suivi de modifications intégré SQL et la définition d’une limite supérieure.

Sur les réplicas en lecture seule, SQL Database ne prend pas en charge le suivi des modifications intégré. Par conséquent, vous devez utiliser la stratégie de limite supérieure.

Nous vous recommandons d’utiliser le type de données rowversion pour la colonne dédiée à la limite supérieure. Toutefois, l’utilisation de rowversion repose sur la fonction MIN_ACTIVE_ROWVERSION, qui n’est pas prise en charge sur les réplicas en lecture seule. Par conséquent, vous devez pointer l’indexeur sur un réplica principal si vous utilisez rowversion.

Si vous essayez d’utiliser rowversion sur un réplica en lecture seule, l’erreur suivante s’affiche :

« L’utilisation d’une colonne rowversion pour le suivi des modifications n’est pas prise en charge sur les réplicas de disponibilité secondaires (en lecture seule). Veuillez mettre à jour la source de données et spécifier une connexion au réplica de disponibilité principal. La propriété de « capacité de mise à jour » de la base de données actuelle est « READ_ONLY ».

Q : Puis-je utiliser une colonne autre que rowversion pour le suivi des modifications de la limite supérieure ?

Cela n’est pas recommandé. Seule la colonne rowversion permet une synchronisation fiable des données. Toutefois, en fonction de votre logique d’application, cette opération peut être sécurisée si :

  • Vous pouvez vous assurer que pendant l’exécution de l’indexeur, aucune transaction n’est en attente sur la table en cours d’indexation (par exemple, toutes les mises à jour de la table s’effectuent de manière planifiée par lot, et la planification de l’indexeur Recherche Azure AI est définie de manière à éviter tout chevauchement avec la planification de la mise à jour de la table).

  • Vous procédez régulièrement à une réindexation complète pour sélectionner toutes les lignes manquantes.