Indexación de datos de Azure SQL

En este artículo se muestra cómo configurar un indexador de Azure SQL para extraer contenido y hacer que se pueda buscar en Azure Cognitive Search. En este flujo de trabajo se crea un índice de búsqueda en Azure Cognitive Search y se carga con contenido existente extraído de Azure SQL Database y las instancias administradas de Azure SQL.

En este artículo se describe la mecánica del uso de indexadores, pero también se describen las características que solo están disponibles con Azure SQL Database o SQL Managed Instance (por ejemplo, el seguimiento de cambios integrado).

Los indexadores de Azure SQL se pueden configurar mediante cualquiera de estos clientes:

• Azure Portal
• API REST de Azure Cognitive Search
• SDK para .NET de Azure Cognitive Search

En este artículo, se usan las API REST.

Requisitos previos

• Los datos proceden de una sola tabla o vista. Si los datos están distribuidos entre varias tablas, puede crear una única vista de los datos. Un inconveniente del uso de vistas es que no podrá usar la detección de cambios integrada de SQL Server para actualizar un índice con cambios incrementales. Para más información, vea Capturar filas cambiadas y eliminadas a continuación.

• Los tipos de datos deben ser compatibles. En los índices de búsqueda se admiten la mayoría de los tipos de SQL, pero no todos ellos. Para obtener una lista, vea Asignación de tipos de datos.

• Las conexiones a una instancia de SQL Managed Instance deben realizarse a través de un punto de conexión público. Para más información, consulte Conexiones de indexador a través de un punto de conexión público.

• Las conexiones con SQL Server en una máquina virtual de Azure requieren la configuración manual de un certificado de seguridad. Para más información, consulte Conexiones de indexador SQL Server en una máquina virtual de Azure.

La sincronización de datos en tiempo real no debe ser un requisito de la aplicación. Un indexador puede volver a indexar la tabla cada cinco minutos como máximo. Si los datos cambian con frecuencia y esos cambios deben reflejarse en el índice en cuestión de segundos o minutos, se recomienda usar la API REST o el SDK de .NET para insertar directamente las filas actualizadas.

Se permite la indexación incremental. Si tiene un conjunto de datos grande y prevé ejecutar el indexador en una programación, Búsqueda cognitiva de Azure debe ser capaz de identificar de forma eficaz las filas nuevas, cambiadas o eliminadas. Solo se permite la indexación no incremental si se indexa a petición (no en una programación), o se indexan menos de 100 000 filas. Para más información, vea Capturar filas cambiadas y eliminadas a continuación.

Azure Cognitive 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 omitir las credenciales en la conexión. Para más información, consulte Configuración de una conexión de indexador mediante una identidad administrada.

Crear un indexador de Azure SQL

1. Cree el origen de datos:

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

   La cadena de conexión puede seguir cualquiera de los formatos siguientes:

   1. Puede obtener la cadena de conexión de Azure Portal: use la opción ADO.NET connection string.
   2. Una cadena de conexión de identidad administrada que no incluye una clave de cuenta con el siguiente formato: 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 usar esta cadena de conexión, siga las instrucciones de Configuración de una conexión de indexador a una base de datos de Azure SQL Database mediante una identidad administrada.

2. Si aún no tiene un índice de Búsqueda cognitiva de Azure de destino, créelo. Puede crear un índice mediante el portal o la API de creación de índices. Asegúrese de que el esquema del índice de destino sea compatible con el de la tabla de origen; consulte la asignación entre tipos de datos de SQL y tipos de datos de Búsqueda cognitiva de Azure.

3. Cree el indexador asignándole un nombre y haciendo referencia al índice de origen y destino de datos:

    POST https://myservice.search.windows.net/indexers?api-version=2020-06-30
    Content-Type: application/json
    api-key: admin-key
   
    {
        "name" : "myindexer",
        "dataSourceName" : "myazuresqldatasource",
        "targetIndexName" : "target index name"
    }
   

Un indizador creado de esta forma no tiene una programación. Se ejecuta automáticamente una vez en cuanto se crea. Puede volver a ejecutarlo en cualquier momento mediante una solicitud ejecutar indizador :

    POST https://myservice.search.windows.net/indexers/myindexer/run?api-version=2020-06-30
    api-key: admin-key

Puede personalizar varios aspectos del comportamiento del indexador, como el tamaño de lote y el número de documentos que se puede omitir antes de que la ejecución de un indexador produzca un error. Para más información, consulte Create Indexer API(API para crear índices).

Puede que necesite permitir que los servicios de Azure se conecten a la base de datos. Consulte Conectarse desde Azure para obtener instrucciones sobre cómo hacerlo.

Para supervisar el estado del indizador y el historial de ejecución (número de elementos indizados, errores, etc.), use una solicitud estado del indizador :

    GET https://myservice.search.windows.net/indexers/myindexer/status?api-version=2020-06-30
    api-key: admin-key

