Como criar e integrar um banco de dados MySQL com o BigQuery na nuvem Google

Como criar e integrar um banco de dados MySQL com o BigQuery na nuvem Google
Larissa Dubiella
Larissa Dubiella

Compartilhe

Imagine que sua empresa não para de crescer. Coisa boa: mais clientes, parcerias, negócios fechados e a equipe aumentando, tudo fluindo como o esperado.

Com o crescimento da empresa, a quantidade de dados também aumenta exponencialmente e, com isso, chegam novos desafios para lidar com a expansão do negócio.

Então, surge a pergunta: como é possível aproveitar todo o potencial dos dados de se transformarem em conhecimento prático e aplicável, sem ter que investir horrores em infraestrutura?

Nesse artigo tem um passo a passo de como criar uma conta no Google Cloud Plataform e integrar com o banco de dados MySQL.

Assim, será possível analisar seus dados diretamente pelo BigQuery, usufruindo das inúmeras vantagens que essa ferramenta oferece.

Continue a sua leitura e venha comigo a aprender sobre como integrar um banco de Dados MySQL com o BigQuery!

Mãos à obra: Conectando BigQuery e MySQL

Lidar com a explosão de dados é um desafio comum para empresas que consideram a cultura data driven para crescer e otimizar seus processos e custos.

A resposta que engloba todos esses aspectos não é segredo: a nuvem.

Para que isso possa se realizar, é necessário seguir uma série de passos para garantir a comunicação entre a máquina local e o servidor da Google — passando por configurações de segurança, autorizações e conexões. Vem comigo?

Gif de dois personagens da animação "Zootopia" se cumprimentando com um toque de mãos.

O Google Cloud e seu servidor local se conhecendo

Banner da Escola de Data Science: Matricula-se na escola de Data Science. Junte-se a uma comunidade de mais de 500 mil estudantes. Na Alura você tem acesso a todos os cursos em uma única assinatura; tem novos lançamentos a cada semana; desafios práticos. Clique e saiba mais!

Como configurar a instância MySQL

Antes de tudo, é essencial que você tenha uma conta Google criada especificamente para o uso do Cloud.

Caso ainda não tenha, temos um artigo que explica como criar e configurar a conta Google para utilizar o BigQuery, incluindo a criação de um projeto e associação de uma conta de faturamento.

Com esses passos, é possível criar uma instância MySQL no Google Cloud Plataform.

Estando dentro do seu projeto, clique no menu, no canto superior esquerdo da tela, e busque SQL:

Captura de tela do menu principal do Google Cloud, indicando a opção SQL.

Na nova tela, selecione “Criar instância com seus créditos gratuitos”:

Captura de tela do Google Cloud. Há um texto de apresentação sobre o serviço de banco de dados totalmente gerenciado oferecido pelo Google Cloud, sendo para MySQL, PostgreSQL e SQL Server. O texto diz que o serviço reduz o custo geral das operações e libera a equipe para focar em inovações. Abaixo, há um botão para criar instância com os créditos gratuitos, e está destacado por um retângulo e uma seta. Ao lado, há também a opção de migrar dados.

Então, iremos escolher MySQL como nosso mecanismo de banco de dados:

Captura de tela da página inicial de criação de uma instância no Google Cloud. É possível escolher entre PostgreSQL e MySQL, e um retângulo indica a escolha do MySQL.

Para criar a instância, antes de mais nada, é necessário ativar a API Compute Engine.

Esse é um serviço de computação e hospedagem da Google que nos permite criar e executar Máquinas Virtuais online, na infraestrutura do Google.

Pode ser que demore um pouquinho até que a API esteja ativada, não se preocupe.

Captura de tela da página de criação de uma instância no Google Cloud. Está escrito que, para criar uma instância, é preciso ativar a API Compute Engine, e um retângulo evidencia o botão “Ativar API”.

Assim que a API for criada, a página será direcionada para a configuração da instância. Enquanto a instância é configurada, é possível visualizar a estimativa de preços, conforme as seleções forem sendo feitas.

Lembrando que a conta gratuita oferece um total de 300 dólares para serem gastos, e é interessante estimar os custos para não desperdiçar créditos gratuitos.

