Querying data in OpenSearch Dashboards - Amazon OpenSearch Service
SQLPPLRecommendations
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Querying data in OpenSearch Dashboards

After you set up your tables and configure your desired optional query acceleration, you can now start performing analytics on your data. To query your data, select the data source from the dropdown menu on the Discover page or Observability page in OpenSearch Dashboards.

If you're using a skipping index or haven't created an index, you can use SQL or Piped Processing Language (PPL) to query your data. If you've configured a materialized view or a covering index, you already have an index and can use Dashboards Query Language (DQL) throughout Dashboards. You can also use PPL with the Observability plugin, and SQL with the Query Workbench plugin. Currently, only the Observability and Query Workbench plugins support PPL and SQL. For querying data using the OpenSearch Service API, refer to the refer to the async API documentation.

SQL

Use the following query to run a sample SQL query for the VPC flow logs table that you created in Create Spark Tables using Query Workbench:

SELECT srcaddr, SUM(CAST(bytes AS LONG)) as total_bytes FROM datasourcename.gluedatabasename.vpclogstable GROUP BY srcaddrORDER BY total_bytes DESCLIMIT 10;

PPL

Use the following queries to run sample PPL queries for the VPC logs table that you created in Create Spark Tables using Query Workbench:

source = datasourcename.gluedatabasename.gluetablename | 
where status = 500 | sort - clientip, @timestamp | head 20
source = datasourcename.gluedatabasename.vpclogstable | fields account_id, srcaddr, dstaddr, action | head 10

Recommendations

There might be instances when results don’t return as expected. If you experience any issues, we recommend you taking the following actions:

  • SELECT * statements return no results - check your table to see if it has nested struc columns which need to be exploded.

  • When selecting multiple tables use the SQL UNION statement to reference multiple tables.

  • Accelerations are set to use a specific number of workers to execute a query. If queries are returning slowly, you can manually allocate more workers to perform queries for increased performance.

  • When building skipping indexes, to use bloom filters for high cardinality and min/max for large ranges to save space on the domain. It is recommended you set your value on a moderate cardinality field if you need to perform an exact match.

  • For more information on popular SQL queries, see Amazon Service Logs.