5 consejos para sacarle el máximo partido a BigQuery.

BigQuery es un producto serverless, altamente escalable y económicamente rentable que nos permite tener un data warehouse con funcionalidades de ML y un motor BI integrado. Es uno de los productos estrella de Google Cloud Platform, que tiene cada vez más y más fans por todo el mundo.

En este artículo vamos a ver 5 consejos para aprovechar más este servicio que seguro nos resultan útiles:

Control de costes

BigQuery es un producto que permite ahorrar mucho dinero si lo comparamos con los costes de adquirir y mantener un data warehouse on-premise siempre que tengamos en cuenta ciertas consideraciones. Algo que no podemos olvidar es que estamos hablando de un producto serverless con un sistema de pago por uso (normalmente) donde no conocer cómo usarlo puede resultar caro.

Vamos a ver unos puntos para asegurar que nuestra experiencia con BigQuery sea satisfactoria y sin sustos en la factura.

• Usa BigQuery en modo sandbox para hacer pruebas sin riesgos ni costes, sobre todo cuando estás empezando.
• Almacenar datos en BigQuery es muy barato (0.023$ GB/mes) y a los 90 días sin modificar se abarata el coste automáticamente.
• Las queries se facturan por bytes procesados. Se puede hacer un dry run para ver el el número de bytes procesados por una query antes de hacerla y, así, saber el coste. Si usamos la web también podemos ver el tamaño de la query.
• Hay caché automática pero hay que saber cuándo funciona y cuándo no.
• Los campos implicados condicionan los bytes procesados ¡No uses select *! Y añade solo los campos que vas a necesitar.
• Los limits y los where no reducen tamaño de la query salvo particiones y clusters.
• Además del sistema de pago por uso, hay otro modelo de reserva de carga (flat rate) en la que el coste no depende de las queries ejecutadas, sino de la capacidad reservada, pudiendo ejecutar todas las queries que necesitemos siempre que no sobrepasemos la capacidad de slots (unidades de proceso) que tengamos reservados.

Control de permisos dinámico

En BigQuery existe una agrupación lógica de tablas llamadas dataset: todas las tablas en BigQuery tienen que pertenecer a un dataset y todos los datasets pertenecen a un proyecto.

El control de acceso a los datos se realiza a nivel de dataset, pudiendo definir qué usuarios o grupos tienen permisos de lectura o de edición a las tablas que componen el dataset.

El problema es que muchas veces necesitamos que determinadas personas accedan solo a un subconjunto de los datos de una determinada tabla. ¿Cómo arreglamos esto? La solución son las vistas autorizadas y nos permiten acceder a tablas de datasets desde queries de otros datasets.

De esta forma, podremos hacer que los usuarios accedan a nuestros datos pero solo ejecutando las queries que hemos aceptado con anterioridad.

Al final tendremos dos datasets: uno que contiene las tablas maestras y otro con las queries que acceden a esos datos. El dataset con las queries estará compartido en modo lectura con los usuarios que necesitan acceder a los datos de forma controlada.

Lo vemos más claro en el siguiente croquis:

Una vez que tenemos claro las vistas autorizadas pasamos al próximo problema: ¿qué hacer cuando cada usuario tiene que poder leer diferentes datos y este acceso puede variar dinámicamente? Un ejemplo: cada usuario solo puede ver los datos de los departamentos a los que pertenece.

Pues bien, una buena solución para esto es crear una tabla intermedia donde indiquemos los correos de cada persona con los departamentos a los que tiene acceso.

Luego, en nuestra vista autorizada, calcularemos los departamentos para mostrar solo los datos de los departamentos correctos para cada usuario (con la función SESSION_USER() obtenemos el usuario que realiza la query).

Como los permisos están normalizados es posible modificarlos en caso de que sea necesario realizando el cambio en solo una fila (recordamos que en las tablas de BigQuery es posible hacer modificaciones pero de forma limitada).

Una variante es tener la tabla de permisos ligada a una spreadsheet ( en el siguiente consejo, lo explicamos).

Diagrama final:

Enlazando sheets

Una funcionalidad de BigQuery consiste en crear tablas con los datos externos. Entre las opciones disponibles una especialmente práctica spreadsheet de drive. Esto es extremadamente útil si sabemos que no se refiere a que carguen los datos, sino que nuestra tabla se actualizará con cada cambio en la hoja de cálculo.

Esto nos ofrece muchas posibilidades ya que podemos usar esta tabla como configuración dando mucha más flexibilidad a nuestras soluciones.

Vamos a ver cómo de sencillo es hacer una tabla enlazada desde la UI de BigQuery:

Una vez creada la tabla podemos consultarla como el resto de tablas de BigQuery con la diferencia de saber que si modificamos la hoja de cálculo también modificaremos la tabla de BigQuery.