Captura de tela com a estimativa de preços, na tela de configurações de criação de uma instância no Google Cloud. A estimativa, no exemplo, é de 45 centavos de dólar por hora e 10 dólares e 82 centavos por dia. Há também um breve texto explicativo dizendo que esse valor representa os recursos de computação, memória e armazenamento, e refletem como a instância está configurada até o momento. Descontos não estão incluídos na estimativa.

Seguindo com a configuração, defina o ID utilizando letras minúsculas, números e hífens, e também uma senha para o usuário raiz.

Anote a senha em algum lugar! Você vai precisar dela em outros momentos.

Em “Versão do banco de dados”, selecione a versão do MySQL que deseja utilizar. No meu exemplo, utilizarei a versão 8.0.

Na opção “política de senha”, é possível selecionar requisitos de complexidade, tamanho mínimo e restrição de usos de senhas repetidas — essas políticas se aplicam a outros usuários que, porventura, estejam trabalhando com essa instância.

Não é necessário fazer nenhuma seleção, caso não se adeque às suas necessidades.

Captura de tela mostrando informações da instância MySQL sendo criada. O ID da instância é artigo-integração-123, há uma senha criptografada, e a versão do banco de dados é MySQL 8.0.

Já a escolha da edição do Cloud SQL depende dos requisitos do projeto - a edição Enterprise Plus oferece muito mais desempenho e segurança, e o custo acompanha o que oferece.

Tenha em mente que o GCP oferece soluções para lidar com conjuntos realmente muito grandes de dados que podem estar sofrendo atualização em tempo real, então, as opções oferecidas atendem necessidades exorbitantes de armazenamento — para estudos e empresas não muito grandes, a edição Enterprise supre todas as necessidades com bastante tranquilidade.

Em predefinição, escolha “Desenvolvimento”. Essa configuração define a capacidade da máquina, em número de CPUs e memória disponível.

Também é possível alterar a capacidade personalizando a instância - existe até opção para utilizar uma CPU compartilhada. Se quiser saber mais sobre, recomendo a leitura da documentação oficial da criação de uma instância.

Tela de escolha de versão de edição do Cloud SQL. Há as versões enterprise plus e enterprise. A enterprise está selecionada.Abaixo, na caixa de escolha de predefinição para a edição, está selecionado “Desenvolvimento”.

Para selecionar a zona, é interessante que seja aquela que está mais perto de você ou de sua empresa, para evitar gerar muita latência, ou seja, o atraso na comunicação da rede na transferência dos dados.

Aqui, escolherei southamerica-east1 (São Paulo). Além disso, selecionarei “Única zona”. É nesse local que está o centro de dados da Google onde a instância ficará hospedada.

Tela de escolha de disponibilidade por região e zona; A disponibilidade por zona está selecionada como “Única zona”. A outra possibilidade é “Várias zonas”, o que permitiria a escolha de mais de uma região. A região escolhida foi São Paulo.

Prontinho. Com as configurações completas, basta clicar em “Criar instância”

Imagem com um destaque mostrando o botão “Criar instância”. Ao lado há o botão de cancelamento, e acima está a opção de personalizar a instância.

Lembrete importante: ao criar a instância, automaticamente, os valores por hora estarão sendo creditados da sua conta.

Para evitar que os créditos acabem muito rápido, todas as vezes que você se desconectar ou pausar os estudos, é recomendado interromper a instância.

É possível fazer isso na página de visão geral da instância. É normal que esse processo demore alguns minutos até ser concluído.

Como criar a conexão entre o MySQL local e a instância na nuvem

Aqui estão os passos para conectar o MySQL local à instância na nuvem:

Instalação do MySQL Workbench na máquina local

Caso ainda não tenha o Workbench instalado, temos um artigo com o passo a passo para a instalação.

Agora, temos a nossa instância MySQL criada na nuvem, e também o IDE do MySQL no computador local. Como será possível integrar esses dois ambientes?

Autorizando a rede local

Durante todo o processo, será necessário criar algumas configurações de segurança.

