¿Cuándo tiene sentido usar Google BigQuery?

A lo largo de mi carrera, tuve que escribir consultas analíticas complejas para diferentes tipos de informes y gráficos. La mayoría de las veces, era un gráfico que mostraba datos agregados por fecha, semana, trimestre, etc. Por lo general, dichos informes se crean para ayudar a los clientes a identificar tendencias e ilustrar cómo su negocio se está desempeñando a un alto nivel. Pero, ¿qué sucede cuando los científicos e ingenieros de datos necesitan crear un informe mucho más extenso, basado en un gran conjunto de datos?

En caso de que el informe se base en un pequeño conjunto de datos, la tarea se puede resolver escribiendo una consulta SQL en una base de datos relacional. En este paso, es importante conocer los conceptos básicos para escribir consultas y cómo hacerlas más rápidas y eficientes. Sin embargo, a veces el informe depende de un conjunto más grande de datos (por ejemplo, millones y más filas en una tabla), pero el informe no depende de las variables de entrada (parámetros), o puede encontrar que la cantidad de valores es bastante pequeña. En tales escenarios, una consulta SQL puede ser lenta, por lo que no sería óptimo que los usuarios esperaran hasta que se ejecute la consulta. La práctica más común en tales casos es ejecutar una consulta con anticipación, antes de que el cliente solicite un informe.

Además, requiere implementar alguna funcionalidad de almacenamiento en caché, de modo que el cliente pueda tomar datos del caché en lugar de ejecutar una consulta en tiempo real. Este enfoque funciona perfectamente, siempre que no necesite mostrar datos en tiempo real. Puede mostrar datos calculados una hora o incluso un día antes. Por lo tanto, el informe/gráfico real se muestra utilizando datos almacenados en caché, no basados ​​en datos en tiempo real.

Volviendo a Google BigQuery

Mientras trabajaba en un proyecto analítico en la industria farmacéutica, necesitaba gráficos que tomaran el código postal y el nombre del fármaco como parámetros de entrada. También necesitaba mostrar algunas comparaciones entre medicamentos en regiones específicas de los Estados Unidos.

La consulta analítica fue muy compleja y terminó ejecutándose alrededor de 50 minutos en nuestro servidor Postgres (CPU de cuatro núcleos con 16 GB de RAM). No pude ejecutarlo antes de tiempo y almacenar en caché los resultados, ya que la consulta tomaba códigos postales y medicamentos como parámetros de entrada, por lo que había miles de combinaciones y era imposible predecir qué cliente elegiría.

Incluso si quisiera intentar ejecutar todas las combinaciones de parámetros de entrada, lo más probable es que mi base de datos se hubiera bloqueado. Así que era hora de elegir un enfoque diferente y elegir una solución fácil de usar. Ese gráfico era importante para el cliente, sin embargo, el cliente no estaba listo para comprometerse a realizar grandes cambios en la arquitectura o migrar a otra base de datos por completo.

En ese proyecto en particular probamos algunos enfoques diferentes:

• Escalado vertical del servidor (agregando RAM y CPU al servidor Postgres)
• Usando bases de datos alternativas como Amazon Redshift y otras.
• También investigamos una solución NoSQL, pero la mayoría de ellas son bastante complejas y requieren muchos cambios en la arquitectura, muchos de los cuales habrían sido demasiado grandes para el cliente.

Finalmente, probamos Google BigQuery. Cumplió con nuestras expectativas y nos permitió hacer el trabajo sin hacer grandes cambios que el cliente se resistiría a aprobar. Pero, ¿qué es Google BigQuery y cómo funciona?

BigQuery es un servicio web basado en REST que le permite ejecutar consultas analíticas complejas basadas en SQL en grandes conjuntos de datos. Después de que subimos los datos a BigQuery y ejecutamos la misma consulta que hicimos con Postgres (la sintaxis es inquietantemente similar), nuestra consulta se ejecutó mucho más rápido y tardó aproximadamente un minuto en completarse. En última instancia, obtuvimos un aumento de rendimiento de 50 veces simplemente usando un servicio diferente. Vale la pena señalar que otras bases de datos no ofrecían la misma ganancia de rendimiento, y seamos generosos y simplemente digamos que ni siquiera estaban cerca. Para ser honesto, me impresionó mucho el aumento de rendimiento proporcionado por BigQuery, ya que las cifras fueron mejores de lo que cualquiera de nosotros había esperado.

