↓Ir al contenido
Pablo Sáenz de Tejada | En español

Cómo convertir un JSON en datos estructurados. La función FLATTEN en Snowflake

·1652 palabras·8 mins
snowflake sql
Pablo Sáenz de Tejada
Autor
Pablo Sáenz de Tejada
Ayudo a las personas a analizar, visualizar y comunicar con datos.
Tabla de contenido

¿Necesitas transformar datos semiestructurados en estructurados? Es más sencillo de lo que parece con la función FLATTEN en Snowflake.

Hoy en día contar con datos semiestructurados, como los disponibles en ficheros JSON, es muy habitual. El INE1, por ejemplo, permite descargar los datos en ficheros JSON semiestructurados. Lo cual puede ser una gran ventaja a la hora de manejar grandes volumenes de información, pero que suele dar más problemas a la hora de analizarlos.

En estos casos, podemos cargar rápidamente datos en nuestro data lake o data warehouse en ese formato semiestructurado, pero entonces ¿cómo facilitamos posteriormente el análisis de esos datos? Aquí es donde entra en juego la función FLATTEN disponible en Snowflake que es increíblemente útil para transformar esos datos semiestructurados en estructurados.

¿Para qué sirve la función Flatten de Snowflake?
#

De forma muy resumida:

La función FLATTEN permite partir de una estructura de datos anidados, como la de un fichero JSON, y convertirla en una tabla plana donde cada elemento anidado se convierte en una fila individual, mientras se mantienen las columnas de la tabla original.

Es decir, permite pasar de unos datos con esta estructura:

| id | COD       | Data                                                            |
|----|-----------|-----------------------------------------------------------------|
| 1  | ADRH18589 | [{"Anyo":2021, "Valor":20587.0},{"Anyo":2020, "Valor":20118.0}] |
| 2  | ADRH18587 | [{"Anyo":2021, "Valor":12271.0},{"Anyo":2020, "Valor":11226.0}] |

A esta otra estructura:

| id | COD       | Anyo | Valor   |
|----|-----------|------|---------|
| 1  | ADRH18589 | 2021 | 20587.0 |
| 1  | ADRH18589 | 2020 | 20118.0 |
| 2  | ADRH18587 | 2021 | 12271.0 |
| 2  | ADRH18587 | 2020 | 11226.0 |

¿Cómo podemos pasar de una estructura a otra?

La sintáxis en SQL no es compleja. En base al caso anterior sería de forma resumida la siguiente:

SELECT
    t.COD,
    d.Anyo,
    d.Valor
FROM
    miTabla t,
    LATERAL FLATTEN (input => t.Data) d;

Es decir, indicamos que queremos consultar la columna COD de la tabla miTabla (t) y que queremos también obtener como dos columnas separadas los valores de Anyo y Valor pero estos se encuentran dentro de la columna Data, la cual queremos expandir con esa función LATERAL FLATTEN.

Ejemplo práctico: Datos de renta del INE por sección censal
#

El INE ofrece datos de renta en España por sección censal que son fáciles de descargar desde la web, y al mismo tiempo muy fáciles de cargar a una plataforma como Snowflake.

Estructura del fichero JSON del INE
#

Al descargar estos datos en formato JSON, obtenemos un fichero que tiene la siguiente estructura:

