fmcquillan99 edited a comment on pull request #560:
URL: https://github.com/apache/madlib/pull/560#issuecomment-796321033
(1)
```
# import database connector psycopg2 and create connection cursor
import psycopg2 as p2
conn = p2.connect('postgresql://gpadmin@localhost:8000/madlib')
cur = conn.cursor()
# import Dill and define functions
import dill
# custom loss
def __squared_error(y_true, y_pred):
import tensorflow.keras.backend as K
return K.square(y_pred - y_true)
pb_squared_error=dill.dumps(squared_error)
# custom metric
def _rmse(y_true, y_pred):
import tensorflow.keras.backend as K
return K.sqrt(K.mean(K.square(y_pred - y_true), axis=-1))
pb_rmse=dill.dumps(rmse)
# call load function
cur.execute("DROP TABLE IF EXISTS madlib.custom_function_table")
cur.execute("SELECT madlib.load_custom_function('custom_function_table',
%s,'__squared_error', 'squared error')", [p2.Binary(pb_squared_error)])
cur.execute("SELECT madlib.load_custom_function('custom_function_table',
%s,'_rmse', 'root mean square error')", [p2.Binary(pb_rmse)])
conn.commit()
```
```
SELECT madlib.load_top_k_accuracy_function('custom_function_table',
2);
SELECT madlib.load_top_k_accuracy_function('custom_function_table',
3);
SELECT madlib.load_top_k_accuracy_function('custom_function_table',
5);
```
```
SELECT id, name, description FROM madlib.custom_function_table ORDER BY id;
```
```
id | name | description
----+-----------------+------------------------
1 | __squared_error | squared error
2 | _rmse | root mean square error
3 | top_2_accuracy | returns top_2_accuracy
4 | top_3_accuracy | returns top_3_accuracy
5 | top_5_accuracy | returns top_5_accuracy
(5 rows)
```
```
DROP TABLE IF EXISTS iris_model, iris_model_summary;
SELECT madlib.madlib_keras_fit('iris_train_packed', -- source table
'iris_model', -- model output table
'model_arch_library', -- model arch table
1, -- model arch id
$$ loss='__squared_error', optimizer='adam',
metrics=['_rmse'] $$, -- compile_params
$$ batch_size=5, epochs=3 $$, -- fit_params
10, -- num_iterations
NULL, -- use_gpus,
NULL, -- validation_table,
NULL, --
metrics_compute_frequency,
NULL, -- warm_start,
NULL, -- name,
NULL, -- description,
'custom_function_table' -- object_table
);
```
works OK
```
SELECT * FROM iris_model_summary;
```
```
-[ RECORD 1
]-------------+--------------------------------------------------------------
source_table | iris_train_packed
model | iris_model
dependent_varname | {class_text}
independent_varname | {attributes}
model_arch_table | model_arch_library
model_id | 1
compile_params | loss='__squared_error', optimizer='adam',
metrics=['_rmse']
fit_params | batch_size=5, epochs=3
num_iterations | 10
validation_table |
object_table | madlib.custom_function_table
metrics_compute_frequency | 10
name |
description |
model_type | madlib_keras
model_size | 0.7900390625
start_training_time | 2021-03-11 00:35:24.12472
end_training_time | 2021-03-11 00:35:26.849639
metrics_elapsed_time | {2.724858045578}
madlib_version | 1.18.0-dev
num_classes | {3}
dependent_vartype | {"character varying"}
normalizing_const | 1
metrics_type | {_rmse}
loss_type | __squared_error
training_metrics_final | 0.228817746043205
training_loss_final | 0.0709948241710663
training_metrics | {0.228817746043205}
training_loss | {0.0709948241710663}
validation_metrics_final |
validation_loss_final |
validation_metrics |
validation_loss |
metrics_iters | {10}
class_text_class_values | {Iris-setosa,Iris-versicolor,Iris-virginica}
```
(2)
same `madlib.custom_function_table` as above
```
DROP TABLE IF EXISTS iris_model, iris_model_summary;
SELECT madlib.madlib_keras_fit('iris_train_packed', -- source table
'iris_model', -- model output table
'model_arch_library', -- model arch table
1, -- model arch id
$$ loss='categorical_crossentropy',
optimizer='adam', metrics=['top_k_categorical_accuracy'] $$, -- compile_params
$$ batch_size=5, epochs=3 $$, -- fit_params
10, -- num_iterations
NULL, -- use_gpus,
NULL, -- validation_table,
NULL, --
metrics_compute_frequency,
NULL, -- warm_start,
NULL, -- name,
NULL, -- description,
NULL -- object_table
);
```
works OK
```
SELECT * FROM iris_model_summary;
```
```
-[ RECORD 1
]-------------+--------------------------------------------------------------------------------------------
source_table | iris_train_packed
model | iris_model
dependent_varname | {class_text}
independent_varname | {attributes}
model_arch_table | model_arch_library
model_id | 1
compile_params | loss='categorical_crossentropy',
optimizer='adam', metrics=['top_k_categorical_accuracy']
fit_params | batch_size=5, epochs=3
num_iterations | 10
validation_table |
object_table |
metrics_compute_frequency | 10
name |
description |
model_type | madlib_keras
model_size | 0.7900390625
start_training_time | 2021-03-11 00:32:13.816587
end_training_time | 2021-03-11 00:32:16.636334
metrics_elapsed_time | {2.81966114044189}
madlib_version | 1.18.0-dev
num_classes | {3}
dependent_vartype | {"character varying"}
normalizing_const | 1
metrics_type | {top_k_categorical_accuracy}
loss_type | categorical_crossentropy
training_metrics_final | 1
training_loss_final | 0.347711235284805
training_metrics | {1}
training_loss | {0.347711235284805}
validation_metrics_final |
validation_loss_final |
validation_metrics |
validation_loss |
metrics_iters | {10}
class_text_class_values | {Iris-setosa,Iris-versicolor,Iris-virginica}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