A pesar de esto, no promocionaría BigQuery como la mejor solución de base de datos del mundo. Si bien funcionó bien para nuestro proyecto, todavía tiene muchas limitaciones, como una cantidad limitada de actualizaciones en la tabla por día, limitaciones en el tamaño de los datos por solicitud y otras. Debe comprender que BigQuery no se puede usar para sustituir una base de datos relacional y está orientado a ejecutar consultas analíticas, no para operaciones y consultas CRUD simples.

En este artículo, intentaré comparar el uso de Postgres (mi base de datos relacional favorita) y BigQuery para escenarios de casos de uso del mundo real. Además, proporcionaría algunas sugerencias en el camino, a saber, mi opinión sobre cuándo realmente tiene sentido usar BigQuery.

Data de muestra

Para comparar Postgres y Google BigQuery, tomé información demográfica pública de cada país agrupada por país, edad, año y sexo (puedes descargar los mismos datos desde este enlace).

Agregué los datos a cuatro tablas:

1. populations
2. locations
3. age_groups
4. populations_aggregated

La última tabla son solo datos agregados de las tres tablas anteriores. Aquí está el esquema DB:

La tabla de populations con la que terminé contiene más de 6,9 ​​millones de filas. No es demasiado, pero fue suficiente para mi prueba.

Con base en datos de muestra, traté de crear consultas que se pueden usar para crear informes y gráficos analíticos de la vida real. Así que preparé consultas para los próximos informes:

1. Población en los EE.UU. agregada por años.
2. Población en 2019 para todos los países a partir de los países más grandes.
3. Las cinco naciones "más antiguas" por cada año. “Más antiguo” denota países donde el porcentaje de personas mayores de 60 años con respecto al número total de personas es el más alto. La consulta debe dar cinco resultados por cada año.
4. Las cinco naciones principales agregadas por año, donde la diferencia entre la población masculina y femenina es la más grande.
5. Obtenga la edad media (promedio) por país para cada año, comenzando desde los países "más antiguos" hasta los "más jóvenes".
6. Encuentre los cinco principales países "moribundos" por cada año. “Muriendo” significa países en los que la población está disminuyendo (la despoblación es la más alta).

Las consultas n.° 1, n.° 2 y n.° 6 son bastante fáciles y directas, pero las consultas n.° 3, n.° 4 y n.° 5 no fueron tan fáciles de escribir, al menos para mí. Tenga en cuenta que soy un ingeniero de back-end y escribir consultas SQL complejas no es exactamente una especialidad mía, por lo que alguien con más experiencia en SQL probablemente podría crear consultas más inteligentes. Sin embargo, en este momento debemos verificar cómo Postgres y BigQuery procesan las mismas consultas con los mismos datos.

Construí 24 consultas en total:

• 6 para Postgres DB, que utilizan tablas no agregadas ( populations , locations , grupos de age_groups )
• 6 para Postgres DB, que están utilizando la populations_aggregated
• 6+6 consultas para BigQuery que utilizan tablas agregadas y no agregadas.

Permítanme compartir las consultas de BigQuery n.° 1 y n.° 5 para datos agregados para que pueda comprender la complejidad de las consultas simples (n.° 1) y complejas n.° 5.

Consulta de población en EE. UU. agregada por años:

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

Consulta de la mediana de edad por país por cada año ordenada de mayor a menor:

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

Nota: Puede encontrar todas las consultas en mi repositorio de bitbucket (el enlace se encuentra al final del artículo).

Resultados de la prueba

Para ejecutar consultas, utilicé dos servidores Postgres diferentes. El primero tiene 1 núcleo de CPU y 4 GB de RAM, respaldado por una unidad SSD. El segundo tiene 16 núcleos de CPU, 64 GB de RAM y también usaba una unidad SSD (el segundo servidor tiene 16 veces el potencial de CPU y RAM).

Además, tenga en cuenta que no hubo carga en las bases de datos durante la ejecución de la prueba. Los creé solo para ejecutar consultas. En situaciones de la vida real, las consultas tardarán más, ya que es posible que se ejecuten otras consultas al mismo tiempo y, además, esas consultas que se ejecutan en paralelo pueden bloquear tablas. Para verificar la velocidad de las consultas, estaba usando pgAdmin3 y la interfaz web de BigQuery.

En mi prueba, obtuve estos resultados:

Permítanme mostrar esos resultados en un gráfico de barras para la consulta n.º 1 y la consulta n.º 5.

Nota: La base de datos de Postgres estaba ubicada en un servidor con sede en EE. UU. y yo resido en Europa, por lo que hubo un retraso adicional en la transmisión de datos de Postgres.

Rendimiento y conclusiones de BigQuery

En base a los resultados que obtuve, llegué a las siguientes conclusiones:

• En el caso de escalar Postgres verticalmente, incluso 16 veces, solo ofrece un 10-25% de rendimiento al ejecutar una sola consulta. En otras palabras, un servidor de Postgres con un solo núcleo de CPU y 4 GB de RAM estaba ejecutando consultas con un tiempo muy similar al que necesitaba un servidor con 16 núcleos de CPU y 64 GB de RAM. Por supuesto, los servidores más grandes pueden procesar conjuntos de datos mucho más grandes, sin embargo, esto no ofrece una gran mejora en el tiempo de ejecución de las consultas.
• Para las uniones de Postgres con tablas pequeñas (la tabla de locations tiene alrededor de 400 filas y age_groups tiene 100 filas) no generan una gran diferencia en comparación con la ejecución de consultas con datos agregados ubicados en una tabla. Además, descubrí que para las consultas que se ejecutan de uno a dos segundos, las consultas con uniones internas son más rápidas, pero para las consultas de ejecución prolongada, la situación es diferente.
• En BigQuery, la situación con combinaciones es totalmente diferente. A BigQuery no le gustan las uniones. La diferencia de tiempo entre las consultas, que usan datos agregados y no agregados, es bastante grande (para las consultas #3 y $5 fue de alrededor de dos segundos). Significa que, para BigQuery, puede hacer tantas subconsultas como desee, pero para un buen rendimiento, la consulta debe usar una tabla.
• Postgres es más rápido para consultas que usan agregación o filtrado simple o que usan un conjunto de datos pequeño. Descubrí que las consultas que demoran menos de cinco segundos en Postgres funcionan más lentamente en BigQuery.
• BigQuery está funcionando mucho mejor para consultas de ejecución prolongada. A medida que aumenta la diferencia en el tamaño del conjunto de datos, también lo hará la diferencia en el tiempo que tardan en completarse estas consultas.

Cuándo tiene sentido usar BigQuery

Ahora, volvamos al tema central discutido en este artículo: ¿cuándo debería usar Google BigQuery? Según mis conclusiones, sugeriría usar BigQuery cuando se cumplan las siguientes condiciones:

• Úselo cuando tenga consultas que se ejecuten durante más de cinco segundos en una base de datos relacional. La idea de BigQuery es ejecutar consultas analíticas complejas, lo que significa que no tiene sentido ejecutar consultas que solo agregan o filtran. BigQuery es adecuado para consultas "pesadas", aquellas que operan con un gran conjunto de datos. Cuanto más grande sea el conjunto de datos, más probabilidades tendrá de obtener rendimiento mediante el uso de BigQuery. El conjunto de datos que utilicé fue de solo 330 MB (megabytes, ni siquiera gigabytes).
• A BigQuery no le gustan las uniones, por lo que debe fusionar sus datos en una tabla para obtener un mejor tiempo de ejecución. BigQuery permite guardar los resultados de la consulta en una nueva tabla, por lo que para crear una nueva tabla agregada, solo cargue todos sus datos en BigQuery, ejecute una consulta que consolidará todos los datos y guárdelo en una nueva tabla.
• BigQuery es bueno para escenarios en los que los datos no cambian con frecuencia y desea usar el caché, ya que tiene un caché integrado. ¿Qué significa esto? Si ejecuta la misma consulta y los datos en las tablas no se modifican (actualizan), BigQuery solo usará los resultados almacenados en caché y no intentará ejecutar la consulta nuevamente. Además, BigQuery no cobra dinero por las consultas almacenadas en caché. Nota: Incluso las consultas almacenadas en caché tardan entre 1 y 1,2 segundos en arrojar resultados.
• También puede usar BigQuery cuando desee reducir la carga en su base de datos relacional. Las consultas analíticas son "pesadas" y su uso excesivo en una base de datos relacional puede generar problemas de rendimiento. Por lo tanto, eventualmente podría verse obligado a pensar en escalar su servidor. Sin embargo, con BigQuery puede mover estas consultas en ejecución a un servicio de terceros para que no afecten su base de datos relacional principal.

Finalmente, algunas palabras más sobre el uso de BigQuery en la vida real. En nuestro proyecto del mundo real, los datos de los informes cambiaban semanal o mensualmente, por lo que podíamos cargar datos en BigQuery manualmente. Sin embargo, si sus datos cambian con frecuencia, la sincronización de datos entre su base de datos relacional y BigQuery puede no ser tan simple, y esta es una advertencia que vale la pena tener en cuenta.

Enlaces

Puede encontrar los datos de muestra utilizados en este artículo aquí, mientras que las consultas y los datos en formato CSV están disponibles aquí.