ApsaraDB for SelectDB:Fonte de dados 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;

Consultas básicas

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

Esquery estendida

Use esquery(field, QueryDSL) para enviar (push down) ao Elasticsearch consultas que não podem ser expressas em SQL — como match_phrase e geo_shape. O parâmetro field associa a consulta a um índice. O parâmetro QueryDSL é um objeto JSON com exatamente uma chave raiz.

Consulta match_phrase:

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

Consulta geo_shape:

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

Consulta bool:

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

Tipo ARRAY

O Elasticsearch não possui um tipo ARRAY explícito, mas um campo pode conter zero ou mais valores. Para declarar um campo como array no SelectDB, adicione uma entrada array_fields sob _meta.selectdb no mapeamento do índice.

Exemplo de estrutura de dados para o índice 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}]
}

Atualize o mapeamento para declarar os campos de array:

# 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"
                ]
            }
        }
    }
}'

Pushdown de condições de filtro

O SelectDB envia as condições de filtro para o Elasticsearch (pushdown) para retornar apenas os dados correspondentes, reduzindo a carga de CPU, memória e I/O em ambos os sistemas. A tabela a seguir mostra como os operadores SQL são mapeados para o Elasticsearch Query DSL.

Habilitar varredura colunar para acelerar consultas

Defina enable_docvalue_scan como true para ler valores de campos do armazenamento orientado a colunas (doc_values) em vez do campo _source. Quando apenas algumas colunas são consultadas, a varredura colunar pode ser mais de dez vezes mais rápida do que a leitura a partir de _source.

O SelectDB aplica dois princípios quando a varredura colunar está habilitada:

• Melhor esforço: Se todos os campos consultados tiverem doc_value habilitado, o SelectDB lê inteiramente do armazenamento orientado a colunas.

• Downgrade automático: Se qualquer campo consultado não possuir doc_value, o SelectDB volta a ler de _source para todos os campos.

Detectar campos KEYWORD

Configure enable_keyword_sniff como true para que o SelectDB utilize automaticamente subcampos KEYWORD em consultas de igualdade sobre campos STRING.

Quando o Elasticsearch cria um índice automaticamente, campos STRING recebem tanto um campo TEXT quanto um subcampo KEYWORD:

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

Sem a detecção de keyword (sniffing), a condição SQL k4 = "SelectDB On ES" gera este Query DSL:

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

Como k4 é um campo TEXT, seu valor é tokenizado em selectdb, on e es — nenhum deles corresponde à frase completa, portanto, nenhum resultado é retornado.

Com enable_keyword_sniff definido como true, o SelectDB reescreve automaticamente a condição para direcionar a busca ao subcampo KEYWORD:

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

O campo KEYWORD armazena o valor original sem modificações, permitindo que a frase exata seja encontrada corretamente.

Habilitar descoberta de nós

Defina nodes_discovery como true para permitir que o SelectDB descubra todos os nós de dados do Elasticsearch com shards alocados.

O Alibaba Cloud Elasticsearch roteia o tráfego por uma instância SLB, o que impede o acesso direto a nós individuais do cluster. Sempre defina nodes_discovery como false ao se conectar ao Alibaba Cloud Elasticsearch.

Habilitar HTTPS

Configure ssl como true para se conectar ao cluster Elasticsearch via HTTPS. O SelectDB confia em todas as solicitações HTTPS provenientes dos nós FE e BE, independentemente da validade do certificado SSL.

Lidar com campos de tempo

As orientações nesta seção aplicam-se apenas a tabelas externas do Elasticsearch. Para catálogos Elasticsearch, os campos de tempo são mapeados automaticamente para o tipo DATE ou DATETIME.

Configure o formato do campo de data no Elasticsearch para suportar uma ampla variedade de entradas:

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

No SelectDB, defina o campo como date, datetime ou varchar. Todas as condições de filtro a seguir funcionam corretamente com pushdown:

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');

Consultar o campo _id

O Elasticsearch atribui automaticamente um _id globalmente único a cada documento quando nenhum é especificado no momento da importação. Para consultar _id de uma tabela externa, declare-o como uma coluna 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"
);

Para consultar _id de um catálogo Elasticsearch, defina mapping_es_id como true.

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