Trabalhando com Dados Não-Estruturados no SQL Server 2012

Posted on abril 2, 2012

5


1

(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

Big Data

(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

Unstructured Data1

(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

image

(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.

image

(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.

image

(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.

USEBaseDocumentosSQL

GO

 

SELECT*

FROM sys.database_filestream_options AS O

WHERE O.database_id = DB_ID()

image

(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

image

(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

image

(Figura 9 – Compartilhamento do FileTable.)

Para acessar esta pasta iremos em

image

(Figura 10 – Local Físico do Compartilhamento do FileTable.)

Porém quando tentamos acessar essa pasta, nos deparamos com a seguinte informação

image

(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.

image

(Figura 12 – Local de Armazenamento dos Arquivos no FileTable.)

Se preferir acessar pelo SSMS

image

(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.

image

(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.

image

(Figura 15 – Copiando os Arquivos para o compartilhamento do FileTable.)

SELECT * FROM BaseDocumentosSQL.dbo.Documentos

image

(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 – https://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

image

(Figura 17 – Editando os arquivos dentro do compartilhamento do FileTable (1).)

image

(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’)

image

(Figura 19 – Arquivos após busca pelas propriedades de Título e Tag)

Mais alguns recursos são disponibilizados, dentre eles temos:

semantickeyphrasetable

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

image

(Figura 20 – Busca utilizando semantickeyphrasetable)

semanticsimilaritytable

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

image

(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

image

(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