Cómo conectar Apache Drill con MySQL

Hoy de nuevo les traigo a ustedes un post relacionado con Apache Drill. Como les comenté en el post anterior, apache drill funciona con plugins, donde en cada uno de estos se define como se establece la conexión. Por ende en este caso nosotros deberemos crear un plugin para conectarnos con MySQL. De nuevo utilizaremos el modo embebed de la herramienta por lo cual para iniciar deberemos ir a la carpeta bin dentro de la instalación de apache drill y ejecutar el archivo drill-embedded en el caso de aquellos que estén utilizando Windows deben ejecutar sqlline.bat.

Desde un navegador web nos vamos a la dirección

http://localhost:8047/storage

Ruta donde están los plugin de antemano definidos. Para crear nuestro nuevo plugin para conectarnos con MySQL nos iremos a la parte inferior y en el campo de texto le daremos el nombre del nuevo plugin a crear, en nuestro caso lo llamaremos mysql y pulsaremos el botón «Create».

create plugin

create plugin

Inmediatamente después en el campo «Configuration» introducimos el siguiente json eliminando el null que trae por defecto y pulsamos el botón «Create»

donde:

username: será el nombre de usuario que utilizamos para conectarnos a MySQL.

password: será el password que utilizamos para conectarnos a MySQL.

url: El host y el puerto al que nos conectamos en MySQL.

configuración del plugin

configuración del plugin

El siguiente paso es colocar jar driver mysql en la ruta <drill_installation_directory>/jars/3rdparty

Hay un último paso a llevar a cabo en la configuración, para que la definición del plugin se mantenga una vez hayamos reinciado el ordenador o iniciado una nueva sesión con drill.

Editaremos el fichero <drill_installation_directory>/conf/drill-override.conf y especificaremos la ruta donde se almacenarán las configuraciones (definiciones) que hagamos de los plugins por ejemplo:

Una vez hecho esto si reiniciamos drill veremos en los storage el plugin de mysql. Por último para comprobar la definición del plugin solo necesitaremos efectuar una consulta por ejemplo utilizando el UI de apache drill de la forma:

SELECT * mysql.database_name.table_name

Al igual que con el post anterior una vez definido el plugin de mysql podremos efectuar consultas del tipo join que involucren una tabla en MySQL, un fichero CSV, un fichero JSON y otros.

Primeros pasos con Apache Drill

¿Qué es apache drill?

Es un motor de consultas open-source para exploración de fuentes de datos con grandes volúmenes de datos. Apache drill nos permite realizar análisis de alto rendimiento sobre datos semiestructurados sin dejar de ofrecer la familiaridad y el ecosistema de la norma ANSI SQL. Apache drill a su vez posee integración con Hive y HBase.

Apache drill a menudo es comparado con Hive y con Impala, por su alto rendimiento por trabajar con ficheros .csv y .json, así como también porque por medio de estas podemos efectuar consultas en HBase, pero hay un aspecto donde drill sobresale y es que puede conectarse a otros gestores de bases de datos como por ejemplo MySQL y MongoDB.

¿Cómo conocí Apache drill?

Me topé con apache drill por casualidad en el 2015, debido a las circunstancias y dificultades con las que trabajábamos, teníamos ordenadores plataformas que no nos permitían instalar nada y como tarea teníamos que hacer cruce de información de grandes ficheros .csv con sistemas de bases de datos relacionales. Los ficheros .csv eran tan grandes que ni siquiera podíamos visualizarlos con excel ni con atom y apache drill termino siendo una herramienta estupenda para poder realizar exploración sobre los datos y eso que lo utilizamos en modo embebido en nuestro ordenadores.

Instalación

Apache drill tiene 2 tipos de instalación dependiendo si será en un cluster o si será en un único nodo, nosotros haremos la de un único nodo, la cual es muy sencilla ya que solo es necesario descomprimir el fichero descargado y ejecutar el  fichero ./drill-embebed el cual esta en la carpeta /bin de nuestra instalación.

Este último paso abrirá una consola donde podremos ejecutar sentencias sql y además se levantará un cliente web al que podremos acceder desde cualquier navegador en la ruta http://localhost:8047

apache drill web client

apache drill web client

Ahora vamos a empezar a jugar con drill, para ello crearemos un fichero json que denominaremos cliente_banco.json con los siguientes datos:

También crearemos un fichero csv con los datos de los clientes y lo llamaremos clientes.csv:

Ahora que empiece la diversión, lo primero que haremos será consultar los datos del fichero clientes.csv como si fuera una tabla con SQL utilizando el cliente Web de drill, para ello será necesario ir a la ruta http://localhost:8047/query

Una vez allí ejecutaremos la siguiente sentencia:

SELECT * FROM dfs.ruta_fichero/clientes.csv

