Fork me on GitHub

通過AMDP調用HANA的PAL函數

SAP預測分析庫(SAP Predictive Analysis Library,PAL)是SAP HANA中的一項功能,它允許我們在SAP HANA SQLScript過程中執行分析算法。

基于ABAP的SAP應用可以調用PAL提供的功能,包含分類,回歸,聚類,關聯規則,社交網絡分析,推薦系統等。通常使用AMDP來實現調用。

AMDP(ABAP-Managed Database Procedures)是一種在SAP HANA中進行ABAP開發時可以使用的代碼優化模式,簡而言之,它可以讓開發者在ABAP中寫HANA數據庫存儲過程。

 

本文鏈接:http://www.jsfhjj.com/hhelibeb/p/12610644.html

英文原文:An example to call PAL Apriori via AMDP

示例

接下來用一個例子來展示如何使用PAL。這里用到的PAL函數是Apriori

步驟一(可選) 熟悉使用SQLScript調用PAL函數

如果你已經熟悉PAL的HANA存儲過程接口和它的調用,可以跳過這步。

通過HANA Studio連接HANA數據庫,運行下面的腳本:

SET SCHEMA ZHAOJE;

DROP TABLE PAL_APRIORI_PARAMETER_TBL;
CREATE COLUMN TABLE PAL_APRIORI_PARAMETER_TBL (
“PARAM_NAME ” VARCHAR(100),
“INT_VALUE” INTEGER,
“DOUBLE_VALUE” DOUBLE,
“STRING_VALUE” VARCHAR (100)
);
INSERT INTO PAL_APRIORI_PARAMETER_TBL VALUES (‘MIN_SUPPORT’, null, 0.1, null);
INSERT INTO PAL_APRIORI_PARAMETER_TBL VALUES (‘MIN_CONFIDENCE’, null, 0.3, null);
INSERT INTO PAL_APRIORI_PARAMETER_TBL VALUES (‘MIN_LIFT’, null, 1.1, null);
INSERT INTO PAL_APRIORI_PARAMETER_TBL VALUES (‘MAX_CONSEQUENT’, 1, null, null);
INSERT INTO PAL_APRIORI_PARAMETER_TBL VALUES (‘PMML_EXPORT’, 1, null, null);

