Usar los Predicados CONTAINS y FREETEXT

SQL Server soporta dos predicados muy poderosos para limitar el conjunto resultado de una consulta, por utilizar índices de texto completo. Estos dos predicados son los predicados CONTAINS y FREETEXT. Ambos soportan varios términos de búsqueda. Además de estos dos predicados, SQL Server soporta dos funciones de valores de tabla, para búsquedas de texto completo y tres funciones de valores de tabla, para búsquedas semánticas. Aprenderá acerca de los dos predicados en esta lección y sobre las cinco funciones en la próxima lección.

El Predicado CONTAINS

Con el predicado CONTAINS, puede buscar lo siguiente:

·         Palabras y frases en el texto

·         Coincidencias exactas o aproximadas

·         Formas inflexionales de una palabra

·         Texto en el cual una palabra de búsqueda está cerca de otra palabra de búsqueda

·         Sinónimos de una palabra buscada

·         Un prefijo de una palabra o una frase solamente

También puede agregar pesos personalizados, a las palabras que está buscando. Utiliza el predicado CONTAINS, en la cláusula WHERE de sus sentencias T-SQL.

Aquí están las formas más importantes de consultas con el predicado CONTAINS en pseudocódigo, donde FTcolumn representa una columna indexada de texto completo y ‘PalabraBusqueda?’ representa la palabra o frase buscada:

·         SELECT…FROM...WHERE CONTAINS(FTcolumn, 'PalabraBusqueda1'). Esta es la forma más simple. Está buscando por filas donde la FTcolumn contiene una coincidencia exacta para 'PalabraBusqueda1'. Este es un término simple.

·  SELECT...FROM...WHERE CONTAINS(FTcolumn, 'PalabraBusqueda1 OR PalabraBusqueda2'). Está buscando por filas donde el FTcolumn contiene una coincidencia exacta para 'PalabraBusqueda1' o para la palabra 'PalabraBusqueda2'. También puede utilizar los operadores lógicos AND y AND NOT y cambiar el orden de evaluación de los operadores en una expresión con paréntesis.

· SELECT...FROM...WHERE CONTAINS(FTcolumn, '"PalabraBusqueda1 PalabraBusqueda2"'). Está buscando por filas donde el FTcolumn contiene una coincidencia exacta para la frase "PalabraBusqueda1 PalabraBusqueda2".

·      SELECT...FROM...WHERE CONTAINS(FTcolumn, '"PalabraBusqueda1*"'). Está buscando por filas donde el FTcolumn contiene al menos una palabra que empieza con las letras 'PalabraBusqueda1'. Este es un término prefijo.

·  SELECT...FROM...WHERE CONTAINS(FTcolumn, 'NEAR(PalabraBusqueda1, PalabraBusqueda2)'). Está buscando por filas donde el FTcolumn contiene PalabraBusqueda1 y PalabraBusqueda2. Este es el término de proximidad personalizada más simple. En esta versión más simple, sólo busca por ocurrencias de ambas palabras, sin importar cuál es la distancia y el orden de los términos. El resultado es similar a un término simple donde dos palabras o frases están conectadas con el operador AND lógico.

·  SELECT...FROM...WHERE CONTAINS(FTcolumn, 'NEAR((PalabraBusqueda1, PalabraBusqueda2), distance)'). Está buscando por filas donde el FTcolumn contiene PalabraBusqueda1 y PalabraBusqueda2. El orden de las palabras de búsqueda no es importante; sin embargo, la distancia es un entero que nos dice cuántos términos no buscados pueden haber como máximo, entre los términos buscados para calificar una fila, para el conjunto de resultado.

·  SELECT...FROM...WHERE CONTAINS(FTcolumn, 'NEAR((PalabraBusqueda1, PalabraBusqueda2), distance, flag)'). Está buscando por filas donde el FTcolumn contiene PalabraBusqueda1 y PalabraBusqueda2. Los dos términos buscados deben estar más cerca juntos que la distancia. La bandera puede tomar valores TRUE o FALSE; el predeterminado es FALSE. Si la bandera se establece a TRUE, entonces el orden de los términos buscados es importante; PalabraBusqueda1 debe estar en el texto antes de PalabraBusqueda2.

