Estrutura de dados do BigQuery no Google: como começar a usar o armazenamento em nuvem

O Google BigQuery é um serviço de armazenamento em nuvem que permite coletar todos os seus dados em um sistema e analisá-los facilmente usando consultas SQL. Para que os dados sejam convenientes para trabalhar, eles devem ser estruturados corretamente. Neste artigo, explicaremos como criar tabelas e conjuntos de dados para upload no Google BigQuery.

Para usar o Google BigQuery, você precisa criar um projeto no Google Cloud Platform (GCP). Após o registro, você terá acesso a todos os produtos do Cloud Platform durante um período de teste gratuito e US$ 300 para gastar nesses produtos nos próximos 12 meses.

Depois de criar um projeto no Google Cloud Platform, você precisa adicionar pelo menos um conjunto de dados ao Google BigQuery.

Um conjunto de dados é um contêiner de nível superior usado para organizar e controlar o acesso aos seus dados. Em termos simples, é uma espécie de pasta na qual suas informações são armazenadas na forma de tabelas e visualizações.

Abra seu projeto no GCP, acesse a guia BigQuery e clique em Criar conjunto de dados :

Na janela que se abre, especifique um nome para o conjunto de dados e a vida útil de uma tabela. Se você quiser que as tabelas com dados sejam excluídas automaticamente, especifique quando exatamente. Ou deixe a opção padrão Perpétua para que as tabelas só possam ser excluídas manualmente.

O campo Site de processamento é opcional. Por padrão, é definido como multirregião dos EUA. Você pode encontrar mais informações sobre regiões para armazenamento de dados na seção de ajuda.

Depois de criar um conjunto de dados, você precisa adicionar uma tabela na qual os dados serão coletados. Uma tabela é um conjunto de linhas. Cada linha consiste em colunas, que também são chamadas de campos. Existem várias maneiras de criar uma tabela no BigQuery, dependendo da fonte de dados:

• Crie manualmente uma tabela vazia e configure um esquema de dados para ela
• Criar uma tabela usando o resultado de uma consulta SQL calculada anteriormente
• Faça upload de um arquivo do seu computador (no formato CSV, AVRO, JSON, Parquet, ORC ou Planilhas Google)
• Em vez de fazer download ou streaming de dados, você pode criar uma tabela que se refira a uma fonte externa: Cloud Bigtable, Cloud Storage ou Google Drive.

Neste artigo, examinaremos mais de perto o primeiro método: criar uma tabela manualmente.

Etapa 1 . Selecione o conjunto de dados ao qual deseja adicionar a tabela e clique em Criar tabela :

Etapa 2. No campo Origem, selecione Tabela Vazia e, no campo Tipo de Tabela , selecione Tabela no formato nativo do objeto de destino. Invente um nome para a mesa.

Importante : Os nomes dos conjuntos de dados, tabelas e campos devem estar em caracteres latinos e conter apenas letras, números e sublinhados.

Etapa 3 . Especifique o esquema da tabela. O esquema consiste em quatro componentes: dois obrigatórios (nome da coluna e tipo de dados) e dois opcionais (modo e descrição da coluna). Tipos e modos de campo adequadamente selecionados facilitarão o trabalho com os dados.

Exemplo de esquema no BigQuery:

Nomes de coluna
No nome da coluna, você precisa especificar o parâmetro pelo qual cada coluna é responsável: data, user_id, produtos, etc. Os títulos podem conter apenas letras latinas, números e sublinhados (máximo de 128 caracteres). Nomes de campos idênticos não são permitidos, mesmo que suas maiúsculas e minúsculas sejam diferentes.

Tipo de dados
Ao criar uma tabela no BigQuery, você pode usar os seguintes tipos de campo:

Modos
O BigQuery é compatível com os seguintes modos para colunas de tabela:

Nota : Não é necessário preencher o campo Modo. Se nenhum modo for especificado, a coluna padrão será NULLABLE.

Descrições das colunas
Se desejar, você pode adicionar uma breve descrição (não mais que 1024 caracteres) para cada coluna da tabela para explicar o que significa um determinado parâmetro.

Ao criar uma tabela vazia no BigQuery, você precisa definir o esquema manualmente. Isso pode ser feito de duas maneiras:
1. Clique no botão Adicionar campo e preencha o nome, tipo e modo de cada coluna.

2. Insira o esquema de tabela como uma matriz JSON usando a opção Editar como texto .

Além disso, o Google BigQuery pode usar a detecção automática de esquema ao carregar dados de arquivos CSV e JSON.

Essa opção funciona de acordo com o seguinte princípio: o BigQuery seleciona um arquivo aleatório da origem especificada, verifica até 100 linhas de dados nele e usa os resultados como uma amostra representativa. Em seguida, ele verifica cada campo no arquivo carregado e tenta atribuir um tipo de dados a ele com base nos valores da amostra.

Ao carregar arquivos do Google, o BigQuery pode alterar o nome de uma coluna para torná-la compatível com sua própria sintaxe SQL. Portanto, recomendamos que você carregue tabelas com nomes de campo em inglês; se os nomes estiverem em russo, por exemplo, o sistema os renomeará automaticamente. Por exemplo:

Se, ao carregar os dados, os nomes das colunas foram inseridos incorretamente ou você deseja alterar os nomes e tipos das colunas em uma tabela existente, você pode fazer isso manualmente. Nós lhe diremos como.

Depois de carregar os dados no Google BigQuery, o layout da tabela pode ser um pouco diferente do original. Por exemplo, um nome de campo pode ter sido alterado devido a um caractere que não é compatível com o BigQuery ou o tipo de campo pode ser INTEGER em vez de STRING. Nesse caso, você pode ajustar manualmente o esquema.

