Oracle® NoSQL Database

Full Text Search

Release 18.1

E85382-02

April 2018

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Introduction

Full Text Search (or just text search) provides the capability to identify natural-language documents that satisfy a query, and optionally to sort them by relevance to the query. The most common type of search is to find all documents containing the given query terms and return them in order of their similarity to the query. Notions of query and similarity are very flexible and depend on the specific application. The simplest search considers query as a set of words and similarity as the frequency of query words in the document.

Oracle NoSQL Database integrates with a third-party open-source search engine, Elasticsearch (ES) to enable text-searching capability in Oracle NoSQL Database, in-concert with the Tables interface. For more information, see:
  • "Elasticsearch Documentation"

  • Tables interface, in the "Introducing Oracle NoSQL Database Tables and Indexes" of the Oracle NoSQL Database Getting Started with the Table API Guide

Full Text search (FTS) is an important aspect of any big data and Oracle NoSQL Database system. Users expect that when they input text into a box and click search, they will get the relevant search results they are looking for in a fraction of a second. This feature provides:

  • High performance full text search of Tables stored in Oracle NoSQL Database

  • Search that allows users to explore a collection of information by applying multiple filters

So that the maintenance of indexes does not affect the performance of an Oracle NoSQL Database store, text indexes will not be maintained locally by Oracle NoSQL Database components, but will instead be maintained by a remote service (Elasticsearch) hosted on other nodes.

Note:

Security in FTS is available only in the Enterprise Edition (EE) and is enabled by default.

Since for Community Edition (CE) and Basic Edition (BE) FTS security is not available, you must run KVStore in a non-secure mode. For more information on how to start KVStore on a non-secure mode, see "Installing and Configuring a Non-secure Store" section of the Oracle NoSQL Database Administrator’s Guide.

This feature provides a means of marking fields in an Oracle NoSQL Database Tables schema as being text searchable. Text indexing allows creating indexes on Oracle NoSQL Database tables that cause the indexed fields to automatically enter into an Elasticsearch cluster. Once the data is in Elasticsearch, you may use any native Elasticsearch API to search and retrieve it. The documents retrieved from Elasticsearch contain references back to the original Oracle NoSQL Database records, facilitating their retrieval.

Prerequisite

To use this feature, you must have an Elasticsearch 2.x cluster running as well as the 18.1 and above version of Oracle NoSQL Database store. In a production environment, for performance reasons, both Oracle NoSQL Database nodes and Elasticsearch nodes are intended to be used in distributed environments with different hosts.

  • See the Elasticsearch documentation:
    • to "Download Elasticsearch"

    • for "Installing Elasticsearch"

  • FTS by default runs in a secure mode, but for that you must have a secure KVStore and secure Elasticsearch cluster. To run FTS in a non-secure mode, you must register Elasticsearch by explicitly setting secure flag to false. For more information, see "Integrate Elasticsearch with Oracle NoSQL Database".

When your Elasticsearch cluster is running, it consists of one or more nodes, for example:

  • The HTTP port, which is used for REST requests. It is 9200 by default.

You must know the host name and HTTP port of at least one node in the cluster, and the name of the cluster itself, which by default is "Elasticsearch". See the command show parameters in "Integrate Elasticsearch with Oracle NoSQL Database". This information is provided to the Oracle NoSQL Database store so that it can connect to the Elasticsearch cluster.

Note:

Note that if KVStore already had a registered Elasticsearch, then after the upgrade, you have to re-register it. From 18.1 release, Elasticsearch has to be registered on the HTTP port. Hence, after upgrade, run the plan register-es command again with the HTTP port. See "Integrate Elasticsearch with Oracle NoSQL Database" for information about registering Elasticsearch .

Integrate Elasticsearch with Oracle NoSQL Database

Before you can create a text index, you must register the Elasticsearch cluster with the Oracle NoSQL Database store, using the register-es plan command. In this command you provide the Elasticsearch cluster name, and the host name and HTTP port of any node in the cluster as follows:

plan register-es -clustername <name> -host <host> 
-port -secure true/false <http port> [-force]

For example:

kv-> plan register-es -clustername elasticsearch 
-host 127.0.0.1 -port 9200
-secure true/false
Started plan 5. Use show plan -id 5 to check status.
To wait for completion, use plan wait -id 5

Note:

You will see an error message if the Elasticsearch cluster already contains "stale indexes" corresponding to the Oracle NoSQL Database. A stale index is one that was not created by the current instance of the store, but by a previous instance of the store, or by a concurrent instance of a store with the same name as the current store, which is not allowed.

The optional -force argument causes the Oracle NoSQL Database store to initialize an Elasticsearch cluster regardless of whether it already contains a stale index corresponding to the Oracle NoSQL Database store. See for example:

kv-> plan register-es -clustername elasticsearch 
-host localhost -port 9200 -secure true/false -force
Started plan 38. Use show plan -id 38 to check status.
To wait for completion, use plan wait -id 38

Oracle NoSQL Database store Admin communicates with the Elasticsearch node to verify its existence, and it will acquire a complete list of connection information for all the nodes in the Elasticsearch cluster. This information will be stored and distributed to all the nodes of the Oracle NoSQL Database store. This command can be repeated if the Elasticsearch cluster's population of nodes changes significantly, to update Oracle NoSQL Database's list of Elasticsearch node connections.

If you want to verify that Elasticsearch is registered with your Oracle NoSQL Database store, run the following command:

show parameters -service <storage node id>

This command produces a list of properties which includes the cluster instance and the name of the cluster. See searchClusterMembers=127.0.0.1:9200 and searchClusterName=elasticsearch in the output below:

kv-> show parameters -service sn1
capacity=1
haHostname=localhost
haPortRange=5005,5007
hostname=localhost
memoryMB=0
mgmtClass=oracle.kv.impl.mgmt.NoOpAgent 
mgmtPollPort=0
mgmtTrapPort=0
numCPUs=8
registryPort=5000
rnHeapMaxMB=0
rnHeapPercent=85
rootDirPath=./kvroot
searchClusterMembers=127.0.0.1:9200
searchClusterName=elasticsearch
serviceLogFileCount=20
serviceLogFileLimit=2000000
storageNodeId=1
systemPercent=10

Deregister Elasticsearch from Oracle NoSQL Database Store

Before deregistering an Elasticsearch cluster, all full text indexes are first removed using the following command:
DROP INDEX [IF EXISTS] index_name ON table_name

