Descripción general

A menudo, el ajuste del rendimiento de BigQuery se lleva a cabo porque queremos reducir los costos o tiempos de ejecución de las consultas. En este lab, analizaremos una cantidad de optimizaciones de rendimiento que podrían funcionar para tu caso de uso. El ajuste del rendimiento debería llevarse a cabo solo al final de la etapa de desarrollo y solo si se observa que las consultas típicas demoran demasiado.

Es mucho mejor tener esquemas de tablas flexibles y consultas elegantes y fáciles de leer y de mantener que ofuscar los diseños de tablas y las consultas en busca de un poco de rendimiento. Sin embargo, habrá instancias en las que necesites mejorar el rendimiento de tus consultas, quizás porque se realizan tan a menudo que las mejoras pequeñas son significativas. Otro aspecto es que el conocimiento de las compensaciones de rendimiento puede ayudarte a decidir entre diseños alternativos.

Objetivos

En este lab, aprenderás las siguientes técnicas para reducir los costos y tiempos de ejecución de BigQuery:

• Minimizar E/S
• Almacenar en caché resultados de consultas anteriores
• Llevar a cabo uniones eficientes
• Evitar abrumar a trabajadores individuales
• Usar funciones de agregación aproximadas

Configuración y requisitos

En cada lab, recibirá un proyecto de Google Cloud y un conjunto de recursos nuevos por tiempo limitado y sin costo adicional.

1. Accede a Qwiklabs desde una ventana de incógnito.

2. Ten en cuenta el tiempo de acceso del lab (por ejemplo, 1:15:00) y asegúrate de finalizarlo en el plazo asignado.
   No existe una función de pausa. Si lo necesita, puede reiniciar el lab, pero deberá hacerlo desde el comienzo.

3. Cuando esté listo, haga clic en Comenzar lab.

4. Anote las credenciales del lab (el nombre de usuario y la contraseña). Las usarás para acceder a la consola de Google Cloud.

5. Haga clic en Abrir Google Console.

6. Haga clic en Usar otra cuenta, copie las credenciales para este lab y péguelas en el mensaje emergente que aparece.
   Si usa otras credenciales, se generarán errores o incurrirá en cargos.

7. Acepta las condiciones y omite la página de recursos de recuperación.

Abra BigQuery en Console

1. En Google Cloud Console, seleccione el menú de navegación > BigQuery.

Se abrirá el cuadro de mensaje Te damos la bienvenida a BigQuery en Cloud Console, que contiene un vínculo a la guía de inicio rápido y enumera las actualizaciones de la IU.

2. Haga clic en Listo.

Tarea 1: Minimiza la E/S

Una consulta que procesa la suma de tres columnas será más lenta que una que procesa la suma de dos, pero la mayor parte de la diferencia de rendimiento se deberá a la lectura de más datos, no a la suma adicional. Por lo tanto, una consulta que procesa la media de una columna será casi tan rápida como una consulta cuyo método de agregación es procesar la varianza de datos (incluso aunque la varianza del procesamiento requiera que BigQuery realice un seguimiento tanto de la suma como de la suma de los cuadrados) porque la mayoría de la sobrecarga de las consultas simples la causa E/S y no el procesamiento.

Sé relevante en SELECT

Dado que BigQuery usa formatos de archivo de columnas, mientras menos columnas haya que leer en SELECT, menos datos habrá para leer. En particular, al hacer SELECT *, se leen todas las columnas en cada fila de la tabla, lo que hace que sea algo lento y costoso.

La excepción es cuando usas SELECT * en una subconsulta. Entonces, solo se hace referencia a algunos campos en una consulta externa. El optimizador de BigQuery sabrá que solo debe leer las columnas que son absolutamente obligatorias.

1. Ejecuta la siguiente consulta en la Ventana del EDITOR de BigQuery:

SELECT bike_id, duration FROM `bigquery-public-data`.london_bicycles.cycle_hire ORDER BY duration DESC LIMIT 1

En la ventana Resultados de la consulta, observa que la consulta se completó en aproximadamente 1.2 s y que procesó alrededor de 372 MB de datos.

2. Ejecuta la siguiente consulta en la ventana del EDITOR de BigQuery:

