Cocinando datos en Bigquery para Power BI: primeros pasos

¿Quieres que a tu cliente se le haga la boca agua cuando consuma los datos? En este post de dos partes veremos cómo preparar bien los ingredientes (Datos) y cómo emplatarlos de manera distintiva (visualización). En primer lugar, vamos a limpiar y preparar los datos mediante el uso de BigQuery y otras herramientas.

Si estás listo solo tienes que seguir los siguientes pasos y obtendrás unos datos para chuparse los dedos:

• Ingredientes a escoger: ¿Cocinas para dos o para una familia? ¿Por qué BigQuery?
• ¿Quieres emplatar como la abuela o como Ferrán Adrià? ¿Por qué Power BI?
• Preparación de datos: BigQuery y otras herramientas.

A continuación vamos a tratar de describir cómo utilizar todo el potencial de Google BigQuery como Datawarehouse para generar paneles de Microsoft PowerBI de la manera más eficaz y vistosa posible.

Tal y como dijo una vez un gran scrum master: “Una gráfica vale más que mil queries”.

Ingredientes a escoger

Lo primero que hay que hacer cuando se cocina es escoger los ingredientes de manera correcta. En este caso, necesitamos una herramienta de analítica y otra de business intelligence y, para escoger una u otra es importante hacerse dos preguntas, que respondemos en los siguientes puntos.

¿Cocinas para dos o para una familia?¿Por qué BigQuery?

Google BigQuery ya ha sido mencionado varias veces en este blog. Nuestro compañero Tomás Calleja daba varias claves para sacarle el máximo partido.

Para aquellos que aún no lo conozcan, os dejamos una descripción:

• Según Google, BigQuery es un almacén de datos en la nube, serverless y autoescalable. Una de las mayores ventajas es que al no tener que gestionar nada te puedes centrar en los datos. Además, el hecho de utilizar SQL para interactuar con los datos lo hace muy asequible para un gran público.

Hay otras herramientas de analítica que pueden conectarse con Power Bi (como es el caso de sql server), pero en este caso vamos a “cocinar” un conjunto de datos muy grande, por eso escogemos BigQuery en este ejemplo, debido a que es capaz de procesar una cantidad de datos muy grande en apenas segundos.

¿Quieres emplatar como la abuela o como Ferrán Adrià?¿Por qué Power BI?

PowerBI es la herramienta de visualización y análisis de datos de Microsoft. Permite generar diferentes visualizaciones sobre los datos y compartirlas con el resto de la organización o clientes.

La interfaz es amigable, ya que todos hemos trabajado en algún momento con la suite MS Office, y en concreto el lenguaje de fórmulas DAX, que se utiliza también en Excel. Para más información, podéis visitar el este post de Marco Russo en el que compara diferentes herramientas de BI.

Hay muchas herramientas de BI, como dice Marco en su post, pero en este caso nos decantamos por Power BI por la familiaridad que tiene para los usuarios de MS Office y las posibilidades de la versión gratuita, que no son pocas.

Power Bi nos permite generar dashboards de una manera rápida y sencilla (pero esto es algo que veremos en la segunda parte del post que publicaremos más adelante).

Preparación de datos

Como si de una receta se tratase, los datos en crudo son los ingredientes que, tras cocinarlos adecuadamente, serán consumidos por el cliente. En este caso a través de Power BI.

A veces cuando llegas a un proyecto y preguntas si hay que hacer un tratamiento para homogeneizarlos y dejarlos usables, te suelen decir que no hace falta porque ya tienen sistemas de control y preparación para tener las tablas o ficheros correctamente.

Esto alguna vez no es así, sobre todo porque la cultura del gobierno del dato no es algo muy implantado en España. Es verdad que poco a poco se va mejorando en este tema, pero aún hay muchos sistemas que no tienen estos controles. Esto da para otro post, así que dejémoslo de momento.

Es importante que, antes de consumir estos datos, revisemos que el estado de los mismos es el correcto. Por ejemplo, ¿quién no se ha encontrado una almeja con arena? Para que el consumidor quede satisfecho es importante preparar los datos correctamente y tratar de “limpiarlos” lo mejor posible.

La preparación de los datos se puede hacer de muchas maneras, pero vamos a tratar de explicar un modo general cómo los utilizamos nosotros en los diferentes proyectos.

Imaginemos que estamos en un proyecto en el que al extraer los datos
se detectaron varios tipos de “errores”. Haremos una separación entre casos simples que se pueden resolver a través de BigQuery y otros que requieren del uso de herramientas más especializadas:

BigQuery

Fechas con diferente formato en base al idioma

