question Joining large tables (dataframe and sql), but only want few columns: select before or after join? in Archives of Support Questions (Read Only)

Joining large tables (dataframe and sql), but only want few columns: select before or after join?

jestinm — Sat, 02 Jul 2016 20:47:46 GMT

Concerning memory usage and efficiency, when joining two large tables with many columns but only want a few columns from each of them, is it better to select() before or after the join()? My instinct tells me to select() before the join but some other voices would be very helpful

Re: Joining large tables (dataframe and sql), but only want few columns: select before or after join?

psingh15 — Sat, 02 Jul 2016 22:31:51 GMT

Yes. Reducing size of dataset before JOIN would surely help rather than other way round.

Re: Joining large tables (dataframe and sql), but only want few columns: select before or after join?

jestinm — Sun, 03 Jul 2016 00:07:41 GMT

Does this also hold for other methods besides JOIN? E.g., I want to do a groupBy. Should I select() before the groupBy()?

Re: Joining large tables (dataframe and sql), but only want few columns: select before or after join?

don_jernigan — Sun, 03 Jul 2016 09:51:06 GMT

Yes. You can think of select() as the "filter" of columns where filter() filters rows. You want to reduce the impact of the shuffle as much as possible. Perform both of these as soon as possible. The groupBy() is going to cause a shuffle by key (most likely). Be careful with the groupBy(). If you can accomplish what you need to do with a reduceBy(), you should use that instead.

If you mean dataframe instead of dataset, SparkSQL will handle much of this optimization for you. But if using normal RDDs, you are going to have to deal with these types of optimizations on your own.

Re: Joining large tables (dataframe and sql), but only want few columns: select before or after join?

psingh15 — Mon, 04 Jul 2016 12:03:41 GMT

Yes. A projection before any sort of transformation/action would help in computation time and storage optimization.