DROP TABLE PAL_APRIORI_TRANS_TBL;
CREATE COLUMN TABLE PAL_APRIORI_TRANS_TBL (
“CUSTOMER” INTEGER,
“ITEM” VARCHAR(20)
);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (2, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (2, ‘item3’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (3, ‘item1’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (3, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (3, ‘item4’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (4, ‘item1’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (4, ‘item3’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (5, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (5, ‘item3’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (6, ‘item1’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (6, ‘item3’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (0, ‘item1’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (0, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (0, ‘item5’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (1, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (1, ‘item4’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (7, ‘item1’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (7, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (7, ‘item3’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (7, ‘item5’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (8, ‘item1’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (8, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (8, ‘item3’);

CALL _SYS_AFL.PAL_APRIORI(PAL_APRIORI_TRANS_TBL, PAL_APRIORI_PARAMETER_TBL, ?, ?);

你會看到下面的挖掘結果,

步驟二 寫AMDP代碼,調用PAL過程

下面是一個AMDP類例子,它調用了PAL Apriori過程。你可以在Eclipse里編輯自己的AMDP代碼。

CLASS zcl_amdp_pal DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .PUBLIC SECTION.
  INTERFACES if_amdp_marker_hdb.
  TYPES: BEGIN OF ty_apdata,
           customer TYPE i,
           item     TYPE c LENGTH 10,
         END OF ty_apdata,
         tt_apdata TYPE STANDARD TABLE OF ty_apdata,

         BEGIN OF ty_apparams,
           name       TYPE c LENGTH 60,
           intargs    TYPE i,
           doubleargs TYPE float,
           stringargs TYPE c LENGTH 100,
         END OF ty_apparams,
         tt_apparams TYPE STANDARD TABLE OF ty_apparams,

         ty_metric   TYPE p LENGTH 5 DECIMALS 4,
         BEGIN OF ty_aprules,
           antecedent TYPE c LENGTH 20,
           consequent TYPE c LENGTH 10,
           support    TYPE ty_metric,
           confidence TYPE ty_metric,
           lift       TYPE ty_metric,
         END OF ty_aprules,
         tt_aprules TYPE STANDARD TABLE OF ty_aprules,

         BEGIN OF ty_appmml,
           row_index     TYPE i,
           model_content TYPE c LENGTH 500,
         END OF ty_appmml,
         tt_appmml TYPE STANDARD TABLE OF ty_appmml.

  METHODS apriori_proc_call
    IMPORTING
      VALUE(it_vapdata)  TYPE tt_apdata
      VALUE(it_apparams) TYPE tt_apparams
    EXPORTING
      VALUE(et_ap_rules) TYPE tt_aprules
      VALUE(et_ap_pmml)  TYPE tt_appmml.

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.

CLASS zcl_amdp_pal IMPLEMENTATION.

  METHOD apriori_proc_call BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT.
    CALL _SYS_AFL.PAL_APRIORI(:it_vapdata, :it_apparams, et_ap_rules, et_ap_pmml);
  ENDMETHOD.

ENDCLASS.

步驟三 在ABAP程序中調用AMDP方法

示例報表代碼如下,

REPORT zz_apriori_test.

DATA: lt_data    TYPE zcl_amdp_pal=>tt_apdata,
      ls_data    LIKE LINE OF lt_data,
      lt_param   TYPE zcl_amdp_pal=>tt_apparams,
      ls_param   LIKE LINE OF lt_param,
      lt_rules   TYPE zcl_amdp_pal=>tt_aprules,
      lt_pmml    TYPE zcl_amdp_pal=>tt_appmml,
      lr_wrapper TYPE REF TO zcl_amdp_pal.

ls_data-customer = 2. ls_data-item = 'item2'. APPEND ls_data TO lt_data.
ls_data-customer = 2. ls_data-item = 'item3'. APPEND ls_data TO lt_data.
ls_data-customer = 3. ls_data-item = 'item1'. APPEND ls_data TO lt_data.
ls_data-customer = 3. ls_data-item = 'item2'. APPEND ls_data TO lt_data.
ls_data-customer = 3. ls_data-item = 'item4'. APPEND ls_data TO lt_data.
ls_data-customer = 4. ls_data-item = 'item1'. APPEND ls_data TO lt_data.
ls_data-customer = 4. ls_data-item = 'item3'. APPEND ls_data TO lt_data.
ls_data-customer = 5. ls_data-item = 'item2'. APPEND ls_data TO lt_data.
ls_data-customer = 5. ls_data-item = 'item3'. APPEND ls_data TO lt_data.
ls_data-customer = 6. ls_data-item = 'item1'. APPEND ls_data TO lt_data.
ls_data-customer = 6. ls_data-item = 'item3'. APPEND ls_data TO lt_data.
ls_data-customer = 0. ls_data-item = 'item1'. APPEND ls_data TO lt_data.
ls_data-customer = 0. ls_data-item = 'item2'. APPEND ls_data TO lt_data.
ls_data-customer = 0. ls_data-item = 'item5'. APPEND ls_data TO lt_data.
ls_data-customer = 1. ls_data-item = 'item2'. APPEND ls_data TO lt_data.
ls_data-customer = 1. ls_data-item = 'item4'. APPEND ls_data TO lt_data.
ls_data-customer = 7. ls_data-item = 'item1'. APPEND ls_data TO lt_data.
ls_data-customer = 7. ls_data-item = 'item2'. APPEND ls_data TO lt_data.
ls_data-customer = 7. ls_data-item = 'item3'. APPEND ls_data TO lt_data.
ls_data-customer = 7. ls_data-item = 'item5'. APPEND ls_data TO lt_data.
ls_data-customer = 8. ls_data-item = 'item1'. APPEND ls_data TO lt_data.
ls_data-customer = 8. ls_data-item = 'item2'. APPEND ls_data TO lt_data.
ls_data-customer = 8. ls_data-item = 'item3'. APPEND ls_data TO lt_data.

CLEAR ls_param. ls_param-name = 'THREAD_NUMBER'. ls_param-intargs = 2. APPEND ls_param TO lt_param.
CLEAR ls_param. ls_param-name = 'MIN_SUPPORT'. ls_param-doubleargs = '0.1'. APPEND ls_param TO lt_param.
CLEAR ls_param. ls_param-name = 'MIN_CONFIDENCE'. ls_param-doubleargs = '0.3'. APPEND ls_param TO lt_param.
CLEAR ls_param. ls_param-name = 'MIN_LIFT'. ls_param-doubleargs = '1.1'. APPEND ls_param TO lt_param.
CLEAR ls_param. ls_param-name = 'MAX_CONSEQUENT'. ls_param-intargs = 1. APPEND ls_param TO lt_param.

CREATE OBJECT lr_wrapper.
CALL METHOD lr_wrapper->apriori_proc_call
  EXPORTING
    it_vapdata  = lt_data
    it_apparams = lt_param
  IMPORTING
    et_ap_rules = lt_rules
    et_ap_pmml  = lt_pmml.

TRY.
    cl_salv_table=>factory(
    IMPORTING
    r_salv_table = DATA(lr_table)
    CHANGING
    t_table = lt_rules ).
    DATA(lr_functions) = lr_table->get_functions( ).
    lr_functions->set_default( abap_true ).
    DATA(lr_columns) = lr_table->get_columns( ).
    DATA(lr_column_1) = lr_columns->get_column('ANTECEDENT').
    lr_column_1->set_long_text('ANTECEDENT'). lr_column_1->set_medium_text('ANTECEDENT'). lr_column_1->set_short_text('ANTECEDENT').
    DATA(lr_column_2) = lr_columns->get_column('CONSEQUENT').
    lr_column_2->set_long_text('CONSEQUENT'). lr_column_2->set_medium_text('CONSEQUENT'). lr_column_2->set_short_text('CONSEQUENT').
    DATA(lr_column_3) = lr_columns->get_column('SUPPORT').
    lr_column_3->set_long_text('SUPPORT' ). lr_column_3->set_medium_text('SUPPORT'). lr_column_3->set_short_text('SUPPORT').
    DATA(lr_column_4) = lr_columns->get_column('CONFIDENCE').
    lr_column_4->set_long_text('CONFIDENCE'). lr_column_4->set_medium_text('CONFIDENCE'). lr_column_4->set_short_text('CONFIDENCE').
    DATA(lr_column_5) = lr_columns->get_column('LIFT').
    lr_column_5->set_long_text('LIFT'). lr_column_5->set_medium_text('LIFT'). lr_column_5->set_short_text('LIFT').
    lr_table->display( ).
  CATCH cx_salv_msg.                                    "#EC NO_HANDLER
  CATCH cx_salv_not_found.                              "#EC NO_HANDLER
ENDTRY.

成功執行后,可以看到如下的執行結果:

 

 可以在相關的應用中使用這些結果。

 
 
 
 
 
posted @ 2020-04-01 11:14  氫氦  閱讀(...)  評論(...編輯  收藏
最新chease0ldman老人