En ocasiones nos encontramos con cosas como esta: 03-sep-2018 00:00:00, 31-jul-2018 00:00:00. Power BI permite mediante la elaboración de nuevas medidas, y usando la función FORMAT, pasar un string a formato fecha y personalizar el formato de dicha fecha (estableciendo cosas como la separación con ‘/’ o ‘-’, la cantidad de dígitos en el año ,etc). El problema es que en un caso como el que presentamos necesitamos de una lógica mayor, y para ello haremos uso de BigQuery.

En este caso, para resolver el problema y transformar el string en un formato de fecha estándar (yyyy-mm-dd) usaremos en siguiente código:

CASE
        WHEN (SUBSTR(fecha,4,3))= "ene" THEN PARSE_DATE('%d-%m-%Y', SUBSTR(REPLACE(fecha,'ene', '01'),0,10))
        WHEN (SUBSTR(fecha,4,3))= "feb" THEN PARSE_DATE('%d-%m-%Y', SUBSTR(REPLACE(fecha,'feb', '02'),0,10))
        WHEN (SUBSTR(fecha,4,3))= "mar" THEN PARSE_DATE('%d-%m-%Y', 0,10))
END as fecha

Fechas en distintos formatos

En otras ocasiones, podemos encontrarnos con fechas que tienen diferentes formatos que nos interesa normalizar (2018-09-07, 20180907, 2018/09/07, etc). Para resolver esto podemos usar el antes nombrado FORMAT.

El problema es que meter este tipo de lógica que afecta a cada fila de la tabla en Power BI cuando el conjunto de datos es muy grande, puede generar problemas de tiempo de procesamiento, y los filtros pueden no funcionar todo lo rápido que se quiere. Por eso se recomienda preparar los datos en una tabla para BI, para que luego puedan ser explotados por el BI que se quiera. En este caso, en BigQuery la solución es muy simple:

select PARSE_DATE('%Y%m%d', fecha) as fecha
select PARSE_DATE('%Y/%m/%d', fecha) as fecha

En el primer caso, el resultado sería el formato 2018-09-07 cuando el formato de entrada es 20180907. En el segundo caso el resultado sería el mismo para una entrada de tipo 2018/09/07.

Columnas con espacios en lugar de vacíos o nulls

Con el fin de ahorrar costes, sobre todo cuando el conjunto de datos es muy grande, eliminar los espacios vacíos es algo clave. Para ello, una vez se haya identificado el campo que contiene espacios vacíos, utilizaremos el siguiente código de manera que se sustituirán por null.

SELECT IF(RTRIM(NOMBRE)==0,null,FIELD) AS NOMBRE

Nombres con espacios a derecha o izquierda

Esto sirve para el caso concreto en el que un campo requiera ser utilizado en la cláusula de algún join, cuando en una tabla existen espacios a izquierda o derecha y en la otra no, o al revés. Para resolver esto podemos usar el siguiente código:

SELECT RTRIM(NOMBRE) AS NOMBRE
SELECT LTRIM(NOMBRE) AS NOMBRE

En el primer caso eliminamos los espacios en la derecha y en el segundo, en la izquierda. Otra opción, en caso de que el campo no tuviera espacios entre medias, es usar la función TRIM() de la misma manera.

Datos no tipados en bbdd

En ocasiones recibimos archivos en raw que no están tipados. Para explotar los datos con un software de BI es aconsejable tratar de tipar los datos lo mejor posible, puesto que por defecto los detectará como String, no solo por optimizar el espacio en algún caso, sino además por mejorar la calidad de la consulta y reducir el coste debido al procesamiento.

Para resolver este tipo de casuísticas podemos usar la función cast(), usada cuando el que el dato ya tiene el formato correcto pero no el tipo, o para aplicar lógicas más complejas combinando la función con los puntos anteriores, entre otras posibilidades.

select cast("20180910" as int64); --> 20180910
select cast("9.09" as float64); --> 9.09

Ficheros sin cabeceras

Lo primero de todo, y lo más importante, es conocer las cabeceras originales. Una vez se haya resuelto esto podemos proceder de muchas maneras, pero la opción más sencilla y rápida es utilizando la propia consola de BigQuery o por comandos en la cloud shell.

1. Usando la consola:

Al pulsar en el botón “crear tabla” se abrirá el siguiente formulario:

• En el primer botón podemos elegir desde qué archivo deseamos crear la tabla.
• Se selecciona el destino de la tabla a crear.
• Se introduce el nombre de la tabla.
• Por último, y a lo que venimos, si seleccionamos la opción “editar como texto” nos aparece un cuadro de texto que nos permite introducir el esquema en formato json. Otra opción, aunque mucho más tediosa, es ir añadiendo campos en el botón “add field”, que desplegará el siguiente formulario donde podemos introducir el nombre el tipo y el modo de cada campo.jsc.

