🔥 Articles, eBooks, Jobs, Columnist, Forum, Podcasts, Courses 🎓
🎓 ecode10.com

Curso de Web API com .NET última versão
[SQL 2012] FileTable . FullText . Semantic | ecode10.com


[SQL 2012] FileTable . FullText . Semantic

Neste post irei falar sobre um novo recurso do SQL Server 2012, FileTable.
DATABASE
Wednesday, June 6, 2012
Ozimar Henrique   Ozimar Henrique  SHARE

Neste post irei falar sobre um novo recurso do SQL Server 2012, FileTable.

Filetables são user-tables especializadas, com o schema pré-definido para armazenar dados FILESTREAM, arquivos e pastas, além dos respectivos atributos.

Cada Filetable representa uma hierarquia de arquivos e diretórios, onde cada registro na tabela corresponde a um específico arquivo ou diretório.

Uma das características deste novo recurso é que ao mover um arquivo para um diretório pré especificado, um registro é criado na Filetable automaticamente.

Vamos para a parte prática:

PARTE 1 - CONFIGURAÇÃO DO SERVIDOR
Para testar este recurso, primeiro temos que habilitar o FILESTREAM no serviço do SQL Server.

Abra o SQL Server Configuration Manager e localize o serviço "SQL Server", vá em propriedades e localize a aba "FILESTREAM", marque as opções conforme a imagem abaixo:




Veja que ele também cria um compartilhamento de rede. No meu caso eu acesso através do endereço: \\NOTEOZI\mssqlserver

Temos que configurar o nível de acesso do FILESTREAM, vamos utlizar o nível "Full access enabled"

use Master 
GO
exec sp_configure filestream_access_level, 2;
reconfigure;

PARTE 2 - CRIAÇÃO DO DATABASE E DA FILETABLE

Vamos criar um Database com o recurso FILESTREAM e também definir o diretório para o uso do FILESTREAM neste database.

