Conexión con JDBC desde Python a la base de datos de IRIS - una nota rápida

Palabras clave: Python, JDBC, SQL, IRIS, Jupyter Notebook, Pandas, Numpy y aprendizaje automático 

Hoy me he encontrado con este artículo de Zphong Li, que publicó en Enero de 2020 pero que creo que es muy interesante y aún útil a día de hoy. Así que... para los que estéis haciendo vuestros primeros pinitos en Machine Learning con InterSystems IRIS, Python y Jupyter... aquí lo tenéis!!

1. Objetivo

Esta es una nota sencilla de 5 minutos, donde os muestro cómo invocar el controlador JDBC de IRIS con la ayuda de Python 3, por ejemplo desde un Jupyter Notebook, para leer y escribir datos en una instancia de la base de datos de IRIS vía SQL.

El año pasado Zhong Li publicó una nota breve sobre como Enlazar Python con una base de datos Caché (sección 4.7). Ahora podría ser el momento de recapitular algunas opciones y discusiones sobre el uso de Python para acceder a una base de datos de IRIS, para leer sus datos en un dataframe de Pandas y una matriz de NumPy para realizar un análisis básico, y después escribir algunos datos pre-procesados o normalizados de nuevo en IRIS y que esté listo para canalizaciones (pipelines) adicionales de ML/DL.

Inmediatamente se me ocurren varias opciones:

1.    ODBC: ¿Cómo hacerlo con PyODBC para Python 3 y SQL nativo?
2.    JDBC: ¿Cómo hacerlo con JayDeBeApi para Python 3 y SQL nativo?
3.    Spark:  ¿Cómo hacerlo con PySpark y SQL?
4.    API nativa de Python para IRIS: ¿Qué hay más allá del anterior Python Binding para Caché?
5.    ¿IPtyhon Magic SQL %%sql? ¿Podría  funcionar con IRIS?

 ¿Hay alguna otra opción que se me haya escapado? También estoy interesado en probarla. 

2. Alcance 

¿Comenzamos con un enfoque JDBC común? En la siguiente nota breve recapitularemos ODBC, Spark y la API nativa de Python. 

En el alcance:

Los siguientes componentes comunes se abordan en esta demostración rápida:

Fuera del alcance:

En esta nota rápida NO se abordarán los siguientes aspectos - son importantes y pueden tratarse por separado con soluciones, implementaciones y servicios específicos: 

3. Demostración

3.1 Ejecución de una instancia de IRIS:

Simplemente ejecuté un contenedor IRIS 2019.4 como servidor "remoto" de la base de datos. Puedes utilizar cualquier instancia de IRIS a la que tengas acceso autorizado.

zhongli@UKM5530ZHONGLI MINGW64 /c/Program Files/Docker Toolbox
$ docker ps
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS                PORTS                                              NAMES
d86be69a03ab        quickml-demo        "/iris-main"        3 days ago          Up 3 days (healthy)   0.0.0.0:9091->51773/tcp, 0.0.0.0:9092->52773/tcp   quickml

3.2 Anaconda y Jupyter Notebook:

Reutilizaremos el mismo enfoque de configuración descrito aquí para Anaconda (sección 4.1) y aquí para Jupyter Notebook (sección 4) en un ordenador portátil. Python 3.x se instala junto con este paso.

3.3 Instalar JayDeBeApi y JPyPe:

JayDeBeApi utiliza JPype 0.7 en el momento de escribir este artículo (enero del 2020), pero no funciona debido a un error conocido, por lo que tuve que utilizar la 0.6.3

3.4 Conectar a una base de datos de IRIS por medio de JDBC 

Hay una documentación oficial de JDBC en IRIS aquí. 

Para las ejecuciones de Python SQL sobre JDBC, utilicé los siguientes códigos como ejemplo. Se conecta a una tabla de datos llamada "DataMining.IrisDataset" dentro del namespace "USER" de esta instancia de IRIS. 

### 1. Establezca las variables de entorno, si es necesario
#importa os
#os.environ['JAVA_HOME']='C:\Progra~1\Java\jdk1.8.0_241'
#os.environ['CLASSPATH'] = 'C:\interSystems\IRIS20194\dev\java\lib\JDK18\intersystems-jdbc-3.0.0.jar'
#os.environ['HADOOP_HOME']='C:\hadoop\bin'  
#winutil binary must be in Hadoop's Home