· SELECT...FROM...WHERE CONTAINS(FTcolumn, 'FORMSOF(INFLECTIONAL, PalabraBusqueda1)'). Este es el formato de término de generación del predicado. Está buscando por filas donde el FTcolumn incluye alguna forma inflexional de la palabra PalabraBusqueda1.

·  SELECT...FROM...WHERE CONTAINS(FTcolumn 'FORMSOF(THESAURUS, PalabraBusqueda1)'). Esto es de nuevo el formato de término de generación del predicado. Está buscando por filas donde el FTcolumn incluye la palabra PalabraBusqueda1 o alguno de los sinónimos para esta palabra, definida en el archivo thesaurus.

·     SELECT...FROM...WHERE CONTAINS(FTcolumn, 'ISABOUT (PalabraBusqueda1 weight(w1), PalabraBusqueda2 weight(w2))’). Este es un término ponderado. Los pesos tienen influencia en el ranking de los documentos retornados. Sin embargo, debido a que el predicado CONTAINS no rankea los resultados, esta forma no tiene alguna influencia en él. La forma ponderada es útil para la función CONTAINSTABLE.

· SELECT...FROM...WHERE CONTAINS(PROPERTY (FTcolumn, 'PropertyName'), 'PalabraBusqueda1'). Esta es una propiedad de búsqueda. Necesita tener documentos con algunas propiedades conocidas. En una consulta de este tipo, está buscando por filas con documentos que tienen la propiedad PropertyName, que contiene el valor PalabraBusqueda1.

El Predicado FREETEXT

El predicado FREETEXT es menos específico y por lo tanto retorna más filas que el predicado CONTAINS. Busca por los valores que coinciden con el significado de una frase y no sólo con palabras exactas. Cuando utiliza el predicado FREETEXT, el motor realiza una separación de palabras de la frase de búsqueda, genera formas inflexionales (hace el stemming), e identifica una lista de expansiones o de reemplazos para las palabras en el término buscado con palabras del thesaurus. La forma es mucho más simple que la forma del predicado CONTAINS: SELECT...FROM...WHERE FREETEXT(FTcolumn, 'PalabraBusqueda1 PalabraBusqueda2'). Con esto, está buscando por filas donde el FTcolumn incluye alguna de las formas inflexionales y alguno de los sinónimos definidos de las palabras PalabraBusqueda1 y PalabraBusqueda2.

Practica: Usando los Predicados CONTAINS y FREETEXT

Después de crear todos los componentes necesarios para una solución de búsqueda de texto completo, es momento de empezar a utilizar la búsqueda de texto completo.

Ejercicio 1: Utilice el predicado CONTAINS

En este ejercicio, se utiliza el predicado CONTAINS. Además, se edita y utiliza un archivo thesaurus.

1.    Si ha cerrado el SSMS, inícielo y conéctese a la instancia de SQL Server. Abra una nueva ventana de consulta haciendo clic en el botón Nueva Consulta.

2.    Conéctese a la base de datos TSQL2012.

3.    Encuentre todas las filas donde la columna extractodoc de la tabla dbo.Documentos incluye la palabra "data". Utilice la siguiente consulta.

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'data');

4.    Encuentre todas las filas donde la columna extractodoc de la tabla dbo.Documentos incluye la palabra "data" o la palabra "índex". Utilice la siguiente consulta.

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'data OR index');

5.    Encuentre todas las filas donde la columna extractodoc de la tabla dbo.Documentos incluye la palabra "data" y no la palabra "mining". Utilice la siguiente consulta.

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'data AND NOT mining');

6.    Encuentra todas las filas donde la columna extractodoc de la tabla dbo.Documentos incluye la palabra "data" o las palabras "fact" y "warehouse". Utilice la siguiente consulta.

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'data OR (fact AND warehouse)');

7.    Encuentra todas las filas donde la columna extractodoc de la tabla dbo.Documentos incluye la frase "data warehouse". Utilice la siguiente consulta.

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'"data warehouse"');

8.    Encuentre todas las filas donde la columna extractodoc de la tabla dbo.Documentos incluye las palabras que comienzan con el prefijo "add". Utilice la siguiente consulta.

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'"add*"');