SELECT * FROM `bigquery-public-data`.london_bicycles.cycle_hire ORDER BY duration DESC LIMIT 1

En la ventana Resultados de la consulta, observa que esta consulta se completó en aproximadamente 4.5 s y que consumió alrededor de 2.6 GB de datos. Tardó mucho más.

Si necesitas leer casi todas las columnas de una tabla, considera usar SELECT * EXCEPT para que no se lean aquellas que no son necesarias.

Nota: BigQuery almacenará en caché los resultados de las consultas para acelerar las consultas repetidas. Desactiva esta caché para ver el rendimiento real del procesamiento de la consulta. Para ello, haz clic en Más > Configuración de consulta y desmarca Usar resultados almacenados en caché.

Reduce la cantidad de datos que se leen

Cuando ajustas una consulta, es importante comenzar por los datos que se están leyendo y considerar si es posible reducirlos. Supongamos que queremos encontrar la duración típica de los alquileres más comunes que son de ida solo.

1. Ejecuta la siguiente consulta en la ventana del editor de BigQuery:

SELECT MIN(start_station_name) AS start_station_name, MIN(end_station_name) AS end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET (5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire WHERE start_station_id != end_station_id GROUP BY start_station_id, end_station_id ORDER BY num_trips DESC LIMIT 10

2. Haz clic en la pestaña Detalles de la ejecución en la ventana Resultados de la consulta.

Los detalles de la consulta indican que el ordenamiento (para los cuantiles aproximados de cada par de estación) requería una repartición de resultados de la etapa de entrada, pero que la mayor parte del tiempo se consume durante el procesamiento.

3. Podemos reducir la sobrecarga de E/S de la consulta si hacemos un filtrado y un reagrupamiento por medio del nombre de la estación, en lugar del ID de la estación, ya que tendremos que leer menos columnas. Ejecuta la siguiente consulta:

SELECT start_station_name, end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET(5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire WHERE start_station_name != end_station_name GROUP BY start_station_name, end_station_name ORDER BY num_trips DESC LIMIT 10

La consulta anterior evita la necesidad de leer las dos columnas de IDs y termina en alrededor de 10.8 segundos. Los efectos posteriores de leer menos datos son los que causan esta aceleración.

El resultado de la consulta sigue siendo el mismo, ya que hay una relación 1:1 entre el nombre de la estación y el ID de la estación.

Reduce el número de procesamientos costosos

Supongamos que queremos encontrar la distancia total que recorrió cada bicicleta en nuestro conjunto de datos.

1. Una forma básica de hacerlo sería encontrar la distancia recorrida en cada viaje realizado por cada bicicleta y sumarlas todas:

WITH trip_distance AS ( SELECT bike_id, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data`.london_bicycles.cycle_hire, `bigquery-public-data`.london_bicycles.cycle_stations s, `bigquery-public-data`.london_bicycles.cycle_stations e WHERE start_station_id = s.id AND end_station_id = e.id ) SELECT bike_id, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bike_id ORDER BY total_distance DESC LIMIT 5

La consulta anterior tarda alrededor de 9.8 segundos (aproximadamente 55 segundos de tiempo de ranura) y redistribuye alrededor de 1.22 MB. El resultado es que algunas bicicletas se utilizaron durante casi 6,000 kilómetros.

2. Procesar la distancia es una operación bastante costosa y podemos evitar unir la tabla cycle_stations con la tabla cycle_hire si procesamos con anterioridad las distancias entre todos los pares de estaciones:

WITH stations AS ( SELECT s.id AS start_id, e.id AS end_id, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data`.london_bicycles.cycle_stations s, `bigquery-public-data`.london_bicycles.cycle_stations e ), trip_distance AS ( SELECT bike_id, distance FROM `bigquery-public-data`.london_bicycles.cycle_hire, stations WHERE start_station_id = start_id AND end_station_id = end_id ) SELECT bike_id, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bike_id ORDER BY total_distance DESC LIMIT 5

Esta consulta solo hace 600,000 cálculos de distancia geográfica frente a los 24 millones anteriores. Ahora tarda alrededor de 31.5 segundos de tiempo de ranura (un 30% más rápido), a pesar de consumir aproximadamente 33.05 MB de datos.

Haz clic en Revisar mi progreso para verificar el objetivo.

Minimizar la E/S

Tarea 2. Cómo almacenar en caché los resultados de consultas anteriores

El servicio de BigQuery almacena en caché automáticamente los resultados de las consultas en una tabla temporal. Si se envía una consulta idéntica en el plazo de las siguientes 24 horas aproximadamente, los resultados se toman desde esta tabla temporal sin ningún tipo de reprocesamiento. Los resultados almacenados en caché son extremadamente rápidos y no generan cargos.

Sin embargo, hay algunas salvedades que se deben tener en cuenta. El almacenamiento de consultas en caché se basa en una comparación exacta de strings. Por lo tanto, incluso los espacios en blanco pueden provocar un error de caché. Las consultas nunca se almacenan en caché si tienen un comportamiento no determinista (por ejemplo, usan CURRENT_TIMESTAMP o RAND), si la tabla o la vista que se está consultando cambió (incluso si las columnas/filas de interés para la consulta no se modificaron), si la tabla está asociada a un búfer de transmisión (incluso si no hay filas nuevas) o si la consulta usa declaraciones DML o se dirige a fuentes de datos externas.

Almacena en caché los resultados intermedios

Es posible mejorar el rendimiento general a costa de un aumento de E/S. Para ello, se deben aprovechar las tablas temporales y las vistas materializadas.

Por ejemplo, supongamos que tienes un número de consultas que comienzan con la búsqueda de la duración típica de un viaje entre un par de estaciones. La cláusula WITH (también llamada expresión de tabla común) mejora la lectura, pero no la velocidad ni el costo de la consulta, dado que los resultados no están almacenados en caché. Esto también ocurre con las vistas y las subconsultas. Si utilizas a menudo una cláusula, vista o subconsulta WITH, una forma de mejorar potencialmente el rendimiento es almacenar el resultado en una tabla (o en una vista materializada).

1. Primero, deberás crear un conjunto de datos llamado mydataset en la región UE (varias regiones en la Unión Europea) (donde se encuentran los datos de bicicletas) en tu proyecto de BigQuery.

• En la sección Explorador que se encuentra en el panel izquierdo, haz clic en el ícono de tres puntos Ver acción cerca de tu proyecto de BigQuery (qwiklabs-gcp-xxxx) y selecciona Crear conjunto de datos.

En el diálogo Crear conjunto de datos, realiza lo siguiente:

• Establece el ID del conjunto de datos en mydataset.

• Establece el Tipo de ubicación en eu (varias regiones en la Unión Europea).

• Deja todas las demás opciones en sus valores predeterminados.

• Para finalizar, haz clic en el botón azul Crear conjunto de datos.

• Ahora, puedes ejecutar la siguiente consulta:

  CREATE OR REPLACE TABLE mydataset.typical_trip AS SELECT start_station_name, end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET (5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire GROUP BY start_station_name, end_station_name

2. Usa la tabla creada para encontrar los días en que los viajes en bicicleta son mucho más largos de lo habitual:

SELECT EXTRACT (DATE FROM start_date) AS trip_date, APPROX_QUANTILES(duration / typical_duration, 10)[OFFSET(5)] AS ratio, COUNT(*) AS num_trips_on_day FROM `bigquery-public-data`.london_bicycles.cycle_hire AS hire JOIN mydataset.typical_trip AS trip ON hire.start_station_name = trip.start_station_name AND hire.end_station_name = trip.end_station_name AND num_trips > 10 GROUP BY trip_date HAVING num_trips_on_day > 10 ORDER BY ratio DESC LIMIT 10

3. Usa la cláusula WITH para encontrar los días en que los viajes en bicicleta son mucho más largos de lo habitual:

WITH typical_trip AS ( SELECT start_station_name, end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET (5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire GROUP BY start_station_name, end_station_name ) SELECT EXTRACT (DATE FROM start_date) AS trip_date, APPROX_QUANTILES(duration / typical_duration, 10)[ OFFSET (5)] AS ratio, COUNT(*) AS num_trips_on_day FROM `bigquery-public-data`.london_bicycles.cycle_hire AS hire JOIN typical_trip AS trip ON hire.start_station_name = trip.start_station_name AND hire.end_station_name = trip.end_station_name AND num_trips > 10 GROUP BY trip_date HAVING num_trips_on_day > 10 ORDER BY ratio DESC LIMIT 10

Nota que es aproximadamente un 50% más veloz, dado que se evita procesar la duración del viaje promedio. Ambas consultas proporcionan el mismo resultado: los viajes en Navidad toman más tiempo de lo habitual. Nota que la tabla mydataset.typical_trip no se actualiza cuando se agregan datos nuevos a la tabla cycle_hire.

Una forma de resolver este problema de datos inactivos es usar una vista materializada o programar consultas para que actualicen la tabla de manera periódica. Deberías medir el costo de esas actualizaciones para ver si la mejora en el rendimiento de la consulta compensa el costo extra de mantener la tabla o la vista materializada actualizadas.

Acelera las consultas con BI Engine

Si hay tablas a las que accedes con frecuencia en las configuraciones de Business Intelligence (BI), como los paneles con agregaciones y filtros, una forma de acelerar tus consultas es utilizar BI Engine. Este almacenará automáticamente fragmentos de datos importantes en la memoria (ya sean columnas de la tabla o resultados derivados) y usará un procesador especializado de consultas ajustado para trabajar principalmente con datos en la memoria. Puedes reservar la cantidad de memoria (hasta un máximo de 10 GB) que BigQuery debería usar para su caché desde la Consola del administrador de BigQuery, debajo de BI Engine.

Asegúrate de reservar esta memoria en la misma región en que se encuentra el conjunto de datos que estás consultando. Luego, BigQuery comenzará a almacenar en caché las tablas, partes de tablas y agregaciones en la memoria y proporcionará los resultados más rápido.

Un caso de uso principal para BI Engine es para las tablas a las que se accede desde herramientas de panel como Google Data Studio. Al asignar memoria para una reserva de BI Engine, podemos hacer que los paneles que se basen en un backend de BigQuery sean mucho más responsivos.

Haz clic en Revisar mi progreso para verificar el objetivo.

Almacenar en caché resultados de consultas anteriores

Tarea 3: Uniones eficientes

Unir dos tablas requiere coordinación de datos y está sujeto a las limitaciones que impone el ancho de banda de comunicación entre ranuras. Si es posible evitar una unión o reducir la cantidad de datos que se unen, hazlo.

Desnormalización

Una forma de mejorar el rendimiento de lectura y evitar las uniones es renunciar a almacenar datos de manera eficiente y, en lugar de eso, agregar copias redundantes de datos. A esto se lo denomina desnormalización.

• Así, en lugar de almacenar la latitud y longitud de la estación de bicicletas de manera separada de la información de alquiler de bicicletas, podríamos crear una tabla desnormalizada:

  CREATE OR REPLACE TABLE mydataset.london_bicycles_denorm AS SELECT start_station_id, s.latitude AS start_latitude, s.longitude AS start_longitude, end_station_id, e.latitude AS end_latitude, e.longitude AS end_longitude FROM `bigquery-public-data`.london_bicycles.cycle_hire AS h JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS s ON h.start_station_id = s.id JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS e ON h.end_station_id = e.id

  Entonces, todas las consultas posteriores no necesitarán realizar una unión debido a que la tabla contendrá toda la información de ubicación necesaria para todos los viajes.

  En este caso, estás sacrificando almacenamiento y lectura de más datos para evitar el costo de procesamiento de una unión. Es posible que el costo de leer más datos de un disco supere el de la unión. Deberías medir si la desnormalización proporciona beneficios en cuanto al rendimiento.

  Haz clic en Revisar mi progreso para verificar el objetivo.

  Desnormalización

Evita las uniones de tablas grandes con ellas mismas

Este tipo de uniones ocurren cuando una tabla se une consigo misma. Aunque BigQuery admite este tipo de uniones, pueden provocar una degradación del rendimiento si la tabla que se une consigo misma es muy grande. En muchos casos, puedes evitar la unión de tablas con ellas mismas si aprovechas las funciones de SQL como la agregación y las funciones analíticas.

Veamos un ejemplo. Uno de los conjuntos de datos públicos de BigQuery es el conjunto de datos de nombres de bebés publicado por la Administración de Seguridad Social de EE.UU.

1. Es posible realizar una consulta en ese conjunto de datos para encontrar los nombres de hombre más comunes en 2015 en el estado de Massachusetts (asegúrate de que tu consulta se ejecute en la región US [múltiples regiones en Estados Unidos]). Haz clic en + Crear una consulta en SQL, luego, selecciona Más > Configuración de consulta > Opciones avanzadas, desmarca Selección automática de ubicación y selecciona Multirregional y US (múltiples regiones en Estados Unidos) o deja la consulta para usar la selección automática de ubicación) y haz clic en Guardar:

SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'M' AND year = 2015 AND state = 'MA' ORDER BY num_babies DESC LIMIT 5

2. De manera similar, haz una consulta en ese conjunto de datos para encontrar los nombres de mujeres más comunes en 2015 en el estado de Massachusetts:

SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'F' AND year = 2015 AND state = 'MA' ORDER BY num_babies DESC LIMIT 5

3. ¿Cuáles son los nombres más comunes asignados tanto a niños como a niñas en el país a lo largo de todos los años en el conjunto de datos? Una forma básica de resolver el problema sería leer toda la tabla de entrada dos veces y hacer una unión con ella misma:

WITH male_babies AS ( SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'M' ), female_babies AS ( SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'F' ), both_genders AS ( SELECT name, SUM(m.num_babies) + SUM(f.num_babies) AS num_babies, SUM(m.num_babies) / (SUM(m.num_babies) + SUM(f.num_babies)) AS frac_male FROM male_babies AS m JOIN female_babies AS f USING (name) GROUP BY name ) SELECT * FROM both_genders WHERE frac_male BETWEEN 0.3 AND 0.7 ORDER BY num_babies DESC LIMIT 5

4. Una solución más rápida y elegante (y correcta) es retransmitir la consulta para que lea la entrada solo una vez y evitar por completo la unión de la tabla con sí misma:

WITH all_babies AS ( SELECT name, SUM( IF (gender = 'M', number, 0)) AS male_babies, SUM( IF (gender = 'F', number, 0)) AS female_babies FROM `bigquery-public-data.usa_names.usa_1910_current` GROUP BY name ), both_genders AS ( SELECT name, (male_babies + female_babies) AS num_babies, SAFE_DIVIDE(male_babies, male_babies + female_babies) AS frac_male FROM all_babies WHERE male_babies > 0 AND female_babies > 0 ) SELECT * FROM both_genders WHERE frac_male BETWEEN 0.3 AND 0.7 ORDER BY num_babies DESC LIMIT 5

Esto tomó alrededor de 2.4 segundos, una mejora en velocidad aproximadamente 30 veces superior.

Reduce los datos que se unen

Es posible realizar la consulta anterior con una unión eficiente siempre y cuando reduzcamos la cantidad de datos que se unen. Para ello, debemos agruparlos por nombre y género lo antes posible:

• Prueba la siguiente consulta:

  WITH all_names AS ( SELECT name, gender, SUM(number) AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current GROUP BY name, gender ), male_names AS ( SELECT name, num_babies FROM all_names WHERE gender = 'M' ), female_names AS ( SELECT name, num_babies FROM all_names WHERE gender = 'F' ), ratio AS ( SELECT name, (f.num_babies + m.num_babies) AS num_babies, m.num_babies / (f.num_babies + m.num_babies) AS frac_male FROM male_names AS m JOIN female_names AS f USING (name) ) SELECT * FROM ratio WHERE frac_male BETWEEN 0.3 AND 0.7 ORDER BY num_babies DESC LIMIT 5

  El agrupamiento previo sirvió para recortar los datos al principio de la consulta, antes de que esta realice una UNIÓN. De esa forma, la redistribución de datos y otras operaciones complejas solo se ejecutan en una cantidad de datos mucho más pequeña y resulta bastante eficiente. La consulta anterior terminó en alrededor de 2 segundos y devolvió el resultado correcto.

Usa una función analítica en lugar de una unión de tabla con sí misma

Supongamos que quieres encontrar el tiempo que pasa entre que se devuelve una bicicleta y se la alquila de nuevo, es decir, el tiempo que la bicicleta permanece en la estación. Este es un ejemplo de una relación dependiente entre filas. Puede parecer que la única forma de resolver esto es unir la tabla con sí misma, y hacer coincidir la end_date de un viaje con la start_date del siguiente. Asegúrate de que la consulta se ejecute en la región eu (varias regiones en la Unión Europea). Haz clic en + Crear consulta en SQL y, luego, selecciona Más > Configuración de consulta > Opciones adicionales y verifica que la selección de ubicación automática esté marcada.

1. Sin embargo, puedes evitar una unión de tabla con sí misma usando una función analítica:

SELECT bike_id, start_date, end_date, TIMESTAMP_DIFF( start_date, LAG(end_date) OVER (PARTITION BY bike_id ORDER BY start_date), SECOND) AS time_at_station FROM `bigquery-public-data`.london_bicycles.cycle_hire LIMIT 5

2. Con esto, podemos procesar el tiempo promedio que una bicicleta no está en uso en cada estación y clasificar las estaciones según esa medida:

WITH unused AS ( SELECT bike_id, start_station_name, start_date, end_date, TIMESTAMP_DIFF(start_date, LAG(end_date) OVER (PARTITION BY bike_id ORDER BY start_date), SECOND) AS time_at_station FROM `bigquery-public-data`.london_bicycles.cycle_hire ) SELECT start_station_name, AVG(time_at_station) AS unused_seconds FROM unused GROUP BY start_station_name ORDER BY unused_seconds ASC LIMIT 5

Realiza uniones con valores procesados anteriormente

A veces, puede ser útil procesar anteriormente las funciones en tablas más pequeñas y, luego, unirlas con los valores procesados con anterioridad en lugar de repetir un cálculo costoso cada vez.

Por ejemplo, supongamos que queremos encontrar el par de estaciones entre las que nuestros clientes usan las bicicletas al ritmo más rápido. Para procesar el ritmo (minutos por kilómetro) al que van, debemos dividir la duración del viaje por la distancia entre las estaciones.

1. Podríamos crear una tabla desnormalizada con distancias entre estaciones y, luego, procesar el ritmo promedio:

WITH denormalized_table AS ( SELECT start_station_name, end_station_name, ST_DISTANCE(ST_GeogPoint(s1.longitude, s1.latitude), ST_GeogPoint(s2.longitude, s2.latitude)) AS distance, duration FROM `bigquery-public-data`.london_bicycles.cycle_hire AS h JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS s1 ON h.start_station_id = s1.id JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS s2 ON h.end_station_id = s2.id ), durations AS ( SELECT start_station_name, end_station_name, MIN(distance) AS distance, AVG(duration) AS duration, COUNT(*) AS num_rides FROM denormalized_table WHERE duration > 0 AND distance > 0 GROUP BY start_station_name, end_station_name HAVING num_rides > 100 ) SELECT start_station_name, end_station_name, distance, duration, duration/distance AS pace FROM durations ORDER BY pace ASC LIMIT 5

La consulta anterior invoca la función geoespacial ST_DISTANCE una vez para cada fila en la tabla cycle_hire (24 millones de veces), tarda alrededor de 14.7 segundos y procesa alrededor de 1.9 GB.

2. De manera alternativa, podemos usar la tabla cycle_stations para procesar con anterioridad la distancia entre cada par de estaciones (esto es una unión de tabla con sí misma) y, luego, unirla con la tabla de tamaño reducido que contiene el promedio de duración entre estaciones:

WITH distances AS ( SELECT a.id AS start_station_id, a.name AS start_station_name, b.id AS end_station_id, b.name AS end_station_name, ST_DISTANCE(ST_GeogPoint(a.longitude, a.latitude), ST_GeogPoint(b.longitude, b.latitude)) AS distance FROM `bigquery-public-data`.london_bicycles.cycle_stations a CROSS JOIN `bigquery-public-data`.london_bicycles.cycle_stations b WHERE a.id != b.id ), durations AS ( SELECT start_station_id, end_station_id, AVG(duration) AS duration, COUNT(*) AS num_rides FROM `bigquery-public-data`.london_bicycles.cycle_hire WHERE duration > 0 GROUP BY start_station_id, end_station_id HAVING num_rides > 100 ) SELECT start_station_name, end_station_name, distance, duration, duration/distance AS pace FROM distances JOIN durations USING (start_station_id, end_station_id) ORDER BY pace ASC LIMIT 5

La consulta reestructurada con las uniones más eficientes tarda solo alrededor de 8.2 segundos, es aproximadamente 1.8 veces más rápida y procesa alrededor de 554 MB, por lo que reduce el costo casi 4 veces.

Haz clic en Revisar mi progreso para verificar el objetivo.

Uniones

Tarea 4: Evita abrumar a un trabajador

Hay algunas operaciones (p. ej., pedidos) que debe llevar a cabo un solo trabajador. Tener que ordenar muchos datos puede abrumar la memoria de un trabajador y provocar un error por “recursos excedidos”. Evita abrumar al trabajador con demasiados datos. Ten en cuenta también que, con las actualizaciones de hardware en los centros de datos de Google, el significado de “demasiado” cambia con el tiempo. Actualmente, esto sería alrededor de 1 GB.

Cómo limitar ordenamientos grandes

1. Digamos que quieres analizar los alquileres y numerarlos 1, 2, 3, etc., según el orden en que el alquiler finalizó. Podríamos hacerlo usando la función ROW_NUMBER():

SELECT rental_id, ROW_NUMBER() OVER(ORDER BY end_date) AS rental_number FROM `bigquery-public-data.london_bicycles.cycle_hire` ORDER BY rental_number ASC LIMIT 5

Tarda 34.5 segundos en procesar solo 372 MB porque necesita ordenar todo el conjunto de datos de bicicletas de Londres en un solo trabajador. Si hubiéramos procesado un conjunto de datos más grande, habríamos abrumado a ese trabajador.

2. Quizá sea buena idea considerar si es posible limitar los ordenamientos grandes y distribuirlos. De hecho, es posible extraer las fechas de los alquileres y, luego, ordenar los viajes dentro de cada día:

WITH rentals_on_day AS ( SELECT rental_id, end_date, EXTRACT(DATE FROM end_date) AS rental_date FROM `bigquery-public-data.london_bicycles.cycle_hire` ) SELECT rental_id, rental_date, ROW_NUMBER() OVER(PARTITION BY rental_date ORDER BY end_date) AS rental_number_on_day FROM rentals_on_day ORDER BY rental_date ASC, rental_number_on_day ASC LIMIT 5

Esto tarda alrededor de 15.1 segundos (2 veces más rápido) porque el ordenamiento puede realizarse en un solo día de datos por vez.

Haz clic en Revisar mi progreso para verificar el objetivo.

Evitar que un trabajador termine sobrepasado

Sesgo de datos

El mismo problema de abrumar a un trabajador (en este caso, abrumar la memoria de un trabajador) puede ocurrir durante un ARRAY_AGG con GROUP BY si una de las claves es mucho más común que las otras.

1. Debido a que hay más de 3 millones de repositorios de GitHub y las confirmaciones están bien distribuidas entre ellos, esta consulta se realiza correctamente (asegúrate de ejecutarla en el centro de procesamiento de US (varias regiones en Estados Unidos)):

SELECT repo_name, ARRAY_AGG(STRUCT(author, committer, subject, message, trailer, difference, encoding) ORDER BY author.date.seconds) FROM `bigquery-public-data.github_repos.commits`, UNNEST(repo_name) AS repo_name GROUP BY repo_name

Observa que, si bien la consulta se realizará correctamente, puede tardar hasta 30 minutos en finalizar. Si entiendes la consulta, continúa con el lab.

2. La mayoría de las personas que usan GitHub viven en unas pocas zonas horarias, por lo que el agrupamiento por zona horaria falla. Le estamos pidiendo a un trabajador único que ordene una fracción significativa de 750 GB:

SELECT author.tz_offset, ARRAY_AGG(STRUCT(author, committer, subject, message, trailer, difference, encoding) ORDER BY author.date.seconds) FROM `bigquery-public-data.github_repos.commits` GROUP BY author.tz_offset

3. Si necesitas ordenar todos los datos, usa claves más detalladas (p. ej., distribuye los datos del grupo entre más trabajadores) y, luego, agrega los resultados que correspondan a la clave deseada. Por ejemplo, en lugar de solo agrupar por zona horaria, es posible agrupar tanto por timezone como por repo_name y, luego, agregarlos en todos los repos para conseguir la respuesta real para cada zona horaria:

SELECT repo_name, author.tz_offset, ARRAY_AGG(STRUCT(author, committer, subject, message, trailer, difference, encoding) ORDER BY author.date.seconds) FROM `bigquery-public-data.github_repos.commits`, UNNEST(repo_name) AS repo_name GROUP BY repo_name, author.tz_offset

Observa que, si bien la consulta se realizará correctamente, puede tardar más de 15 minutos en finalizar. Si entiendes la consulta, continúa con el lab.

Tarea 5. Funciones de agregación aproximadas

BigQuery proporciona aproximaciones rápidas y de memoria baja de funciones agregadas. En lugar de usar COUNT(DISTINCT …), podemos usar APPROX_COUNT_DISTINCT en transmisiones de datos grandes cuando puede tolerarse una pequeña incertidumbre estadística en el resultado.

Recuento aproximado

1. Podemos usar la siguiente consulta para encontrar la cantidad de repositorios de GitHub únicos:

SELECT COUNT(DISTINCT repo_name) AS num_repos FROM `bigquery-public-data`.github_repos.commits, UNNEST(repo_name) AS repo_name

La consulta anterior tarda 8.3 segundos en procesar el resultado correcto de 3347770.

2. Con la función aproximada:

SELECT APPROX_COUNT_DISTINCT(repo_name) AS num_repos FROM `bigquery-public-data`.github_repos.commits, UNNEST(repo_name) AS repo_name

La consulta anterior tarda alrededor de 3.9 segundos (2 veces más rápido) y devuelve un resultado aproximado de 3399473, que excede la respuesta correcta en alrededor de un 1.5%.

El algoritmo aproximado es mucho más eficiente que el exacto solo en conjuntos de datos grandes y se recomienda para los casos de uso en los que se toleran errores de aproximadamente un 1%. Antes de usar la función aproximada, haz una medición en tu caso de uso.

Otras funciones aproximadas disponibles son APPROX_QUANTILES para procesar percentiles, APPROX_TOP_COUNT para encontrar los elementos principales y APPROX_TOP_SUM para procesar los elementos principales en función de la suma de un elemento.

Haz clic en Revisar mi progreso para verificar el objetivo.

Funciones de agregación aproximadas

¡Felicitaciones!

Aprendiste varias técnicas para mejorar potencialmente el rendimiento de tus consultas. Cuando consideres algunas de estas técnicas, recuerda la cita del legendario científico informático Donald Knuth: “La optimización prematura es la raíz de todo mal”.

Próximos pasos y más información

• Documentación para optimizar el rendimiento de las consultas de Google Cloud Platform.
• Prácticas recomendadas para controlar costos de BigQuery.

Finalice su lab

Cuando haya completado el lab, haga clic en Finalizar lab. Google Cloud Skills Boost quitará los recursos que usó y limpiará la cuenta.

Tendrá la oportunidad de calificar su experiencia en el lab. Seleccione la cantidad de estrellas que corresponda, ingrese un comentario y haga clic en Enviar.

La cantidad de estrellas indica lo siguiente:

• 1 estrella = Muy insatisfecho
• 2 estrellas = Insatisfecho
• 3 estrellas = Neutral
• 4 estrellas = Satisfecho
• 5 estrellas = Muy satisfecho

Puede cerrar el cuadro de diálogo si no desea proporcionar comentarios.

Para enviar comentarios, sugerencias o correcciones, use la pestaña Asistencia.

Copyright 2020 Google LLC. All rights reserved. Google y el logotipo de Google son marcas de Google LLC. Los demás nombres de productos y empresas pueden ser marcas de las respectivas empresas a las que estén asociados.