Editing Elasticsearch


== Concept mapping ==

<table class="wikisort" border="1">
            <tr>
                <th>MySQL (RDBMS)</th>
                <th>Elasticsearch</th>
                <th>Excel</th>
                <th>Example Value (Bookstore)</th>
            </tr>
            <tr>
                <td>Table</td>
                <td>Index</td>
                <td>Worksheet</td>
                <td>books</td>
            </tr>
            <tr>
                <td>Row</td>
                <td>Document</td>
                <td>Row</td>
                <td class="code">{ "id": 1, "title": "The Great Gatsby", "author": "F. Scott Fitzgerald", "price": 9.99 }</td>
            </tr>
            <tr>
                <td>Field</td>
                <td>Field</td>
                <td>Column</td>
                <td>title</td>
            </tr>
            <tr>
                <td>Schema</td>
                <td>Mapping</td>
                <td>Column Headers</td>
                <td class="code"><pre>{
    "properties": {
        "title": { "type": "text" },
        "author": { "type": "keyword" },
        "price": { "type": "float" }
    }
}</pre></td>
            </tr>
            <tr>
                <td>SQL</td>
                <td>DSL</td>
                <td>Formulas/Filters</td>
                <td class="code">SQL: SELECT * FROM books WHERE author = 'F. Scott Fitzgerald'
DSL: { "query": { "term": { "author": "F. Scott Fitzgerald" } } }
Excel: Filter column "author" for "F. Scott Fitzgerald"</td>
            </tr>
    </table>

== Count the number of documents ==

Elasticsearch: [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-count.html#search-count-api-example Count API | Elasticsearch Guide [8.15] Elastic]

<pre>
GET /MY_INXEX/_count
</pre>

MySQL [https://dev.mysql.com/doc/refman/8.4/en/counting-rows.html MySQL :: MySQL 8.4 Reference Manual :: 5.3.4.8 Counting Rows]
<pre>
SELECT COUNT(*) FROM MY_TABLE
</pre>

== Truncate the index or table ==
Elasticsearch: [https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-delete.html Delete API | Elasticsearch Guide [8.15] | Elastic]<ref>[https://stackoverflow.com/questions/31929695/truncate-index-in-elasticsearch python - Truncate index in elasticsearch - Stack Overflow]</ref>

<pre>
DELETE /MY_INXEX/
</pre>

MySQL [https://dev.mysql.com/doc/refman/8.4/en/truncate-table.html MySQL :: MySQL 8.4 Reference Manual :: 15.1.37 TRUNCATE TABLE Statement]
<pre>
TRUNCATE MY_TABLE
</pre>

== Create the index or table ==
Elasticsearch<ref>[https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-create-index.html#mappings Create index API | Elasticsearch Guide [8.15] | Elastic]</ref><ref>[https://opster.com/guides/elasticsearch/search-apis/elasticsearch-strings-keyword-vs-text-vs-wildcard/ Elasticsearch Keyword VS. Text - Strings Types Explained]</ref><ref>[https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-types.html Field data types | Elasticsearch Guide [8.15] | Elastic]</ref>
<pre>
PUT /MY_INXEX
{
  "mappings": {
    "properties": {
     "id": { "type": "keyword" },
      "field1": { "type": "text" }
    }
  }
}
</pre>

MySQL<ref>[https://dev.mysql.com/doc/refman/8.4/en/create-table.html MySQL :: MySQL 8.4 Reference Manual :: 15.1.20 CREATE TABLE Statement]</ref>

<pre>
CREATE TABLE MY_TABLE (
  id INT AUTO_INCREMENT PRIMARY KEY,
  field1 TEXT
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
</pre>


== Select all data from index or table ==
Elasticsearch<ref>[https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-get-index.html Get index API | Elasticsearch Guide [8.15] | Elastic]</ref>

<pre>
GET /MY_INDEX/_search/?size=1000&pretty=true
</pre>


MySQL<ref>[https://dev.mysql.com/doc/refman/8.4/en/selecting-all.html MySQL :: MySQL 8.4 Reference Manual :: 5.3.4.1 Selecting All Data]</ref>

<pre>
SELECT * 
FROM MY_TABLE
LIMIT 1000
</pre>

or

<pre>
SELECT * 
FROM MY_TABLE
</pre>

== Select particular data from index or table (exact match) ==
Elasticsearch<ref>[https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-get-index.html Get index API | Elasticsearch Guide [8.15] | Elastic]</ref>
<pre>
GET /MY_INDEX/_search/?q=_id:1
</pre>

MySQL<ref>[https://dev.mysql.com/doc/refman/8.4/en/selecting-rows.html MySQL :: MySQL 8.4 Reference Manual :: 5.3.4.2 Selecting Particular Rows]</ref>

<pre>
SELECT * 
FROM MY_TABLE
WHERE id = 1
</pre>


== Select particular data from index or table (partial match) ==
Elasticsearch<ref>[https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html Boolean query | Elasticsearch Guide [8.15] | Elastic]</ref>


<pre>
GET /MY_INDEX/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "match": {
            "field1": "NVIDIA"
          }
        }
      ]
    }
  }
}
</pre>

MySQL

<pre>
SELECT * 
FROM MY_TABLE
WHERE field1 LIKE "%NVIDIA%"
</pre>

== References ==
<references />

[[Category: MySQL]] [[Category: Search]]