resultado query csv en drill

resultado query csv en drill

El resultado no se puede apreciar muy bien además que se ve que asume la cabecera del fichero csv como si fuese un registro, para mejorar esto, será necesario hacer una pequeña modificación en la configuración. Apache drill funciona con plugins donde se almacena toda la configuración de las conexiones con ficheros del filesystem, de gestores de bases de datos, tipo mongoDB, MySQL, etc. por ende será necesario que editemos la configuración del plugin de filesystem para que tome en cuenta la cabecera del fichero csv (NOTA: Aquí también podríamos configurar el tipo de separador ya sea «,»o «;» o «|» entre otros).

Para realizar la actualización de la configuración deberemos ir a la ruta http://localhost:8047/storage y hacer clic en el botón «Update» del plugin dfs.

storage

storage

Allí veremos un json utilizado para la configuración y buscaremos el elemento «csv» dentro del objeto «formats» y le añadiremos el atributo «skipFirstLine»: true como se muestra en la siguiente imagen y procederemos a actualizar el plugin pulsando el botón «Update».

configurando plugin dfs

configurando plugin dfs para que no tome en cuenta la primera linea del csv

Si intentamos de nuevo la consulta veremos como es obviada la cabecera del fichero csv, aunque aún no vemos el resultado como una tabla, para eso utilizaremos alias para identificar a cada columna al momento de efectuar la consulta de la siguiente forma:

SELECT columns[0] as ID, columns[1] as NOMBRE, columns[2] as APELLIDOS, columns[3] as PROVINCIA FROM dfs.ruta_fichero/clientes.csv

Obteniendo lo siguiente:

resultado de la consulta

resultado de la consulta

Vamos a profundizar aún más y ahora realizaremos un join entre los datos del fichero csv y del fichero json, ejecutando la siguiente consulta:

SELECT tablaCSV.columns[0] as ID, tablaCSV.columns[1] as NOMBRE, tablaCSV.columns[2] as APELLIDOS, tablaCSV.columns[3] as PROVINCIA, tablaJSON.BANCO FROM dfs.ruta_fichero/clientes.csv tablaCSV
LEFT JOIN dfs..ruta_fichero/cliente_banco.json tablaJSON
ON tablaCSV.columns[0] = tablaJSON.ID

Obteniendo:

resultado del left join

resultado del left join

Apache drill nos ofrece a su vez más posibilidades como por ejemplo crear tablas en formato parquet  a partir de un json o de un fichero csv. Apache drill es una herramienta que me gusta mucho pero también cuenta con algún aspecto a mejorar, por ejemplo me gustaría poder utilizar la cabecera de un fichero csv como nombre de columna al efectuar consultas y esto no funciona del todo bien, de hecho hice unas pruebas y fue así como me percate de este pequeño error que estoy seguro (y espero) se solucione pronto.

Para que la cabecera de un archivo csv sea tomada en cuenta como nombre de columna de una tabla es necesario modificar la configuración del plugin dfs, al igual que lo hicimos antes para que no tomase en cuenta la primera fila del archivo, editando el formato csv así como en la siguiente imagen.

config dfs plugin

configurando dfs plugin para que reconozca la cabecera de los ficheros csv

De nuevo repitamos la consulta que hacíamos al principio:

SELECT * FROM dfs.ruta_fichero/clientes.csv

Vemos como de inmediato sin haber utilizado alias en la consulta el resultado es devuelto como una tabla

Si ahora repetimos la consulta veremos como la salida de los resultados ha cambiado, dándonos una perspectiva de que tenemos una tabla, utilizando la cabecera del fichero csv como la cabecera de la tabla de resultados

consulta de todos los campos

consulta de todos los campos

Además al igual que con ejemplos anteriores podemos efectuar join con otras tablas independientemente en el formato o fuente que se encuentren (son, csv, parquet, mysql, etc…), el problema (o error) esta cuando intentamos consultar por un campo en especifico alguno puede que no devuelva nada como por ejemplo si efectuamos la siguiente consulta:

SELECT ID, NOMBRE FROM dfs.ruta_fichero/clientes.csv

consulta de ID y NOMBRE

consulta de ID y NOMBRE

 

Esto me pareció tan extraño que intente jugar con distintos parámetros de configuración e incluso con la forma de realizar la consulta y no pude solventar este comportamiento por lo que publiqué  este error en stackoverflow por si estaba haciendo algo mal y alguien podía echarme una mano.

No quiero que lo último los desanime a probar la herramienta ya que esta cuenta con muchas bondades que dan para redactar unas cuantas entradas más, espero que hayan podido seguir todos los ejemplos y tener una perspectiva de lo que podemos alcanzar con la herramienta.

ACTUALIZACIÓN 28-09-2016:

