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

[Yury-Fridlyand]

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 Bit-Quill#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]

alternative, using jvm options or environment variable.

[ksco92]

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]

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 Bit-Quill#160, but that approach was rejected.