Save Pandas DataFrame into IRIS - a quick note

Keyword: Pandas DataFrame, IRIS, Python, JDBC

Purpose

Pandas DataFrame is popular tool for EDA (Exploratory Data Analysis). In ML tasks, the first thing we usually perform is to understand the data a bit more. Last week I was trying this Covid19 dataset in Kaggle. Basically the data is a spreadsheet of 1925 encounter rows with 231 columns, and the task is simply to predict whether a patient (linked to  1 or more encounter records) would be admitted to ICU. So it's a normal classification task, and we would as usual use padas.DataFrame to take a quick look first.  

We have IRIS IntegrtedML now, which has a sleek SQL wrapper around a powerful options of "AutoML(s)" , so frequently I'd like to save various stage of dataframes into IRIS database tables, then run IntegratedML upon them with comparisons to tradition ML algorithms.  However, since the dataframe.to_sql() is not not working with IRIS yet, it turned out actually I spent most of time fiddling with other data saving methods. It felt like you wanted to cook a nice omelette for an enjoyable Saturday morning breakfast but you turned out doing the gas and sink plumbings under the hob all the day. It's not perfect but I think I'd better record a brief note here in case it's gone after few weeks again.

Scope

 No, I didn't make dataframe.to_sql() work with IRIS, sorry we are not not there yet, however I did spent some time making a quick Python function as simple and raw as possible to dynamically save a dataframe into IRIS directly via JDBC (JayDeBeApi).  It might not be working with PyODBC yet due to a known issue ("MemoryError").

Environment

I am testing with this IntegratedML template via a simple docker-compose as a base for the following scripts. The GitHub repo includes an environment topology. I am using this JDBC Jupyter Notebook to connect into the IRIS container. 

Test

 1. Define a Python Function to emulate dataframe.to_sql()

I ran this in a Notebook cell:

def to_sql_iris(cursor, dataFrame, tableName, schemaName='SQLUser', drop_table=False ):
        """"
        Dynamically insert dataframe into an IRIS table via SQL by "excutemany" 
        
        Inputs:
            cursor:      Python JDBC or PyODBC cursor from a valid and establised DB connection
            dataFrame:   Pandas dataframe
            tablename:   IRIS SQL table to be created, inserted or apended
            schemaName:  IRIS schemaName, default to "SQLUser"
            drop_table:  If the table already exsits, drop it and re-create it if True; othrewise keep it and appen 
        Output:
            True is successful; False if there is any exception.
        """
        if drop_table:   
            try:                 
                curs.execute("DROP TABLE %s.%s" %(schemaName, tableName))
            except Exception:
                pass
        
        try: 
            dataFrame.columns = dataFrame.columns.str.replace("[() -]", "_")
            curs.execute(pd.io.sql.get_schema(dataFrame, tableName))
        except Exception:
            pass
        
        curs.fast_executemany = True
        cols = ", ".join([str(i) for i in dataFrame.columns.tolist()])
        wildc =''.join('?, ' * len(dataFrame.columns))
        wildc = '(' + wildc[:-2] + ')'
        sql = "INSERT INTO " + tableName + " ( " + cols.replace('-', '_') + " ) VALUES" + wildc
        #print(sql)
        curs.executemany(sql, list(dataFrame.itertuples(index=False, name=None)) )
        return True

Basically it is trying to dynamically insert a dataframe into an IRIS table. If the table already exists, then the full dataframe will be appended at its end, otherwise a new table will be created based on the dimensions(column names and data types) of the dataframe and its full content will be inserted.  It is simply using  "executemany" method.

2. Test - Read the raw datafile into dataframe

Run the following in Notebook, to load the raw data from local drive into a dataframe. The raw data can be simply downloaded from this Kaggle site.

import numpy as np 
import pandas as pd 
from sklearn.impute import SimpleImputer
import matplotlib.pyplot as plt
from sklearn.linear_model import LogisticRegression
from sklearn.model_selection import train_test_split
from sklearn.metrics import classification_report, roc_auc_score, roc_curve
import seaborn as sns
sns.set(style="whitegrid")

import os
for dirname, _, filenames in os.walk('./input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))
        
df = pd.read_excel("./input/raw_data_kaggle_covid_icu.xlsx")

df

3. Test - Connect into IRIS DB via Python over JDBC:

import jaydebeapi
url = "jdbc:IRIS://irisimlsvr:51773/USER" 
driver = 'com.intersystems.jdbc.IRISDriver'
user = "SUPERUSER"
password = "SYS"
jarfile = "./intersystems-jdbc-3.1.0.jar"

conn = jaydebeapi.connect(driver, url, [user, password], jarfile)
curs = conn.cursor()

4. Test - Save the dataframe into an IRIS table

iris_schema = 'SQLUser'
iris_table = 'Covid19RawTableFromCSV'

to_sql_iris(curs, df, iris_table, iris_schema, drop_table=True)   # save it into a new IRIS table of specified name
#to_sql_iris(curs, df, iris_table)  # append dataframe to an exsiting IRIS table

Out[4]: True

import pandas as pd
from IPython.display import display

df2 = pd.read_sql("SELECT COUNT(*) from %s.%s" %(iris_schema, iris_table),conn)
display(df2)
   

So the full data is inserted into an IRIS table called "Covid19RawTableFromCSV". If logging into IRIS Management Portal, we should see the table too with records.

5. Test - run a bit benchmark:

Let's insert this dataframe e.g. 10x times, to see how long it took in this basic CE docker  with single JDBC session:

from tqdm import tqdm
import pandas as pd
import time
from IPython.display import display

start = time.clock() 
for i in tqdm(range(0, 10)):
    to_sql_iris(curs, df, iris_table)
    
print("Total time elasped: ", time.clock()-start, " for importing total records:")
df2 = pd.read_sql("SELECT COUNT(*) from %s.%s" %(iris_schema, iris_table),conn)
display(df2)

100%|██████████| 10/10 [00:14<00:00,  1.42s/it]

Total time elasped:  12.612431999999998  for importing total records:

That's it, although very basic, at least I now can save whatever manipulated dataframes along the data analysis pipline, and able to invoke IntegratedML via its SQL interface  on them now, for a bit real ML attempts.  

Some caveats to be mentioned here:  The string in DataFrame is sometimes interpreted as "object", which needs to be converted to string before being inserted into IRIS table, something like  df['column'].astype(str) for example.  "DROP TABLE" is used for overwriting previous tables. "DROP VIEW" can be used to delete previous views.