topic Re: Looking for ideas: non-equi join in pyspark notebook in Data Engineering

Looking for ideas: non-equi join in pyspark notebook

Ostrzak — Mon, 22 Jul 2024 13:20:50 GMT

Hi,

I'm looking for a way to create a join in pyspark notebook that accepts "not equal" in the joining clause. Simplistic example:

SELECT *

FROM tableA

JOIN tableB ON (tableA.id = tableB.id) AND (tableA.diff_id <> tableB.diff_id)

I tried different approaches both with SQL syntax in spark sql, as well as columnar expressions in pyspark api; nothing worked 😞

I am aware of the "crossjoin and filter" approach, but this is super inefficient and my datasets are relatively big.
Any help will be appreciated!

Re: Looking for ideas: non-equi join in pyspark notebook

Anonymous — Tue, 23 Jul 2024 04:08:52 GMT

HI @Ostrzak,

The 'ON' statement should put the key field between TableA and TableB that used to join, you can't directly put filter condition in it.
I'd like to suggest adding where condition after the join to filter or do pre-filter on two tables before join. (according to your description , you want the diff_id not equal records, so you can get the similar part records and use this as condition to filter 'main table' records which not include in it)
Join and filter:

SELECT * FROM tableA JOIN tableB ON tableA.id = tableB.id where tableA.id = tableB.id AND tableA.diff_id <> tableB.diff_id

Regards,

Xiaoxin Sheng

Re: Looking for ideas: non-equi join in pyspark notebook

Ostrzak — Tue, 23 Jul 2024 10:56:34 GMT

Hi Xiaoxin,

Thank you very much for your help. I found yesterday something similar, but your approach is much more elegant; especially that I need to dynamically change the JOIN and WHERE clauses. Now I can do it with f-strings 🙂
I'm accepting your post as a solution.