Output tables in Kmeans

[Frengo]

Hi, I have a P-user and I'm working with the free version of HANA Cloud
The area of focus is clustering with K-means.
Following the documentation, I created the input and output tables.
I then call the function:

CALL _SYS_AFL.PAL_KMEANS

The result is the following error: Could not execute 'CALL _SYS_AFL.PAL_KMEANS(
DATA_INPUT_ENCODED_CLIENTI_ENERGIA,
PARAMETERS, ...'
Error: (dberror) [2048]: column store error: search table error: [2620] executor: plan operation failed;Can not insert data from temp table "H00::DEV-BA-PAL:P3_EECB960D20BCAA4CA32A5090F32BAE6A (t -1)" into table ""DEV-BA-PAL"."OUTPUT_RESULT_ASSIGNMENTS"".

Is there any limitation of the use of K-means in the free version of HANA Cloud?

I appreciate your support

Francesco

[Xuanqi_Liu]

Hi Francesco,

The free version of K-Means does not have any usage restrictions. This issue might be related to the dataset.

Could you provide more detail information about data set? I would help us better analyze this issue.

B&R,

Michael

[Frengo]

@ChristophMorgen could you please provide me with more information on this? How can I delete temporary tables if they are not saved?

[ChristophMorgen]

@Frengo, it is somewhat not transparent to me / the community how your app / scenarios looks in detail, which tools you use etc.

From the error code you shared, one idea about you problem is that in your procedure code, you use references to temporary tables which might not exist anymore by the time you execute the procedure: Can not insert data from temp table

[Frengo]

the complete code, in addition to what has already been reported, contains only the creation of the output tables. There are no references to temporary tables

[Frengo]

Thanks for your help @Xuanqi_Liu, I share the code of the sample dataset I'm using. The input table for KMeans is the encoded table: 

CREATE COLUMN TABLE DATA_INPUT_CLIENTI_ENERGIA (

"ID" INTEGER,

"NOME" NVARCHAR (50),

"RANGE_ETA" NVARCHAR (10),

"REGIONE" NVARCHAR (30),

"AREA_GEOGRAFICA" NVARCHAR (20),

"CONSUMO_KWH_TOTALE" DECIMAL (6,2),

"CONSUMO_KWH_F1" DECIMAL (6,2),

"CONSUMO_KWH_F2" DECIMAL (6,2), 

"CONSUMO_KWH_F3" DECIMAL (6,2),

"COSTO_KWH" DECIMAL (4,3),

"TIPO_USO" NVARCHAR (20)

);

DO BEGIN

    DECLARE i INT = 1;

    DECLARE totale DECIMAL(6,2);

    DECLARE f1 DECIMAL(6,2);

    DECLARE f2 DECIMAL(6,2);

    DECLARE f3 DECIMAL(6,2);

    DECLARE max_f2_perc DECIMAL(5,4);

    DECLARE f1_perc DECIMAL(5,4);

    DECLARE f2_perc DECIMAL(5,4);

    DECLARE regione VARCHAR(50);

    DECLARE area_geografica VARCHAR(20);

    WHILE :i <= 10000 DO

        totale := ROUND(500 + RAND() * 4500, 2);  -- Totale tra 500 e 5000

        f1_perc := 0.30 + RAND() * 0.20;

        f1 := ROUND(totale * f1_perc, 2);

        max_f2_perc := LEAST(0.50, 1.00 - f1_perc);  -- max 50%, ma non oltre 100%-F1

        f2_perc := 0.30 + RAND() * (max_f2_perc - 0.30);

        f2 := ROUND(totale * f2_perc, 2);

        f3 := ROUND(totale - f1 - f2, 2);

        regione := CASE MOD(:i, 20)

            WHEN 0 THEN 'Lombardia'

            WHEN 1 THEN 'Lazio'

            WHEN 2 THEN 'Campania'

            WHEN 3 THEN 'Sicilia'

            WHEN 4 THEN 'Veneto'

            WHEN 5 THEN 'Emilia-Romagna'

            WHEN 6 THEN 'Piemonte'

            WHEN 7 THEN 'Puglia'

            WHEN 8 THEN 'Toscana'

            WHEN 9 THEN 'Calabria'

            WHEN 10 THEN 'Sardegna'

            WHEN 11 THEN 'Liguria'

            WHEN 12 THEN 'Marche'

            WHEN 13 THEN 'Abruzzo'

            WHEN 14 THEN 'Friuli-Venezia Giulia'

            WHEN 15 THEN 'Trentino-Alto Adige'

            WHEN 16 THEN 'Umbria'

            WHEN 17 THEN 'Basilicata'

            WHEN 18 THEN 'Molise'

            ELSE 'Valle d Aosta'

        END;

        area_geografica := CASE

            WHEN regione IN ('Piemonte', 'Lombardia', 'Veneto', 'Emilia-Romagna', 'Liguria', 'Friuli-Venezia Giulia', 

            'Trentino-Alto Adige', 'Valle d Aosta') THEN 'Nord'

            WHEN regione IN ('Toscana', 'Marche', 'Lazio', 'Umbria', 'Abruzzo') THEN 'Centro'

            WHEN regione IN ('Campania', 'Puglia', 'Molise', 'Basilicata', 'Calabria') THEN 'Sud'

            WHEN regione IN ('Sicilia', 'Sardegna') THEN 'Isole'

            ELSE 'Altro'

        END;

        INSERT INTO DATA_INPUT_CLIENTI_ENERGIA (

            ID, 

            NOME, 

            RANGE_ETA, 

            REGIONE, 

            AREA_GEOGRAFICA,

            CONSUMO_KWH_TOTALE, 

            CONSUMO_KWH_F1, 

            CONSUMO_KWH_F2, 

            CONSUMO_KWH_F3,

            COSTO_KWH, 

            TIPO_USO

        )

        VALUES (

            :i,

            'cliente_' || :i,

            CASE MOD(:i, 5)

                WHEN 0 THEN '18-25'

                WHEN 1 THEN '26-35'

                WHEN 2 THEN '36-45'

                WHEN 3 THEN '46-60'

                ELSE '60+'

            END,

            :regione,

            :area_geografica,

            :totale,

            :f1,

            :f2,

            :f3,

            ROUND(0.150 + RAND() * 0.150, 3),

            CASE MOD(:i, 4)

                WHEN 0 THEN 'Residenziale'

                WHEN 1 THEN 'Industriale'

                WHEN 2 THEN 'Commerciale'

                ELSE 'Agricolo'

            END

        );

        i := :i + 1;

    END WHILE;

