Come indicizzare i dati da SQL di Azure in Ricerca di intelligenza artificiale di Azure

Questo articolo illustra come configurare un indicizzatore che importa contenuto da database SQL di Azure o da un'istanza gestita di SQL di Azure e lo rende ricercabile in Ricerca di intelligenza artificiale di Azure.

Questo articolo integra Creare un indicizzatore con informazioni specifiche di Azure SQL. Usa le API REST per illustrare un flusso di lavoro in tre parti comune a tutti gli indicizzatori: creare un'origine dati, creare un indice, creare un indicizzatore.

Questo articolo fornisce anche:

  • Descrizione dei criteri di rilevamento delle modifiche supportati dall'indicizzatore SQL di Azure in modo da poter configurare l'indicizzazione incrementale.

  • Sezione domande frequenti per le risposte alle domande sulla compatibilità delle funzionalità.

Nota

La sincronizzazione dei dati in tempo reale non è possibile con un indicizzatore. Un indicizzatore può reindicizzare la tabella al massimo ogni 5 minuti. Se gli aggiornamenti dei dati devono essere riportati prima nell'indice, è consigliabile eseguire direttamente il push delle righe aggiornate.

Prerequisiti

  • Un database SQL di Azure con dati in una singola tabella o vista o un Istanza gestita di SQL con un endpoint pubblico.

    Usare una tabella se i dati sono di grandi dimensioni o se è necessaria l'indicizzazione incrementale usando le funzionalità di rilevamento delle modifiche native di SQL.

    Usare una vista se è necessario consolidare i dati da più tabelle. Le viste di grandi dimensioni non sono ideali per l'indicizzatore SQL. Una soluzione alternativa consiste nel creare una nuova tabella solo per l'inserimento nell'indice di Ricerca di intelligenza artificiale di Azure. Sarà possibile usare il rilevamento delle modifiche integrato di SQL, più semplice da implementare rispetto a High Water Mark.

  • Autorizzazioni di lettura. Ricerca di intelligenza artificiale di Azure supporta l'autenticazione di SQL Server, in cui vengono forniti il nome utente e la password nel stringa di connessione. In alternativa, è possibile configurare un'identità gestita e usare i ruoli di Azure.

Per esaminare gli esempi in questo articolo, è necessario un client REST.

Altri approcci per la creazione di un indicizzatore SQL di Azure includono Azure SDK o Importazione guidata dati nella portale di Azure. Se si usa portale di Azure, assicurarsi che l'accesso a tutte le reti pubbliche sia abilitato nel firewall SQL di Azure e che il client abbia accesso tramite una regola in ingresso.

Definire l'origine dati

La definizione dell'origine dati specifica i dati da indicizzare, credenziali e criteri per identificare le modifiche nei dati. Un'origine dati viene definita come risorsa indipendente in modo che possa essere usata da più indicizzatori.

  1. Creare un'origine dati o aggiornare l'origine dati per impostarne la definizione:

     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. Specificare un nome univoco per l'origine dati che segue le convenzioni di denominazione di Ricerca intelligenza artificiale di Azure.

  3. Impostare "type" su "azuresql" (obbligatorio).

  4. Impostare "credentials" su un stringa di connessione:

    • È possibile ottenere un stringa di connessione di accesso completo dal portale di Azure. Usare l'opzione ADO.NET connection string. Impostare il nome utente e la password.

    • In alternativa, è possibile specificare un'identità gestita stringa di connessione che non include i segreti del database con il formato seguente: 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;.

    Per altre informazioni, vedere Connessione per database SQL di Azure indicizzatore usando un'identità gestita.

Aggiungere campi di ricerca a un indice

In un indice di ricerca aggiungere campi che corrispondono ai campi nel database SQL. Verificare che lo schema dell'indice di ricerca sia compatibile con lo schema di origine usando tipi di dati equivalenti.

  1. Creare o aggiornare un indice per definire i campi di ricerca che archivieranno i dati:

    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. Creare un campo chiave documento ("chiave": true) che identifichi in modo univoco ogni documento di ricerca. Questo è l'unico campo necessario in un indice di ricerca. In genere, la chiave primaria della tabella viene mappata al campo chiave di indice. La chiave del documento deve essere univoca e non Null. I valori possono essere numerici nei dati di origine, ma in un indice di ricerca una chiave è sempre una stringa.

  3. Creare altri campi per aggiungere altro contenuto ricercabile. Per indicazioni, vedere Creare un indice .

Mapping dei tipi di dati

