Azure SQL Database to Search Sync

Sincronización de Azure SQL Database a Azure Search usando indexadores

Introducción

Si usamos Azure SQL Database para almacenar los datos de nuestra aplicación, y queremos habilitar la búsqueda de texto completo, mediante un servicio de Azure Search, la mejor opción es utilizar indexadores, que hagan el trabajo de sincronización de nuestros datos hacia el servicio de búsqueda. Actualmente esta funcionalidad no está disponible en el SDK de Azure Search, así que deberemos usar la API.

Actualmente los indexadores sólo funcionan con Azure SQL Database, SQL Server sobre VMs en Azure y DocumentDB. (Si están interesados en sincronizar los datos desde DocumentDB, pueden ver este post).

Conceptos

Azure Search soporta la creación y administración de orígenes de datos (data sources) e indexadores (indexers) que operan contra estos orígenes de datos.

Un origen de datos especifica qué datos deben ser indexados, las credenciales para acceder a estos datos, y ls políticas para que Azure Search identifique eficientemente los cambios en los datos. Un origen de datos se define de forma independiente para poder ser usado con más de un indexador.

Un idexador describe como fluyen los datos desde un origen de datos hacia el índice de búsqueda. Debería considerarse la creación de un idexador por cada combinación de índice destino y origen de datos. Se pueden tener múltiples indexadores escribiendo en un mismo índice, pero un indexador puede escribir solamente en un índice. Un indexador se usa para:

  • Ejecutar una copia de única vez para poblar el índice.
  • Sincronizar un índice con los cambios en el origen de datos en un horario programado. Este horario es parte de la definición del indexador.
  • Invocar actualizaciones del índice bajo demanda cuando sea necesario.

Cuando usar un indexador de Azure SQL Database

Dependiendo de varios factores relacionados con tus datos, un idexador puede o no ser apropiado. Si tus datos cumplen con estos requisitos, entonces es posible usar un indexador de Azure SQL Database:

  • Todos los datos vienen de una misma tabla o vista.
    • Si los datos están desparramados por varias tablas, podemos crear una vista y usar ésta con el indexador. Sin embargo, hay que tener en cuenta que de esta manera no tendremos disponible la detección de cambios integrada en SQL Server.
  • Los tipos de datos usados están soportados por el indexador. La mayoría de los datos de SQL están soportados, pero no todos. El detalle de las equivalencias se encuentra más adelante.
  • No se necesita una actualización en tiempo real del índice cuando se cambia una fila.
    • El indexador puede reindexar la tabla como mucho cada 5 minutos. Si los datos cambian mucho, o se requiere tener reflejados los cambios en cuestión de segundos o minutos, entonces deberá usarse la API de Azure Search de forma directa.
  • Si se tiene un gran conjunto de datos y se planea correr el indexador de forma programada, el schema permite identificar eficientemente las filas cambiadas (o eliminadas). El detalle de como hacerlo se encuentra más adelante.
  • El tamaño de los campos de una fila a indexar no excede el máximo permitido para un request de Azure Search, que son 16 MB.

Paso 1: Crear el origen de datos

Para crear el origen de datos, tenemos que hacer un HTTP POST con los siguientes headers:

El valor de api-version es obligatorio, y debe ser versión 2015-02-28 o superior.

El body del request contiene la definición del origen de datos, que debe incluir los siguientes campos:

  • name: el nombre del origen de datos (debe ser en minúsculas).
  • type: usar azuresql.
  • credentials:
    • connectionString: Requerido. Especificar la información de conexión a la base de datos de Azure SQL Database con el siguiente formato (se puede obtener del portal, eligiendo la cadena de conexión ADO.NET): Server=tcp:<tu servidor>.database.windows.net,1433;Database=<tu base de datos>;User ID=<tu nombre de usuario>;Password=<tu contraseña>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;
  • container:
    • name: Requerido. Nombre de la tabla o vista a indexar.
  • dataChangeDetectionPolicy: Opcional. Explicación a continuación.
  • dataDeletionDetectionPolicy: Opcional. Explicación a continuación.

Ejemplo del body del request:

Capturando los documentos modificados

Si nuestra base de datos soporta el Seguimiento de cambios, es recomendable usar la política de seguimiento de cambios de SQL. Esta política habilita el seguimiento de cambios más eficiente, y también le permite a Azure Search identificar filas borradas, sin la necesidad de tener una columna de borrado lógico (soft delete) en nuestra tabla.

El seguimiento de cambios integrado está disponible desde las versiones:

  • SQL Server 2008 R2 o superior, si se usa SQL Server en una VM de Azure.
  • Azure SQL Database V12, si se usa Azure SQL Database.

Al usar la política de seguimiento de cambios integrada de SQL, no hay que especificar una política de borrado de datos – esta política ya posee el soporte para detectar filas borradas.

Esta política sólo puede ser usada con tablas, no con vistas. Se debe habilitar el seguimiento de cambios antes de poder usar esta política. Para habilitarlo en una tabla, ejecutar el siguiente código:

Para usar esta política deberemos crear (o actualizar) nuestro origen de datos como:

Política de cambios High Water Mark