Al haber quedado con la inquietud del mal funcionamiento al ejecutar la consulta sobre el csv indicando como columnas la cabecera del fichero, me decidí a escribir a lista de usuarios de apache drill por si en dado caso me estaba topando con un bug (cosa extraña porque llevaría así al menos 3 releases) y ellos me han dado la respuesta, el problema estaba en el espacio en blanco inmediatamente después de la coma, por lo cual al reformular la consulta y hacerla de la siguiente manera funcionó a la perfección:

SELECT ID, ‘ NOMBRE’ FROM dfs.ruta_fichero/clientes.csv

Pero otra forma quizás más elegante aún es que se eliminase el espacio después de la coma en la cabecera del archivo csv, de esa manera  basta con que coloquemos los nombres de las columnas sin necesidad de encerrarlas entre comillas al momento de formular la consulta.

SELECT ID, NOMBRE FROM dfs.ruta_fichero/clientes.csv

Usando regex para formatear SQL

En algunas ocasiones llegue a leer  de regex, también lo estudie (muy poco) para sacar la certificación SCJP y en rara ocasión utilizarlo para alguna validación, aunque sin dudarlo donde mas pude conocer de su potencial fue conversando con colegas de profesión.

En esta oportunidad quiero compartir con ustedes una solución que pude dar a un problema, agilizando (mucho) mi trabajo y facilitando lo que tenia que hacer.

El problema: Un script para la inserción de un gran numero de registros (aproximadamente por el orden de los 16000) que afectaba a varias tablas. Dicho script era producto de una exportación de una BD oracle, la exportación le otorgo a los valores de las fechas un formato especial de la forma {d ‘yyyy-mm-dd’}. Por ejemplo {d ‘2010-09-14’}.

Al ejecutar los script arrojaba un error, ademas de que el formato de la fecha en mi BD local era de la forma ‘dd-mm-yyyy’, entre las soluciones que me aconsejaban era utilizar un editor al estilo notepad++, ultraedit y de esta manera poder eliminar las llaves y la letra d, pero esto no solucionaba totalmente el problema ya que aun estaba de por medio el formato en el que estaba representada la fecha. Fue en ese momento que pensé regex y de toda su potencia y aunque no estaba seguro de que pudiese ser la solución sentí que tenia que darle un vistazo en detalle a las características que podría darme.

Después de visitar algunos sitios web, pude dar con lo que necesitaba y me dispuse a implementar una solución (java) para mi problema, que de antemano se que puede hacerse mas elegante e incluso por que no decirlo, implementarse mejor, pero el entusiasmo que tengo en compartirlo con ustedes hace que no me dedique a refinar mi programa, pero lo que mas deseo que evitar que algún colega tenga que editar 10000 INSERT.

Aquí les dejo el código

Como podrán ver la funcionalidad de este programa consiste en recibir 2 argumentos y en cuyo caso que no los reciba arrojar un mensaje de error. El primer argumento es la ruta donde esta ubicado el fichero SQL y el segundo argumento es el nombre del nuevo fichero SQL que habrá sido formateado.

La clave: El patron regex utilizado.

String patternStr = «\\{[d]\\s\\'(\\d{4})-(\\d{2})-(\\d{2})\\’\\}+»;

Tiene las siguientes características:

  • \\{      busca existe una llave «{«.
  • [d]      seguida de la letra d.
  • \\s      seguido de un espacio en blanco.
  • \\’      busca el carácter «‘» (comilla simple)
  • (\\d{4})-(\\d{2})-(\\d{2})     seguido de 4 dígitos más «-» seguido de 2 dígitos mas «-» y por ultimo 2 dígitos. Como podrán notar los dígitos aparecen dentro de paréntesis, esta es una particularidad de regex que nos permite hacer grupos y esto resulto fundamental para poder resolver el formato de la fecha.
  • \\’    busca el carácter «‘» (comilla simple).
  • \\}    busca el carácter «}».
  • + Nos indica que al buscar este patrón, este puede aparecer en más de una oportunidad.

Por ultimo está la sentencia utilizada para llevar a cabo el reemplazo:

bos.write(line.replaceAll(patternStr, «‘$3-$2-$1′»)+»\n»);

Donde al hallar el patrón, es sustituido por el segundo parametro utilizado «‘$3-$2-$1′», aqui enlazamos con los grupos definidos, ya que eso es a lo que hacen referencia las variables $, donde por ejemplo $1 significa el primer grupo definido  en el patrón que se corresponde con el año, $2 el mes y $3 el día.

Como pudieron ver regex no solo ha de ser utilizado para llevar a cabo validaciones sino que además nos permite utilizarlo para llevar a cabo funciones de formateo y a en mi caso me ahorro editar a mano 15000 registros o en el mejor de los casos hacer un programa Java muy rebuscado trabajando con cadenas.