Tipo di dati SQL Tipi di campo di Ricerca intelligenza artificiale di Azure Note
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 Ricerca di intelligenza artificiale di Azure non supporta la conversione di tipi decimali in Edm.Double perché in questo modo si perde la precisione
char, nchar, varchar, nvarchar Edm.String
Collection(Edm.String)
Una stringa SQL può essere usata per popolare un campo Collection(Edm.String) se la stringa rappresenta una matrice JSON di stringhe: ["red", "white", "blue"]
smalldatetime, datetime, datetime2, date, datetimeoffset Edm.DateTimeOffset, Edm.String
uniqueidentifer Edm.String
geografia Edm.GeographyPoint Sono supportate solo le istanze geografiche di tipo POINT con SRID 4326 (ossia l'impostazione predefinita)
rowversion Non applicabile Le colonne della versione di riga non possono essere archiviate nell'indice di ricerca, ma possono essere usate per il rilevamento delle modifiche
time, timespan, binary, varbinary, image, xml, geometry, CLR types Non applicabile Non supportato

Configurare ed eseguire l'indicizzatore SQL di Azure

Dopo aver creato l'indice e l'origine dati, è possibile creare l'indicizzatore. La configurazione dell'indicizzatore specifica gli input, i parametri e le proprietà che controllano i comportamenti di runtime.

  1. Creare o aggiornare un indicizzatore assegnando un nome e facendo riferimento all'origine dati e all'indice di destinazione:

    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. In parametri la sezione di configurazione include parametri specifici di Azure SQL:

    • Il timeout di query predefinito per l'esecuzione di query SQL è di 5 minuti, che è possibile eseguire l'override.

    • "convertHighWaterMarkToRowVersion" ottimizza i criteri di rilevamento delle modifiche di High Water Mark. I criteri di rilevamento delle modifiche vengono impostati nell'origine dati. Se si usano i criteri di rilevamento delle modifiche nativi, questo parametro non ha alcun effetto.

    • "disableOrderByHighWaterMarkColumn" causa l'omissione della clausola ORDER BY dalla query SQL usata dai criteri di contrassegno di acqua elevato. Se si usano i criteri di rilevamento delle modifiche nativi, questo parametro non ha alcun effetto.

  3. Specificare i mapping dei campi se sono presenti differenze nel nome o nel tipo di campo o se sono necessarie più versioni di un campo di origine nell'indice di ricerca.

  4. Per altre informazioni sulle altre proprietà, vedere Creare un indicizzatore .

Un indicizzatore viene eseguito automaticamente quando viene creato. È possibile evitare questo problema impostando "disabilitato" su true. Per controllare l'esecuzione dell'indicizzatore, eseguire un indicizzatore su richiesta o inserirlo in una pianificazione.

Controllare lo stato dell'indicizzatore

Per monitorare lo stato dell'indicizzatore e la cronologia di esecuzione, inviare una richiesta Get Indexer Status :To monitor the indexer status and execution history, send a Get Indexer Status request:

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

La risposta include lo stato e il numero di elementi elaborati. Dovrebbe essere simile all'esempio seguente:

    {
        "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
        ]
    }

La cronologia di esecuzione contiene fino a 50 delle esecuzioni completate più di recente, ordinate in ordine cronologico inverso in modo che l'esecuzione più recente venga prima.

Indicizzazione di righe nuove, modificate ed eliminate

Se il database SQL supporta il rilevamento delle modifiche, un indicizzatore di ricerca può selezionare solo il contenuto nuovo e aggiornato nelle esecuzioni successive dell'indicizzatore.

Per abilitare l'indicizzazione incrementale, impostare la proprietà "dataChangeDetectionPolicy" nella definizione dell'origine dati. Questa proprietà indica all'indicizzatore quale meccanismo di rilevamento delle modifiche viene usato nella tabella o nella vista.

Per gli indicizzatori SQL di Azure, sono disponibili due criteri di rilevamento delle modifiche:

  • "SqlIntegratedChangeTrackingPolicy" (si applica solo alle tabelle)

  • "HighWaterMarkChangeDetectionPolicy" (funziona per tabelle e viste)

Criteri di rilevamento delle modifiche integrati di SQL

È consigliabile usare "SqlIntegratedChangeTrackingPolicy" per l'efficienza e la capacità di identificare le righe eliminate.

Requisiti del database:

  • SQL Server 2012 SP3 e versioni successive, se si usa SQL Server nelle macchine virtuali di Azure
  • database SQL di Azure o Istanza gestita di SQL
  • Solo tabelle (nessuna vista)
  • Nel database abilitare il rilevamento delle modifiche per la tabella
  • Nessuna chiave primaria composita (una chiave primaria contenente più di una colonna) nella tabella
  • Nessun indice cluster nella tabella. Come soluzione alternativa, qualsiasi indice cluster deve essere eliminato e ricreato come indice non cluster, ma le prestazioni potrebbero essere influenzate nell'origine rispetto alla presenza di un indice cluster