[
    {
        "COD":"ADRH18589", 
        "Nombre":"Melilla sección 01001. Dato base. Renta neta media por persona. ", 
        "T3_Unidad":"Euros", 
        "T3_Escala":" ", 
        "MetaData":[
            {"Id":366733, "Variable":{"Id":847, "Nombre":"Secciones", "Codigo":"SECC"}, "Nombre":"Melilla sección 01001", "Codigo":"5200101001"},
            {"Id":72, "Variable":{"Id":3, "Nombre":"Tipo de dato", "Codigo":""}, "Nombre":"Dato base", "Codigo":""},
            {"Id":284048, "Variable":{"Id":482, "Nombre":"SALDOS CONTABLES", "Codigo":""}, "Nombre":"Renta neta media por persona ", "Codigo":""}
        ], 
        "Data":[
            {"Fecha":"2021-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2021, "Valor":20587.0},
            {"Fecha":"2020-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2020, "Valor":20118.0},
            {"Fecha":"2019-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2019, "Valor":19863.0},
            {"Fecha":"2018-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2018, "Valor":18507.0},
            {"Fecha":"2017-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2017, "Valor":17919.0},
            {"Fecha":"2016-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2016, "Valor":16805.0},
            {"Fecha":"2015-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2015, "Valor":16237.0}
        ]
    },
    {
        "COD":"ADRH18587", 
        "Nombre":"Melilla sección 01002. Dato base. Renta neta media por persona. ", 
        "T3_Unidad":"Euros", 
        "T3_Escala":" ", 
        "MetaData":[
            {"Id":366734, "Variable":{"Id":847, "Nombre":"Secciones", "Codigo":"SECC"}, "Nombre":"Melilla sección 01002", "Codigo":"5200101002"},
            {"Id":72, "Variable":{"Id":3, "Nombre":"Tipo de dato", "Codigo":""}, "Nombre":"Dato base", "Codigo":""},
            {"Id":284048, "Variable":{"Id":482, "Nombre":"SALDOS CONTABLES", "Codigo":""}, "Nombre":"Renta neta media por persona ", "Codigo":""}
        ],
        "Data":[
            {"Fecha":"2021-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2021, "Valor":12271.0},
            {"Fecha":"2020-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2020, "Valor":11226.0},
            {"Fecha":"2019-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2019, "Valor":11145.0},
            {"Fecha":"2018-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2018, "Valor":10769.0},
            {"Fecha":"2017-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2017, "Valor":10204.0},
            {"Fecha":"2016-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2016, "Valor":10024.0},
            {"Fecha":"2015-01-01T00:00:00.000+01:00", "T3_TipoDato":"Definitivo", "T3_Periodo":"A", "Anyo":2015, "Valor":9752.0}
        ]
    }
]

El fichero JSON de arriba contiene los datos para dos secciones censales. Cada elemento dentro del JSON, es decir, cada sección censal, está formado por 5 elementos. Además, dos de ellos (Metadata y Data), contienen a su vez sub-elementos:

  • COD
  • Nombre
  • T3_Unidad
  • T3_Escala
  • Metadata - Con 3 sub-elementos contienendo cada uno de ellos:
    • ID
    • Variable
    • Nombre
    • Código
  • Data - Con 7 sub-elementos contienendo cada uno de ellos:
    • Fecha
    • T3_TipoDato
    • T3_Periodo
    • Anyo
    • Valor

Estructura de la tabla al subir los datos a Snowflake
#

Una de las ventajas de Snowflake es que podemos cargar directamente los datos de ficheros JSON como este, pero al hacerlo almacenará la información en 2 filas, una por sección censal, y en 5 columnas: COD, Nombre, T3_Unidad, T3_escala, Metadata y Data.

Algo similar a esto:

| COD       | Nombre | T3_Unidad | T3_Escala | Metadata    | Data    |
|-----------|--------|-----------|-----------|-------------|---------|
| ADRH18589 | Texto  | Euros     |           | [Metadatos] | [Datos] |
| ADRH18587 | Texto  | Euros     |           | [Metadatos] | [Datos] |

¿Cómo podemos analizar entonces la información a la renta neta media por persona, para cada sección censal y cada año?

De alguna manera, tenemos que conseguir tener una columna con la Fecha o Anyo y el Valor de cada sección censal que está dentro de Data. Además, tal vez necesitemos también mantener como columnas independientes información dentro de la sección de Metadata, por ejemplo el Nombre y el Código pero sólo del primer elemento dentro de dicha columna de Metadata. Es precisamente para este tipo de casos de uso donde la función nativa FLATTEN de Snowflake es de gran ayuda.

La query o consulta en Snowflake con la función FLATTEN
#

Esta es la query en Snowflake que nos va a permitir analizar la información más fácilmente expandiendo el array de elementos DATA dentro del fichero JSON, mantiendo el resto de la estructura y además generando columnas adicionales para el nombre de la sección censal y el código de la sección censal que se encuentran en el primer elemento del array de Metadata:

SELECT
    t.COD,
    d.value:Anyo::INTEGER as Anyo,
    d.value:Fecha::date as Fecha,
    d.value:T3_TipoDato::VARCHAR(50) as tipoDato,
    d.value:T3_Periodo::VARCHAR(1) as tipoPeriodo,
    d.value:Valor::FLOAT as rentaNetaMediaPersona,
    t.metadata[0]:Nombre::STRING as nombreSeccionCensal,
    t.metadata[0]:Codigo::STRING as codigoSeccionCensal
FROM
    miTabla t,
    LATERAL FLATTEN(input => t.DATA) d

Lo que indicamos en la query es lo siguiente:

  • Las columnas de la tabla que queremos mantener tal como están, como es el caso de COD.
  • Las columnas que no queremos expandir, pero que queremos consultar dentro de un lugar específico de un array, como en el caso de la columna metadata, de la que queremos obtener el nombre y el código pero sólo del primer elemento del array. De ahí que seleccionemos metadata[0] (el primer elemento dentro del array de la columna metadata).
  • Los valores dentro del array de data que queremos expandir en filas diferentes, indicando además con :: el tipo de dato que queremos para esa nueva columna. Por ejemplo: d.value:Fecha::date as Fecha indica que queremos obtener el valor que estamos expandiendo dentro del elemento d que se llama Fecha y queremos que Snowflake lo trate como una valor de tipo fecha y a esa nueva columna que genere, le ponga el nombre Fecha.
  • ¿Y qué significa ese d.value? Aquí es donde entra en acción la parte final de la query LATERAL FLATTEN (input => t.DATA) d. Lo que estamos indicando es que, dentro de la tabla miTabla t, queremos desanidar la columna DATA, generando una nueva fila para cada elemento dentro de ese array de DATA. Podemos incluso utilizar múltiples instancias de LATERAL FLATTEN, ya que cada vista LATERAL que utilicemos se basa en la anterior para referenciar a los elementos dentro de arrays de varios niveles.

El resultado
#

Con dicha query, conseguiremos una nueva tabla o vista con la siguiente estructura:

| COD       | Anyo | Fecha      | tipoDato   | tipoPeriodo | rentaNetaMediaPersona | nombreSeccionCensal   | codigoSeccionCensal |
|-----------|------|------------|------------|-------------|-----------------------|-----------------------|---------------------|
| ADRH18589 | 2021 | 2021-01-01 | Definitivo | A           | 20587.0               | Melilla sección 01001 | 5200101001          |
| ADRH18589 | 2020 | 2020-01-01 | Definitivo | A           | 20118.0               | Melilla sección 01001 | 5200101001          |
| ADRH18589 | 2019 | 2019-01-01 | Definitivo | A           | 19863.0               | Melilla sección 01001 | 5200101001          |
| ADRH18589 | 2018 | 2018-01-01 | Definitivo | A           | 18507.0               | Melilla sección 01001 | 5200101001          |
| ADRH18589 | 2017 | 2017-01-01 | Definitivo | A           | 17919.0               | Melilla sección 01001 | 5200101001          |
| ADRH18589 | 2016 | 2016-01-01 | Definitivo | A           | 16805.0               | Melilla sección 01001 | 5200101001          |
| ADRH18589 | 2015 | 2015-01-01 | Definitivo | A           | 16237.0               | Melilla sección 01001 | 5200101001          |
| ADRH18587 | 2021 | 2021-01-01 | Definitivo | A           | 12271.0               | Melilla sección 01002 | 5200101002          |
| ADRH18587 | 2020 | 2020-01-01 | Definitivo | A           | 11226.0               | Melilla sección 01002 | 5200101002          |
| ADRH18587 | 2019 | 2019-01-01 | Definitivo | A           | 11145.0               | Melilla sección 01002 | 5200101002          |
| ADRH18587 | 2018 | 2018-01-01 | Definitivo | A           | 10769.0               | Melilla sección 01002 | 5200101002          |
| ADRH18587 | 2017 | 2017-01-01 | Definitivo | A           | 10204.0               | Melilla sección 01002 | 5200101002          |
| ADRH18587 | 2016 | 2016-01-01 | Definitivo | A           | 10024.0               | Melilla sección 01002 | 5200101002          |
| ADRH18587 | 2015 | 2015-01-01 | Definitivo | A           | 9752.0                | Melilla sección 01002 | 5200101002          |

Con esta estructura, la información ya es mucho más fácil de analizar con cualquier herramienta de BI o de analítica, como Tableau.

Ventajas de la función FLATTEN
#

Como vemos, la función FLATTEN es una forma fácil y rápida de generar tablas o vistas estructuradas de datos semi-estructurados en Snowflake. Alguna de las ventajas que nos ofrece esta función son:

  • Acceder a elementos anidados: Facilitando así el trabajo sobre esos elementos anidados en la estructura de datos.
  • Mejorar la estructura de datos: Ayudando a transformar datos semi-estructurados en un formato relacional para un analizarlos más fácilmente.
  • Flexibilidad en Consultas: Permitiéndonos trabajar de manera más fácil sobre grandes conjuntos de datos.
  • Ahorrar tiempo de preparación de datos: Ya que evitará complejos procesos de ETL, pudiendo cargar en bruto los datos semi-estructurados y posteriormente generar vistas con la estructura que nos sea más cómoda para el análisis.

  1. Instituto Nacional de Estadística de España ↩︎