Text classification using Oracle Machine Learning

The classification of unstructured text data can be automated using Oracle Text and Oracle Machine Learning (OML). In this article, I would like to share a basic example of how to achieve this with minimum effort. If you want to try this on your own, please check my previous article called ‘The database can be a document management system’, and run the hands-on lab ‘RAG example with Oracle AI Vector Search’. Once you have a sandbox database environment running, you can follow this simple OML experiment. Here is my sandbox with some documents processed that I can use as an example.

Document chunk with markdown format

Each vectorized chunk has a markdown version of the data that was split into individual text lines.

Same chunk split into lines

The machine learning classification task is a supervised operation, so it means you need to have some pre-labeled data that will be used to train a model. So make sure you apply labels to a large number of lines to train and test your machine learning models. In most cases, the operation is a multi-label classification.

Each line can have one of these 8 classification labels

The sample application is described in the previous materials, and for this use case, I will be brief. Please use the documentation for more details, or post your questions below if you have any.

Create a view with the lines that have labels, adding the labels of the previous and next lines, as the line type of the neighbors can be important to decide the type of a given line.

-- Create a data view with text line types and their neighbor types
CREATE OR REPLACE VIEW mdlines_type_neghbor_type AS
  select ID, VECTOR_ID, BLOBDOC_ID, CHUNK_NO, LINE_NO, LINE_CONTENTS, LINE_TYPE, 
       lag(LINE_TYPE) over (partition by BLOBDOC_ID order by LINE_NO) as prev_line_type,
       lead(LINE_TYPE) over (partition by BLOBDOC_ID order by LINE_NO) as next_line_type
  from MDLINES where LINE_TYPE is not NULL;

From this view, the data is split by a 70/30 ratio for training and testing.

CREATE OR REPLACE VIEW mdlines_train_data AS
  select ID, VECTOR_ID, BLOBDOC_ID, CHUNK_NO, LINE_NO, LINE_CONTENTS, LINE_TYPE, PREV_LINE_TYPE, NEXT_LINE_TYPE
  from MDLINES_TYPE_NEGHBOR_TYPE where ID <= 350000;

CREATE OR REPLACE VIEW mdlines_test_data AS
  select ID, VECTOR_ID, BLOBDOC_ID, CHUNK_NO, LINE_NO, LINE_CONTENTS, LINE_TYPE, PREV_LINE_TYPE, NEXT_LINE_TYPE
  from MDLINES_TYPE_NEGHBOR_TYPE where ID > 350000;

Create an Oracle Text lexer and a Data Mining policy.

DECLARE
  l_policy VARCHAR2(30):='linetype_anlsys_policy';
  l_preference VARCHAR2(30):='linetype_anlsys_lexer';
BEGIN
  -- create a lexer preference in the Text data dictionary
  ctx_ddl.CREATE_PREFERENCE(l_preference, 'BASIC_LEXER');
  -- create a policy for Data Mining
  ctx_ddl.CREATE_POLICY(l_policy, lexer => l_preference);
END;

Use a simple table for the settings.

-- Data Mining settings for text mining 
CREATE TABLE text_mining_settings
( setting_name VARCHAR2(30),
  setting_value VARCHAR2(4000)
);

Provide the parameters for the OML algorithm to be used for the multi-label classification model.

-- OML supports fully Automatic Data Preparation, user-directed general data preparation, and user-specified embedded data preparation
DECLARE
  -- Oracle Text policy created using CTX_DDL.CREATE_POLICY
  l_policy VARCHAR2(30):='linetype_anlsys_policy';
BEGIN
  -- Naive Bayes algorithm (default) 
  INSERT INTO text_mining_settings
  VALUES ( dbms_data_mining.algo_name,
           dbms_data_mining.algo_naive_bayes );
  -- fully Automatic Data Preparation
  INSERT INTO text_mining_settings
  VALUES ( dbms_data_mining.prep_auto,
           dbms_data_mining.prep_auto_on );
  INSERT INTO text_mining_settings
  VALUES ( dbms_data_mining.odms_text_policy_name,
           l_policy );
  COMMIT;
END;

Create the OML model for the multi-label classification task. Append the transformation instructions as an attribute to the transformation list called xformlist. Use the settings table TEXT_MINING_SETTINGS for the model parameters.

DECLARE
  -- additional transformations parameter
  xformlist dbms_data_mining_transform.TRANSFORM_LIST;
