Checkpoints
Create a new dataset to store the tables
/ 10
Create a table
/ 10
Working as intended
/ 20
Create a table
/ 20
Create view
/ 10
Create a new view to returns recent 10 transactions
/ 20
Run a query with session_user in views for limiting data access
/ 10
Como criar tabelas permanentes e visualizações com acesso controlado no BigQuery
GSP410
Visão geral
O BigQuery é um banco de dados de análise NoOps, totalmente gerenciado e de baixo custo desenvolvido pelo Google. Com ele, você pode consultar muitos terabytes de dados sem ter que gerenciar uma infraestrutura ou precisar de um administrador de banco de dados. O BigQuery usa SQL e está disponível no modelo de pagamento por uso. Assim, você pode se concentrar na análise dos dados para encontrar insights relevantes.
Você usará um conjunto de dados de comércio eletrônico com milhões de registros do Google Analytics referentes à Google Merchandise Store e carregados no BigQuery. Com uma cópia do conjunto de dados, você analisará os campos e linhas disponíveis para extrair insights.
Neste laboratório, você aprenderá a criar novas tabelas de relatórios permanentes e análises lógicas usando um conjunto de dados de e-commerce.
Configuração e requisitos
Antes de clicar no botão Start Lab
Leia estas instruções. Os laboratórios são cronometrados e não podem ser pausados. O timer é iniciado quando você clica em Começar o laboratório e mostra por quanto tempo os recursos do Google Cloud vão ficar disponíveis.
Este laboratório prático permite que você realize as atividades em um ambiente real de nuvem, não em uma simulação ou demonstração. Você vai receber novas credenciais temporárias para fazer login e acessar o Google Cloud durante o laboratório.
Confira os requisitos para concluir o laboratório:
- Acesso a um navegador de Internet padrão (recomendamos o Chrome).
- Tempo para concluir o laboratório---não se esqueça: depois de começar, não será possível pausar o laboratório.
Como iniciar seu laboratório e fazer login no console do Google Cloud
-
Clique no botão Começar o laboratório. Se for preciso pagar, você verá um pop-up para selecionar a forma de pagamento. No painel Detalhes do laboratório à esquerda, você verá o seguinte:
- O botão Abrir Console do Cloud
- Tempo restante
- As credenciais temporárias que você vai usar neste laboratório
- Outras informações se forem necessárias
-
Clique em Abrir Console do Google. O laboratório ativa recursos e depois abre outra guia com a página Fazer login.
Dica: coloque as guias em janelas separadas lado a lado.
Observação: se aparecer a caixa de diálogo Escolher uma conta, clique em Usar outra conta. -
Caso seja preciso, copie o Nome de usuário no painel Detalhes do laboratório e cole esse nome na caixa de diálogo Fazer login. Clique em Avançar.
-
Copie a Senha no painel Detalhes do laboratório e a cole na caixa de diálogo Olá. Clique em Avançar.
Importante: você precisa usar as credenciais do painel à esquerda. Não use suas credenciais do Google Cloud Ensina. Observação: se você usar sua própria conta do Google Cloud neste laboratório, é possível que receba cobranças adicionais. -
Acesse as próximas páginas:
- Aceite os Termos e Condições.
- Não adicione opções de recuperação nem autenticação de dois fatores (porque essa é uma conta temporária).
- Não se inscreva em testes gratuitos.
Depois de alguns instantes, o console do GCP vai ser aberto nesta guia.
Abrir o console do BigQuery
- No Console do Google Cloud, selecione o menu de navegação > BigQuery:
Você verá a caixa de mensagem Olá! Este é o BigQuery no Console do Cloud. Ela tem um link para o guia de início rápido e as notas de versão.
- Clique em OK.
O console do BigQuery vai abrir.
Tarefa 1: Crie um novo conjunto de dados para armazenar as tabelas
-
No BigQuery, clique no ícone Exibir ações ao lado do ID do projeto e selecione Criar conjunto de dados.
-
Defina o ID do conjunto de dados como ecommerce e não altere as outras opções (Local dos dados, Validade da tabela padrão).
-
Clique em CRIAR CONJUNTO DE DADOS.
Clique em Verificar meu progresso para conferir o objetivo.
Tarefa 2: Solução de problemas com instruções CREATE TABLE
A equipe de analistas de dados enviou as instruções de consulta abaixo para criar uma tabela permanente no novo conjunto de dados "ecommerce" que você criou. O problema é que as instruções não estão funcionando corretamente.
Descubra o que há de errado com cada consulta e proponha uma solução.
Regras para criar tabelas com SQL no BigQuery
Leia estas regras da instrução CREATE TABLE que você usará como um guia ao corrigir consultas:
- A lista de colunas especificada ou as colunas inferidas de query_statement (ou as duas) precisam estar presentes.
- Quando a lista de colunas e a cláusula as query_statement estão presentes, o BigQuery ignora os nomes na cláusula as query_statement e combina as colunas com a lista de colunas por posição.
- Quando a cláusula as query_statement está presente e a lista de colunas não existe, o BigQuery determina os nomes e os tipos de colunas com base na cláusula as query_statement.
- É necessário especificar os nomes das colunas pela lista de colunas ou pela cláusula as query_statement.
- Nomes de colunas duplicados não são permitidos.
Consulta 1: colunas e mais colunas
- Adicione essa consulta ao Editor do BigQuery, clique em Executar, identifique o erro e responda às seguintes perguntas:
Error: CREATE TABLE has columns with duplicate name fullVisitorId at [7:2]
A consulta acima violou qual das regras da instrução CREATE TABLE?
Consulta 2: repensando as colunas
- Adicione essa consulta ao Editor do BigQuery, clique em Executar, identifique o erro e responda às seguintes perguntas:
Error: The number of columns in the column definition list does not match the number of columns produced by the query at [5:1]
A consulta acima violou qual das regras da instrução CREATE TABLE?
fullVisitorId
e channelGrouping
, mas, na instrução da consulta, foi especificado todas as colunas retornadas (\*).Consulta 3: é válida! Ou será que não?
- Adicione essa consulta ao Editor do BigQuery, clique em Executar, identifique o erro e responda às seguintes perguntas:
Valid: This query will process 1.1 GiB when run.
Lembre-se da regra número 2: quando a lista de colunas e a cláusula as
query_statement estão presentes, o BigQuery ignora os nomes na cláusula as
query_statement e combina as colunas com a lista de colunas por posição.
Clique em Verificar meu progresso para ver o objetivo.
Consulta 4: o vigia
- Execute a consulta abaixo no Editor do BigQuery, identifique o erro e responda às seguintes perguntas:
Valid: This query will process 907.52 MiB when run.
Corrija e execute novamente a consulta modificada para confirmar se ela foi executada corretamente.
Consulta 5: funcionando conforme esperado
- Execute essa consulta no Editor do BigQuery e responda às seguintes perguntas::
- Navegue pelo painel do conjunto de dados ecommerce para confirmar se
all_sessions_raw_(1)
está presente.
Por que o nome completo da tabela não é mostrado?
Resposta: o sufixo de tabela 20170801 é particionado automaticamente por dia. Se criássemos mais tabelas para outros dias, all_sessions_raw_(N)
aumentaria em N dias diferentes de dados. Há outro laboratório que explica várias maneiras de você particionar suas tabelas de dados.
Clique em Verificar meu progresso para ver o objetivo.
Consulta 6: sua vez de praticar
Objetivo: no "Editor de consultas", crie uma nova tabela permanente que armazene todas as transações com receita para 1º de agosto de 2017.
Use as regras abaixo como um guia:
- Crie uma nova tabela em seu conjunto de dados de ecommerce com o nome revenue_transactions_20170801. Substitua a tabela se já houver uma.
- Extraia seus dados brutos da tabela data-to-insights.ecommerce.all_sessions_raw
- Divida o campo de receita por 1.000.000 e armazene-o como FLOAT64 em vez de INTEGER.
- Na sua tabela final, inclua apenas transações com receita. Uma boa dica é usar uma cláusula WHERE.
- Inclua apenas as transações de 1º de agosto de 2017.
- Inclua estes campos:
- fullVisitorId como um campo de string REQUIRED
- visitId como um campo de string REQUIRED (dica: você precisará realizar uma conversão de tipo)
- channelGrouping como um campo de string REQUIRED
- totalTransactionRevenue como um campo FLOAT64
- Consultando o esquema, adicione descrições curtas para os quatro campos acima.
- Elimine os registros em duplicidade que tenham o mesmo
fullVisitorId
evisitId
(dica: use DISTINCT).
- Escreva a resposta para o prompt acima no BigQuery e compare com a resposta abaixo.
Possível resposta:
-
Depois de executar a consulta, abra seu conjunto de dados ecommerce para verificar se o nome da nova tabela é revenue_transactions_20170801 e selecione-a.
-
Confira o esquema com base no exemplo abaixo. Observe os tipos de campos, que são obrigatórios, e a descrição opcional:
Como processar atualizações de dados upstream
Quais são as melhores opções para evitar dados desatualizados?
Há duas formas de evitar dados desatualizados nas tabelas de relatórios:
- Atualizar com frequência as tabelas permanentes repetindo as consultas inseridas em novos registros. Você pode usar as consultas programadas do BigQuery ou um fluxo de trabalho do Cloud Dataprep / Cloud Dataflow para fazer isso.
- Usar visualizações lógicas para executar novamente uma consulta armazenada sempre que a visualização for selecionada.
No restante deste laboratório, você se concentrará em criar visualizações lógicas.
Clique em Verificar meu progresso para ver o objetivo.
Tarefa 3: Como criar visualizações
As visualizações são consultas salvas executadas toda vez que a visualização é chamada. No BigQuery, as visualizações são lógicas e não materializadas. Apenas a consulta é armazenada como parte da visualização, e não os dados subjacentes.
Consulte as 100 últimas transações
- Copie e cole a consulta abaixo e execute-a no BigQuery:
- Faça uma verificação para filtrar os resultados. Qual foi a última transação acima de US$ 2.000?
Resposta:
date |
fullVisitorId |
visitId |
channelGrouping |
totalTransactionRevenue |
20170801 |
9947542428111966715 |
1501608078 |
Referral |
2.934,61 |
Se novos registros fossem adicionados a esse conjunto de dados público "ecommerce", a transação mais recente também seria atualizada.
- Para economizar tempo e melhorar a organização e a colaboração, você pode salvar como visualizações as consultas que mais faz nos relatórios, conforme demonstrado abaixo:
vw_
ou incluir um sufixo como _vw
ou _view
.
Também é possível adicionar uma descrição e rótulos à sua visualização usando o comando OPTIONS.
- Copie e cole a consulta abaixo e execute-a no BigQuery:
-
Encontre a tabela
vw_latest_transactions
recém-criada no seu conjunto de dados ecommerce e selecione-a. -
Selecione a guia DETALHES.
-
Confirme se os campos Descrição e Marcadores da sua visualização aparecem corretamente na interface do BigQuery.
Também é possível ver a consulta que define a visualização na página "Detalhes". Isso é útil para entender a lógica de visualizações que você ou sua equipe criaram.
Clique em Verificar meu progresso para ver o objetivo.
- Agora, execute esta consulta para criar uma nova visualização:
Error: Already Exists: Table project-name:ecommerce.vw_latest_transactions
Provavelmente ocorrerá um erro se você já tiver criado a visualização. Sabe por quê?
Resposta: a instrução de criação da visualização foi atualizada para ser simplesmente CREATE em vez de CREATE OR REPLACE, o que não permitirá que você substitua tabelas ou visualizações. Uma terceira opção, CREATE VIEW IF NOT EXISTS, só permitirá que você crie a visualização se a tabela ou a visualização não existirem. Caso contrário, ela ignora a criação e nenhum erro é retornado.
Criação de visualizações: agora é sua vez
Situação: sua equipe antifraude pediu que você criasse um relatório que lista as 10 transações mais recentes com pedidos no valor mínimo de 1.000 para revisão manual.
Tarefa: crie uma nova visualização que mostre todas as 10 transações mais recentes com receita superior a 1.000 a partir do dia 1º de janeiro de 2017.
Use estas regras como um guia:
-
Crie uma nova visualização em seu conjunto de dados de e-commerce com o nome "vw_large_transactions". Substitua a visualização se já houver uma.
-
Adicione a descrição "large transactions for review" à visualização.
-
Crie um rótulo para a visualização [("org_unit", "loss_prevention")].
-
Extraia seus dados brutos da tabela
data-to-insights.ecommerce.all_sessions_raw
. -
Divida o campo de receita por 1.000.000.
-
Inclua apenas transações com receita de no mínimo 1.000.
-
Inclua apenas transações a partir de 1º de janeiro de 2017 começando pela mais recente.
-
Inclua apenas currencyCode = 'USD'.
-
Retorne esses campos:
- date
- fullVisitorId
- visitId
- channelGrouping
- totalTransactionRevenue AS revenue
- currencyCode
- v2ProductName
-
Elimine os registros em duplicidade. Uma boa dica para fazer isso é usar DISTINCT.
-
Sua vez:
Possível solução:
Observe que você precisa repetir a divisão na cláusula WHERE porque não é possível usar como filtros nomes de campos com alias.
Clique em Verificar meu progresso para ver o objetivo.
Crédito extra
Situação: seu departamento de combate a fraudes agradeceu pela consulta e está monitorando-a diariamente em busca de pedidos suspeitos. Desta vez, ele solicitou que você inclua uma amostra dos produtos que fazem parte de cada pedido junto aos resultados retornados antes.
Usando a função de agregação de strings do BigQuery STRING_AGG e o campo v2ProductName
, modifique sua consulta anterior para retornar 10 dos nomes de produtos em cada pedido listados em ordem alfabética.
Possível solução:
Observe a inclusão de STRING_AGG () para agregar a lista de produtos em cada pedido e, já que você está executando uma agregação, precisa adicionar GROUP BY aos outros campos.
Use SESSION_USER() em visualizações para limitar o acesso aos dados
Situação: o líder da sua equipe de dados solicitou que você estabelecesse uma maneira de limitar quem da organização pode ver os dados retornados pela visualização recém-criada. As informações de pedidos são bastante sensíveis e só devem ser compartilhadas com os usuários que realmente precisam delas.
Tarefa: modifique a visualização que você criou anteriormente para que apenas usuários que fazem login com um domínio de sessão qwiklabs.net acessem os dados na visualização subjacente. Observação: você criará listas de permissões de grupos de usuários específicos em um laboratório posterior que fala de acesso. Por enquanto, você está fazendo a validação com base no domínio do usuário da sessão.
- Para conferir as informações de login da sua própria sessão, execute a consulta abaixo que usa SESSION_USER ():
Você verá o endereço xxxx@qwiklabs.net.
- Modifique a consulta abaixo para adicionar um filtro e permitir que apenas usuários no domínio
qwiklabs.net
acessem os resultados da visualização:
Possível solução:
- Execute a consulta acima para confirmar se você tem acesso aos registros retornados.
Agora, remova todos os domínios do filtro IN REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') IN ('')
, execute a consulta novamente e confirme se nenhum registro é retornado.
- Crie novamente e substitua a visualização vw_large_transactions pela nova consulta acima. Como um parâmetro OPTIONS extra, adicione
expiration_timestamp
para toda a visualização por 90 dias a partir de agora:
Possível solução:
Clique em Verificar meu progresso para conferir o objetivo.
- Confirme com a instrução SELECT abaixo se você tem acesso aos dados retornados na visualização (de acordo com o acesso do seu domínio) e o carimbo de data/hora de expiração nos detalhes da visualização:
Parabéns!
Você criou tabelas e visualizações controladas por acesso usando a Linguagem de Definição de Dados (DDL) do SQL no BigQuery.
Termine a Quest
Este laboratório autoguiado faz parte da Quest BigQuery for Marketing Analysts. Uma Quest é uma série de laboratórios relacionados que formam um programa de aprendizado. Ao concluir uma Quest, você ganha um selo como reconhecimento da sua conquista. É possível publicar os selos e incluir um link para eles no seu currículo on-line ou nas redes sociais. Inscreva-se nesta Quest e receba o crédito de conclusão na mesma hora. Consulte o catálogo do Google Cloud Ensina para ver todas as Quests disponíveis.
Comece o próximo laboratório
Continue a Quest com o laboratório Como ingerir novos conjuntos de dados no BigQuery ou confira estas sugestões:
- Como criar um data warehouse usando as funções JOIN e UNION
- Como criar tabelas particionadas por data no BigQuery
Próximas etapas / Saiba mais
Você já tem uma conta do Google Analytics e quer consultar seus próprios conjuntos de dados no BigQuery? Siga este guia de exportação.
Manual atualizado em 24 de janeiro de 2023
Laboratório testado em 24 de janeiro de 2023
Copyright 2024 Google LLC. Todos os direitos reservados. Google e o logotipo do Google são marcas registradas da Google LLC. Todos os outros nomes de produtos e empresas podem ser marcas registradas das respectivas empresas a que estão associados.