[FEATURE] Enable Calcite-only PPL commands (join, lookup, etc.) for Prometheus datasources

[robertpaschedag]

Is your feature request related to a problem?

Non-OpenSearch datasources (e.g., Prometheus) cannot use any of the 31 Calcite-only PPL commands (join, lookup, flatten, expand, eventstats, streamstats, union, etc.). When Calcite is enabled, queries against Prometheus fall back to the V2 engine, which only supports source, where (limited), stats, and describe. This means users cannot correlate Prometheus metrics with OpenSearch data using join or lookup, severely limiting observability use cases.

What solution would you like?

Make PrometheusMetricTable participate in the Calcite query planning pipeline by implementing Calcite's TranslatableTable interface. This enables:

1. All 31 Calcite-only PPL commands to work with Prometheus datasources
2. Cross-datasource joins between OpenSearch indices and Prometheus metrics (federated queries)
3. Filter pushdown of label equality conditions and time ranges into PromQL
4. Dynamic sub-schema resolution for multi-part table references (source = prometheus.metric_name)

What alternatives have you considered?

1. Implementing join/lookup in the V2 engine — Would require refactoring the single-table assumption in Planner.findTable(), creating LogicalJoin/LogicalLookup nodes, and implementing physical join operators. Estimated 2-4 weeks, and wouldn't benefit from Calcite's built-in optimizer.
2. Using ScannableTable (no pushdown) — Simpler but all filtering done in-memory by Calcite. Not viable for production since Prometheus queries need time-bounded PromQL.
3. Using FilterableTable — Simpler filter pushdown model, but TranslatableTable.toRel() takes priority per Calcite's resolution order, and doesn't allow custom cost models.

Do you have any additional context?

• This was explicitly out of scope in the original Prometheus federation design (docs/dev/datasource-prometheus.md: "Join Queries across datasources is out of scope")
• The implementation leverages Calcite's native multi-schema federation — no changes to the join/lookup implementations themselves
• Label equality filters (service = 'frontend') and time range comparisons on @timestamp are pushed down to PromQL; all other conditions are evaluated in-memory by Calcite
• Default time window is 1 hour (matching V2 behavior); full TranslatableTable with time range pushdown allows WHERE clause narrowing
• Validated end-to-end with OTel demo: source = logs | join prometheus.request ON ... works correctly

Yes..... this description is AI generated...but the initial request comes from real users...currently using elasticsearch ES|QL. The difference is, that the metrics are also stored in elasticsearch indices, while, in our current case, the metrics are stored in an associated prometheus compatible data source.

So...being able to do some lookup, join, etc. also on those metrics would be pretty nice