La respuesta debe ser similar a la siguiente:

    {
        "@odata.context":"https://myservice.search.windows.net/$metadata#Microsoft.Azure.Search.V2015_02_28.IndexerExecutionInfo",
        "status":"running",
        "lastResult": {
            "status":"success",
            "errorMessage":null,
            "startTime":"2015-02-21T00:23:24.957Z",
            "endTime":"2015-02-21T00:36:47.752Z",
            "errors":[],
            "itemsProcessed":1599501,
            "itemsFailed":0,
            "initialTrackingState":null,
            "finalTrackingState":null
        },
        "executionHistory":
        [
            {
                "status":"success",
                "errorMessage":null,
                "startTime":"2015-02-21T00:23:24.957Z",
                "endTime":"2015-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). Puede encontrar información adicional sobre la respuesta en Obtener el estado del indizador

Ejecutar indexadores según una programación

También puede disponer que el indizador se ejecute periódicamente según una programación. Para ello, agregue la propiedad schedule al crear o actualizar el indexador. El ejemplo siguiente muestra una solicitud PUT para actualizar el indizador:

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

    {
        "dataSourceName" : "myazuresqldatasource",
        "targetIndexName" : "target index name",
        "schedule" : { "interval" : "PT10M", "startTime" : "2015-01-01T00:00:00Z" }
    }

El parámetro interval es obligatorio. El intervalo se refiere al tiempo entre el inicio de dos ejecuciones consecutivas de indizador. El intervalo mínimo permitido es de 5 minutos y el máximo de un día. Debe tener el formato de un valor "dayTimeDuration" XSD (subconjunto restringido de un valor de duración ISO 8601 ). El patrón de este es: P(nD)(T(nH)(nM)). Ejemplos: PT15M para cada 15 minutos, PT2H para cada 2 horas.

Para más información sobre cómo definir las programaciones del indexador, consulte Programación de indexadores para Azure Cognitive Search.

Capturar filas nuevas, cambiadas y eliminadas

Búsqueda cognitiva de Azure usa la indexación incremental para evitar tener que volver a indexar la tabla o vista completa cada vez que se ejecuta un indexador. Búsqueda cognitiva de Azure proporciona dos directivas de detección de cambios para admitir la indexación incremental.

Directiva de seguimiento de cambios integrada de SQL

Si la base de datos SQL admite el seguimiento de cambios, se recomienda usar la directiva de seguimiento de cambios integrada de SQL. Esta es la directiva más eficiente. Además, permite que Búsqueda cognitiva de Azure identifique las filas eliminadas sin tener que agregar a la tabla una columna de "eliminación temporal" explícita.

Requisitos

• Requisitos de versión de la 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 (vistas no).
• 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.

Uso

Para usar esta directiva, cree o actualice el origen de datos de la siguiente manera:

    {
        "name" : "myazuresqldatasource",
        "type" : "azuresql",
        "credentials" : { "connectionString" : "connection string" },
        "container" : { "name" : "table or view name" },
        "dataChangeDetectionPolicy" : {
           "@odata.type" : "#Microsoft.Azure.Search.SqlIntegratedChangeTrackingPolicy"
      }
    }

Al usar la directiva de seguimiento de cambios integrada de SQL, no especifique una directiva de detección de eliminación de datos independiente, ya que esta directiva tiene compatibilidad integrada para identificar las filas eliminadas. Sin embargo, para que las eliminaciones se detecten "mágica y automáticamente", la clave de documento del índice de búsqueda debe ser la misma clave principal en la tabla SQL.

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

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].

Uso

Para usar una directiva de marca de límite superior, cree o actualice el origen de datos de la siguiente manera:

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

convertHighWaterMarkToRowVersion

Si usa un tipo de datos rowversion para la columna de marca de límite superior, considere la posibilidad de usar la opción de configuración indexador convertHighWaterMarkToRowVersion. convertHighWaterMarkToRowVersion hace dos cosas:

• 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 una o varias combinaciones pueden tener filas con valores rowversion duplicados. Restar 1 garantiza que la consulta de indexador no pierde estas filas.

Para habilitar esta característica, 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, puede utilizar la configuración de indexador queryTimeout para establecer el tiempo de espera de consulta 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 se recomienda porque si se interrumpe la ejecución del indexador por un error, dicho indexador tiene que volver a procesar todas las filas si se ejecuta más tarde, incluso si ya ha procesado casi todas las filas en el momento en el 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 quitaron físicamente de la tabla, Búsqueda cognitiva de Azure 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. 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.

Asignación entre tipos de datos de SQL y de Búsqueda cognitiva de Azure

Valores de configuración

El indexador de SQL expone varios valores de configuración:

Esta configuración se utiliza en el objeto parameters.configuration en la definición del indexador. Por ejemplo, para establecer el tiempo de espera de consulta en 10 minutos, cree o actualice el indexador con la siguiente configuración:

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

Preguntas más frecuentes

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. Consulte el artículo Configuración de una conexión desde un indexador de Búsqueda cognitiva de Azure a SQL Server en una máquina virtual de Azure para más información.

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, vea Inserción de datos en un índice de Búsqueda cognitiva de Azure mediante el uso de Azure Data Factory.

P: P: ¿Puedo usar un indexador de Azure SQL con bases de datos que no son de SQL Server que se ejecutan en IaaS en Azure?

No. No admitimos esta posibilidad, ya que no hemos probado el indexador con bases de datos que no son de SQL Server.

P: ¿Puedo crear varios indizadores que se ejecuten según una programación?

Sí. Sin embargo, solo puede ejecutarse un indizador en un nodo de cada vez. Si necesita varios indizadores que se ejecuten simultáneamente, considere la posibilidad de escalar verticalmente el servicio de búsqueda a más de una unidad de búsqueda.

P: ¿Afecta la ejecución de un indexador a la carga de trabajo de la consulta?

Sí. El indizador se ejecuta en uno de los nodos del servicio de búsqueda, y los recursos de dicho nodo se reparten entre la indización y la prestación de servicios al tráfico de consultas y otras solicitudes de API. Si al ejecutar cargas de trabajo intensivas de indexación y consulta detecta una frecuencia alta de errores 503 o el aumento de los tiempos de respuesta, considere la posibilidad de escalar verticalmente el servicio de búsqueda.

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, Búsqueda cognitiva de Azure admite dos directivas de detección de cambios: Directiva de seguimiento de cambios integrada de SQL y directiva de detección de cambios 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. Pero según la lógica de 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 Búsqueda cognitiva de Azure 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.