É possível fazer análise de big data com MySQL?

Sim. Na realidade, o MySQL já é usado na análise de big data (ou megadados) — tanto quanto o PostgreSQL.
No caso do MySQL, as estruturas InnoDB organizadas em índices oferecem vantagens para as atividades de análise de megadados — boa estrutura name-value-pair (ou par-valor-nome), que é mais útil para análise do que os índices secundários.
Para fazer análises de big data, você passa por processo semelhante ao que passaria usando o Hadoop, por exemplo:

  • organizar preliminarmente os dados
  • criar estruturas otimizadas especificametne para a sua análise
  • criar a estrutura de busca

Com um grande “parque de máquinas”, é desejável paralelizar o processo e usar scripts de análise que o façam localmente, em cada instância — com um subprocesso de agregação dos resultados ao final.
Se for usar o Hadoop de forma mais tradicional sobre dados desestruturados, use o MySQL para armazenar os resultados agregados pelo Hadoop.

Soluções de análise de big data com o MySQL

De maneira geral, há 2 maneiras de realizar a análise de megadados no MySQL:

  • O MySQL pode ser usado em conjunto com um sistema mais tradicional para lidar com big data — como o Hadoop.
    Grandes quantidades de dados podem ser armazenados em HDFS e processados com o Hadoop — com o resultado armazenado no MySQL, para a análise.

    O HDFS (Hadoop Distributed File System) é um sistema de arquivos distribuído, que armazena dados em commodity machines oferecendo grande largura de banda para fluxo de dados em um cluster.

    Por exemplo, a métrica crua pode ser armazenada em HDFS, mas os dados agregados e sumarizados são enviados ao MySQL, onde são analisados.

  • O MySQL pode ser usado como um grande armazém de dados.
    Tipicamente, ele ficará fragmentado para suportar tal volume de dados.
    Neste caso, os mecanismos de armazenamento mais adequados são o InnoDB ou o TokuDB. Alternativamente, os mecanismos InfoBright ou InfiniDB também podem ser empregados.

Sendo o banco de dados de código aberto líder na web em aplicações baseadas na nuvem (cloud based applications), o MySQL se integra às plataformas de big data.
O uso de soluções MySQL com Hadoop é o que ajuda a resolver problemas envolvendo big data.
Referências sobre o Hadoop: http://hadoop.apache.org/docs/r1.2.1/hdfs_design.html.

Buscas FULLTEXT requerendo ou excluindo palavras no MySQL

As buscas FULLTEXT Index, no MySQL, são indicadas para encontrar strings (cadeias de caracteres) dentre grandes volumes de texto no banco de dados — por serem extremamente eficientes e entregarem o resultado rápido.
O recurso é flexível o suficiente para permitir que palavras sejam incluídas ou excluídas da busca.
Nos exemplos, que seguem, vou usar a minha tabela ‘biblia’ — justamente por que ela tem um volume de texto consideravelmente grande. Contudo, você pode readequar todos os exemplos aos bancos de dados que tiver disponíveis.
mysql logo over gray ubuntu wallpaper
Se você sentir alguma dificuldade em entender o assunto, sugiro ler o artigo Introdução a buscas FULLTEXT.
Normalmente, as pesquisas FULLTEXT retornam registros que contém uma das palavras incluídas na string de busca, ainda que uma delas não esteja presente no registro.
O exemplo, abaixo, retorna registros que contenham um dos nomes Davi ou Golias:

SELECT COUNT(*) FROM biblia WHERE MATCH(textover) AGAINST('Davi Golias');
+----------+
| COUNT(*) |
+----------+
|      895 |
+----------+
1 row in set (0.00 sec)

No meu banco de dados, há 895 registros contendo ‘Davi’ e/ou ‘Golias’.
Abaixo, segue uma pequena amostra do conteúdo da minha tabela “biblia”, onde é possível ver que nem todos os versículos têm os dois personagens citados simultaneamente em seus textos:

SELECT numliv AS 'Livro',
    -> numcap AS 'Capítulo',
    -> numver AS 'Versículo',
    -> textover AS 'Texto do versículo'
    -> FROM biblia WHERE MATCH(textover)
    -> AGAINST('Davi Golias') LIMIT 5\G;
*************************** 1. row ***************************
              Livro: 9
          Capítulo: 17
         Versículo: 23
Texto do versículo: Enquanto ainda falava com eles, eis que veio subindo do exército dos filisteus o campeão, cujo nome era Golias, o filisteu de Gate, e falou conforme aquelas palavras; e Davi as ouviu.
*************************** 2. row ***************************
              Livro: 9
          Capítulo: 21
         Versículo: 9