O Google conta com uma camada de proteção que evita que qualquer computador se conecte a instâncias na nuvem — para isso, nesse momento, é necessário registrar o endereço IP do computador que irá acessar na instância criada, para que o Cloud saiba que, quando receber uma solicitação de acesso desse computador específico, é seguro aceitar e liberar o acesso.

Vamos lá?

Para descobrir o endereço IP do seu computador, basta acessar o site whatsmyip.org.

Com seu endereço em mãos, volte ao Console, na página da instância, e acesse a aba “Conexões”:

Captura de tela da página visão geral da instância artigo-integração. Ao lado esquerdo está o menu, e ao lado direito um gráfico de uso de CPU. Uma seta aponta para a opção “Conexões”, no menu.

Acesse a seção “Rede” e certifique-se de ter a opção “IP Público” selecionada:

Captura de tela da tela “conexões” da instância. Acima, está um menu com as opções resumo, rede, segurança e testes de conectividade. A opção Redes está selecionada. Abaixo, na seção “Atribuição de IP da instância, estão as opções IP Privado e IP público. A opção IP público está selecionada.

Um pouco mais abaixo, nesta mesma tela, clique em “Adicionar uma rede”:

Captura de tela. Um retângulo destaca o botão “Adicionar uma rede” na lista de Redes autorizadas.

Escolha um nome para a rede e preencha seu IP no campo “rede”. Em seguida, clique em concluir.

Uma imagem mostra os passos de criação de uma nova rede. O primeiro passo é adicionar um nome, que é Computador local, no exemplo. O segundo passo é adicionar o número do IP, e o terceiro é clicar no botão de concluir.

Então, clique em “Salvar”, no fim da página. É provável que seja necessário esperar alguns segundinhos, enquanto a rede é criada.

Imagem destacando o botão “Salvar”, ao lado do botão “destacar alterações”.

Assim que terminar de carregar, a nova rede estará listada em “redes autorizadas”:

Imagem da lista de redes autorizadas. Agora, a rede Computador local está na lista.

Criando a conexão local

Agora, o Cloud já sabe que pode deixar seu computador entrar na instância. O próximo passo é contar ao MySQL onde está a instância, para que ele possa pedir o acesso.

Para encontrar a instância MySQL do Cloud através do IDE local, é necessário recolher o endereço IP público da instância.

Esse número pode ser encontrado na página “Visão Geral”, logo abaixo do gráfico de uso de CPU. Copie esse endereço.

Captura de tela. Ao lado esquerdo, está o menu da instância, e a opção “Visão Geral” está selecionada. Ao lado direito, está um quadro de informações com o título “Conectar-se a essa instância”. Nesse quadro, o “Endereço IP público” está destacado, mostrando que é o que vamos utilizar agora. Há também outras informações, como o nome de conexão e indicações de links para ajuda com a conexão.

No Workbench, crie uma nova conexão:

Tela inicial do MySQL Workbench. Uma seta aponta para o sinal de “mais”, que adiciona uma nova conexão.

Nas configurações da conexão:

  1. Adicione o nome da conexão;
  2. Em “hostname”, adicione o endereço IP público;
  3. Em “Username”, digite “root”;
  4. A senha, no campo “Password”, é a que foi definida no momento de criação da instância. Para digitá-la, clique no botão “Store in Vault” e então abrirá uma janela com um espaço para a senha;
  5. Então, teste a conexão clicando em “Test Connection”.
Imagem da janela de nova conexão no Workbench, indicando cada passo a ser feito.

Se todas as informações estiverem corretas, você verá uma janela dizendo que a conexão foi feita com sucesso!

Janela de conexão MySQl feita com sucesso.

Clique em “OK” para fechar a janela de teste e, novamente, para criar a conexão.

Inserindo dados no MySQL local

Com a conexão criada, vamos criar um banco de dados e povoá-lo. No editor do Workbench, crie um schema, selecione para utilizá-lo, então crie e popule suas tabelas.

Se você tiver interesse em me acompanhar durante todo o processo, utilize o script de criação do banco de dados de horta medicinal. Basta abrir o arquivo diretamente no Workbench e executar o script clicando no ícone de raio.

