{"id":92300,"date":"2021-08-30T18:20:50","date_gmt":"2021-08-30T18:20:50","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=92300"},"modified":"2022-04-24T21:17:22","modified_gmt":"2022-04-24T21:17:22","slug":"sentiment-analysis-oracle-text","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/sentiment-analysis-oracle-text\/","title":{"rendered":"Text Mining and Sentiment Analysis: Oracle Text"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Text Mining and Sentiment Analysis: Introduction<\/a><\/li>\n
  2. Text Mining and Sentiment Analysis: Power BI Visualizations<\/a><\/li>\n
  3. Text Mining and Sentiment Analysis: Analysis with R<\/a><\/li>\n
  4. Text Mining and Sentiment Analysis: Oracle Text<\/a><\/li>\n
  5. Text Mining and Sentiment Analysis:\u00a0Data Visualization in Tableau<\/a><\/li>\n
  6. Sentiment Analysis with Python<\/a><\/li>\n<\/ol>\n\n

    The fourth article of the series will focus on leveraging Oracle<\/em>\u00a0Text<\/em> features in Oracle Database to perform sentiment analysis.<\/p>\n

    The first three articles of this series covered Sentiment Analysis using tools and services in the Microsoft ecosystem: Azure cognitive services and Power BI. This article will explore the Text features in an Oracle Database to perform Sentiment Analysis. The approach demonstrated in this article is useful in environments where the Oracle platform, tools, and technologies are predominantly used. Several organizations have not fully embraced the use of the public cloud for various reasons. This approach is suitable when using the public cloud may not be feasible in your organization.<\/p>\n

    Oracle Text feature has been available since Oracle Database release 12c. It provides indexing, word and theme searching, sentiment analysis, and document classification capabilities. According to Oracle documentation<\/a> \u201cOracle Text can perform linguistic analysis on documents, as well as search text using a variety of strategies including keyword searching, context queries, Boolean operations, pattern matching, mixed thematic queries, HTML\/XML section searching, and so on. It can render search results in various formats including unformatted text, HTML with term highlighting, and original document format. Oracle Text supports multiple languages and uses advanced relevance-ranking technology to improve search quality. Oracle Text also offers advanced features like classification, clustering, and support for information visualization metaphors\u201d.<\/p>\n

    This article will focus on performing sentiment analysis using Oracle Text. Oracle Application Developer\u2019s guide<\/a> describes this as \u201cOracle Text enables you to perform sentiment analysis for a topic or document by using sentiment classifiers that are trained to identify sentiment metadata. Sentiment analysis is the process of identifying and extracting sentiment metadata about a specified topic or entity from a set of documents. Trained sentiment classifiers identify the sentiment. When you run a query with sentiment analysis, in addition to the search results, sentiment metadata (positive or negative sentiment) is also identified and displayed\u201d.<\/p>\n

    In Oracle Text, a sentiment classifier is a specific type of document classifier that is used to extract sentiment metadata. Oracle text provides a default sentiment classifier (unsupervised). If you have suitable training data, Oracle Text also gives you the ability to train your own sentiment classifiers.<\/p>\n

    Please note that this article uses the Oracle Database Application Development<\/em> virtual machine, running on a windows host for the demo.<\/p>\n

    Initial setup<\/h2>\n
      \n
    1. Download and install VirtualBox by following instructions listed on VirtualBox website<\/a>.<\/li>\n
    2. Download and set up Oracle Database Virtual Box Appliance \/ Virtual Machine<\/em> by following instructions listed on the Oracle website<\/a>.<\/li>\n
    3. Start up the Virtual Machine and ensure SQL Developer<\/em> is available on the desktop screen. Please note that you may need to create a free account to download the Virtual Machine.<\/li>\n<\/ol>\n

      \"Oracle<\/p>\n

      Figure 1. Oracle Database Developer Virtual Machine<\/strong><\/p>\n

        \n
      1. This article uses the same Excel file raw data from previous articles, available on my GitHub repository<\/a>. Please download and save this file at a convenient location on your Virtual Machine<\/li>\n<\/ol>\n

        Set up an Oracle Text User<\/h3>\n

        Set up a user with the CTXAPP role, enabling you to work with Oracle Text indexes and use Oracle Text PL\/SQL packages.<\/p>\n

          \n
        1. On the Virtual Machine, launch the Oracle SQL Developer<\/em> application. Under the Connections menu, click on system >.<\/em> When prompted for credentials, enter sys as sysdba<\/em> in the username field and oracle<\/em> in the password field.<\/li>\n
        2. When a new worksheet opens, run this query select * from v$database ;<\/code>. From the query result, verify the value under the NAME<\/em> column is ORCLDB<\/em>. You may also run the query select user from dual<\/code> to verify the username of the current connection. It is important to verify this, as the subsequent steps for setting up the Oracle Text user need to be performed as the system administrator. The screenshot below unions the two queries for simplicity of display.<\/li>\n<\/ol>\n

          \"Oracle<\/p>\n

          Figure 2. Use Oracle SQL Developer to connect to ORCLDB database as \u201cSYS\u201d user<\/strong><\/p>\n

            \n
          1. Run the following SQL statement to create user redgatedemo<\/em>.<\/li>\n<\/ol>\n
            CREATE USER redgatedemo IDENTIFIED BY mydemopassword;<\/pre>\n
            Upon successful execution, you should see the message User REDGATEDEMO created<\/em>.<\/p>\n
              \n
            1. Run the following SQL statements to grant user redgatedemo<\/em> the roles necessary for connecting and using database and CTXAPP resources. The CTXAPP role includes execute privileges on various CTX PL\/SQL packages.<\/li>\n<\/ol>\n
              GRANT RESOURCE, CONNECT, CTXAPP TO redgatedemo;\r\nGRANT UNLIMITED TABLESPACE TO redgatedemo;<\/pre>\n
              Upon successful execution, you should see the message Grant<\/em> succeeded<\/em>.<\/p>\n
              Import raw data file in Oracle Table<\/h3>\n
              Click the green plus sign under Connections<\/em> to open the New \/ Select Database Connection<\/em> dialog. Connect to the Oracle Database with user redgatedemo<\/em> as shown in Figure 3.<\/p>\n
              \"Oracle<\/p>\n
              Figure 3. Connect to Oracle database with redgatedemo user<\/strong><\/p>\n
                \n
              1. Run the following SQL to create a table, with a unique index and primary key.<\/li>\n<\/ol>\n
                -- Create a Table to load text data\r\nCREATE TABLE REDGATEDEMO.DEMO_SENTIMENTANALYSIS_TEAMHEALTH\r\n(\r\n    ID                     NUMBER GENERATED ALWAYS AS IDENTITY\r\n   ,PERIOD                 VARCHAR2( 500 ) NOT NULL\r\n   ,MANAGER                VARCHAR2( 500 ) NOT NULL\r\n   ,TEAM                   VARCHAR2( 500 ) NOT NULL\r\n-- this will store raw text of survey response\r\n   ,RESPONSE               VARCHAR2( 4000 ) NOT NULL \r\n   ,DW_CREATION_DATE       DATE DEFAULT SYSDATE\r\n);\r\n\r\n-- create a unique index\r\nCREATE UNIQUE INDEX REDGATEDEMO.DEMO_SENTIANALYSIS_TEAMHEALTH_PK\r\n    ON REDGATEDEMO.DEMO_SENTIMENTANALYSIS_TEAMHEALTH(ID )\r\n    ONLINE;\r\n\r\n-- create a primary key\r\nALTER TABLE REDGATEDEMO.DEMO_SENTIMENTANALYSIS_TEAMHEALTH\r\n    ADD CONSTRAINT DEMO_SENTIANALYSIS_TEAMHEALTH_PK PRIMARY KEY\r\n    (ID )\r\nUSING INDEX REDGATEDEMO.DEMO_SENTIANALYSIS_TEAMHEALTH_PK ENABLE VALIDATE;\r\n<\/pre>\n
                \"Image<\/p>\n
                Figure 4. Create table to load text data<\/strong><\/p>\n
                  \n
                1. If you haven\u2019t already, download the Excel file from the Github repo<\/a> and save it to a convenient location on the Virtual Machine<\/li>\n
                2. In Oracle SQL Developer, under the left side connection menu, navigate to Oracle Connections > redgatedemo > Tables > DEMO_SENTIMENTANALYSIS_TEAMHEALTH<\/em>. Right click on this table and select Import<\/em> Data<\/em><\/li>\n<\/ol>\n
                  \"Oracle<\/p>\n
                  Figure 5. Launch the Data Import Wizard<\/strong><\/p>\n
                    \n
                  1. This will launch Data Import Wizard<\/em>. Use this easy-to-follow five-step wizard to load the previously downloaded Excel file into the Oracle database table – REDGATEDEMO.DEMO_SENTIANALYSIS_TEAMHEALTH<\/em><\/li>\n<\/ol>\n
                    \"Data<\/p>\n
                    Figure 6. Step 1 of the Data Import Wizard<\/strong><\/p>\n
                    Please note Step 3 of the Data<\/em> Import<\/em> Wizard<\/em> is Choose<\/em> columns<\/em>. Please ensure you have selected only the four columns Period<\/em>, Manager<\/em>, Team<\/em>, and Response<\/em> to import. Sometimes you may see a long list of empty excel columns with headings like column5<\/em>, column6<\/em>, etc. under the Available<\/em> columns<\/em> section of the wizard, which should not be imported.<\/p>\n
                    \"Data<\/p>\n
                    Figure 7. Step 3 of the Data Import Wizard (select columns to import)<\/strong><\/p>\n
                    The wizard will display a success message upon completion<\/p>\n
                    \"Import<\/p>\n
                    Figure 8. Data Import wizard completed successfully<\/strong><\/p>\n
                    Sentiment Classifier in Oracle text<\/h2>\n
                    In Oracle Text, a sentiment classifier is a specific type of document classifier that can extract sentiment metadata from a document. To use a sentiment classifier, it is first associated with a sentiment classifier preference, then trained.<\/p>\n
                    Oracle documentation<\/a> describes a sentiment classifier preference as \u201cIt specifies the parameters that are used to train a sentiment classifier. These parameters are defined as attributes of the sentiment classifier preference. You can either create a sentiment classifier preference or use the default\u201d. The default sentiment classifier is CTXSYS.DEFAULT_SENTIMENT_CLASSIFIER<\/code>. The CTX_DDL.CREATE_PREFERENCE<\/code>\u00a0procedure is used to create a user-defined sentiment classifier preference and the\u00a0CTX_DDL.SET_ATTRIBUTE<\/code>\u00a0procedure is used to specify its parameters.<\/p>\n
                    Oracle Text provides a default sentiment classifier, a pre-trained out-of-the-box model that works well for most general-purpose sentiment analysis applications. It leverages an AUTO_LEXER<\/code><\/a> that performs language identification, word segmentation, document analysis, part-of-speech tagging, and stemming. In the world of text processing and analysis, a lexer reads an input text and breaks it down into tokens using language-specific grammar rules. The lexer generates a stream of tokens as output. Oracle\u2019s AUTO_LEXER<\/code> supports several languages and is most commonly used due to its ability to detect the language automatically. The default sentiment classifier is ideal for use in scenarios where a labeled training dataset is not available, and your sentiment classification needs are general purpose in nature (for example, social media posts, Google and Yelp reviews, etc.)<\/p>\n
                    Oracle Text also provides the ability to create your own user-defined sentiment classifiers. To train your own sentiment classifier, a user needs an associated sentiment classifier preference, a training set of documents, and the target sentiment categories. The CTX_CLS.SA_TRAIN<\/code>\u00a0procedure is used to train a sentiment classifier. During training, a user assigns each sample document to a category, which Oracle Text uses to infer rules for how to perform sentiment analysis. This technique is an example of supervised machine learning and relies on suitable training data. You may need to train your own sentiment classifier if the default classifier does not meet the needs of your use-case (for example, complex text in a domain that needs deep subject matter understanding like medicine, international law, etc.). Training a user-defined sentiment classifier is out of scope for this article. To learn how to train your a user-defined sentiment classifier in Oracle Text, please follow this link<\/a> to Oracle documentation.<\/p>\n
                    Performing Sentiment Analysis using the default classifier<\/h2>\n
                    The CTX_DOC PL\/SQL<\/em> package is a part of Oracle Text features and provides several functions and procedures for various document services, including Sentiment<\/em>. The following steps demonstrate the use of the CTX_DOC<\/em> package for performing sentiment analysis, using the unsupervised technique (with default sentiment classifier and sentiment classifier preference)<\/p>\n
                      \n
                    1. Run the following SQL statement to create a sentiment classifier preference demoautolexer<\/em> of type AUTO_LEXER<\/em>. Oracle Text will default values for attributes that are not explicitly defined. Please note that the type AUTO_LEXER<\/em> should be used only for the default classifier. When training a user-defined classifier, always use the type SENTIMENT_CLASSIFIER<\/em>.<\/li>\n<\/ol>\n
                      exec ctx_ddl.create_preference('demoautolexer','AUTO_LEXER');<\/pre>\n
                      \"Create<\/p>\n
                      Figure 9. create a sentiment classifier preference of type AUTO_LEXER<\/strong><\/p>\n
                        \n
                      1. Run the following SQL statement to create a context index on the document set (table with text data). The context parameters for this index specify the use of AUTO_LEXER<\/code> and default stoplist<\/code>. A stoplist<\/em> refers to the list of most common words in a particular language, which are usually removed before processing natural language data. This step may take a few minutes to complete.<\/li>\n<\/ol>\n
                        create index DEMO_SENTIMENTANALYSIS_TEAMHEALTH_TXTIDX \r\non DEMO_SENTIMENTANALYSIS_TEAMHEALTH(RESPONSE) indextype is\r\nctxsys.context parameters \r\n('lexer demoautolexer stoplist ctxsys.default_stoplist');<\/pre>\n
                         <\/p>\n
                        \"create<\/p>\n
                        Figure 10. Create text index<\/strong><\/p>\n
                          \n
                        1. You can now use this text index in a select statement using the SENTIMENT_AGGREGATE<\/code> method of the CTX_DOC<\/em> package. This method takes a document id as input, which in this example is the ID<\/code> field (the primary key of table DEMO_SENTIMENTANALYSIS_TEAMHEALTH<\/em>).<\/li>\n<\/ol>\n
                          Oracle documentation<\/a> describes the output of theCTX_DOC.SENTIMENT_AGGREGATE<\/code>\u00a0procedure as \u201ca single consolidated sentiment score for the document. The sentiment score is a value in the range of -100 to 100, and it indicates the strength of the sentiment. A negative score represents a negative sentiment, and a positive score represents a positive sentiment. Based on the sentiment scores, you can group scores into labels such as Strongly Negative (\u201380 to \u2013100), Negative (\u201380 to \u201350), Neutral (-50 to +50), Positive (+50 to +80), and Strongly Positive (+80 to +100).\u201d<\/p>\n
                          Run the following SQL statement to see the sentiment scores document id = 3<\/code> (Row with ID = 3):<\/p>\n
                          SELECT ctx_doc.sentiment_aggregate('DEMO_SENTIMENTANALYSIS_TEAMHEALTH_TXTIDX','3') from dual;<\/pre>\n
                          \"Run<\/p>\n
                          Figure 11. Use ctx_doc.sentiment_aggregate to generate sentiment score for document id = 3<\/strong><\/p>\n
                            \n
                          1. Run the following SQL statement to see the text as well as sentiment scores for document ids 3 and 4.<\/li>\n<\/ol>\n
                            SELECT TO_CHAR(ID) DOC_ID, RESPONSE FROM DEMO_SENTIMENTANALYSIS_TEAMHEALTH WHERE ID = 3\r\nUNION ALL\r\nselect 'Sentiment Score -> ', TO_CHAR(ctx_doc.sentiment_aggregate('DEMO_SENTIMENTANALYSIS_TEAMHEALTH_TXTIDX','3')) from dual\r\nUNION ALL\r\nSELECT TO_CHAR(ID) DOC_ID, RESPONSE FROM DEMO_SENTIMENTANALYSIS_TEAMHEALTH WHERE ID = 4\r\nUNION ALL\r\nselect 'Sentiment Score -> ', TO_CHAR(ctx_doc.sentiment_aggregate('DEMO_SENTIMENTANALYSIS_TEAMHEALTH_TXTIDX','4')) from dual<\/pre>\n
                            \"Run<\/p>\n
                            Figure 12. SQL statement to select text and its sentiment score<\/strong><\/p>\n
                            The results of this SQL statement can be interpreted as:<\/p>\n
                              \n
                            1. The sentiment for document id 3 with the text \u201cwe work well as a team, we have fun together, I think we are very healthy\u201d is 31, which falls within the Neutral range of -50 to +50<\/li>\n
                            2. The sentiment for document id 4 with text \u201cI fell pretty good about the health of our team. My main concerns re a lack of vision into what our application’s true purpose is and the future direction of our team.\u201d is -28, which also falls in the within the Neutral range of -50 to +50 but is relatively more negative than the sentiment for document id 3. This is confirmed by human interpretation of the text.<\/li>\n<\/ol>\n
                              A developer would run such SQL statement per document (per row in the table) using a looping operation weand wrapping it in a user-defined PL\/SQL package.<\/p>\n
                              Conclusion<\/h2>\n
                              This article demonstrated how to perform sentiment analysis with Oracle Text features available in an Oracle Database, using the default sentiment classifier that comes out of the box in Oracle. I highlighted the potential scenarios where the default sentiment classifier may not fulfill your needs, and you may have to train your own user-defined sentiment classifier. This article also helped readers get familiar with how to write a SQL statement to generate sentiment scores and interpret them to gain meaningful insights.<\/p>\n
                              References:<\/h2>\n