Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Situation Awareness
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Using SQL to Search for Data in OpenSearch

Updated on 2024-10-12 GMT+08:00

For OpenSearch clusters, Open Distro for Elasticsearch SQL lets you write queries in SQL rather than the Elasticsearch query domain-specific language (DSL).

Users who are familiar with SQL can use SQL to search for data in OpenSearch clusters.

SQL Usage Example

  • Use SQL to search for data on Kibana (recommended).

    Log in to Kibana and, on the Dev Tools page, send requests using request parameters or the request body to _plugins/_sql.

    For example, run the following command to retrieve 50 records from the my-index index:
    1
    2
    3
    4
    POST _plugins/_sql
    {
      "query": "SELECT * FROM my-index LIMIT 50"
    }
    
    By default, the query result is returned in JSON format. To return data in CSV format, set the format parameter in the command as follows:
    1
    2
    3
    4
    POST _plugins/_sql?format=csv
    {
      "query": "SELECT * FROM my-index LIMIT 50"
    }
    

    When query result is returned in CSV format, each row corresponds to a document and each column corresponds to a field.

  • Run cURL commands on an ECS to execute SQL statements to search for data.

    For example, run the following command to retrieve 10 records from the kibana_sample_data_flights index.

    curl -XPOST https://localhost:9200/_opendistro/_sql -u username:password -k -d '{"query": "SELECT * FROM kibana_sample_data_flights LIMIT 10"}' -H 'Content-Type: application/json'

    localhost indicates the cluster access address. username and password indicate the username and password for accessing the security-mode cluster, respectively.

Supported SQL Operations

Open Distro for Elasticsearch supports the following SQL operations: statements, conditions, aggregations, include and exclude fields, common functions, joins, and show.

  • Statements
    Table 1 Statements

    Statement

    Example

    Select

    SELECT * FROM my-index

    Delete

    DELETE FROM my-index WHERE _id=1

    Where

    SELECT * FROM my-index WHERE ['field']='value'

    Order by

    SELECT * FROM my-index ORDER BY _id asc

    Group by

    SELECT * FROM my-index GROUP BY range(age, 20,30,39)

    Limit

    SELECT * FROM my-index LIMIT 50 (default is 200)

    Union

    SELECT * FROM my-index1 UNION SELECT * FROM my-index2

    Minus

    SELECT * FROM my-index1 MINUS SELECT * FROM my-index2

    NOTE:

    As with any complex query, large UNION and MINUS statements can strain or even crash your cluster.

  • Conditions
    Table 2 Conditions

    Condition

    Example

    Like

    SELECT * FROM my-index WHERE name LIKE 'j%'

    And

    SELECT * FROM my-index WHERE name LIKE 'j%' AND age > 21

    Or

    SELECT * FROM my-index WHERE name LIKE 'j%' OR age > 21

    Count distinct

    SELECT count(distinct age) FROM my-index

    In

    SELECT * FROM my-index WHERE name IN ('alejandro', 'carolina')

    Not

    SELECT * FROM my-index WHERE name NOT IN ('jane')

    Between

    SELECT * FROM my-index WHERE age BETWEEN 20 AND 30

    Aliases

    SELECT avg(age) AS Average_Age FROM my-index

    Date

    SELECT * FROM my-index WHERE birthday='1990-11-15'

    Null

    SELECT * FROM my-index WHERE name IS NULL

  • Aggregations
    Table 3 Aggregations

    Aggregation

    Example

    avg()

    SELECT avg(age) FROM my-index

    count()

    SELECT count(age) FROM my-index

    max()

    SELECT max(age) AS Highest_Age FROM my-index

    min()

    SELECT min(age) AS Lowest_Age FROM my-index

    sum()

    SELECT sum(age) AS Age_Sum FROM my-index

  • Include and exclude fields
    Table 4 Include and exclude fields

    Pattern

    Example

    include()

    SELECT include('a*'), exclude('age') FROM my-index

    exclude()

    SELECT exclude('*name') FROM my-index

  • Functions
    Table 5 Functions

    Function

    Example

    floor

    SELECT floor(number) AS Rounded_Down FROM my-index

    trim

    SELECT trim(name) FROM my-index

    log

    SELECT log(number) FROM my-index

    log10

    SELECT log10(number) FROM my-index

    substring

    SELECT substring(name, 2,5) FROM my-index

    round

    SELECT round(number) FROM my-index

    sqrt

    SELECT sqrt(number) FROM my-index

    concat_ws

    SELECT concat_ws(' ', age, height) AS combined FROM my-index

    /

    SELECT number / 100 FROM my-index

    %

    SELECT number % 100 FROM my-index

    date_format

    SELECT date_format(date, 'Y') FROM my-index

    NOTE:

    You must enable fielddata in the document mapping for most string functions to work properly.

  • Joins
    Table 6 Joins

    Join

    Example

    Inner join

    SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s JOIN school sc ON sc.name = s.school_name WHERE s.age > 20

    Left outer join

    SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s LEFT JOIN school sc ON sc.name = s.school_name

    Cross join

    SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s CROSS JOIN school sc

    For details about the constraints, see Joins.

  • Show

    Show commands display indexes and mappings that match an index pattern. You can use * or % as a wildcard.

    Table 7 Show

    Show

    Example

    Show tables like

    SHOW TABLES LIKE logs-*

Joins

Open Distro for Elasticsearch SQL supports inner joins, left outer joins and cross joins. Joins have the following constraints:

  • You can only join two indexes.
  • You must use an alias for an index (for example, people p).
  • In an ON clause, you can only use the AND conditions.
  • In a WHERE statement, do not combine trees that contain multiple indexes. For example, the following statement will work:
    WHERE (a.type1 > 3 OR a.type1 < 0) AND (b.type2 > 4 OR b.type2 < -1)
    The following statement will not work:
    WHERE (a.type1 > 3 OR b.type2 < 0) AND (a.type1 > 4 OR b.type2 < -1)
  • You cannot use GROUP BY or ORDER BY to obtain results.
  • LIMIT with OFFSET (for example, LIMIT 25 OFFSET 25) is not supported.

JDBC Driver

The Java Database Connectivity (JDBC) driver allows you to integrate Open Distro for Elasticsearch with your business intelligence (BI) applications.

For details about how to download and use JAR files, see GitHub Repositories.

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback