Come indicizzare i dati da Azure SQL in Azure AI Search
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 Azure AI Search.
Questo articolo integra Creare un indicizzatore con informazioni specifiche per Azure SQL. Esso usa le API REST per illustrare un flusso di lavoro a tre passaggi 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 Azure SQL in modo da poter configurare l'indicizzazione incrementale.
Sezione domande frequenti (FAQ) 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 Azure AI Search. Sarà possibile usare il rilevamento delle modifiche integrato di SQL, più semplice da implementare rispetto al limite massimo.
Autorizzazioni di lettura. Azure AI Search supporta l'autenticazione di SQL Server, in cui vengono forniti il nome utente e la password nella 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 Azure SQL includono gli SDK di Azure o Importazione guidata dati nel portale di Azure. Se si usa il portale di Azure, assicurarsi che l'accesso a tutte le reti pubbliche sia abilitato nel firewall di Azure SQL 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 e credenziali e criteri per identificare modifiche nei dati. Un’origine dati è definita come risorsa indipendente affinché possa essere usata da più indicizzatori.
Creare un'origine dati o Creare o aggiornare un'origine dati per impostarne la definizione:
POST https://myservice.search.windows.net/datasources?api-version=2024-07-01 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 }Specificare un nome univoco per l'origine dati che segue le convenzioni di denominazione di Azure AI Search.
Impostare "type" su
"azuresql"(obbligatorio).Impostare "credentials" su una stringa di connessione:
È possibile ottenere una stringa di connessione con accesso completo dal portale di Azure. Usare l'opzione
ADO.NET connection string. Impostare il nome utente e la password.In alternativa, è possibile specificare una stringa di connessione dell'identità gestita 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 Connettersi all'indicizzatore di Database SQL di Azure tramite 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.
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=2024-07-01 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 } ] }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.
Creare altri campi per rendere il contenuto più ricercabile. Per indicazioni, vedere Creare un indice .
Mapping di tipi di dati
| Tipo di dati SQL | Tipi di campo di Azure AI Search | 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 | Azure AI Search non supporta la conversione di tipi decimali Edm.Double, perché in tal caso si perderebbe 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 di versione di riga non possono essere archiviate nell'indice di ricerca, ma possono essere usate per il rilevamento modifiche |
| time, timespan, binary, varbinary, image, xml, geometry, CLR types | Non applicabile | Non supportato |
Configurare ed eseguire l'indicizzatore di Azure SQL
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 della fase di esecuzione.
Creare o aggiornare l'indicizzatore assegnandogli un nome e il riferimento all’origine dati e all'indice di destinazione:
POST https://[service name].search.windows.net/indexers?api-version=2024-07-01 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 }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, su cui è possibile eseguire l'override.
"convertHighWaterMarkToRowVersion" ottimizza i criteri di rilevamento delle modifiche di limite massimo. 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" fa in modo che la query SQL usata dai criteri di limite massimo ometta la clausola ORDER BY. Se si usano i criteri di rilevamento delle modifiche nativi, questo parametro non ha alcun effetto.
Specificare i mapping dei campi se sono presenti differenze nel nome o nel tipo di campo oppure se sono necessarie più versioni di un campo di origine nell'indice di ricerca.
Per ulteriori informazioni su altre proprietà, vedere Creare un indicizzatore.
Un indicizzatore viene eseguito automaticamente al momento della sua creazione. È possibile ovviare a 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 Ottieni stato dell’indicizzatore:
GET https://myservice.search.windows.net/indexers/myindexer/status?api-version=2024-07-01
Content-Type: application/json
api-key: [admin key]
La risposta include lo stato e il numero di elementi elaborati. Dovrebbe risultare 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 esecuzioni completate più recenti in ordine cronologico inverso, in modo che l'esecuzione più recente venga visualizzata per 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 sulla tabella o sulla vista.
Per gli indicizzatori di Azure SQL, 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 della modifica per la tabella
- Nessuna chiave primaria composta, ovvero una chiave primaria che contiene 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=2024-07-01
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 operazioni di eliminazione righe 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
Questi criteri di rilevamento delle modifiche si basano su una colonna di "livello più alto" nella tabella o 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 di livello più alto 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 un qualsiasi altro tipo di dati, il rilevamento delle modifiche potrebbe non garantire l'acquisizione di tutte le modifiche in presenza di transazioni in esecuzione contemporaneamente a 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=2024-07-01
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 limite massimo, le query usate dall'indicizzatore SQL possono scadere. In particolare, la clausola ORDER BY [High Water Mark Column] richiede l'esecuzione efficiente di un indice nel caso in cui la tabella contenga numerose righe.
convertHighWaterMarkToRowVersion
Se si usa un tipo di dati rowversion per la colonna del limite massimo, è consigliabile impostare la proprietà convertHighWaterMarkToRowVersion nella configurazione dell'indicizzatore. L'impostazione di questa proprietà su true comporta i comportamenti seguenti:
Usa il tipo di dati rowversion per la colonna di livello massimo 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 potrebbero avere righe con valori rowversion duplicati. La sottrazione di uno 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 la configurazione dell'indicizzatore queryTimeout su un valore superiore rispetto a quello predefinito di timeout 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, questa operazione è sconsigliata perché, se l'esecuzione dell'indicizzatore è stata interrotta da un errore, l'indicizzatore deve elaborare nuovamente tutte le righe in caso di esecuzione in un secondo momento, anche se l'indicizzatore ha già elaborato quasi tutte le righe nel momento in cui è stata interrotta. 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, Azure AI Search non può dedurre in alcun modo 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 semplicemente 1, True o true nel campo del portale.
Domande frequenti
D: È possibile indicizzare le colonne Always Encrypted?
No, attualmente gli indicizzatori di Azure AI Search non supportano le colonne Always Encrypted.
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 l'articolo Configurare una connessione da un indicizzatore di Azure AI Search a SQL Server in una VM Azure.
D: Posso usare l'indicizzatore di Azure SQL con i database SQL in esecuzione locale?
Non direttamente. La connessione diretta non è consigliata né supportata, in quanto richiederebbe l’apertura dei 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 Push dei dati in un indice di Azure AI Search con 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, Azure AI Search supporta due criteri di rilevamento delle modifiche: il rilevamento delle modifiche integrato di SQL e il livello più alto.
Nelle repliche di sola lettura il 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 funzione MIN_ACTIVE_ROWVERSION, che non è supportata nelle repliche di sola lettura. Pertanto, se si usa rowversion è necessario puntare l'indicizzatore a una replica primaria.
Se si tenta di usare rowversion su una replica di sola lettura, si visualizzerà 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:
Accertarsi che, durante l'esecuzione dell'indicizzatore, non siano presenti transazioni in sospeso nella tabella che si sta indicizzando. Ad esempio, tutti gli aggiornamenti della tabella vengono eseguiti come batch in una pianificazione e la pianificazione dell'indicizzatore di Azure AI Search è impostato per evitare la sovrapposizione con la pianificazione dell'aggiornamento della tabella.
Eseguire periodicamente una reindicizzazione completa per prelevare le righe mancanti.