<jupyter_start><jupyter_text>Storing Machine Learning Run Metadata Using sqliteAuthor: <NAME>Last Updated: 05/07/2019This notebook walks through the creation of a flexible relational model that can be used to store metadata related to a given machine learning train/deployment run.  The relational model is then implemented in sqlite using a parallel ETL approach where data is stored in memory for on-demand processing needs downstream during training/deployment runs and archived on disk for reproducibility/audit trail purposes. Storing as much detail as possible about a given machine learning model run is necessary for model relevancy, metric tracking, and model assessment. Other pros/cons of this implementation technique are also explained.  Import Libraries<jupyter_code>import sqlalchemy
import sqlite3
from sqlite3 import Error
import pandas as pd
import numpy as np
from functools import partial
np.random.seed(0)
from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestClassifier
from sklearn.model_selection import GridSearchCV
from sklearn.datasets import make_classification
from sklearn.externals import joblib
from datetime import datetime
import time
import os
import zipfile
def create_connection():
    """ create a database connection to a SQLite database """
    try:
        conn = sqlite3.connect(':memory:')
        print(sqlite3.version)
    except Error as e:
        print(e)
    finally:
        conn.close()<jupyter_output><empty_output><jupyter_text>Load data<jupyter_code>features, target = make_classification(n_samples=1000, n_features=15, n_informative=6, n_classes=20)
df = pd.concat([pd.DataFrame(features), pd.Series(target,name='target')], axis=1)
df.head()<jupyter_output><empty_output><jupyter_text>Split into train, test set<jupyter_code>X_train, X_test, y_train, y_test = train_test_split(df.drop('target',axis=1), df['target'], test_size=0.2)<jupyter_output><empty_output><jupyter_text>Store current time as `str`We'll use this later in a variety of ways.<jupyter_code>now = datetime.now()
now = now.strftime("%Y%m%d%H%M")
now<jupyter_output><empty_output><jupyter_text>Train model and tune hyper parameters using `GridSearchCV`<jupyter_code># Create a random forest Classifier. By convention, clf means 'Classifier'
clf = RandomForestClassifier(random_state=0)

# Train the Classifier to take the training features and learn how they relate
# to the training y (the species)
clf.fit(X_train, y_train)
train_untuned_accuracy = clf.score(X_test, y_test)
print('rfc untuned accuracy: {}'.format(train_untuned_accuracy))

param_grid = { 
    'n_estimators': [20, 40],
    'max_features': ['auto', 'log2'],
    'max_depth': [10,20]
}


t = time.asctime( time.localtime(time.time()) )
CV_rfc = GridSearchCV(estimator=clf, param_grid=param_grid, cv=10)
CV_rfc.fit(X_train, y_train)
print(CV_rfc.best_params_)
CV_rfc.refit
e = time.asctime( time.localtime(time.time()) )
train_tuned_accuracy = CV_rfc.score(X_test, y_test)
print('tuned accuracy: {}'.format(train_tuned_accuracy))<jupyter_output>rfc untuned accuracy: 0.295
{'max_depth': 20, 'max_features': 'auto', 'n_estimators': 40}
tuned accuracy: 0.315<jupyter_text>`SQLite` classThe `SQLite` class is used to create the sqlite database and execute queries.Under the hood, it uses `pandas` and `sqlite3` python libraries.<jupyter_code>class SQLite:

    def __init__(self, db=None):
        """
        
        """
        if db:
            assert isinstance(db, str)
            assert db.split('.')[1] == 'db'
        self.create_connection(db)
    
    
    def create_connection(self, db=None):
        """ create a database connection to a SQLite database """
        try:
            if db:
                self.conn = sqlite3.connect(db)
                print(sqlite3.version)
            else:
                self.conn = sqlite3.connect(':memory:')
                print(sqlite3.version)
        except Error as e:
            print(e)
    
    def close_conn(self):
        self.conn.close()
            
def query_sqlite_db(conn, query):
    """
    
    """
    try:
        cur = conn.cursor()    
        cur.execute(query)
    except Error as e:
        print(e)
    finally:
        cur.close()<jupyter_output><empty_output><jupyter_text>Model metadata<jupyter_code>create_sql = """CREATE TABLE Model(id INTEGER PRIMARY KEY, name TEXT, type TEXT, start_dt FLOAT, end_dt FLOAT)"""
insert_sql = """INSERT INTO Model VALUES({},'{}','{}','{}','{}')""".format(now, type(CV_rfc.estimator).__name__ ,str(type(CV_rfc.estimator))[8:-2],t,e)