For more information, see "Drop Index". Otherwise, you get the following error message:

kv-> plan deregister-es
Cannot deregister ES because these text indexes exist:
mytestIndex
JokeIndex

To deregister an Elasticsearch cluster from the Oracle NoSQL Database store, use the following command:

kv-> plan deregister-es
Executed plan 16, waiting for completion...
Plan 16 ended successfully

To verify if the deregistration of Elasticsearch was successful or not, run the following command:

show parameters -service <storage node id>

See Also:

Drop Index

Security in Full Text Search

For a secured NoSQL store, you must send encrypted data over HTTPS to the Elasticsearch (ES) cluster. ES either relies on plugins to support Transport Layer Security (TLS)/Secure Sockets Layer (SSL), or any other HTTP server wrapper on ES (see Elasticsearch documentation "Getting Started (Long Version)"). The ES recommended plugin is Shield, which comes with a proprietary license. Moreover, since Public Key Infrastructure-Based User Authentication (PKI) realm is used as user authentication (see the section "Using Public Key Infrastructure-Based User Authentication" under "User Authentication"), FTS solution is dependent more on shield unless other plugins support the same. For more information on Shield, see Elasticsearch documentation on "Introduction to Shield" and "Getting Started with Shield".

Note that:
  • FTS Security works in conjunction with KVStore security. This means that you can set up FTS security only on a secure KV Store.

  • Trying to register a secure ES on a non-secure store or vice versa will produce a relevant error message.

  • FTS uses the same keystore and truststore available in the storage node security directory for its own certificates and keys.

User Authentication

Shield requires user authentication. Hence, for authenticating the user FTS uses Public Key Infrastructure (PKI) Based User Authentication (see, "PKI User Authentication" for more information).

Using Public Key Infrastructure-Based User Authentication

Shield provides a PKI realm (see, "PKI Realm Configuration" for more information). FTS uses this user realm to authenticate users. It requires the client certificate to have a user name in the Distinguished Name of the certificate (see, "Assigning Roles for PKI Users").

The private key for the client certificate is stored in the keystore (Storage Node’s security directory) of the KVStore. The public key (the self-signed client certificate), is available in the truststore on all the ES nodes. This client certificate has the user name in its distinguished name (DN). You have to map a role to this Distinguished Name in the PKI realm of the ES.

Note:

There are two options for KVStore to trust the certificates of ES Nodes:
  • Self-signed ES Node certificates

    In this option KVStore’s truststore must have certificates of all the ES Nodes.

    If the KVStore truststore has the self-signed certificates of all the ES server nodes, managing certificates may appear to be cumbersome, especially when an Elasticsearch cluster is added with more nodes.

    However, note that adding new nodes will not cause any inconsistency in data in Elasticsearch and KVStore.

  • CA-signed certificates

    In this option KVStore’s truststore needs to only have the CA certificate (see "Certificate Authority" for more information) and not all of ES Node’s certificates.

You must import the ES certificate or the CA certificate manually into the truststore of the Storage Node’s security directory. For more information about how to import the certificates, see the section on "Workflow for Security Configuration".

HTTPS Protocol for Full Text Search

The Elasticsearch cluster enables a port that supports HTTPS protocol. The FTS HTTP client for Elasticsearch uses this port to communicate with Elasticsearch.

This Elasticsearch HTTPS port is known to the KVStore ES client as part of the registering ES cluster with the KVStore. This ES HTTP client uses the same truststore and keystore as that of KVStore. The alias under which the keys and certificates are put in these key/trust store is FTS.

You must generate the keypair in the keystore of Storage Node’s security directory under the alias FTS. For more information, see the "Workflow for Security Configuration" section.

Workflow for Security Configuration

This section provides information on how to setup a secure KVStore for FTS and a secure Elasticsearch.

To configure security, you must have a secure KVStore and a secure Elasticsearch cluster.

  1. After you set up a secure KVStore following the instructions mentioned in "Security Configurations" of the Oracle NoSQL DB Security Guide, you:
    • Generate the keypair for FTS and export the self-signed certificate from the keystore.

    • Import the self-signed certificates of all ES Nodes in the KVStore truststore. If ES uses CA-signed certificates, import only the CA certificate in the truststore of the KVStore.

    • Copy the truststore and keystore files in all Storage Nodes of the KVStore.

    See "Set up a Secure KVStore for FTS" for more information.

  2. After you set up ES security following the instructions mentioned in "Getting Started with Security" of the Elasticsearch documentation, you:
    • Generate the keypair for FTS and export the self-signed certificate from the keystore.

    • Import the self-signed FTS certificate in the truststore of all ES Nodes.

    • Create a shield user.

    • Create a role mapping for the Distinguished Name (DN) and add a role.

    See "Set up a Secure Elasticsearch" for more information.

Note:

The “Certificate” used in the sections below refers to a digitally self-signed X509 certificate.

Set up a Secure KVStore for FTS

Follow the steps below to set up a secure KVStore for FTS:

  1. Set up KVStore security following the instructions in "Security Configuration Overview" of the Oracle NoSQL DB Security Guide. After setting up the KVStore security, import FTS certificates in the same keystore and truststore as that of the KVStore. Note that it is good to have the truststore and keystore in one location. You can then copy the keystore files to all StorageNodes.
    Assuming the location for truststore and keystore files is:
    cd /nosqldata/securityFileBasedData
    1. Generate the keypair for FTS in the keystore of KVStore using the alias FTS:
       keytool -genkey -alias FTS -keystore store.keys -keyalg RSA -keysize 2048 -validity 1712

      At the prompt, Enter the FirstName and LastName, enter the user name in Elasticsearch Cluster for NoSQL (for example: NoSQL).

      Note that the user name is the Common Name (CN) in the certificate. Elasticsearch Public Key Infrastructure (PKI) realm will use this CN entry to extract the user name from the certificate.

    2. Export the self-signed certificate from the keystore as this needs to be imported in the truststore of Elasticsearch nodes. Use the following command:
       keytool -export -alias FTS -file nosql_fts.crt -keystore store.keys
  2. If KVStore uses self-signed ES Node certificates, import the self-signed certificates of all ES Nodes in the KVStore truststore. If ES uses CA-signed certificates, import only the CA certificate in the truststore of the KVStore. Use the following command to:
    • Import all self-signed certificates of ES Nodes into the NoSQL truststore, for example:
         cd /nosqldata/securityFileBasedData/security
             	keytool -importcert -keystore store.trust -file ES_NODE1.cert -alias node1
             	keytool -importcert -keystore store.trust -file ES_NODE2.cert -alias node2
    • Import only CA certificate, for example:
      cd /nosqldata/securityFileBasedData/security
             	keytool -importcert -keystore store.trust -file CA_CERT.cert -alias my_ca

      Note that .cert extension is only used an example. The extension could be .pem as well.

  3. Copy this truststore and keystore in all Storage Nodes of the KVStore, for example:
       cp store.keys /nosqldata/kvroot1/security/
       cp store.keys /nosqldata/kvroot2/security/
       cp store.keys /nosqldata/kvroot3/security
    
  4. Repeat the same for the store.trust file.

