Informações gerais

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 ecommerce com milhões de registros do Google Analytics referentes à Google Merchandise Store e carregados no BigQuery. Você tem uma cópia do conjunto de dados usado neste laboratório e 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 existente.

Configuração e requisitos

Para cada laboratório, você recebe um novo projeto do Google Cloud e um conjunto de recursos por um determinado período e sem custos financeiros.

1. Faça login no Qwiklabs em uma janela anônima.

2. Confira o tempo de acesso do laboratório (por exemplo, 1:15:00) e finalize todas as atividades nesse prazo.
   Não é possível pausar o laboratório. Você pode reiniciar o desafio, mas vai precisar refazer todas as etapas.

3. Quando tudo estiver pronto, clique em Começar o laboratório.

4. Anote as credenciais (Nome de usuário e Senha). É com elas que você vai fazer login no Console do Google Cloud.

5. Clique em Abrir Console do Google.

6. Clique em Usar outra conta, depois copie e cole as credenciais deste laboratório nos locais indicados.
   Se você usar outras credenciais, vai receber mensagens de erro ou cobranças.

7. Aceite os termos e pule a página de recursos de recuperação.

Abra o BigQuery no Console do Cloud

1. No Console do Google Cloud, selecione o menu de navegação > BigQuery:

Você vai 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 lista as atualizações da interface.

2. Clique em Concluído.

Tarefa 1: Crie um novo conjunto de dados para armazenar as tabelas

1. No BigQuery, clique em visualizar ações próximo ao nome do projeto e, em seguida, clique em Criar conjunto de dados.

2. No campo ID do conjunto de dados, digite E-commerce. Não altere as outras opções (Local dos dados, Expiração da tabela padrão).

3. Clique em Create dataset.

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:

1. A lista de colunas especificada ou as colunas inferidas de query_statement (ou as duas) precisam estar presentes.
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.
3. 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 coluna com base na cláusula as query_statement.
4. É necessário especificar os nomes das colunas pela lista de colunas ou pela cláusula as query_statement.
5. Não é permitido duplicar nomes de colunas.

Consulta 1: colunas e mais colunas

• Adicione essa consulta no EDITOR, clique em Executar, identifique o erro e responda às seguintes perguntas:

#standardSQL # copy one day of ecommerce data to explore CREATE OR REPLACE TABLE ecommerce.all_sessions_raw_20170801 OPTIONS( description="Raw data from analyst team into our dataset for 08/01/2017" ) AS SELECT fullVisitorId, * FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' #56,989 records ;

Error: CREATE TABLE has columns with duplicate name fullVisitorId at [8:2]

A consulta acima violou qual das regras da instrução CREATE TABLE?

Consulta 2: repensando as colunas

• Adicione essa consulta no EDITOR, clique em Executar, identifique o erro e responda às seguintes perguntas:

#standardSQL # copy one day of ecommerce data to explore CREATE OR REPLACE TABLE ecommerce.all_sessions_raw_20170801 #schema ( fullVisitorId STRING OPTIONS(description="Unique visitor ID"), channelGrouping STRING OPTIONS(description="Channel e.g. Direct, Organic, Referral...") ) OPTIONS( description="Raw data from analyst team into our dataset for 08/01/2017" ) AS SELECT * FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' #56,989 records ;

Error: The number of columns in the column definition list does not match the number of columns produced by the query at [6:1]

A consulta acima violou qual das regras da instrução CREATE TABLE?

Nota: não é possível especificar um esquema de campos para uma nova tabela que não corresponda ao número de colunas retornadas pela instrução de consulta. No exemplo acima, um esquema de duas colunas foi especificado com fullVisitorId e channelGrouping, mas, na instrução de consulta, todas as colunas retornadas (\*) foram especificadas.

Consulta 3: é válido! Ou será que não?

• Adicione essa consulta no EDITOR, clique em Executar, identifique o erro e responda às seguintes perguntas:

#standardSQL # copy one day of ecommerce data to explore CREATE OR REPLACE TABLE ecommerce.all_sessions_raw_20170801 #schema ( fullVisitorId STRING OPTIONS(description="Unique visitor ID"), channelGrouping STRING OPTIONS(description="Channel e.g. Direct, Organic, Referral...") ) OPTIONS( description="Raw data from analyst team into our dataset for 08/01/2017" ) AS SELECT fullVisitorId, city FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' #56,989 records ;

Valid: This query will process 1.05 GB 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.

Consulta 4: o vigia

1. Execute a consulta abaixo no EDITOR, identifique o erro e responda às seguintes perguntas:

#standardSQL # copy one day of ecommerce data to explore CREATE OR REPLACE TABLE ecommerce.all_sessions_raw_20170801 #schema ( fullVisitorId STRING NOT NULL OPTIONS(description="Unique visitor ID"), channelGrouping STRING NOT NULL OPTIONS(description="Channel e.g. Direct, Organic, Referral..."), totalTransactionRevenue INT64 NOT NULL OPTIONS(description="Revenue * 10^6 for the transaction") ) OPTIONS( description="Raw data from analyst team into our dataset for 08/01/2017" ) AS SELECT fullVisitorId, channelGrouping, totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' #56,989 records ;

