oml4sql-feature-extraction-text-mining-esa.sql

-----------------------------------------------------------------------
-- Oracle Machine Learning for SQL (OML4SQL) 23ai
--
-- Feature Extraction - ESA Algorithm for Text Mining
--
-- Copyright (c) 2024 Oracle Corporation and/or its affilitiates.
--
-- The Universal Permissive License (UPL), Version 1.0
--
-- https://oss.oracle.com/licenses/upl/
-----------------------------------------------------------------------

-----------------------------------------------------------------------
-- Prerequisites
-----------------------------------------------------------------------

-- Install the Wiki ESA model
-- https://oss.oracle.com/machine-learning/
-- Run the dmsh.sql script 
-- https://github.com/oracle-samples/oracle-db-examples/blob/main/machine-learning/sql/23ai/dmsh.sql

-----------------------------------------------------------------------
-- EXAMPLES IN THIS SCRIPT
-----------------------------------------------------------------------
-- Create an ESA model with CREATE_MODEL PL/SQL procedure, which uses a
-- separate settings table as input. 

-- Walk thorugh 3 ESA use cases with the model

-- Create an ESA model with CREATE_MODEL2, which provides settings
-- as part of the procedure. 

-- (23ai Feature) Create an ESA model with dense projections, which is 
-- similar to a doc2vec approach, by specifying the ESAS_EMBEDDINGS 
-- parameter as ESAS_EMBEDDINGS_ENABLED.

-- (23ai Feature) Use the dense projection scoring results to create a 
-- clustering model. You can use such projections to improve the quality 
-- of, e.g., classification and clustering models - a common use case 
-- for dense projections.
-----------------------------------------------------------------------


SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

SET serveroutput ON
SET pages 10000

-----------------------------------------------------------------------
-- SET UP AND ANALYZE THE DATA
-----------------------------------------------------------------------
-- Create a policy for text feature extraction
-- The policy will include stemming
BEGINctx_ddl.drop_policy('dmdemo_esa_policy');
EXCEPTION when others then null; END;
/
BEGINctx_ddl.drop_preference('dmdemo_esa_lexer');
EXCEPTION when others then null; END;
/
BEGIN
ctx_ddl.create_preference('dmdemo_esa_lexer', 'BASIC_LEXER');
ctx_ddl.set_attribute('dmdemo_esa_lexer', 'index_stems', 'ENGLISH');
-- ctx_ddl.set_attribute('dmdemo_esa_lexer', 'index_themes', 'YES');
END;
/
BEGIN
ctx_ddl.create_policy('dmdemo_esa_policy', lexer=>'dmdemo_esa_lexer');
END;
/

-----------------------------------------------------------------------
-- BUILD THE MODEL
-----------------------------------------------------------------------

-- Cleanup old model and objects for repeat runs
BEGINDBMS_DATA_MINING.DROP_MODEL('ESA_text_sample');
EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE ESA_text_sample_settings';
EXCEPTION WHEN OTHERS THEN NULL; END;
/

-- Create a build data view with case ID and text column
CREATE OR REPLACEVIEWmining_build_text_parallelAS
SELECT/*+ parallel (4)*/min(cust_id) as cust_id, comments
FROM mining_build_text
WHERE length(comments) >=70
GROUP BY comments;

-- Create settings table to choose text policy and auto data prep
CREATETABLEESA_text_sample_settings (
 setting_name VARCHAR2(30),
 setting_value VARCHAR2(4000));

BEGIN
-- Populate settings table
INSERT INTO ESA_text_sample_settings VALUES
 (dbms_data_mining.algo_name, dbms_data_mining.algo_explicit_semantic_analys);
INSERT INTO ESA_text_sample_settings VALUES
 (dbms_data_mining.prep_auto, dbms_data_mining.prep_auto_on);
INSERT INTO ESA_text_sample_settings VALUES
 (dbms_data_mining.odms_text_policy_name, 'DMDEMO_ESA_POLICY');
-- lower than the default value of 100 due to the small size of data
INSERT INTO ESA_text_sample_settings VALUES
 ('ESAS_MIN_ITEMS', 5);
-- lower than the default value of 3 due to the small size of data
INSERT INTO ESA_text_sample_settings VALUES
 ('ODMS_TEXT_MIN_DOCUMENTS', 2);
--('ODMS_TEXT_MAX_FEATURES', 10000);
--('ESAS_TOPN_FEATURES', 500);
--('ESAS_VALUE_THRESHOLD', 0.0001);
COMMIT;
END;
/