I criteri di rilevamento delle modifiche vengono aggiunti alle definizioni dell'origine dati. Per utilizzare questo criterio, creare o aggiornare l'origine dati nel modo indicato di seguito:

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"
    }

Quando si usano i criteri di rilevamento delle modifiche integrati di SQL, non specificare un criterio di rilevamento dell'eliminazione dei dati separato. I criteri di rilevamento delle modifiche integrati di SQL supportano l'identificazione delle righe eliminate. Tuttavia, affinché le righe eliminate vengano rilevate automaticamente, la chiave del documento nell'indice di ricerca deve essere la stessa della chiave primaria nella tabella SQL.

Nota

Quando si usa TRUNCATE TABLE per rimuovere un numero elevato di righe da una tabella SQL, l'indicizzatore deve essere reimpostato per reimpostare lo stato di rilevamento delle modifiche in modo che possa selezionare le righe eliminate.

Criteri di rilevamento delle modifiche con limite massimo

Questo criterio di rilevamento delle modifiche si basa su una colonna "high water mark" nella tabella o nella vista che acquisisce la versione o l'ora dell'ultimo aggiornamento di una riga. Se si usa una vista, è consigliabile usare i criteri di livello più alto.

La colonna del contrassegno d'acqua elevato deve soddisfare i requisiti seguenti:

  • Tutti gli inserimenti specificano un valore per la colonna.
  • Tutti gli aggiornamenti a un elemento modificano anche il valore della colonna.
  • Il valore di questa colonna aumenta in base a ogni modifica o aggiornamento.
  • Le query con le clausole QUERY e ORDER BY seguenti possono essere eseguite in modo efficiente: WHERE [High Water Mark Column] > [Current High Water Mark Value] ORDER BY [High Water Mark Column]

Nota

È consigliabile usare il tipo di dati rowversion per la colonna di livello più alto. Se viene usato qualsiasi altro tipo di dati, il rilevamento delle modifiche non garantisce l'acquisizione di tutte le modifiche in presenza di transazioni in esecuzione simultaneamente con una query dell'indicizzatore. Quando si usa rowversion in una configurazione con le repliche di sola lettura, è necessario puntare l'indicizzatore alla replica primaria. Per scenari di sincronizzazione dei dati, è possibile usare solo una replica primaria.

I criteri di rilevamento delle modifiche vengono aggiunti alle definizioni dell'origine dati. Per utilizzare questo criterio, creare o aggiornare l'origine dati nel modo indicato di seguito:

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]"
        }
    }

Nota

Se la tabella di origine non dispone di un indice nella colonna del contrassegno di acqua elevato, è possibile che si verifichi un timeout delle query usate dall'indicizzatore SQL. In particolare, la clausola richiede l'esecuzione ORDER BY [High Water Mark Column] efficiente di un indice quando la tabella contiene molte righe.

convertHighWaterMarkToRowVersion

Se si usa un tipo di dati rowversion per la colonna del contrassegno d'acqua elevato, è consigliabile impostare la proprietà nella configurazione dell'indicizzatore convertHighWaterMarkToRowVersion . L'impostazione di questa proprietà su true comporta i comportamenti seguenti:

  • Usa il tipo di dati rowversion per la colonna del contrassegno d'acqua elevato nella query SQL dell'indicizzatore. L'uso del tipo di dati corretto migliora le prestazioni delle query dell'indicizzatore.

  • Sottrae uno dal valore rowversion prima dell'esecuzione della query dell'indicizzatore. Le viste con join uno-a-molti possono avere righe con valori rowversion duplicati. La sottrazione di una garantisce che la query dell'indicizzatore non manchi queste righe.

Per abilitare questa proprietà, creare o aggiornare l'indicizzatore con la configurazione seguente:

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

queryTimeout

Se si verificano errori di timeout, impostare l'impostazione di configurazione dell'indicizzatore queryTimeout su un valore superiore al timeout predefinito di 5 minuti. Ad esempio, per impostare il timeout su 10 minuti, creare o aggiornare l'indicizzatore con la seguente configurazione:

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

disableOrderByHighWaterMarkColumn

La clausola ORDER BY [High Water Mark Column] può anche essere disabilitata. Tuttavia, ciò non è consigliato perché se l'esecuzione dell'indicizzatore viene interrotta da un errore, l'indicizzatore deve rielaborare tutte le righe se viene eseguito in un secondo momento, anche se l'indicizzatore ha già elaborato quasi tutte le righe al momento dell'interruzione. Per disabilitare la clausola ORDER BY, usare l'impostazione disableOrderByHighWaterMarkColumn nella definizione dell'indicizzatore:

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

Criteri di rilevamento eliminazione colonna di eliminazione temporanea

