Indexación de datos de Azure SQL en Azure AI Search
En este artículo, aprenderá a configurar un indexador que importa contenido de Azure SQL Database o Azure SQL Managed Instance y hace que se puedan realizar búsquedas en él en Azure AI Search.
En este artículo se complementa la creación de un indexador con información específica de Azure SQL. Usa las API REST para mostrar un flujo de trabajo de tres partes común a todos los indexadores: crear un origen de datos, crear un índice y crear un indexador.
En este artículo también aparece:
Una descripción de las directivas de detección de cambios admitidas por el indexador Azure SQL para que pueda configurar la indexación incremental.
Una sección de preguntas más frecuentes (FAQ) para obtener respuestas a preguntas sobre la compatibilidad de características.
Nota:
La sincronización de datos en tiempo real no es posible con un indexador. Un indexador puede volver a indexar la tabla cada cinco minutos como máximo. Si las actualizaciones de datos deben reflejarse en el índice antes, se recomienda insertar filas actualizadas directamente.
Requisitos previos
Una base de datos de Azure SQL con datos en una sola tabla o vista, o bien una instancia de SQL Managed Instance con un punto de conexión público.
Use una tabla si los datos son grandes o si necesita indexación incrementalmediante las funcionalidades de detección de cambios nativas de SQL.
Use una vista si necesita consolidar datos de varias tablas. Las vistas grandes no son ideales para el indexador de SQL. Una solución alternativa consiste en crear una nueva tabla solo para la ingesta en el índice de Azure AI Search. Así podrá usar el seguimiento de cambios integrado de SQL, que es más fácil de implementar que la marca de límite superior.
Permisos de lectura. Azure AI Search admite la autenticación de SQL Server, donde el nombre de usuario y la contraseña se proporcionan en la cadena de conexión. Como alternativa, puede configurar una identidad administrada y usar roles de Azure.
Para solucionar los ejemplos de este artículo, necesita un cliente REST.
Otros enfoques para crear un indexador de Azure SQL son SDK de Azure o asistente para importar datos en el Azure Portal. Si usa Azure Portal, asegúrese de que el acceso a todas las redes públicas está habilitado en el firewall de Azure SQL y de que el cliente tiene acceso a través de una regla de entrada.
Definición del origen de datos
La definición del origen de datos especifica los datos que se indexan, las credenciales y las directivas para identificar los cambios en los datos. Un origen de datos se define como un recurso independiente de forma que puedan usarlo varios indexadores.
Cree un origen de datos o Cree o actualice un origen de datos para establecer su definición:
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 }Especifique un nombre único para el origen de datos que respete las convenciones de nomenclatura de Azure AI Search.
Establezca "type" en
"azuresql"(obligatorio).Establezca "credentials" en una cadena de conexión:
Puede obtener la cadena de conexión de acceso completo en Azure Portal. Use la opción
ADO.NET connection string. Escriba un nombre de usuario y una contraseña.De forma alternativa, puede especificar una cadena de conexión de identidad administrada que no incluya secretos de base de datos con el formato siguiente:
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;.
Para más información, consulte Conexión al indexador de base de datos de Azure SQL mediante una identidad administrada.
Adición de campos de búsqueda a un índice
En un índice de búsqueda, agregue campos que se correspondan a los campos de la base de datos de SQL. Asegúrese de que el esquema de índice de búsqueda es compatible con el esquema de origen mediante el uso de tipos de datos equivalentes.
Cree o actualice un índice para definir campos de búsqueda que almacenarán los datos:
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 } ] }Cree un campo de clave de documento ("clave": true) que identifique de forma única cada documento de búsqueda. Este es el único campo necesario en un índice de búsqueda. Normalmente, la clave principal de la tabla se asigna al campo de clave de índice. La clave del documento debe ser única y no null. Los valores pueden ser numéricos en los datos de origen, pero en un índice de búsqueda, una clave siempre es una cadena.
Cree más campos para agregar contenido que se pueda buscar mejor. Consulte Creación de un índice para más información al respecto.
Asignación de tipos de datos
| tipo de datos de SQL | Tipos de campos de Azure AI Search | Notas |
|---|---|---|
| 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, numérico decimal de dinero | Edm.String | Azure AI Search no admite la conversión de tipos decimales en Edm.Double, ya que se podría perder precisión |
| char, nchar, varchar, nvarchar | Edm.String Collection(Edm.String) |
Una cadena SQL se puede usar para rellenar un campo Collection(Edm.String) si la cadena representa una matriz JSON de cadenas: ["red", "white", "blue"] |
| smalldatetime, datetime, datetime2, date, datetimeoffset | Edm.DateTimeOffset, Edm.String | |
| uniqueidentifer | Edm.String | |
| geography | Edm.GeographyPoint | Solo se admiten instancias de geography de tipo POINT con SRID 4326 (que es el valor predeterminado) |
| rowversion | No aplicable | Las columnas de versión de la fila no se pueden almacenar en el índice de búsqueda, pero pueden usarse para el seguimiento de cambios |
| time, timespan, binary, varbinary, image, xml, geometry, tipos CLR | No aplicable | No compatible |
Configuración y ejecución del indexador de Azure SQL
Una vez creados el índice y el origen de datos, ya podrá crear el indexador. La configuración del indexador especifica las entradas, los parámetros y las propiedades que controlan los comportamientos en tiempo de ejecución.
Cree o actualice un indexador asignándole un nombre y haciendo referencia al origen de datos y al índice de destino:
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 }En parámetros, la sección de configuración tiene parámetros específicos de Azure SQL:
El tiempo de espera de consulta predeterminado para la ejecución de consultas SQL es de 5 minutos, que puede invalidar.
"convertHighWaterMarkToRowVersion" optimiza la directiva de detección de cambios en la marca de agua superior. Las directivas de detección de cambios se establecen en el origen de datos. Si usa la directiva de detección de cambios nativa, este parámetro no tiene ningún efecto.
"disableOrderByHighWaterMarkColumn" hace que la consulta SQL utilizada por la directiva de detección de cambios de marca de agua superior omita la cláusula ORDER BY (ordenar por). Si usa la directiva de detección de cambios nativa, este parámetro no tiene ningún efecto.
Especifique asignaciones de campos si hay diferencias en el nombre o el tipo de campo, o si necesita varias versiones de un campo de origen en el índice de búsqueda.
Consulte Creación de un indexador para más información sobre otras propiedades.
Un indexador se ejecuta automáticamente cuando se crea. Puede evitarlo estableciendo el valor de "disabled" en true. Para controlar la ejecución del indexador, ejecute un indexador a petición o prográmelo.
Comprobación del estado del indexador
Para supervisar el estado del indexador y el historial de ejecución, envíe una solicitud para Obtener el estado del indexador:
GET https://myservice.search.windows.net/indexers/myindexer/status?api-version=2024-07-01
Content-Type: application/json
api-key: [admin key]
La respuesta incluye el estado y el número de elementos procesados. Debe tener un aspecto similar al siguiente ejemplo:
{
"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
]
}
El historial de ejecución contiene como máximo las 50 ejecuciones completadas más recientemente en orden cronológico inverso (la ejecución más reciente aparece en primer lugar).
Indexación de filas nuevas, cambiadas y eliminadas
Si la base de datos SQL admite el seguimiento de cambios, un indexador de búsqueda puede seleccionar solo el contenido nuevo y actualizado en las ejecuciones posteriores del indexador.
Para habilitar la indexación incremental, establezca la propiedad "dataChangeDetectionPolicy" en la definición del origen de datos. Esta propiedad indica al indexador qué mecanismo de seguimiento de cambios se usa en la tabla o vista.
Para los indexadores de Azure SQL, hay dos directivas de detección de cambios:
"SqlIntegratedChangeTrackingPolicy" (solo se aplica a tablas)
"HighWaterMarkChangeDetectionPolicy" (funciona para tablas y vistas)
Directiva de seguimiento de cambios integrada de SQL
Se recomienda usar "SqlIntegratedChangeTrackingPolicy" por su eficacia y su capacidad para identificar las filas eliminadas.
Requisitos de base de datos:
- SQL Server 2012 SP3 y versiones posteriores, si usa SQL Server en máquinas virtuales de Azure
- Azure SQL Database o SQL Managed Instance
- Solo tablas (sin vistas)
- En la base de datos, habilite el seguimiento de los cambios para la tabla.
- Ninguna clave principal compuesta (una clave principal que contiene más de una columna) en la tabla.
- No hay índices agrupados en la tabla. Como solución, cualquier índice agrupado tendría que eliminarse y volver a crearse como índice no agrupado; sin embargo, el rendimiento podría verse afectado en el origen en comparación con un índice agrupado
Las directivas de detección de cambios se agregan a las definiciones de orígenes de datos. Para usar esta directiva, cree o actualice el origen de datos de la siguiente manera:
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"
}
Al usar una directiva de seguimiento de cambios integrada de SQL, no especifique una directiva de detección de eliminación de datos independiente. La directiva de seguimiento de cambios integrada de SQL tiene compatibilidad integrada para identificar las filas eliminadas. Sin embargo, para que las filas eliminadas se detecten automáticamente, la clave de documento del índice de búsqueda debe ser la misma clave principal en la tabla SQL.
Nota:
Cuando se usa TRUNCATE TABLE para quitar un gran número de filas de una tabla SQL, debe restablecer el indexador para que se restablezca Seguimiento de cambios y recopile las eliminaciones de filas.
Directiva de detección de cambios de límite superior
Esta directiva de detección de cambios se basa en una columna de "marca de límite superior" de la tabla o vista que captura la versión o la hora en que se actualizó por última vez una fila. Si usa una vista, debe usar una directiva de marca de límite superior.
La columna de marca de límite superior debe cumplir los siguientes requisitos:
- Todas las inserciones especifican un valor para la columna.
- Todas las actualizaciones de un elemento también cambian el valor de la columna.
- El valor de esta columna aumenta con cada inserción o actualización.
- Las consultas con las cláusulas WHERE y ORDER BY siguientes se pueden ejecutar de forma eficaz:
WHERE [High Water Mark Column] > [Current High Water Mark Value] ORDER BY [High Water Mark Column].
Nota:
Se recomienda encarecidamente usar el tipo de datos rowversion para la columna de marca de límite superior. Si se utiliza cualquier otro tipo de datos, no se garantiza que el seguimiento de cambios capture todos los cambios en el caso de transacciones que se ejecutan simultáneamente con una consulta de indexador. Cuando se usa rowversion en una configuración con réplicas de solo lectura, debe señalar el indexador a la réplica principal. Solo se puede usar una réplica principal para escenarios de sincronización de datos.
Las directivas de detección de cambios se agregan a las definiciones de orígenes de datos. Para usar esta directiva, cree o actualice el origen de datos de la siguiente manera:
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:
Si la tabla de origen no tiene un índice en la columna de marca de agua superior, las consultas utilizadas por el indexador SQL pueden agotar el tiempo de espera. En concreto, la cláusula ORDER BY [High Water Mark Column] requiere un índice para ejecutarse de forma eficaz cuando la tabla contiene muchas filas.
convertHighWaterMarkToRowVersion
Si usa un tipo de datos rowversion para la columna de marca de límite superior, considere la posibilidad de configurar la propiedad convertHighWaterMarkToRowVersion en la configuración indexador. Si se establece esta propiedad en true, se produce el comportamiento siguiente:
Utilice el tipo de datos rowversion para la columna de marca de límite superior en la consulta SQL del indexador. El uso del tipo de datos correcto mejora el rendimiento de las consultas de indexador.
Reste 1 del valor rowversion antes de que se ejecute la consulta de indexador. Las vistas con uniones de uno a muchos pueden tener filas con valores de la versión de fila duplicadas. Restar 1 garantiza que la consulta de indexador no pierde estas filas.
Para habilitar esta propiedad, cree o actualice el indexador con la siguiente configuración:
{
... other indexer definition properties
"parameters" : {
"configuration" : { "convertHighWaterMarkToRowVersion" : true } }
}
queryTimeout
Si se producen errores de tiempo de espera, establezca la opción de configuración del indexador queryTimeout en un valor mayor que el tiempo de espera predeterminado de 5 minutos. Por ejemplo, para establecer el tiempo de espera en 10 minutos, cree o actualice el indexador con la siguiente configuración:
{
... other indexer definition properties
"parameters" : {
"configuration" : { "queryTimeout" : "00:10:00" } }
}
disableOrderByHighWaterMarkColumn
También puede deshabilitar la cláusula ORDER BY [High Water Mark Column]. Sin embargo, esto no es recomendable porque si la ejecución del indizador se interrumpe por un error, el indizador tiene que volver a procesar todas las filas si se ejecuta más tarde, incluso si el indizador ya ha procesado casi todas las filas en el momento en que se interrumpió. Para deshabilitar la cláusula ORDER BY, utilice la configuración disableOrderByHighWaterMarkColumn en la definición del indexador:
{
... other indexer definition properties
"parameters" : {
"configuration" : { "disableOrderByHighWaterMarkColumn" : true } }
}
Directiva de detección de eliminación de columna de eliminación temporal
Cuando se eliminan filas de la tabla de origen, probablemente le interesará eliminar dichas filas del índice de búsqueda. Si usa la directiva de seguimiento de cambios integrada de SQL, esto se lleva a cabo automáticamente. Sin embargo, la directiva el seguimiento de cambios de marca de límite superior no le ayuda con las filas eliminadas. ¿Qué debe hacer?
Si las filas se quitan físicamente de la tabla, Azure AI Search no tiene forma de deducir la presencia de registros que ya no existen. Sin embargo, puede utilizar la técnica de "eliminación temporal" para eliminar filas lógicamente sin quitarlas de la tabla. Agregue una columna a la tabla o vista y marque filas como eliminadas mediante esa columna.
Cuando use la técnica de la eliminación temporal, puede especificar la directiva de eliminación temporal de la manera que se indica a continuación al crear o actualizar el origen de datos:
{
…,
"dataDeletionDetectionPolicy" : {
"@odata.type" : "#Microsoft.Azure.Search.SoftDeleteColumnDeletionDetectionPolicy",
"softDeleteColumnName" : "[a column name]",
"softDeleteMarkerValue" : "[the value that indicates that a row is deleted]"
}
}
softDeleteMarkerValue debe ser una cadena en la representación JSON de su origen de datos. Use la representación de cadena del valor real. Por ejemplo, si tiene una columna de enteros donde las filas eliminadas se marcan con el valor 1, use "1". Si tiene una columna BIT donde las filas eliminadas se marcan con el valor booleano true, use el literal de cadena "True" o "true", no importan las mayúsculas y minúsculas.
Si va a configurar una directiva de eliminación temporal del Azure Portal, no agregue comillas alrededor del valor del marcador de eliminación temporal. El contenido del campo ya se entiende como una cadena y se traducirá automáticamente en una cadena JSON. En los ejemplos anteriores, simplemente escriba 1, True o true en el campo del portal.
Preguntas más frecuentes
P: ¿Puedo indexar columnas Always Encrypted?
No, los indexadores de Azure AI Search no admiten actualmente columnas Always Encrypted.
P: ¿Puedo usar un indizador de SQL Azure con Bases de datos SQL que se ejecutan en máquinas virtuales de IaaS en Azure?
Sí. Sin embargo, debe permitir que el servicio de búsqueda se conecte a su base de datos. Para más información, consulte el artículo Configuración de una conexión desde un indexador de Azure AI Search a SQL Server en una máquina virtual de Azure.
P: ¿Puedo usar un indizador de SQL Azure con Bases de datos SQL que se ejecutan localmente?
No directamente. No se recomienda ni se admite una conexión directa, ya que requeriría abrir las bases de datos al tráfico de Internet. Los clientes han realizado correctamente este escenario mediante tecnologías de puente como Azure Data Factory. Para más información, consulte Inserción de datos en un índice de Azure AI Search mediante el uso de Azure Data Factory.
P: ¿Puedo usar una réplica secundaria en un clúster de conmutación por error como un origen de datos?
Depende. Para la indexación completa de una tabla o vista, puede usar una réplica secundaria.
Para la indexación incremental, Azure AI Search admite dos directivas de detección de cambios: seguimiento de cambios integrado de SQL y marca de límite superior.
En las réplicas de solo lectura, la SQL Database no admite el seguimiento de cambios integrado. Por tanto, debe usar la directiva Marca de límite superior.
La recomendación estándar es usar el tipo de datos rowversion para la columna de marca de límite superior. Pero el uso de rowversion se basa en la función MIN_ACTIVE_ROWVERSION, que no se admite en las réplicas de solo lectura. Por tanto, si usa rowversion debe apuntar el indexador a una réplica principal.
Si intenta usar rowversion en una réplica de solo lectura, verá el siguiente error:
"No se admite el uso de una columna rowversion para el seguimiento de cambios en réplicas de disponibilidad secundarias (de solo lectura). Actualice el origen de datos y especifique una conexión con la réplica de disponibilidad principal. La propiedad actual de "Updateability" de la base de datos es "READ_ONLY"".
P: ¿Puedo usar una columna alternativa, que no sea rowversion, para el seguimiento de los cambios de marca de límite superior?
No se recomienda. Solo rowversion permite la sincronización de datos confiable. Sin embargo, dependiendo de la lógica de su aplicación, puede ser seguro si:
Se puede asegurar de que, cuando se ejecuta el indexador, no hay ninguna transacción pendiente en la tabla que se indexa (por ejemplo, todas las actualizaciones de tabla tienen lugar como un lote según una programación y la programación del indexador de Azure AI Search está establecida para evitar la superposición con la programación de las actualizaciones de tabla).
Vuelve a indexar periódicamente para seleccionar las filas que faltan.