---------------------
-- CREATE A NEW MODEL
-- Note that the transform makes the column 'comments'
-- to be treated as unstructured text data
--
DECLARE
 xformlist dbms_data_mining_transform.TRANSFORM_LIST;
BEGIN
dbms_data_mining_transform.SET_TRANSFORM(
 xformlist, 'comments', null, 'comments', 'comments',
-- 'TEXT(POLICY_NAME:DMDEMO_ESA_POLICY)(TOKEN_TYPE:STEM)');
'TEXT(POLICY_NAME:DMDEMO_ESA_POLICY)(TOKEN_TYPE:THEME)');

DBMS_DATA_MINING.CREATE_MODEL(
 model_name =>'ESA_text_sample',
 mining_function =>dbms_data_mining.feature_extraction,
 data_table_name =>'mining_build_text_parallel',
 case_id_column_name =>'cust_id',
 settings_table_name =>'ESA_text_sample_settings',
 xform_list => xformlist);
END;
/

-------------------------
-- DISPLAY MODEL SETTINGS
--
column setting_name format a30;
column setting_value format a30;
column setting_type format a10;

SELECT setting_name, setting_value, setting_type AS type
FROM user_mining_model_settings
WHERE model_name ='ESA_TEXT_SAMPLE'
ORDER BY setting_name;

--------------------------
-- DISPLAY MODEL SIGNATURE
--
column attribute_name format a30
column attribute_type format a20
column data_type format a20

SELECT attribute_name, attribute_type, data_type
FROM user_mining_model_attributes
WHERE model_name='ESA_TEXT_SAMPLE'
ORDER BY attribute_name;


-- Get a list of model views
col view_name format a30
col view_type format a50

SELECT view_name, view_type
FROM user_mining_model_views
WHERE model_name='ESA_TEXT_SAMPLE'
ORDER BY view_name;

-----------------------------------------------------------------------
-- APPLY THE MODEL
-----------------------------------------------------------------------
--
-- Unlike other feature extraction functions, ESA does not discover new features. 
-- It treats the rows of the training data as predefined features. 
-- Test data are expressed via these pre-defined features as a basis.
--

-------------------------------------------------
-- SCORE NEW DATA USING SQL FUNCTIONS
--
------------------
-- BUSINESS CASE 1
-- List the customer comments (features) most relevant to the input text.
-- The comments are sorted according to their relevancy.
--
column value format 9.99999
column comments format a200

SELECT cust_id, 
1-feature_compare(ESA_TEXT_SAMPLE 
 using 'card discount' comments AND
 using comments) similarity,
 comments 
FROM mining_build_text_parallel
ORDER BY similarity desc;

-- Another similar example
SELECT cust_id, 
1-feature_compare(ESA_TEXT_SAMPLE 
 using 'computer manual' comments AND
 using comments) similarity,
 comments 
FROM mining_build_text_parallel
ORDER BY similarity DESC

-- Another example with longer text
-- The input is the following comment

SELECT comments
FROM mining_test_text_parallel
WHERE cust_id =103030;

-- the most relevant comments from build data
SELECTs.value, m.comments
FROM (SELECT feature_set(ESA_TEXT_SAMPLE, 5 using *) fset
FROM (SELECT comments FROM mining_test_text_parallel
WHERE cust_id =103030)) t,
 TABLE(t.fset) s, 
 mining_build_text_parallel m
WHEREs.feature_id=m.cust_id
ORDER BYs.valuedesc;

------------------
-- BUSINESS CASE 2
-- List the attributes that represent customer comments (cust_id=101613).
-- The attributes are sorted according to their coefficients.
--
column coefficient format 9.99999
column attribute_subname format a30

-- comments for cust_id=101613
SELECT comments 
FROM mining_build_text_parallel 
WHERE cust_id =101613;

-- attributes representing the comments in the model
SELECT attribute_subname, coefficient
FROM dm$vaESA_TEXT_SAMPLE
WHERE feature_id =101613
ORDER BY coefficient desc;

------------------
-- BUSINESS CASE 3
-- Compare customer comments using the model
--
-- create a test data view with case ID and text column
CREATE OR REPLACEVIEWmining_test_text_onlyAS
SELECT cust_id, comments 
FROM mining_test_text_parallel
WHERE cust_id <103005;

column comments format a50
-- test data
SELECT cust_id, comments
FROM mining_test_text_only
ORDER BY cust_id;

-- test data relatedness based on the model
-- smaller values correspond to more related data rows

column comp format 9.99999

SELECTa.cust_id id1, b.cust_id id2,
 feature_compare(ESA_TEXT_SAMPLE using a.commentsand using b.comments) comp
