Movatterモバイル変換


[0]ホーム

URL:


Loading

Index patterns

Elasticsearch SQL supports two types of patterns for matching multiple indices or tables:

The Elasticsearch notation for enumerating, including or excludingmulti-target syntax is supportedas long as it is quoted or escaped as a table identifier.

For example:

SHOW TABLES "*,-l*"; catalog       |     name      | type     |     kind---------------+---------------+----------+---------------javaRestTest      |emp            |TABLE     |INDEXjavaRestTest      |employees      |VIEW      |ALIAS

Notice the pattern is surrounded by double quotes". It enumerated* meaning all indices however it excludes (due to-) all indices that start withl. This notation is very convenient and powerful as it allows both inclusion and exclusion, depending on the target naming convention.

The same kind of patterns can also be used to query multiple indices or tables.

For example:

SELECT emp_no FROM "e*p" LIMIT 1;    emp_no---------------10001
Note

There is the restriction that all resolved concrete tables have the exact same mapping.

[preview] To run across-cluster search, specify a cluster name using the<remote_cluster>:<target> syntax, where<remote_cluster> maps to a SQL catalog (cluster) and<target> to a table (index or data stream). The<remote_cluster> supports wildcards (*) and<target> can be an index pattern.

For example:

SELECT emp_no FROM "my*cluster:*emp" LIMIT 1;    emp_no---------------10001

The commonLIKE statement (including escaping if needed) to match a wildcard pattern, based on one_ or multiple% characters.

UsingSHOW TABLES command again:

SHOW TABLES LIKE 'emp%'; catalog       |     name      | type     |     kind---------------+---------------+----------+---------------javaRestTest      |emp            |TABLE     |INDEXjavaRestTest      |employees      |VIEW      |ALIAS

The pattern matches all tables that start withemp.

This command supportsescaping as well, for example:

SHOW TABLES LIKE 'emp!%' ESCAPE '!'; catalog       |     name      |     type      |     kind---------------+---------------+---------------+---------------

Notice how nowemp% does not match any tables because%, which means match zero or more characters, has been escaped by! and thus becomes an regular char. And since there is no table namedemp%, an empty table is returned.

In a nutshell, the differences between the two type of patterns are:

FeatureMulti indexSQLLIKE
Type of quoting"'
InclusionYesYes
ExclusionYesNo
EnumerationYesNo
One char patternNo_
Multi char pattern*%
EscapingNoESCAPE

Which one to use, is up to you however try to stick to the same one across your queries for consistency.

Note

As the query type of quoting between the two patterns is fairly similar (" vs'), Elasticsearch SQLalways requires the keywordLIKE for SQLLIKE pattern.

Welcome to the docs for thelatest Elastic product versions, including Elastic Stack 9.0 and Elastic Cloud Serverless.To view previous versions, go toelastic.co/guide.


[8]ページ先頭

©2009-2025 Movatter.jp