Usando uma consulta SQL, selecione todas as colunas na tabela e especifique um novo nome para a coluna que deseja renomear. Nesse caso, você pode substituir a tabela existente ou criar uma nova. Exemplos de solicitação:

Usando uma consulta SQL, selecione todos os dados de uma tabela e converta a coluna correspondente em um tipo de dados diferente. Você pode usar os resultados da consulta para substituir uma tabela existente ou criar uma nova. Exemplo de solicitação:

Você pode alterar o modo de coluna de REQUIRED para NULLABLE conforme descrito na documentação de ajuda. A segunda opção é exportar os dados para o Cloud Storage e de lá retorná-los ao BigQuery com o modo correto para todas as colunas.

Use a consulta SELECT * EXCEPT para excluir uma coluna (ou colunas) e, em seguida, grave os resultados da consulta na tabela antiga ou crie uma nova. Exemplo de solicitação:

Além disso, há uma segunda maneira de alterar o esquema adequado para todas as tarefas descritas acima: exportar dados e carregá-los em uma nova tabela. Para renomear uma coluna, você pode fazer upload de dados do BigQuery para o Cloud Storage e exportá-los do Cloud Storage para o BigQuery em uma nova tabela ou substituir os dados da tabela antiga usando os parâmetros avançados:

Você pode ler sobre outras maneiras de alterar a estrutura da tabela na documentação de ajuda do Google Cloud Platform.

Você pode fazer download de dados e fazer upload de dados para o BigQuery sem a ajuda de desenvolvedores por meio da interface ou de um complemento especial do OWOX BI. Vamos considerar cada método em detalhes.

Para fazer upload das informações necessárias para o armazenamento – por exemplo, dados sobre usuários e pedidos offline – abra seu conjunto de dados, clique em Criar tabela e selecione a fonte de dados: Cloud Storage, seu computador, Google Drive ou Cloud Bigtable. Especifique o caminho para o arquivo, seu formato e o nome da tabela onde os dados serão carregados:

Depois de clicar em Criar tabela, uma tabela aparecerá em seu conjunto de dados.

Também é possível fazer upload de dados processados ​​do BigQuery – por exemplo, para criar um relatório por meio da interface do sistema. Para isso, abra a tabela desejada com os dados e clique no botão Exportar:

O sistema oferecerá duas opções: visualizar os dados no Google Data Studio ou fazer upload para o Google Cloud Storage. Se você selecionar a primeira opção, irá imediatamente para o Data Studio, onde poderá salvar o relatório.

Escolher exportar para o Google Cloud Storage abrirá uma nova janela. Nele, você precisa especificar onde salvar os dados e em qual formato.

O complemento gratuito OWOX BI BigQuery Reports permite que você transfira dados de forma rápida e conveniente diretamente do Google BigQuery para o Google Sheets e vice-versa. Assim, você não precisa preparar arquivos CSV ou usar serviços pagos de terceiros.

Por exemplo, digamos que você queira fazer upload de dados de pedidos off-line para o BigQuery para criar um relatório de ROPO. Para isso, você precisará:

1. Instale o complemento BigQuery Reports em seu navegador.
2. Abra seu arquivo de dados no Google Sheets e na guia Add-ons , selecione OWOX BI BigQuery Reports → Upload data to BigQuery.
3. Na janela que se abre, selecione seu projeto e conjunto de dados no BigQuery e insira o nome desejado para a tabela. Além disso, selecione os campos cujos valores você deseja carregar. Por padrão, o tipo de todos os campos é STRING, mas recomendamos escolher o tipo de dados de acordo com o contexto (por exemplo, para campos com identificadores numéricos, escolha INTEGER; para preços, FLOAT):
   

4. Clique no botão Iniciar upload e seus dados serão carregados no Google BigQuery​

Você também pode usar esse complemento para exportar dados do BigQuery para o Planilhas Google, por exemplo, para visualizar dados ou compartilhá-los com colegas que não têm acesso ao BigQuery. Por esta:

1. Abra o Planilhas Google. Na guia Add-ons , selecione OWOX BI BigQuery Reports → Add a new report:
   

2. Em seguida, insira seu projeto no Google BigQuery e selecione Adicionar nova consulta.
3. Na nova janela, insira sua consulta SQL. Pode ser uma consulta que carrega dados de uma tabela para o BigQuery ou uma consulta que extrai e calcula os dados necessários.
4. Renomeie a consulta para facilitar a localização e a inicialização clicando no botão Salvar e executar.
   

Para fazer upload de dados do BigQuery para o Planilhas Google regularmente, você pode ativar as atualizações de dados programadas:

1. Na guia Add-ons , selecione OWOX BI BigQuery Reports → Schedule report :
   

2. Na janela que se abre, defina a hora e a frequência das atualizações do relatório e clique em Salvar :
   

Se você ainda não apreciou os benefícios do armazenamento em nuvem do Google BigQuery, recomendamos experimentá-lo. Com a ajuda do OWOX BI, você pode combinar dados do seu site, fontes de publicidade e sistemas internos de CRM no BigQuery para:

• Configure análises de ponta a ponta e descubra o retorno real do seu marketing, levando em consideração pedidos offline, devoluções e todas as etapas do usuário no caminho para uma compra;
• Crie relatórios sobre dados completos não amostrados com quaisquer parâmetros e indicadores;
• Avaliar canais de aquisição de clientes usando análise de coorte;
• Descubra como sua publicidade online impacta as vendas offline;
• Reduza a participação nos custos de publicidade, amplie o ciclo de vida dos clientes e aumente o LTV da sua base de clientes como um todo;
• Segmente os clientes em função da sua atividade e personalize a comunicação com eles.

OWOX BI tem um período de teste gratuito durante o qual você pode experimentar todos os recursos do serviço.