MySQL Administrator: criando índices e manipulando dados

Exportação de dados - Apresentação

Olá pessoal, tudo bem? Meu nome é Victorino Vila e irei te acompanhar neste curso de MySQL Administrator: Criando Índices e Manipulando Dados.

Audiodescrição: Victorino é uma pessoa de pele clara e olhos escuros. Seus cabelos são curtos, lisos e grisalhos. Usa barba e bigode e está com uma camiseta azul-marinho. Ao fundo, parede azul sem decorações.

O que você vai aprender?

Este curso é voltado para pessoas que desejam aprofundar seus conhecimentos na administração de ambientes MySQL.

Serão abordadas neste curso duas das funções fundamentais para quem administra dados, que servem para ajudar às pessoas desenvolvedoras na otimização de consultas e na manipulação de grandes volumes de dados.

No que diz respeito à manipulação de dados, você aprenderá como exportar dados de um banco MySQL para arquivos externos e como transferi-los desses arquivos externos de volta para outras bases de dados. Isso porque o processo de integração e transferência de dados é essencial em qualquer ambiente MySQL.

Outro ponto importante que abordaremos é sobre a performance das consultas. O DBA (Database administrator ou administrador de banco de dados) é o profissional que auxilia as pessoas desenvolvedoras a melhorar a performance das consultas, principalmente utilizando índices.

Para usar índices de forma clara e eficaz, o DBA precisa aprender como esses índices funcionam, como o índice consegue localizar o dado dentro das tabelas de forma mais rápida e como mensurar a performance de uma consulta para verificar se suas ações estão realmente otimizando os processos.

Tudo isso vocês aprenderão neste curso, sempre utilizando a empresa Sucos S.A., uma base de dados criada no primeiro curso desta formação.

Trabalharemos em um caso prático: vamos criar um banco de dados departamental da empresa Sucos S.A. para uma diretoria de vendas da empresa.

Vamos transferir os dados segmentados para essa base e, em seguida, essa pessoa usuária da diretoria de vendas nos solicitará a melhora da performance de um relatório importante que ela usa, o de obter as vendas dos produtos dentro de um determinado ano para determinado cliente.

Temos certeza de que você sairá deste curso lidando com consultas SQL de outra forma e também sabendo como transferir grandes volumes de dados entre bancos MySQL de maneira fácil e rápida.

O que você já deve saber?

Para aproveitar bem o conteúdo deste curso, recomendamos que você tenha um conhecimento básico de SQL.

De preferência, que tenha feito a formação de MySQL Desenvolvedor que oferecemos na Alura e, claro, que já tenham concluído o primeiro curso da formação MySQL Administrador.

Não se preocupe se você achar que esse assunto é muito difícil para você. Afinal, te acompanharemos em todas as aulas. Vamos começar? Até o próximo vídeo!

Exportação de dados - Entendendo o cenário de trabalho

Download Base_departamental.sql

Vamos continuar desempenhando o papel de DBA (Database Administrator) da empresa Sucos S.A.

No curso anterior, da formação de MySQL Administrador, trabalhamos com a base de dados sucos_vendas. Essa é uma base de dados grande, com muitas linhas de dados. Nosso desafio, como DBA, é segmentar essa base para atender a um sistema específico de uma diretoria de vendas.

Entendendo o cenário de trabalho

Para exemplificar, vamos observar a tabela_de_vendedores na base de dados sucos_vendas. Basta executar a seguinte consulta em um script no MySQL Workbench:

SELECT * FROM tabela_de_vendedores

Ao executar essa consulta, clicando no botão com ícone de raio na barra de ferramentas, notamos que existem quatro diretorias de vendas.

Vamos trabalhar com a primeira, a diretoria de vendas 00235. O objetivo é fazer com que essa diretoria gerencie suas próprias vendas.

A base original é muito grande, portanto, precisamos isolar os dados dessa base original para enviá-los para outra base de dados menor, somente com dados deste vendedor.

Então, o que vamos fazer?

1. Criar uma nova base de dados;
2. Criar novas tabelas;
3. Exportar os dados da base de dados sucos_vendas;
4. Importar nessa base de dados departamental.

Criando nova base de dados

No MySQL Workbench, numa área vazia qualquer, vamos clicar com o botão direito do mouse e selecionar "Create Schema". Com isso, podemos colocar o nome da base como sucos_vendas_00235, que é o identificador deste vendedor.

