[Proposal] Multiple Query Languages Support

[kavilla]

Opening this proposal for extending features within the data plugin.

PR: [LINK]
Current feature branch for OSD: [LINK]
PoC Plugin: [LINK]

Glossary

• Data Connection
  • NOTE: Still being discussed on what is the data connection. For the purpose of this document, the data connection source is the cluster, or in SQL, the database.
• Data Source
  • NOTE: Still being discussed on what is the data source. For the purpose of this document, the data source is the index or indices of the data connection, or in SQL, the table of the data connection.
• Result/Data Set
  • The data collection of fields and values from querying the data source.
• Data Frame
  • A data frame is a collection of fields. Each field has a collection of values. Borrowing from industry standards, the data frame is a data structure that unifies the result sets in a single response.
• Index Pattern
  • An index pattern provides a way to interact with the fields and data of an index in a structured way. It can point to one or more indices. An index pattern can be thought of as the normalization of the result set. Which when created, will instruct the data frame how the users specifically want the result sets to be normalized when utilized by OpenSearch Dashboards. If no index pattern matches the data source, the data frame will use a generic normalization that comforms to what OpenSearch Dashboards needs.
• Data Frame Schema
  • In comparison to the fields of the data frame, the data frame schema is all the fields available from the data source. Where as the fields dimension of the data frame is the specific fields in the result set.
• Search Strategy
  • The search strategy can be thought of as the adapter to your data source. Here is where you will need to map the response back to the data frame (and the type of data frame).
• Search Interceptor
  • The search interceptor manages and control search requests from the application to the data source by executing the search strategy and responds to the application with the data source produced by the search strategy. It is typically used in conjunction with the search service to provide additional functionality such as caching, debouncing, or request cancellation.
• Data Plugin
  • The data plugin provides common data access services, such as search and query, for solutions and application developers. It sets the default search strategy and search interceptor. It is also responsible for rendering the top search bar, index pattern select, and time picker. Plugins are not required to take the data plugin as an dependency as they can new up a HTTP client and query whatever directly in their plugin. It expects OpenSearch DSL queries and OpenSearch responses for dashboards, visualizations, and Discover to function correctly.

Background

OpenSearch Dashboards and plugins lack a unified query experience. Here is a high-level overview of what exists today:

In the original architecture, as depicted in the diagram, a plugin was required to implement its own unique search strategies. For instance, if we wanted to support new query languages, they would need to be added to both the Data plugin and the Discover plugin. Furthermore, the system would execute these queries using either _search or _msearch for OpenSearch, thereby eliminating the flexibility to query different types of data sources.

To be more generic, to add a new query language, you have to add a search strategy and register it. But other plugins cannot not use it until the work is done to hook into it. Even with that, search strategies are built to handle DSL queries. To add a new a new language, you would also need to build the query into a DSL and expects documents for it to integrate into the system.

Problem

While our primary focus will be on designing around the requirements of the current state of the Discover plugin, the design will also lay the groundwork for broader applications. This includes extending its use to visualizations and implementing various search strategies for different data connections and sources.

The problem we're trying to solve is the inconsistency in capabilities across different query languages within OpenSearch Dashboards. This inconsistency, resulting from various code paths performing similar tasks like data table display or visualization interaction, leads to a potentially frustrating user experience. There is also an inconsistencies in the data connections, the data connection might not support specific query languages, or different ways handling requests. We are addressing this issue now because it's crucial to provide a unified and predictable user experience, regardless of the chosen query language or data connection::data source.

Furthermore, we aim to foster a more inclusive and extensible development environment. Currently, plugins can not augment the search functionalities within the Data plugin nor can they provide custom adapters for their specific data sources. This makes OpenSearch Dashboards coupled to OpenSearch queries and OpenSearch data sources. Without interoperability, data can become isolated in different databases, making it difficult to access and analyze collectively. Interoperability allows for seamless data integration across different NoSQL databases. Some NoSQL databases are not optimized for complex queries. Interoperability allows for the use of different databases for different tasks, enabling more complex data operations. Interoperability in NoSQL databases can help solve several problems. However, there's a need to clearly document the capabilities and limitations of these enhancements to help developers understand what they can and cannot do with each query language, setting appropriate expectations.

