Por Ajay Lakshminarayanarao e Allen Fung

O Google Bigquery é uma ferramenta poderosa. Um engenheiro de software com uma ideia minimalista de SQL pode utilizá-la com facilidade. Você pode hackear grandes quantidades de dados para obter insights em segundos. No ShareThis nós olhamos para terabytes de dados sociais acionáveis todos os dias e eles se tornaram parte integrante do nosso trabalho de análise, verificação e processamento.

Um dos problemas que enfrentamos recentemente foi a segregação de linhas em uma tabela Bigquery (BQ) baseada no país. O BQ é baseado no armazenamento colunar. Em vez de armazenar todo o registro em um único volume, o BQ divide a linha com base nos valores das colunas e armazena cada valor em seu próprio volume de armazenamento. Para tirar partido deste esquema, concebemos uma solução eficiente de consulta de baixo custo para recuperar valores para cada país, conforme descrito abaixo.

Problema: Segregar uma tabela BQ por país
O Caminho Certo

Para segregar uma tabela BQ por país precisamos de consultar a tabela, tantas vezes quanto o número de países.

Vamos assumir que precisamos de consultar as colunas A, B, C, D e E. O tamanho de cada coluna é de 20GB. O número facturável de bytes será de 100GB para a nossa tabela.

Se precisarmos executar nossa consulta tantas vezes quanto o número de países, ou seja, 240, nosso número faturável de bytes será de 24.000 GB ou quase 24 TB. Digamos que o custo para 1 TB é de 5$, nosso custo total será de 120$ para uma tabela de BQ.

A forma optimizada

O truque aqui é agrupar o número necessário de campos como uma única coluna. Isto por si só não irá salvar o número de bytes faturados. O segundo truque é pivotar pela coluna de segregação; neste caso, será por país.

Após as etapas acima, nossa tabela de resultados conterá tantas colunas quanto os países. Cada coluna representa os dados de um país e terá os dados agrupados anteriormente apenas para esse país.

Na nossa tabela BQ de 100GB, podemos assumir um resultado provável, uma vez que a coluna que representa os EUA é de 20GB, os 10 principais países com uma média de cerca de 3GB e os restantes 230 países com uma média de 0,5GB.

Agora, os nossos bytes faturáveis são baseados no tamanho dos dados de cada país. O custo faturável para consultar os EUA será de apenas 0,1$, o custo para os principais países será de 0,015$ cada e o resto dos países muito menor do que isso.

O total de bytes faturáveis será de 100GB para a tabela agrupada e 165GB para consultas de todos os países combinados. O custo total é de cerca de 1,5$!

Gerar a tabela agrupada

Podemos agrupar as colunas necessárias em uma única coluna usando o CONCAT. Podemos criar uma linha em um formato que represente uma linha em um arquivo CSV ou um objeto JSON válido.

Para a segunda etapa, a consulta consistirá em 240 declarações CASE, com uma declaração CASE para cada país para girar para uma determinada coluna. A consulta será grande, mas o BQ suporta um tamanho de consulta de até 256KB. Podemos combinar ambas as etapas em uma única consulta, onde Concatamos em cada declaração de caso.

Exemplo de consulta:

SELECCIONAR
( CASO QUANDO geo.ISO = 'US' THEN CONCAT( A,B,C,D,E ) ) COMO PAÍSEUA,
( CASO QUANDO geo.ISO = 'CA' ENTÃO CONCAT( A,B,C,D,E ) ) COMO PAÍSCA ...
DE
mergelog_2016010101;