ApsaraDB for SelectDB:Source de données Elasticsearch

CREATE CATALOG test_es PROPERTIES (
    "type"="es",
    "hosts"="http://127.0.0.1:9200",
    "user"="test_user",
    "password"="test_passwd",
    "nodes_discovery"="false"
);

-- Switch to the catalog, then query
SWITCH test_es;
SELECT * FROM es_table LIMIT 10;

-- Use the fully qualified database path
USE test_es.default_db;
SELECT * FROM es_table LIMIT 10;

-- Use the fully qualified table name directly
SELECT * FROM test_es.default_db.es_table LIMIT 10;

Requêtes de base

SELECT * FROM es_table WHERE k1 > 1000 AND k3 = 'term' OR k4 LIKE 'fu*z_';

Requête étendue esquery

Utilisez esquery(field, QueryDSL) pour déléguer à Elasticsearch des requêtes inexprimables en SQL, telles que match_phrase et geo_shape. Le paramètre field associe la requête à un index. Le paramètre QueryDSL est un objet JSON comportant exactement une clé racine.

Requête match_phrase :

SELECT * FROM es_table WHERE esquery(k4, '{"match_phrase": {"k4": "selectdb on es"}}');

Requête geo_shape :

SELECT * FROM es_table WHERE esquery(k4, '{"geo_shape": {"location": {"shape": {"type": "envelope", "coordinates": [[13, 53], [14, 52]]}, "relation": "within"}}}');

Requête bool :

SELECT * FROM es_table WHERE esquery(k4, '{"bool": {"must": [{"terms": {"k1": [11, 12]}}, {"terms": {"k2": [100]}}]}}');

Type ARRAY

Bien qu'Elasticsearch ne possède pas de type ARRAY explicite, un champ peut contenir zéro ou plusieurs valeurs. Pour déclarer un champ comme tableau dans SelectDB, ajoutez une entrée array_fields sous _meta.selectdb dans le mapping de l'index.

Exemple de structure de données pour l'index doc :

{
  "array_int_field": [1, 2, 3, 4],
  "array_string_field": ["selectdb", "is", "the", "best"],
  "id_field": "id-xxx-xxx",
  "timestamp_field": "2022-11-12T12:08:56Z",
  "array_object_field": [{"name": "xxx", "age": 18}]
}

Mettez à jour le mapping pour déclarer les champs de type tableau :

# Elasticsearch 7.x and later
curl -X PUT "localhost:9200/doc/_mapping?pretty" -H 'Content-Type: application/json' -d '
{
    "_meta": {
        "selectdb": {
            "array_fields": [
                "array_int_field",
                "array_string_field",
                "array_object_field"
            ]
        }
    }
}'

# Elasticsearch 6.x and earlier
curl -X PUT "localhost:9200/doc/_mapping?pretty" -H 'Content-Type: application/json' -d '
{
    "_doc": {
        "_meta": {
            "selectdb": {
                "array_fields": [
                    "array_int_field",
                    "array_string_field",
                    "array_object_field"
                ]
            }
        }
    }
}'

Délégation des conditions de filtrage

SelectDB délègue les conditions de filtrage à Elasticsearch afin que seules les données correspondantes soient retournées, réduisant ainsi la charge CPU, mémoire et E/S sur les deux systèmes. Le tableau suivant illustre la correspondance entre les opérateurs SQL et le Query DSL d'Elasticsearch.

Activer le scan colonnaire pour accélérer les requêtes

Définissez enable_docvalue_scan sur true pour lire les valeurs des champs depuis le stockage orienté colonne (doc_values) plutôt que depuis le champ _source. Lorsque seules quelques colonnes sont interrogées, le scan colonnaire s'avère plus de dix fois plus rapide que la lecture depuis _source.

Lorsque le scan colonnaire est activé, SelectDB applique deux principes :

• Au mieux : Si tous les champs interrogés ont doc_value activé, SelectDB lit intégralement les données depuis le stockage orienté colonne.

• Rétrogradation automatique : Si l'un des champs interrogés ne dispose pas de doc_value, SelectDB revient à la lecture depuis _source pour l'ensemble des champs.

Détecter les champs KEYWORD

Définissez enable_keyword_sniff sur true pour que SelectDB utilise automatiquement les sous-champs KEYWORD lors des requêtes d'égalité sur des champs STRING.

Lorsqu'Elasticsearch crée automatiquement un index, les champs STRING reçoivent à la fois un champ TEXT et un sous-champ KEYWORD :