Clicamos no botão "Apply" no canto inferior direito, depois revisamos os dados e apertamos em "Apply" novamente, e, por fim, clicamos em "Finish" para finalizar a operação.

No painel do lado esquerdo, já aparece a nova base sucos_vendas_00235. Vamos dar um duplo clique sobre ela para poder deixá-la como a base ativa do nosso servidor.

Criando novas tabelas

Vamos agora criar as tabelas nesta nova base.

Nas atividades dessa aula, disponibilizaremos um link para fazer o download do arquivo Base_departamental.sql. Abra esse arquivo com um editor de texto, copie os comandos que estão dentro desse arquivo e cole no script dentro do MySQL Workbench.

CREATE TABLE `tabela_de_produtos` (
  `CODIGO_DO_PRODUTO` varchar(10) NOT NULL,
  `NOME_DO_PRODUTO` varchar(50) DEFAULT NULL,
  `EMBALAGEM` varchar(20) DEFAULT NULL,
  `TAMANHO` varchar(10) DEFAULT NULL,
  `SABOR` varchar(20) DEFAULT NULL,
  `PRECO_DE_LISTA` float NOT NULL
) ;

CREATE TABLE `tabela_de_clientes` (
  `CPF` varchar(11) NOT NULL,
  `NOME` varchar(100) DEFAULT NULL,
  `ENDERECO_1` varchar(150) DEFAULT NULL,
  `ENDERECO_2` varchar(150) DEFAULT NULL,
  `BAIRRO` varchar(50) DEFAULT NULL,
  `CIDADE` varchar(50) DEFAULT NULL,
  `ESTADO` varchar(2) DEFAULT NULL,
  `CEP` varchar(8) DEFAULT NULL,
  `DATA_DE_NASCIMENTO` date DEFAULT NULL,
  `IDADE` smallint DEFAULT NULL,
  `SEXO` varchar(1) DEFAULT NULL,
  `LIMITE_DE_CREDITO` float DEFAULT NULL,
  `VOLUME_DE_COMPRA` float DEFAULT NULL,
  `PRIMEIRA_COMPRA` bit(1) DEFAULT NULL
) ;

CREATE TABLE `notas_fiscais` (
  `CPF` varchar(11) NOT NULL,
  `DATA_VENDA` date DEFAULT NULL,
  `NUMERO` int NOT NULL,
  `IMPOSTO` float NOT NULL
) ;

CREATE TABLE `itens_notas_fiscais` (
  `NUMERO` int NOT NULL,
  `CODIGO_DO_PRODUTO` varchar(10) NOT NULL,
  `QUANTIDADE` int NOT NULL,
  `PRECO` float NOT NULL
) ;

Com esse comando, vamos criar quatro tabelas: a tabela_de_produtos, a tabela_de_clientes, a tabela de notas_fiscais e a tabela de itens_notas_fiscais.

Lembre-se que não precisamos criar a tabela de vendedores nesta base departamental, porque todos os dados já são do vendedor 00235.

De modo proposital, notem que essas tabelas que estão sendo criadas não possuem nem chave primária, nem chave estrangeira. Isso será importante no decorrer do curso.

Após nos certificarmos que estamos com a base sucos_vendas_00235 selecionada, vamos selecionar todos esses comandos e executá-los.

O script rodou com sucesso. Se clicamos com o botão direito do mouse em "Tables" dentro da base de dados no painel lateral esquerdo e clicamos em "Refresh All", serão listadas as quatro tabelas dessa nova base.

Próximos passos

O que faremos a seguir é transferir os dados de uma base de dados para outra. Como estamos no mesmo servidor, poderíamos até fazer isso via SQL diretamente, mas um dos temas desse curso é manipulação de dados dentro do MySQL.

Por isso, vamos supor que essas duas bases estejam em servidores diferentes e máquinas que às vezes não se comunicam. Desse modo, vamos precisar transferir arquivos de dados de uma base para outra.

Essa é uma função importante da pessoa administradora do MySQL, fazer transferências de dados entre bases. Vamos demonstrar nos próximos vídeos como vamos fazer a exportação e importação usando diversas maneiras.

Exportação de dados - Exportando dados para CSV usando o mysqldump

Embora os bancos de dados sejam excelentes ferramentas para armazenar e gerenciar dados, muitas vezes precisamos extrair alguns dados do banco de dados para usá-los em outra ferramenta externa, como, por exemplo, uma planilha de Excel. Ou então, às vezes, precisamos exportar os dados para um arquivo externo que seja lido por uma ferramenta de BI.

