HCP Trial - PAL: AFL Error [423] when creating wrapper

[Former Member]

Hi,

i am following Single Exponential Smoothing - SAP HANA Predictive Analysis Library (PAL) - SAP Library in the HCP Trial.

I replaced

CALL SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('DM_PAL', 'SINGLESMOOTH_TEST_PROC'); 
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'SINGLESMOOTH', 'DM_PAL', 'SINGLESMOOTH_TEST_PROC',PAL_SINGLESMOOTH_PDATA_TBL); 

with

CALL "HCP"."HCP_AFL_WRAPPER_ERASER"('SINGLESMOOTH_TEST_PROC');

CALL "HCP"."HCP_AFL_WRAPPER_GENERATOR"('SINGLESMOOTH','AFLPAL','SINGLESMOOTH_TEST_PROC',PAL_SINGLESMOOTH_PDATA_TBL);

because of the HCP Trial limitations.

Now i am getting this error:

Could not execute 'CALL ...' in 151 ms 602 µs .

SAP DBTech JDBC: [423]: AFL error:  [423] "HCP"."HCP_AFL_WRAPPER_GENERATOR": line 31 col 5 (at pos 1742): [423] (range 3) AFL error exception: AFL error:  [423] "PSADBA"."AFL_WRAPPER_GENERATOR": line 57 col 5 (at pos 1842): [423] (range 3) AFL error exception: AFL error: registration finished with errors, see indexserver trace

when calling the wrapper like in the example above.

The rest of the code is still like in the example in the link.

How can i get rid of this error?

Thank you in advance,

Julian

[Former Member · Accepted solution]

Hi Julian,

I also tried to execute the PAL function in HCP trial and finally it succeed..

I think there are two main differences of the usage:

1. find the schema where you can create the required table or table type.

2. create procedure with the HCP AFL wrapper generator

Here is the script which I execute successfully in HCP trial . I tried it successfully again few minutes ago:)

You can replace all the schema name "NEO_D29KICVQSCRTK9RDQX8PJE72Y" with your own schema (the result from the first query) .

Hope it could help.

-- Find the schema
SELECT SCHEMA_NAME FROM "HCP"."HCP_DEV_METADATA" ;
SET SCHEMA NEO_D29KICVQSCRTK9RDQX8PJE72Y;


-- 1. Create required table type and table
DROP TYPE PAL_KMEANS_DATA_T;
CREATE TYPE PAL_KMEANS_DATA_T AS TABLE( "ID" INTEGER, "V000" DOUBLE, "V001" VARCHAR(2), "V002" DOUBLE);
DROP TYPE PAL_KMEANS_ASSIGNED_T;
CREATE TYPE PAL_KMEANS_ASSIGNED_T AS TABLE( "ID" INTEGER, "CLUSTER" INTEGER, "DISTANCE" DOUBLE);
DROP TYPE PAL_KMEANS_CENTERS_T;
CREATE TYPE PAL_KMEANS_CENTERS_T AS TABLE( "CLUSTER_ID" INTEGER, "V000" DOUBLE, "V001" VARCHAR(2), "V002" DOUBLE);
DROP TYPE PAL_CONTROL_T;
CREATE TYPE PAL_CONTROL_T AS TABLE( "NAME" VARCHAR (100), "INTARGS" INTEGER, "DOUBLEARGS" DOUBLE, "STRINGARGS" VARCHAR (100));
DROP TABLE PAL_KMEANS_PDATA_TBL;
CREATE COLUMN TABLE PAL_KMEANS_PDATA_TBL("ID" INTEGER, "TYPENAME" VARCHAR(100), "DIRECTION" VARCHAR(100));
INSERT INTO PAL_KMEANS_PDATA_TBL VALUES (1, 'NEO_D29KICVQSCRTK9RDQX8PJE72Y.PAL_KMEANS_DATA_T', 'in');
INSERT INTO PAL_KMEANS_PDATA_TBL VALUES (2, 'NEO_D29KICVQSCRTK9RDQX8PJE72Y.PAL_CONTROL_T', 'in');
INSERT INTO PAL_KMEANS_PDATA_TBL VALUES (3, 'NEO_D29KICVQSCRTK9RDQX8PJE72Y.PAL_KMEANS_ASSIGNED_T', 'out');
INSERT INTO PAL_KMEANS_PDATA_TBL VALUES (4, 'NEO_D29KICVQSCRTK9RDQX8PJE72Y.PAL_KMEANS_CENTERS_T', 'out');
GRANT SELECT ON NEO_D29KICVQSCRTK9RDQX8PJE72Y.PAL_KMEANS_PDATA_TBL to SYSTEM;

