sqlhanaapriori

Why isn't my result table showing any results when using APRIORI function of PAL in HANA?


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;

Solution

  • Solved :

    Set the support and confidence very low,since there are over 2 million records in the input table.