O MySQL possui diversas formas de realizar esse processo, ou seja, de exportar dados para uma fonte externa em diversos formatos.

Conhecendo mysqldump

Voltando ao nosso problema da base de dados sucos_vendas, temos a base de dados corporativa e a base local do vendedor 00235. Vamos fazer esse processo de exportar dados da base corporativa para a base local, usando como exemplo a tabela_de_clientes.

Vamos usar uma ferramenta muito utilizada por DBAs para fazer essas exportações, que é o mysqldump.

O mysqldump é uma ferramenta que permite exportar todo o banco de dados, ou apenas parte dele, ou seja, algumas tabelas, do MySQL para um arquivo externo que contenha instruções SQL.

Quando exportamos os dados, teremos nesse arquivo exportado uma série de comandos INSERT para incluir os dados de volta dentro de outro banco de dados. Esse outro banco de dados precisará ter uma tabela igual àquela que exportamos.

Porém, se vamos exportar esses dados com o comando INSERT, teremos que, obrigatoriamente, importar em outro banco de dados MySQL, que é o nosso caso. Mas talvez funcione executar esses comandos INSERT em outro banco de dados do tipo SQL Server ou Oracle.

Vamos levar em consideração o pior caso. Apesar de saber que vamos exportar isso para um banco de dados MySQL, não queremos exportar esse arquivo usando comandos INSERT. Logo, o mysqldump permite exportar os dados no formato CSV.

Para quem não sabe, o formato CSV (Comma-separated values ou valores separados por vírgulas) é um arquivo texto onde na primeira linha teremos um cabeçalho com o nome das colunas e, cada linha subsequente, é uma linha da tabela de dados. Sendo que todas essas informações são separadas por um caractere separador - muitas vezes usamos o ponto e vírgula.

Se usarmos a cláusula --tab no comando mysqldump, dois arquivos serão criados: um com extensão SQL, com os dados para você poder criar a tabela do banco destino, e outro com extensão .txt, que são os dados no formato CSV.

Não vamos precisar depois usar o arquivo .sql, já que a tabela de clientes já foi criada na nossa base departamental.

Exportando dados de uma tabela para CSV

Na nossa máquina, já criamos um diretório chamado C:\export para poder exportar o dado para este diretório. Vamos, então, executar o mysqldump.

Para isso, vamos acessar a linha de comando do Windows, apertando a tecla "Windows" para abrir o menu, buscando por "cmd" e selecionando "prompt de comando".

No terminal, navegaremos até o diretório onde o MySQL está instalado. No nosso caso, navegamos até o diretório \Program Files\My SQL\MySQL Server 8.0\bin através do comando cd.

cd \
cd "Program Files"
cd MySQL
cd "MySQL Server 8.0"
cd bin

Se executamos apenas mysqldump, teremos uma explicação de como esse comando é usado:

mysqldump

Usage: mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2, DB3…]

OR mysqldump [OPTIONS] --all-databases [OPTIONS]

Vamos acrescentar várias opções. Após o comando mysqldump, acrescentaremos -u root que é o nosso usuário, seguido de -p sucos_vendas tabela_de_clientes, ou seja, o nome da base de origem e o nome da tabela cujos dados serão exportados.

Depois, colocaremos a cláusula --tab igual a C:/export/, entre aspas. Assim, vamos redirecionar para o diretório onde vamos gravar esses arquivos. Note que não usamos a barra invertida.

Também usaremos uma cláusula chamada --fields-terminated-by que será igual ao caractere que vai separar os dados, entre aspas. Nesse caso, escolhemos o ponto e vírgula (;).

Por fim, digitaremos --lines-terminated-by que será igual ao caractere que vai fazer a quebra de linha. Será \r\n, entre aspas e com barra invertida.

mysqldump -u root -p sucos_vendas tabela_de_clientes --tab="C:/export/" --fields-terminated-by=";" --lines-terminated-by="\r\n"

Após dar "Enter", devemos digitar a nossa senha.

mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

Encontramos um erro 1290, informando que o servidor MySQL está rodando com a opção --secure-file-priv. Essa variável determina que não podemos salvar um arquivo externo no diretório C:/export/ - exceto se esse diretório seja definido nesta variável.

Assim como aprendemos no curso anterior da formação, podemos verificar o valor dessa variável de sistema no MySQL Workbench, com o comando SHOW VARIABLES LIKE e o secure_file_priv, entre aspas. Atente-se que o nome da variável dentro do banco leva underscore (_) e não um traço (-).