Tela de scripts do Workbench, preenchida com um script de criação do schema horta e criação de tabelas propriedade medicinal e parte utilizada. Uma seta aponta para o símbolo de raio que executa o script.

Caso você queira se aprofundar mais no conhecimento de MySQL, especificamente, confira a formação SQL com MySQL Server.

Comunicando o MySQL com o BigQuery

Nesse passo, é interessante manter duas abas abertas no seu navegador: uma com a Visão Geral da instância criada, e outra com o BigQuery Studio.

Algumas informações que serão preenchidas dentro do BigQuery estarão disponíveis para serem copiadas na página de Visão Geral da instância.

Essa página é acessada ao se escolher “SQL” no menu principal do Cloud.

Página principal do cloud. Ao lado esquerdo o menu está aberto e há um retângulo na opção “SQL”.

No momento, já temos a instância criada, algumas configurações de segurança feitas, e a conexão entre a máquina local e a nuvem.

Agora, mesmo que o MySQL e o BigQuery estejam dentro do mesmo projeto, é necessário criar uma porta de comunicação entre eles.

Então, o que falta realizarmos?

Vamos criar um conjunto de dados, ativar mais uma API, conectar à fonte dos dados e, então, finalizar as configurações de segurança.

Como criar o conjunto de dados

Acesse o BigQuery Studio, o local onde as consultas e manipulações dos dados serão feitas. No menu principal, selecione “BigQuery” e então “BigQuery Studio”:

Página principal do cloud. Ao lado esquerdo o menu está aberto e há um retângulo na opção “BigQuery”. Mais à direita, um submenu está aberto e a opção “BigQuery Studio” também está destacada por um retângulo.

Na aba “Explorer”, estará o nome do seu projeto. Clique nos três pontinhos logo ao lado do nome e selecione “Criar conjunto de dados”:

No BigQuery Studio, na aba “Explorer”, logo ao lado do nome do projeto, uma seta aponta para os três pontos que simbolizam o menu. Com o menu aberto, a opção “Criar conjunto de dados está destacada por um retângulo.

Preencha:

  1. Código do conjunto de dados: um nome escolhido para o conjunto de dados;
  2. Tipo de local: Região;
  3. Região: Mantenha a mesma região utilizada em todas as partes do projeto. No caso do exemplo, estou utilizando southamerica -east1 (São Paulo);
  4. Crie o conjunto de dados.

Ativando a API

A API BigQuery Connection permite que o Big Query se conecte a fontes de dados externas. Vamos ativá-la.

No menu principal, busque por “APIs e serviços” e então clique em “Biblioteca”:

Captura de tela mostrando o menu do Google Cloud aberto, a opção APIs e Serviços selecionada, e a opção “Biblioteca” selecionada no submenu.

Busque por “BigQuery Connection API” na barra de pesquisa e então, clique em “Ativar”. Pode ser necessário aguardar alguns segundos até que a API esteja ativa.

Conectando à fonte de dados externa

Esse passo é feito diretamente no BigQuery, mas, antes disso, de volta na página “Visão Geral” da instância, busque o nome da conexão no mesmo card em que pegamos o endereço de IP público utilizado para criar a conexão do Workbench, logo abaixo. Copie esse nome.

Captura de tela. Ao lado esquerdo, está o menu da instância, e a opção “Visão Geral” está selecionada. Ao lado direito, está um quadro de informações com o título “Conectar-se a essa instância”. Nesse quadro, o “Nome da conexão” está destacado, mostrando que é o que vamos utilizar agora. Há também outras informações, como o endereço IP público e indicações de links para ajuda com a conexão.

No BigQuery Studio, com o conjunto de dados já criado e em posse do nome da instância na área de transferência, podemos adicionar a fonte de dados. Clique em “Adicionar”:

Captura de tela do BigQuery. Uma seta aponta para o botão “Adicionar”, na parte “Explorer”.

Então, escolha “Conexões com fontes de dados externas”:

Captura de tela com as opções para adicionar dados. As opções são arquivo local, google cloud storage, ou conexões com fontes de dados externas. Essa última está destacada por um retângulo.

