ESQL: Lookup Join meta issue

[costin]

Description

Meta ticket around the main implementation topics required for lookup join

• add grammar / parser LOOKUP JOIN

• Make it execute correctly on data nodes, too, and enable csv tests ...OnTheDataNode. See (reproducible) failed runs from ESQL: Fix lookup join output #117639 branch:

  • https://gradle-enterprise.elastic.co/s/5cihyuwehu2lu
  • https://gradle-enterprise.elastic.co/s/aox7wtnevoij2
  • Enhance LOOKUP JOIN csv-spec tests to cover more cases and fix several bugs found #117843

• ON condition

  • rewrite into JOIN equi-join maybe later
  • make ON field alias to USING field (syntax already used in enrich) - alternative is to deprecate this syntax
  • change index name grammar for identifier to source (to allow date math and others)
  • enhance the field matching grammar against various declarations such as parent.subfield & co.

• CSV tests (some suggestions to start with: Esql/lookup join grammar #116515 (comment))

  • Cover primary use cases: Enhance LOOKUP JOIN csv-spec tests to cover more cases and fix several bugs found #117843
  • Add tests for using timeseries indices in the FROM

• Known bugs

  • Make PushDownAndCombineLimits work for Joins ESQL: LookupJoin fails to push down limits #117698
  • Additional null rows ESQL: LOOKUP JOIN produces additional null rows #117702
  • EsqlSession.fieldNames does not handle lookup references that are also mentioned in aliases (erases them)
  • EsqlSession calls preAnalyzeLookupIndices asking for all the lookup index' fields all the time, c.f. Enhance LOOKUP JOIN csv-spec tests to cover more cases and fix several bugs found #117843 (comment)
  • Using an index alias makes it fail with a 500
  • Filtering right side columns removes all data ESQL: Skip lookup fields when eliminating missing fields #118658
  • using same lookup index in FROM breaks an internal assumption ESQL: using LOOKUP JOIN with same index as in FROM not working #118865
  • duplicate rows in some specific cases ESQL lookup join with lookup index results in duplicate set of rows. #118852
  • ESQL: LOOKUP JOIN and Sort: wrong optimizations or unplannable with "unknown physical plan node [OrderExec] #120817
  • ESQL: No rows with JOIN/ENRICH + EVAL + KEEP #120272

• field resolution

  • establish shadowing rules
  • clarify in USING/ON pattern the rules around the join key which are following downstream (left vs right)
  • make the other side fields nullable (this changes the equality for existing fields causing side-effects) create new fields or update the entire field declaration.
  • check resolution exception to differentiate between 500 and 400 ESQL: Don't throw VerificationException on illegal JOIN state during resolution #118826

• clarify output order rules in case of common keys (join keys and columns with same names) for consistency

  • SQL JOIN:
    • columns are kept in place and in lacking qualifiers, lead to ambiguation errors
    • JOIN USING moves the join keys in front followed by the columns in table declaration order
  • in ESQL the columns override each other:
    • in EVAL, the latest declaration wins alongside its position - all previous declarations are hidden as if they were removed
    • existing LOOKUP and INLINESTATS return left columns, join keys, right columns using EVAL rules for non-joining keys
    • STATS BY x, returns grouping keys last (declaration order)
  • proposal:
    • a. return join keys first (like SQL first)
    • b. return join keys last (declaration order, similar to STATS BY - clashes with USING)
    • c. we use ON, which is not a thing in SQL, and have it behave similar to ENRICH: join columns in ON remain in place, right hand side columns are added on the right (like ENRICH) and shadow any left hand side columns with conflicting names.
  • (c.f. output computation in Analyzer (Esql/lookup join grammar #116515 (comment)))
  • We decided on Proposal c.

• Filter pushdown (on the left/main branch) - see ESQL: Push down filter passed lookup join #118410

• Fetch only the required fields (relates to field extraction + fieldNames), resp. prune irrelevant lookup index fields from the plan. ESQL: Prune lookup join cols #118808

• Look whether the LookupJoin output could be computed inside the class ESQL: Fix lookup join output #117639

• Resolve lookup table mappings - LOOKUP JOIN using field-caps for field mapping #117246

  • Update lookup table mappings solution to support multiple join commands - Craig

• Update verifier and add corresponding tests Esql/lookup join grammar #116515 (comment).

  • disallow lookup joins on remote clusters/validate against CCQ
  • disallow lookup joins on more than 1 join field
  • Have validation for stuff that the grammar permits but we can't actually handle yet, like LOOKUP JOIN idx ON a == b AND to_upper(c) LIKE d

• Enhance tests

  • Cover primary use cases: Enhance LOOKUP JOIN csv-spec tests to cover more cases and fix several bugs found #117843
  • Add negative tests (missing indexes, indexes with wrong mode, unresolvable field references, etc.) ESQL: Small LOOKUP JOIN cleanups #117922

Compute

• Make field extractor work with LookupOperators (keep field extraction in one place)
• Bifurcate join operator/allow other operators between join and Lookup source (eval, filter, etc..)
• Align LookupJoin and LookupJoinExec - the first allows branching, the second does not.

Update: decided to postpone all this until we either evolve the way we perform the actual lookup join, or allow more features like actual condition expressions.

Later

• Pushdown optimizations ESQL: LOOKUP JOIN push down optimizations #119082

Maybe

• obtain IndexMode inside IndexResolver (FieldCaps)
• ReplaceMissingFieldWithNull rule does not have access to SearchStats for lookup indices ESQL: SearchStats for lookup indices in LocalLogicalPlanOptimizer #118690
• refactor JoinConfig into general Expression
• rewrite into JOIN equi-join
• name qualifiers (for disambiguation) (look to descope)

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[nicpenning]

I have been monitoring this feature and looking forward to it's capabilities.

I noticed this PR (elastic/kibana#207350) came down the pipeline and it made me wonder.

For LOOKUP to be usable on multiple indices, does it require the indices that will be joined to have Lookup Index mode or is that just a more efficient way to perform the operation?

I ask because I am trying to understand how the LOOKUP varies from some of the way that ENRICH works. It is painful today having to create Enrich policy after Enrich policy on the same dataset because you can only have 1 match field.

One of many use cases is enriching data from 1 source with another. Let's say I have firewall logs with source.ip : 1.2.3.4, event.action : block, url.domain : bad.xyz and I want to join that with DHCP data around that same time with the host.ip : 1.2.3.4., host.name : computer1, I would want to combine this into a single row showing something like:
host.ip : 1.2.3.4, host.name : computer 1, source.ip : 1.2.3.4, url.domain: bad.xyz, event.action : block

This would allow for joining data in way to simplify outcomes of multiple data sources.

Another use case is simply enriching without the need for enrich policies as stated above and just "enrich" using other indices ad-hoc. However, from what I have observed so far, there may be additional steps to perform this LOOKUP.

Any additional details on what the goal here is with some examples could help understand what is to come.

Thanks!

[nik9000]

For LOOKUP to be usable on multiple indices, does it require the indices that will be joined to have Lookup Index mode or is that just a more efficient way to perform the operation?

The LOOKUP JOIN will require that the index we join into the main stream has the lookup mode. Fundamentally it's about having a single sharded index which made the problem simpler. It was easier to get there from our ENRICH behavior than arbitrary joining.

[nicpenning]

Thanks for the clarification!

[getkub]

This feature is so important, for many of Enterprise customers like ours which are now exporting data outside Elastic to do the same. Can we have an example docs page to show the various abilities of these commands please?

When we say 'lookup' join searchtime , it is broad statement (for instance we are really looking for joining with ANOTHER index). So the ability to do 'sub-search' is quite important too as part of lookup. Hence if we can update into a list or preview of Elastic v9.x somewhere please I can produce some list of lookup examples and update you for testing ? Thanks again

[tylerperk]

Hi @getkub Lookup Join was released as Tech Preview in 8.18. Please check the announcement blog and the docs for more information.

We are aware of the need for joining against any index and sub searches - both are on the future roadmap.

[getkub]

We are aware of the need for joining against any index and sub searches - both are on the future roadmap.

Great progress.
In the blogpost example, if you could solve it using "<subsearch>" instead of an "<index>", that would be ideal

FROM kibana_sample_data_logs | WHERE response.keyword != "200" 
| LOOKUP JOIN   [ FROM env_index |  stats count(*) by client.ip, environment | keep client.ip, environment] ON client.ip
| STATS COUNT(*) by response, environment