Valid: This query will process 907.52 MB when run.

2. Corrija e execute novamente a consulta modificada para confirmar se ela foi executada corretamente.

Consulta 5: funcionando conforme o desejado

1. Execute essa consulta no EDITOR, identifique o erro e responda às seguintes perguntas:

#standardSQL # copy one day of ecommerce data to explore CREATE OR REPLACE TABLE ecommerce.all_sessions_raw_20170801 #schema ( fullVisitorId STRING NOT NULL OPTIONS(description="Unique visitor ID"), channelGrouping STRING NOT NULL OPTIONS(description="Channel e.g. Direct, Organic, Referral..."), totalTransactionRevenue INT64 OPTIONS(description="Revenue * 10^6 for the transaction") ) OPTIONS( description="Raw data from analyst team into our dataset for 08/01/2017" ) AS SELECT fullVisitorId, channelGrouping, totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' #56,989 records ;

2. 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.

Consulta 6: sua vez de praticar

1. 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.
• Lembre-se de eliminar registros em duplicidade que tenham o mesmo fullVisitorId e visitId (dica: use DISTINCT)

/* escreva a resposta para o prompt acima em BigQuery e compare com a resposta abaixo */

Possível resposta:

#standardSQL # copy one day of ecommerce data to explore CREATE OR REPLACE TABLE ecommerce.revenue_transactions_20170801 #schema ( fullVisitorId STRING NOT NULL OPTIONS(description="Unique visitor ID"), visitId STRING NOT NULL OPTIONS(description="ID of the session, not unique across all users"), channelGrouping STRING NOT NULL OPTIONS(description="Channel e.g. Direct, Organic, Referral..."), totalTransactionRevenue FLOAT64 NOT NULL OPTIONS(description="Revenue for the transaction") ) OPTIONS( description="Revenue transactions for 08/01/2017" ) AS SELECT DISTINCT fullVisitorId, CAST(visitId AS STRING) AS visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' AND totalTransactionRevenue IS NOT NULL #XX transactions ;

2. 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.

3. Confira o esquema com base no exemplo abaixo. Observe os tipos de campo, 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:

1. 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.
2. 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.

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

1. Copie e cole a consulta abaixo e execute-a no BigQuery:

#standardSQL SELECT DISTINCT date, fullVisitorId, CAST(visitId AS STRING) AS visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE totalTransactionRevenue IS NOT NULL ORDER BY date DESC # latest transactions LIMIT 100 ;

2. Examine os dados 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.

3. 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:

#standardSQL CREATE OR REPLACE VIEW ecommerce.vw_latest_transactions AS SELECT DISTINCT date, fullVisitorId, CAST(visitId AS STRING) AS visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE totalTransactionRevenue IS NOT NULL ORDER BY date DESC # latest transactions LIMIT 100 ; Nota: normalmente, é difícil saber apenas pelo nome se você está selecionando de uma tabela ou de uma visualização. Uma convenção simples é prefixar o nome da visualização com vw_ ou incluir um sufixo como _vw ou _view.

4. 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:

#standardSQL CREATE OR REPLACE VIEW ecommerce.vw_latest_transactions OPTIONS( description="latest 100 ecommerce transactions", labels=[('report_type','operational')] ) AS SELECT DISTINCT date, fullVisitorId, CAST(visitId AS STRING) AS visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE totalTransactionRevenue IS NOT NULL ORDER BY date DESC # latest transactions LIMIT 100 ;

5. Encontre a tabela vw_latest_transactions recém-criada no seu conjunto de dados "ecommerce" e selecione-a.

6. Selecione a guia Detalhes.

7. Confirme que os campos Descrição e Marcadores da sua visualização aparecem corretamente na IU do BigQuery.

Também é possível ver a consulta que define a exibição na página "Detalhes". Isso é útil para entender a lógica de visualizações que você ou sua equipe criaram.

8. Agora, execute esta consulta para criar uma nova visualização:

#standardSQL # top 50 latest transactions CREATE VIEW ecommerce.vw_latest_transactions # CREATE OPTIONS( description="latest 50 ecommerce transactions", labels=[('report_type','operational')] ) AS SELECT DISTINCT date, fullVisitorId, CAST(visitId AS STRING) AS visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE totalTransactionRevenue IS NOT NULL ORDER BY date DESC # latest transactions LIMIT 50 ;

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ê sobrescreva 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 nenhuma erro é retornado.

Criação de visualizações: agora é sua vez

Situação: sua equipe de combate a fraudes 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:
  • data
  • fullVisitorId
  • visitId
  • channelGrouping
  • totalTransactionRevenue AS revenue
  • currencyCode
  • v2ProductName