Na janela de configuração da fonte de dados, preencha:

  1. Tipo de conexão: Cloud SQL - MySQL;
  2. Código da conexão: é o nome que será dado para a conexão entre o BigQuery e a instância;
  3. Tipo de local: Região;
  4. Região: a mesma utilizada em todo o projeto. No caso, southamerica - east1 (São Paulo);
  5. Nome amigável e descrição são opcionais;
  6. Criptografia: Chave gerenciada pelo Google;
  7. Nome da conexão do Cloud SQL: é o nome da conexão que foi copiado;
  8. Nome do banco de dados: o schema definido no MySQL. É importante respeitar maiúsculas e minúsculas - o nome deve ser igual em todos os caracteres;
  9. Nome do usuário: root;
  10. Senha do usuário: a senha definida no momento de criação da instância.

Confira se tudo está certinho e, então, clique em “Criar conexão”

Adicionando papéis de acesso

Ufa, estamos quase lá! Vamos finalizar as configurações de segurança exigidas. Para isso, é necessário ativar o compartilhamento de conexão para o usuário Google.

No BigQuery Studio, em “Explorer”, estão listados os conjuntos de dados e a conexão externa recém-criada.

Clique duas vezes sobre ela para que as informações abram, ao lado. Então, clique em “Compartilhar”:

Captura de tela do BigQuery Studio. Na aba “Explorer”, no lado esquedo, o projeto agora tem algumas pastas como conteúdo. Uma das pastas é chamada conexões externas e ali está o nome da conexão recém criada. Está destacado por um retângulo. No lado direito estão as informações da conexão, e na parte de cima a opção “compartilhar” está destacada.

Clique em “Adicionar principal”:

Imagem da tela de compartilhamento de uma conexão externa no BigQuery. O botão “Adicionar principal” está destacado por um retângulo.

Em “Adicionar participantes”, insira o seu usuário Google criado para o uso do Cloud.

Esse mesmo que está logado, agora. Em papéis, escolha “BigQuery Connection Admin”. É necessário passar o mouse por “BigQuery” para que essa opção apareça.

Imagem da tela de permitir acesso à conexão externa. Em “novos principais”, está o email de teste sendo utilizado para a construção deste artigo. Em “Papel”, está BigQuery Connection Admin.

Então, salve. Você pode ver que o e-mail estará salvo com um novo papel atribuído. Essa aba pode ser fechada.

Para finalizar as configurações de segurança, é preciso adicionar mais um papel, agora, no IAM, que é o sistema de gerenciamento de identidade e acesso do Google Cloud.

Voltando as informações da conexão externa, busque pelo ID da conta de serviço.

Esse ID é um código bastante grande, e vamos utilizá-lo nessa nova etapa. Se preferir, salve em um bloco de notas por enquanto, ou deixe a aba aberta e escolha “abrir em nova guia”, clicando com o botão direito, no passo a seguir.

Imagem da tela de informações da conexão. Na última linha, está destacada a informação “ID da conta de serviço”.

No menu principal do Cloud, vá em “IAM e administração”, e então “IAM”.

Imagem do menu principal do Google Cloud. A opção “IAM e Administração está selecionada, abrindo um submenu, onde está selecionada a opção IAM.

Na página IAM, clique em “Permitir acesso”:

Imagem da tela de permissões do projeto, na seção de IAM. Um retângulo destaca a opção “Permitir acesso”.

Em “novos principais”, entra o ID da conta de serviço, que conversamos agora pouco. E, em “Papéis”, navegue até “Cloud SQL” e escolha “Cliente do Cloud SQL”.

Boa! A partir de agora, já é possível acessarmos os dados do MySQL diretamente pelo BigQuery.

Gif do personagem preguiça da animação “Zootopia” abrindo a boca e sorrindo, com um semblante de satisfação.

Finalmente!

Teste de conexão

Não vou te deixar aqui sem garantir que está tudo funcionando como o esperado! Vem comigo descobrir como consultar os dados.

Os dados não estão diretamente no armazenamento do BigQuery, então, executar um simples SELECT certamente resultará em um erro.

A solução para isso é utilizar uma função de consulta federada em conjunto com a cláusula de seleção.

