Tratamiento SQL de archivos CSV. Comparador implementado utilizando Apache Calcite.

Esta información puede resultar de especial interés para aquellos que, como yo, estáis cursando el Máster en Business Analytics y Big Data UCJC de IMF Business School, y en general, a cualquiera que necesite comparar dos conjuntos de datos, por ejemplo el resultado de un proceso con lo esperado. Datos extraidos de (probablemente) fuentes heterogéneas, a las cuales no tenemos acceso simultáneo. Un ejemplo podría ser dos esquemas de Hive, a los que accedemos desde Hue, y únicamente podemos consultar y descargar el resultados de ambas consultas, para compararlos.

El caso es que hace unos días, mientras trabajaba en un proyecto BigData con scala-spark, como parte de las prácticas del Máster, me encontré por casualidad con Apache Calcite. Digo de casualidad, porque no lo utilizaba directamente, sino que es una dependencia del spark-hive de Cloudera.

Apache Calcite

es un framework de manejo de datos (no confundir con BDD). Con unos 5 años de vida no es, por su nombre, uno de los proyectos más conocidos de la Apache Software Fundation.

No hay información abundante (en español) acerca de este producto, pero según he podido investigar, proyectos Apache de primer nivel como Drill, Kylin, Storm o Flink utilizan Calcite, para parseo de SQLs y optimización de queries, entre otros.

Por último, adivina que? es una de las dependencias de Apache Spark siendo este sí uno de los frameworks de computación en clúster más utilizados / conocidos de moda actualmente.

Como decíamos, no almacena datos, pero dispone de adaptadores para conectarse a diversas fuentes de datos. Algunos de estos adaptadores permiten conexión JDBC, MongoDB, Solr, Elasticsearch, Cassandra y un largo etc.

CSVs como si de SQL se tratase

Pues bien, es mediante el adaptador de CSV que hemos encontrado un uso que da solución a una necesidad que nos ha asaltado con cierta frecuencia en el tiempo: es la de acceder / analizar o incluso comparar archivos CSV no desde el punto de vista de archivos planos (para eso hay muchos comparadores), sino desde el punto de vista de un "dataset". Casos en los que el orden de las columnas no importa, y muchas veces tampoco el orden de las filas. Para determinar que una matriz de datos es equivalente a otra, independientemente del orden de sus filas y columnas.

Simplemente descargamos los datos a comparar de ambas fuentes, en formato CSV, y Calcite nos va a permitir acceder a ellos como dos fuentes de datos accesibles vía SQL, lenguaje mayoritario que todo el mundo conoce y que permite análisis y comparaciones ágiles.

Los pasos para tener funcionando nuestro flamante “eSeQueLizador” (perdón, los que me conocéis lo sabéis, me gusta hablar como los niños pequeños, me encantan las “analogías”) de CSV’s son los siguientes:

1) Lo primero es descargar y construir calcite. Esto, siguiendo sus instrucciones, son únicamente 4 comandos

$ git clone https://github.com/apache/calcite.git
$ cd calcite
$ ./mvnw install -DskipTests -Dcheckstyle.skip=true 

o mvnw.cmd install -DskipTests -Dcheckstyle.skip=true si estamos en windows (sic)

2) Configurar una fuente de datos, que haría las veces de “esquema”. Para ello en una ubicación de nuestra preferencia (para el ejemplo calcite\example\csv\data` ) creamos un archivo descriptor de la fuente de datos (para el ejemplo almacen.json):

{
  "version": "1.0",
  "defaultSchema": "ALMACEN",
  "schemas": [
    {
      "name": "ALMACEN",
      "type": "custom",
      "factory":         "org.apache.calcite.adapter.csv.CsvSchemaFactory",
      "operand": {
        "directory": "almacen"
      }
    }
  ]
}

y una carpeta "almacen" referenciada por el mismo (en el ejemplo calcite\example\csv\data\almacen) que contendrá los archivos .CSV que harán las veces de tabla (en el ejemplo STOCKALMACEN.csv y STOCKALMACENFAKE.csv )

Si quieres seguir el ejemplo puedes descargar los archivos

3) Arrancar la consola con el comando sqlline (shell script incluido en el proyecto calcite\example\csv )

4) conectarnos a la fuente de datos configurada en el paso 2. De la siguiente forma:

!connect jdbc:calcite:model=data/almacen.json admin admin

En este momento ya podemos consultar las “tablas” (archivos CSV tratados como tal) existentes en el “esquema” almacen -la carpeta referenciada en la fuente de datos que hemos configurado- con el comando:

jdbc:calcite:model=data/almacen.json> !tables

Show tables - CSV's SQL style

Y a la vista de las “tablas” disponibles, trabajar con ellas SQL’style:

Queries - CSV's SQL style

Más Queries - CSV's SQL style

Por ejemplo, podemos descubrir fácilmente las filas de la tabla (líneas del archivo) stockalmacen que no se encuentran en stockalmacenfake, considerando que su “primaria” sería la columna referencia, de la siguiente forma:

select * from stockalmacen where not referencia in (select referencia from stockalmacenfake);

Joins - CSV's SQL style

Finalmente, hemos ido un paso más allá, y como ejercicio, hemos codificado un pequeño

Proyecto Java

donde hemos implementado un comparador de archivos CSV por código. Permite realizar la comparación con independencia del orden de las filas o columnas, si así lo deseamos, e indicando la clave primaria del dataset si la conocemos. Finalmente, nos informa si son dataset equivalentes o no (respuesta booleana), esto es, si contienen o no “los mismos” datos.

Espero que sea de vuestro interés, o por lo menos, que no os haga perder el tiempo.

El proyecto lo podéis descargar de mi repo de GitHub.

Alberto Morales Morales

Software craftsman. Passion for developing quality code that can be proud of. Happily married.

Madrid, Spain.