### 2. Obtiene la conexión JDBC y el cursor
import JayDeBeApi 
url = "jdbc:IRIS://192.168.99.101:9091/USER"
driver = 'com.intersystems.jdbc.IRISDriver'
user = "SUPERUSER"
password = "SYS"
#libx = "C:/InterSystems/IRIS20194/dev/java/lib/JDK18"
jarfile = "C:/InterSystems/IRIS20194/dev/java/lib/JDK18/intersystems-jdbc-3.0.0.jar"
conn = jaydebeapi.connect(driver, url, [user, password], jarfile)
curs = conn.cursor()

### 3. Especifica la fuente de la tabla de datos
dataTable = "DataMining.IrisDataset"

### 4. Obtiene el resultado y visualiza
curs.execute("select TOP 20 * from %s" % dataTable)
result = curs.fetchall()
print("Total records: " + str(len(result)))
for i in range(len(result)):
    print(result[i])

### 5. Cerrar y limpiar - Los mantendre abiertos para los próximos accesos.
#curs.close()
#conn.close()

Total records: 150
(1, 1.4, 0.2, 5.1, 3.5, 'Iris-setosa')
(2, 1.4, 0.2, 4.9, 3.0, 'Iris-setosa')
(3, 1.3, 0.2, 4.7, 3.2, 'Iris-setosa')
... ...
(49, 1.5, 0.2, 5.3, 3.7, 'Iris-setosa')
(50, 1.4, 0.2, 5.0, 3.3, 'Iris-setosa')
(51, 4.7, 1.4, 7.0, 3.2, 'Iris-versicolor')
... ... 
(145, 5.7, 2.5, 6.7, 3.3, 'Iris-virginica')
... ... 
(148, 5.2, 2.0, 6.5, 3.0, 'Iris-virginica')
(149, 5.4, 2.3, 6.2, 3.4, 'Iris-virginica')
(150, 5.1, 1.8, 5.9, 3.0, 'Iris-virginica')

 
Ahora hemos verificado que Python en JDBC estaba funcionando. Lo siguiente es solo un poco de análisis de datos de rutina y preprocesamiento para los canales habituales de ML que deberíamos mencionar una y otra vez para demostraciones y comparaciones posteriores, por lo que se adjunta para mayor comodidad. 

3.5 Convertir los resultados de SQL al DataFrame de Pandas y después a las matrices de NumPy

Instala los paquetes Pandas y NumPy a través de Conda si aún no están instalados, como se explicó en el punto 3.3.

A continuación, ejecute lo siguiente como un ejemplo:

### Transforma los resultados de SQL "sqlData" en un dataframe de Pandas "df", y después en una matriz de NumPy "arrayN" para otras canalizaciones (*pipelines*) de ML
import pandas as pd
sqlData = "SELECT * from DataMining.IrisDataset"
df= pd.io.sql.read_sql(sqlData, conn)
df = df.drop('ID', 1)
df = df[['SepalLength', 'SepalWidth', 'PetalLength', 'PetalWidth', 'Species']]

# Establece las etiquetas en 0, 1, 2, para la matriz NumPy
df.replace('Iris-setosa', 0, inplace=True)
df.replace('Iris-versicolor', 1, inplace=True)
df.replace('Iris-virginica', 2, inplace=True)

# Convierte el dataframe en una matriz Numpy
arrayN = df.to_numpy()

### 6. Cierra y limpia - ¿si la conexión ya no es necesaria?
#curs.close()
#conn.close()

Echemos un vistazo rutinario a los datos actuales:

df.head(5)

df.describe()

Ahora tenemos un DataFrame, y una matriz NumPy normalizada de una tabla de datos fuente a nuestra disposición.  

Ciertamente, ¿podemos probar varios análisis de rutina con los que comenzaría un experto en ML, como se indica a continuación, en Python para reemplazar a R, como en el enlace que se encuentra aquí?

La fuente de datos se cita aquí

3.6 Dividir los datos y volver a escribirlos en la base de datos de IRIS por medio de SQL:

Ciertamente, podemos dividir los datos en un conjunto de Entrenamiento y otro de Validación o Prueba, como es habitual, y después escribirlos de nuevo en tablas temporales de la base de datos, para algunas emocionantes funciones ML de IRIS:

import numpy as np
from matplotlib
import pyplot 
from sklearn.model_selection import train_test_split

# mantiene, por ejemplo, el 20% = 30 filas como datos de prueba, y el 80% = 120 filas para entrenamiento
X = arrayN[:,0:4]
y = arrayN[:,4]
X_train, X_validation, Y_train, Y_validation = train_test_split(X, y, test_size=0.20, random_state=1, shuffle=True)

# Hace que el 80% de las filas, escogidas aleatoriamente, estén en el conjunto Entrenamiento
labels1 = np.reshape(Y_train,(120,1))
train = np.concatenate([X_train, labels1],axis=-1)

# Hace que el 20% de las filas, escogidas eleatoriamente, estén en el conjunto Prueba,
lTest1 = np.reshape(Y_validation,(30,1))
test = np.concatenate([X_validation, lTest1],axis=-1)

# Escribe el conjunto de datos de entrenamiento en un dataframe de Pandas
dfTrain = pd.DataFrame({'SepalLength':train[:, 0], 'SepalWidth':train[:, 1], 'PetalLength':train[:, 2], 'PetalWidth':train[:, 3], 'Species':train[:, 4]})
dfTrain['Species'].replace(0, 'Iris-setosa', inplace=True)
dfTrain['Species'].replace(1, 'Iris-versicolor', inplace=True)
dfTrain['Species'].replace(2, 'Iris-virginica', inplace=True)

# Escribe los datos de pruebas en un dataframe de Pandas
dfTest = pd.DataFrame({'SepalLength':test[:, 0], 'SepalWidth':test[:, 1], 'PetalLength':test[:, 2], 'PetalWidth':test[:, 3], 'Species':test[:, 4]})
dfTest['Species'].replace(0, 'Iris-setosa', inplace=True)
dfTest['Species'].replace(1, 'Iris-versicolor', inplace=True)
dfTest['Species'].replace(2, 'Iris-virginica', inplace=True)

### 3. Especifica los nombres de las tablas temporales
dtTrain = "TRAIN02"
dtTest = "TEST02"

### 4. Crea 2 tablas temporales - puedes probar eliminar las tablas temporales y volver a crearlas una y otra vez
curs.execute("Create Table %s (%s DOUBLE, %s DOUBLE, %s DOUBLE, %s DOUBLE, %s VARCHAR(100))" %(dtTrain,dfTrain.columns[0],dfTrain.columns[1],dfTrain.columns[2], dfTrain.columns[3], dfTrain.columns[4]))
curs.execute("Create Table %s (%s DOUBLE, %s DOUBLE, %s DOUBLE, %s DOUBLE, %s VARCHAR(100))" %(dtTest,dfTest.columns[0],dfTest.columns[1],dfTest.columns[2],dfTest.columns[3],dfTest.columns[4]))

 
### 5. Escribe el conjunto de Entrenamiento y el conjunto de Prueba en las tablas. Se puede intentar borrar cada vez el registro anterior y luego insertarlo otra vez.
curs.fast_executemany = True
curs.executemany( "INSERT INTO %s (SepalLength, SepalWidth, PetalLength, PetalWidth, Species) VALUES (?, ?, ?, ? ,?)" % dtTrain, list(dfTrain.itertuples(index=False, name=None)) )
curs.executemany( "INSERT INTO %s (SepalLength, SepalWidth, PetalLength, PetalWidth, Species) VALUES (?, ?, ?, ? ,?)" % dtTest, list(dfTest.itertuples(index=False, name=None)) )

### 6. Cierra y limpia - ¿si la conexión ya no es necesaria?
#curs.close()
#conn.close()

Ahora, si cambiamos a la Consola de Administración de IRIS, o a la Consola del Terminal SQL, deberíamos ver 2 tablas temporales creadas: TRAIN02 con 120 filas y TEST02 con 30 filas.

Tendré que detenerme aquí, ya que supuestamente este artículo es una nota rápida muy breve.

4. Advertencias

• El contenido anterior puede ser modificado o perfeccionado. 

5. Siguiente

Simplemente reemplazaremos las secciones 3.3 y 3.4 con PyODBC, PySPark y la API nativa de Python para IRIS, a menos que a alguien no le importe contribuir con una nota rápida - también lo agradeceré.