Indexering av data från Azure SQL i Azure AI Search
I den här artikeln får du lära dig hur du konfigurerar en indexerare som importerar innehåll från Azure SQL Database eller en hanterad Azure SQL-instans och gör den sökbar i Azure AI Search.
Den här artikeln kompletterar Skapa en indexerare med information som är specifik för Azure SQL. Den använder REST-API:er för att demonstrera ett arbetsflöde i tre delar som är gemensamt för alla indexerare: skapa en datakälla, skapa ett index, skapa en indexerare.
Den här artikeln innehåller också:
En beskrivning av de principer för ändringsidentifiering som stöds av Azure SQL-indexeraren så att du kan konfigurera inkrementell indexering.
Ett avsnitt med vanliga frågor och svar för svar på frågor om funktionskompatibilitet.
Kommentar
Datasynkronisering i realtid är inte möjligt med en indexerare. En indexerare kan indexera om tabellen högst var femte minut. Om datauppdateringar behöver återspeglas i indexet tidigare rekommenderar vi att du skickar uppdaterade rader direkt.
Förutsättningar
En Azure SQL-databas med data i en enda tabell eller vy, eller en SQL Managed Instance med en offentlig slutpunkt.
Använd en tabell om dina data är stora eller om du behöver inkrementell indexering med hjälp av SQL:s inbyggda funktioner för ändringsidentifiering.
Använd en vy om du behöver konsolidera data från flera tabeller. Stora vyer är inte idealiska för SQL-indexerare. En lösning är att skapa en ny tabell bara för inmatning till ditt Azure AI Search-index. Du kommer att kunna använda SQL-integrerad ändringsspårning, vilket är enklare att implementera än Högvattenmärke.
Läsbehörigheter. Azure AI Search stöder SQL Server-autentisering, där användarnamn och lösenord finns på anslutningssträng. Du kan också konfigurera en hanterad identitet och använda Azure-roller.
Om du vill gå igenom exemplen i den här artikeln behöver du en REST-klient.
Andra metoder för att skapa en Azure SQL-indexerare är Azure SDK:er eller guiden Importera data i Azure-portalen. Om du använder Azure-portalen kontrollerar du att åtkomsten till alla offentliga nätverk är aktiverad i Azure SQL-brandväggen och att klienten har åtkomst via en inkommande regel.
Definiera datakällan
Datakällans definition anger vilka data som ska indexeras, autentiseringsuppgifter och principer för att identifiera ändringar i data. En datakälla definieras som en oberoende resurs så att den kan användas av flera indexerare.
Skapa datakälla eller Uppdatera datakälla för att ange dess definition:
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 }Ange ett unikt namn för datakällan som följer namngivningskonventionerna för Azure AI Search.
Ange "typ" till
"azuresql"(krävs).Ange "autentiseringsuppgifter" till en anslutningssträng:
Du kan få fullständig åtkomst anslutningssträng från Azure-portalen. Använd alternativet
ADO.NET connection string. Ange användarnamn och lösenord.Du kan också ange en hanterad identitet anslutningssträng som inte innehåller databashemligheter med följande format:
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;.
Mer information finns i Anslut till Azure SQL Database-indexeraren med hjälp av en hanterad identitet.
Lägga till sökfält i ett index
I ett sökindex lägger du till fält som motsvarar fälten i SQL-databasen. Kontrollera att sökindexschemat är kompatibelt med källschemat med hjälp av motsvarande datatyper.
Skapa eller uppdatera ett index för att definiera sökfält som lagrar data:
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 } ] }Skapa ett dokumentnyckelfält ("nyckel": sant) som unikt identifierar varje sökdokument. Det här är det enda fält som krävs i ett sökindex. Vanligtvis mappas tabellens primärnyckel till indexnyckelfältet. Dokumentnyckeln måste vara unik och inte null. Värdena kan vara numeriska i källdata, men i ett sökindex är en nyckel alltid en sträng.
Skapa fler fält för att lägga till mer sökbart innehåll. Se Skapa ett index för vägledning.
Mappa datatyper
| SQL-datatyp | Fälttyper för Azure AI Search | Kommentar |
|---|---|---|
| bit | Edm.Boolean, Edm.String | |
| int, smallint, tinyint | Edm.Int32, Edm.Int64, Edm.String | |
| bigint | Edm.Int64, Edm.String | |
| verklig, flyttal | Edm.Double, Edm.String | |
| smallmoney, decimaler för pengar | Edm.String | Azure AI Search stöder inte konvertering av decimaltyper till Edm.Double eftersom det skulle förlora precision |
| char, nchar, varchar, nvarchar | Edm.String Collection(Edm.String) |
En SQL-sträng kan användas för att fylla i ett Collection(Edm.String)-fält om strängen representerar en JSON-matris med strängar: ["red", "white", "blue"] |
| smalldatetime, datetime, datetime2, date, datetimeoffset | Edm.DateTimeOffset, Edm.String | |
| uniqueidentifer | Edm.String | |
| geografi | Edm.GeographyPoint | Endast geografiska instanser av typen POINT med SRID 4326 (som är standard) stöds |
| Rowversion | Inte tillämpligt | Radversionskolumner kan inte lagras i sökindexet, men de kan användas för ändringsspårning |
| time, timespan, binary, varbinary, image, xml, geometry, CLR types | Inte tillämpligt | Stöds inte |
Konfigurera och köra Azure SQL-indexeraren
När indexet och datakällan har skapats är du redo att skapa indexeraren. Indexerarens konfiguration anger indata, parametrar och egenskaper som styr körningstidsbeteenden.
Skapa eller uppdatera en indexerare genom att ge den ett namn och referera till datakällan och målindexet:
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 }Under parametrar innehåller konfigurationsavsnittet parametrar som är specifika för Azure SQL:
Standardvärdet för frågetimeout för SQL-frågekörning är 5 minuter, vilket du kan åsidosätta.
"convertHighWaterMarkToRowVersion" optimerar för principen för ändringsidentifiering med högvattenmärke. Principer för ändringsidentifiering anges i datakällan. Om du använder principen för intern ändringsidentifiering har den här parametern ingen effekt.
"disableOrderByHighWaterMarkColumn" gör att SQL-frågan som används av principen för högvattenmärke utelämnar ORDER BY-satsen. Om du använder principen för intern ändringsidentifiering har den här parametern ingen effekt.
Ange fältmappningar om det finns skillnader i fältnamn eller typ, eller om du behöver flera versioner av ett källfält i sökindexet.
Mer information om andra egenskaper finns i Skapa en indexerare .
En indexerare körs automatiskt när den skapas. Du kan förhindra detta genom att ange "inaktiverad" till true. Om du vill kontrollera indexerarens körning kör du en indexerare på begäran eller sätter den enligt ett schema.
Kontrollera status för indexerare
Om du vill övervaka indexerarens status och körningshistorik skickar du en get indexer-statusbegäran :
GET https://myservice.search.windows.net/indexers/myindexer/status?api-version=2020-06-30
Content-Type: application/json
api-key: [admin key]
Svaret innehåller status och antalet bearbetade objekt. Det bör se ut ungefär som i följande exempel:
{
"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
]
}
Körningshistoriken innehåller upp till 50 av de senast slutförda körningarna, som sorteras i omvänd kronologisk ordning så att den senaste körningen kommer först.
Indexera nya, ändrade och borttagna rader
Om din SQL-databas stöder ändringsspårning kan en sökindexerare bara hämta det nya och uppdaterade innehållet vid efterföljande indexerarekörningar.
Om du vill aktivera inkrementell indexering anger du egenskapen "dataChangeDetectionPolicy" i datakälldefinitionen. Den här egenskapen talar om för indexeraren vilken mekanism för ändringsspårning som används i tabellen eller vyn.
För Azure SQL-indexerare finns det två principer för ändringsidentifiering:
"SqlIntegratedChangeTrackingPolicy" (gäller endast tabeller)
"HighWaterMarkChangeDetectionPolicy" (fungerar för tabeller och vyer)
SQL Integrated Ändringsspårning Policy
Vi rekommenderar att du använder "SqlIntegratedChangeTrackingPolicy" för dess effektivitet och dess förmåga att identifiera borttagna rader.
Databaskrav:
- SQL Server 2012 SP3 och senare, om du använder SQL Server på virtuella Azure-datorer
- Azure SQL Database eller SQL Managed Instance
- Endast tabeller (inga vyer)
- Aktivera ändringsspårning för tabellen i databasen
- Ingen sammansatt primärnyckel (en primärnyckel som innehåller mer än en kolumn) i tabellen
- Inga klustrade index i tabellen. Som en lösning skulle alla klustrade index behöva tas bort och återskapas som icke-illustrerat index, men prestanda kan påverkas i källan jämfört med att ha ett klustrat index
Principer för ändringsidentifiering läggs till i datakällans definitioner. Om du vill använda den här principen skapar eller uppdaterar du datakällan så här:
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"
}
När du använder EN SQL-princip för integrerad ändringsspårning ska du inte ange en separat princip för identifiering av databorttagning. Sql-principen för integrerad ändringsspårning har inbyggt stöd för att identifiera borttagna rader. Men för att de borttagna raderna ska kunna identifieras automatiskt måste dokumentnyckeln i sökindexet vara samma som primärnyckeln i SQL-tabellen.
Kommentar
När du använder TRUNCATE TABLE för att ta bort ett stort antal rader från en SQL-tabell måste indexeraren återställas för att återställa ändringsspårningstillståndet för att hämta radborttagningar.
Princip för ändringsidentifiering av högvattenmärke
Den här principen för ändringsidentifiering förlitar sig på en kolumn med högvattenmärke i tabellen eller vyn som fångar upp den version eller tidpunkt då en rad senast uppdaterades. Om du använder en vy måste du använda en princip för högvattenmärke.
Högvattenmärkeskolumnen måste uppfylla följande krav:
- Alla infogningar anger ett värde för kolumnen.
- Alla uppdateringar av ett objekt ändrar också värdet för kolumnen.
- Värdet för den här kolumnen ökar med varje infogning eller uppdatering.
- Frågor med följande WHERE- och ORDER BY-satser kan köras effektivt:
WHERE [High Water Mark Column] > [Current High Water Mark Value] ORDER BY [High Water Mark Column]
Kommentar
Vi rekommenderar starkt att du använder datatypen rowversion för högvattenmärkeskolumnen. Om någon annan datatyp används garanteras inte ändringsspårning att samla in alla ändringar i närvaro av transaktioner som körs samtidigt med en indexerarfråga. När du använder rowversion i en konfiguration med skrivskyddade repliker måste du peka indexeraren på den primära repliken. Endast en primär replik kan användas för datasynkroniseringsscenarier.
Principer för ändringsidentifiering läggs till i datakällans definitioner. Om du vill använda den här principen skapar eller uppdaterar du datakällan så här:
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]"
}
}
Kommentar
Om källtabellen inte har något index för kolumnen högvattenmärke kan frågor som används av SQL-indexeraren överskrida tidsgränsen. I synnerhet ORDER BY [High Water Mark Column] kräver satsen att ett index körs effektivt när tabellen innehåller många rader.
convertHighWaterMarkToRowVersion
Om du använder en radversionsdatatyp för kolumnen högvattenmärke bör du överväga att ange convertHighWaterMarkToRowVersion egenskapen i indexerarens konfiguration. Om du ställer in den här egenskapen på sant resulterar det i följande beteenden:
Använder datatypen rowversion för kolumnen högvattenmärke i indexerarens SQL-fråga. Om du använder rätt datatyp förbättras indexerarens frågeprestanda.
Subtraherar en från värdet för rowversion innan indexerarfrågan körs. Vyer med en-till-många-kopplingar kan ha rader med dubbla radversionsvärden. Genom att subtrahera en ser du till att indexerarfrågan inte missar dessa rader.
Om du vill aktivera den här egenskapen skapar eller uppdaterar du indexeraren med följande konfiguration:
{
... other indexer definition properties
"parameters" : {
"configuration" : { "convertHighWaterMarkToRowVersion" : true } }
}
queryTimeout
Om du får timeout-fel anger du queryTimeout indexerarens konfigurationsinställning till ett värde som är högre än standardvärdet på 5 minuters timeout. Om du till exempel vill ange tidsgränsen till 10 minuter skapar eller uppdaterar du indexeraren med följande konfiguration:
{
... other indexer definition properties
"parameters" : {
"configuration" : { "queryTimeout" : "00:10:00" } }
}
disableOrderByHighWaterMarkColumn
Du kan också inaktivera ORDER BY [High Water Mark Column] satsen. Detta rekommenderas dock inte eftersom om indexerarens körning avbryts av ett fel måste indexeraren bearbeta alla rader igen om den körs senare, även om indexeraren redan har bearbetat nästan alla rader vid den tidpunkt då den avbröts. Om du vill inaktivera ORDER BY -satsen använder du disableOrderByHighWaterMarkColumn inställningen i indexerardefinitionen:
{
... other indexer definition properties
"parameters" : {
"configuration" : { "disableOrderByHighWaterMarkColumn" : true } }
}
Princip för identifiering av borttagning av mjuk borttagningskolumn
När rader tas bort från källtabellen vill du förmodligen även ta bort dessa rader från sökindexet. Om du använder sql-principen för integrerad ändringsspårning tas detta hand om åt dig. Principen för ändringsspårning med högvattenmärke hjälper dig dock inte med borttagna rader. Vad bör jag göra?
Om raderna tas bort fysiskt från tabellen kan Azure AI Search inte härleda förekomsten av poster som inte längre finns. Du kan dock använda metoden "mjuk borttagning" för att logiskt ta bort rader utan att ta bort dem från tabellen. Lägg till en kolumn i tabellen eller vyn och markera rader som borttagna med den kolumnen.
När du använder metoden för mjuk borttagning kan du ange principen för mjuk borttagning på följande sätt när du skapar eller uppdaterar datakällan:
{
…,
"dataDeletionDetectionPolicy" : {
"@odata.type" : "#Microsoft.Azure.Search.SoftDeleteColumnDeletionDetectionPolicy",
"softDeleteColumnName" : "[a column name]",
"softDeleteMarkerValue" : "[the value that indicates that a row is deleted]"
}
}
SoftDeleteMarkerValue måste vara en sträng i JSON-representationen av datakällan. Använd strängrepresentationen av ditt faktiska värde. Om du till exempel har en heltalskolumn där borttagna rader har markerats med värdet 1 använder du "1". Om du har en BIT-kolumn där borttagna rader har markerats med det booleska true-värdet använder du strängliteralen "True" eller "true", så spelar det ingen roll.
Om du konfigurerar en princip för mjuk borttagning från Azure-portalen ska du inte lägga till citattecken runt markörvärdet för mjuk borttagning. Fältinnehållet tolkas redan som en sträng och kommer automatiskt att översättas till en JSON-sträng åt dig. I exemplen ovan skriver 1du bara , True eller true i portalens fält.
Vanliga frågor
F: Kan jag indexeras Always Encrypted-kolumner?
Nej. Always Encrypted-kolumner stöds för närvarande inte av Azure AI Search-indexerare.
F: Kan jag använda Azure SQL-indexerare med SQL-databaser som körs på virtuella IaaS-datorer i Azure?
Ja. Du måste dock tillåta att söktjänsten ansluter till databasen. Mer information finns i Konfigurera en anslutning från en Azure AI Search-indexerare till SQL Server på en virtuell Azure-dator.
F: Kan jag använda Azure SQL-indexerare med SQL-databaser som körs lokalt?
Inte direkt. Vi rekommenderar eller stöder inte en direkt anslutning, eftersom det skulle kräva att du öppnar dina databaser för Internettrafik. Kunderna har lyckats med det här scenariot med hjälp av bryggtekniker som Azure Data Factory. Mer information finns i Skicka data till ett Azure AI Search-index med Azure Data Factory.
F: Kan jag använda en sekundär replik i ett redundanskluster som datakälla?
Det beror på. För fullständig indexering av en tabell eller vy kan du använda en sekundär replik.
För inkrementell indexering stöder Azure AI Search två principer för ändringsidentifiering: SQL-integrerad ändringsspårning och högvattenmärke.
På skrivskyddade repliker stöder SQL Database inte integrerad ändringsspårning. Därför måste du använda principen för högvattenmärke.
Vår standardrekommendering är att använda datatypen rowversion för högvattenmärkeskolumnen. Men om du använder rowversion används MIN_ACTIVE_ROWVERSION funktionen, som inte stöds på skrivskyddade repliker. Därför måste du peka indexeraren på en primär replik om du använder rowversion.
Om du försöker använda rowversion på en skrivskyddad replik visas följande fel:
"Användning av en radversionskolumn för ändringsspårning stöds inte på sekundära (skrivskyddade) tillgänglighetsrepliker. Uppdatera datakällan och ange en anslutning till den primära tillgänglighetsrepliken. Den aktuella databasegenskapen "Uppdateringsbarhet" är "READ_ONLY".
F: Kan jag använda en alternativ, icke-rowversionskolumn för ändringsspårning med högvattenmärke?
Det rekommenderas inte. Endast rowversion tillåter tillförlitlig datasynkronisering. Beroende på programlogik kan det dock vara säkert om:
Du kan se till att det inte finns några utestående transaktioner i tabellen som indexeras när indexeraren körs (till exempel sker alla tabelluppdateringar som en batch enligt ett schema och azure AI Search-indexerarens schema anges för att undvika överlappning med tabelluppdateringsschemat).
Du gör regelbundet en fullständig omindex för att hämta eventuella missade rader.