I've always found it hard to bend my mind to SQL codes especially those with nested subqueries. Instead, I love to have codes organized in the same way as data-flow like Pig. For people like me, SQL CTEs(common table expressions) come to our rescue and greatly improve readability of SQL codes. This is a simplified typical CTR use case written in SQL CTEs, which returns all users that both view and click on items.
with t1 as
(select * from impression),
t2 as
(select * from click)
select t1.* from t1, t2 where t1.user_id == t2.user_id
I've always found it hard to bend my mind to SQL codes especially those with nested subqueries. Instead, I love to have codes organized in the same way as data-flow like Pig. For people like me, SQL CTEs(common table expressions) come to our rescue and greatly improve readability of SQL codes. This is a simplified typical CTR use case written in SQL CTEs, which returns all users that both view and click on items.
Both Hive and Spark's Hive context support CTE.