Gradient Boosting machine error

[Former Member]

I am getting this error using the PAL_GBDT_PREDICT on a HANA2 Express setup locally (according to latest official setup guide)

 column store error: search table error:  [2629] executor: plan terminated internally after being inactive for too long 

I can see this error occurring only one other time in the forums, here, again with a PAL function. Here is my code if anyone wants to replicate it:

Dataset: I used the white wine data set available here. Split the data 50:50 into a WINE_TRAIN and WINE_TEST table in your schema.

DROP TYPE PAL_GBDT_TRAIN_T;
CREATE TYPE PAL_GBDT_TRAIN_T AS TABLE ("fixedacidity" DOUBLE CS_DOUBLE, "volatileacidity" DOUBLE CS_DOUBLE, "citricacid" DOUBLE CS_DOUBLE, "residualsugar" DOUBLE CS_DOUBLE, "chlorides" DOUBLE CS_DOUBLE, "freesulfurdioxide" DOUBLE CS_DOUBLE, "totalsulfurdioxide" DOUBLE CS_DOUBLE, "density" DOUBLE CS_DOUBLE, "pH" DOUBLE CS_DOUBLE, "sulphates" DOUBLE CS_DOUBLE, "alcohol" DOUBLE CS_DOUBLE, "quality" varchar(5));

DROP TYPE PAL_GBDT_CONTROL_T;
CREATE TYPE PAL_GBDT_CONTROL_T AS TABLE(NAME VARCHAR(50),INTARGS INTEGER, DOUBLEARGS DOUBLE, STRINGARGS VARCHAR(100));

DROP TYPE PAL_GBDT_CV_T;
CREATE TYPE PAL_GBDT_CV_T AS TABLE("PARAM_NAME" VARCHAR(5000),"PARAM_VALUE" VARCHAR(5000));

DROP TYPE PAL_GBDT_MODEL_T;
CREATE TYPE PAL_GBDT_MODEL_T AS TABLE("LINE_" INTEGER, "KEY_" VARCHAR(5000), "VALUE_" VARCHAR(5000));

DROP TABLE PAL_GBDT_TRAIN_PDATA_TBL;
CREATE TABLE PAL_GBDT_TRAIN_PDATA_TBL("POSITION" INT, "SCHEMA_NAME" NVARCHAR(256), "TYPE_NAME" NVARCHAR(256), "PARAMETER_TYPE" VARCHAR(7));

INSERT INTO PAL_GBDT_TRAIN_PDATA_TBL VALUES (1,'DEVUSER','PAL_GBDT_TRAIN_T','IN');
INSERT INTO PAL_GBDT_TRAIN_PDATA_TBL VALUES (2,'DEVUSER','PAL_GBDT_CONTROL_T','IN');
INSERT INTO PAL_GBDT_TRAIN_PDATA_TBL VALUES (3,'DEVUSER','PAL_GBDT_CV_T','OUT');
INSERT INTO PAL_GBDT_TRAIN_PDATA_TBL VALUES (4,'DEVUSER','PAL_GBDT_MODEL_T','OUT');

CALL SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('DEVUSER','PAL_GBDT_TRAIN');
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'GBDTTRAIN', 'DEVUSER', 'PAL_GBDT_TRAIN',PAL_GBDT_TRAIN_PDATA_TBL);

DROP TABLE PAL_GBDT_CV_TBL;
CREATE COLUMN TABLE PAL_GBDT_CV_TBL  like PAL_GBDT_CV_T;

DROP TABLE PAL_GBDT_MODEL_TBL;
CREATE COLUMN TABLE PAL_GBDT_MODEL_TBL LIKE PAL_GBDT_MODEL_T;

DROP TABLE PAL_GBDT_CONTROL_TBL;
CREATE COLUMN TABLE PAL_GBDT_CONTROL_TBL LIKE PAL_GBDT_CONTROL_T;

INSERT INTO PAL_GBDT_CONTROL_TBL VALUES('LEARNING_RATE', null, 0.1, null);
INSERT INTO PAL_GBDT_CONTROL_TBL VALUES('MIN_SPLIT_LOSS', null, 0.0, null);
INSERT INTO PAL_GBDT_CONTROL_TBL VALUES('FOLD_NUM', 5, null, null);
INSERT INTO PAL_GBDT_CONTROL_TBL VALUES('ITER_NUM', 4, null, null);
INSERT INTO PAL_GBDT_CONTROL_TBL VALUES('CV_METRIC', null, null,'ERROR_RATE');
INSERT INTO PAL_GBDT_CONTROL_TBL VALUES('REF_METRIC', null, null, 'AUC');
INSERT INTO PAL_GBDT_CONTROL_TBL VALUES('MAX_TREE_DEPTH', 6, null, null);
INSERT INTO PAL_GBDT_CONTROL_TBL VALUES('RANGE_LEARNING_RATE', null, null,'[0.1,3,1.0]');
INSERT INTO PAL_GBDT_CONTROL_TBL VALUES('RANGE_MIN_SPLIT_LOSS', null, null,'[0.1,3,1.0]');

