Por Ajay Lakshminarayanarao y Allen Fung

Google Bigquery es una poderosa herramienta. Un ingeniero de software que tiene una idea minimalista de SQL puede usarlo con facilidad. Usted puede hackear grandes cantidades de datos para Insights en segundos. En ShareThis nos fijamos en terabytes de datos sociales procesables todos los días y se ha convertido en una parte integral de nuestro trabajo para el análisis, los cheques y el procesamiento.

Uno de los problemas recientes que enfrentamos fue separar filas en una tabla de Bigquery (BQ) basada en el país. BQ se basa en el almacenamiento columnar. En lugar de almacenar todo el registro en un solo volumen, BQ divide la fila basándose en valores de columna y almacena cada valor en su propio volumen de almacenamiento. Para aprovechar este esquema hemos diseñado una solución de consulta de bajo costo eficiente para recuperar valores para cada país como se describe a continuación.

Problema: para separar una tabla BQ por país
La manera directa

Para separar una tabla BQ por país necesitamos consultar la tabla, tantas veces como el número de países.

Supongamos que necesitamos consultar las columnas a, B, C, D y e. El tamaño de cada columna es de 20 GB. El número facturable de bytes será 100GB para nuestra tabla.

Si necesitamos ejecutar nuestra consulta tantas veces como el número de países, es decir, 240, nuestro número de bytes facturable será de 24.000 GB o casi 24 TB. Digamos que los cargos por 1 TB son $5, nuestro costo total será $120 para una tabla BQ.

La manera optimizada

El truco aquí es agrupar el número requerido de campos como una sola columna. Esto por sí solo no guardará el número de bytes facturados. El segundo truco es pivotar por la columna de segregación; en este caso será por país.

Después de los pasos anteriores, nuestra tabla de resultados contendrá tantas columnas como países. Cada columna representa los datos de un país y tendrá los datos previamente agrupados sólo para ese país.

En nuestra tabla BQ de 100GB, podemos asumir un resultado probable, ya que la columna que nos representa es de 20 GB, los 10 principales países que promedian alrededor de 3 GB y el resto de los 230 países que promedian hacia 0,5 GB.

Ahora, nuestros Bytes Facturables se basan en el tamaño de los datos de cada país. El costo facturable para la consulta de nosotros será sólo $0,1, el costo para los principales países será de $0,015 cada uno y el resto de los países mucho más pequeños que eso.

Los bytes facturables totales serán 100GB para la tabla agrupada y 165GB para las consultas de todos los países combines. El costo total es de alrededor de $1,5!

Generar la tabla agrupada

Podemos agrupar las columnas requeridas en una sola columna utilizando concat. Podemos crear una fila en un formato que represente una línea en un archivo CSV o un objeto JSON válido.

Para el segundo paso, la consulta consistirá en 240 sentencias Case, con una sentencia case para cada país que pivotee a una columna determinada. La consulta será grande, pero BQ soporta tamaño de consulta de hasta 256KB. Podemos combinar ambos pasos en una única consulta en la que nos concat en cada caso de sentencia.

Consulta de ejemplo:

SELECCIONE
(Caso cuando geo. ISO = ' nosotros ' entonces concat (A, B, C, D, E)) como paísNOS
(Caso cuando geo. ISO = ' CA ' entonces concat (A, B, C, D, E)) como paísCA...
DE
mergelog_20160101;