CREATE DATABASE TesteFT
ON
PRIMARY ( NAME = TesteFT,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TesteFT.mdf'),
FILEGROUP FSTesteFTGroup CONTAINS FILESTREAM( NAME = FSTesteFT, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FSTesteFT')
LOG ON ( NAME = TesteFT_Log,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TesteFT_Log.ldf')
WITH FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'FileTables')
GO

Para verificar a criação do database com as características do FILESTREAM, utilizamos o comando:

SELECT * FROM sys.database_filestream_options WHERE database_id=DB_ID('TesteFT')

Vamos criar nossa FileTable, especificando o nome da PK e de 2 Unique Key criadas automaticamente.

Estas especificações são opcionais porém acho mais organizado e melhor documentado.

USE TesteFT
GO
CREATE TABLE Arquivos AS FileTable
WITH (
      FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME=PK_Arquivos,
      FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME=UK_ArquivosStream,
      FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME=UK_ArquivosPath
)
GO

OBS: Em alguns ambientes precisei usar a instrução "SET ANSI_NULLS ON" antes da criação da tabela

Para verificar a criação de uma FileTable utilize o comando: SELECT * FROM sys.filetables

Você pode visualizar o diretório de uma FileTable específica através do menu "Explore FileTable Directory"




Vamos fazer alguns testes, copie uns arquivos para dentro da pasta especificada e depois execute o comando:

SELECT name, file_type, cached_file_size FROM Arquivos




PARTE 3 - FULLTEXT

Para que o FullText reconheça o máximo filtros possíveis é necessário executar os comandos:

EXEC sp_fulltext_service 'load_os_resources', 1
EXEC sp_fulltext_service 'restart_all_fdhosts'

Para verificar: select * from sys.fulltext_document_types

Caso seja necessário, você pode baixar o "Microsoft Office 2010 Filter Packs" no endereço: http://www.microsoft.com/download/en/details.aspx?id=17062

Vamos criar nosso catálogo FullText para que possamos fazer nossas pesquisas aos arquivos e seus conteúdos.

CREATE FULLTEXT CATALOG FTC_Arquivo

Antes de criar nosso índice, iremos criar uma "SEARCH PROPERTY LIST" para que possamos pesquisar também as propriedades de um arquivo (Autor, Título, tag, etc).

Para obter a lista de possíveis filtros acesse o link: http://msdn.microsoft.com/pt-br/library/ee677618.aspx

Funciona para as extensões .docx, .xlsx, .pptx

CREATE SEARCH PROPERTY LIST ArqPropriedades;
GO
ALTER SEARCH PROPERTY LIST ArqPropriedades
ADD 'Titulo'
WITH
( PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
PROPERTY_INT_ID = 2
);
ALTER SEARCH PROPERTY LIST ArqPropriedades
ADD 'Autor'
WITH
( PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
PROPERTY_INT_ID = 4
);

Para verificar:
SELECT property_name, property_set_guid, property_int_id FROM sys.registered_search_properties




Agora sim podemos criar nosso FULLTEXT INDEX.

Na criação do você pode especificar o idioma utlizado, basta consultar(select * from sys.fulltext_languages). Português do Brasil é o 1046

CREATE FULLTEXT INDEX ON Arquivos
(
   name language 1046,
   file_stream TYPE COLUMN file_type language 1046
)
KEY INDEX UK_ArquivosStream
ON FTC_Arquivo
WITH SEARCH PROPERTY LIST = ArqPropriedades
GO

Vamos fazer algumas pesquisas:

SELECT name Arquivo, file_stream.GetFileNamespacePath(1,1) Local
FROM Arquivos
WHERE CONTAINS(file_stream, 'tabelas')

SELECT name Arquivo, file_stream.GetFileNamespacePath(1,1) Local
FROM Arquivos
WHERE CONTAINS(PROPERTY(file_stream, 'Autor'), 'Henrique')

SELECT name Arquivo, file_stream.GetFileNamespacePath(1,1) Local
FROM Arquivos
WHERE CONTAINS(PROPERTY(file_stream, 'Titulo'), 'olap')




PARTE 4 - SEMANTIC SEARCH

Vamos utilizar um novo recurso chamado Semanti Search, com ele podemos verificar ocorrencias dentro de um arquivo, comparar arquivos e outras funções.

Para começar precisamos instalar um database de semantica

Baixe o arquivo neste endereço: http://www.microsoft.com/downloads/details.aspx?familyid=67E1CEB5-5B3B-4002-B43F-7A2807E9DA5D&displaylang=pt-br

Instale na pasta indicada.
Anexar o database ao seu servidor:

CREATE DATABASE semanticsdb
ON ( FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb.mdf' )
LOG ON ( FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb_log.ldf' )
FOR ATTACH
Registrar como semantic language statistics database:
EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb'
Vamos apagar o FullText Index para recriá-lo usando semantics:
DROP FULLTEXT INDEX ON Arquivos
GO
CREATE FULLTEXT INDEX ON Arquivos
(
name language 1046 STATISTICAL_SEMANTICS,
file_stream TYPE COLUMN file_type language 1046 STATISTICAL_SEMANTICS
)
KEY INDEX UK_ArquivosStream
ON FTC_Arquivo
WITH SEARCH PROPERTY LIST = ArqPropriedades
GO

Vamos utilizar algumas funções:

SemanticKeyPhraseTable - Lista a quantidade de valores que aparecem com maior frequência dentro do arquivo.

SELECT name, keyphrase, AVG(score) AS score
FROM SemanticKeyPhraseTable (Arquivos, *)
INNER JOIN Arquivos
ON stream_id = document_key
where score>0.7
GROUP BY name, keyphrase
ORDER BY name, score DESC




Neste exemplo peguei somente as palavras com mais de 70% de frequencia
SemanticSimilarityTable - Lista a similaridade entre um arquivo indicado e os outros

DECLARE @ArqID uniqueidentifier
SELECT @ArqID = stream_id FROM Arquivos WHERE name = '[BI] Dimensão Tempo.docx'

SELECT name ArquivoComparado, score Similaridade
FROM SemanticSimilarityTable(Arquivos, *, @ArqID)
INNER JOIN Arquivos 
ON stream_id = matched_document_key
ORDER BY score DESC




Este recurso é uma novidade muito boa, principalmente para o pessoal que trabalha com GED.



Curso de Web API com .NET última versão


Related articles




Curso de Web API com .NET última versão
Top