CALL DEVUSER.PAL_GBDT_TRAIN(WINE_TRAIN, PAL_GBDT_CONTROL_TBL, PAL_GBDT_CV_TBL, PAL_GBDT_MODEL_TBL) WITH overview;

DROP TYPE PAL_GBDT_PREDICT_T;
--CREATE TABLE PAL_GBDT_PREDICT_T LIKE "DEVUSER"."WINE_TEST";
CREATE TYPE PAL_GBDT_PREDICT_T AS TABLE ("ID" INTEGER CS_INT, "fixedacidity" DOUBLE CS_DOUBLE, "volatileacidity" DOUBLE CS_DOUBLE, "citricacid" DOUBLE CS_DOUBLE, "residualsugar" DOUBLE CS_DOUBLE, "chlorides" DOUBLE CS_DOUBLE, "freesulfurdioxide" DOUBLE CS_DOUBLE, "totalsulfurdioxide" DOUBLE CS_DOUBLE, "density" DOUBLE CS_DOUBLE, "pH" DOUBLE CS_DOUBLE, "sulphates" DOUBLE CS_DOUBLE, "alcohol" DOUBLE CS_DOUBLE) ;

DROP TYPE PAL_GBDT_CONTROL_T;
CREATE TYPE PAL_GBDT_CONTROL_T AS TABLE( NAME VARCHAR(50), INTARGS INTEGER, DOUBLEARGS DOUBLE, STRINGARGS VARCHAR(100));

DROP TYPE PAL_GBDT_RESULT_T;
CREATE TYPE PAL_GBDT_RESULT_T as table( "ID" INTEGER, "LABEL" VARCHAR(50), "PROB" DOUBLE);

drop table PAL_GBDT_RESULT_TBL;
create table PAL_GBDT_RESULT_TBL like PAL_GBDT_RESULT_T;

DROP TABLE PAL_GBDT_PREDICT_PDATA_TBL;
CREATE TABLE PAL_GBDT_PREDICT_PDATA_TBL( "POSITION" INTEGER, "SCHEMA_NAME" NVARCHAR(256), "TYPE_NAME" NVARCHAR(256), "PARAMETER_TYPE" VARCHAR(7));

INSERT INTO PAL_GBDT_PREDICT_PDATA_TBL VALUES (1,'DEVUSER','PAL_GBDT_PREDICT_T','IN');
INSERT INTO PAL_GBDT_PREDICT_PDATA_TBL VALUES (2,'DEVUSER','PAL_GBDT_CONTROL_T','IN');
INSERT INTO PAL_GBDT_PREDICT_PDATA_TBL VALUES (3,'DEVUSER','PAL_GBDT_MODEL_T','IN');
INSERT INTO PAL_GBDT_PREDICT_PDATA_TBL VALUES (4,'DEVUSER','PAL_GBDT_RESULT_T','OUT');

CALL SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('DEVUSER','PAL_GBDT_PREDICT');
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE( 'AFLPAL', 'GBDTPREDICT', 'DEVUSER', 'PAL_GBDT_PREDICT', PAL_GBDT_PREDICT_PDATA_TBL);

DROP TABLE PAL_GBDT_CONTROL_TBL;
CREATE COLUMN TABLE PAL_GBDT_CONTROL_TBL  LIKE PAL_GBDT_CONTROL_T;

CALL DEVUSER.PAL_GBDT_PREDICT( WINE_TEST, PAL_GBDT_CONTROL_TBL, PAL_GBDT_MODEL_TBL, PAL_GBDT_RESULT_TBL) WITH overview;
SELECT * FROM PAL_GBDT_RESULT_TBL;

Any help appreciated

[Former Member]

Hi Michael, we will investigate the issue.

Best regards,

Xingtian from PAL team

[former_member343107 · Accepted solution]

Hello Michael,

We’ll have further investigation on this. For the moment, you can try a workaround by setting the parameter “VERBOSE_OUTPUT”:

INSERT INTO PAL_GBDT_CONTROL_TBL VALUES('VERBOSE_OUTPUT', 1, null,null);

Hope this helps.

Best Regards

Jerome

[Former Member · Accepted solution]

Hi Jerome,

I've similar error with 'RANDOMFORESTSCORING',

'SAP DBTech JDBC: [2048]: column store error: search table error: [2629] executor: plan terminated internally after being inactive for too long'

If possible, could you guide me how we could workaround this problem, please?

PS. I have tried setting 'VERBOSE' to 1, but it didn't work.

Kind regards,

Tarapong

[Former Member]

Thanks Jerome, it worked!