Remove unnecessary joins from view at runtime.

["Grant Overby (groverby)" <gr...@cisco.com>]

Suppose I have a view that joins 3 tables together. If I execute a query against this view that is answerable by only joining 2 of these 3 tables together, can hive preform this optimization automatically?

Example:

For the below select and view, I'd like hive to avoid the join on iede_xff6.

SELECT access_control_policy_name, xff_v4 FROM intrusion_view;


CREATE VIEW intrusion_view AS SELECT
ie.access_control_policy_name,
iede_xff4.data as xff_v4,
iede_xff6.data as xff_v6
FROM intrusion_events as ie
LEFT OUTER JOIN intrusion_extra_data_events iede_xff4 ON 1=1
    AND ie.dt = iede_xff4.dt
    AND ie.event_id = iede_xff4.event_id
    AND ie.event_second = iede_xff4.event_second
    AND ie.sensor_id = iede_xff4.sensor_id
    AND iede_xff4.type = 1
LEFT OUTER JOIN intrusion_extra_data_events iede_xff6 ON 1=1
    AND ie.dt = iede_xff6.dt
    AND ie.event_id = iede_xff6.event_id
    AND ie.event_second = iede_xff6.event_second
    AND ie.sensor_id = iede_xff6.sensor_id
    AND iede_xff6.type = 2



[http://www.cisco.com/web/europe/images/email/signature/est2014/logo_06.png?ct=1398192119726]

Grant Overby
Software Engineer
Cisco.com<http://www.cisco.com/>
groverby@cisco.com<ma...@cisco.com>
Mobile: 865 724 4910






[http://www.cisco.com/assets/swa/img/thinkbeforeyouprint.gif] Think before you print.

This email may contain confidential and privileged material for the sole use of the intended recipient. Any review, use, distribution or disclosure by others is strictly prohibited. If you are not the intended recipient (or authorized to receive for the recipient), please contact the sender by reply email and delete all copies of this message.

Please click here<http://www.cisco.com/web/about/doing_business/legal/cri/index.html> for Company Registration Information.