9.    Encuentre todas las filas donde la columna extractodoc de la tabla dbo.Documentos incluye la palabra "problem" en cualquier lugar cerca de la palabra "data". Utilice la siguiente consulta.

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'NEAR(problem, data)');

10.  Encuentre todas las filas donde la columna extractodoc de la tabla dbo.Documentos incluye la palabra "problem" en cualquier lugar cerca de la palabra "data". Pruébelo con una consulta que busca fragmentos donde las palabras son menos de cinco y luego con una consulta donde las palabras son menos de un término no buscado de distancia. De las dos consultas siguientes, el primero debe retornar una fila y el segundo ninguna fila.

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'NEAR((problem, data),5)');

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'NEAR((problem, data),1)');

11.  Encuentre todas las filas donde la columna extractodoc de la tabla dbo.Documentos incluye la palabra "problem" en cualquier lugar cerca de la palabra "data". Prueba con una consulta que busca fragmentos donde las palabras son menos de cinco términos no buscados de distancia. Sin embargo, se especifica que la palabra "problem" debe estar antes de la palabra "data". Utilice la siguiente consulta.

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'NEAR((problem, data), 5, TRUE)');

12.  Encuentra todas las filas donde la columna extractodoc de la tabla dbo.Documentos incluye la palabra "presentation". Pruebe con una consulta que busca una coincidencia exacta y con una consulta que busca cualquier forma de inflexional de la palabra. De las siguientes dos consultas, la primera consulta no debe retornar alguna fila y la segunda consulta una fila.

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'presentation');

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'FORMSOF(INFLECTIONAL, presentation)');

Ejercicio 2: Usar Sinónimos y FREETEXT

En este ejercicio, edita y utiliza un archivo thesaurus para añadir un sinónimo.

1.    Utilice el Bloc de notas para editar el archivo thesaurus para el idioma US English. Agregue un sinónimo "necessity" para la palabra "need". El archivo a editar es el archivo tsenu.xml, ubicado en una instalación predeterminada en la carpeta C:\Archivos de programa\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\ FTData. Si no utiliza la ruta predeterminada para la instalación o utiliza una instancia no predeterminada, entonces la ruta en una forma general es SQL_Server_Install_Path\Microsoft SQL Server\MSSQL11.Instance_id\MSSQL\ FTData. Elimine los comentarios XML del archivo.

Después de la edición, el contenido del archivo debe ser el siguiente.

<XML ID="Microsoft Search Thesaurus">

    <thesaurus xmlns="x-schema:tsSchema.xml">

    <diacritics_sensitive>0</diacritics_sensitive>

        <expansion>

           _{Internet Explorer}

           _IE

           _IE5

        </expansion>

        <replacement>

           <pat>NT5</pat>

           <pat>W2K</pat>

           _{Windows 2000}

        </replacement>

        <expansion>

           _run

           _jog

        </expansion>

        <expansion>

           _need

           _necessity

        </expansion>

    </thesaurus>

</XML>

2.    Cargue el archivo thesaurus para US English.

EXEC sys.sp_fulltext_load_thesaurus_file 1033;

3.    Encuentre todas las filas donde la columna extractodoc de la tabla dbo.Documentos incluye la palabra "need" o su sinónimo. Pruebe con una consulta que busque por una coincidencia exacta y con una consulta que busque por sinónimos de la palabra. De las siguientes dos consultas, la primera consulta no debería retornar alguna fila y la segunda consulta una fila.

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'need');

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(extractodoc, N'FORMSOF(THESAURUS, need)');

4.    Busque por todas las filas de la tabla dbo.Documentos donde el documento en la columna contenidodoc contiene una propiedad llamada "Authors" con un valor que incluye la palabra "Dejan". Utilice la siguiente consulta.

SELECT id, titulo, extractodoc

FROM dbo.Documentos

WHERE CONTAINS(PROPERTY(contenidodoc,'Authors'), 'Dejan');

5.    Por último, encontrar todas las filas donde la columna extractodoc contiene alguna de las palabras "data", "presentation", o "need". Las palabras pueden estar en cualquier forma inflexional. Buscar sinónimos también. Utilice la siguiente consulta.

SELECT id, titulo, tipodoc, extractodoc

FROM dbo.Documentos

WHERE FREETEXT(extractodoc, N'data presentation need');

