Search and Analyze data in Amazon OpenSearch using SQL

What is Amazon OpenSearch?

Note: This tutorial uses a domain with open access. For the highest level of security, we recommend that you put your domain inside a virtual private cloud (VPC).

Step 1 - Create an Amazon OpenSearch Service domain

To experience the capability of using SQL with OpenSearch, we will setup an OpenSearch service domain which is synonymous with an OpenSearch cluster.

1. Go to https://aws.amazon.com and choose Sign In to the Console.
2. Under Analytics, choose Amazon OpenSearch Service.
3. Under the "Get Started" choice dialog, select "Managed Clusters" then click on Create domain.
4. Provide a name for the domain. The examples in this tutorial use the name movies.
5. For the domain creation method, choose Standard create. (Note: To quickly configure a production domain with best practices, you can choose Easy create. For the development and testing purposes of this tutorial, we'll use Standard create.)
6. For templates, choose Dev/test.
7. For the deployment option, choose Domain with standby.
8. For Version, choose the latest version.
9. For now, ignore the Data nodes, Warm and cold data storage, Dedicated master nodes, Snapshot configuration, and Custom endpoint sections.
10. For simplicity in this tutorial, use a public access domain. Under Network, choose Public access.
11. In the fine-grained access control settings, keep the Enable fine-grained access control check box selected. Select Create master user and provide a username and password.
12. For now, ignore the SAML authentication and Amazon Cognito authentication sections.
13. For Access policy, choose Only use fine-grained access control. In this tutorial, fine-grained access control handles authentication, not the domain access policy.
14. Ignore the rest of the settings and choose Create. New domains typically take 15–30 minutes to initialize, but can take longer depending on the configuration. After your domain initializes, select it to open its configuration pane. Note the domain endpoint under General information (for example, https://search-my-domain.us-east-1.es.amazonaws.com), which you'll use in the next step.

Step 2 - Ingest Sample data into your OpenSearch domain

This step covers the ingestion of sample data into OpenSearch so that you can test sample SQL queries on that data.

1. Navigate to your OpenSearch service on the AWS console. On the Dashboard section, your domain should be listed under the header “Name”. Click on any of your domains.

   

2. Click on the OpenSearch Dashboard URL link for this domain.

   

3. Login into OpenSearch Dashboards with the username and password you created in Step 1.11

   

4. After login is successful, you will see the screens below in sequence - click “Add data”, Select “Global” tenant and click on the “Add data” button under “Sample eCommerce orders”. That will ingest the sample data comprising of e-commerce orders, into this OpenSearch domain, which you will query using SQL for the rest of this tutorial.

Note: If the Sample eCommerce Orders was already ingested you will see “View data” instead, you can skip the step above in that case.**

1. Click the Hamburger Icon (the icon top lef under "Open Search Dashboards" - it looks like 3 horizontal lines above each other) to expand the menu, and then click on “Query Workbench”. This will open up the “Query Editor” where you can enter your SQL queries, with the results showing in the “Output” pane below that.

Step 3 - Running Basic SQL Queries

We are starting with running a basic set of SQL queries to help understand the key concepts.

1. To list all your indexes in your current domain, run the SQL query below in the Query Workbench:

1. Retrieve a limited set of documents (5) from the sample e-commerce table (sample results follow the SQL query below)

Note: the (+) sign next to “order” indicates that there are nested JSON documents which we can see when we expand it. Later we will see how we can query nested JSON document fields using SQL.

1. We want retrieve a specific order, so we need to get the datatypes of all the columns in this table, using the SQL query below, and scrolling through the results to find the datatype of the column order_id.

1. Since the datatype of the column order_id is keyword, we need to enclose the search in quotes (updated).

1. Use the SQL SELECT clause, along with FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT to search and aggregate data. Among these clauses, SELECT and FROM are required, as they specify which fields to retrieve and which indexes to retrieve them from. All other clauses are optional.

1. Use the DISTINCT clause to get back only unique field values. You can specify one or more field names:

1. To use an aggregate function like SUM - enclose a single field name/column or expression as a parameter. If you specify a list of one or more fields before the aggregate function, you must specify the same list as part of the GROUP BY clause otherwise the aggregate function will calculate the expression over all the documents in the index. Run the query below to calculate the sum of total_quantity by manufacturer.

1. You can use an expression in the GROUP BY clause. For example, the following query returns the average sales or total_price and count of records for each year and month:

1. Use aggregate expressions as part of larger expressions in SELECT. The following query calculates the average commission for each manufacturer as 5% of the average sales:

1. Both WHERE and HAVING are used to filter results. The WHERE filter is applied before the GROUP BY phase, so you cannot use aggregate functions in a WHERE clause. However, you can use the WHERE clause to limit the rows to which the aggregate is then applied. Below is a list of operators you can use with the WHERE clause to specify a condition to filter the results.

1. The HAVING filter is applied after the GROUP BY phase, so you can use the HAVING clause to limit the groups that are included in the results.

1. Use an alias for an aggregate expression in the HAVING clause. The following query uses an alias to return the total quantity by manufacturer where the sum exceeds 2,000.

1. The DELETE statement deletes documents that satisfy the predicates in the WHERE clause. If you don’t specify the WHERE clause, all documents are deleted. It is disabled by default. To enable the DELETE functionality in SQL, you need to update the configuration by sending the following request using the console in Dev Tools.

Step 4 - Running Complex SQL queries on multiple indexes or tables

Now that we have covered the basics of SQL querying, in this section we run more complex SQL queries to understand how they work.

1. Using Inner join

1. Use Cross join, also known as cartesian join, to combine each document from the first index with each document from the second. The result set is the the cartesian product of documents of both indexes. This operation is similar to the inner join without the ON clause that specifies the join condition.

Note: It’s risky to perform a cross join on two indexes of large or even medium size. It might trigger a circuit breaker that terminates the query to avoid running out of memory.

1. Use left outer join to retain rows from the first index if it does not satisfy the join predicate. The keyword OUTER is optional.

1. Use subquery which is a complete SELECT statement used within another statement and enclosed in parenthesis:

Step 5 - Using SQL Functions

In this step we cover the rich set of SQL Functions which can help the application builder leverage more search and analytics capabilities of OpenSearch.

1. Use the MATCH function in SQL for full-text search (a subset of full-text queries available in OpenSearch is supported). You can search documents that match a string, number, date, or boolean value for a given field. The MATCHQUERY and MATCH_QUERY functions are synonyms for the MATCH relevance function.
   Syntax:

• analyzer
• boost

1. To search for text in multiple fields, use MULTI_MATCH function. This function maps to the multi_match query used in search engine, to returns the documents that match a provided text, number, date or boolean value with a given field or fields. eg. To search for Dale in either the firstname or lastname fields could be called from SQL using multi_match function. The MATCHQUERY and MATCH_QUERY functions are synonyms for the MATCH relevance function.

1. To return a relevance score along with every matching document, use the SCORE, SCOREQUERY, or SCORE_QUERY functions.

A note about Query DSL: OpenSearch provides a search language called query domain-specific language (DSL) that you can use to search your data. Query DSL is a flexible language with a JSON interface. With query DSL, you need to specify a query in the query parameter of the search. One of the simplest searches in OpenSearch uses the match_all query, which matches all documents in an index:

• Full-text queries: Use full-text queries to search text documents. For an analyzed text field search, full-text queries split the query string into terms with the same analyzer that was used when the field was indexed. For an exact value search, full-text queries look for the specified value without applying text analysis. To learn more, see Full-text queries.
• Term-level queries: Use term-level queries to search documents for an exact specified term, such as an ID or value range. Term-level queries do not analyze search terms or sort results by relevance score. To learn more, see Term-level queries.
• Geographic and xy queries: Use geographic queries to search documents that include geographic data. Use xy queries to search documents that include points and shapes in a two-dimensional coordinate system. To learn more, see Geographic and xy queries.
• Joining queries: Use joining queries to search nested fields or return parent and child documents that match a specific query. Types of joining queries include nested, has_child, has_parent, and parent_id queries.
• Span queries: Use span queries to perform precise positional searches. Span queries are low-level, specific queries that provide control over the order and proximity of specified query terms. They are primarily used to search legal documents. To learn more, see Span queries.
• Specialized queries: Specialized queries include all other query types (distance_feature, more_like_this, percolate, rank_feature, script, script_score, wrapper, and pinned_query).

Conclusion

Next Steps

Comment: You can further explore the latest version of OpenSearch by reviewing the documentation on this site OpenSearch documentation. To build an application using the OpenSearch SQL API, review this link OpenSearch SQL API and refer to the different SQL functions and operators which you can use depending on your application requirements.

1. Tarball
2. RPM
3. Debian
4. Windows
5. Docker
6. Helm
7. Ansible playbooks.