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.
