I am using the APRIORIRULE
function of PAL in HANA. Everything is fine with the execution, but my result table doesn't display any result.
I have all the necessary rights for the schema I am using.
I expect to have a set of rules, but instead all I get is the head of the result table, without any data on it.
Why isn't my result table showing any results when using APRIORI function?
SET SCHEMA KLEAKOCI;
DROP TYPE NEW_DATA;
CREATE TYPE NEW_DATA AS TABLE(
"CUSTOMER" VARCHAR(200),
"ITEM" VARCHAR(200)
);
DROP TYPE RESULT_T;
CREATE TYPE RESULT_T AS TABLE(
"PRERULE" VARCHAR(500),
"POSTRULE" VARCHAR(500),
"SUPPORT" DOUBLE,
"CONFIDENCE" DOUBLE,
"LIFT" DOUBLE
);
DROP TYPE MODEL;
CREATE TYPE MODEL AS TABLE(
"ID" INTEGER,
"PMMLMODEL" VARCHAR(5000)
);
DROP TYPE CONTROL_T;
CREATE TYPE CONTROL_T AS TABLE(
"NAME" VARCHAR(100),
"INTARGS" INTEGER,
"DOUBLEARGS" DOUBLE,
"STRINGARGS" VARCHAR (100)
);
DROP TABLE SIGNATURE;
CREATE COLUMN TABLE SIGNATURE (
"POSITION" INT,
"SCHEMA_NAME" NVARCHAR(256),
"TYPE_NAME" NVARCHAR(256),
"PARAMETER_TYPE" VARCHAR(7)
);
INSERT INTO SIGNATURE VALUES (1, 'KLEAKOCI', 'NEW_DATA', 'IN');
INSERT INTO SIGNATURE VALUES (2, 'KLEAKOCI', 'CONTROL_T', 'IN');
INSERT INTO SIGNATURE VALUES (3, 'KLEAKOCI', 'RESULT_T', 'OUT');
INSERT INTO SIGNATURE VALUES (4, 'KLEAKOCI', 'MODEL', 'OUT');
CALL "SYS".AFLLANG_WRAPPER_PROCEDURE_DROP('KLEAKOCI', 'APRIORIPROC');
CALL "SYS".AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'APRIORIRULE',
'KLEAKOCI', 'APRIORIPROC', "KLEAKOCI"."SIGNATURE");
DROP TABLE PAL_APRIORI;
CREATE COLUMN TABLE PAL_APRIORI LIKE NEW_DATA;
INSERT INTO PAL_APRIORI
SELECT "customerNumber", "orderCode"
FROM "assetCentral"."asset"
WHERE "customerNumber" IS NOT NULL AND "orderCode" IS NOT NULL
GROUP BY "customerNumber", "orderCode"
HAVING COUNT(*) = 1
ORDER BY "customerNumber";
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 ('MIN_SUPPORT', null, 0.5, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('MIN_CONFIDENCE', null, 0.3, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('MIN_LIFT', null, 1.1, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('MAX_CONSEQUENT', 1, null, null);
DROP TABLE PAL_APRIORI_RESULT_TBL;
CREATE COLUMN TABLE PAL_APRIORI_RESULT_TBL LIKE RESULT_T;
DROP TABLE PAL_APRIORI_PMMLMODEL_TBL;
CREATE COLUMN TABLE PAL_APRIORI_PMMLMODEL_TBL LIKE MODEL;
CALL "KLEAKOCI"."APRIORIPROC"
("KLEAKOCI"."PAL_APRIORI",
"KLEAKOCI"."#PAL_CONTROL_TBL",
"KLEAKOCI"."PAL_APRIORI_RESULT_TBL",
"KLEAKOCI"."PAL_APRIORI_PMMLMODEL_TBL")
WITH overview;
SELECT * FROM "KLEAKOCI".PAL_APRIORI_RESULT_TBL;
SELECT * FROM "KLEAKOCI".PAL_APRIORI_PMMLMODEL_TBL;
Solved :
Set the support and confidence very low,since there are over 2 million records in the input table.