Ozimar Henrique
[SQL 2012] FileTable . FullText . Semantic
Neste post irei falar sobre um novo recurso do SQL Server 2012, FileTable.
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.
Subscribe ecode10.com
Receive our latest updates about programming languages, software, database, books, ebooks, classes, jobs and more.
You can cancel anytime.
Log In
Related articles
Creating a SiteMap using SQL Database
Como indexar melhor meu site e links?
Transformando linhas em colunas usando SQL Server
veja como funciona
Comando LEFT no database
veja como