END;

CREATE COLUMN TABLE DATA_INPUT_ENCODED_CLIENTI_ENERGIA (

"ID" INTEGER,

"NOME" NVARCHAR (50),

"RANGE_ETA_ENCODED" INTEGER,

"REGIONE_ENCODED" INTEGER,

"AREA_GEOGRAFICA_ENCODED" INTEGER,

"CONSUMO_KWH_TOTALE" DECIMAL (6,2),

"CONSUMO_KWH_F1" DECIMAL (6,2),

"CONSUMO_KWH_F2" DECIMAL (6,2),

"CONSUMO_KWH_F3" DECIMAL (6,2),

"COSTO_KWH" DECIMAL (4,3),

"TIPO_USO_ENCODED" INTEGER

);

INSERT INTO DATA_INPUT_ENCODED_CLIENTI_ENERGIA

SELECT 

ID,

nome,

CASE

WHEN range_eta = '18-25' THEN 0

WHEN range_eta = '26-35' THEN 1

WHEN range_eta = '36-45' THEN 2

WHEN range_eta = '46-60' THEN 3

WHEN range_eta = '60+' THEN 4

ELSE 5

END AS range_eta_encoded,

CASE

WHEN regione = 'Lombardia' THEN 0

WHEN regione = 'Lazio' THEN 1

WHEN regione = 'Campania' THEN 2

WHEN regione = 'Sicilia' THEN 3

WHEN regione = 'Veneto' THEN 4

WHEN regione = 'Emilia-Romagna' THEN 5

WHEN regione = 'Piemonte' THEN 6

WHEN regione = 'Puglia' THEN 7

WHEN regione = 'Toscana' THEN 8

WHEN regione = 'Calabria' THEN 9

WHEN regione = 'Sardegna' THEN 10

WHEN regione = 'Liguria' THEN 11

WHEN regione = 'Marche' THEN 12

WHEN regione = 'Abruzzo' THEN 13

WHEN regione = 'Friuli-Venezia Giulia' THEN 14

WHEN regione = 'Trentino-Alto Adige' THEN 15

WHEN regione = 'Umbria' THEN 16

WHEN regione = 'Basilicata' THEN 17

WHEN regione = 'Molise' THEN 18

ELSE 19

END AS regione_encoded,

CASE

WHEN area_geografica = 'Nord' THEN 0

WHEN area_geografica = 'Centro' THEN 1

WHEN area_geografica = 'Sud' THEN 2

WHEN area_geografica = 'Isole' THEN 4

ELSE 5

END AS area_geografica_encoded,

CONSUMO_KWH_TOTALE,

CONSUMO_KWH_F1,

CONSUMO_KWH_F2,

CONSUMO_KWH_F3,

COSTO_KWH,

CASE

WHEN tipo_uso = 'Residenziale' THEN 0

WHEN tipo_uso = 'Industriale' THEN 1

WHEN tipo_uso = 'Agricolo' THEN 2

WHEN tipo_uso = 'Commerciale' THEN 3

ELSE 4

END AS tipo_uso_encoded

FROM DATA_INPUT_CLIENTI_ENERGIA;

I look forward for your reply, thank you

[ChristophMorgen]

@Frengo, your data example with KMEANS just works fine with HANA Cloud trial. your problem may derive from something else, e.g. temporary tables already deleted ...