Quando le righe vengono eliminate dalla tabella di origine, è probabile che si desideri eliminarle anche dall’indice di ricerca. Se si utilizzano i criteri di rilevamento delle modifiche integrati di SQL, questa operazione è automatica. Tuttavia, i criteri di rilevamento delle modifiche limite massimo non sono di supporto all’utente con le righe eliminate. Cosa fare?

Se le righe vengono rimosse fisicamente dalla tabella, Ricerca di intelligenza artificiale di Azure non può dedurre la presenza di record che non esistono più. Tuttavia, è possibile usare la tecnica di "eliminazione temporanea" per eliminare in modo logico le righe senza rimuoverle dalla tabella. Aggiungere una colonna alla tabella o alla vista e contrassegnare le righe come eliminate tramite la colonna.

Quando si utilizza la tecnica dell’eliminazione temporanea, è possibile specificare la modalità di eliminazione temporanea come segue se si crea o si aggiorna l’origine dati:

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

SoftDeleteMarkerValue deve essere una stringa nella rappresentazione JSON dell'origine dati. Usare la rappresentazione di stringa del valore effettivo. Ad esempio, se si dispone di una colonna di valori integer in cui le righe eliminate sono contrassegnate con il valore 1, usare "1". Se si ha una colonna BIT in cui le righe eliminate sono contrassegnate con il valore booleano true, usare il valore letterale stringa "True" o "true". La distinzione tra maiuscole e minuscole non è rilevante.

Se si configura un criterio di eliminazione temporanea dal portale di Azure, non aggiungere virgolette intorno al valore dell'indicatore di eliminazione temporanea. Il contenuto del campo è già compreso come stringa e verrà tradotto automaticamente in una stringa JSON. Negli esempi precedenti digitare 1semplicemente o Truetrue nel campo del portale.

Domande frequenti

D: È possibile indicizzare le colonne Always Encrypted?

No. Le colonne Always Encrypted non sono attualmente supportate dagli indicizzatori di Ricerca intelligenza artificiale di Azure.

D: Posso usare l'indicizzatore di Azure SQL con i database SQL in esecuzione sulle macchine virtuali IaaS in Azure?

Sì. Tuttavia, è necessario consentire al servizio di ricerca di connettersi al database. Per altre informazioni, vedere Configurare una connessione da un indicizzatore di Ricerca intelligenza artificiale di Azure a SQL Server in una macchina virtuale di Azure.

D: Posso usare l'indicizzatore di Azure SQL con i database SQL in esecuzione locale?

Non direttamente. Non è consigliabile o supportare una connessione diretta, perché in questo modo è necessario aprire i database al traffico Internet. I clienti hanno avuto esito positivo in questo scenario grazie all'uso delle tecnologie bridge come Azure Data Factory. Per altre informazioni, vedere Eseguire il push dei dati in un indice di Ricerca di intelligenza artificiale di Azure usando Azure Data Factory.

D: Posso usare una replica secondaria in un cluster di failover come origine dati?

Dipende. Per l'indicizzazione completa di una tabella o vista, è possibile usare una replica secondaria.

Per l'indicizzazione incrementale, Ricerca di intelligenza artificiale di Azure supporta due criteri di rilevamento delle modifiche: rilevamento delle modifiche integrato di SQL e High Water Mark.

Nelle repliche di sola lettura database SQL non supporta il rilevamento delle modifiche integrato. Pertanto, è necessario usare il criterio del livello più alto.

È consigliabile sempre usare il tipo di dati rowversion per la colonna di livello più alto. Tuttavia, l'uso di rowversion si basa sulla MIN_ACTIVE_ROWVERSION funzione , che non è supportata nelle repliche di sola lettura. Pertanto, è necessario puntare l'indicizzatore a una replica primaria se si usa rowversion.

Se si tenta di usare rowversion in una replica di sola lettura, verrà visualizzato l'errore seguente:

"L'uso di una colonna rowversion per il rilevamento delle modifiche non è supportato nelle repliche di disponibilità secondarie (di sola lettura). Aggiornare l'origine dati e specificare una connessione alla replica di disponibilità primaria. La proprietà 'Updateability' del database corrente è 'READ_ONLY'".

D: Posso usare una colonna diversa, non rowversion, per il rilevamento delle modifiche del livello più alto?

Non è consigliabile. Solo rowversion consente una sincronizzazione dei dati affidabile. Tuttavia, a seconda della logica dell'applicazione, potrebbe essere sicuro:

  • Quando l'indicizzatore viene eseguito, non sono presenti transazioni in sospeso nella tabella indicizzata, ad esempio tutti gli aggiornamenti delle tabelle vengono eseguiti come batch in base a una pianificazione e la pianificazione dell'indicizzatore di Ricerca intelligenza artificiale di Azure è impostata per evitare sovrapposizioni con la pianificazione degli aggiornamenti delle tabelle.

  • Eseguire periodicamente una reindicizzazione completa per prelevare le righe mancanti.