• Elimine os registros em duplicidade. Uma boa dica para fazer isso é usar DISTINCT.

Sua vez

/* escreva a resposta para o prompt acima em BigQuery e compare com a resposta abaixo */

Possível solução:

#standardSQL CREATE OR REPLACE VIEW ecommerce.vw_large_transactions OPTIONS( description="large transactions for review", labels=[('org_unit','loss_prevention')] ) AS SELECT DISTINCT date, fullVisitorId, visitId, channelGrouping, totalTransactionRevenue / 1000000 AS revenue, currencyCode #v2ProductName FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE (totalTransactionRevenue / 1000000) > 1000 AND currencyCode = 'USD' ORDER BY date DESC # latest transactions LIMIT 10 ; Nota: Você precisa repetir a divisão na cláusula WHERE, porque não pode usar nomes de campo com alias como filtros.

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:

#standardSQL CREATE OR REPLACE VIEW ecommerce.vw_large_transactions OPTIONS( description="large transactions for review", labels=[('org_unit','loss_prevention')] ) AS SELECT DISTINCT date, fullVisitorId, visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue, currencyCode, STRING_AGG(DISTINCT v2ProductName ORDER BY v2ProductName LIMIT 10) AS products_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE (totalTransactionRevenue / 1000000) > 1000 AND currencyCode = 'USD' GROUP BY 1,2,3,4,5,6 ORDER BY date DESC # latest transactions LIMIT 10

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 confidenciais 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. Nota: 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.

1. Para ver as informações de login da sua própria sessão, execute a consulta abaixo que usa SESSION_USER ():

#standardSQL SELECT SESSION_USER() AS viewer_ldap;

Você verá o endereço xxxx@qwiklabs.net.

2. 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:

#standardSQL SELECT DISTINCT SESSION_USER() AS viewer_ldap, REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') AS domain, date, fullVisitorId, visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue, currencyCode, STRING_AGG(DISTINCT v2ProductName ORDER BY v2ProductName LIMIT 10) AS products_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE (totalTransactionRevenue / 1000000) > 1000 AND currencyCode = 'USD' # add filter here GROUP BY 1,2,3,4,5,6,7,8 ORDER BY date DESC # latest transactions LIMIT 10

Possível solução:

#standardSQL SELECT DISTINCT SESSION_USER() AS viewer_ldap, REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') AS domain, date, fullVisitorId, visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue, currencyCode, STRING_AGG(DISTINCT v2ProductName ORDER BY v2ProductName LIMIT 10) AS products_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE (totalTransactionRevenue / 1000000) > 1000 AND currencyCode = 'USD' AND REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') IN ('qwiklabs.net') GROUP BY 1,2,3,4,5,6,7,8 ORDER BY date DESC # latest transactions LIMIT 10

3. Execute a consulta acima para confirmar se você tem acesso aos registros retornados.

4. 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.

5. 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:

expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 90 DAY).

Possível solução:

#standardSQL CREATE OR REPLACE VIEW ecommerce.vw_large_transactions OPTIONS( description="large transactions for review", labels=[('org_unit','loss_prevention')], expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 90 DAY) ) AS #standardSQL SELECT DISTINCT SESSION_USER() AS viewer_ldap, REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') AS domain, date, fullVisitorId, visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue, currencyCode, STRING_AGG(DISTINCT v2ProductName ORDER BY v2ProductName LIMIT 10) AS products_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE (totalTransactionRevenue / 1000000) > 1000 AND currencyCode = 'USD' AND REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') IN ('qwiklabs.net') GROUP BY 1,2,3,4,5,6,7,8 ORDER BY date DESC # latest transactions LIMIT 10; Nota: a opção expiration_timestamp option também pode ser aplicada a tabelas permanentes.

6. 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.

#standardSQL SELECT * FROM ecommerce.vw_large_transactions;

Parabéns!

Você criou tabelas e visualizações controladas por acesso usando a Linguagem de Definição de Dados (DDL) do SQL no BigQuery.

Finalize o laboratório

Clique em Terminar o laboratório após a conclusão. O Google Cloud Ensina remove os recursos usados e limpa a conta por você.

Você vai poder avaliar sua experiência no laboratório. Basta selecionar o número de estrelas, digitar um comentário e clicar em Enviar.

O número de estrelas indica o seguinte:

• 1 estrela = muito insatisfeito
• 2 estrelas = insatisfeito
• 3 estrelas = neutro
• 4 estrelas = satisfeito
• 5 estrelas = muito satisfeito

Feche a caixa de diálogo se não quiser enviar feedback.

Para enviar seu feedback, fazer sugestões ou correções, use a guia Suporte.

Copyright 2026 Google LLC. Todos os direitos reservados. Google e o logotipo do Google são marcas registradas da Google LLC. Todos os outros nomes de empresas e produtos podem ser marcas registradas das empresas a que estão associados.