Read More

Crear Catálogos e Indices de Texto Completo

La búsqueda de texto completo permite búsquedas aproximadas en bases de datos SQL Server 2012. Antes de empezar a usar predicados y funciones de texto completo, debe crear índices de texto completo dentro de catálogos de texto completo. Después de crear índices de texto completo sobre las columnas de caracteres en su base de datos, puede buscarlos por:

·         Término Simple, esto es, una o más palabras o frases específicas.

·         Término de Prefijo, los cuales son términos con que las palabras o frases empiezan.

·         Término de Generación, significando formas inflexionales de las palabras.

·         Término de Proximidad, o palabras o frases cercanas a otra palabra o frase.

·         Término Thesaurus, o sinónimos de una palabra.

·     Término Ponderado, que son palabras o frases que utilizan valores con su peso personalizado.

·         Búsqueda Semántica Estadística, o frases clave en un documento.

·         Documentos Similares, donde la similitud es definida por frases claves semánticas.

Componentes de Búsqueda de Texto Completo

Para empezar a utilizar la búsqueda de texto completo, tiene que entender los componentes de texto completo. Para empezar, puede comprobar si la Búsqueda de Texto Completo está instalada, por utilizar la siguiente consulta.

SELECT SERVERPROPERTY('IsFullTextInstalled');

Si Búsqueda de Texto Completo no está instalado, debe volver a ejecutar el setup.

Puede crear índices de texto completo sobre columnas de tipo CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, IMAGE, XML y VARBINARY(MAX). Además de usar índices de texto completo sobre datos carácter de SQL Server, puede almacenar documentos completos en columnas binarias o XML, y utilizar consultas de texto completo sobre estos documentos. Las columnas de tipo de datos VARBINARY(MAX), IMAGE, o XML requieren una columna de tipo adicional, en la cual almacena la extensión del archivo (por ejemplo, .docx, .pdf, o .xlsx) del documento en cada fila.

Necesita filtros apropiados para los documentos. Filtros, llamados ifilters en la terminología de texto completo, extraer la información textual y eliminar el formato de los documentos. Puede comprobar qué filtros están instalados en su instancia por utilizar la siguiente consulta.

EXEC sys.sp_help_fulltext_system_components 'filter';

Además de utilizar el procedimiento almacenado de sistema, también puede comprobar qué filtros están instalados en su instancia por consultar la vista de catálogo sys.fulltext_document_types, de la siguiente manera.

SELECT document_type, path

FROM sys.fulltext_document_types;

Muchos formatos populares son soportados por defecto. Puede instalar filtros adicionales, tales como los filtros para formatos de documentos Microsoft Office 2010. Puede descargar el paquete de filtros de Microsoft Office 2010 en http://www.microsoft.com/es-ES/download/details.aspx?id=17062.

Después de descargar los paquetes de filtros, se instalan en su ordenador con su instancia SQL Server, por utilizar las instrucciones proporcionadas con los paquetes de filtros. Para un paquete de filtros de Office 2010, por ejemplo, todo lo que necesita hacer es ejecutar el archivo descargado de extracción automática.

Después de instalar el paquete de filtros en su computadora, necesita registrar los filtros en SQL Server por utilizar el siguiente comando.

EXEC sys.sp_fulltext_service 'load_os_resources', 1;

Puede necesitar reiniciar el SQL Server. Después de reiniciarlo, compruebe si los filtros fueron instalados exitosamente por utilizar el procedimiento de sistema sys.sp_help_fulltext_components de nuevo.

Los separadores de palabras y lematizadores (stemmers) realizan análisis lingüístico en todos los datos de texto completo. Puesto que las reglas difieren de un idioma a otro, los separadores de palabras y lematizadores son específicos del idioma. Un separador de palabra identifica las palabras individuales (o tokens). Los tokens son insertados en un índice de texto completo en formato comprimido. El lematizador genera formas inflexionales de una palabra basada en las reglas de un idioma. Puede utilizar la siguiente consulta para comprobar que idiomas son soportados en SQL Server.

SELECT lcid, name

FROM sys.fulltext_languages

ORDER BY name;

