Hỗ trợ câu lệnh SQL trong Elasticsearch
Elasticsearch 6.3 bao gồm một số tính năng chính mới, bao gồm rollups và hỗ trợ Java 10, nhưng một trong những bổ sung hấp dẫn nhất trong phiên bản này là hỗ trợ truy vấn SQL.
Theo DB-Engines, Elasticsearch đứng thứ 8 về mức độ phổ biến, xếp sau Oracle, MySQL, PostgreSQL và các cơ sở dữ liệu SQL khác. Điều này giải thích tại sao khả năng thực thi các câu lệnh SQL trên dữ liệu được lập chỉ mục trong Elasticsearch đã nằm trong danh sách mong muốn của nhiều người dùng Elasticsearch trong một thời gian.
Ngôn ngữ truy vấn gốc để tìm kiếm trong Elasticsearch, Query DSL, là một công cụ mạnh mẽ, nhưng không phải ai cũng thấy nó dễ học. Hỗ trợ được thêm vào Elasticsearch 6.3 giúp những người thông thạo các câu lệnh SQL dễ dàng truy vấn dữ liệu theo phương pháp thân thiện với người dùng hơn và hưởng lợi từ hiệu suất mà Elasticsearch mang lại.
Dưới đây là đánh giá ngắn gọn về những gì được bao gồm trong hỗ trợ mới này và các khả năng mà nó bao gồm (vẫn ở chế độ thử nghiệm).
Một phần của X-Pack
Không cần phải cài đặt thêm một plugin hoặc cấu hình bất cứ thứ gì. Hỗ trợ SQL được tích hợp trong gói Elasticsearch mặc định và được cung cấp như một phần của các tính năng cơ bản khác của X-Pack theo giấy phép Elastic đặc biệt.
Thực thi các câu lệnh SQL
Trước khi bắt đầu kiểm tra các câu lệnh SQL của mình, điều quan trọng là bạn phải hiểu cách tổ chức dữ liệu trong Elasticsearch và các thuật ngữ khác nhau mà SQL và Elasticsearch sử dụng. Ví dụ: các column trong SQL là các field trong Elasticsearch. Row tương ứng với document. Kiểm tra các khái niệm khác nhau ở đây.
Hãy bắt đầu với việc thực thi một số câu lệnh SELECT đơn giản bằng REST API chấp nhận JSON:
GET _xpack/sql
{
"query": "DESCRIBE logstash*"
}Điều này sẽ cung cấp cho tôi bảng phân tích tất cả các cột (hoặc trường) trong index Logstash của tôi, trong trường hợp này là chứa log truy cập Apache.
Có một giới hạn mặc định là 100 cột cho truy vấn này, vì vậy việc thực thi câu lệnh này đối với một index lớn (index Metricbeat chẳng hạn) sẽ không thành công. Bạn có thể thay đổi điều này bằng cách cấu hình cài đặt index index.max_docvalue_fields_search.
Kết quả có thể được hiển thị bằng định dạng JSON bằng cách sử dụng một tham số được thêm vào trong truy vấn như sau:
GET _xpack/sql?format=json
{
"query": "DESCRIBE logstash*"
}
Tương tự như vậy, chúng ta có thể xem dữ liệu ở định dạng bảng bằng cách thay đổi truy vấn như sau:
GET _xpack/sql?format=txt
{
"query": "DESCRIBE logstash*"
}
GET _xpack/sql?format=json
{
"query": "SELECT avg(system.memory.free) FROM metricbeat*"
}Kết quả trả về dạng JSON:
{
"columns": [
{
"name": "AVG(system.memory.free)",
"type": "long"
}
],
"rows": [
[
70962166.58631256
]
]
}Bạn có thể cảm thấy thú vị với những truy vấn này, kết hợp các câu lệnh FROM và GROUP BY, ví dụ:
GET _xpack/sql?format=txt
{
"query": "SELECT avg(system.process.memory.size), system.process.name FROM metricbeat* system.process.name GROUP BY system.process.name",
"fetch_size":5
}Kết quả, lần này được hiển thị ở định dạng bảng:
AVG(system.process.memory.size)|system.process.name
-------------------------------+-------------------
8.06273024E8 |agent
9277440.0 |docker-container
8.11589632E8 |dockerd
6.990266368E9 |dotnet
3.3151664758153844E9 |java Nhiều phương thức truy vấn
Điều mà tôi nghĩ rằng người dùng sẽ thấy cực kỳ hữu ích là các cách khác nhau mà các câu lệnh SQL có thể được thực thi. Trong ví dụ trên, tôi đã sử dụng công cụ Console bên trong Kibana để thực thi REST API và tôi có thể dễ dàng thực hiện điều tương tự từ dòng lệnh:
curl -XGET "http://localhost:9200/_xpack/sql" -H 'Content-Type: application/json' -d'
> {
> "query": "SELECT count(message) FROM logstash*"
> }'
{"columns":[{"name":"COUNT(message)","type":"long"}],"rows":[[34560]]}REST API là một cách để thực hiện, nhưng cũng có một công cụ SQL CLI được cung cấp trong Elasticsearch có thể chạy như sau trong thư mục cài đặt Elasticsearch:
sudo ./bin/elasticsearch-sql-cliTuy nhiên, cũng có một trình điều khiển JDBC cho Elasticsearch có thể được cài đặt như một thành phần độc lập hoặc sử dụng Maven, vì vậy bạn có thể dễ dàng kết nối Elasticsearch với các ứng dụng Java của mình. Thông tin thêm về việc sử dụng trình điều khiển này có sẵn tại đây.
Kết hợp với Query DSL
Trong một số trường hợp mà cú pháp SQL đơn giản là không đủ, bạn có thể kết hợp Elasticsearch Query DSL trong các truy vấn SQL của mình bằng cách thêm các tham số bổ sung.
Ví dụ: sử dụng tham số bộ lọc để thu hẹp kết quả:
GET _xpack/sql?format=txt
{
"query": "SELECT avg(system.process.memory.size), system.process.name FROM metricbeat* system.process.name GROUP BY system.process.name",
"filter": {
"term": {
"system.process.name" : "java"
}
},
"fetch_size":5
}Kết quả:
AVG(system.process.memory.size)|system.process.name
-------------------------------+-------------------
3.315269631096404E9 |java API Translate
Một khả năng hữu ích khác được hỗ trợ trong hỗ trợ SQL là khả năng chuyển đổi câu lệnh thành Elasticsearch Query DSL:
GET _xpack/sql/translate
{
"query": "SELECT avg(system.process.memory.rss.bytes) FROM metricbeat* GROUP BY system.process.name"
}Kết quả:
{
"size": 0,
"_source": false,
"stored_fields": "_none_",
"aggregations": {
"groupby": {
"composite": {
"size": 1000,
"sources": [
{
"51711": {
"terms": {
"field": "system.process.name",
"order": "asc"
}
}
}
]
},
"aggregations": {
"51878": {
"avg": {
"field": "system.process.memory.rss.bytes"
}
}
}
}
}
}Phần kết luận
Việc thực thi một câu lệnh JOIN trong SQL vẫn không được hỗ trợ và có thể sẽ không bao giờ được hỗ trợ (do mô hình dữ liệu cơ bản trong Elasticsearch), nhưng hỗ trợ SQL trong Elasticsearch 6.3 là một bước tiến lớn trong việc làm cho Elasticsearch dễ tiếp cận hơn với người mới bắt đầu và người dùng đến từ thế giới của cơ sở dữ liệu quan hệ.
Như đã đề cập ở trên, có một số câu lệnh SQL được hỗ trợ tại thời điểm này trong Elasticsearch và có một số hạn chế để kết hợp một số câu lệnh SQL với nhau. Nhiều tùy chọn thực thi, cũng như các câu lệnh được hỗ trợ, tạo nên một trường hợp mạnh mẽ để kiểm tra tính năng này.