You must now follow the steps to set up security for Elasticsearch (see, "Set up a Secure Elasticsearch".

Set up a Secure Elasticsearch

After you set up a secure KVStore (see section "Set up a Secure KVStore"), follow the steps below to set up a secure Elasticsearch:

  1. Set up ES security by following the instructions mentioned in "Getting Started with Security" in the Elasticsearch documentation.

    Note:

    • ES documentation uses CA certificates whereas the examples above use self-signed certificates.

    • ES uses PKI realm for FTS security to work as FTS user authentication depends on PKI.

  2. Ensure that the elasticsearch.yml (see, "PKI Realm Configuration") has at least the following entries:
    shield.transport.ssl: true
      shield.http.ssl: true
      shield.http.ssl.client.auth: required
    
      shield.authc.realms:
                         pki1:
                              type: pki
                              order: 0
                         file1:
                             type: file
                             order: 1
     
  3. After setting up ES security, import the self- signed FTS certificate created above in the truststore of all ES Nodes. To do so:
    • Go to ES node keystore dir ($ES_HOME/config/shield)
      keytool -importcert -keystore node1_trust.jks -file nosql_fts.crt -alias FTS
    • At the prompt asking to trust this certificate, choose yes

  4. The entry in CN is the username. If this is not yet a shield user, create this user now. To create a shield user:
    cd $ES_HOME
    	   ./bin/shield/esusers useradd NoSQL -r admin
    
  5. Add user and role_mapping for the PKI Realm to map a role to the Distinguished Name in the certificate. For more information, see Elasticsearch documentation on "Mapping Users and Groups to Roles".
  6. View the CN Entry in the nosql_fts cert.
    keytool -list -v -keystore node1_trust.jks -alias FTS
    	   Owner: CN=NoSQL, OU=NoSQL, O=Oracle, L=BLR, ST=KN, C=IN
    • Create a role mapping for the Distinguished Name (DN). DN is the owner entry in the certificate.
      edit role_mapping.yml of $ES_HOME/config/shield/role_mapping.yml
    • Add a role for the above Distinguished Name (DN).

      admin:
        	"CN=NoSQL, OU=NoSQL, O=Oracle, L=BLR, ST=KN, C=IN"
      
  7. After you start all ES Nodes, you check (if you have the private key of NoSQL FTS certificate available) the connection to ES with NoSQL store certificate using the following command:
    curl -k -E <nosql_fts.cert> --key <nosql_fts_privatekey>  -XGET 
    'https://<ES_NODE_HOSTNAME>:<HTTP_PORT>'

    Note:

    Note that the cURL command will not work if you do not provide the certificate and the private key in the cURL argument.

  8. Register your ES Cluster in NoSQL. To do so, start the command line and log in to KVStore CLI. You can then use the following command:
    plan register-es -clustername oracle_kv -host <ANY_ES_NODE_HOSTNAME> -port <HTTP_PORT> -secure true

    Note that if this works, it means all certificates setup are correct.

Create Full Text Index

You can create text indexes on the Oracle NoSQL Database table by using this DDL command:

CREATE FULLTEXT INDEX [if not exists] <index-name> ON <table-name>
    (<field-name> [ <mapping-spec> ], ...) 
    [ES_SHARDS = <n>] [ES_REPLICAS = <n>
    [OVERRIDE] [COMMENT <comment>]

where:

  • IF NOT EXISTS is optional, and it causes the CREATE FULLTEXT INDEX statement to be ignored if an index by that name currently exists. If this phrase is not specified, and an index using the specified name does currently exist, then the CREATE FULLTEXT INDEX statement will fail with an error.

  • index-name is the name of the index you want to create.

  • table-name is the name of the table that you want to index.

  • field-name is the name of a field that you want to index.

  • mapping-spec is a small JSON document that influences Elasticsearch's handling of the field. For more information, see "Mapping Full Text Index Field to Elasticsearch Index Field".

  • ES_SHARDS and ES_REPLICAS are optional properties that are transmitted to Elasticsearch when the corresponding text index is created. It is explained further below.

  • OVERRIDE is optional and is used to force a creation that otherwise would be prohibited.

For more information, see "Example - Create Full Text Index".

After you create the text index, you can verify the same by using the following statement:

show indexes -table <tableName>

After you create the index, you can run the show table command that lists the full text index that you have created. This command will give the table structure including the indexes that have been created for that table:

show table -name <tableName>

For example:

kv-> show table -name mytestTable
{
"type" : "table",
"name" : "mytestTable",
"owner" : null,
"comment" : null,
"shardKey" : [ "id" ],
"primaryKey" : [ "id" ],
"fields" : [ {
"name" : "id",
"type" : "INTEGER",
"nullable" : true,
"default" : null
}, {
"name" : "category",
"type" : "STRING",
"nullable" : true,
"default" : null
}, {
"name" : "txt",
"type" : "STRING",
"nullable" : true,
"default" : null
} ],
"indexes" : [ {
"name" : "mytestIndex",
"comment" : null,
"fields" : [ "category", "txt" ]
} ]
}

Note:

You cannot evolve an index. If you want to change the index definition, for example, add more columns to the index, you have to delete the index and create a new one.

To configure the number of shards and replicas for an Elasticsearch index, (for more information about these parameters, see Elasticsearch documentation) the keywords ES_SHARDS and ES_REPLICAS are included for these values in the CREATE FULLTEXT INDEX statement.

Note:

The assignments of ES_SHARDS and ES_REPLICAS are optional.

Since Elasticsearch does not allow to modify the number of shards after the index is created, it is recommended that the users pass a value for this number of shards if the default value is not suitable.

The values assigned to ES_SHARDS and ES_REPLICAS are given as the values for number_of_shards and number_of_replicas when you create an index in Elasticsearch. See Elasticsearch documentation for more information about Elasticsearch index properties.

While creating an index, the CREATE FULLTEXT INDEX statement uses the OVERRIDE flag, which allows to delete any index existing in Elasticsearch by the same name as would be created by the command.

For example:

CREATE FULLTEXT INDEX mytestIndex 
on  mytestTable (category, txt) OVERRIDE

Map Full Text Index Field to Elasticsearch Index Field

The CREATE FULLTEXT INDEX command is similar to the command that creates regular secondary indexes. One difference is the addition of the optional <mapping-spec> clause that follows the field name. If present, <mapping-spec> is a small JSON document that influences Elasticsearch's treatment of the field.

The other difference is optional settings of shards and replicas for the Elasticsearch index.

Note:

<mapping-spec> is an optional clause.

When a user creates a text index, an Elasticsearch index will be created and named as:

ondb.<store-name>.<table>.<textIndex>

Note:

Index Name is in lowercase.

For more information, see "Indexes Created in Elasticsearch".

The index contains a single mapping which is generated from the CREATE FULLTEXT INDEX command. See "Mapping" section in the Elasticsearch documentation. .

One aspect of the mapping is a list of fields that compose the document type, along with their types. In the absence of a <mapping-spec>, Oracle NoSQL Database will supply a default type for each field that corresponds to the type of the column in the Oracle NoSQL Database table. For example, if a table column A has the type string, then the mapping supplied to Elasticsearch will declare a field named A of type string. If you want Elasticsearch to treat column A as an integer despite its being a string in Oracle NoSQL Database, you must provide an explicit type by including a <mapping-spec> clause:

{ "type" : "integer" }

The <mapping-spec>, in addition to specifying the type of the field, can also contain any of a large set of parameters for determining how Elasticsearch handles the field. For example, if you want to store the field, but not index it (that is, not make it available for search), you would include the tuple "index" : "no". For information about a list of such parameters, see "Mapping Parameters" in the Elasticsearch documentation. .

You may supply a <mapping-spec> that does not include the type key, and Oracle NoSQL Database will supply the default type.

The following scalar column type will be mapped to ES, STRING, INTEGER, LONG, BOOLEAN, FLOAT, DOUBLE. For example, a Long ("LONG") in Oracle NoSQL Database will be mapped to Long ("long") in ES.

Note:

Indexed fields can include non-scalar types, which are specified in the same way and with the same limitations as those for secondary indexes. For more information, see "Indexing Non-Scalar Data Types" in Oracle NoSQL Database Getting Started with the Table APIGuide.

You may want to use mapping spec to put different analyzers on a field. For example, let us assume that you want two indexed fields: “category” and “txt”. “Category” uses a standard analyzer and for the field “txt” stemming is required, and you want to use a snowball analyzer. This example assumes you have Elasticsearch version 2.x.

Note that the snowball analyzer provides stemming for some languages including English. Stemming tries to index the stem of the word instead of the given word. For more information, see "Stemming" and "Snowball Analyzer". For example, the word “fitted” would get indexed as “fit”.

You must then do the following:

  1. execute 'CREATE FULLTEXT INDEX JokeIndex ON Joke (category, txt)'
  2. plan register-es -clustername elasticsearch -host localhost 
    -port 9200 -secure true/false  <CHANGE VALUES ACCORDINGLY>
  3. execute 'CREATE FULLTEXT INDEX JokeIndex ON Joke 
    (category{"type":"string","analyzer":"standard"}, 
    txt{"analyzer":"snowball"})'
  4. put table -name Joke -json 
    '{ "id" : 2, "category" : "self-referential", "txt" : 
    "Is it solipsistic in here, or is it just me?" }'
  5. curl -XGET 'http://localhost:7200/ondb.kvlightstore.joke.jokeindex/
    _search? q=txt:solipsist&pretty'

    Note that cURL will not work if you do not give the certificate and the private key in the cURL argument.

Note:

In a search call, the word “solipsistic” is actually queried by using the stemmed word “solipsist” and the effect of snowball analyzer is seen as it fetches a document with the word “solipsistic”.

NoSQL TIMESTAMP, a scalar data type, is also supported for full text index (for more information, see "Timestamp" in the Oracle NoSQL Database Getting Started with SQL for Oracle NoSQL Database Guide). The maximum precision of NoSQL TIMESTAMP is 9 digits (nanosecond precision) and ES "date" type only supports 3 (millisecond precision). The NoSQL TIMESTAMP type is therefore mapped to the following two kinds of ES data types based on the specified precision:

  1. If precision of TIMESTAMP type is in [0..3], then it maps to an ES "date" field:

    {
        "type" : "date",
        "format" : "strict_date_optional_time||epoch_millis" 
      }

    Here, the "date" type represents the date and time with fractional second.

  2. If precision of TIMESTAMP type is in [4..9], then it maps to an ES object that contains two fields:

    • "date" - Here the "date" type represents the date and time with no fractional second.

    • "nanos" - Is an integer type field. Here the "nanos" is the number of nano seconds less than 1 second.

    {
        "properties" : {
          "date" : {
          "type" : "date",
        "format" : "strict_date_optional_time||epoch_millis"
        },
        "nanos" : {
           "type" : "integer"
        }
      }

Hence, for the query on "date" field for TIMESTAMP with precision 0 ~ 3, the field name is "<timestamp-field>", and query on "date" field for TIMESTAMP with precision 4 ~ 9, the field name in the ES query string is "<timestamp-field>.date". See the following example:

  1. Register elasticsearch cluster with NoSQL store.
    plan register-es -clustername oracle_kv -host localhost 
    -port 9200 -secure true/false -wait
  2. Table textts0 contains TIMESTAMP(0) field and with a text index on it.
    execute "create table IF NOT EXISTS textts0 
    (id integer, ts0 TIMESTAMP(0), primary key(id))"
      execute "CREATE FULLTEXT INDEX IF NOT EXISTS idxts0 ON textts0 (ts0)"
      put table -name textts0 -json '{"id":1,"ts0":"1970-01-01"}'
      put table -name textts0 -json '{"id":2,"ts0":"2016-10-18T23:59:59"}'
      ...
  3. Query on ts0 TIMESTMAP(0) field in range "1970-01-01" ~ "2016-10-19", the field name is ts0:
    curl -XGET 'localhost:9200/ondb.kvstore.textts0.idxts0/_search?pretty' 
    -d '{ 
           "query": {
               "range": {
                   "ts0": {
                       "gte": "1970-01-01",        
                       "lt": "2016-10-19"
                     }
                }
            }
        }'
  4. Table textts6 contains the TIMESTAMP(6) field with a text index on it.
    execute "create table IF NOT EXISTS textts6 
    (id integer, ts6 TIMESTAMP(6), primary key(id))"
      execute "CREATE FULLTEXT INDEX IF NOT EXISTS idxts6 ON textts6 (ts6)"
      put table -name textts6 -json '{"id":1,"ts6":"1970-01-01"}'
      put table -name textts6 -json 
      '{"id":2,"ts6":"2016-10-18T23:59:59.999999"}'     
        ...
                    }
                }
            }
        }'
  5. Query on ts6 TIMESTMAP(6) in range "1970-01-01" ~ "2016-10-19", the field name is ts6.date.
    curl -XGET 'localhost:9200/ondb.kvstore.textts6.idxts6/_search?pretty' 
    -d '{
           "query": {
               "range": {
                   "ts6.date": {
                       "gte": "1970-01-01", 
                       "lt": "2016-10-19T00:00:00" 
                    }
                }
            }
        }'