Los lematizadores son específicos del idioma. Si utiliza una versión localizada de SQL Server, SQL Server Setup establece el idioma de texto completo por defecto al idioma de su instancia, si el idioma es soportado en su instancia. Si el idioma no es soportado, o si utiliza una versión no localizada de SQL Server, el idioma de texto completo por defecto es el inglés. Puede especificar un idioma diferente para cada columna indexada de texto completo. Puede cambiar el idioma predeterminado por usar el procedimiento de sistema sys.sp_configure.

Los separadores de palabras son específicos del idioma también. Si un separador de palabras no existe para el idioma de su instancia, un separador de palabras neutral es utilizado. El separador de palabras neutral utiliza sólo caracteres neutrales como espacios para la separación de texto en palabras individuales.

Imagine que tiene documentos acerca de SQL Server. La frase "SQL Server" probablemente aparece en todos los documentos. Tal frase no le ayuda con las búsquedas; sin embargo, satura un índice de texto completo. Puede evitar la indexación de tales palabras ruidosas por crear stoplists de stopwords. Puede comprobar los stopwords y stoplists actuales en su base de datos por utilizar las consultas siguientes.

SELECT stoplist_id, name

FROM sys.fulltext_stoplists;

SELECT stoplist_id, stopword, language

FROM sys.fulltext_stopwords;

Las consultas de texto completo pueden buscar no sólo por las palabras que suministre en una consulta; pueden buscar por sinónimos también. SQL Server encuentra sinónimos en archivos thesaurus. Cada idioma tiene un archivo thesaurus XML asociado. La ubicación de los archivos thesaurus para una instancia por defecto es SQL_Server_install_path\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\FTDATA\. Puede editar manualmente cada archivo thesaurus y configurar los siguientes elementos:

·      Diacritics_sensitive. Establecer el valor de este elemento a 0 si el idioma no es sensible al acento, o 1 si es sensible al acento.

·         Expansion. Utilice este elemento para agregar palabras de expansión a una palabra. Por ejemplo, puede agregar la palabra de expansión "autor" a la palabra "escritor" con el fin de buscar "autor", también, cuando un usuario final busca la palabra "escritor".

·         Replacement. Utilice este elemento para definir palabras o términos de reemplazo para una palabra o término específico. Por ejemplo, "Windows 2008" podría ser un reemplazo para "Win 2k8". En tal ejemplo, SQL Server podría buscar por "Windows 2008", aun cuando "Win 2k8" fue utilizado en un término de búsqueda.

Después de editar el archivo thesaurus para un idioma específico, debe cargarlo con la siguiente llamada al procedimiento de sistema.

EXEC sys.sp_fulltext_load_thesaurus_file 1033;

El parámetro del procedimiento denota el ID lenguaje; en este caso, (1033), que es el idioma US English.

Las consultas de texto completo pueden buscar también en las propiedades del documento. Cuales propiedades pueden ser buscadas, dependen del filtro del documento. Puede crear una lista de propiedades de búsqueda para definir las propiedades de búsqueda para sus documentos. Puede incluir propiedades que un filtro específico puede extraer de un documento.

Creando y Gestionando Catálogos e Indices de Texto Completo

Una vez que tenga toda la infraestructura de texto completo en su lugar, puede comenzar a usarla. Los índices de texto completo son almacenados en los catálogos de texto completo. Un catálogo de texto completo es un objeto virtual, un contenedor para índices de texto completo. Como un objeto virtual, no pertenece a algún filegroup.

La siguiente es la sintaxis para crear catálogos de texto completo.

CREATE FULLTEXT CATALOG nombre_catalogo

     [ON FILEGROUP filegroup ]

     [IN PATH 'rutaraiz']

     [WITH <opcion_catalogo>]

     [AS DEFAULT]

     [AUTHORIZATION nombre_propietario ]

<opcion_catalogo>::=

     ACCENT_SENSITIVITY = {ON|OFF}

Las opciones ON FILEGROUP e IN PATH se mantienen por compatibilidad con SQL Server 2008 y versiones anteriores y no tienen efecto en SQL Server 2012; debería evitar utilizarlas. La opción ACCENT_SENSITIVITY determina si los índices de texto completo en este catálogo son sensibles al acento o no. Si cambia esta opción más adelante, tendrá que reconstruir todos los índices de texto completo en el catálogo.