Texto do versículo: Respondeu o sacerdote: A espada de Golias, o filisteu, a quem tu feriste no vale de Elá, está aqui envolta num pano, detrás do éfode; se a queres tomar, toma-a, porque não há outra aqui senão ela. E disse Davi: Não há outra igual a essa; dá-ma.
*************************** 3. row ***************************
              Livro: 9
          Capítulo: 22
         Versículo: 10
Texto do versículo: o qual consultou por ele ao Senhor, e lhe deu mantimento, e lhe deu também a espada de Golias, o filisteu.
*************************** 4. row ***************************
              Livro: 9
          Capítulo: 17
         Versículo: 4
Texto do versículo: Então saiu do arraial dos filisteus um campeão, cujo nome era Golias, de Gate, que tinha de altura seis côvados e um palmo.
*************************** 5. row ***************************
              Livro: 13
          Capítulo: 20
         Versículo: 5
Texto do versículo: Tornou a haver guerra com os filisteus; e El-Hanã, filho de Jair, matou Lami, irmão de Golias, o giteu, cuja lança tinha a haste como órgão de tecelão,
5 rows in set (0.01 sec)

Se você deseja ver apenas os resultados em que as duas palavras estejam presentes, o resultado obtido, até agora, é indesejável.
Para obter um resultado mais relevante, reescreva a declaração com o uso do operador AND, para juntar as condições de pesquisa. Veja como:

SELECT numliv AS 'Livro', numcap AS 'Capítulo', numver AS 'Versículo', textover AS 'Texto do versículo' FROM biblia WHERE MATCH(textover) AGAINST ('Davi') AND MATCH(textover) AGAINST ('Golias')\G;

A consulta irá retornar 2 resultados (na minha versão):

*************************** 1. row ***************************
              Livro: 9
          Capítulo: 17
         Versículo: 23
Texto do versículo: Enquanto ainda falava com eles, eis que veio subindo do exército dos filisteus o campeão, cujo nome era Golias, o filisteu de Gate, e falou conforme aquelas palavras; e Davi as ouviu.
*************************** 2. row ***************************
              Livro: 9
          Capítulo: 21
         Versículo: 9
Texto do versículo: Respondeu o sacerdote: A espada de Golias, o filisteu, a quem tu feriste no vale de Elá, está aqui envolta num pano, detrás do éfode; se a queres tomar, toma-a, porque não há outra aqui senão ela. E disse Davi: Não há outra igual a essa; dá-ma.
2 rows in set (0.00 sec)

Use o modo booleano para adicionar termos às suas buscas FULLTEXT no MySQL

O MySQL permite usar o modo booleano para pesquisar a ocorrência de múltiplas palavras. Para fazer uso dele, basta preceder cada palavra com um sinal de adição ‘+’ e acrescentar IN BOOLEAN MODE ao final da string:

SELECT COUNT(*) FROM biblia WHERE MATCH(textover) AGAINST('+Davi +Golias' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

O modo booleano também pode ser usado para excluir palavras da busca. Basta usar o sinal de subtração ‘-‘. Veja como:

SELECT COUNT(*) FROM biblia WHERE MATCH(textover) AGAINST('-Davi +Golias' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

Use o modo booleano do MySQL com curingas

Um outro recurso útil do modo booleano do MySQL é a possibilidade de uso de curingas (wildcards). Ao acrescentar um sinal ‘*’ ao final de uma palavra, você vai obter todos os resultados que a contiverem.
A consulta abaixo, conta todas as ocorrências, na tabela ‘bíblia’ que começam com ‘serv’ — o que inclui servo, serva, servidão, serviço etc:

SELECT COUNT(*) FROM biblia WHERE MATCH(textover) AGAINST('serv*' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|     1435 |
+----------+
1 row in set (0.11 sec)

Introdução a buscas via FULLTEXT no MySQL

Usar o casamento de padrões para encontrar informações dentro dos campos de uma tabela é eficiente, como foi demonstrado no post Como encontrar padrões entre os seus dados no MySQL. À medida em que o volume de informação (ou de texto) aumenta, a operação de busca vai se tornar mais lenta.
MySQL logo Dolphin in gray shades
A tarefa de buscar um mesmo trecho dentro de várias strings é também muito comum — e não é muito eficiente através do casamento de padrões — principalmente em bancos de dados volumosos.

SELECT * FROM clientesCidades WHERE coluna1 LIKE 'padrao' OR  coluna1 LIKE 'padrao' OR  coluna2 LIKE 'padrao OR coluna3 LIKE 'padrao'' ... 

Imagine fazer isto para 10 colunas ou mais.
Uma alternativa melhor é usar a busca FULLTEXT, projetada para sondar grandes volumes de texto — em múltiplas colunas simultaneamente.
Para poder usar este recurso, é necessário adicionar o índice FULLTEXT à sua tabela.
Feito isto, é possível usar o operador MATCH para procurar por strings dentro das colunas.
Tabelas MYISAM podem fazer uso do recurso FULLTEXT, nos campos do tipo CHAR, VARCHAR ou TEXT.
A melhor forma de criar exemplos de uso do FULLTEXT é usar uma tabela com uma grande quantidade de texto. Se você não tiver uma disponível, tente encontrar algum repositório na Internet com informações em texto.
Nos exemplos, que seguem, vou usar a bíblia em modo texto, que baixei daqui — pelo fato de ela estar muito bem organizada e ter uma grande quantidade de texto para nosso uso.
Vejo um trecho-exemplo:

livro original capítulo original versículo original texto
010 5 7 Viveu Sete, depois que gerou a Enos, oitocentos e sete anos; e gerou filhos e filhas.
010 5 8 Todos os dias de Sete foram novecentos e doze anos; e morreu.
010 5 9 Enos viveu noventa anos, e gerou a Quenã.

Você pode usar qualquer outra tabela que tiver disponível, com uma grande quantidade de texto. Só não esqueça de adequar os exemplos deste tutorial ao seu caso.

Como importar dados em texto pro MySQL

Notou que cada coluna pode ser um campo para uma tabela no banco de dados MySQL?
Que campos são estes?
Cada registro, pode ser compartimentado sob as seguintes colunas:

  1. O número do livro: 010, 020 etc.;
  2. O número do capítulo;
  3. O número do versículo e
  4. O texto do versículo.

—Os números dos livros correspondem aos nomes (em outra tabela): 010 – Gênesis, 020 – Exodus etc.
Esta relação não será abordada neste tutorial.


Antes de prosseguir, vou criar uma tabela para receber os dados do texto.
Dentro do console MySQL, crie uma tabela da seguinte forma:

CREATE TABLE biblia
(
numliv TINYINT UNSIGNED NOT NULL, 
numcap TINYINT UNSIGNED NOT NULL, 
numver TINYINT UNSIGNED NOT NULL, 
textover TEXT NOT NULL 
)
TYPE = MyISAM;

Verifique a tabela:

EXPLAIN biblia;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| numliv   | tinyint(3) unsigned | NO   |     | NULL    |       |
| numcap   | tinyint(3) unsigned | NO   |     | NULL    |       |
| numver   | tinyint(3) unsigned | NO   |     | NULL    |       |
| textover | text                | NO   |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Feito isto, importe o arquivo informando seu nome e o caminho completo através da declaração LOAD DATA LOCAL INFILE, que funciona assim:

LOAD DATA LOCAL INFILE '/diretorio/arquivo.txt' INTO TABLE nome-da-tabela;

No meu exemplo, o comando ficou assim:

LOAD DATA LOCAL INFILE '/home/henry/Downloads/KJV/port/biblia.txt' INTO TABLE biblia;

Se você se deparar com a mensagem de erro The used command is not allowed with this MySQL version, leia o artigo Como resolver o erro The used command…


Se você tiver algum outro problema neste processo, tente encontrar ajuda em outros artigos neste site (use o quadro de busca) ou em outros — neste post vamos nos concentrar no uso do FULLTEXT.

Prepare sua tabela para usar o FULLTEXT

Depois de povoar a tabela com os dados importados do arquivo texto, prepare-a para usar a busca por FULLTEXT adicionando esta diretiva ao index. Isto pode ser feito no ato da criação da tabela ou, depois, com a declaração ALTER TABLE:

ALTER TABLE biblia ADD FULLTEXT (textover);
Query OK, 31110 rows affected (0.98 sec)
Records: 31110  Duplicates: 0  Warnings: 0

Embora seja possível incluir a definição index na declaração CREATE TABLE, este método (ALTER TABLE…) é mais eficiente do que povoar a tabela, previamente indexada, com um grande volume de dados.

Como fazer buscas no MySQL, usando o index

Para realizar suas pesquisas dentro de uma tabela, através do index, use a função MATCH() para nomear a coluna indexada e a função AGAINST() para especificar qual texto deve ser encontrado.
Por exemplo, se quiser saber quantas vezes o nome Elias é citado (você já teve esta curiosidade?), faça a busca dentro da coluna textover, com a seguinte declaração:

SELECT COUNT(*) from biblia WHERE MATCH(textover) AGAINST('Elias');
+----------+
| COUNT(*) |
+----------+
|      100 |
+----------+
1 row in set (0.01 sec)

Faça suas próprias tentativas, para fixar o conhecimento, antes de seguir em frente.
Se quiser ver alguns destes versículos, expostos verticalmente, para facilitar a leitura, use uma declaração como a que segue:

SELECT numcap, numver, textover FROM biblia 
    -> WHERE MATCH(textover) AGAINST('Elias') LIMIT 5\G;
*************************** 1. row ***************************
  numcap: 17
  numver: 23
textover: E Elias tomou o menino, trouxe-o do quarto à casa, e o entregou a sua mãe e disse Elias: Vês aí, teu filho vive:
*************************** 2. row ***************************
  numcap: 2
  numver: 1
textover: Quando o Senhor estava para tomar Elias ao céu num redemoinho, Elias partiu de Gilgal com Eliseu.
*************************** 3. row ***************************
  numcap: 19
  numver: 13
textover: E ao ouvi-la, Elias cobriu o rosto com a capa e, saindo, pôs-se à entrada da caverna. E eis que lhe veio uma voz, que dizia: Que fazes aqui, Elias?
*************************** 4. row ***************************
  numcap: 18
  numver: 40
textover: Disse-lhes Elias: Agarrai os profetas de Baal! que nenhum deles escape: Agarraram-nos; e Elias os fez descer ao ribeiro de Quisom, onde os matou.
*************************** 5. row ***************************
  numcap: 18
  numver: 7
textover: Quando, pois, Obadias já estava em caminho, eis que Elias se encontrou com ele; e Obadias, reconhecendo-o, prostrou-se com o rosto em terra e disse: És tu, meu senhor Elias?
5 rows in set (0.00 sec)

Ainda que os resultados estejam ordenados “corretamente” por capítulo, versos na sua query (na minha, não estão, como você pode perceber), será mera coincidência.
Por padrão, o resultado da buscia via FULLTEXT é ordenado conforme um ranking próprio de relevância.
Use a cláusula ORDER BY para ordenar seus resultados explícitamente:

SELECT numcap, numver, textover FROM biblia 
    -> WHERE MATCH(textover) AGAINST('Elias')
    -> ORDER BY numliv,numcap,numver LIMIT 5\G;

Veja como o resultado é diferente:

*************************** 1. row ***************************
  numcap: 17
  numver: 1
textover: Então Elias, o tisbita, que habitava em Gileade, disse a Acabe: Vive o Senhor, Deus de Israel, em cuja presença estou, que nestes anos não haverá orvalho nem chuva, senão segundo a minha palavra.
*************************** 2. row ***************************
  numcap: 17
  numver: 2
textover: Depois veio a Elias a palavra do Senhor, dizendo:
*************************** 3. row ***************************
  numcap: 17
  numver: 13
textover: Ao que lhe disse Elias: Não temas; vai, faze como disseste; porém, faze disso primeiro para mim um bolo pequeno, e traz-mo aqui; depois o farás para ti e para teu filho.
*************************** 4. row ***************************
  numcap: 17
  numver: 15
textover: Ela foi e fez conforme a palavra de Elias; e assim comeram, ele, e ela e a sua casa, durante muitos dias.
*************************** 5. row ***************************
  numcap: 17
  numver: 16
textover: Da vasilha a farinha não se acabou, e da botija o azeite não faltou, conforme a palavra do Senhor, que ele falara por intermédio de Elias.
5 rows in set (0.08 sec)

Como adicionar índices às outras colunas

Se houver texto relevante nas outras colunas, pode ser interessante aplicar o index a elas também. Isto poderia ser feito assim:

ALTER TABLE biblia ADD INDEX (numliv), ADD INDEX (numcap), ADD INDEX (numver);

No “exemplo bíblico”, usado aqui, isto não teria muita utilidade. Mas em outras tabelas pode ajudar a tornar as buscas mais eficientes.

Adicione mais palavras à sua busca FULLTEXT

Se já sabemos que Elias é citado 100 vezes nesta versão da bíblia, vamos descobrir quantas vezes Elias e Eliseu (o profeta que o substituiu) são citados:

SELECT COUNT(*) from biblia WHERE MATCH(textover) AGAINST('Elias Eliseu');
+----------+
| COUNT(*) |
+----------+
|      156 |
+----------+
1 row in set (0.00 sec)

E se acrescentarmos as citações a Obadias a esta conta?

SELECT COUNT(*) from biblia WHERE MATCH(textover) AGAINST('Elias Eliseu Obadias');
+----------+
| COUNT(*) |
+----------+
|      172 |
+----------+

1 row in set (0.04 sec)[/false]
Se você necessitar fazer pesquisas dentro de múltiplas colunas, simultâneamente, nomeie-as todas quando for constituir o seu index?

ALTER TABLE tabela ADD FULLTEXT (col1, col2, col3);

Conclusão

Daqui pra frente, você pode fazer sua própria busca pelos seus personagens favoritos da bíblia.
A tabela biblia pode ser usada para aprender mais sobre o MySQL — conheça as minhas receitas MySQL.