--CALL "SYS".AFLLANG_WRAPPER_PROCEDURE_DROP('NEO_D29KICVQSCRTK9RDQX8PJE72Y', 'PAL_KMEANS_PROC');
--CALL "SYS".AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'KMEANS', 'DM_PAL', 'PAL_KMEANS_PROC', PAL_KMEANS_PDATA_TBL);
-- 2. Use HCP wrapper generator to create the procedure
CALL "HCP"."HCP_AFL_WRAPPER_ERASER"('PAL_KMEANS');
CALL "HCP"."HCP_AFL_WRAPPER_GENERATOR"('PAL_KMEANS','AFLPAL', 'KMEANS', PAL_KMEANS_PDATA_TBL);   -- cannot configure the target schema? suffix, AFL area, function, signature table


-- 3. prepare to call the kmeans
DROP TABLE #PAL_CONTROL_TBL;
CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_CONTROL_TBL( "NAME" VARCHAR (100), "INTARGS" INTEGER, "DOUBLEARGS" DOUBLE, "STRINGARGS" VARCHAR (100));
INSERT INTO #PAL_CONTROL_TBL VALUES ('THREAD_NUMBER', 2, null, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('GROUP_NUMBER', 4, null, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('INIT_TYPE', 1, null, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('DISTANCE_LEVEL',2, null, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('MAX_ITERATION', 100, null, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('EXIT_THRESHOLD', null, 1.0E-6, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('CATEGORY_WEIGHTS', null, 0.5, null);
DROP TABLE PAL_KMEANS_DATA_TBL;
CREATE COLUMN TABLE PAL_KMEANS_DATA_TBL LIKE PAL_KMEANS_DATA_T;
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (0, 0.5, 'A', 0.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (1, 1.5, 'A', 0.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (2, 1.5, 'A', 1.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (3, 0.5, 'A', 1.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (4, 1.1, 'B', 1.2);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (5, 0.5, 'B', 15.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (6, 1.5, 'B', 15.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (7, 1.5, 'B', 16.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (8, 0.5, 'B', 16.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (9, 1.2, 'C', 16.1);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (10, 15.5, 'C', 15.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (11, 16.5, 'C', 15.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (12, 16.5, 'C', 16.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (13, 15.5, 'C', 16.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (14, 15.6, 'D', 16.2);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (15, 15.5, 'D', 0.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (16, 16.5, 'D', 0.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (17, 16.5, 'D', 1.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (18, 15.5, 'D', 1.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (19, 15.7, 'A', 1.6);
DROP TABLE PAL_KMEANS_ASSIGNED_TBL;
CREATE COLUMN TABLE PAL_KMEANS_ASSIGNED_TBL LIKE PAL_KMEANS_ASSIGNED_T;
DROP TABLE PAL_KMEANS_CENTERS_TBL;
CREATE COLUMN TABLE PAL_KMEANS_CENTERS_TBL LIKE PAL_KMEANS_CENTERS_T;


-- 4. Call the procedure
CALL "_SYS_AFL"."NEO_D29KICVQSCRTK9RDQX8PJE72Y_PAL_KMEANS"(PAL_KMEANS_DATA_TBL, #PAL_CONTROL_TBL, PAL_KMEANS_ASSIGNED_TBL, PAL_KMEANS_CENTERS_TBL) with OVERVIEW;
SELECT * FROM PAL_KMEANS_ASSIGNED_TBL;
SELECT * FROM PAL_KMEANS_CENTERS_TBL;

[Former Member · Accepted solution]

Hello,

thank you very much for your code example!

It does work, but when i try to apply the same principle for the SINGLESMOOTH function, i still get the mentioned error,

One of my Input Tables must be incorrect, but i don't know where to find the correct format. According to the HANA PAL website they should be correct. Single Exponential Smoothing - SAP HANA Predictive Analysis Library (PAL) - SAP Library

This is the code up until the line in which the error occurs.

-- SINGLESMOOTH
-- Set the schema
SET SCHEMA NEO_2IEC9JKBRL0NG9ZA0DYGM3ILG;

-- 1. Create required table type and table
--data
DROP TYPE PAL_SINGLESMOOTH_DATA_T;
CREATE TYPE PAL_SINGLESMOOTH_DATA_T AS TABLE( "ID" INTEGER, "RAWDATA" DOUBLE);
--result
DROP TYPE PAL_SINGLESMOOTH_RESULT_T;
CREATE TYPE PAL_SINGLESMOOTH_RESULT_T AS TABLE ("TIME" INT, "OUTPUT" DOUBLE);
--statistcs
DROP TYPE PAL_SINGLESMOOTH_STATISTICS_T;
CREATE TYPE PAL_SINGLESMOOTH_STATISTICS_T AS TABLE ("NAME" VARCHAR (50), "VALUE" DOUBLE);
--control
DROP TYPE PAL_CONTROL_T;
CREATE TYPE PAL_CONTROL_T AS TABLE( "NAME" VARCHAR (100), "INTARGS" INTEGER, "DOUBLEARGS" DOUBLE, "STRINGARGS" VARCHAR (100));
--pdata
DROP TABLE PAL_SINGLESMOOTH_PDATA_TBL;
CREATE COLUMN TABLE PAL_SINGLESMOOTH_PDATA_TBL("ID" INTEGER, "TYPENAME" VARCHAR(100), "DIRECTION" VARCHAR(100));
INSERT INTO PAL_SINGLESMOOTH_PDATA_TBL VALUES (1, 'NEO_2IEC9JKBRL0NG9ZA0DYGM3ILG.PAL_SINGLESMOOTH_DATA_T', 'in');
INSERT INTO PAL_SINGLESMOOTH_PDATA_TBL VALUES (2, 'NEO_2IEC9JKBRL0NG9ZA0DYGM3ILG.PAL_CONTROL_T', 'in'); 
INSERT INTO PAL_SINGLESMOOTH_PDATA_TBL VALUES (3, 'NEO_2IEC9JKBRL0NG9ZA0DYGM3ILG.PAL_SINGLESMOOTH_RESULT_T', 'out');
INSERT INTO PAL_SINGLESMOOTH_PDATA_TBL VALUES (4, 'NEO_2IEC9JKBRL0NG9ZA0DYGM3ILG.PAL_SINGLESMOOTH_STATISTICS_T', 'out');
GRANT SELECT ON NEO_2IEC9JKBRL0NG9ZA0DYGM3ILG.PAL_SINGLESMOOTH_PDATA_TBL to SYSTEM;


-- 2. Use HCP wrapper generator to create the procedure
CALL "HCP"."HCP_AFL_WRAPPER_ERASER"('PAL_SINGLESMOOTH');
CALL "HCP"."HCP_AFL_WRAPPER_GENERATOR"('PAL_SINGLESMOOTH','AFLPAL', 'SINGLESMOOTH', PAL_SINGLESMOOTH_PDATA_TBL);

EDIT: The problem here was the statistics table! I just commented it out and it worked!

Thanks Qinhe Lin, i marked your answer as correct!

[achab]

Hi Julian,

Disclaimer: I am not a PAL coding expert 🙂

I will just try some of the "usual suspects" possibilities that could cause the error.

Are the prerequisites fulfilled?

Prerequisites - SAP HANA Predictive Analysis Library (PAL) - SAP Library

Have you created the proper security?

http://help.sap.com/saphelp_hanaplatform/helpdata/en/25/3f2b552f55436ba1243ff0d7b374b3/content.htm?f...

Have you checked the proper PAL installation?

Checking PAL Installation - SAP HANA Predictive Analysis Library (PAL) - SAP Library

Does your data table contains decimal?

The current PAL does not support the decimal data type as per the doc here: http://help.sap.com/hana/SAP_HANA_Predictive_Analysis_Library_PAL_en.pdf

p9.

Have you checked that the prerequisites for the algorithm that you are using are met?

As per the doc link you sent they are:

• No missing or null data in the inputs.
• The data is numeric, not categorical.

Hope that helps,

Best regards

Antoine

[Former Member]

Hi Antoine,

thanks for taking the time to answer! As to the points you mentioned:

• The prerequisites should be fulfilled by default in HCP, right?
• I don't know how to grant me the roles in hcp
• The PAL is installed (per default in hana cloud platform), the given select statements return results
• The data is in the right format, i am using sample data from the linked PAL document

Can you tell me how to grant me the roles you mentioned?
I thought it had something to do with the limitations of HCP Trial, but i didn't change the security settings, so it might be that.

[achab]

It seems I said something foolish as nothing needs to be configured for HCP trial + PAL. It should work right away.

There a few specific things that change compared to PAL standard usage. See SAP HANA Cloud Platform

I noticed

Consider creating the all related artifacts (signature, input, parameter, output tables, etc.) in the HANA Repository. If you choose to create them in the Catalog, you should do it in your NEO_ schema (this is your application schema).

And the table below

I also saw: that might be of help.

I would also recommend you cross-post your question there:

Finally looping in to provide more insights.

Sorry for not being more helpful here.

Best regards

Antoine

[Former Member]

Hello Antoine,

my bad for posting in the wrong subforum.

Thank you for your help, after trying some things tomorrow, i will either post this question in the correct subforum or update the solution here.

In either case: Thanks for your help!

Best Regards,

Julian

[Vlado]

Please do NOT cross-post questions! See .

Instead, move the question to the correct space (if you're the OP) or ask a moderator to move it (Alert moderator link).

Thanks!

--Vlado (HCP space mod)

[achab]

Hi Vladimir,

The reason I was suggesting the cross-post specifically for this thread is that the questions lies at the boundaries of predictive and HCP technologies. I hope it clarifies.

Anyway as it seems we do have the right persons on the thread (thanks for jumping in!) let's keep it here for now.

For my curiosity what does OP means?

Thanks & regards

Antoine

[Vlado]

OP = Original Poster

Even in that case, it's still not advisable to cross-post. Instead, mentioning the other space(s) (as you did above) should do the job as it would appear also in the feeds of people following that other space(s) - that's exactly how I caught up on this thread

[achab]

Makes sense! I will do that moving forward.

thx

antoine