BEGIN
  -- Transformation list called xformlist:
  -- TEXT indicates that the attribute contains unstructured text;
  -- TEXT value optionally followed by POLICY_NAME, TOKEN_TYPE, MAX_FEATURES, and MIN_DOCUMENTS parameters;
  -- TOKEN_TYPE possible values: NORMAL, STEM, THEME, SYNONYM, BIGRAM, STEM_BIGRAM;
  -- SYNONYM may be optionally followed by an Oracle Text thesaurus name in square brackets.
  dbms_data_mining_transform.SET_TRANSFORM( xformlist, 'LINE_CONTENTS',
                NULL, 'LINE_CONTENTS', NULL, 'TEXT(TOKEN_TYPE:NORMAL)');
  -- mining model for classification, MDLINES_TRAIN_DATA.LINE_TYPE as a column to parse and use for text mining
  dbms_data_mining.CREATE_MODEL
  ( model_name => 'LINETYPE_ANALYSIS'
  , mining_function => dbms_data_mining.classification
  , data_table_name => 'MDLINES_TRAIN_DATA'
  , case_id_column_name => 'ID'
  , target_column_name => 'LINE_TYPE'
  , settings_table_name => 'TEXT_MINING_SETTINGS'
  , xform_list => xformlist
  );
END;
/

Retrieve the models your database schema has access to.

SELECT * FROM ALL_MINING_MODELS order by CREATION_DATE desc;

Verify the ML model on a sample of 50 rows from the test data.

-- retrieve the LINETYPE predicted by the classification model for the test data
-- compare the result with the LINE_TYPE column, which stores the pre-label to evaluate the accuracy
select LINE_CONTENTS, LINE_TYPE,
       prediction(LINETYPE_ANALYSIS using *) as LINETYPE
from MDLINES_TEST_DATA where rownum <= 50;

Use the confusion matrix to calculate the model accuracy. This matrix is built based on the line types used in my sample application.

-- populate the confusion matrix, also known as an error matrix
select 'Predicted ' || prediction(LINETYPE_ANALYSIS using *) as "Classification",
       count(case when LINE_TYPE = 1 then 1 end) as "1 title",
       count(case when LINE_TYPE = 2 then 1 end) as "2 text",
       count(case when LINE_TYPE = 3 then 1 end) as "3 code",
       count(case when LINE_TYPE = 4 then 1 end) as "4 noise",
       count(case when LINE_TYPE = 5 then 1 end) as "5 link",
       count(case when LINE_TYPE = 6 then 1 end) as "6 info",
       count(case when LINE_TYPE = 7 then 1 end) as "7 author",
       count(case when LINE_TYPE = 8 then 1 end) as "8 index"
from MDLINES_TEST_DATA
group by "Classification" order by "Classification";

Confusion matrix for the manually created ML model

There are ways to tune this model, but this is not the scope of this article. My objective is to show the simplest example to create an ML model for a multi-label classification task, which I mentioned multiple times in other materials, including live sessions.

Get Valentin Leonard Tabacaru’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Oracle Autonomous AI Database offers AutoML, a better way to create ML models that are already tuned for that task using your data.

Oracle Machine Learning AutoML Experiments

For this use case, it created 5 models using different algorithms with distinct balanced accuracy values.

The input was the same MDLINES_TRAIN_DATA view, and the same target column LINE_TYPE. The case ID column is ID.

Training data view with feature importance and target column

Use the same SQL statement to retrieve the models created.

SELECT * FROM ALL_MINING_MODELS order by CREATION_DATE desc;

Use the same confusion matrix to calculate the model accuracy for each one of the models provided.

-- confusion matrix for the model with the best balanced accuracy
select 'Predicted ' || prediction(NB_59F2F58EB9 using *) as "Classification",
       count(case when LINE_TYPE = 1 then 1 end) as "1 title",
       count(case when LINE_TYPE = 2 then 1 end) as "2 text",
       count(case when LINE_TYPE = 3 then 1 end) as "3 code",
       count(case when LINE_TYPE = 4 then 1 end) as "4 noise",
       count(case when LINE_TYPE = 5 then 1 end) as "5 link",
       count(case when LINE_TYPE = 6 then 1 end) as "6 info",
       count(case when LINE_TYPE = 7 then 1 end) as "7 author",
       count(case when LINE_TYPE = 8 then 1 end) as "8 index"
from MDLINES_TEST_DATA
group by "Classification" order by "Classification";

Confusion matrix for the AutoML model

Please leave your comments and share your experience with these features. Have you tried this hands-on workshop? What is the best way to classify unstructured text data?