Here is an example with full text index on TIMESTAMP(9), it is represented with "Date" and "Nanos" in ES. We can do query on one of them or on both of them:

  1. Register Elasticsearch cluster with NoSQL store.
    plan register-es -clustername oracle_kv -host localhost 
    -port 9200 -secure true/false -wait
  2. Create table, full text index on ts9.
    execute "CREATE TABLE IF NOT EXISTS textts9(id integer, 
    ts9 TIMESTAMP(9), primary key(id))"
    execute 'CREATE FULLTEXT INDEX IF NOT EXISTS idxts9 ON textts9 (ts9)'
  3. Load six rows to table:
    put table -name textts9 -json '{"id":1,"ts9":
    "2016-01-01T01:00:00.300000001"}'
    put table -name textts9 -json '{"id":2,"ts9":
    "2016-01-02T01:00:00.100000001"}'
    put table -name textts9 -json '{"id":3,"ts9":
    "2016-01-02T01:00:00.200000001"}'
    put table -name textts9 -json '{"id":4,"ts9":
    "2016-01-02T01:00:00.300000001"}'
    put table -name textts9 -json '{"id":5,"ts9":
    "2016-01-03T02:00:00.123456789"}'
  4. Query on ts9 with ts9.date as "2016-01-02T01:00:00".
    curl -XGET 'localhost:9200/ondb.kvstore.textts9.idxts9/_search?pretty'
    -d '{"query": {
        "term" : { "ts9.date":"2016-01-02T01:00:00"}
    }}'
    > 
    {
      "took" : 3,
      "timed_out" : false,
      "_shards" : {
        "total" : 5,
        "successful" : 5,
        "failed" : 0
      },
      "hits" : {
        "total" : 3,
        "max_score" : 1.9162908,
        "hits" : [ {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0003",
          "_score" : 1.9162908,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "3"
            },
            "ts9" : {
              "date" : "2016-01-02T01:00:00",
              "nanos" : "200000001"
            }
          }
        }, {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0002",
          "_score" : 1.0,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "2"
            },
            "ts9" : {
              "date" : "2016-01-02T01:00:00",
              "nanos" : "100000001"
            }
          }
        }, {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0004",
          "_score" : 0.30685282,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "4"
            },
            "ts9" : {
              "date" : "2016-01-02T01:00:00",
              "nanos" : "300000001"
            }
          }
        } ]
      }
    }
  5. Query on ts9 with ts9.nanos is 200000001.
    curl -XGET 'localhost:9200/ondb.kvstore.textts9.idxts9/_search?pretty' 
    -d '{"query": {
    >     "term" : { "ts9.nanos":200000001}
    > }}'
    {
      "took" : 1,
      "timed_out" : false,
      "_shards" : {
        "total" : 5,
        "successful" : 5,
        "failed" : 0
      },
      "hits" : {
        "total" : 1,
        "max_score" : 1.5108256,
        "hits" : [ {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0003",
          "_score" : 1.5108256,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "3"
            },
            "ts9" : {
              "date" : "2016-01-02T01:00:00",
              "nanos" : "200000001"
            }
          }
        } ]
      }
    }
  6. Query on ts9 with ts9.date as "2016-01-02T01:00:00" and ts9.nanos is 200000001.
    curl -XGET 'localhost:9200/ondb.kvstore.textts9.idxts9/_search?pretty'
    -d '{"query": {
        "bool" : {
          "must" : {
            "term" : { "ts9.date":"2016-01-02T01:00:00"}
          },
          "must" : {
            "term" : { "ts9.nanos":200000001}
          }
        }
       }
    }'
    > 
    {
      "took" : 4,
      "timed_out" : false,
      "_shards" : {
        "total" : 5,
        "successful" : 5,
        "failed" : 0
      },
      "hits" : {
        "total" : 1,
        "max_score" : 2.4402385,
        "hits" : [ {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0003",
          "_score" : 2.4402385,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "3"
            },
            "ts9" : {
              "date" : "2016-01-02T01:00:00",
              "nanos" : "200000001"
            }
          }
        } ]
      }
    }
  7. Query on ts9 with ts9.date as "2016-01-02T01:00:00" and ts9.nanos is in range of 100000000 ~ 300000000.
    curl -XGET 'localhost:9200/ondb.kvstore.textts9.idxts9/_search?pretty'
    -d '{"query": {
        "bool" : {
          "must" : {
              "term" : { "ts9.date":"2016-01-02T01:00:00"}
           },
          "must" : {
            "range" : { "ts9.nanos":{"gte":100000000,"lte":300000000}}
          }
        }
       }
    }'
    >
    {
      "took" : 12,
      "timed_out" : false,
      "_shards" : {
        "total" : 5,
        "successful" : 5,
        "failed" : 0
      },
      "hits" : {
        "total" : 2,
        "max_score" : 2.1615205,
        "hits" : [ {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0003",
          "_score" : 2.1615205,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "3"
            },
            "ts9" : {
              "date" : "2016-01-02T01:00:00",
              "nanos" : "200000001"
            }
          }
        }, {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0002",
          "_score" : 1.4142135,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "2"
            },
            "ts9" : {
              "date" : "2016-01-02T01:00:00",
              "nanos" : "100000001"
            }
          }
        } ]
      }
    }
  8. Sort on ts9.date, ts9.nanos.
    curl -XGET 'localhost:9200/ondb.kvstore.textts9.idxts9/_search?pretty'
    -d '{"sort":[{"ts9.date":"asc"}, {"ts9.nanos":"asc"}]}'
    >
    {
      "took" : 2,
      "timed_out" : false,
      "_shards" : {
        "total" : 5,
        "successful" : 5,
        "failed" : 0
      },
      "hits" : {
        "total" : 6,
        "max_score" : null,
        "hits" : [ {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0001",
          "_score" : null,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "1"
            },
            "ts9" : {
              "date" : "2016-01-01T01:00:00",
              "nanos" : "300000001"
            }
          },
          "sort" : [ 1451610000000, 300000001 ]
        }, {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0002",
          "_score" : null,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "2"
            },
            "ts9" : {
              "date" : "2016-01-02T01:00:00",
              "nanos" : "100000001"
            }
          },
          "sort" : [ 1451696400000, 100000001 ]
        }, {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0003",
          "_score" : null,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "3"
            },
            "ts9" : {
              "date" : "2016-01-02T01:00:00",
              "nanos" : "200000001"
            }
          },
          "sort" : [ 1451696400000, 200000001 ]
        }, {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0004",
          "_score" : null,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "4"
            },
            "ts9" : {
              "date" : "2016-01-02T01:00:00",
              "nanos" : "300000001"
            }
          },
          "sort" : [ 1451696400000, 300000001 ]
        }, {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0005",
          "_score" : null,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "5"
            },
            "ts9" : {
              "date" : "2016-01-03T02:00:00",
              "nanos" : "123456789"
            }
          },
          "sort" : [ 1451786400000, 123456789 ]
        }, {
          "_index" : "ondb.kvstore.textts9.idxts9",
          "_type" : "text_index_mapping",
          "_id" : "/v/¨0006",
          "_score" : null,
          "_source" : {
            "_pkey" : {
              "_table" : "textts9",
              "id" : "6"
            },
            "ts9" : {
              "date" : "2016-01-03T06:00:00",
              "nanos" : "600000001"
            }
          },
          "sort" : [ 1451800800000, 600000001 ]
        } ]
      }
    }

