Topic outline

• Searching Unstructured Text Data

  Searching Unstructured Text Data

  See how to use SQL Search with InterSystems IRIS® data platform to search unstructured text data stored in SQL tables. SQL Search gives you the ability to rapidly search unstructured text data, using basic, semantic, or analytic indexing. This feature is fundamental to accessing the content of large volumes of text stored in SQL.

  In this exercise, you will gain some basic experience with InterSystems SQL Search. You will create and populate an SQL table with indexed text describing a variety of aviation events, then use SQL Search to find text stored in the table that includes specific words or phrases.

  
  
• Collapse allExpand all
  Try It

  Try It

  This exercise walks you through the basic steps of searching text data stored as a string in an SQL table column. You will first build and populate an SQL table with text describing aviation events, then use SQL Search to find text in the table that includes specific words or phrases, such as references to visibility or temperature. 

  •  You must be logged in to launch an InterSystems IRIS instance.

    • Log in with your existing account.
    • Don't have an InterSystems account? Create a new account.
  • Download the sample files
    

    Using the InterSystems Learning Labs instance, download the sample files:

    1. Open the IDE using the link in the launcher above. If prompted, log in with the credentials for your lab.
    2. Open the command-line terminal in the IDE by clicking Terminal > New Terminal.
    3. Change directories to /home/project/shared and use the git clone command to clone the repo:

       git clone https://github.com/intersystems/Samples-Aviation

    The folder is added to the Explorer panel on the left under Shared, and the directory is available to InterSystems IRIS in /home/project/shared.

  • Connect to the SAMPLES namespace

    1. In the IDE, select the ObjectScript extension by clicking the InterSystems icon on the left.
    2. Under EXPLORER, click Choose Server and Namespace.
    3. You will be prompted to choose a folder. Select shared.
    4. Select the InterSystems server avliris.
    5. Select SAMPLES as the namespace.
    6. If prompted, enter the password for your lab instance.
  • Create a basic SQL search index
    

    1. In the IDE, create a Basic SQL Search index by defining a class in the SAMPLES namespace.
    2. Return to the Explorer panel. In the directory /home/project/shared/Samples-Aviation/src/cls/Aviation/, create a new file called TestSQLSrch.cls and add the following code to this file:
       Begin code:

       Class Aviation.TestSQLSrch Extends %Persistent [DdlAllowed,Owner={UnknownUser},SqlRowIdPrivate, SqlTableName=TestSQLSrch ]
       {
            Property UniqueNum As %Integer;
            Property Narrative As %String(MAXLEN=100000) [ SqlColumnNumber=3 ];
            Index NarrBasicIdx On (Narrative) As %iFind.Index.Basic(INDEXOPTION=0, LANGUAGE="en",LOWER=1);
            Index UniqueNumIdx On UniqueNum [ Type=index,Unique ];
       }

       End code. This example creates a persistent class (table) containing a Narrative property (column), and defines a Basic SQL Search index for this property.
    3. Right-click the file name in the Explorer panel and click Import and Compile.
       Because this is a new class, you must now populate the table with text data.
  • Populate the table

    Next, you will populate the table with text data and build the SQL Search index. An SQL Search index is built and maintained like any other SQL index.

    1. Open the Web Terminal using the link in the launcher above, and enter the credentials for your lab instance.
    2. Enter the following commands to populate the new table with text data from the Aviation.Event table you downloaded. Begin code:

       set $namespace = "SAMPLES"
         set in1="INSERT OR UPDATE INTO Aviation.TestSQLSrch (UniqueNum,Narrative) "
         set in2="SELECT %ID,NarrativeFull FROM Aviation.Event WHERE %ID < 100"
         set myinsert=in1_in2
         set tStatement=##class(%SQL.Statement).%New()
         set qStatus=tStatement.%Prepare(myinsert)
           if qStatus'=1 {write "%Prepare failed:" DO $System.Status.DisplayError(qStatus) quit}
         set rset=tStatement.%Execute()
         write !,"Total rows inserted=",rset.%ROWCOUNT

       End code.

       In this example, the SQL Search index is automatically built as each record is added. For performance reasons, you may wish to use the %NOINDEX option to defer building indices until the table is fully populated, and then build the SQL Search index (and any other defined indices) using the %Build() method.

       Alternatively, you could add an SQL Search index to an existing persistent class that already contains text data, and then populate the SQL Search index using the %Build() method.

  • Use SQL Search

    Now, you will use SQL Search to return all records in the table that contain phrases referencing the visibility and temperature.

    1. Open an SQL Shell in the Web Terminal by entering the following command:
       DO $SYSTEM.SQL.Shell()
       This will display the following output: Begin code:

       SQL Command Line Shell
       ----------------------------------------------
       The command prefix is currently set to: < >.
       Enter q to quit, ? for help.
       [SQL]USER>>

       End code.
    2. Next, use SQL Search as a WHERE clause condition of a SELECT query. The WHERE clause can contain other conditions associated by AND logic.
       1. Run the following SQL Query in the SAMPLES namespace: Begin code:

          SELECT %iFind.Highlight(Narrative,'"visibility [1-4] mile*" AND "temp* ? degrees"') 
          FROM Aviation.TestSQLSrch 
          WHERE %ID %FIND search_index(NarrBasicIdx,'"visibility [1-4] mile*" "temp* ? degrees"',0,'en')

          End code.

    In the code above, the search_index() function specifies the search_item parameter as "visibility [1-4] mile*" "temperature ? degree*". This returns all records that contain both positional phrases, in any order:

    • "visibility [1-4] mile*" returns phrases with from 1 to 4 words between the words visibility and mile. Because mile* specifies a wildcard, it could match either mile or miles, for example:
      • visibility less than 1 mile
      • visibility 10 miles
      • visibility approximately 20 statute miles
      • visibility for many miles
    • Similarly, "temp* ? degrees" returns phrases with a word beginning with temp and ending in 0 or more non-space wildcard characters, a single missing word, and then the word degrees. Thus it would return records with the following phrases:
      • temperature 20 degrees
      • temp. 20 degrees
      • temperature in degrees

    "temp* ? degrees" could also return the phrase temporarily without degrees, which is probably unintended.

    This example also highlights the returned text by applying the same search_item to the returned records, which highlights every instance of either of these phrases by delimiting them with <b> and </b> tags. For example, the query might return a report like the following:

    Begin code:

    At 0902, a weather observation from Puntilla Lake was reporting, in part: wind, calm; <b>visibility, 25 statute miles</b>; clouds and sky condition, 5,000 feet overcast, <b>temperature, 23 degrees</b> F; dew point 21 degrees F; altimeter, 30.35 inHG.

    End code.

    The search_index function also specifies a few other parameters:

    • The search_index parameter is the SQL Search index defined earlier, NarrBasicIdx.
    • The search_option parameter can apply an optional transformation to the search. Here it is set to the default, 0, meaning no search transformation.
    • The search_language parameter specifies the default, 'en' (English).

    To learn more about using SQL Search and the different options that you can specify for each parameter, continue to the What's Next? section below.

• What's Next?

  What's Next?

  Continue learning about SQL Search with the following resources:
  

  • Read more about the SQL Search tool, including how to index sources and perform searches (documentation, 30m).
  • Learn about the REST interface for InterSystems SQL Search (documentation, 2m).

  • Share Your Feedback!