Join hints across dialects

[williaster]

I have a project where the base dialect is presto which doesn't support join hints. We currently also support outputting our generated SQL into spark, which does support join hints which we want to leverage.

The challenge is that if we insert join hints in the base presto queries, they are stripped with the logging message hints not supported. So then when we convert to spark, the hints are gone.

I'm trying to figure out a good strategy for something like

1. write base queries in presto (no hints)
2. transpile to spark
3. add hints to spark

but I'm struggling to find the syntax to insert the hints at step 2 using sqlglot. There are say ~10+ subqueries that would benefit from them, so perhaps a transform would work but I couldn't figure out the right syntax in the docs.

Thanks!

---

p.s. A HUGE win would also being able to swap out id_subject in (select id_subject from subquery) in presto to a semi join in spark. this seems even harder than the hints, but maybe could be done with a similar transform approach.

[georgesittas]

Regarding the hints, what about patching or subclassing the Presto dialect to bring the hint support back in? E.g. you would allow them to be parsed but the generator would still omit them so you don't get invalid Presto SQL if you wanted to target that instead of Spark.

The semi-join one would need to be implemented as a pre-generation transformation, i.e. something similar to what we have in transforms.py. I'd probably look into preprocessing exp.Select and looking for exp.In expressions, so you then check if those can be transformed into semi-joins and transform the Select accordingly.

EDIT: with "to bring the hint support back in" I meant reverting this change, i.e. adding "/*+" back to KEYWORDS.

[williaster]

thanks for the tip here! 🙏 in case it's helpful for others, this is a basic override of the Presto dialect class that seemed to work. may still tweak a bit.