Indexes Created in Elasticsearch

For each text index in Oracle NoSQL Database store, an Elasticsearch index is created with a unique name:

ondb.<store-name>.<table>.<textIndex>

For example, for a text index mytestIndex in table mytestTable in store mystore, the corresponding Elasticsearch index would be:

ondb.mystore.mytesttable.mytestindex

Here the name of the index is ondb.<storename>.<tablename>.<indexname>, where <tablename> itself might contain multiple dotted component names, if it is the name of a child table.

Note:

You will notice that an extra index is created in Elasticsearch with a name like ondb.<store-name>._checkpoint. You must not remove or modify this index. It contains internal information to help with recovery during restarts of Oracle NoSQL Database components.

Example - Create Full Text Index

  1. Create a table as follows:

    kv-> execute 'CREATE TABLE mytestTable 
    (id INTEGER, category STRING, txt STRING, PRIMARY KEY (id))'
    Statement completed successfully
  2. Use the following command to make a text index on the table that indexes the category and txt columns:

    kv-> execute 'CREATE FULLTEXT INDEX mytestIndex 
    ON mytestTable (category, txt)'
    Statement completed successfully
  3. Insert data into the “mytestTable” Table:

    kv-> put table -name mytestTable -json 
    '{ "id" : 1, "category" : "pun", "txt" : "Spring is natures way of
    saying, Let us party" }'
    Operation successful, row inserted.
    kv-> put table -name mytestTable -json 
    '{ "id" : 2, "category" : "self-referential", "txt" : "I am thankful
    for the mess to clean after a party because it means I have been
    surrounded by friends" }'
    Operation successful, row inserted.
    kv-> put table -name mytestTable -json 
    '{ "id" : 3, "category" : "stupid", "txt" : "Doing nothing is hard,
    you never know when you are done" }'
    Operation successful, row inserted.
    kv-> put table -name mytestTable -json 
    '{ "id" : 4, "category" : "thoughtful", "txt" : "Do not worry if plan
    A fails, there are 25 more letters in the alphabet" }'
    Operation successful, row inserted.
    kv-> get table -name mytestTable
    {"id":4,"category":"thoughtful","txt":"Do not worry if plan A fails, 
    there are 25 more letters in the alphabet"}
    {"id":1,"category":"pun","txt":"Spring is natures way of saying, 
    Let us party"}
    {"id":2,"category":"self-referential","txt":"I am thankful for the
    mess to clean after a party because it means I have been surrounded
    by friends"}
    {"id":3,"category":"stupid","txt":"Doing nothing is hard, you never
    know when you are done"}
    4 rows returned

    Note:

    As you enter these records, Oracle NoSQL Database produces documents that are sent to Elasticsearch for indexing. You can find the documents by searching the Elasticsearch cluster. For more information, see "Elasticsearch: The Definitive Guide".

  4. Search Elasticsearch cluster to find the document that was created in the steps before. Elasticsearch allows REST calls as queries, so we can do REST calls using the cURL command. The cURL command sends a request to the Elasticsearch node's HTTP port.

    Note:

    cURL is a common utility program that can issue and display the results of HTTP requests. Currently, it is supported on Microsoft Windows, Linux, and Mac OS X. For more information, see "cURL" and "Search Using the cURL Command". However, cURL is an alternative method/option for querying Elasticsearch. The other options can be using:

    • elasticsearch-head, a web front end for browsing and interacting with an Elasticsearch cluster, helps to query. elasticsearch-head is part of ES standard installation and can be enabled by following the steps mentioned in "Installing and Running" section.

      For more information, see "Search Using elasticsearch-head".

    • Java API commands, see "Search Text Index Using JAVA APIs".

Search Using the cURL Command
  • Use the following cURL command to produce every document that is indexed with the mytestIndex mapping:

    curl -s localhost:9200/ondb.mystore
    .mytesttable.mytestindex/_search\?pretty
    {
      "took" : 4,
      "timed_out" : false,
      "_shards" : {
        "total" : 5,
        "successful" : 5,
        "failed" : 0
      },
      "hits" : {
        "total" : 4,
        "max_score" : 1.0,
        "hits" : [ {
          "_index" : "ondb.mystore.mytesttable.mytestindex",
          "_type" : "text_index_mapping",
          "_id" : "/w/¨0003",
          "_score" : 1.0,
          "_source":{"_pkey":{"_table":"mytestTable","id":"3"},"category":
          "stupid","txt":       "Doing nothing is hard, you never know when
          you are done"}
        }, {
          "_index" : "ondb.mystore.mytesttable.mytestindex",
          "_type" : "text_index_mapping",
          "_id" : "/w/¨0002",
          "_score" : 1.0,
          "_source":{"_pkey":{"_table":"mytestTable","id":"2"},"category":
          "self-referential","txt": "I am thankful for the mess to clean
          after a party because it means I have been surrounded by friends"}
        }, {
          "_index" : "ondb.mystore.mytesttable.mytestindex",
          "_type" : "text_index_mapping",
          "_id" : "/w/¨0004",
          "_score" : 1.0,
          "_source":{"_pkey":{"_table":"mytestTable","id":"4"},"category":
          "thoughtful","txt": "Do not worry if plan A fails, there are 25
          more letters in the alphabet"}
        }, {
          "_index" : "ondb.mystore.mytesttable.mytestindex",
          "_type" : "text_index_mapping",
          "_id" : "/w/¨0001",
          "_score" : 1.0,
          "_source":{"_pkey":{"_table":"mytestTable","id":"1"},"category":
          "pun","txt": "Spring is natures way of saying, Let us party"}
        } ]
      }
    }

    Note:

    • The name that we gave to the text index in the CREATE FULLTEXT statement was mytestIndex, so we are restricting the search to the Elasticsearch index associated with that Oracle NoSQL Database text index.

    • The cURL command above asked to search for every record in mytestIndex (there is no search term, so every record matches the search). The argument "pretty" means to pretty-print the output.

    • The result contains an array of "hits" with a single member. The interesting property is "_source", which contains "_pkey" which has the table and primary key for the original KVStore record; and the two indexed fields "category" and "txt".

    • Each item has a “_score” field which Elasticsearch uses to indicate the level of relevance for search hits. For more information, see "What is Relevance?" in the Elasticsearch documentation.
  • You can narrow the search by putting a search term into request, using "q=" like the example below:

    curl -s localhost:9200/ondb.mystore.mytesttable
    .mytestindex/_search\?q=25\&pretty
    {
      "took" : 11,
      "timed_out" : false,
      "_shards" : {
        "total" : 5,
        "successful" : 5,
        "failed" : 0
      },
      "hits" : {
        "total" : 1,
        "max_score" : 0.25,
        "hits" : [ {
          "_index" : "ondb.mystore.mytesttable.mytestindex",
          "_type" : "text_index_mapping",
          "_id" : "/w/¨0004",
          "_score" : 0.25,
          "_source":{"_pkey":{"_table":"mytestTable","id":"4"},"category":
          "thoughtful","txt": "Do not worry if plan A fails, there are 25
          more letters in the alphabet"}
        } ]
      }
    }

See Also:

What is Relevance?

Search Using elasticsearch-head

The following are the two query types:

  • An aggregated query looks as follows:

    Aggregate Search
  • A query with “match_all” looks as follows:

    Match_All Search

For more information, see "elasticsearch-head" in the Elasticsearch documentation.

See Also:

elasticsearch-head

Search Text Index Using JAVA APIs

For information about creating Oracle NoSQL Database tables, see "Introducing Oracle NoSQL Database Tables and Indexes" in the Oracle NoSQL Database Getting Started with the Table API Guide. To create a text index, you must use the method KVStore.executeSync to issue the DDL command CREATE FULLTEXT INDEX, as mentioned in the section "Example - Create Full Text Index".

Searching the index, on the other hand, must be done using Elasticsearch APIs. Here is a very simple example of a program that searches a document type that corresponds to an Oracle NoSQL Database text index. This command, given the arguments "localhost 9300 kvstore MyIndex 25" produces exactly the same output as the cURL command in the section "Search using cURL Command".

Note:

To build and run this program, you will need all .jar files supplied with the Elasticsearch distribution in your class path. One way to achieve this would be to use the Java command's class path wildcard feature, for example:
java -cp ".:/home/…/elasticsearch-2.0.0/lib/*" \
    DoSearch localhost 9200 kvstore mytestIndex 25

Note that Oracle NoSQL Database KVStore does not bundle Elasticsearch libraries from version 18.1.

See the following sample program:

import java.net.InetAddress;

import org.elasticsearch.action.search.SearchRequestBuilder;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.transport.TransportClient;
import org.elasticsearch.common.transport.InetSocketTransportAddress;
import org.elasticsearch.index.query.QueryBuilders;

public class DoSearch {

    public static void main(String args[]) throws Exception {
        
        if (args.length < 4 || args.length > 5) {
            System.err.println
                ("Usage: DoSearch <esTransportHost> <esTransportPort> " +
                 "<kvStoreName> <tableName> <indexName> [search-term]");
        }
        String esTransportHost = args[0];
        int esTransportPort = Integer.parseInt(args[1]);
        String kvStoreName = args[2];
        String tableName = args[3];
        String indexName = args[4];
        String searchTerm = args.length > 5 ? args[5] : null;

        TransportClient client =
            TransportClient.builder().build();

        client.addTransportAddress
            (new InetSocketTransportAddress
             (InetAddress.getByName(esTransportHost), esTransportPort));

        final String esIndexName = "ondb." + kvStoreName.toLowerCase() 
        + "." +
            tableName + "." + indexName;
            
        SearchRequestBuilder sb = client.prepareSearch(esIndexName);
        if (searchTerm != null) {
            sb.setQuery(QueryBuilders.simpleQueryStringQuery(searchTerm));
        }
            
        SearchResponse response = sb.execute().actionGet();
        System.out.println(response);
    }
}

Drop Index

Text indexes share the same namespace as regular secondary indexes within a table. You can use the same statement to remove either type of index. DROP INDEX on a text index stops the population of the index from Oracle NoSQL Database shards, and removes the mapping and all related documents from Elasticsearch.

If a table to which a text index mapping refers is dropped, the text index will automatically be dropped as part of the process.

You can drop text indexes by using this DDL command:

DROP INDEX [IF EXISTS] index_name ON table_name [OVERRIDE]
DROP TABLE [IF EXISTS] table_name

For example:

kv-> execute 'drop table mytestTable'
Statement completed successfully

While deleting an index, you can use the OVERRIDE flag. The DROP INDEX statement uses the OVERRIDE flag to enable overriding of the default constraints:

DROP INDEX [IF EXISTS] index_name ON table_name [OVERRIDE]

For example:

DROP INDEX mytestIndex on mytestTable OVERRIDE

For more information about the constraints, see "Troubleshooting".

See Also:

Troubleshooting

Troubleshooting

The most common problems that might arise when using Oracle NoSQL Database with Elasticsearch are those related to data transfer failure and data not getting indexed. For information, see "Troubleshooting" in the Elasticsearch documentation.

The following sections describe some of the causes of these issues and provide steps you can follow to resolve these problems. Here are some things you can check to verify whether the data is successfully transferred and indexed:

  • You can verify Oracle NoSQL Database's information that it uses to connect to the Elasticsearch cluster by issuing the runadmin command show parameters -service sn1 where sn1 is the ID of any storage node in the store. The parameters of interest are searchClusterName, which is the Elasticsearch cluster name; and searchClusterMembers which is a list of host:port representing the nodes in the Elasticsearch cluster.

    The searchClusterSecure parameter provides us information on whether FTS is setup in a secure or non secure mode .

  • Be sure that when you are registering the Elasticsearch cluster, you give an ES node's HTTP port and not its transport port.

  • You can do a quick check of connectivity from the Oracle NoSQL Database master administrative node by re-registering the Elasticsearch cluster using the command plan register-es. This plan is safe to run multiple times. If it runs without errors, then the Elasticsearch cluster is at least available to the administrative node.

  • Both Oracle NoSQL Database and Elasticsearch nodes should be configured to listen on appropriate network interfaces. If an Oracle NoSQL Database store is using a public interface, but Elasticsearch is using the loopback interface, then they will not be able to communicate properly. The network interface is configured for an Elasticsearch node by the property network.host in the elasticsearch.yml, and for an Oracle NoSQL Database storage node by the -host option to the makebootconfig command.

  • You can issue a command like curl http://localhost:9200/_cat/indices to get a list of the indexes in an Elasticsearch cluster, to verify that they correspond to the text indexes you created in Oracle NoSQL Database. The output of this command will also show an indication of the status of each index using the color names green, yellow, and red. If the status is red, then the index cannot be populated.

    Note:

     

    If Elasticsearch cluster is secure, then cURL needs an argument for private key and the certificate.

     
  • If you see unexplained failures to index, see the RepNode logs for SEVERE log messages or exceptions related to Elasticsearch. For information about finding logs, see "Software Monitoring" in the Oracle NoSQL Database Runbook.

  • The logs might show some connection refused errors from ESHttpClient, but that does not actually mean that the request has failed. Requests are re-tried on other Elasticsearch nodes and may have succeeded.

  • With a heavily update-dominated workload, the Elasticsearch cluster can lag quite far behind Oracle NoSQL Database. It can take minutes for a new Oracle NoSQL Database record to be reflected in search results from Elasticsearch. This issue can be mitigated by increasing the number of Elasticsearch nodes, tuning Elasticsearch, and especially by storing Elasticsearch data on solid state disks.

  • Compare Record counts

    You can compare the number of records in table for Oracle NoSQL Database with the number of documents in your Elasticsearch cluster (this assumes that the Oracle NoSQL Database has a static number of items). This is particularly useful, for instance your cluster is in a test environment where the number of records is set and you do not add more. To find the number of records in Oracle NoSQL Database, use the following statement in the Command Line Interface (CLI):

    kv-> aggregate table -name mytestTable -count
    Row count: 100

    To get the number of records for Elasticsearch, use the following command: http://localhost:9200/ondb.mystore.joke.jokeindex/_count

    If it is successful, you get the following response:

    {"count":100,"_shards":
        {
        "total":5,"successful":5,"failed":0
        }
    }

    The count returned by Elasticsearch is the same value as the number of records shown in the CLI. The matching values provide assurance that all records have been transferred and indexed by Elasticsearch.

  • ElasticServer Version Mismatch

    The FTS HTTP client is aware of the ES cluster version, but it does not throw any version mismatch error.

    Note that for the Oracle NoSQL Database version 18.1, we support ES version 2.x.

  • Check Elasticsearch Mappings

    You can influence the mapping by including a mapping-spec in the original CREATE command, but you cannot provide your own mapping. Be aware that this default mapping from Elasticsearch includes assumptions about data types and data structures in your documents. On the basis of these assumptions, Elasticsearch may omit your document from the index. For example, a string mapped to an object will not be indexed in the current version. Also, a record that contains only empty strings or nulls in the indexed fields will be omitted. For more information about expected data structures, see "Mapping".

  • The population of a new text index begins immediately when it is created. If the existing database is large, this operation can take quite a long time. Furthermore, when a text index is created, if other text indexes that were created earlier already exist, they too will be populated over from scratch. This is because all text indexes share the single gateway in server to stream data to the Elasticsearch cluster, and due to the newly added index, the gateway has to start from the very beginning, resulting in all indexes getting re-populating. For these reasons it is best to create all the text indexes that you need at the same time, and preferably before the database has been populated.

  • When you execute the CREATE FULLTEXT INDEX statement, Elasticsearch determines whether an index already exists with the same name. If such an index exists, the CREATE statement will fail, unless the OVERRIDE flag is given; in which case the existing index will be deleted before the new index is created by the same name. For more information about using the OVERRIDE flag, see "Create Full Text Index".

  • When the DROP INDEX statement is executed to remove a text index, the health status of the Elasticsearch cluster must be GREEN, unless the OVERRIDE flag is given, in which case the deletion will proceed. The metadata describing the index will be removed from Oracle NoSQL Database. However, there is a possibility that the corresponding Elasticsearch index will not be removed. For more information about using the OVERRIDE flag, see "Drop Index".

    This constraint helps to avoid an issue where an Elasticsearch index deletion can be undone when a node that was offline during the deletion returns to the cluster.


Oracle NoSQL Database Full Text Search, Release 18.1

E85382-02

Copyright © 2011, 2018, Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.