Tenets

• Abstract UI and Common Functionality: If supporting multiple query languages is a requirement, abstract service. This would reduce code duplication and ensure consistent behavior across different query languages.
• Enhancements for Search Capabilities: Plugins can now augment the search functionalities within the data plugin. They can provide custom adapters for their specific data sources. This enhancement allows developers to extend the search capabilities without any hindrance, even if their plugins are not treated as first-party. This fosters a more inclusive and extensible development environment, enabling a wider range of data source integrations.
• Provide Clear Documentation: Clearly document the capabilities and limitations of search enhancements. This would help developers understand what they can and cannot do with each query language and set appropriate expectations.

Proposed Solution

This method enhances the search service within the Data plugin, enabling it to dynamically switch the search interceptor at runtime. This mutable search interceptor can be modified by the new UI service, allowing plugin developers to integrate their query language. To support a new query language, developers will need to create a search strategy. This strategy will handle the translation of the query into a format understandable by their data source, manage the request to the data source, and format the response into a standardized data structure which is the data frame.

Create the UI Service

The Data plugin currently implements the top search bar, index pattern select, langauge selector, and time picker. With the UI service, it will abstract the components to a service that can be modified by other plugins.

Plugins will be able to register UI enhancements to directly modify or extend functionality of the top search bar. The initial list of supported enhancements will be:

• Adding a new querying language
  • Requires a when the user submits a query
• [OPTIONAL] Showing/hiding of the query input bar according to the query language
  • Contributors can hide the query input bar for users when adding a query language that doesn't support extra inputs.
• [OPTIONAL] Showing/hiding the visibility of the filter bar according to the query language
  • Contributors can hide the query input bar for users when adding a query language if they would want filters to be added within the query input.
• [OPTIONAL] Showing/hiding the visibility of the date picker according to the query language
  • Contributors can hide the query input bar for users when adding a query language if they would want date ranges to be added within the query input.
• [OPTIONAL] Showing/hiding the visibility of the auto refresh according to the query language
  • Contributors can hide the query input bar for users when adding a query language if they want to support auto refreshing.
• [OPTIONAL] Setting the initial value of query input according to the query language
  • Contributors can set the initial value of the query input for users, which replaces the key of the <data_source>, for when the query language does not support an empty query input.

Here is an example of interacting with the UI service to modify the search bar:

data.__enhance({
    ui: {
        query: {
            language: 'SQL',
            search: sqlSearchInterceptor,
            searchBar: { 
                showDatePicker: false,
                showFilterBar: false,
                queryStringInput: { 
                    initialValue: 'SELECT * FROM <data_source>'
                },
            },
        },
    },
});

Here we can see what the user will see if they are querying with DQL:

Here we can see what the user will if they are querying with the above example configurations:

Search Interceptor

The search interceptor invokes the appropriate search strategy. While a default search interceptor is provided out-of-the-box, plugins can replace it with a custom implementation. This custom search interceptor can be registered and activated based on the selected query language.

The search interceptor should be capable of accepting a DataFrame. This allows developers to leverage provided utility functions to construct queries for their specific data sources. For instance, if the query does not include the necessary time field for time series data, developers can use the getTimeField function to ensure the query is appropriately structured. This enhances the flexibility and usability of the search interceptor, catering to diverse data connect::data source requirements.

When users select the query language, the data service will replace the default search interceptor with the search interceptor provided by the adapter.

Search Strategy

The search strategy can be thought of as the adapter to your data connection::data source. Here is where you will need to map the response back to the data frame. For example:

// Parse the request parameters from the request body
const requestParams = parseRequest(request.body.query.qs);

// Update the request body query with the parsed search parameters
request.body.query = requestParams.search;

// Execute the PPL query and get the raw response
const rawResponse: any = await pplFacet.describeQuery(request);

// Get the 'search source' from the request parameters
const source = requestParams.map.get('search source');

// Create a partial DataFrame with the source and schema from the raw response
const partial: PartialDataFrame = {
  name: source,
  fields: rawResponse.data.schema,
};

