Trabalhando com Dados Não-Estruturados no SQL Server 2012 (pt-BR)
Introdução
http://luanmorenodba.files.wordpress.com/2012/04/1_thumb.jpg?w=244&h=154
(Figura 1 – DataCenter – Armazenamento de Diversas Informações.)
Devido a grande quantidade de informações que possuímos hoje, temos a capacidade de criar novos paradigmas, novos produtos, ter invenções que mudem o curso da humanidade e assim faça com que nossas horas do dia possam ser bem mais aproveitadas com tais tecnologias.
A tecnologia traz isso, a facildade em toques, hoje temos acesso na palma de nossas mãos, podemos comprar mercadorias, vender itens, compartilhar informações, realizar transferências bancárias e assim por diante. Porém esse grande acúmulo de informações tem causado um fenômeno chamado de Big Data.
O Fenômeno BIG DATA
http://luanmorenodba.files.wordpress.com/2012/04/big-data_thumb.jpg?w=244&h=232
(Figura 2 – As Informações Armazenadas na Nuvem.)
Esse fenômeno consiste no crescimento exponencial das informações que estão armazenadas em diversos tipos de dispositivos, assim como em banco de dados. A dificuldade de capturar, armazenar, procurar, compartilhar, visualizar e principalmente analizar, passa a ser uma tarefa difícil e desafiadora.
Hoje em dia achar tendências a partir de análises, passa a ter uma complexidade muito grande, porque as informações estão espalhadas por diversos locais e fontes.
Com essa dificuldade em mente, passamos a ter nesta década um novo recurso, O Cloud Computing possibilita facilidades no manuseio e armazenamento dessas informaçõs, faz com que possamos tirar os olhos de tarefas administrativas e nos concentrar mais na informação propriamente dita.
Em 2011 pesquisas apontam que o fenômeno do Big Data é igual a um efeito Tri-Dimensional que seria, aumento no volume das informações + velocidade de crescimento + variedade das informações (Fontes dos Dados, Tipos …). Com isso passamos a entender que o crescimento das informações são inevitáeis, e para que possamos “analisar” as mesmas tenhamos que possuir uma grande capacidade de hardware e software para que essa tarefa seja possível.
Dados Não-Estruturados
http://luanmorenodba.files.wordpress.com/2012/04/unstructured-data1_thumb.jpg?w=210&h=178
(Figura 3 – Quantidade de Informações Não-Estruturadas.)
Dentro desse conceito explicado, temos um grande agravante, informações que ainda não foram estruturadas, que ainda não passaram por algum pocesso de modelagem ou normatização, isso faz com que o tempo para análise dessas informações seja mais demorada.
O resultado desse processo é iregularidade , ambiguidade, formatação, e dificuldade de entendimento que faz com que tenhamos que possuir ferramentas para armazenamento, otimização e consultas desses dados.
Hoje em dia, possuímos 80% de nossas informações armazenadas fora de banco de dados, fazendo com que a maioria dessas informações não sejam estruturadas.
Assim vemos que esses dois grandes eventos estão bem ligados, a dificuldade de se estruturar uma informações com a quantidade assustadora de conteúdos publicados a cada dia que se passa.
Armazenamento dos Dados Não-Estruturados no SQL Server
Como grande porcentagem das informações não-estruturadas são armazenadas em arquivos ou documentos, as empresas geralmente manteem seus metadados em banco de dados.
Armazenamento em BLOB
A vantagem desse armazenamento é a grande possibilidade de se poder realizar backups, assim como possuir a integrção com o serviço de pesquisa avançada do SQL Server (FULL-Text Search) e não podendo deixar de falar sobre a facilidade de administração e gerenciamento dos dados.
Armazenamento em FileStream
Esse recurso provê a possibildiade de um armazenamento, gerenciamento e streaming dos dados armazenados, agora em pastas do sistema operacional, possibilitando assim maior integração e manuseio desses arquivos.
Armazenamento em FileTables
Como nova proposta de solução do SQL Server 2012 o FileTable é um novo recurso integrado com o FILESTREAM que faz com que arquivos e diretórios sejam endereçados dentro do banco de dados com total integração com a API do Windows.
BLOB x FileStream x FileTables
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb.png?w=833&h=184
(Figura 4 – Comparação entre os recursos explicados. -http://msdn.microsoft.com/enus/library/hh403405.aspx#Filestream)
Entendendo o FileTable no SQL Server 2012
O FileTable integra o armazenamento dos dados, gerenciamento de componentes e prove total integração com os serviços do SQL Server, incluindo Full-Text Search e o novo recurso chamado Semantic Search que veremos mais a seguir.
Ou seja, agora é possível realizar o armazenamento de arquivos e documentos em tabelas especias que são chamadas de FileTables, assim seu armazenamento efetivo ficará em pastas do próprio sistema, fazendo com que não haja nenhuma mudança para as aplicações e clientes.
Com isso o SQL Server 2012 quebra uma grande barreira em relação ao armazenamento de informações não-estruturadas. Agora é possível simplesmente mover arquivos para a pasta que contenha esse recurso, para que a mesma seja totalmente integrada com o SQL Server.
Demo
Para que seja possível realizar a configuração desse recurso, primeiramente precisamos habilitar o FileStream na instância.
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb1.png?w=653&h=190
(Figura 5 – Painel de Controle do SQL Server Configuration Manager)
Indo na propriedade da Instância a do SQL Server 2012, vá na aba FILESTREAM e habilite os seguintes recursos.
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb2.png?w=338&h=145
(Figura 6 – Habilitando o FILESTREAM para trabalhar com FileTables.)
Logo após habilitar o FILESTREAM na instância desejada, precisamos tambêm habilitá-lo dentro do SQL Server, para isso utilize:
EXEC sp_configure‘filestream_access_level’, 1
reconfigure
go
Precisamor criar um local aonde ficaram armazenados o FILEGROUP que irá conter o FILESTREAM assim como suas informações.
EXEC sp_configure‘xp_cmdshell’, 1
reconfigure
go
* *
EXEC xp_cmdshell‘IF NOT EXIST C:\DocumentosDadosSQL MKDIR C:\DocumentosDadosSQL’
* *
EXEC sp_configure‘xp_cmdshell’, 0
reconfigure
go
Depois desses recursos habilitados, iremos criar um banco de dados que servirá para esse exemplo, para isso realizaremos o seguinte comando.
CREATE DATABASE BaseDocumentosSQL
ON PRIMARY
( name = BaseDocumentosSQL,
* filename = N’C:\DocumentosDadosSQL\BaseDocumentosSQL_mdf.mdf’*
),
FILEGROUP ArmazenamentoDadosSQL
CONTAINSFILESTREAM
( name = FG_BaseDocumentosSQL_Arq,
* filename = N’C:\DocumentosDadosSQL\FG_BaseDocumentosSQL_Arq’*
)
LOGON
( name = BaseDocumentosSQLLog,
* filename = N’C:\DocumentosDadosSQL\BaseDocumentosSQLLog_ldf.ldf’*
)
WITHFILESTREAM
( non_transacted_access = FULL,
* directory_name = ‘DocumentosDadosSQL’*
)
Logo após criado, podemos visualizar as informações de FILESTREAM da seguinte forma.
USE BaseDocumentosSQL
GO
* *
SELECT*
FROM sys.database_filestream_options AS O
WHERE O.database_id = DB_ID()
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb3.png?w=496&h=41
(Figura 7 – Informações Referentes ao FILESTREAM.)
Agora com o banco de dados criado e assim especificado o FILESTREAM para o FILEGROUP ArmazenamentoDadosSQL, podemos realizar a criação do FileTable a seguir.
CREATE TABLE BaseDocumentosSQL.dbo.Documentos
AS FILETABLE WITH (filetable_directory = N’BaseDocumentosSQL’)
Visualizando as informações do FileTable criado.
SELECT SCHEMA_NAME()+‘.’+OBJECT_NAME(FT.object_id) AS Name, is_enabled,
* directory_name, filename_collation_id, filename_collation_name*
FROM sys.filetables AS FT
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb4.png?w=506&h=46
(Figura 8 – Informações Referentes ao FileTable.)
Como a criação foi concluída com sucesso, será criado um compartilhamento para que os arquivos possam ser acessados, e para que possamos saber esse caminho podemos utilizar…
SELECT FILETABLEROOTPATH() AS ‘Root’
GO
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb5.png?w=346&h=41
(Figura 9 – Compartilhamento do FileTable.)
Para acessar esta pasta iremos em
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb6.png?w=344&h=70
(Figura 10 – Local Físico do Compartilhamento do FileTable.)
Porém quando tentamos acessar essa pasta, nos deparamos com a seguinte informação
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb7.png?w=425&h=139
(Figura 11 – Erro durante acesso a pasta do FileTable.)
O sistema informa que não estamos com acesso na pasta, mas porque isso de fato acontece? Esse erro é gerado pelo nível de permisão que atribuimos para o FILESTREAM. Aqui quando especificamos
EXEC sp_configure‘filestream_access_level’, 1
reconfigure
go
O nível de acesso1 é interpretado somente como arquivos ou seja o acesso é somente por T-SQL.
Na verdade precisamor especificar
EXEC sp_configure‘filestream_access_level’, 2
reconfigure
go
Isso ocorre porque o nível de acesso 2 nos permite duas vias de acesso TSQL + Win32 tanto local como Remoto.
Logo após o nível de acesso ser trocado, podemos acessar a pasta.
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb8.png?w=564&h=28
(Figura 12 – Local de Armazenamento dos Arquivos no FileTable.)
Se preferir acessar pelo SSMS
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb9.png?w=225&h=321
(Figura 13 – Acessando as informações do FileTable pelo SSMS.)
Como a tabela foi criada, junto a isso são criadas uma série de campos para controle das informações que serão geridas no FileTable.
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb10.png?w=280&h=287
(Figura 14 – Informações da tabela Documentos.)
Logo após toda a estrutura criada, irei copiar uma pasta chamada documentos contendo diversos arquivos, alêm de copiar diversos arquivos de SQL Server e logo após isso iremos realizar uma consulta na tabela.
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb11.png?w=488&h=208
(Figura 15 – Copiando os Arquivos para o compartilhamento do FileTable.)
SELECT * FROM BaseDocumentosSQL.dbo.Documentos
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb12.png?w=728&h=117
(Figura 16 – Arquivos copiados dentro do SQL Server.)
Ou seja bastou somente que eu copiasse os arquivos para que os mesmos aparecessem dentro do SQL Server, com isso agora podemos nos beneficiar de todos os recursos do SQL Server.
Utilizando o Full-Text Search e Semantic-Search no SQL Server 2012
Se você possui alguma dúvida em relação ao Full-Text Search veja este arquivo que escrevi –http://luanmorenodba.wordpress.com/2011/10/26/entenda-o-full-text-search/
Neste momento iremos realizar a criação da estrutura do FULL-Text Seach, assim será criado um Catálogo um índíce UNIQUE e finalmente o FULL-Text Index.
USEBaseDocumentosSQL
GO
* *
EXEC sp_fulltext_service‘load_os_resources’,1
EXEC sp_fulltext_service‘restart_all_fdhosts’
Antes de criar o FULL-Text em cima da tabela documentos que contem FileTables, iremos utilizar o novo recurso do SQL Server 2012, o property list
Esse novo recurso possibilita a criação de uam lista que será utilizada para pesquisar detalhes de um arquivo dentro da tabela.
CREATE SEARCH PROPERTY LIST PropriedadesDocumentos;
GO
CREATE FULLTEXT CATALOG CatalogoDadosSQL AS DEFAULT
GO
CREATE UNIQUE INDEX idxNCLFTS_Documentos_stream_id
ON BaseDocumentosSQL.dbo.Documentos(stream_id)
GO
*CREATE FULLTEXT INDEX *
ON BaseDocumentosSQL.dbo.Documentos
(
* file_stream TYPE COLUMN*
* file_type LANGUAGE 1033 statistical_semantics*
)
* KEY INDEX idxNCLFTS_Documentos_stream_id*
* ON CatalogoDadosSQL*
* WITH SEARCH PROPERTY LIST = PropriedadesDocumentos*
GO
Reparem que agora na criação do FULL-Text Index é especificado a coluna file_stream e o file_type contendo statistical_semantics que será utilizando para a criação de estatísticas semânticas para busca de similaridades entre arquivos. (Para maiores informações sobre este comando acesse: http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(CREATE_FULLTEXT_INDEX_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true)
Agora iremos alterar a lista de propriedades que é chamadas de IFilters que suportam as seguintes extensões: *.docx, xlsx, docx e ppt, *para assim criar as seguintes propriedades.
ALTER SEARCH PROPERTY LIST PropriedadesDocumentos
ADD‘Title’
WITH
( PROPERTY_SET_GUID = ‘F29F85E0-4FF9-1068-AB91-08002B27B3D9′,
* PROPERTY_INT_ID = 2,*
* PROPERTY_DESCRIPTION = ‘System.Title – Title of the item.’*
);
* *
ALTER SEARCH PROPERTY LIST PropriedadesDocumentos
ADD‘Author’
WITH
( PROPERTY_SET_GUID = ‘F29F85E0-4FF9-1068-AB91-08002B27B3D9′,
* PROPERTY_INT_ID = 4,*
* PROPERTY_DESCRIPTION = ‘System.Author – Author or authors of the item.’*
);
* *
ALTER SEARCH PROPERTY LIST PropriedadesDocumentos
ADD‘Tags’
WITH
( PROPERTY_SET_GUID = ‘F29F85E0-4FF9-1068-AB91-08002B27B3D9′,
* PROPERTY_INT_ID = 5,*
* PROPERTY_DESCRIPTION = ‘System.Keywords – Keywords (Tags) of the item.’*
);
GO
Agora é possível realizar pesquisas em cima dos arquivos utilizando propriedades como Autor, Título e Tags dos arquivos dentro do FileTable.
Neste momento, iremos editar alguns documetos, colocarei em todos o Título: SQL Server e a tag: Informações
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb13.png?w=456&h=211
(Figura 17 – Editando os arquivos dentro do compartilhamento do FileTable (1).)
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb14.png?w=391&h=303
(Figura 18 – Editando os arquivos dentro do compartilhamento do FileTable (2).)
Para todos esses arquivos temos as seguintes informações mostradas acima. Logo após isso é necessário realizar o REBUILD do FULL-Text Search para que as alterações seja refletidas dentro do SQL Server.
ALTER FULLTEXT INDEX ON dbo.Documentos
START FULL POPULATION;
GO
Utilizando a consulta que acessa as propriedades dos documentos que acabamos de vincular, podemos visualizar..
SELECT name DocumentName, file_stream.GetFileNamespacePath() Path FROM dbo.Documentos
WHERE CONTAINS(PROPERTY(file_stream, ‘Title’), ‘SQL AND Server’)
SELECT name DocumentName, file_stream.GetFileNamespacePath() Path FROM dbo.Documentos
WHERE CONTAINS(PROPERTY(file_stream, ‘Tags’), ‘Informações’)
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb15.png?w=483&h=96
(Figura 19 – Arquivos após busca pelas propriedades de Título e Tag)
Mais alguns recursos são disponibilizados, dentre eles temos:
Essa função retorna linhas de acordo com Key Phrases ou seja quantidade de valores que aparecem com maior frequência dentro do arquivo.
SELECT name, document_key, keyphrase, AVG(score) AS score
FROM SEMANTICKEYPHRASETABLE (dbo.Documentos, *)
INNER JOIN dbo.Documentos
ON stream_id = document_key
GROUP BY name, document_key, keyphrase
ORDER BY name, score DESC
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb16.png?w=562&h=199
(Figura 20 – Busca utilizando semantickeyphrasetable)
Retorna um ou mais valores dos documentos no qual o conteúdo de uma coluna é semanticalmente similar a outro documento.
DECLARE @Title NVARCHAR(1000)
DECLARE @DocID UNIQUEIDENTIFIER
* *
SET @Title = ‘Dpm2007 Datasheet SQL Server.docx’
* *
SELECT @DocID = stream_id
FROM dbo.Documentos
WHERE name = @Title
* *
SELECT @Title AS SourceTitle, name AS MatchedTitle, stream_id, score
FROM SEMANTICSIMILARITYTABLE(dbo.Documentos, *, @DocID)
*INNER JOIN dbo.Documentos *
ON stream_id = matched_document_key
ORDER BY score DESC
GO
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb17.png?w=570&h=197
(Figura 21 – Busca utilizando semanticsimilaritytable)
semanticsimilaritydetailstable
Retorna um ou mais valores entre dois documentos no qual o conteúdo de uma coluna é semanticalmente similar a outro documento, sendo um chamado de source e outro chamado de matched.
DECLARE @SourceTitle NVARCHAR(1000)
DECLARE @MatchedTitle NVARCHAR(1000)
DECLARE @SourceDocID UNIQUEIDENTIFIER
DECLARE @MatchedDocID UNIQUEIDENTIFIER
* *
SET @SourceTitle = ‘SQL2008 – Manageability.doc’
SET @MatchedTitle = ‘Whitepaper SQL Server 2012 – AlwaysOn.docx’
* *
SELECT @SourceDocID = stream_id FROM dbo.Documentos WHERE name = @SourceTitle
SELECT @MatchedDocID = stream_id FROM dbo.Documentos WHERE name = @MatchedTitle
* *
SELECT @SourceTitle AS SourceTitle, @MatchedTitle AS MatchedTitle, keyphrase, score
FROM SEMANTICSIMILARITYDETAILSTABLE(dbo.Documentos, file_stream, @SourceDocID,file_stream, @MatchedDocID)
ORDER BY score DESC
http://luanmorenodba.files.wordpress.com/2012/04/image_thumb18.png?w=571&h=246
(Figura 22 – Busca utilizando semanticsimilaritydetailstable)
Vemos claramente que a utilização de dados não-estruturados dentro do SQL Server 2012 passar a ser mais fácil, leve e otimizada, possibilitando uma maior administração e eficiência destes dados.
Fontes:
http://en.wikipedia.org/wiki/Big_data
http://en.wikipedia.org/wiki/Unstructured_data
http://msdn.microsoft.com/en-us/library/ff929144(v=sql.110).aspx