[RFC] OpenSearch and Apache Spark Integration

[penghuo]

Introduction

We received a feature request for query execution on object stores in OpenSearch.

• [FEATURE] Materialized views (aka virtual indexes) on object stores #1080

We have investigated the possibility to build a new solution for OpenSearch uses and leverage object store as storage. Which includes

• [FEATURE] Object Storage (S3) Data Ingestion through Streaming Query #948
• [RFC] Query S3 in OpenSearch Observability #719
• [RFC] Automatic Workload-Driven Query Acceleration by OpenSearch opensearch-spark#128

We found the challenges are

• OpenSearch aggregation framework is the simplified MPP frameworks and does not support shuffle stage.
• OpenSearch query framework missing key feature support, E.g. JOIN, Subquery.

We found these work have been solved by general purpose data preprocessing system, E.g. Presto, Spark, Trino. And build such a platform require years to mature.

Idea

The initial idea is

1. Using SQL as interface.
2. Leverage spark as query/compute execution engine.

High level diagram:

User Experience

1. User configure SPARK cluster as computation resource, E.g. https://SPARK:7707.
2. User submit SQL to OpenSearch cluster use _plugins/_sql REST API.
   1. SQL engine parse and analysis the SQL query.
   2. SQL engine decide whether route the query to SPARK cluster or run query locally.
3. In phase-1, we provide interface to let user create derived dataset from data on object store and store in OpenSearch. Then query will be optimized based derived dataset automatically during query time.
4. In phase-2, we provide opt-in optimization choice for user. The derived dataset will be create automatically based on query pattern.