Utilizaremos a função EXTERNAL_QUERY, que executa uma consulta em um banco de dados externo. Essa função exige dois parâmetros:

  • connection_id: um “endereço” que une palavras reservadas e códigos que identificam o projeto e a conexão;
  • external_database_query: a consulta que será executada.

A sintaxe do parâmetro connection_id é a seguinte:

projects/<ID_DO_PROJETO>/locations/<LOCALIZAÇÃO>/connections/<ID_DA_CONEXÃO>
  • ID_DO_PROJETO: O nome do projeto sempre está na parte superior da página, perto da barra de pesquisa. Clicando na setinha ao lado do nome, encontra-se a lista de projetos e seus respectivos id’s.
Imagem mostrando o ID do projeto, encontrado ao lado do nome do projeto, na janela que abre ao clicar na setinha ao lado do nome do projeto ativo.
  • LOCALIZAÇÃO: A região onde a instância está hospedada. Na aba “Explorer” do BigQuery, em “Conexões externas” é a parte que está antes do ponto final.
Imagem destacando a primeira parte do nome da conexão externa, que é a localização da conexão. No caso, é southamerica-east1.
  • ID_DA_CONEXÃO: O nome da conexão. O restante do nome da conexão externa, após o ponto final.
Imagem mostrando a última parte do nome da conexão externa. No caso, é banco-horta.

Sendo assim, o resultado fica:

projects/integrando-mysql-e-bigquery/locations/southamerica-east1/connections/banco-horta

Para testar, podemos realizar uma consulta simples. Usarei a seguinte, que seleciona todos os registros da tabela planta:

SELECT * FROM planta;

Montando os parâmetros, temos como resultado:

SELECT * FROM
EXTERNAL_QUERY("projects/integrando-mysql-e-bigquery/locations/southamerica-east1/connections/banco-horta", "SELECT * FROM planta")

Clicando em “Executar”, a consulta é feita:

Imagem do editor de script do BigQuery. Um retângulo destaca o botão executar. A query que seleciona todos os valores da tabela planta está na tela, e abaixo está o resultado da consulta.

Eba! Consulta efetuada com sucesso!

Gif de dois personagens da animação “Zootopia” dançando alegres.

Uhul!

Se tiver vontade de explorar outras consultas com a base de dados da horta, deixo aqui algumas outras opções para inspiração.

Além disso, o Google Cloud também oferece soluções interessantes para a migração completa de banco de dados.

Conclusão

Nesse artigo, te mostrei como criar uma instância MySQL no Google Cloud compreendendo as escolhas de configuração, como comunicar o MySQL com o BigQuery, como consultar seus dados diretamente do BigQuery e, também, como garantir que as exigências de segurança sejam cumpridas.

Chegamos ao fim com uma comunicação funcional entre o banco de dados local e a nuvem, possibilitando que muitas facilidades oferecidas pelo Cloud possam fazer parte do seu dia a dia e da sua equipe, tais como:

  • Velocidade e poder de processamento;
  • Escalabilidade sob demanda;
  • Segurança com criptografia em repouso e em trânsito;
  • Facilidade para criar análises complexas;
  • Integração entre diversas ferramentas;
  • Acessibilidade global;
  • Gerenciamento simplificado.

Com tantas possibilidades, pode parecer bem desafiador entrar no universo GCP. Porém, com uma documentação bem organizada, completa e de fácil entendimento, navegar pela plataforma se torna uma experiência de descobertas.

Além disso, o canal no Youtube do Google Cloud também conta com informações preciosas para as pessoas usuárias do Cloud, com palestras, lives e vídeos curtos explicativos.

Por fim, te convido a conhecer melhor nossa formação BigQuery, em que um time de especialistas se reuniu para se debruçar sobre esse assunto te ajudar a alavancar ainda mais sua carreira descobrindo na prática o poder dessa ferramenta!

Um abraço e até mais.

Créditos

Larissa Dubiella
Larissa Dubiella

Larissa é encantada pelo potencial da tecnologia em trazer respostas rápidas para questões complexas, permitindo que a curiosidade vá muito além. Preza por um aprendizado que seja prazeroso e acredita que o conhecimento só é verdadeiro quando é coletivo.

Veja outros artigos sobre Data Science