Modifica un catálogo de texto completo por utilizar la sentencia ALTER FULLTEXT CATALOG, y lo elimina con la sentencia DROP FULLTEXT CATALOG.

Después de que tiene un catálogo de texto completo, puede crear índices de texto completos apropiados. La sintaxis para crear un índice de texto completo es la siguiente.

CREATE FULLTEXT INDEX ON nombre_tabla

   [ ( { nombre_columna

            [ TYPE COLUMN nombre_tipo_columna ]

            [ LANGUAGE termino_languaje ]

            [ STATISTICAL_SEMANTICS ]

       } [ ,...n]

     ) ]

   KEY INDEX nombre_indice

   [ ON <opcion_filegroup_catalogo> ]

   [ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]

[;]

< opcion_filegroup_catalogo >::=

 {

    nombre_catalogo_fulltext

 | (nombre_catalogo_fulltext, FILEGROUP nombre_filegroup)

 | ( FILEGROUP nombre_filegroup, nombre_catalogo_fulltext)

 | ( FILEGROUP nombre_filegroup)

 }

<with_option>::=

 {

   CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] }

 | STOPLIST [ = ] { OFF | SYSTEM | nombre_stoplist }

 | SEARCH PROPERTY LIST [ = ] nombre_lista_property

 }

La mayoría de las opciones son auto-descriptivas. Se aprende acerca de ellos en la práctica en este post. Lo siguiente describe algunas opciones avanzadas:

·         KEY INDEX nombre_indice. Este es el nombre del índice de llave unique en una tabla. Tiene que usar una columna unique, llave simple, no nula. Los enteros son recomendados.

·         CHANGE_TRACKING [=] {MANUAL | AUTO | OFF [, NO POPULATION]}. Esta opción especifica si SQL Server actualiza un índice de texto completo automáticamente. SQL Server utiliza un mecanismo de seguimiento de cambios, para rastrear los cambios.

·     STATISTICAL_SEMANTICS. Esta opción crea frases de llaves adicionales y documentos similares de índices, que son parte de la indexación semántica estadística.

La última opción mencionada, la opción STATISTICAL_SEMANTICS, merece una explicación más profunda. La búsqueda semántica estadística le da una visión más profunda en los documentos, por la extracción e indexación de frases claves estadísticamente relevantes. La búsqueda de texto completo utiliza estas frases clave para identificar e indexar documentos que son similares o relacionados. Consultar estos índices semánticos por utilizar tres funciones rowset de T-SQL, para recuperar los resultados como datos estructurados. Utiliza estas funciones en las prácticas en este post. La búsqueda semántica extiende la funcionalidad de búsquedas de texto completo. Le permite consultar el significado de los documentos. Por ejemplo, puede consultar el índice de las frases claves, para construir la taxonomía de los documentos. Puede consultar el documento de similitud de índices para identificar resúmenes, que coinciden con una descripción del trabajo. La búsqueda semántica te da la posibilidad de crear tu propia solución de minería de texto. La búsqueda semántica podría ser especialmente interesante en conjunción con los componentes de minería de texto de SQL Server Integration Services (SSIS).

Para utilizar la característica Busqueda Semantica, tiene que tener el Full-Text Search instalado. Además, necesita instalar la Semantic Language Statistics Database. Lo instalará en la práctica de este post.

Práctica: Creando un Indice de Texto Completo

