Scoring Functions inside the Greenplum Database

Scoring Function Names

The names of the scoring functions that are built in Greenplum have the following format:

modelname_EM_outputvarname

modelname is the name that was specified in the MODELNAME argument of the %INDGP_PUBLISH_MODEL macro. modelname is always followed by _EM_ in the scoring function name. For more information about the MODELNAME argument, see %INDGP_PUBLISH_MODEL Macro Syntax.

outputvarname is derived from the names of the EM_ output variables in the score.xml file that is generated from the SAS Enterprise Miner Score Code Export node. For more information about the score.xml file, see Fixed Variable Names.

One scoring function is created for each EM_ output variable in the score.xml file. For example, if the scoring model DATA step program takes ten inputs and creates three new variables, then three scoring functions are defined. Each scoring function has the name of an output variable. For example, if you set MODELNAME=credit in the %INDGP_PUBLISH_MODEL macro and the EM_ output variables are “EM_PREDICTION”, “EM_PROBABILITY”, and “EM_DECISION”, then the name of the scoring functions that are created would be “credit_EM_PREDICTION”, “credit_EM_PROBABILITY”, and “credit_EM_DECISION”.

Note: A scoring function name cannot exceed 63 characters.

CAUTION:

When the scoring function is generated, the names are case insensitive.

Consequently, if you have model names “Model01” and “model01”, and you create two scoring functions, the second scoring function overwrites the first scoring function.

Using the Scoring Functions

The scoring functions are available to use in any SQL expression in the same way that Greenplum built-in functions are used. For an example, see Model Publishing Macro Example.

Tip

In Greenplum, character variables have a length of 32K. If you create an output table or data set to hold the scored rows, it is recommended that you create the table and define the variables. Here is an example.

proc sql noerrorstop;
connect to greenplum (<connection options>);
execute (create table scoretab (
   ID                    integer 
   , EM_SEGMENT            float
   , EM_EVENTPROBABILITY   float
   , EM_PROBABILITY        float
   , EM_CLASSIFICATION     varchar (32)
   )
   distributed by (id)
) by greenplm;
execute ( insert into scoretab 
select id,
function prefix_EM_SEGMENT (
   comma-delimited input column list
   ) as "EM_ SEGMENT",
function prefix_EM_EVENTPROBABILITY (
   comma-delimited input column list
   ) as "EM_EVENTPROBABILITY",
function prefix_EM_PROBABILITY (
   comma-delimited input column list
   ) as "EM_PROBABILITY"
cast(function prefix_EM_CLASSIFICATION (
   comma-delimited input column list
   ) as varchar(32)) as "EM_CLASSIFICATION",
from scoring_input_table  
   order by id
) by greenplm;
quit;

There are four ways to see the scoring functions that are created:

From Greenplum, start psql to connect to the database and submit an SQL statement. In this example, 'SCHEMA' is the actual schema value.
```
psql -h hostname -d databasename -U userid
select proname
   from pg_catalog.pg_proc f, pg_catalog.pg_namespace s
   where f.pronamespace=s.oid and upper(s.nspname)='SCHEMA';
```

From SAS, use SQL procedure code that produces output in the LST file. The following example assumes that the model name that you used to create the scoring functions is mymodel.

proc sql noerrorstop;
  connect to greenplm (user=username pw=password dsn= dsnname);
  
select *
   from connection to greenplm
      (select proname
       from pg_catalog.pg_proc f, pg_catalog.pg_namespace s
       where f.pronamespace=s.oid and upper(s.nspname)='SCHEMA');
   disconnect from greenplm;
quit;

Look at the SampleSQL.txt file that is produced when the %INDGP_PUBLISH_MODEL macro is successfully run. This file can be found in the output directory (OUTDIR argument) that you specify in the macro.

The SampleSQL.txt file contains basic code that, with modifications, can be used to run your score code inside Greenplum.

For example, the SampleSQL.txt file refers to an ID column in allmush1_intab that is populated with a unique integer from 1 to n, with n being the number of rows in the table. The ID column uniquely identifies each row. You would replace the ID column with your own primary key column.

Note: The function and table names must be fully qualified if the function and table are not in the same schema.

The following example assumes that the model name that you used to create the scoring functions is allmush1.
```
drop table allmush1_outtab;
create table allmush1_outtab(
 id integer
,"EM_CLASSIFICATION" varchar(33)
,"EM_EVENTPROBABILITY" float
,"EM_PROBABILITY" float
);
insert into allmush1_outtab(
 id
,"EM_CLASSIFICATION"
,"EM_EVENTPROBABILITY"
,"EM_PROBABILITY"
)
select id,
 allmush1_em_classification("BRUISES"
,"CAPCOLOR"
,"GILLCOLO"
,"GILLSIZE"
,"HABITAT"
,"ODOR"
,"POPULAT"
,"RINGNUMB"
,"RINGTYPE"
,"SPOREPC"
,"STALKCBR"
,"STALKROO"
,"STALKSAR"
,"STALKSHA"
,"VEILCOLO")
  as "EM_CLASSIFICATION",
 allmush1_em_eventprobability("BRUISES"
,"CAPCOLOR"
,"GILLCOLO"
,"GILLSIZE"
,"HABITAT"
,"ODOR"
,"POPULAT"
,"RINGNUMB"
,"RINGTYPE"
,"SPOREPC"
,"STALKCBR"
,"STALKROO"
,"STALKSAR"
,"STALKSHA"
,"VEILCOLO")
  as "EM_EVENTPROBABILITY",
 allmush1_em_probability("BRUISES"
,"CAPCOLOR"
,"GILLCOLO"
,"GILLSIZE"
,"HABITAT"
,"ODOR"
,"POPULAT"
,"RINGNUMB"
,"RINGTYPE"
,"SPOREPC"
,"STALKCBR"
,"STALKROO"
,"STALKSAR"
,"STALKSHA"
,"VEILCOLO")
  as "EM_PROBABILITY"
from allmush1_intab ;
```
You can look at the SAS log that is created when the %INDGP_PUBLISH_MODEL macro was run. A message that indicates whether a scoring function is successfully or not successfully executed is printed to the SAS log.