Support client spooling protocol

[sbernauer]

Some customers have the problem that it is very slow to get data out of Trino.

The spooling protocol uses an object storage location to store the data for retrieval by the client. The coordinator and all workers can write result set data to the storage in parallel
[..]
Provides higher throughput for data transfer, specifically for queries that return more data.
Requires object storage and configuration on the Trino cluster.

Some of the customers use IBM DataStage and have performance problems, which should be solved by client spooling.

This task is about supporting https://trino.io/docs/current/client/client-protocol.html#spooling-protocol via our CRD

Notable acceptance criteria

• configOverrides are supported

[lfrancke]

Next Step: Technical Refinement
Timebox: 2 days maximum to investigate and update this issue

Goals/outcome I'd like to see:

• Clarify if CRD Versioning can/should be used?
• A totally non-finished non-working rough draft of how the CRD for this could look like (it's okay for this to be not final, and purely based on reading the docs)
  • This probably means roughly deciding which of these settings we want/need to expose and how: https://trino.io/docs/current/admin/properties-client-protocol.html#spooling-protocol-properties
• What object storage backends need to be supported? Do we have all the necessary infrastructure in place (I mean in op-rs) or do we need bigger discussions around new structs etc.
• How do we test/validate the performance improvement?
• Prepare a go/no-go decision: Should you recommend "go" please also try to include a rough plan on what to do in which order focusing on steps that are independently useful (e.g. maybe only implement S3 support initially and then later add others, or only support it with overrides initially and leave CRD changes until later)

I especially find this part in the Trino docs confusing:

Suitable object storage systems for spooling are S3 and compatible systems, Azure Storage, and Google Cloud Storage. The object storage system must provide good connectivity for all cluster nodes as well as any clients.

Activate the desired system with fs.s3.enabled, fs.azure.enabled, or fs.s3.enabled=true in etc/spooling-manager.propertiesand configure further details using relevant properties from Spooling file system properties, S3 file system support, Azure Storage file system support, and Google Cloud Storage file system support.

Questions:

• Are S3, Azure and Google Cloud separate things or are they all "S3 compatible"? (In regards to our S3Bucket/S3Connection)
  • I know we have a common S3 struct but do we need/have the same for Azure/Google Cloud?
• If it fits within the timebox please add example config settings for how spooling would look like for each of these three storage locations. In particular for Azure and Google as the docs only have S3 examples for fs.location

Note

I opened a PR to (hopefully) clarify the docs in Trino with my understanding. If that isn't merged it makes sense to look at that.

Please use our refinement template and fill out the relevant sections, especially:

• Dependencies (object storage setup, customer access for testing maybe if this happened for a specific customer?)
• Tasks (break down the implementation work)
• Acceptance Criteria (what "working client spooling" looks like)

After refinement: We'll have a clear go/no-go decision
If anyone wants to collaborate on this refinement or has experience with Trino's spooling feature, please jump in!

[razvan]

Next Step: Technical Refinement Timebox: 2 days maximum to investigate and update this issue
* Clarify if CRD Versioning can/should be used?

As briefly discussed in the daily (8.09) we want to use CRD versioning only to manage breaking changes. This is a new feature.

* A totally non-finished non-working rough draft of how the CRD for this could look like (it's okay for this to be not final, and purely based on reading the docs)
  
  * This probably means roughly deciding which of these settings we want/need to expose and how: https://trino.io/docs/current/admin/properties-client-protocol.html#spooling-protocol-properties

A decision is being made here: https://github.com/stackabletech/decisions/issues/60

* What object storage backends need to be supported? Do we have all the necessary infrastructure in place (I mean in op-rs) or do we need bigger discussions around new structs etc.

Only the S3 filesystem is supported by the CRD here. The others (Azure Block Storage and Google Cloud ...) should be possible via config overrides.

NOTE: Support for other S3 compatible systems such as the ones above is a separate epic because it touches multiple repos and existing features.

* How do we test/validate the performance improvement?

Update: we have tests for functionality not for performance gains. When spooling is configured, Trino decides how to best serve query results.

I hope an integration test with Minio as backend can do it.