SHOW VARIABLES LIKE 'secure_file_priv';

Note que o diretório onde ele vai salvar é o C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\. Isso está definido no arquivo my.ini, quando subimos o banco.

Se quisermos modificar esse diretório para C:/export/, teríamos que buscar pela variável secure-file-priv no arquivo C:\ProgramData\MySQL\MySQL Server 8.0\my.ini e mudar seu valor. Com isso, seria preciso parar o banco e subi-lo novamente.

Ou podemos simplesmente salvar no diretório que já está configurado nessa variável. No terminal, usamos a seta para cima para repetir o comando anterior, substituindo o valor de --tab para C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/, invertendo as barras.

mysqldump -u root -p sucos_vendas tabela_de_clientes --tab="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/" --fields-terminated-by=";" --lines-terminated-by="\r\n"

Podemos apertar "Enter", digitar a senha e executar o comando.

Para conferir que o comando foi bem-sucedido, vamos acessar o diretório "Uploads" e conferir se os dois arquivos, tabela_de_clientes.sql e tabela_de_clientes.txt, já estão lá.

No primeiro arquivo, temos o comando para criar a base de dados.

tabela_de_clientes.sql parcialmente transcrito:

-- MySQL dump 10.13  Distrib 8.0.37, for Win64 (x86_64)
--
-- Host: localhost    Database: sucos_vendas
-- ------------------------------------------------------
-- Server version       8.0.37

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `tabela_de_clientes`
--

DROP TABLE IF EXISTS `tabela_de_clientes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tabela_de_clientes` (
  `CPF` varchar(11) NOT NULL,
  `NOME` varchar(100) DEFAULT NULL,
  `ENDERECO_1` varchar(150) DEFAULT NULL,
  `ENDERECO_2` varchar(150) DEFAULT NULL,
  `BAIRRO` varchar(50) DEFAULT NULL,
  `CIDADE` varchar(50) DEFAULT NULL,
  `ESTADO` varchar(2) DEFAULT NULL,
  `CEP` varchar(8) DEFAULT NULL,
  `DATA_DE_NASCIMENTO` date DEFAULT NULL,
  `IDADE` smallint DEFAULT NULL,
  `SEXO` varchar(1) DEFAULT NULL,
  `LIMITE_DE_CREDITO` float DEFAULT NULL,
  `VOLUME_DE_COMPRA` float DEFAULT NULL,
  `PRIMEIRA_COMPRA` bit(1) DEFAULT NULL,
  PRIMARY KEY (`CPF`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

E, no outro arquivo, temos os dados, separados por ponto e vírgula.

tabela_de_clientes.txt parcialmente transcrito:

1471156710;Érica Carvalho;R. Iriquitia;;Jardins;São Paulo;SP;80012212;1990-09-01;27;F;170000;24500;
19290992743;Fernando Cavalcante;R. Dois de Fevereiro;;Água Santa;Rio de Janeiro;RJ;22000000;2000-02-12;18;M;10000;20000;
2600586709;César Teixeira;Rua Conde de Bonfim;;Tijuca;Rio de Janeiro;RJ;22020001;2000-03-12;18;M;120000;22000;
3623344710;Marcos Nogueira;Av. Pastor Martin Luther King Junior;;Inhauma;Rio de Janeiro;RJ;22002012;1995-01-13;23;M;110000;22000;
492472718;Eduardo Jorge;R. Volta Grande;;Tijuca;Rio de Janeiro;RJ;22012002;1994-07-19;23;M;75000;9500;
50534475787;Abel Silva ;Rua Humaitá;;Humaitá;Rio de Janeiro;RJ;22002112;1995-09-11;22;M;170000;26000;

Próximos passos

Com o arquivo TXT salvo, podemos usá-lo como fonte para recuperar na base local.

Porém, esta não é a única maneira, e talvez nem a melhor maneira, de exportar um arquivo texto a partir de uma outra base de dados MySQL.

No próximo vídeo, vamos exportar os dados de movimento de notas fiscais do vendedor 00235, porém não usando o mysqldump e, sim, de uma outra forma.

Sobre o curso MySQL Administrator: criando índices e manipulando dados

O curso MySQL Administrator: criando índices e manipulando dados possui 205 minutos de vídeos, em um total de 59 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Exportação de dados
• Importação e manipulação de dados
• Como as consultas são resolvidas pelo MYSQL
• Conceitos e implementação de índices
• Chaves e performance