Caching table partition after join

["Zalzberg, Idan (Agoda)" <Id...@agoda.com>]

Hi,

I have a complicated scenario where I can't seem to explain to spark how to handle the query in the best way.
I am using spark from the thrift server so only SQL.

To explain the scenario, let's assume:

Table A:
Key : String
Value : String

Table B:
Key: String
Value2: String
Part : String [Partitioning column]

Now, all my queries look like
Select A.Value,B.Value2 from A join B on (A.Key = B.Key) where B.Part = "SOMEHTING"

I would like to have a cache of the *output* of this query without knowing the value "SOMETHING" in advance.
I.e. I would image I can create a *lazy* view like

Create view MyView as Select A.Value,B.Value2,B.Part from A join B on (A.Key = B.Key)

Users will *never* query the whole view, that would be huge and blow up the cache. They would always do something like:

Select * from MyView where Part = "SOMETHINGELSE"

I would expect spark to be able to push down the partition filter on Part and then cache only those output from the query, basically keeping the view partitioned by Part in the same manner as B is.

I cannot pre-materialize the whole view since it will be very big, even in columnar storage.

Can I achieve this behavior?

Thanks


________________________________
This message is confidential and is for the sole use of the intended recipient(s). It may also be privileged or otherwise protected by copyright or other legal rules. If you have received it by mistake please let us know by reply email and delete it from your system. It is prohibited to copy this message or disclose its content to anyone. Any confidentiality or privilege is not waived or lost by any mistaken delivery or unauthorized disclosure of the message. All messages sent to and from Agoda may be monitored to ensure compliance with company policies, to protect the company's interests and to remove potential malware. Electronic messages may be intercepted, amended, lost or deleted, or contain viruses.