s = SQLite()
query_sqlite_db(s.conn, create_sql)
query_sqlite_db(s.conn, insert_sql)
df = pd.read_sql_query('select * from Model',s.conn)
df.head()<jupyter_output><empty_output><jupyter_text>Extending the concept to .pkl filesNow let's extend the concept from above to include .pkl files generated during the machine learning lifecycle. We'll be using the `cv_results_` attribute of the `GridSearchCV` object to illustrate.<jupyter_code>CV_rfc.best_estimator_
import pickle

# reference: https://stackoverflow.com/a/2340858

pdata0 = pickle.dumps(CV_rfc.best_params_, pickle.HIGHEST_PROTOCOL)
pdata1 = pickle.dumps(CV_rfc.cv_results_['params'], pickle.HIGHEST_PROTOCOL)
pdata2 = pickle.dumps(CV_rfc.cv_results_['mean_test_score'], pickle.HIGHEST_PROTOCOL)
pdata3 = pickle.dumps(CV_rfc.cv_results_['mean_train_score'], pickle.HIGHEST_PROTOCOL)
pdata4 = pickle.dumps(CV_rfc.cv_results_['mean_fit_time'], pickle.HIGHEST_PROTOCOL)
pdata5 = pickle.dumps(CV_rfc.cv_results_['mean_score_time'], pickle.HIGHEST_PROTOCOL)
pdata6 = pickle.dumps(CV_rfc.best_estimator_, pickle.HIGHEST_PROTOCOL)
create_ModelGeneral_sql = """CREATE TABLE ModelTrainCV(id INTEGER PRIMARY KEY, name TEXT, type TEXT, start_dt TEXT, end_dt TEXT, optimal_model_params BLOB, all_models_params BLOB, all_models_test_scores BLOB, all_models_train_scores BLOB, all_models_fit_time_secs BLOB, all_models_score_time_secs BLOB, optimal_model BLOB)"""
curr = s.conn.cursor()
curr.execute(create_ModelGeneral_sql)
curr.execute("INSERT INTO ModelTrainCV VALUES (?,?,?,?,?,?,?,?,?,?,?,?)",(now, type(CV_rfc.estimator).__name__, str(type(CV_rfc.estimator))[8:-2], t, e, sqlite3.Binary(pdata0), sqlite3.Binary(pdata1), sqlite3.Binary(pdata2), sqlite3.Binary(pdata3), sqlite3.Binary(pdata4), sqlite3.Binary(pdata5), sqlite3.Binary(pdata6)))
df = pd.read_sql_query('select * from ModelTrainCV',s.conn)
df.head()
model = pickle.loads(df['optimal_model'][0])
assert np.allclose(model.predict(X_test), CV_rfc.predict(X_test))
print('success!')<jupyter_output>success!<jupyter_text>Write in memory database to disk<jupyter_code>s.conn.commit()

# write database to disk

c2 = sqlite3.connect('mydb.db')
with c2:
    for line in s.conn.iterdump():
        if line not in ('BEGIN;', 'COMMIT;'): # let python handle the transactions
            c2.execute(line)
c2.commit()
s.close_conn()
c2.close()<jupyter_output><empty_output><jupyter_text>Zip and remove<jupyter_code>def zip_database(zip_file_path, sqlite_db_file_path):
    """Function to dump sqlite3 database into zipped file
    see: http://bit.ly/IAUmKc
    python 2.6+ only
    connection: sqlite3 database connection
    dumpfile: the name of the zipfile to store the sql_file info 
              [remember to add a .zip extension]
    sql_file: The name of the sql file to dump the database info into
              [remember to add a .sql extension]
    """
    
    zf = zipfile.ZipFile(zip_file_path, mode='w', compression = zipfile.ZIP_DEFLATED)

    # Create a zip file and write add the dump into it as a new file
    zf.write(os.getcwd()+'\\'+sqlite_db_file_path)
    zf.close()
zip_database('test2.zip', 'mydb.db')
if os.path.isfile(os.getcwd()+'\\'+'mydb.db'):
    os.remove(os.getcwd()+'\\'+'mydb.db')
else:    ## Show an error ##
    print("Error: %s file not found" % os.getcwd()+'\\'+'mydb.db')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11698637051993943, 'fraction_numerical': 0.012619888944977285, 'mean_word_length': 4.805860805860806, 'pattern_counts': {'":': 0, '<': 40, '<?xml version=': 0, '>': 40, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '135817', 'license': 'Unlicense', 'n_tokens_mistral': 2762, 'n_tokens_neox': 2687, 'n_words': 654, 'path': 'Storing Machine Learning Run Metadata Using sqlite.ipynb', 'repo_name': 'tjefferies/courses', 'size': '7934'}