[FEATURE] Support join functionality for trace-analytics use case

[joshuali925]

Trace analytics considered using SQL/PPL for its queries, but some graphs rely on joining two indices (span index and service map index) together which is not supported by SQL/PPL. Trace analytics was implemented with DSL + javascript, would be good if join being added to SQL could support this use case.

Schema

There will be at least 2 indices, otel-v1-apm-span-* (large) and otel-v1-apm-service-map (small).

Relevant fields from indices:

otel-v1-apm-span-*:

• traceId - A unique identifier for a trace. All spans from the same trace share the same traceId.
• spanId - A unique identifier for a span within a trace, assigned when the span is created.
• parentSpanId - The spanId of this span's parent span. If this is a root span, then this field must be empty.
• durationInNanos - Difference in nanoseconds between startTime and endTime. (this is latency in UI)
• serviceName - The resource from the span originates.
• traceGroup - The name of the trace's root span.

otel-v1-apm-service-map:

• serviceName - The name of the service which emitted the span.
• destination.domain - The serviceName of the service being called by this client.
• destination.resource - The span name (API, operation, etc.) being called by this client.
• target.domain - The serviceName of the service being called by a client.
• target.resource - The span name (API, operation, etc.) being called by a client.
• traceGroupName - The top-level span name which started the request chain.

Full schemas are defined in data-prepper repo: otel-v1-apm-span-*, otel-v1-apm-service-map

Sample documents

Run the following commands to dump sample documents for the two indices to localhost:9200:

curl -X PUT "localhost:9200/otel-v1-apm-service-map?pretty"
curl https://raw.githubusercontent.com/opensearch-project/trace-analytics/main/dashboards-observability/.cypress/utils/otel-v1-apm-service-map-mappings.json | curl -H 'Content-Type: application/json' -XPOST 'localhost:9200/otel-v1-apm-service-map/_mapping?pretty' --data-binary @-
curl https://raw.githubusercontent.com/opensearch-project/trace-analytics/main/dashboards-observability/.cypress/utils/otel-v1-apm-service-map.json | curl -H 'Content-Type: application/x-ndjson' -XPOST 'localhost:9200/otel-v1-apm-service-map/_bulk?pretty' --data-binary @-

curl -X PUT "localhost:9200/otel-v1-apm-span-000001?pretty"
curl https://raw.githubusercontent.com/opensearch-project/trace-analytics/main/dashboards-observability/.cypress/utils/otel-v1-apm-span-000001-mappings.json | curl -H 'Content-Type: application/json' -XPOST 'localhost:9200/otel-v1-apm-span-000001/_mapping?pretty' --data-binary @-
curl https://raw.githubusercontent.com/opensearch-project/trace-analytics/main/dashboards-observability/.cypress/utils/otel-v1-apm-span-000001.json | curl -H 'Content-Type: application/x-ndjson' -XPOST 'localhost:9200/otel-v1-apm-span-000001/_bulk?pretty' --data-binary @-
curl https://raw.githubusercontent.com/opensearch-project/trace-analytics/main/dashboards-observability/.cypress/utils/otel-v1-apm-span-000002.json | curl -H 'Content-Type: application/x-ndjson' -XPOST 'localhost:9200/otel-v1-apm-span-000001/_bulk?pretty' --data-binary @-

Requirement

Support join to calculate the following:

For each service, join span index on service map index to calculate metrics under different type of filters.

This sample query calculates latency when filtered by trace group client_cancel_order for the order service. I only have a subquery example, don't have the join version of the query..

SELECT avg(durationInNanos)
FROM `otel-v1-apm-span-000001` t1
WHERE t1.serviceName = `order`
  AND ((t1.name in
          (SELECT target.resource
           FROM `otel-v1-apm-service-map`
           WHERE serviceName = `order`
             AND traceGroupName = `client_cancel_order`)
        AND t1.parentSpanId != NULL)
       OR (t1.parentSpanId = NULL
           AND t1.name = `client_cancel_order`))
  AND t1.traceId in
    (SELECT traceId
     FROM `otel-v1-apm-span-000001`
     WHERE serviceName = `order`)