src/ports/postgres/modules/deep_learning/test/madlib_keras_model_averaging_e2e.sql_in - madlib - Git at Google

 /* ---------------------------------------------------------------------*//**
  *
  * Licensed to the Apache Software Foundation (ASF) under one
  * or more contributor license agreements.  See the NOTICE file
  * distributed with this work for additional information
  * regarding copyright ownership.  The ASF licenses this file
  * to you under the Apache License, Version 2.0 (the
  * "License"); you may not use this file except in compliance
  * with the License.  You may obtain a copy of the License at
  *
  *   http://www.apache.org/licenses/LICENSE-2.0
  *
  * Unless required by applicable law or agreed to in writing,
  * software distributed under the License is distributed on an
  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  * KIND, either express or implied.  See the License for the
  * specific language governing permissions and limitations
  * under the License.
  *
  *//* ---------------------------------------------------------------------*/

 m4_include(`SQLCommon.m4')

 \i m4_regexp(MODULE_PATHNAME,
              `\(.*\)libmadlib\.so',
              `\1../../modules/deep_learning/test/madlib_keras_iris.setup.sql_in'
 )

 \i m4_regexp(MODULE_PATHNAME,
              `\(.*\)libmadlib\.so',
              `\1../../modules/deep_learning/test/madlib_keras_custom_function.setup.sql_in'
 )

 DROP TABLE if exists pg_temp.iris_model, pg_temp.iris_model_summary;
 SELECT madlib_keras_fit(
 	'iris_data_packed',
 	'pg_temp.iris_model',
 	'iris_model_arch',
 	1,
 	$$loss='categorical_crossentropy', optimizer='Adam(lr=0.01)', metrics=['accuracy']$$,
   $$batch_size=16, epochs=1$$,
 	3,
 	FALSE
 );
 SELECT CASE WHEN is_ver_greater_than_gp_640_or_pg_11() is TRUE THEN assert_guc_value('plan_cache_mode', 'auto') END;

 SELECT assert(
         model_arch_table = 'iris_model_arch' AND
         validation_table is NULL AND
         source_table = 'iris_data_packed' AND
         model = 'pg_temp.iris_model' AND
         dependent_varname = 'class_text' AND
         independent_varname = 'attributes' AND
         madlib_version is NOT NULL AND
         num_iterations = 3 AND
         start_training_time < now() AND
         end_training_time < now() AND
         num_classes = 3 AND
         class_values = '{Iris-setosa,Iris-versicolor,Iris-virginica}' AND
         dependent_vartype LIKE '%char%' AND
         normalizing_const = 1,
         'Keras Fit Multiple Output Summary Validation failed. Actual:' || __to_char(summary))
 FROM (SELECT * FROM pg_temp.iris_model_summary) summary;

 -- Run Predict
 DROP TABLE IF EXISTS pg_temp.iris_predict;
 SELECT madlib_keras_predict(
     'pg_temp.iris_model',
     'iris_data',
     'id',
     'attributes',
     'pg_temp.iris_predict',
     'prob',
     FALSE);
 SELECT CASE WHEN is_ver_greater_than_gp_640_or_pg_11() is TRUE THEN assert_guc_value('plan_cache_mode', 'auto') END;

 -- Run Evaluate
 DROP TABLE IF EXISTS pg_temp.evaluate_out;
 SELECT madlib_keras_evaluate(
     'pg_temp.iris_model',
     'iris_data_val',
     'pg_temp.evaluate_out',
     FALSE);

 SELECT assert(loss >= 0 AND
         metric >= 0 AND
         metrics_type = '{accuracy}', 'Evaluate output validation failed.  Actual:' || __to_char(evaluate_out))
 FROM pg_temp.evaluate_out;
 SELECT CASE WHEN is_ver_greater_than_gp_640_or_pg_11() is TRUE THEN assert_guc_value('plan_cache_mode', 'auto') END;

 -- Test for one-hot encoded user input data
 DROP TABLE if exists iris_model, iris_model_summary, iris_model_info;
 SELECT madlib_keras_fit(
 	'iris_data_one_hot_encoded_packed',
 	'iris_model',
 	'iris_model_arch',
 	1,
 	$$loss='categorical_crossentropy', optimizer='Adam(lr=0.01)', metrics=['accuracy']$$,
   $$batch_size=16, epochs=1$$,
 	3,
 	FALSE
 );

 SELECT assert(
         model_arch_table = 'iris_model_arch' AND
         validation_table is NULL AND
         source_table = 'iris_data_one_hot_encoded_packed' AND
         model = 'iris_model' AND
         dependent_varname = 'class_one_hot_encoded' AND
         independent_varname = 'attributes' AND
         madlib_version is NOT NULL AND
         num_iterations = 3 AND
         start_training_time < now() AND
         end_training_time < now() AND
         dependent_vartype = 'integer[]' AND
         num_classes = NULL AND
         class_values = NULL AND
         normalizing_const = 1,
         'Keras Fit Multiple Output Summary Validation failed when user passes in 1-hot encoded label vector. Actual:' || __to_char(summary))
 FROM (SELECT * FROM iris_model_summary) summary;

 -- Run Predict
 DROP TABLE IF EXISTS iris_predict;
 SELECT madlib_keras_predict(
     'iris_model',
     'iris_data_one_hot_encoded',
     'id',
     'attributes',
     'iris_predict',
     'prob',
     FALSE);

 -- Run Evaluate
 DROP TABLE IF EXISTS evaluate_out;
 SELECT madlib_keras_evaluate(
     'iris_model',
     'iris_data_one_hot_encoded_val',
     'evaluate_out',
     FALSE);

 SELECT assert(loss >= 0 AND
         metric >= 0 AND
         metrics_type = '{accuracy}', 'Evaluate output validation failed.  Actual:' || __to_char(evaluate_out))
 FROM evaluate_out;

 -- TEST custom loss function


 DROP TABLE IF EXISTS test_custom_function_table;
 SELECT load_custom_function('test_custom_function_table', custom_function_zero_object(), 'test_custom_fn', 'returns test_custom_fn');
 SELECT load_custom_function('test_custom_function_table', custom_function_one_object(), 'test_custom_fn1', 'returns test_custom_fn1');

 DROP TABLE if exists iris_model, iris_model_summary, iris_model_info;
 SELECT madlib_keras_fit(
     'iris_data_packed',
     'iris_model',
     'iris_model_arch',
     1,
     $$ optimizer=SGD(lr=0.01, decay=1e-6, nesterov=True), loss='test_custom_fn', metrics=['mae']$$::text,
     $$ batch_size=2, epochs=1, verbose=0 $$::text,
     3,
     FALSE, NULL, 1, NULL, NULL, NULL,
     'test_custom_function_table'
 );
 DROP TABLE if exists iris_model, iris_model_summary, iris_model_info;
 SELECT madlib_keras_fit(
     'iris_data_packed',
     'iris_model',
     'iris_model_arch',
     1,
     $$ optimizer=SGD(lr=0.01, decay=1e-6, nesterov=True), loss='categorical_crossentropy', metrics=['test_custom_fn1']$$::text,
     $$ batch_size=2, epochs=1, verbose=0 $$::text,
     3,
     FALSE, NULL, 1, NULL, NULL, NULL,
     'test_custom_function_table'
 );
 DROP TABLE if exists iris_model, iris_model_summary, iris_model_info;
 SELECT madlib_keras_fit(
     'iris_data_packed',
     'iris_model',
     'iris_model_arch',
     1,
     $$ optimizer=SGD(lr=0.01, decay=1e-6, nesterov=True), loss='test_custom_fn', metrics=['test_custom_fn1']$$::text,
     $$ batch_size=2, epochs=1, verbose=0 $$::text,
     3,
     FALSE, NULL, 1, NULL, NULL, NULL,
     'test_custom_function_table'
 );

 SELECT assert(
         model_arch_table = 'iris_model_arch' AND
         model_id = 1 AND
         model_type = 'madlib_keras' AND
         source_table = 'iris_data_packed' AND
         model = 'iris_model' AND
         dependent_varname = 'class_text' AND
         independent_varname = 'attributes' AND
         dependent_vartype LIKE '%char%' AND
         normalizing_const = 1 AND
         pg_typeof(normalizing_const) = 'real'::regtype AND
         name is NULL AND
         description is NULL AND
         object_table = 'test_custom_function_table' AND
         model_size > 0 AND
         madlib_version is NOT NULL AND
         compile_params = $$ optimizer=SGD(lr=0.01, decay=1e-6, nesterov=True), loss='test_custom_fn', metrics=['test_custom_fn1']$$::text AND
         fit_params = $$ batch_size=2, epochs=1, verbose=0 $$::text AND
         num_iterations = 3 AND
         metrics_compute_frequency = 1 AND
         num_classes = 3 AND
         class_values = '{Iris-setosa,Iris-versicolor,Iris-virginica}' AND
         metrics_type = '{test_custom_fn1}' AND
         array_upper(training_metrics, 1) = 3 AND
         training_loss = '{0,0,0}' AND
         array_upper(metrics_elapsed_time, 1) = 3 ,
         'Keras model output Summary Validation failed. Actual:' || __to_char(summary))
 FROM (SELECT * FROM iris_model_summary) summary;

 SELECT assert(
         model_weights IS NOT NULL AND
         model_arch IS NOT NULL, 'Keras model output validation failed. Actual:' || __to_char(k))
 FROM (SELECT * FROM iris_model) k;

 DROP TABLE IF EXISTS evaluate_out;
 SELECT madlib_keras_evaluate(
     'iris_model',
     'iris_data_val',
     'evaluate_out',
     FALSE);

 SELECT assert(loss >= 0 AND
         metric >= 0 AND
         metrics_type = '{test_custom_fn1}' AND
         loss_type = 'test_custom_fn', 'Evaluate output validation failed.  Actual:' || __to_char(evaluate_out))
 FROM evaluate_out;
 SELECT CASE WHEN is_ver_greater_than_gp_640_or_pg_11() is TRUE THEN assert_guc_value('plan_cache_mode', 'auto') END;

 DROP TABLE if exists iris_model, iris_model_summary, iris_model_info;
 SELECT assert(trap_error($TRAP$SELECT madlib_keras_fit(
     'iris_data_packed',
     'iris_model',
     'iris_model_arch',
     1,
     $$ optimizer=SGD(lr=0.01, decay=1e-6, nesterov=True), loss='fail_test_custom_fn', metrics=['mae']$$::text,
     $$ batch_size=2, epochs=1, verbose=0 $$::text,
     3,
     FALSE, NULL, 1, NULL, NULL, NULL,
     'test_custom_function_table'
 );$TRAP$) = 1,
 'custom function in compile_params not defined in Object table.');

 DROP TABLE if exists iris_model, iris_model_summary, iris_model_info;
 SELECT assert(trap_error($TRAP$SELECT madlib_keras_fit(
     'iris_data_packed',
     'iris_model',
     'iris_model_arch',
     1,
     $$ optimizer=SGD(lr=0.01, decay=1e-6, nesterov=True), loss='accuracy', metrics=['fail_test_custom_fn']$$::text,
     $$ batch_size=2, epochs=1, verbose=0 $$::text,
     3,
     FALSE, NULL, 1, NULL, NULL, NULL,
     'test_custom_function_table'
 );$TRAP$) = 1,
 'custom function in compile_params not defined in Object table.');
	/* ---------------------------------------------------------------------//*
	*
	* Licensed to the Apache Software Foundation (ASF) under one
	* or more contributor license agreements. See the NOTICE file
	* distributed with this work for additional information
	* regarding copyright ownership. The ASF licenses this file
	* to you under the Apache License, Version 2.0 (the
	* "License"); you may not use this file except in compliance
	* with the License. You may obtain a copy of the License at
	*
	* http://www.apache.org/licenses/LICENSE-2.0
	*
	* Unless required by applicable law or agreed to in writing,
	* software distributed under the License is distributed on an
	* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	* KIND, either express or implied. See the License for the
	* specific language governing permissions and limitations
	* under the License.
	*
	// ---------------------------------------------------------------------*/

	m4_include(`SQLCommon.m4')

	\i m4_regexp(MODULE_PATHNAME,
	`\(.*\)libmadlib\.so',
	`\1../../modules/deep_learning/test/madlib_keras_iris.setup.sql_in'
	)

	\i m4_regexp(MODULE_PATHNAME,
	`\(.*\)libmadlib\.so',
	`\1../../modules/deep_learning/test/madlib_keras_custom_function.setup.sql_in'
	)

	DROP TABLE if exists pg_temp.iris_model, pg_temp.iris_model_summary;
	SELECT madlib_keras_fit(
	'iris_data_packed',
	'pg_temp.iris_model',
	'iris_model_arch',
	1,
	$$loss='categorical_crossentropy', optimizer='Adam(lr=0.01)', metrics=['accuracy']$$,
	$$batch_size=16, epochs=1$$,
	3,
	FALSE
	);
	SELECT CASE WHEN is_ver_greater_than_gp_640_or_pg_11() is TRUE THEN assert_guc_value('plan_cache_mode', 'auto') END;

	SELECT assert(
	model_arch_table = 'iris_model_arch' AND
	validation_table is NULL AND
	source_table = 'iris_data_packed' AND
	model = 'pg_temp.iris_model' AND
	dependent_varname = 'class_text' AND
	independent_varname = 'attributes' AND
	madlib_version is NOT NULL AND
	num_iterations = 3 AND
	start_training_time < now() AND
	end_training_time < now() AND
	num_classes = 3 AND
	class_values = '{Iris-setosa,Iris-versicolor,Iris-virginica}' AND
	dependent_vartype LIKE '%char%' AND
	normalizing_const = 1,
	'Keras Fit Multiple Output Summary Validation failed. Actual:' \|\| __to_char(summary))
	FROM (SELECT * FROM pg_temp.iris_model_summary) summary;

	-- Run Predict
	DROP TABLE IF EXISTS pg_temp.iris_predict;
	SELECT madlib_keras_predict(
	'pg_temp.iris_model',
	'iris_data',
	'id',
	'attributes',
	'pg_temp.iris_predict',
	'prob',
	FALSE);
	SELECT CASE WHEN is_ver_greater_than_gp_640_or_pg_11() is TRUE THEN assert_guc_value('plan_cache_mode', 'auto') END;

	-- Run Evaluate
	DROP TABLE IF EXISTS pg_temp.evaluate_out;
	SELECT madlib_keras_evaluate(
	'pg_temp.iris_model',
	'iris_data_val',
	'pg_temp.evaluate_out',
	FALSE);

	SELECT assert(loss >= 0 AND
	metric >= 0 AND
	metrics_type = '{accuracy}', 'Evaluate output validation failed. Actual:' \|\| __to_char(evaluate_out))
	FROM pg_temp.evaluate_out;
	SELECT CASE WHEN is_ver_greater_than_gp_640_or_pg_11() is TRUE THEN assert_guc_value('plan_cache_mode', 'auto') END;

	-- Test for one-hot encoded user input data
	DROP TABLE if exists iris_model, iris_model_summary, iris_model_info;
	SELECT madlib_keras_fit(
	'iris_data_one_hot_encoded_packed',
	'iris_model',
	'iris_model_arch',
	1,
	$$loss='categorical_crossentropy', optimizer='Adam(lr=0.01)', metrics=['accuracy']$$,
	$$batch_size=16, epochs=1$$,
	3,
	FALSE
	);

	SELECT assert(
	model_arch_table = 'iris_model_arch' AND
	validation_table is NULL AND
	source_table = 'iris_data_one_hot_encoded_packed' AND
	model = 'iris_model' AND
	dependent_varname = 'class_one_hot_encoded' AND
	independent_varname = 'attributes' AND
	madlib_version is NOT NULL AND
	num_iterations = 3 AND
	start_training_time < now() AND
	end_training_time < now() AND
	dependent_vartype = 'integer[]' AND
	num_classes = NULL AND
	class_values = NULL AND
	normalizing_const = 1,
	'Keras Fit Multiple Output Summary Validation failed when user passes in 1-hot encoded label vector. Actual:' \|\| __to_char(summary))
	FROM (SELECT * FROM iris_model_summary) summary;

	-- Run Predict
	DROP TABLE IF EXISTS iris_predict;
	SELECT madlib_keras_predict(
	'iris_model',
	'iris_data_one_hot_encoded',
	'id',
	'attributes',
	'iris_predict',
	'prob',
	FALSE);

	-- Run Evaluate
	DROP TABLE IF EXISTS evaluate_out;
	SELECT madlib_keras_evaluate(
	'iris_model',
	'iris_data_one_hot_encoded_val',
	'evaluate_out',
	FALSE);

	SELECT assert(loss >= 0 AND
	metric >= 0 AND
	metrics_type = '{accuracy}', 'Evaluate output validation failed. Actual:' \|\| __to_char(evaluate_out))
	FROM evaluate_out;

	-- TEST custom loss function


	DROP TABLE IF EXISTS test_custom_function_table;
	SELECT load_custom_function('test_custom_function_table', custom_function_zero_object(), 'test_custom_fn', 'returns test_custom_fn');
	SELECT load_custom_function('test_custom_function_table', custom_function_one_object(), 'test_custom_fn1', 'returns test_custom_fn1');

	DROP TABLE if exists iris_model, iris_model_summary, iris_model_info;
	SELECT madlib_keras_fit(
	'iris_data_packed',
	'iris_model',
	'iris_model_arch',
	1,
	$$ optimizer=SGD(lr=0.01, decay=1e-6, nesterov=True), loss='test_custom_fn', metrics=['mae']$$::text,
	$$ batch_size=2, epochs=1, verbose=0 $$::text,
	3,
	FALSE, NULL, 1, NULL, NULL, NULL,
	'test_custom_function_table'
	);
	DROP TABLE if exists iris_model, iris_model_summary, iris_model_info;
	SELECT madlib_keras_fit(
	'iris_data_packed',
	'iris_model',
	'iris_model_arch',
	1,
	$$ optimizer=SGD(lr=0.01, decay=1e-6, nesterov=True), loss='categorical_crossentropy', metrics=['test_custom_fn1']$$::text,
	$$ batch_size=2, epochs=1, verbose=0 $$::text,
	3,
	FALSE, NULL, 1, NULL, NULL, NULL,
	'test_custom_function_table'
	);
	DROP TABLE if exists iris_model, iris_model_summary, iris_model_info;
	SELECT madlib_keras_fit(
	'iris_data_packed',
	'iris_model',
	'iris_model_arch',
	1,
	$$ optimizer=SGD(lr=0.01, decay=1e-6, nesterov=True), loss='test_custom_fn', metrics=['test_custom_fn1']$$::text,
	$$ batch_size=2, epochs=1, verbose=0 $$::text,
	3,
	FALSE, NULL, 1, NULL, NULL, NULL,
	'test_custom_function_table'
	);

	SELECT assert(
	model_arch_table = 'iris_model_arch' AND
	model_id = 1 AND
	model_type = 'madlib_keras' AND
	source_table = 'iris_data_packed' AND
	model = 'iris_model' AND
	dependent_varname = 'class_text' AND
	independent_varname = 'attributes' AND
	dependent_vartype LIKE '%char%' AND
	normalizing_const = 1 AND
	pg_typeof(normalizing_const) = 'real'::regtype AND
	name is NULL AND
	description is NULL AND
	object_table = 'test_custom_function_table' AND
	model_size > 0 AND
	madlib_version is NOT NULL AND
	compile_params = $$ optimizer=SGD(lr=0.01, decay=1e-6, nesterov=True), loss='test_custom_fn', metrics=['test_custom_fn1']$$::text AND
	fit_params = $$ batch_size=2, epochs=1, verbose=0 $$::text AND
	num_iterations = 3 AND
	metrics_compute_frequency = 1 AND
	num_classes = 3 AND
	class_values = '{Iris-setosa,Iris-versicolor,Iris-virginica}' AND
	metrics_type = '{test_custom_fn1}' AND
	array_upper(training_metrics, 1) = 3 AND
	training_loss = '{0,0,0}' AND
	array_upper(metrics_elapsed_time, 1) = 3 ,
	'Keras model output Summary Validation failed. Actual:' \|\| __to_char(summary))
	FROM (SELECT * FROM iris_model_summary) summary;

	SELECT assert(
	model_weights IS NOT NULL AND
	model_arch IS NOT NULL, 'Keras model output validation failed. Actual:' \|\| __to_char(k))
	FROM (SELECT * FROM iris_model) k;

	DROP TABLE IF EXISTS evaluate_out;
	SELECT madlib_keras_evaluate(
	'iris_model',
	'iris_data_val',
	'evaluate_out',
	FALSE);

	SELECT assert(loss >= 0 AND
	metric >= 0 AND
	metrics_type = '{test_custom_fn1}' AND
	loss_type = 'test_custom_fn', 'Evaluate output validation failed. Actual:' \|\| __to_char(evaluate_out))
	FROM evaluate_out;
	SELECT CASE WHEN is_ver_greater_than_gp_640_or_pg_11() is TRUE THEN assert_guc_value('plan_cache_mode', 'auto') END;

	DROP TABLE if exists iris_model, iris_model_summary, iris_model_info;
	SELECT assert(trap_error($TRAP$SELECT madlib_keras_fit(
	'iris_data_packed',
	'iris_model',
	'iris_model_arch',
	1,
	$$ optimizer=SGD(lr=0.01, decay=1e-6, nesterov=True), loss='fail_test_custom_fn', metrics=['mae']$$::text,
	$$ batch_size=2, epochs=1, verbose=0 $$::text,
	3,
	FALSE, NULL, 1, NULL, NULL, NULL,
	'test_custom_function_table'
	);$TRAP$) = 1,
	'custom function in compile_params not defined in Object table.');

	DROP TABLE if exists iris_model, iris_model_summary, iris_model_info;
	SELECT assert(trap_error($TRAP$SELECT madlib_keras_fit(
	'iris_data_packed',
	'iris_model',
	'iris_model_arch',
	1,
	$$ optimizer=SGD(lr=0.01, decay=1e-6, nesterov=True), loss='accuracy', metrics=['fail_test_custom_fn']$$::text,
	$$ batch_size=2, epochs=1, verbose=0 $$::text,
	3,
	FALSE, NULL, 1, NULL, NULL, NULL,
	'test_custom_function_table'
	);$TRAP$) = 1,
	'custom function in compile_params not defined in Object table.');