Olá Habitantes! Você se sente intimidado pela necessidade de processar conjuntos de dados de petabyte? Conheça o Google BigQuery, um sistema de armazenamento de informações que pode consolidar dados em toda a empresa, facilitar análises interativas e permitir o aprendizado de máquina. Agora você pode armazenar, consultar, receber e explorar dados com eficiência em um ambiente conveniente. Walyappa Lakshmanan e Jordan Taijani ensinarão a você como trabalhar em um data warehouse moderno usando todo o poder de uma nuvem pública escalonável e sem servidor. Com este livro, você irá: - Mergulhar nos componentes internos do BigQuery - Explorar os tipos de dados, funções e operadores que o Big Query suporta - Otimizar consultas e implementar esquemas para melhorar o desempenho ou reduzir custos - Aprender sobre GIS, viagem no tempo, DDL / DML.funções personalizadas e scripts SQL - Resolva muitos problemas de aprendizado de máquina - Aprenda como proteger dados, controlar o desempenho e autenticar usuários.
Minimizando custos de rede
Minimizando custos de rede O BigQuery é um serviço regional disponível em todo o mundo. Por exemplo, se você estiver solicitando um conjunto de dados armazenado na região da UE, a solicitação será executada em servidores localizados em um data center na União Europeia. Para que você possa armazenar os resultados da consulta em uma tabela, ela deve estar em um conjunto de dados que também esteja na região da UE. No entanto, a API REST do BigQuery pode ser chamada (ou seja, executar uma consulta) de qualquer lugar do mundo, até mesmo de computadores fora do GCP. Ao trabalhar com outros recursos do GCP, como Google Cloud Storage ou Cloud Pub / Sub, o melhor desempenho é obtido se eles estiverem na mesma região do conjunto de dados. Portanto, se a solicitação for executada a partir de uma instância do Compute Engine ou cluster do Cloud Dataproc, a sobrecarga da rede será mínima,se a instância ou cluster também estiver na mesma região que o conjunto de dados solicitado. Ao acessar o BigQuery de fora do GCP, considere sua topologia de rede e tente minimizar o número de saltos entre o computador cliente e o centro do GCP onde o conjunto de dados reside.
Respostas concisas e incompletas
Acessando diretamente a API REST, a sobrecarga da rede pode ser reduzida aceitando respostas concisas e incompletas. Para aceitar respostas compactadas, você pode especificar no cabeçalho HTTP que está pronto para aceitar um arquivo gzip e garantir que a linha "gzip" esteja presente no cabeçalho do agente do usuário, por exemplo:
Accept-Encoding: gzip
User-Agent: programName (gzip)Nesse caso, todas as respostas serão compactadas usando gzip. Por padrão, as respostas do BigQuery contêm todos os campos listados na documentação. No entanto, se soubermos em qual parte da resposta estamos interessados, podemos pedir ao BigQuery para enviar apenas essa parte, reduzindo assim a sobrecarga da rede. Por exemplo, neste capítulo, vimos como obter informações completas sobre um trabalho usando a API de trabalhos. Se você estiver interessado apenas em um subconjunto da resposta completa (por exemplo, apenas as etapas no plano de consulta), poderá especificar os campos de interesse para limitar o tamanho da resposta:
JOBSURL="https://www.googleapis.com/bigquery/v2/projects/$PROJECT/jobs"
FIELDS="statistics(query(queryPlan(steps)))"
curl --silent \
-H "Authorization: Bearer $access_token" \
-H "Accept-Encoding: gzip" \
-H "User-Agent: get_job_details (gzip)" \
-X GET \
"${JOBSURL}/${JOBID}?fields=${FIELDS}" \
| zcatObserve que também afirma que aceitamos dados compactados gzip.
Combinar vários pedidos em pacotes
Ao usar a API REST, é possível combinar várias chamadas da API BigQuery usando o tipo de conteúdo multipart / mixed e solicitações HTTP aninhadas em cada parte. O corpo de cada parte especifica a operação HTTP (GET, PUT, etc.), o caminho para a URL, cabeçalhos e corpo. Em resposta, o servidor enviará uma única resposta HTTP com o tipo de conteúdo multipart / misto, cada parte contendo a resposta (em ordem) à solicitação correspondente na solicitação em lote. Embora as respostas sejam retornadas em uma ordem específica, o servidor pode processar chamadas em qualquer ordem. Portanto, uma solicitação em lote pode ser considerada um grupo de solicitações executadas em paralelo. Aqui está um exemplo de envio de uma solicitação em lote para obter alguns detalhes dos planos de execução das últimas cinco solicitações em nosso projeto. Primeiro usamos a ferramenta de linha de comando do BigQuery,para obter as últimas cinco missões bem-sucedidas:
# 5
JOBS=$(bq ls -j -n 50 | grep SUCCESS | head -5 | awk '{print $1}')A solicitação é enviada ao endpoint do BigQuery para processamento em lote:
BATCHURL="https://www.googleapis.com/batch/bigquery/v2"
JOBSPATH="/projects/$PROJECT/jobs"
FIELDS="statistics(query(queryPlan(steps)))"Solicitações individuais podem ser definidas no caminho do URL:
request=""
for JOBID in $JOBS; do
read -d '' part << EOF
--batch_part_starts_here
GET ${JOBSPATH}/${JOBID}?fields=${FIELDS}
EOF
request=$(echo "$request"; echo "$part")
doneEm seguida, você pode enviar a solicitação como uma solicitação composta:
curl --silent \
-H "Authorization: Bearer $access_token" \
-H "Content-Type: multipart/mixed; boundary=batch_part_starts_here" \
-X POST \
-d "$request" \
"${BATCHURL}"Leitura em massa usando a API BigQuery Storage
No Capítulo 5, discutimos o uso da API REST do BigQuery e bibliotecas de cliente para enumerar tabelas e recuperar resultados de consulta. A API REST retorna dados como registros paginados que são mais adequados para conjuntos de resultados relativamente pequenos. No entanto, com o advento do aprendizado de máquina e das ferramentas distribuídas de extração, transformação e carregamento (ETL), as ferramentas externas agora exigem acesso em massa rápido e eficiente ao repositório gerenciado do BigQuery. Esse acesso de leitura em massa é fornecido na API BigQuery Storage por meio do protocolo Remote Procedure Call (RPC). Com a API de armazenamento do BigQuery, os dados estruturados são transmitidos pela rede em um formato de serialização binário que mais se aproxima do formato de armazenamento de dados colunar.Isso fornece paralelização adicional do conjunto de resultados em vários consumidores.
Os usuários finais não usam a API BigQuery Storage diretamente. Em vez disso, eles usam Cloud Dataflow, Cloud Dataproc, TensorFlow, AutoML e outras ferramentas que usam a API Storage para ler dados diretamente do armazenamento gerenciado, em vez da API BigQuery.
Como a API Storage acessa os dados armazenados diretamente, a permissão para acessar a API BigQuery Storage é diferente da API BigQuery existente. As permissões da API BigQuery Storage devem ser configuradas independentemente das permissões do BigQuery.
A API BigQuery Storage fornece vários benefícios para ferramentas que leem dados diretamente do armazenamento gerenciado do BigQuery. Por exemplo, os consumidores podem ler conjuntos de registros não sobrepostos de uma tabela usando vários threads (por exemplo, permitindo leituras distribuídas de diferentes servidores de produção no Cloud Dataproc), segmentando dinamicamente esses threads (reduzindo assim a latência de cauda, que pode ser um problema sério para jobs MapReduce) , selecione um subconjunto de colunas para ler (para passar apenas os recursos usados pelo modelo para estruturas de aprendizado de máquina), filtre os valores da coluna (reduza a quantidade de dados transmitidos pela rede) e ainda garanta a consistência dos instantâneos (ou seja, leitura de dados de um determinado ponto no tempo).
No Capítulo 5, cobrimos o uso da extensão %% bigquery no Jupyter Notebook para carregar os resultados da consulta em DataFrames. No entanto, os exemplos usaram conjuntos de dados relativamente pequenos - de uma dúzia a várias centenas de registros. É possível carregar todo o conjunto de dados london_bicycles (24 milhões de registros) em um DataFrame? Sim, você pode, mas, neste caso, você deve usar a API Storage, não a API BigQuery, para carregar dados no DataFrame. Primeiro, você precisa instalar a biblioteca de cliente da API de armazenamento Python com suporte para Avro e pandas. Isso pode ser feito com o comando
%pip install google-cloud-bigquery-storage[fastavro,pandas]Então, tudo o que resta é usar a extensão %% bigquery, como antes, mas adicionar um parâmetro que requer que a API de armazenamento seja usada:
%%bigquery df --use_bqstorage_api --project $PROJECT
SELECT
start_station_name
, end_station_name
, start_date
, duration
FROM `bigquery-public-data`.london_bicycles.cycle_hireObserve que estamos aproveitando a capacidade da API de armazenamento de fornecer acesso direto a colunas individuais aqui; não é necessário ler toda a tabela do BigQuery em um DataFrame. Se a solicitação retornar uma pequena quantidade de dados, a extensão usará automaticamente a API do BigQuery. Portanto, não assusta se você sempre indicar essa bandeira nas células do notebook. Para ativar o sinalizador --usebqstorageapi em todas as células do bloco de notas, você pode definir o sinalizador de contexto:
import google.cloud.bigquery.magics
google.cloud.bigquery.magics.context.use_bqstorage_api = TrueEscolha de um formato de armazenamento eficiente
O desempenho de uma consulta depende de onde e em que formato os dados que compõem a tabela estão armazenados. Em geral, quanto menos a consulta precisar realizar pesquisas ou conversões de tipo, melhor será o desempenho.
Fontes de dados internas e externas O
BigQuery oferece suporte a consultas em fontes externas, como Google Cloud Storage, Cloud Bigtable e Planilhas Google, mas você só pode obter o melhor desempenho com suas próprias tabelas.
Recomendamos usar o BigQuery como um repositório de dados analíticos para todos os seus dados estruturados e semiestruturados. As fontes de dados externas são mais utilizadas para armazenamento temporário (Google Cloud Storage), uploads ao vivo (Cloud Pub / Sub, Cloud Bigtable) ou atualizações periódicas (Cloud SQL, Cloud Spanner). Em seguida, configure seu pipeline de dados para carregar dados em uma programação dessas fontes externas no BigQuery (consulte o Capítulo 4).
Se você precisar solicitar dados do Google Cloud Storage, salve-os em um formato colunar compactado (como Parquet), se possível. Use formatos baseados em registro, como JSON ou CSV, como último recurso.
Staging Bucket Lifecycle Management
Se você fizer upload de dados para o BigQuery depois de colocá-los no Google Cloud Storage, certifique-se de excluí-los da nuvem após o upload. Se você usar o pipeline ETL para carregar dados no BigQuery (para transformá-los significativamente ou deixar apenas parte dos dados ao longo do caminho), convém salvar os dados originais no Google Cloud Storage. Nesses casos, você pode ajudar a reduzir custos definindo regras de gerenciamento do ciclo de vida do intervalo que reduzem o armazenamento no Google Cloud Storage.
Veja como você pode ativar o gerenciamento do ciclo de vida do bucket e configurar a movimentação automática de dados de regiões federadas ou classes padrão com mais de 30 dias para o Nearline Storage e dados armazenados no Nearline Storage por mais de 90 dias para o Coldline Storage:
gsutil lifecycle set lifecycle.yaml gs://some_bucket/Neste exemplo, o arquivo lifecycle.yaml contém o seguinte código:
{
"lifecycle": {
"rule": [
{
"action": {
"type": "SetStorageClass",
"storageClass": "NEARLINE"
},
"condition": {
"age": 30,
"matchesStorageClass": ["MULTI_REGIONAL", "STANDARD"]
}
},
{
"action": {
"type": "SetStorageClass",
"storageClass": "COLDLINE"
},
"condition": {
"age": 90,
"matchesStorageClass": ["NEARLINE"]
}
}
]}}Você pode usar o gerenciamento do ciclo de vida não apenas para alterar a classe de um objeto, mas também para remover objetos mais antigos do que um determinado limite.
Armazenamento de dados como matrizes e estruturas
Além de outros conjuntos de dados disponíveis publicamente, o BigQuery tem um conjunto de dados contendo informações sobre tempestades ciclônicas (furacões, tufões, ciclones etc.) de serviços meteorológicos em todo o mundo. As tempestades ciclônicas podem durar várias semanas e seus parâmetros meteorológicos são medidos aproximadamente a cada três horas. Suponha que você decida encontrar neste conjunto de dados todas as tempestades que ocorreram em 2018, a velocidade máxima do vento atingida por cada tempestade e a hora e o local da tempestade quando essa velocidade máxima foi atingida. A consulta a seguir recupera todas essas informações do conjunto de dados público:
SELECT
sid, number, basin, name,
ARRAY_AGG(STRUCT(iso_time, usa_latitude, usa_longitude, usa_wind) ORDER BY
usa_wind DESC LIMIT 1)[OFFSET(0)].*
FROM
`bigquery-public-data`.noaa_hurricanes.hurricanes
WHERE
season = '2018'
GROUP BY
sid, number, basin, name
ORDER BY number ASCA consulta recupera o identificador da tempestade (sid), suas estações, piscina e nome da tempestade (se atribuído) e, em seguida, encontra uma série de observações feitas para aquela tempestade, classificando as observações em ordem decrescente de velocidade do vento e escolhendo a velocidade máxima para cada tempestade ... As próprias tempestades são ordenadas por número sequencial. O resultado inclui 88 registros e se parece com isto:
A solicitação levou 1,4 segundos e processou 41,7 MB. A primeira entrada descreve a tempestade Bolaven, que atingiu uma velocidade máxima de 29 m / s em 2 de janeiro de 2018 às 18:00 UTC.
Como as observações são feitas por vários serviços meteorológicos, esses dados podem ser padronizados usando campos aninhados e armazenados no BigQuery, conforme mostrado abaixo:
CREATE OR REPLACE TABLE ch07.hurricanes_nested AS
SELECT sid, season, number, basin, name, iso_time, nature, usa_sshs,
STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
tokyo_wind AS wind, tokyo_pressure AS pressure) AS tokyo,
... AS cma,
... AS hko,
... AS newdelhi,
... AS reunion,
... bom,
... AS wellington,
... nadi
FROM `bigquery-public-data`.noaa_hurricanes.hurricanesAs consultas nesta tabela são semelhantes às consultas na tabela original, mas com uma ligeira alteração nos nomes das colunas (usa.latitude em vez de usa_latitude):
SELECT
sid, number, basin, name,
ARRAY_AGG(STRUCT(iso_time, usa.latitude, usa.longitude, usa.wind) ORDER BY
usa.wind DESC LIMIT 1)[OFFSET(0)].*
FROM
ch07.hurricanes_nested
WHERE
season = '2018'
GROUP BY
sid, number, basin, name
ORDER BY number ASCEssa solicitação processa a mesma quantidade de dados e é executada na mesma quantidade de tempo que a original, usando o conjunto de dados público. O uso de campos aninhados (estruturas) não altera a velocidade ou custo da consulta, mas pode torná-la mais legível. Uma vez que existem muitas observações da mesma tempestade durante sua duração, podemos alterar o armazenamento para caber todo o conjunto de observações para cada tempestade em um registro:
CREATE OR REPLACE TABLE ch07.hurricanes_nested_track AS
SELECT sid, season, number, basin, name,
ARRAY_AGG(
STRUCT(
iso_time,
nature,
usa_sshs,
STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
tokyo_wind AS wind, tokyo_pressure AS pressure) AS tokyo,
... AS cma,
... AS hko,
... AS newdelhi,
... AS reunion,
... bom,
... AS wellington,
... nadi
) ORDER BY iso_time ASC ) AS obs
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes
GROUP BY sid, season, number, basin, nameObserve que agora armazenamos o sid, a estação e outras características da tempestade como colunas escalares, porque eles não mudam dependendo de sua duração.
O resto dos dados, mudando a cada observação, são armazenados como uma série de estruturas. Esta é a aparência da consulta para a nova tabela:
SELECT
number, name, basin,
(SELECT AS STRUCT iso_time, usa.latitude, usa.longitude, usa.wind
FROM UNNEST(obs) ORDER BY usa.wind DESC LIMIT 1).*
FROM ch07.hurricanes_nested_track
WHERE season = '2018'
ORDER BY number ASCEssa solicitação retornará o mesmo resultado, mas desta vez processará apenas 14,7 MB (uma redução de custo três vezes maior) e será concluída em um segundo (um aumento de 30% na velocidade). O que causou essa melhoria de desempenho? Quando os dados são armazenados como uma matriz, o número de registros na tabela cai drasticamente (de 682.000 para 14.000), 2 porque agora há apenas um registro por tempestade, não muitos registros - um para cada observação. Então, quando filtramos as linhas por temporada, o BigQuery pode descartar muitos casos relacionados ao mesmo tempo, conforme mostrado na Figura 1. 7,13.
Outra vantagem é que não há necessidade de duplicar os registros de dados quando casos com diferentes níveis de detalhe são armazenados na mesma tabela. Uma tabela pode armazenar dados de alteração de latitude e longitude para tempestades e dados de alto nível, como nomes de tempestades e estação. E como o BigQuery armazena dados tabulares em colunas usando compactação, você pode consultar e processar dados de alto nível sem medo do custo de trabalhar com dados detalhados - agora eles são armazenados como uma matriz separada de valores para cada tempestade.
Por exemplo, para descobrir o número de tempestades por ano, você pode consultar apenas as colunas obrigatórias:
WITH hurricane_detail AS (
SELECT sid, season, number, basin, name,
ARRAY_AGG(
STRUCT(
iso_time,
nature,
usa_sshs,
STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
tokyo_wind
AS wind, tokyo_pressure AS pressure) AS tokyo
) ORDER BY iso_time ASC ) AS obs
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes
GROUP BY sid, season, number, basin, name
)
SELECT
COUNT(sid) AS count_of_storms,
season
FROM hurricane_detail
GROUP BY season
ORDER BY season DESCA solicitação anterior processou 27 MB, que é metade dos 56 MB que teriam de ser processados se os campos de repetição aninhados não fossem usados.
Os campos aninhados por si só não melhoram o desempenho, embora possam melhorar a legibilidade ao realizar uma junção com outras tabelas relacionadas. Além disso, os campos de repetição aninhados são extremamente úteis do ponto de vista de desempenho. Considere o uso de campos de repetição aninhados em seu esquema porque eles podem aumentar drasticamente a velocidade e reduzir o custo da filtragem de consultas em uma coluna não aninhada ou de repetição (em nosso caso, temporada).
A principal desvantagem de campos de repetição aninhados é a dificuldade de implementar streaming em tal tabela se as atualizações de streaming envolverem a adição de itens a matrizes existentes. Isso é muito mais difícil de implementar do que adicionar novos registros: você precisará modificar um registro existente - para a tabela de informações sobre tempestades, esta é uma desvantagem significativa, pois novas observações são constantemente adicionadas a ela, e isso explica porque este conjunto de dados público não usa duplicatas aninhadas Campos.
Prática de uso de matrizes
A experiência tem mostrado que é necessária alguma prática para usar campos de repetição aninhados com êxito. O conjunto de dados de amostra do Google Analytics no BigQuery é ideal para essa finalidade. A maneira mais fácil de identificar dados aninhados em um esquema é encontrar a palavra RECORD na coluna Type, que corresponde ao tipo de dados STRUCT, e a palavra REPEATED na coluna Mode, conforme mostrado abaixo:
Neste exemplo, o campo TOTALS é STRUCT (mas não repetido) e o campo HITS é STRUCT e se repete. Isso faz algum sentido, porque o Google Analytics rastreia os dados da sessão do visitante no nível de agregação (um valor de sessão para totals.hits) e no nível de granularidade (valores hit.time separados para cada página e imagens recuperadas de seu site) ... O armazenamento de dados nesses diferentes níveis de detalhe sem duplicar o visitorId nos registros só é possível com matrizes. Depois de salvar os dados em um formato de repetição com matrizes, você precisa considerar a implantação desses dados em suas solicitações usando UNNEST, por exemplo:
SELECT DISTINCT
visitId
, totals.pageviews
, totals.timeOnsite
, trafficSource.source
, device.browser
, device.isMobile
, h.page.pageTitle
FROM
`bigquery-public-data`.google_analytics_sample.ga_sessions_20170801,
UNNEST(hits) AS h
WHERE
totals.timeOnSite IS NOT NULL AND h.page.pageTitle =
'Shopping Cart'
ORDER BY pageviews DESC
LIMIT 10
, [1,2,3,4,5] :
[1,
2
3
4
5]Em seguida, você pode executar operações SQL normais, como WHERE, para filtrar ocorrências em páginas com títulos como Carrinho de compras. Tente!
Por outro lado, o conjunto de dados de informações de commit público do GitHub (bigquery-publicdata.githubrepos.commits) usa um campo de repetição aninhado (reponame) para armazenar a lista de repositórios afetados pelo commit. Ele não muda com o tempo e fornece consultas mais rápidas que filtram qualquer outro campo.
Armazenamento de dados como tipos geográficos
O conjunto de dados público do BigQuery contém uma tabela de limites de área do CEP dos EUA (bigquery-public-data.utilityus.zipcodearea) e outra tabela com polígonos que descrevem os limites de cidades dos EUA (bigquery-publicdata.utilityus.uscitiesarea). Uma coluna zipcodegeom é uma string, enquanto uma coluna city_geom é um tipo geográfico.
A partir dessas duas tabelas, você pode obter uma lista de todos os códigos postais de Santa Fé no Novo México, conforme mostrado abaixo:
SELECT name, zipcode
FROM `bigquery-public-data`.utility_us.zipcode_area
JOIN `bigquery-public-data`.utility_us.us_cities_area
ON ST_INTERSECTS(ST_GeogFromText(zipcode_geom), city_geom)
WHERE name LIKE '%Santa Fe%'Essa consulta leva 51,9 segundos, processa 305,5 MB de dados e retorna os seguintes resultados:
Por que essa solicitação está demorando tanto? Não por causa da operação STINTERSECTS, mas principalmente porque a função STGeogFromText deve avaliar as células S2 e construir o tipo GEOGRAPHY correspondente a cada código postal.
Podemos tentar modificar a tabela de código postal fazendo isso de antemão e armazenar a geometria como um valor GEOGRAPHY:
CREATE OR REPLACE TABLE ch07.zipcode_area AS
SELECT
* REPLACE(ST_GeogFromText(zipcode_geom) AS zipcode_geom)
FROM
`bigquery-public-data`.utility_us.zipcode_areaREPLACE (consulte a consulta anterior) é uma maneira conveniente de substituir uma coluna de uma expressão SELECT *.O novo conjunto de dados tem 131,8 MB, o que é significativamente maior do que os 116,5 MB da tabela original. No entanto, as consultas nesta tabela podem usar a cobertura S2 e são muito mais rápidas. Por exemplo, a consulta a seguir leva 5,3 segundos (um aumento de 10x na velocidade) e processa 320,8 MB (um ligeiro aumento no custo ao usar um plano tarifário "sob demanda"):
SELECT name, zipcode
FROM ch07.zipcode_area
JOIN `bigquery-public-data`.utility_us.us_cities_area
ON ST_INTERSECTS(zipcode_geom, city_geom)
WHERE name LIKE '%Santa Fe%'Os benefícios de desempenho de armazenar dados geográficos em uma coluna GEOGRAFIA são mais do que atraentes. É por isso que o conjunto de dados utilityus está obsoleto (ainda está disponível para manter as consultas já gravadas) ativo. Recomendamos o uso da tabela bigquery-public-data.geousboundaries.uszip_codes, que armazena informações geográficas em uma coluna GEOGRAPHY e é constantemente atualizada.
»Mais detalhes sobre o livro podem ser encontrados no site da editora
» Índice
» Trecho
Para Habitantes desconto de 25% no cupom - Google No
ato do pagamento da versão em papel do livro, é enviado um e-book por e-mail.