2. Usando cloud shell:

• Arrancamos la cloud shell, pulsando el botón situado en la parte superior derecha.

• Se abrirá la consola en la parte inferior, tal y como se muestra en la imagen.

• Finalmente, introducimos el siguiente comando creado previamente, un fichero Json que contenga el esquema deseado en la tabla:

bq --location=location load \
--source_format=format \
project_id:dataset.table \
path_to_data_file \
path_to_schema_file

• El json debe de tener el formato que vemos a continuación. El “campo mode” no es obligatorio, por defecto obtendrá el valor nullable.

[
  {
    "mode": "REQUIRED",
    "name": "qtr",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "rep",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "sales",
    "type": "FLOAT"
  }
]

Cabeceras cambiantes entre fechas para el mismo fichero

Una solución más eficaz sería tratar todos los ficheros mediante un programa antes de cargarlos en BigQuery, pero con el objetivo de no perder los datos en crudo y centrar la analítica en los mismos.

Una posible solución que nos ofrece BigQuery son las vistas, las cuales guardan una query que relaciona estos dos ficheros y devuelve la tabla formada por la unión de ambos y tipada correctamente con los campos de interés.

Lo positivo de esto es que al no tratarse de una tabla, no ocupa espacio en el storage de Bigquery. Lo negativo es que cada vez que se llame a la vista, se estará ejecutando la query por debajo y por tanto estará procesando más datos.

Supongamos que tenemos la dos siguientes tablas:

Tabla natalidad 1

Tabla natalidad 2

Como podemos observar, las tablas contienen diferente cantidad de campos y algunos tienen nombres diferentes. Si se intentara actuar de la manera habitual en BigQuery realizando un append, nos saldrá un error indicándonos que el número de columnas es diferente. Para resolverlo y guardarlo en una vista se puede hacer lo siguiente:

CREATE VIEW IF NOT EXISTS
  `dataset.view_name` AS (Select *
  FROM (
    SELECT
      year,
      month,
      state,
      is_male AS male,
      child_race,
      CAST(weight_pounds AS float) AS weight,
      mother_resindence_state AS mother_state,
      mother_age
    FROM
      `dataset.natalidad_1`)
  UNION ALL (
    SELECT
      year,
      month,
      state,
      male,
      child_race,
      CAST(weight_pounds AS float) AS weight,
      mother_state,
      mother_age
    FROM
      `dataset.natalidad_2`) );

De forma que seleccionamos solo los campos de interés, y conseguimos tener una vista que devuelve los datos resultantes de la unión de las dos tablas.

Otras herramientas

Hay veces que para preparar los datos será necesario el uso de herramientas externas, como puede ser el próximo ejemplo.

Cuando el sistema operativo en el que se ha salvado el archivo utiliza un encoder diferente al que utiliza el software de analítica (en este caso Bigquery), se producen errores al importar el archivo. En este caso, hablamos de un retorno de carro (CR) que se interpreta mal debido a que los encoders son diferentes; es decir, en un campo hay oraciones en las que se ha utilizado el botón enter, de forma que al tratar de importarlo en sistema lo detecta como un salto de línea.

Para este problema hay múltiples soluciones como montar un script de python o usar spark, pero por su simpleza podemos usar el siguiente script de bash para eliminar estos salto de línea no deseados:

#!/bin/bash
<br>inputfile=$1
<br>outputfile=$2
<br>#cambia el retorno de carro al final por una @ | añade un espacio delante del salto de línea | elimina los saltos de línea | cambia @ por salto de línea
<br> sed 's/\r$/@/' $inputfile | tr '\n' ' \n' | tr -d '\n'| tr '@' '\n'  > $outputfile
<br>#elimina el espacio al principio de cada fila
<br> sed -i 's/^ *//' $outputfile

En el caso en el que el conjunto de datos a modificar sea muy grande, y para evitar problemas en la memoria, es preferible usar un script de python o spark, usando librerías como dask, que permite procesar los ficheros en disco.

Conclusiones

Cuando el producto está basado en los datos, es importante disponer de herramientas que nos permitan limpiarlos y estructurarlos de forma que la analítica y la posterior explotación sea lo más óptima posible.

BigQuery es una herramienta increíble, no solo porque permite almacenar gran cantidad de datos estructurados y analizarlos con una potencia de procesamiento altísima, sino que además, como hemos visto, tenemos la posibilidad de preparar estos datos para que sean explotados de una forma óptima por la herramienta de BI que escojamos.

¡Tenemos un podcast que puede interesarte!