Un apunte importante: estas tablas no pueden ser particionadas, clusterizadas ni cacheadas.

Consolidando datos

Uno de los handicaps más importantes de BigQuery es la limitación a la hora de editar y borrar datos. Esto puede ser un problema cuando queremos analizar con BigQuery datos que vienen de una base de datos que se actualiza constantemente. Ir sincronizando cada cambio con BigQuery no es posible y hacer descargas enteras frecuentemente es ineficiente y caro.

Una buena solución para este problema consiste en añadir en cada registro una marca de tiempo e ir guardando las modificaciones; gracias a esta marca de tiempo podemos quedarnos para cada id con la última versión.

Como podemos ver, este enfoque es muy poderoso ya que no solo podemos actualizar los valores todas las veces que necesitemos sin tener las limitaciones de edición sino que podemos analizar los datos para ver cómo ha variado el mismo (recordamos que para cada valor tenemos información de su evolución).

Veamos esto con un ejemplo. Imaginemos que tenemos una tabla con el saldo en cuenta de nuestros clientes a día 1:

Ahora vemos los valores al día siguiente (día 2):

Como vemos, no solo se han modificado algunos de los valores, sino que hay un cliente que ha desaparecido. Veamos ahora cómo tendríamos guardados nuestros datos en BigQuery.

Ahora lo único que tenemos que hacer es consultar para cada id los valores más actualizados, sin haber tenido que editar y conservando los cambios en los valores.

Como seguramente ya habréis adivinado, este enfoque tiene un problema y es que el coste de hacer las queries irá creciendo sin parar al tener siempre que leer todos los datos para obtener la imagen actualizada… bueno, vamos a solucionar este problema haciendo tablas consolidadas.

Lo que vamos a hacer es ir creando tablas consolidadas de forma que nuestra query se lanzará sobre la última consolidada más las particiones con las modificaciones entre la fecha de consolidación de la tabla y al actual.

Como es un poco complicado vamos a verlo poco a poco:

1º. Nosotros continuamos guardando los datos en nuestra tabla maestra, añadiendo siempre un timestamp y particionando por fecha de inserción.

2º. Periódicamente, por ejemplo tres días, generamos una nueva versión consolidada de la tabla en la que para cada registro obtenemos el valor más actualizado. Esto lo conseguimos juntando la última consolidada y las particiones posteriores.

3º. A la hora de hacer las queries las haremos sobre la última consolidada disponible y las particiones de la maestra con las modificaciones no consolidadas.

La T del ETL

En algunas ocasiones es más fácil y económico hacer las transformaciones de nuestros datos directamente desde BigQuery sin requerir de otros productos como puede ser DataFlow (Apache Beam) o DataProc (Hadoop/Spark).

Vamos a ver cómo sería el proceso con un ejemplo que seguro que se entiende mejor. Imaginemos que tenemos unos archivos en “bruto” en formato csv que se han subido a Cloud Storage. En BigQuery no hay ningún problema en cargar estos datos o usarlos en la query directamente desde cloud storage.

Como podemos ver, antes de poder utilizar estos datos por nuestro sistema de BI necesitamos limpiarlos un poco realizando las siguientes acciones:

• Todos los Ids hay que ponerlos como números.
• La fecha de inicio tiene que ser formateada como fecha.
• Eliminado de espacios duplicados y poner en minúsculas la descripción.

El objetivo de estas transformaciones sería dejar los datos de la siguiente manera:

Ahora lo que tenemos que hacer es crear la query que limpie nuestros datos y nos lo deje como queremos. Al ser SQL standard es muy fácil encontrar la solución correcta, en caso de ser tablas muy grandes es recomendable reducir y el conjunto de datos y hacer pruebas antes de ejecutar la query contra el total.

SELECT 
CAST(REPLACE(Id,'"', '') as INT64) AS Id,
Nombre AS Nombre,
PARSE_DATE('%d/%m/%Y',REPLACE(FechaInicio,'--', '/')) AS FechaInicio,
LOWER(TRIM(Descripcion, ' ')) AS Descripcion
FROM `bigquery-project.post_trucos_bigquery.raw2`

Solo nos queda ejecutar la query sobre el total de los datos y guardar los resultados como una nueva tabla quedando los datos ya listos para su análisis.

Aunque se pueda hacer este proceso mediante vistas hay que saber que tendremos el coste añadido de realizar la query de transformación cada vez que necesitemos los datos.

Pero, aún hay mucho más...

Como hemos visto, BigQuery es una herramienta con grandes posibilidades pero aún nos queda mucho por conocer: usar sus funcionalidades GIS junto con Google Maps, entrenar o usar modelos de machine learning con BigQuery ML, conectarlo con Colab, con Data Studio…

¡Tenemos un podcast que puede interesarte!