/** * Example using the Wine dataset from UCI * https://archive.ics.uci.edu/ml/datasets/Wine * * The data was splited in two sets. One with 70% of the data and another with 30%. * The first set is in the file examples/wine/dataset/wine_70.data, this set will be * used to train our model to classify new data * The second set is in the file examples/wine/dataset/wine_30.data, this simulate new * data that we will store in our table. * * We will create a table and store the data used to train the model in the * examples/wine/model/train_wine_naive_bayes.py script. * After that, we will save our model in the directory in examples/wine/model/wine_naive_bayes.joblib * * With our trained model, we will create a trigger to classify new data and * store the classification result in the 'wine_class' column. */ -- Create the table where we will store our data CREATE TABLE wine ( id SERIAL PRIMARY KEY, wine_class INT, -- where we will insert the classification result alcohol FLOAT, malic_acid FLOAT, ash FLOAT, alcalinity FLOAT, magnesium FLOAT, phenols FLOAT, flavanoids FLOAT, nonflavanoid FLOAT, proanthocyanins FLOAT, color FLOAT, huue FLOAT, od280 FLOAT, proline float ); -- Populate the table with the data used to train our model COPY wine( wine_class, alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, huue, od280, proline ) FROM '/home/vagrant/examples/wine/dataset/wine_70.data' DELIMITER ','; -- You can run the example/wine/models/train_wine_naive_bayes.py to train and save your model -- The model will use the values in the column 'wine_class' as target -- If you want to see the results of your model over your stored data you can use the predict_table_row function SELECT * FROM predict_table_row( '/home/vagrant/examples/wine/models/wine_naive_bayes.joblib', -- The trained model 'wine', -- Table with the data '{"alcohol", "malic_acid", "ash", "alcalinity", "magnesium", "phenols", "flavanoids", "nonflavanoid", "proanthocyanins", "color", "hue", "od280", "proline"}', -- The columns used as feature 1 -- The ID of your data ); -- After trained you can create a trigger to populate the new data that you insert into the table -- You can use the function classification_trigger to create a trigger to apply your model over your data -- and save it in a specifc column. This columns must exists in the same table that the trigger are acting. CREATE TRIGGER classify_wine BEFORE INSERT OR UPDATE ON "wine" FOR EACH ROW EXECUTE PROCEDURE classification_trigger( '/home/vagrant/examples/wine/models/wine_naive_bayes.joblib', -- Model 'wine_class', -- Column where you will store the result of the classification 'alcohol', -- Features 'malic_acid', 'ash', 'alcalinity', 'magnesium', 'phenols', 'flavanoids', 'nonflavanoid', 'proanthocyanins', 'color', 'hue', 'od280', 'proline' ); -- With the trigger created you can insert a new row in your table -- Notice that the following insert doesn't insert any value to 'wine_class' column INSERT INTO wine ( alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (14.83,1.64,2.17,14,97,2.8,2.98,.29,1.98,5.2,1.08,2.85,1045); -- Then you can check the newly inserted data -- If everything works, the column 'wine_class' will be populated SELECT * FROM wine WHERE id = (SELECT MAX(id) FROM wine); /** -- If you want to insert the data available in the file examples/wine/dataset/wine_30.data in your database to test the new trigger you can use the following commands. INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (14.83,1.64,2.17,14,97,2.8,2.98,.29,1.98,5.2,1.08,2.85,1045); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.33,1.1,2.28,16,101,2.05,1.09,.63,.41,3.27,1.25,1.67,680); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.86,1.35,2.27,16,98,2.98,3.15,.22,1.85,7.22,1.01,3.55,1045); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.85,3.27,2.58,22,106,1.65,.6,.6,.96,5.58,.87,2.11,570); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.07,1.5,2.1,15.5,98,2.4,2.64,.28,1.37,3.7,1.18,2.69,1020); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.42,1.61,2.19,22.5,108,2,2.09,.34,1.61,2.06,1.06,2.96,345); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (14.19,1.59,2.48,16.5,108,3.3,3.93,.32,1.86,8.7,1.23,2.82,1680); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.64,1.36,2.02,16.8,100,2.02,1.41,.53,.62,5.75,.98,1.59,450); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (14.1,2.02,2.4,18.8,103,2.75,2.92,.32,2.38,6.2,1.07,2.75,1060); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.74,1.67,2.25,16.4,118,2.6,2.9,.21,1.62,5.85,.92,3.2,1060); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.86,1.35,2.32,18,122,1.51,1.25,.21,.94,4.1,.76,1.29,630); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.2,3.03,2.32,19,96,1.25,.49,.4,.73,5.5,.66,1.83,510); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.7,3.55,2.36,21.5,106,1.7,1.2,.17,.84,5,.78,1.29,600); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (14.38,1.87,2.38,12,102,3.3,3.64,.29,2.96,7.5,1.2,3,1547); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.51,1.73,1.98,20.5,85,2.2,1.92,.32,1.48,2.94,1.04,3.57,672); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.27,4.28,2.26,20,120,1.59,.69,.43,1.35,10.2,.59,1.56,835); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.56,1.71,2.31,16.2,117,3.15,3.29,.34,2.34,6.13,.95,3.38,795); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.34,2.45,2.46,21,98,2.56,2.11,.34,1.31,2.8,.8,3.38,438); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.37,1.17,1.92,19.6,78,2.11,2,.27,1.04,4.68,1.12,3.48,510); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (11.64,2.06,2.46,21.6,84,1.95,1.69,.48,1.35,2.8,1,2.75,680); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.72,1.81,2.2,18.8,86,2.2,2.53,.26,1.77,3.9,1.16,3.14,714); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.37,1.13,2.16,19,87,3.5,3.1,.19,1.87,4.45,1.22,2.87,420); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (11.82,1.72,1.88,19.5,86,2.5,1.64,.37,1.42,2.06,.94,2.44,415); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.08,3.9,2.36,21.5,113,1.41,1.39,.34,1.14,9.40,.57,1.33,550); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.68,1.83,2.36,17.2,104,2.42,2.69,.42,1.97,3.84,1.23,2.87,990); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.21,1.19,1.75,16.8,151,1.85,1.28,.14,2.5,2.85,1.28,3.07,718); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.51,1.24,2.25,17.5,85,2,.58,.6,1.25,5.45,.75,1.51,650); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.29,3.17,2.21,18,88,2.85,2.99,.45,2.81,2.3,1.42,2.83,406); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.58,1.29,2.1,20,103,1.48,.58,.53,1.4,7.6,.58,1.55,640); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.83,1.65,2.6,17.2,94,2.45,2.99,.22,2.29,5.6,1.24,3.37,1265); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.77,1.9,2.68,17.1,115,3,2.79,.39,1.68,6.3,1.13,2.93,1375); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.88,5.04,2.23,20,80,.98,.34,.4,.68,4.9,.58,1.33,415); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.23,3.3,2.28,18.5,98,1.8,.83,.61,1.87,10.52,.56,1.51,675); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (14.3,1.92,2.72,20,120,2.8,3.14,.33,1.97,6.2,1.07,2.65,1280); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.81,2.31,2.4,24,98,1.15,1.09,.27,.83,5.7,.66,1.36,560); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.08,1.39,2.5,22.5,84,2.56,2.29,.43,1.04,2.9,.93,3.19,385); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (14.22,3.99,2.51,13.2,128,3,3.04,.2,2.08,5.1,.89,3.53,760); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (11.03,1.51,2.2,21.5,85,2.46,2.17,.52,2.01,1.9,1.71,2.87,407); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.29,2.83,2.22,18,88,2.45,2.25,.25,1.99,2.15,1.15,3.3,290); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (14.2,1.76,2.45,15.2,112,3.27,3.39,.34,1.97,6.75,1.05,2.85,1450); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.05,3.86,2.32,22.5,85,1.65,1.59,.61,1.62,4.8,.84,2.01,515); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (14.38,3.59,2.28,16,102,3.25,3.17,.27,2.19,4.9,1.04,3.44,1065); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (14.75,1.73,2.39,11.4,91,3.1,3.69,.43,2.81,5.4,1.25,2.73,1150); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.71,1.86,2.36,16.6,101,2.61,2.88,.27,1.69,3.8,1.11,4,1035); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.69,3.26,2.54,20,107,1.83,.56,.5,.8,5.88,.96,1.82,680); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.11,1.01,1.7,15,78,2.98,3.18,.26,2.28,5.3,1.12,3.18,502); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (14.16,2.51,2.48,20,91,1.68,.7,.44,1.24,9.7,.62,1.71,660); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.58,1.66,2.36,19.1,106,2.86,3.19,.22,1.95,6.9,1.09,2.88,1515); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (12.37,1.63,2.3,24.5,88,2.22,2.45,.4,1.9,2.12,.89,2.78,342); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.05,1.77,2.1,17,107,3,3,.28,2.03,5.04,.88,3.35,885); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.76,1.53,2.7,19.5,132,2.95,2.74,.5,1.35,5.4,1.25,3,1235); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (11.84,2.89,2.23,18,112,1.72,1.32,.43,.95,2.65,.96,2.52,500); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.41,3.84,2.12,18.8,90,2.45,2.68,.27,1.48,4.28,.91,3,1035); INSERT INTO wine (alcohol, malic_acid, ash, alcalinity, magnesium, phenols, flavanoids, nonflavanoid, proanthocyanins, color, hue, od280, proline) VALUES (13.75,1.73,2.41,16,89,2.6,2.76,.29,1.81,5.6,1.15,2.9,1320); */