// Convert the partial DataFrame into a DataFrame
const dataFrame = createDataFrame(partial);

// For each field in the DataFrame, set its values to the corresponding values in the raw response
dataFrame.fields.forEach((field, index) => {
  field.values = rawResponse.data.datarows.map((row: any) => row[index]);
});

// Set the size of the DataFrame to the number of data rows in the raw response
dataFrame.size = rawResponse.data.datarows.length;

return {
    type: 'data_frame',
    body: dataFrame,
    took: rawResponse.took,
} as IDataFrameResponse;

As you can see developers can convert their data to fields and values and call createDataFrame which will map the fields that OpenSearch Dashboards understands. If there any types that we need to expand for converting this is where that work will go.

Then developers will return back the data frame in the response. OpenSearch Dashboards parses this response and use it for further processing or visualization. This transformation process ensures compatibility between the custom data source and OpenSearch Dashboards, enabling seamless integration and usage of the data. Developers can decide if they want to include aggregations.

Data Frame

Index patterns play a crucial role within OpenSearch Dashboards, as the application anticipates the creation of an index pattern that aligns with your indices, with the mappings for that data serving as the type for the fields. As stated, above, the index pattern is an expected normalization of the data source which has to manually created by users. This means there can be a difference in the index pattern and the underlying indices. If you created an index pattern in the past but then ingest data that has new fields. In this case, users must refresh the index patterns.

To address this, the DataFrame type will be used with a schema and fields. The IDataFrame interface provides a standardized structure for data manipulation and analysis. This structure simplifies operations such as filtering, sorting (maybe), and aggregating, and facilitates the transfer of data between different parts of an application or even different applications. By explicitly defining the data structure through the IDataFrame interface, we not only enhance the predictability and usability of the system, but also empower developers to enhance their search capabilities. This well-defined structure provides a reliable blueprint for developers to map their data, thereby facilitating more efficient data manipulation and analysis within OpenSearch Dashboards. The schema will be the description of the data source where as the fields will be the expected result set.

The current implementation depends on the search strategy for properly getting the schema and fields. Upon data connection::data source change, the data frame will null when passed to the search strategy, developers can key off to populate the schema there are whatever hydration strategy of their choosing.

// TODO: add schema to this screen shot

There is a fallback of relying the fields if the schema isn't populated. The creation of UI Settings related to hydration search strategies can also be utilized. For example:

• update the schame per data source change
• update the schema per query
• update the schema per response (if schema is included in the response)
• automatic with increasing time intervals if the schema hasn't changed for a while. If the schema does change after the interval, then reset the time interval.

After receiving the data frame, the data frame will be normalized into an index pattern that is saved into memory. This temporary index pattern can then be utilized to interact with the other portions of the application.

Unsupported Types (non-Native)

The data plugin aims to convert the query and filters into an OpenSearch DSL, subsequently invoking the _search method from the chosen search interceptor.

When the query is passed to the data plugin to build out the DSL, the language is passed with the query. In code, we know how to support kuery (DQL), Lucene, or DSL.

However, if the query incorporates a language that is not native to OpenSearc, the data plugin recognizes the query as an unsupported type since it is not one of the three. In such cases, it refrains from constructing an OpenSearch Query and passes the full query to the search strategy. This approach ensures compatibility with a variety of query languages, enhancing the flexibility of the data plugin.

FAQ

[1] At what stage of the execution is this data frame structure generated?
When the Data plugin detects the query received is not an OpenSearch DSL, and there is not a data frame cached. It will create a data frame then it will pass around that data frame until data source is changed or a query is a DSL. If the data source has changed. It will replace the data frame cached. If it is a native query language, then it will unset the data frame.

[2] How often and under what circumstances is the data frame updated?
Per query, the data frame is updated, UI settings can be configured to change this strategy. Support data frames that require splicing (in the case of polling) into existing data frame instead of replacing fields.

[3] How does the data frame handle conflicts in value types?
The data frame will attempt to map fields that could be a timestamp to date based on if it can successfully be transformed. This logic can be expanded in the utility helper function.