This package implements data processing, training and scoring of ML models. It is built to be used

inside PL/Python functions, inside a Postgres connection process.

Most functions can work in any Python environment; some functions are tightly coupled to

PL/Python by requiring a`plpy` object passed in as an argument: it provides database access methods.

For now, we require that this package is installed into the system Python packages space:

sudo pip3 setup.py install

See`../sql/install.sql` for list of functions. This package is not meant to be used directly.

CREATE EXTENSION IF NOT EXISTS plpython3u;

DROPSCHEMA pgml CASCADE;

CREATESCHEMAIF NOT EXISTS pgml;

CREATE OR REPLACEFUNCTIONpgml_version()

CREATE OR REPLACEFUNCTIONpgml.version()

RETURNSTEXT

AS $$

import pgml

CREATESCHEMAIF NOT EXISTS pgml;

CREATETABLEpgml.model_versions(

idBIGSERIALPRIMARY KEY,

nameVARCHAR,

CREATE OR REPLACEFUNCTIONpgml_validate(table_nameTEXT)

CREATE OR REPLACEFUNCTIONpgml.validate(table_nameTEXT)

RETURNS BOOL

AS $$

frompgml.sql import all_rows

CREATE OR REPLACEFUNCTIONpgml_train(table_nameTEXT, yTEXT)

CREATE OR REPLACEFUNCTIONpgml.train(table_nameTEXT, yTEXT)

RETURNSTEXT

AS $$

frompgml.train import train

DROPFUNCTION pgml_score(model_nameTEXT, VARIADIC featuresDOUBLE PRECISION[]);

CREATE OR REPLACEFUNCTIONpgml_score(model_nameTEXT, VARIADIC featuresDOUBLE PRECISION[])

CREATE OR REPLACEFUNCTIONpgml.score(model_nameTEXT, VARIADIC featuresDOUBLE PRECISION[])

RETURNSDOUBLE PRECISION

AS $$

frompgml.sql import models_directory

\i sql/install.sql

\i data/winequality-red.sql

SELECTpgml_version();

SELECTpgml.version();

SELECTpgml_validate('wine_quality_red');

SELECTpgml.validate('wine_quality_red');

SELECTpgml_train('wine_quality_red','quality');

SELECTpgml.train('wine_quality_red','quality');

SELECT*FROMpgml.model_versions;

\timing

WITH latest_modelAS (

SELECT name||'_'|| idAS model_nameFROMpgml.model_versionsORDER BY idDESCLIMIT1

)

SELECTpgml_score(

SELECTpgml.score(

(SELECT model_nameFROM latest_model),-- last model we just trained