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.

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:

IdNombreValor
2Cliente 125
5Cliente 2300
16Cliente 3200

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

IdNombreValor
5Cliente 2200
30Cliente 40
16Cliente 3Borrado

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.

IdNombreValorTimestampDeleted
2Cliente 125Día 1
5Cliente 2300Día 1
16Cliente 3200Día 1
5Cliente 2200Día 2
30Cliente 40Día 2
16Cliente 30Día 2TRUE

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.

IdNombreValorTimestampDeleted
2Cliente 125Día 1
5Cliente 2200Día 2
30Cliente 40Día 2
16Cliente 30Día 2TRUE

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:

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

. 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.

. 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.

IdNombreFecha de inicioDescripción
"2"Cliente 11--12--19Es un cliente bueno
5Cliente 21--12--19Demasiados espacios
"16"Cliente 31--12--19TODO MAYUS
17Cliente 41--12--19asfasdfasdf

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

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

IdNombreFecha de inicioDescripción
2Cliente 11/12/19es un cliente bueno
5Cliente 21/12/19demasiados espacios
16Cliente 31/12/19todo mayus
17Cliente 41/12/19asfasdfasdf

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

Cuéntanos qué te parece.

Enviar.

Los comentarios serán moderados. Serán visibles si aportan un argumento constructivo. Si no estás de acuerdo con algún punto, por favor, muestra tus opiniones de manera educada.