docs.intersystems.com
InterSystems IRIS Data Platform 2019.2

First Look: SQL Search with InterSystems Products
InterSystems: The power behind what matters   
Search:  


This First Look introduces you to InterSystems IRIS™ data platform support for SQL text search, which provides semantic context searching of unstructured text data in a variety of languages. It covers the following topics:
This First Look presents an introduction to SQL context-aware text searching and walks through some initial tasks associated with indexing text data for searching and performing SQL Search. Once you’ve completed this exploration, you will have indexed text in an SQL column for text searching and performed several types of searches. These activities are designed to use only the default settings and features, so that you can acquaint yourself with the fundamentals of the feature. For the full documentation on SQL Search, see the SQL Search Guide.
A related, but separate, tool for handling unstructured texts is Natural Language Processing (NLP). SQL Search presupposes that you know what you are looking for. NLP text analysis allows you to analyze the contents of texts with no prior knowledge of the text contents.
To browse all of the First Looks, including those that can be performed on a free evaluation instance of InterSystems IRIS, see InterSystems First Looks.
Why SQL Search Is Important
The ability to rapidly search unstructured text data is fundamental to accessing the content of the huge volume of text commonly stored by many companies and institutions. Any search facility for such data must have the following functionality:
How InterSystems IRIS Implements SQL Search
SQL Search can search text data found in a column in an SQL table. In order to do this, you must create an SQL Search index for the column containing the text data. InterSystems implements a table column as a property in a persistent class.
There are three levels of index available, each supporting additional features as well as all of the features of the lower levels: Basic, Semantic, and Analytic:
Populating the index. Like all SQL indices, you can either build the index directly after the table has been populated with data, or have SQL automatically build the index entries as you insert records into an empty table. In either case, SQL automatically updates this index as part of subsequent insert, update, or delete operations.
You perform an SQL search you write a SELECT query in which the WITH clause contains %ID %FIND search_index() syntax. The search_index() function parameters include the name of the SQL Search index and a search string. This search string can include wildcard, positional phrase, and entity syntax characters. The search string can also include AND, OR, and NOT logical operators.
Trying SQL Search for Yourself
It’s easy to use InterSystems IRIS SQL Search. This simple procedure walks you through the basic steps of searching text data stored as a string in an SQL table column.
To use the procedure, you will need a running InterSystems IRIS instance. Your choices for InterSystems IRIS include several types of licensed and free evaluation instances; the instance need not be hosted by the system you are working on (although they must have network access to each other). For information on how to deploy each type of instance if you do not already have one to work with, see Deploying InterSystems IRIS in InterSystems IRIS Basics: Connecting an IDE.
You will also use Atelier, the Eclipse-based IDE for InterSystems IRIS, to create ObjectScript code in your instance. For instructions for setting up Atelier and connecting it to your instance, see Atelier in InterSystems IRIS Basics: Connecting an IDE; more detailed information is available in First Look: Atelier with InterSystems Products. (You can also use the Studio IDE from InterSystems, a client application running on Windows systems, to create the ObjectScript code; for more information, see Using Studio and Studio in Connecting an IDE.)
Before You Begin
To use the procedure, you will need a running InterSystems IRIS instance. Your choices for InterSystems IRIS include several types of licensed and free evaluation instances; the instance need not be hosted by the system you are working on (although they must have network access to each other). For information on how to deploy each type of instance if you do not already have one to work with, see Deploying InterSystems IRIS in InterSystems IRIS Basics: Connecting an IDE.
You will also use Atelier, the Eclipse-based IDE for InterSystems IRIS, to create ObjectScript code in your instance. For instructions for setting up Atelier and connecting it to your instance, see Atelier in InterSystems IRIS Basics: Connecting an IDE; more detailed information is available in First Look: Atelier with InterSystems Products. (You can also use the Studio IDE from InterSystems, a client application running on Windows systems, to create the ObjectScript code; for more information, see Using Studio and Studio in Connecting an IDE.)
Finally, you will need to obtain the Aviation.Event table and associated files from the GitHub repo https://github.com/intersystems/Samples-Aviation.
Downloading and Setting up the Sample Files
The Samples-Aviation sources must be accessible by the instance. The procedure for downloading the files depends on the type of instance you are using, as follows:
Once you have the sample files, follow the steps provided in the Samples-Aviation README.md file under “Setup instructions”:
  1. Create a namespace called SAMPLES as follows:
    1. Open the Management Portal for your instance in your browser, using the URL described for your instance in InterSystems IRIS Basics: Connecting an IDE.
    2. Select System Administration > Configuration > System Configuration > Namespaces to go to the Namespaces page.
    3. On the Namespaces page, select Create New Namespace. This displays the New Namespace page; follow the instructions for using this page in Create/Modify a Namespace in the “Configuring InterSystems IRIS” chapter of the System Administration Guide. Call the new namespace SAMPLES.
    4. Select Save near the top of the page and then select Close at the end of the resulting log.
  2. To enable the SAMPLES web application for use with InterSystems IRIS Analytics:
  3. Open the InterSystems IRIS Terminal using the procedure described for your instance in InterSystems IRIS Basics: Connecting an IDE and enter the following command to change to the namespace where the sample will be loaded:
    ZN "SAMPLES"
  4. Enter the following command, replacing .path with the full path of the directory that contains the README.md and LICENSE files of the repo you cloned or downloaded:
    do $system.OBJ.Load("<path>\buildsample\Build.AviationSample.cls","ck")
  5. Enter the following command:
    do ##class(Build.AviationSample).Build()
    When prompted, enter the full path of the directory that contains the README.md and LICENSE files. The method then loads and compiles the code and performs other needed setup steps.
Creating and Testing a Basic SQL Search Index
Once the code is compiled, which may take a minute or two, continue with the following steps:
  1. Open Atelier, verify that it is connected to your instance, and create a Basic SQL Search index by defining the following class in the SAMPLES namespace:
       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 ];
      }
    This example creates a persistent class (table) that contains a Narrative property (column), and defines a Basic SQL Search index for this property. Because this is a new class, you must populate the table with text data.
  2. 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.
    and enter the following commands to populate the new table with text data from the Aviation.Event table you downloaded. In this example, the SQL Search index is automatically built as each record is added:
      ZNSPACE "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
    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.
  3. Open a SQL Shell in Terminal, as described in the first few steps of Creating and Populating a Table With a SQL Script File in First Look: InterSystems SQL, and use SQL Search as a WHERE clause condition of a SELECT query. The WHERE clause can contain other conditions associated by AND logic. Run the following SQL Query in the SAMPLES namespace:
    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')
This example also highlights the returned text by applying the same search_item to the returned records. This highlights every instance of either of these phrases by delimiting them with <b> and </b> tags.
This example is provided to give you some initial experience with InterSystems IRIS SQL Search. You should not use this example as the basis for developing a real application. To use SQL Search in a real situation you should fully research the available choices provided by the software, then develop your application to create robust and efficient code.
Learn More About SQL Search
InterSystems has other resources to help you learn more about SQL Search, including:


Send us comments on this page
View this article as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-10-14 06:33:38