Indice degli argomenti

• Searching Unstructured Text Data

  Searching Unstructured Text Data

  Content Type
  Exercise

  Duration
  15m

  Level
  Introductory

  Learn 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 build an SQL table containing aviation event descriptions and use SQL Search to retrieve relevant text based on specific words or phrases.

  
  

  Learning Objectives

  • Create an SQL table and populate it with text.
  • Use SQL Search to find text entries mentioning specific terms.
  • Experiment with different types of text indexing to improve search results.

  Prerequisites

  • None
• Start the Exercise

  Start the Exercise

  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. 

  • Launch Your Lab

    Use the online lab provided to complete this exercise.

  •  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.
  • Create a basic SQL search index
    

    1. Double-click the icon on the desktop of your lab to open Visual Studio Code.
    2. In VS Code, locate the Explorer panel. In the directory /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. Save the file, then 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. Within VS Code, click Terminal at the top of the screen, then New Terminal.
    2. Run iris session iris to start a new InterSystems IRIS Terminal session, using the credentials tech/demo to authenticate.
    3. Enter the following commands to populate the new table with text data from the Aviation.Event table you downloaded.
       You can paste these into the Terminal all at once, but you will need to press Enter to run the final line manually. All previous lines will automatically run when pasted in.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 InterSystems IRIS Terminal session 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. 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, see the resources linked 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!