FROM mining_test_text_only a, mining_test_text_only b
WHEREa.cust_id<b.cust_id
ORDER BY comp;

-----------------------------------------------------------------------
-- CREATE MODEL WITH CREATE_MODEL2
-----------------------------------------------------------------------
-- This improves on the CREATE_MODEL procedure by allowing you to use a query
-- rather than a database table as input. You also no longer need to declare
-- and populate a settings table.
-- Other than using CREATE_MODEL2, this model is identical to the first one.

-- Cleanup old model and objects for repeat runs
BEGINDBMS_DATA_MINING.DROP_MODEL('ESA_text_sample2');
EXCEPTION WHEN OTHERS THEN NULL; END;
/

DECLARE
 xformlist dbms_data_mining_transform.TRANSFORM_LIST;
 v_setlst DBMS_DATA_MINING.SETTING_LIST;

BEGIN
 v_setlst('PREP_AUTO') :='ON';
 v_setlst('ALGO_NAME') :='ALGO_EXPLICIT_SEMANTIC_ANALYS';
 v_setlst('ODMS_TEXT_POLICY_NAME') :='DMDEMO_ESA_POLICY';
 v_setlst('ESAS_MIN_ITEMS') :='5';
 v_setlst('ODMS_TEXT_MIN_DOCUMENTS') :='2';

dbms_data_mining_transform.SET_TRANSFORM(
 xformlist, 'comments', null, 'comments', 'comments',
'TEXT(POLICY_NAME:DMDEMO_ESA_POLICY)(TOKEN_TYPE:STEM)');

DBMS_DATA_MINING.CREATE_MODEL2(
 model_name =>'ESA_text_sample2',
 mining_function =>'FEATURE_EXTRACTION',
 data_query =>'SELECT * FROM mining_build_text_parallel',
 case_id_column_name =>'cust_id',
 set_list => v_setlst,
 xform_list => xformlist);
END;
/

-------------------------
-- Score using the model

column value format 9.99999
column comments format a200

SELECTs.feature_id, s.value
FROM
 (SELECT cust_id, comments, feature_set(ESA_TEXT_SAMPLE2, 5 using comments) fset 
FROM mining_build_text_parallel) t,
 TABLE(t.fset) s
ORDER BYs.valuedesc;


-----------------------------------------------------------------------
-- CREATE MODEL WITH DENSE PROJECTIONS
-----------------------------------------------------------------------
-- Create another model with CREATE_MODEL2, this time specifying the
-- ESAS_EMBEDDINGS parameter and setting it to ESAS_EMBEDDINGS_ENABLE.
-- Doing so means that scoring this model will produce dense projections with
-- embeddings, similar to a doc2vec approach.

BEGINDBMS_DATA_MINING.DROP_MODEL('ESA_text_sample_dense');
EXCEPTION WHEN OTHERS THEN NULL; END;
/
DECLARE
 xformlist dbms_data_mining_transform.TRANSFORM_LIST;
 v_setlst DBMS_DATA_MINING.SETTING_LIST;

BEGIN
 v_setlst('PREP_AUTO') :='ON';
 v_setlst('ALGO_NAME') :='ALGO_EXPLICIT_SEMANTIC_ANALYS';
 v_setlst('ODMS_TEXT_POLICY_NAME') :='DMDEMO_ESA_POLICY';
 v_setlst('ESAS_MIN_ITEMS') :='5';
 v_setlst('ODMS_TEXT_MIN_DOCUMENTS') :='2';
 v_setlst('ESAS_EMBEDDINGS') :='ESAS_EMBEDDINGS_ENABLE';
 v_setlst('ESAS_EMBEDDING_SIZE') :='1024';

dbms_data_mining_transform.SET_TRANSFORM(
 xformlist, 'comments', null, 'comments', 'comments',
'TEXT(POLICY_NAME:DMDEMO_ESA_POLICY)(TOKEN_TYPE:STEM)');

DBMS_DATA_MINING.CREATE_MODEL2(
 model_name =>'ESA_text_sample_dense',
 mining_function =>'FEATURE_EXTRACTION',
 data_query =>'SELECT * FROM mining_build_text_parallel',
 case_id_column_name =>'cust_id',
 set_list => v_setlst,
 xform_list => xformlist);
END;
/


-------------------------
-- DISPLAY MODEL SETTINGS
-- Note the ESAS_EMBEDDINGS and ESAS_EMBEDDINGS_SIZE settings.
--
column setting_name format a30;
column setting_value format a30;
column setting_type format a10;

