| Ian Maxon | 49d15b2 | 2020-12-06 16:23:00 -0800 | [diff] [blame^] | 1 | <!DOCTYPE html> |
| 2 | <!-- |
| 3 | | Generated by Apache Maven Doxia Site Renderer 1.8.1 from src/site/markdown/sqlpp/fulltext.md at 2020-12-06 |
| 4 | | Rendered using Apache Maven Fluido Skin 1.7 |
| 5 | --> |
| 6 | <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> |
| 7 | <head> |
| 8 | <meta charset="UTF-8" /> |
| 9 | <meta name="viewport" content="width=device-width, initial-scale=1.0" /> |
| 10 | <meta name="Date-Revision-yyyymmdd" content="20201206" /> |
| 11 | <meta http-equiv="Content-Language" content="en" /> |
| 12 | <title>AsterixDB – AsterixDB Support of Full-text search queries</title> |
| 13 | <link rel="stylesheet" href="../css/apache-maven-fluido-1.7.min.css" /> |
| 14 | <link rel="stylesheet" href="../css/site.css" /> |
| 15 | <link rel="stylesheet" href="../css/print.css" media="print" /> |
| 16 | <script type="text/javascript" src="../js/apache-maven-fluido-1.7.min.js"></script> |
| 17 | |
| 18 | </head> |
| 19 | <body class="topBarDisabled"> |
| 20 | <div class="container-fluid"> |
| 21 | <div id="banner"> |
| 22 | <div class="pull-left"><a href=".././" id="bannerLeft"><img src="../images/asterixlogo.png" alt="AsterixDB"/></a></div> |
| 23 | <div class="pull-right"></div> |
| 24 | <div class="clear"><hr/></div> |
| 25 | </div> |
| 26 | |
| 27 | <div id="breadcrumbs"> |
| 28 | <ul class="breadcrumb"> |
| 29 | <li id="publishDate">Last Published: 2020-12-06</li> |
| 30 | <li id="projectVersion" class="pull-right">Version: 0.9.6-SNAPSHOT</li> |
| 31 | <li class="pull-right"><a href="../index.html" title="Documentation Home">Documentation Home</a></li> |
| 32 | </ul> |
| 33 | </div> |
| 34 | <div class="row-fluid"> |
| 35 | <div id="leftColumn" class="span2"> |
| 36 | <div class="well sidebar-nav"> |
| 37 | <ul class="nav nav-list"> |
| 38 | <li class="nav-header">Get Started - Installation</li> |
| 39 | <li><a href="../ncservice.html" title="Option 1: using NCService"><span class="none"></span>Option 1: using NCService</a></li> |
| 40 | <li><a href="../ansible.html" title="Option 2: using Ansible"><span class="none"></span>Option 2: using Ansible</a></li> |
| 41 | <li><a href="../aws.html" title="Option 3: using Amazon Web Services"><span class="none"></span>Option 3: using Amazon Web Services</a></li> |
| 42 | <li class="nav-header">AsterixDB Primer</li> |
| 43 | <li><a href="../sqlpp/primer-sqlpp.html" title="Using SQL++"><span class="none"></span>Using SQL++</a></li> |
| 44 | <li class="nav-header">Data Model</li> |
| 45 | <li><a href="../datamodel.html" title="The Asterix Data Model"><span class="none"></span>The Asterix Data Model</a></li> |
| 46 | <li class="nav-header">Queries</li> |
| 47 | <li><a href="../sqlpp/manual.html" title="The SQL++ Query Language"><span class="none"></span>The SQL++ Query Language</a></li> |
| 48 | <li><a href="../SQLPP.html" title="Raw SQL++ Grammar"><span class="none"></span>Raw SQL++ Grammar</a></li> |
| 49 | <li><a href="../sqlpp/builtins.html" title="Builtin Functions"><span class="none"></span>Builtin Functions</a></li> |
| 50 | <li class="nav-header">API/SDK</li> |
| 51 | <li><a href="../api.html" title="HTTP API"><span class="none"></span>HTTP API</a></li> |
| 52 | <li><a href="../csv.html" title="CSV Output"><span class="none"></span>CSV Output</a></li> |
| 53 | <li class="nav-header">Advanced Features</li> |
| 54 | <li><a href="../aql/externaldata.html" title="Accessing External Data"><span class="none"></span>Accessing External Data</a></li> |
| 55 | <li><a href="../feeds.html" title="Data Ingestion with Feeds"><span class="none"></span>Data Ingestion with Feeds</a></li> |
| 56 | <li><a href="../udf.html" title="User Defined Functions"><span class="none"></span>User Defined Functions</a></li> |
| 57 | <li><a href="../sqlpp/filters.html" title="Filter-Based LSM Index Acceleration"><span class="none"></span>Filter-Based LSM Index Acceleration</a></li> |
| 58 | <li class="active"><a href="#"><span class="none"></span>Support of Full-text Queries</a></li> |
| 59 | <li><a href="../sqlpp/similarity.html" title="Support of Similarity Queries"><span class="none"></span>Support of Similarity Queries</a></li> |
| 60 | <li><a href="../interval_join.html" title="Support of Interval Joins"><span class="none"></span>Support of Interval Joins</a></li> |
| 61 | <li class="nav-header">Deprecated</li> |
| 62 | <li><a href="../aql/primer.html" title="AsterixDB Primer: Using AQL"><span class="none"></span>AsterixDB Primer: Using AQL</a></li> |
| 63 | <li><a href="../aql/manual.html" title="Queries: The Asterix Query Language (AQL)"><span class="none"></span>Queries: The Asterix Query Language (AQL)</a></li> |
| 64 | <li><a href="../aql/builtins.html" title="Queries: Builtin Functions (AQL)"><span class="none"></span>Queries: Builtin Functions (AQL)</a></li> |
| 65 | </ul> |
| 66 | <hr /> |
| 67 | <div id="poweredBy"> |
| 68 | <div class="clear"></div> |
| 69 | <div class="clear"></div> |
| 70 | <div class="clear"></div> |
| 71 | <div class="clear"></div> |
| 72 | <a href=".././" title="AsterixDB" class="builtBy"><img class="builtBy" alt="AsterixDB" src="../images/asterixlogo.png" /></a> |
| 73 | </div> |
| 74 | </div> |
| 75 | </div> |
| 76 | <div id="bodyColumn" class="span10" > |
| 77 | <!-- |
| 78 | ! Licensed to the Apache Software Foundation (ASF) under one |
| 79 | ! or more contributor license agreements. See the NOTICE file |
| 80 | ! distributed with this work for additional information |
| 81 | ! regarding copyright ownership. The ASF licenses this file |
| 82 | ! to you under the Apache License, Version 2.0 (the |
| 83 | ! "License"); you may not use this file except in compliance |
| 84 | ! with the License. You may obtain a copy of the License at |
| 85 | ! |
| 86 | ! http://www.apache.org/licenses/LICENSE-2.0 |
| 87 | ! |
| 88 | ! Unless required by applicable law or agreed to in writing, |
| 89 | ! software distributed under the License is distributed on an |
| 90 | ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| 91 | ! KIND, either express or implied. See the License for the |
| 92 | ! specific language governing permissions and limitations |
| 93 | ! under the License. |
| 94 | !--> |
| 95 | <h1>AsterixDB Support of Full-text search queries</h1> |
| 96 | <div class="section"> |
| 97 | <h2><a name="Table_of_Contents"></a><a name="toc" id="toc">Table of Contents</a></h2> |
| 98 | <ul> |
| 99 | |
| 100 | <li><a href="#Motivation">Motivation</a></li> |
| 101 | <li><a href="#Syntax">Syntax</a></li> |
| 102 | <li><a href="#FulltextIndex">Creating and utilizing a Full-text index</a></li> |
| 103 | </ul></div> |
| 104 | <div class="section"> |
| 105 | <h2><a name="Motivation_.5BBack_to_TOC.5D"></a><a name="Motivation" id="Motivation">Motivation</a> <font size="4"><a href="#toc">[Back to TOC]</a></font></h2> |
| 106 | <p>Full-Text Search (FTS) queries are widely used in applications where users need to find records that satisfy an FTS predicate, i.e., where simple string-based matching is not sufficient. These queries are important when finding documents that contain a certain keyword is crucial. FTS queries are different from substring matching queries in that FTS queries find their query predicates as exact keywords in the given string, rather than treating a query predicate as a sequence of characters. For example, an FTS query that finds “rain” correctly returns a document when it contains “rain” as a word. However, a substring-matching query returns a document whenever it contains “rain” as a substring, for instance, a document with “brain” or “training” would be returned as well.</p></div> |
| 107 | <div class="section"> |
| 108 | <h2><a name="Syntax_.5BBack_to_TOC.5D"></a><a name="Syntax" id="Syntax">Syntax</a> <font size="4"><a href="#toc">[Back to TOC]</a></font></h2> |
| 109 | <p>The syntax of AsterixDB FTS follows a portion of the XQuery FullText Search syntax. Two basic forms are as follows:</p> |
| 110 | |
| 111 | <div> |
| 112 | <div> |
| 113 | <pre class="source"> ftcontains(Expression1, Expression2, {FullTextOption}) |
| 114 | ftcontains(Expression1, Expression2) |
| 115 | </pre></div></div> |
| 116 | |
| 117 | <p>For example, we can execute the following query to find Chirp messages where the <tt>messageText</tt> field includes “voice” as a word. Please note that an FTS search is case-insensitive. Thus, “Voice” or “voice” will be evaluated as the same word.</p> |
| 118 | |
| 119 | <div> |
| 120 | <div> |
| 121 | <pre class="source"> use TinySocial; |
| 122 | |
| 123 | select element {"chirpId": msg.chirpId} |
| 124 | from ChirpMessages msg |
| 125 | where ftcontains(msg.messageText, "voice", {"mode":"any"}); |
| 126 | </pre></div></div> |
| 127 | |
| 128 | <p>The DDL and DML of TinySocial can be found in <a href="../sqlpp/primer-sqlpp.html#ADM:_Modeling_Semistructured_Data_in_AsterixDB">ADM: Modeling Semistructured Data in AsterixDB</a>.</p> |
| 129 | <p>The <tt>Expression1</tt> is an expression that should be evaluable as a string at runtime as in the above example where <tt>msg.messageText</tt> is a string field. The <tt>Expression2</tt> can be a string, an (un)ordered list of string value(s), or an expression. In the last case, the given expression should be evaluable into one of the first two types, i.e., into a string value or an (un)ordered list of string value(s).</p> |
| 130 | <p>The following examples are all valid expressions.</p> |
| 131 | |
| 132 | <div> |
| 133 | <div> |
| 134 | <pre class="source"> ... where ftcontains(msg.messageText, "sound") |
| 135 | ... where ftcontains(msg.messageText, "sound", {"mode":"any"}) |
| 136 | ... where ftcontains(msg.messageText, ["sound", "system"], {"mode":"any"}) |
| 137 | ... where ftcontains(msg.messageText, {{"speed", "stand", "customization"}}, {"mode":"all"}) |
| 138 | </pre></div></div> |
| 139 | |
| 140 | <p>The last <tt>FullTextOption</tt> parameter clarifies the given FTS request. If you omit the <tt>FullTextOption</tt> parameter, then the default value will be set for each possible option. Currently, we only have one option named <tt>mode</tt>. And as we extend the FTS feature, more options will be added. Please note that the format of <tt>FullTextOption</tt> is a record, thus you need to put the option(s) in a record <tt>{}</tt>. The <tt>mode</tt> option indicates whether the given FTS query is a conjunctive (AND) or disjunctive (OR) search request. This option can be either <tt>“all”</tt> (AND) or <tt>“any”</tt> (OR). The default value for <tt>mode</tt> is <tt>“all”</tt>. If one specifies <tt>“any”</tt>, a disjunctive search will be conducted. For example, the following query will find documents whose <tt>messageText</tt> field contains “sound” or “system”, so a document will be returned if it contains either “sound”, “system”, or both of the keywords.</p> |
| 141 | |
| 142 | <div> |
| 143 | <div> |
| 144 | <pre class="source"> ... where ftcontains(msg.messageText, ["sound", "system"], {"mode":"any"}) |
| 145 | </pre></div></div> |
| 146 | |
| 147 | <p>The other option parameter,<tt>“all”</tt>, specifies a conjunctive search. The following examples will find the documents whose <tt>messageText</tt> field contains both “sound” and “system”. If a document contains only “sound” or “system” but not both, it will not be returned.</p> |
| 148 | |
| 149 | <div> |
| 150 | <div> |
| 151 | <pre class="source"> ... where ftcontains(msg.messageText, ["sound", "system"], {"mode":"all"}) |
| 152 | ... where ftcontains(msg.messageText, ["sound", "system"]) |
| 153 | </pre></div></div> |
| 154 | |
| 155 | <p>Currently AsterixDB doesn’t (yet) support phrase searches, so the following query will not work.</p> |
| 156 | |
| 157 | <div> |
| 158 | <div> |
| 159 | <pre class="source"> ... where ftcontains(msg.messageText, "sound system", {"mode":"any"}) |
| 160 | </pre></div></div> |
| 161 | |
| 162 | <p>As a workaround solution, the following query can be used to achieve a roughly similar goal. The difference is that the following queries will find documents where <tt>msg.messageText</tt> contains both “sound” and “system”, but the order and adjacency of “sound” and “system” are not checked, unlike in a phrase search. As a result, the query below would also return documents with “sound system can be installed.”, “system sound is perfect.”, or “sound is not clear. You may need to install a new system.”</p> |
| 163 | |
| 164 | <div> |
| 165 | <div> |
| 166 | <pre class="source"> ... where ftcontains(msg.messageText, ["sound", "system"], {"mode":"all"}) |
| 167 | ... where ftcontains(msg.messageText, ["sound", "system"]) |
| 168 | </pre></div></div> |
| 169 | </div> |
| 170 | <div class="section"> |
| 171 | <h2><a name="Creating_and_utilizing_a_Full-text_index_.5BBack_to_TOC.5D"></a><a name="FulltextIndex" id="FulltextIndex">Creating and utilizing a Full-text index</a> <font size="4"><a href="#toc">[Back to TOC]</a></font></h2> |
| 172 | <p>When there is a full-text index on the field that is being searched, rather than scanning all records, AsterixDB can utilize that index to expedite the execution of a FTS query. To create a full-text index, you need to specify the index type as <tt>fulltext</tt> in your DDL statement. For instance, the following DDL statement create a full-text index on the <tt>GleambookMessages.message</tt> attribute. Note that a full-text index cannot be built on a dataset with the variable-length primary key (e.g., string).</p> |
| 173 | |
| 174 | <div> |
| 175 | <div> |
| 176 | <pre class="source">use TinySocial; |
| 177 | |
| 178 | create index messageFTSIdx on GleambookMessages(message) type fulltext; |
| 179 | </pre></div></div></div> |
| 180 | </div> |
| 181 | </div> |
| 182 | </div> |
| 183 | <hr/> |
| 184 | <footer> |
| 185 | <div class="container-fluid"> |
| 186 | <div class="row-fluid"> |
| 187 | <div class="row-fluid">Apache AsterixDB, AsterixDB, Apache, the Apache |
| 188 | feather logo, and the Apache AsterixDB project logo are either |
| 189 | registered trademarks or trademarks of The Apache Software |
| 190 | Foundation in the United States and other countries. |
| 191 | All other marks mentioned may be trademarks or registered |
| 192 | trademarks of their respective owners. |
| 193 | </div> |
| 194 | </div> |
| 195 | </div> |
| 196 | </footer> |
| 197 | </body> |
| 198 | </html> |