En esta práctica, crea una tabla, la puebla con algunos documentos y datos de texto (https://onedrive.live.com/redir?resid=21485C1E4BBA52A!722&authkey=!ALSPxEfmtMdZJ14&ithint=file%2crar), y crea un catálogo e índice de texto completo sobre esta tabla. Esta práctica asume que el idioma predeterminado de su instancia es US English.

Ejercicio 1: Crear una Tabla y Componentes de Texto Completo

En este ejercicio, creará una tabla de demostración, lo poblará con algún texto demo, y luego creará stopwords y una stoplist y propiedades de documento de búsqueda.

1.    Inicie el SSMS y conéctese a su instancia de SQL Server.

2.    Abra una nueva ventana de consulta haciendo clic en el botón Nueva Consulta.

3.    Cambie el contexto a la base de datos TSQL2012.

4.    Compruebe si Full-Text Search está instalado utilizando la siguiente consulta.

SELECT SERVERPROPERTY('IsFullTextInstalled');

5.    Si Full-Text Search no está instalado, ejecute el SQL Server Setup e instálelo. También instale el paquete de filtros Microsoft Office 2010.

6.    Crear una tabla que utilizará para búsquedas de texto completo. Crearlo en el esquema dbo y nombrarlo Documentos. Utilice la información de la Tabla para las columnas de su tabla dbo.Documentos.

Utilice el siguiente código para crear la tabla.

CREATE TABLE dbo.Documentos

(

  id INT IDENTITY(1,1) NOT NULL,

  titulo NVARCHAR(100) NOT NULL,

  tipodoc NCHAR(4) NOT NULL,

  extractodoc NVARCHAR(1000) NOT NULL,

  contenidodoc VARBINARY(MAX) NOT NULL,

  CONSTRAINT PK_Documentos

   PRIMARY KEY CLUSTERED(id)

);

7.    Importe los cuatro documentos incluidos en la carpeta para este post. Si la carpeta es C:\TSQL, entonces puede utilizar el siguiente código directamente; de lo contrario, cambie la carpeta en las funciones OPENROWSET adecuadamente.

INSERT INTO dbo.Documentos

(titulo, tipodoc, extractodoc, contenidodoc)

SELECT N'Columnstore Indices and Batch Processing',

 N'docx',

 N'You should use a columnstore index on your fact tables,

   putting all columns of a fact table in a columnstore index.

   In addition to fact tables, very large dimensions could benefit

   from columnstore indices as well.

   Do not use columnstore indices for small dimensions. ',

 bulkcolumn

FROM OPENROWSET(BULK

'C:\TSQL\ColumnstoreIndicesAndBatchProcessing.docx',

                SINGLE_BLOB) AS doc;

INSERT INTO dbo.Documentos

(titulo, tipodoc, extractodoc, contenidodoc)

SELECT N'Introduction to Data Mining',

N'docx',

N'Using Data Mining is becoming more a necessity for every company

and not an advantage of some rare companies anymore. ',

bulkcolumn

FROM OPENROWSET(BULK 'C:\TSQL\IntroductionToDataMining.docx',

                SINGLE_BLOB) AS doc;

INSERT INTO dbo.Documentos

(titulo, tipodoc, extractodoc, contenidodoc)

SELECT N'Why Is Bleeding Edge a Different Conference',

 N'docx',

 N'During high level presentations attendees encounter many questions.

   For the third year, we are continuing with the breakfast Q&A session.

   It is very popular, and for two years now,

   we could not accommodate enough time for all questions and discussions! ',

 bulkcolumn

FROM OPENROWSET(BULK 'C:\TSQL\WhyIsBleedingEdgeADifferentConference.docx',

                SINGLE_BLOB) AS doc;

INSERT INTO dbo.Documentos

(titulo, tipodoc, extractodoc, contenidodoc)

SELECT N'Additivity of Measures',

 N'docx',

 N'Additivity of measures is not exactly a data warehouse design problem.

   However, you have to realize which aggregate functions you will use

   in reports for which measure, and which aggregate functions

   you will use when aggregating over which dimension.',

 bulkcolumn

FROM OPENROWSET(BULK 'C:\TSQL\AdditivityOfMeasures.docx',

                SINGLE_BLOB) AS doc;

8.    Crear una lista de propiedad de búsqueda llamada ListaPropiedadBusquedaPalabra. Agregar la propiedad Authors a la lista. Las propiedades de documento tienen IDs enteros y GUIDs predefinidos. Para la propiedad Authors de los documentos Office, el GUID es F29F85E0-4FF9-1068-AB91-08002B27B3D9, y el ID entero es 4. Utilice el siguiente código.

CREATE SEARCH PROPERTY LIST ListaPropiedadBusquedaPalabra;

GO

ALTER SEARCH PROPERTY LIST ListaPropiedadBusquedaPalabra

 ADD 'Authors'

 WITH (PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9',

       PROPERTY_INT_ID = 4,

       PROPERTY_DESCRIPTION = 'System.Authors – autores de un articulo dado');

9.    Crear una lista stopwords llamada SQLStopList. Agregar la palabra SQL, utilizando inglés como el idioma. Utilice el siguiente código.

CREATE FULLTEXT STOPLIST SQLStopList;

GO

ALTER FULLTEXT STOPLIST SQLStopList

 ADD 'SQL' LANGUAGE 'English';

10.  Revisar la lista stopwords y recuerde el ID stoplist. Utilice la siguiente consulta.

SELECT w.stoplist_id,

 l.name,

 w.stopword,

 w.language

FROM sys.fulltext_stopwords AS w

 INNER JOIN sys.fulltext_stoplists AS l

  ON w.stoplist_id = l.stoplist_id;

11.  Utilice la vista de gestión dinámica sys.dm_fts_parser para comprobar cómo la búsqueda de texto completo está analizando cadenas de acuerdo a su stoplist, información thesaurus, separador de palabras en el idioma seleccionado, y lematizadores en el idioma seleccionado. Por ejemplo, las siguientes dos consultas verifican cómo una cadena es separada en palabras y que formas inflexionales de una búsqueda de texto completo de palabra puede utilizar. Note los parámetros de la vista de gestión dinámica: El primero es la cadena de caracteres a analizar, el segundo es el ID idioma (1033 para US English), el tercero es el ID stoplist que obtuvo de la consulta anterior, y el cuarto es una marca mostrando si el análisis debería ser sensible a acentos o no.

SELECT *

FROM sys.dm_fts_parser

(N'"Additivity of measures is not exactly a data warehouse design problem.

   However, you have to realize which aggregate functions you will use

   in reports for which measure, and which aggregate functions

   you will use when aggregating over which dimension."', 1033, 5, 0);

SELECT *

FROM sys.dm_fts_parser

('FORMSOF(INFLECTIONAL,'+ 'function' + ')', 1033, 5, 0);

Ejercicio 2: Instalar una Base de Datos Semántica y Crear un Indice de Texto Completo

En este ejercicio, se instala una base de datos semántica y luego crea un índice de texto completo.

1.    Compruebe si la Semantic Language Statistics Database está instalada. Si la siguiente consulta no retorna una fila, debe instalarlo.

SELECT *

FROM sys.fulltext_semantic_language_statistics_database;

Para instalar la Semantic Language Statistics Database, ejecute el paquete SemanticLanguageDatabase.msi desde la carpeta x64\Setup (si está usando una instancia de 64 bits) o x86\Setup (si la instancia es de 32 bits) de la unidad de SQL Server Setup.

2.    Compruebe si la cuenta de servicio de SQL Server tiene permisos Read y Write en la carpeta donde instaló los archivos Semantic Language Statistics Database. La carpeta predeterminada es C:\Archivos de programa\Microsoft Semantic Language Database. Si ha instalado la base de datos en la carpeta predeterminada, puede adjuntarlo con el comando siguiente.

CREATE DATABASE semanticsdb ON

(FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb.mdf'),

(FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb_log.ldf')

FOR ATTACH;

3.    Después de adjuntar la base de datos, registrarlo por utilizar el siguiente código.

EXEC sp_fulltext_semantic_register_language_statistics_db

 @dbname = N'semanticsdb';

4.    Compruebe si la Semantic Language Statistics Database fue instalada exitosamente por repetir la consulta del paso 1. Esta vez, la consulta debe retornar una fila.

5.    Por último, es el momento para crear un catálogo. Nómbrelo CatalogoTcDocumentos. Utilice el siguiente código.

CREATE FULLTEXT CATALOG CatalogoTcDocumentos;

6.    Ahora, crear un índice de texto completo. Debería indexar las columnas extractodoc y contenidodoc. Ajuste el seguimiento de cambio para poblar el índice a AUTO. Utilice el siguiente código.

CREATE FULLTEXT INDEX ON dbo.Documentos

(

extractodoc Language 1033,

contenidodoc TYPE COLUMN tipodoc

Language 1033

STATISTICAL_SEMANTICS

)

KEY INDEX PK_Documentos

ON CatalogoTcDocumentos

WITH STOPLIST = SQLStopList,

SEARCH PROPERTY LIST = ListaPropiedadBusquedaPalabra,

CHANGE_TRACKING AUTO;

Read More