SELECT setting_name, setting_value, setting_type AS type
FROM user_mining_model_settings
WHERE model_name ='ESA_TEXT_SAMPLE_DENSE'
ORDER BY setting_name;

--------------------------
-- DISPLAY MODEL SIGNATURE
--
column attribute_name format a30
column attribute_type format a20
column data_type format a20

SELECT attribute_name, attribute_type, data_type
FROM user_mining_model_attributes
WHERE model_name='ESA_TEXT_SAMPLE_DENSE'
ORDER BY attribute_name;

--------------------------
-- Get a list of model views
--
col view_name format a30
col view_type format a50

SELECT view_name, view_type
FROM user_mining_model_views
WHERE model_name='ESA_TEXT_SAMPLE_DENSE'
ORDER BY view_name;

-------------------------
-- STORE SCORING RESULTS IN TABLE
--

BEGIN EXECUTE IMMEDIATE 'DROP TABLE esa_dense_results';
EXCEPTION WHEN OTHERS THEN NULL; END;
/
column value format 9.99999
column comments format a200

CREATETABLEesa_dense_resultsAS
SELECT cust_id, s.feature_id, s.value, comments 
FROM (SELECT cust_id, comments,
 feature_set(ESA_TEXT_SAMPLE_DENSE using comments) fset 
FROM mining_build_text_parallel) t,
 TABLE(t.fset) s;

-----------------------------------------------------------------------
-- PASS PROJECTIONS INTO CLUSTERING MODEL
-----------------------------------------------------------------------
-- A common use case for the dense projections created by the scoring results
-- of the model above is to use them to train other models, such as
-- classification or clustering. The expectation is that the addition of these
-- results may improve model accuracy.
-- Here we create a clustering model using the dense projections.

-------------------------
-- Prepare ESA dense results to create clustering model
--

CREATE OR REPLACEVIEWesa_dense_results_vAS
SELECT cust_id, 
 comments,
 CAST(COLLECT(dm_nested_numerical(feature_id, value))
AS dm_nested_numericals) AS nest
FROM esa_dense_results
GROUP BY cust_id, comments;

---------------------------
-- CREATE CLUSTERING MODEL
-- Use the table augmented with the dense projection to create a
-- clustering model

BEGINDBMS_DATA_MINING.DROP_MODEL('CLUSTERING_EXAMPLE');
EXCEPTION WHEN OTHERS THEN NULL; END;
/

DECLARE
 xformlist DBMS_DATA_MINING_TRANSFORM.TRANSFORM_LIST;
 v_setlst DBMS_DATA_MINING.SETTING_LIST;

BEGIN
 v_setlst('PREP_AUTO') :='ON';
 v_setlst('ALGO_NAME') :='ALGO_KMEANS';
 v_setlst('KMNS_DETAILS') :='KMNS_DETAILS_HIERARCHY';

DBMS_DATA_MINING.CREATE_MODEL2(
 model_name =>'CLUSTERING_EXAMPLE',
 mining_function =>'CLUSTERING',
 data_query =>'SELECT * FROM esa_dense_results_v',
 case_id_column_name =>'cust_id',
 set_list =>v_setlst);
END;
/

-------------------------
-- DISPLAY MODEL SETTINGS
--
column setting_name format a30
column setting_value format a30

SELECT setting_name, setting_value
FROM user_mining_model_settings
WHERE model_name ='CLUSTERING_EXAMPLE'
ORDER BY setting_name;

-- Get a list of model views
col view_name format a30
col view_type format a50

SELECT view_name, view_type
FROM user_mining_model_views
WHERE model_name='CLUSTERING_EXAMPLE'
ORDER BY view_name;

-- CLUSTERS
-- For each cluster_id, provide the number of records in the cluster,
-- the parent cluster id, the level in the hierarchy, and dispersion,
-- which is a measure of individual cluster quality, and computationally,
-- the sum of square errors.
--
SELECT cluster_id CLU_ID, RECORD_COUNT REC_CNT, parent, TREE_LEVEL,
 ROUND(TO_NUMBER(dispersion),3) dispersion
FROM DM$VDCLUSTERING_EXAMPLE
ORDER BY CLUSTER_ID;

SELECT CLUSTER_ID CLU_ID, RECORD_COUNT REC_CNT, ROUND(TO_NUMBER(dispersion),3) DISPERSION
FROM DM$VDCLUSTERING_EXAMPLE
WHERE LEFT_CHILD_ID IS NULLAND RIGHT_CHILD_ID IS NULL
ORDER BY CLUSTER_ID;

-----------------------------------------------------------------------
-- End of script
-----------------------------------------------------------------------