"k4": {
    "type": "text",
    "fields": {
        "keyword": {
            "type": "keyword",
            "ignore_above": 256
        }
    }
}

Sans la détection de mots-clés, la condition SQL k4 = "SelectDB On ES" génère le Query DSL suivant :

"term": {"k4": "SelectDB On ES"}

Puisque k4 est un champ TEXT, sa valeur est tokenisée en selectdb, on et es. Aucun de ces termes ne correspondant à l'expression complète, aucun résultat n'est retourné.

Avec enable_keyword_sniff défini sur true, SelectDB réécrit automatiquement la condition pour cibler le sous-champ KEYWORD :

"term": {"k4.keyword": "SelectDB On ES"}

Le champ KEYWORD stockant la valeur originale non modifiée, l'expression exacte correspond correctement.

Activer la découverte de nœuds

Définissez nodes_discovery sur true pour permettre à SelectDB de découvrir tous les nœuds de données Elasticsearch possédant des shards alloués.

Alibaba Cloud Elasticsearch achemine le trafic via une instance SLB, ce qui empêche l'accès direct aux nœuds individuels du cluster. Définissez toujours nodes_discovery sur false lors de la connexion à Alibaba Cloud Elasticsearch.

Activer HTTPS

Définissez ssl sur true pour vous connecter au cluster Elasticsearch via HTTPS. SelectDB approuve toutes les requêtes HTTPS provenant des nœuds FE et BE, indépendamment de la validité du certificat SSL.

Gérer les champs temporels

Les instructions de cette section s'appliquent uniquement aux tables Elasticsearch externes. Pour les catalogs Elasticsearch, les champs temporels sont automatiquement mappés vers le type DATE ou DATETIME.

Configurez le format du champ de date dans Elasticsearch pour prendre en charge un large éventail d'entrées :

"dt": {
    "type": "date",
    "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
}

Dans SelectDB, définissez le champ comme date, datetime ou varchar. Toutes les conditions de filtrage suivantes sont correctement déléguées :

SELECT * FROM doe WHERE k2 > '2020-06-21';
SELECT * FROM doe WHERE k2 < '2020-06-21 12:00:00';
SELECT * FROM doe WHERE k2 < 1593497011;
SELECT * FROM doe WHERE k2 < now();
SELECT * FROM doe WHERE k2 < date_format(now(), '%Y-%m-%d');

Interroger le champ _id

Elasticsearch attribue automatiquement un identifiant _id globalement unique à chaque document lorsqu'aucun n'est spécifié lors de l'importation. Pour interroger _id depuis une table externe, déclarez-le comme une colonne VARCHAR :

CREATE EXTERNAL TABLE `doe` (
    `_id`  varchar COMMENT "",
    `city` varchar COMMENT ""
) ENGINE=ELASTICSEARCH
PROPERTIES (
    "hosts"    = "http://127.0.0.1:8200",
    "user"     = "root",
    "password" = "root",
    "index"    = "doe"
);

Pour interroger _id depuis un catalog Elasticsearch, définissez mapping_es_id sur true.

+----------------------------------------------+
|                                              |
| SelectDB   +------------------+              |
|            |       FE         +--------------+-------+
|            |                  |  Request Shard Location
|            +--+-------------+-+              |       |
|               ^             ^                |       |
|               |             |                |       |
|  +-------------------+ +------------------+  |       |
|  |            |      | |    |             |  |       |
|  | +----------+----+ | | +--+-----------+ |  |       |
|  | |      BE       | | | |      BE      | |  |       |
|  | +---------------+ | | +--------------+ |  |       |
+----------------------------------------------+       |
   |        |          | |        |         |          |
   |        |          | |        |         |          |
   |    HTTP SCROLL    | |    HTTP SCROLL   |          |
+-----------+---------------------+------------+       |
|  |        v          | |        v         |  |       |
|  | +------+--------+ | | +------+-------+ |  |       |
|  | |               | | | |              | |  |       |
|  | |   DataNode    | | | |   DataNode   +<-----------+
|  | |               | | | |              | |  |       |
|  | |               +<--------------------------------+
|  | +---------------+ | | |--------------| |  |       |
|  +-------------------+ +------------------+  |       |
|   Same Physical Node                         |       |
|                                              |       |
|           +-----------------------+          |       |
|           |                       |          |       |
|           |      MasterNode       +<-----------------+
| ES        |                       |          |
|           +-----------------------+          |
+----------------------------------------------+