Mientras que el seguimiento de cambios integrado es algo recomendado, no puede ser usado sobre una vista, o si usamos una versión antigua de Azure SQL Database. En estos casos, se debe considerar el uso de la política de High Water Mark. Esta política puede usarse si la tabla posee una columna que cumpla con las siquientes características:

  • Todas las inserciones especifican un valor para la columna.
  • Todas las actualizaciones a un item también cambian el valor de la columna.
  • El valor de la columna aumenta con cada cambio.
  • Las consultas que usan una cláusula WHERE similar a WHERE [Columna High Water Mark] > [Valor actual de High Water Mark] pueden ser ejecutadas eficientemente.

Por ejemplo, una columna rowversion indexada es un candidato ideal para ser la columna high water mark. Para usar esta política, creamos nuestro origen de datos de forma similar a:

Política de de detección de filas borradas con una columna de borrado lógico (Soft Delete)

Cuando las filas son borradas de una tabla, probablemente también querramos que esos datos se eliminen del índice de búsqueda. Si se usa el seguimiento de cambios integrado, esta tarea se realiza automáticamente. Sin embargo, la política de cambios High water mark no nos ayuda en este caso. ¿Qué hacemos?

Si las filas son removidas físicamente de la base de datos, no tendremos suerte – no hay manera de inferir la presencia de registros que ya no existen. Sin embargo, podemos usar un borrado lógico (soft delete), marcando en una columna las filas que fueron borradas.

Para agregar esta política a nuestra definición, haremos algo como lo siguiente:

Paso 2: Crear un índice

Si todavía no tenemos un índice en nuestro servicio de Azure Search, deberemos crear uno. Podemos crearlo desde el nuevo portal de Azure, o vía API, con un POST HTTP:

Mapeo entre los tipos de datos de SQL y los tipos de datos de Azure Search:

 Tipo de dato SQL  Tipo de campo compatible en el índice destino 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, money, decimal, numeric  Edm.String Azure Search no soporta la conversión de tipos decimal a Edm.Double porque se perdería precisión.
 char, nchar, varchar, nvarchar  Edm.String
 Vectores de datos primitivos (EJ.: [“a”, “b”, “c”])  Collection(Edm.String)
 smalldatetime, datetime, datetime2, date, datetimeoffset  Edm.DateTimeOffset, Edm.String
 uniqueidentifer  Edm.String
 rowversion  No soportado Las columnas Row-version no pueden ser almacenadas en el índice, pero pueden usarse para seguimiento de cambios.
 time, timespan, binary, varbinary, image, xml, geometry, geography, tipos CLR  No soportados

Ejemplo del body del Request

Response

Si el índice se creó correctamente, recibiremos un response HTTP 201 Created.

Paso 3: Crear el indexador

Podemos crear un nuevo indexador haciendo un POST HTTP con los siguientes headers:

En el body del request deberemos incluir la definición del indexador, que deberá incluir:

  • name: Requerido. El nombre del indexador.
  • dataSourceName: Requerido. El nombre del origen de datos.
  • targetIndexName: Requerido. El nombre del índice destino.
  • schedule: Opcional. Veremos esto a continuación.

Correr un indexador en forma recurrente

Si lo deseamos, podemos hacer que nuestro indexador se ejecute recurrentemente cada cierto tiempo, usando el campo schedule, junto con los siguientes datos:

  • interval: Requerido. Un valor de tiempo que indica cada cuando correrá el indexador. El menor intervalo permitido es de 5 minutos, y el más largo de 1 día. Debe estar formateado como un valor XSD “dayTimeDuration” (un subconjunto restringido del valor de duración ISO 8601). El patrón para esto es: P[nD][T[nH][nM]]. Por ejemplo: PT15M para ejecutar cada 15 minutos, PT2H para ejecutar cada 2 horas.
  • startTime: Requerido. Una especificación de fecha y hora UTC que especifica cuándo debería comenzar a ejecutarse el indexador.

Ejemplo del body del Request

El ejemplo crea un indexador que copia los datos del origen de datos creado en el Paso 1, al índice creado en el Paso 2, comenzando el 1 de abril de 2015, y corriendo una vez por hora.

Response

Si el indexador fue creado correctamente recibiremos un response HTTP 201 Created.

Paso 4: Correr el indexador

Aparte de poder correr periódicamente, a horarios programados, un indexador puede ser invocado bajo demanda a través de un POST HTTP:

Response

Si se invocó exitosamente el indexador, recibiremos un response HTTP 202 Accepted.

Paso 5: Obtener el estado del indexador

Con un GET HTTP podemos conocer el estado actual y el hostorial de ejecución de un indexador:

Response

Recibiremos un response HTTP 200 OK, que en el body contiene el dato del estado del indexador, el resultado de la última corrida, y el historial de las invocaciones recientes (si existieran).

El response debería verse similar a lo siguiente:

El historial de ejecución contiene las 50 ejecuciones completadas más recientes, en orden cronológico inverso.

Espero que les haya sido útil.

@gjbellmann

Más información

Si quieren aprender más sobre Azure Search, pueden ver una Introducción a Azure Search y Usando Azure Search desde .NET.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s