[FEATURE] Add a feature flag which will activate API to switch SQL engines

Yury-Fridlyand commented 1 year ago

Is your feature request related to a problem?

The plugin has 2 SQL engines, we are currently working on deprecating the old one. To test compatibility we need to have an option to specify the SQL engine with each query sent to the OpenSearch. This option shouldn't be available to any end user, so it should be enabled by a feature flag (hereby: FF) during build.

What solution would you like?

A gradle command line option to activate corresponding FF which will enable the feature. Feature PoC available for review at https://github.com/Bit-Quill/opensearch-project-sql/pull/160

What alternatives have you considered?

Specify non-zero fetch_size or format=json REST parameter to enforce query execution on the old engine (those options are not implemented in new one).
Add nullif(1,1) is null statement to WHERE clause to ensure that query won't be executed on legacy engine. It doesn't support nullif function.

Do you have any additional context?

FF usage example:

Build plugin as usual (feature deactivated and unavailable)

./gradlew build

(This command in used in CI). https://github.com/opensearch-project/sql/blob/2.x/.github/workflows/sql-test-and-build-workflow.yml#L45

Build plugin with FF:

./gradlew build -Dengine_switch_option

penghuo commented 1 year ago

alternative, using jvm options or environment variable.

ksco92 commented 1 year ago

From these threads:

https://opensearch.slack.com/archives/C0526AVT84S/p1689790429252599

I faced an issue where V1 would have issues querying aliases via SQL and DataGrip was not allowing to set the fetch size to 0.

What about a separate driver that has fetch size hard coded to 0? If I recall correctly, @Yury-Fridlyand gave me a jar with that and fixed my problem by running all queries in V2. However, that file in my laptop doesn't really scale if I want my users to be able to query and I'd prefer if they can download it directly from the OS website. This driver should include all the dependencies, not like the Maven one.

Yury-Fridlyand commented 1 year ago

I expect configuring that feature flag would be unavailable for AWS managed OpenSearch. I tried to implement engine switch as a URL parameter, which could be used by JDBC/ODBC driver in https://github.com/Bit-Quill/opensearch-project-sql/pull/160, but that approach was rejected.

opensearch-project / sql