http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java index e8ad8ff..e8905c1 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java @@ -48,14 +48,14 @@ public class TestBridgeTableQueries extends TestQueryRewrite { public void testBridgeTablesWithoutDimtablePartitioning() throws Exception { String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", + String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", - null, "group by usersports.name", null, + null, "group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column @@ -68,14 +68,14 @@ public class TestBridgeTableQueries extends TestQueryRewrite { public void testBridgeTablesForExprFieldWithoutDimtablePartitioning() throws Exception { String query = "select substr(usersports.name, 10), sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select substr(usersports.name, 10), sum(basecube.msr2) FROM ", + String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + + " join (select user_interests.user_id as user_id,collect_set(substr(usersports.name, 10)) as balias0" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", - null, "group by substr(( usersports . name ), 10 )", null, + null, "group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column @@ -109,14 +109,14 @@ public class TestBridgeTableQueries extends TestQueryRewrite { conf.set(CubeQueryConfUtil.BRIDGE_TABLE_FIELD_AGGREGATOR, "custom_aggr"); String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", + String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,custom_aggr(usersports.name) as name" + + " join (select user_interests.user_id as user_id,custom_aggr(usersports.name) as balias0" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", - null, "group by usersports.name", null, + null, "group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column @@ -129,16 +129,17 @@ public class TestBridgeTableQueries extends TestQueryRewrite { public void testBridgeTablesWithMegringChains() throws Exception { String query = "select userInterestIds.sport_id, usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select userInterestIds.sport_id, usersports.name," + String expected = getExpectedQuery("basecube", "select userInterestIds.balias0, usersports.balias0," + " sum(basecube.msr2) FROM ", " join " + getDbName() + "c1_usertable userdim on basecube.userid = userdim.id join (select userinterestids" - + ".user_id as user_id,collect_set(userinterestids.sport_id) as sport_id from " + getDbName() + + ".user_id as user_id,collect_set(userinterestids.sport_id) as balias0 from " + getDbName() + "c1_user_interests_tbl userinterestids group by userinterestids.user_id) userinterestids on userdim.id = " - + "userinterestids.user_id join (select userinterestids.user_id as user_id,collect_set(usersports.name) as name" - + " from " + getDbName() + "c1_user_interests_tbl userinterestids join " + + "userinterestids.user_id " + + "join (select userinterestids.user_id as user_id,collect_set(usersports.name) as balias0 from " + + getDbName() + "c1_user_interests_tbl userinterestids join " + getDbName() + "c1_sports_tbl usersports on userinterestids.sport_id = usersports.id" + " group by userinterestids.user_id) usersports on userdim.id = usersports.user_id", - null, "group by userInterestIds.sport_id, usersports.name", null, + null, "group by userinterestids.balias0, usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column @@ -152,21 +153,21 @@ public class TestBridgeTableQueries extends TestQueryRewrite { String query = "select usersports.name, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, hConf); String expected1 = getExpectedQuery("basecube", - "select usersports.name as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + "select usersports.balias0 as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, - "group by usersports.name", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + "group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); String expected2 = getExpectedQuery("basecube", - "select usersports.name as `name`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "select usersports.balias0 as `name`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, - "group by usersports.name", null, + "group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); @@ -182,21 +183,21 @@ public class TestBridgeTableQueries extends TestQueryRewrite { query = "select sports, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE; hqlQuery = rewrite(query, hConf); expected1 = getExpectedQuery("basecube", - "select usersports.name as `sports`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + "select usersports.balias0 as `sports`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, - "group by usersports.name", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + "group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); expected2 = getExpectedQuery("basecube", - "select usersports.name as `sports`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "select usersports.balias0 as `sports`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, - "group by usersports.name", null, + "group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); @@ -215,22 +216,22 @@ public class TestBridgeTableQueries extends TestQueryRewrite { String query = "select usersports.name, xusersports.name, yusersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select usersports.name, xusersports.name, yusersports.name," - + " sum(basecube.msr2) FROM ", + String expected = getExpectedQuery("basecube", "select usersports.balias0, xusersports.balias0, " + + "yusersports.balias0, sum(basecube.msr2) FROM ", " join " + getDbName() + "c1_usertable userdim_1 on basecube.userid = userdim_1.id " - + " join (select user_interests_1.user_id as user_id, collect_set(usersports.name) as name from " + + " join (select user_interests_1.user_id as user_id, collect_set(usersports.name) as balias0 from " + getDbName() + "c1_user_interests_tbl user_interests_1 join " + getDbName() + "c1_sports_tbl usersports on " + "user_interests_1.sport_id = usersports.id group by user_interests_1.user_id) " + "usersports on userdim_1.id = usersports.user_id" + " join " + getDbName() + "c1_usertable userdim_0 on basecube.yuserid = userdim_0.id " - + " join (select user_interests_0.user_id as user_id,collect_set(yusersports.name) as name from " + + " join (select user_interests_0.user_id as user_id,collect_set(yusersports.name) as balias0 from " + getDbName() + "c1_user_interests_tbl user_interests_0 join " + getDbName() + "c1_sports_tbl yusersports on " + " user_interests_0.sport_id = yusersports.id group by user_interests_0.user_id) yusersports on userdim_0.id =" + " yusersports.user_id join " + getDbName() + "c1_usertable userdim on basecube.xuserid = userdim.id" - + " join (select user_interests.user_id as user_id,collect_set(xusersports.name) as name from " + + " join (select user_interests.user_id as user_id,collect_set(xusersports.name) as balias0 from " + getDbName() + "c1_user_interests_tbl user_interests join " + getDbName() + "c1_sports_tbl xusersports" + " on user_interests.sport_id = xusersports.id group by user_interests.user_id) xusersports on userdim.id = " - + " xusersports.user_id", null, "group by usersports.name, xusersports.name, yusersports.name", null, + + " xusersports.user_id", null, "group by usersports.balias0, xusersports.balias0, yusersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column @@ -246,23 +247,23 @@ public class TestBridgeTableQueries extends TestQueryRewrite { String query = "select usersports.name, xusersports.name, yusersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.name, xusersports.name, yusersports.name," - + " sum(basecube.msr2) FROM ", + String expected = getExpectedQuery("basecube", "select usersports.balias0, xusersports.balias0, " + + "yusersports.balias0, sum(basecube.msr2) FROM ", " left outer join " + getDbName() + "c1_usertable userdim_1 on basecube.userid = userdim_1.id " - + " left outer join (select user_interests_1.user_id as user_id, collect_set(usersports.name) as name from " + + " left outer join (select user_interests_1.user_id as user_id, collect_set(usersports.name) as balias0 from " + getDbName() + "c1_user_interests_tbl user_interests_1 join " + getDbName() + "c1_sports_tbl usersports on " + "user_interests_1.sport_id = usersports.id group by user_interests_1.user_id) " + "usersports on userdim_1.id = usersports.user_id" + " left outer join " + getDbName() + "c1_usertable userdim_0 on basecube.yuserid = userdim_0.id " - + " left outer join (select user_interests_0.user_id as user_id,collect_set(yusersports.name) as name from " + + " left outer join (select user_interests_0.user_id as user_id,collect_set(yusersports.name) as balias0 from " + getDbName() + "c1_user_interests_tbl user_interests_0 join " + getDbName() + "c1_sports_tbl yusersports on " + " user_interests_0.sport_id = yusersports.id group by user_interests_0.user_id) yusersports on userdim_0.id =" + " yusersports.user_id left outer join " + getDbName() + "c1_usertable userdim on basecube.xuserid = userdim.id" - + " left outer join (select user_interests.user_id as user_id,collect_set(xusersports.name) as name from " + + " left outer join (select user_interests.user_id as user_id,collect_set(xusersports.name) as balias0 from " + getDbName() + "c1_user_interests_tbl user_interests join " + getDbName() + "c1_sports_tbl xusersports" + " on user_interests.sport_id = xusersports.id group by user_interests.user_id) xusersports on userdim.id = " - + " xusersports.user_id", null, "group by usersports.name, xusersports.name, yusersports.name", null, + + " xusersports.user_id", null, "group by usersports.balias0, xusersports.balias0, yusersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column @@ -270,21 +271,22 @@ public class TestBridgeTableQueries extends TestQueryRewrite { hqlQuery = rewrite(query, conf); TestCubeRewriter.compareQueries(hqlQuery, expected); } + @Test public void testBridgeTablesWithDimTablePartitioning() throws Exception { Configuration conf = new Configuration(hConf); conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C2"); String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", + String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", " join " + getDbName() + "c2_usertable userdim ON basecube.userid = userdim.id and userdim.dt='latest' " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + getDbName() + "c2_user_interests_tbl user_interests" + " join " + getDbName() + "c2_sports_tbl usersports on user_interests.sport_id = usersports.id" + " and usersports.dt='latest and user_interests.dt='latest'" + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", - null, "group by usersports.name", null, + null, "group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c2_testfact1_base")); TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column @@ -298,10 +300,10 @@ public class TestBridgeTableQueries extends TestQueryRewrite { String query = "select usersports.name, cubestatecountry.name, cubecitystatecountry.name," + " sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select usersports.name, cubestatecountry.name, " + String expected = getExpectedQuery("basecube", "select usersports.balias0, cubestatecountry.name, " + "cubecitystatecountry.name, sum(basecube.msr2) FROM ", " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + " group by user_interests.user_id) usersports" @@ -313,7 +315,7 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + "c1_countrytable cubecitystatecountry on statedim_0.countryid=cubecitystatecountry.id" + " join " + getDbName() + "c1_statetable statedim on basecube.stateid=statedim.id and (statedim.dt = 'latest')" + " join " + getDbName() + "c1_countrytable cubestatecountry on statedim.countryid=cubestatecountry.id ", - null, "group by usersports.name, cubestatecountry.name, cubecitystatecountry.name", null, + null, "group by usersports.balias0, cubestatecountry.name, cubecitystatecountry.name", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column @@ -321,4 +323,532 @@ public class TestBridgeTableQueries extends TestQueryRewrite { hqlQuery = rewrite(query, hConf); TestCubeRewriter.compareQueries(hqlQuery, expected); } + + @Test + public void testBridgeTablesWithFilterBeforeFlattening() throws Exception { + String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE + + " and usersports.name = 'CRICKET' and usersports.name in ('BB', 'FOOTBALL')" + + " and usersports.name != 'RANDOM' and usersports.name not in ('xyz', 'ABC')" + + " and (some_filter(usersports.name, 'CRICKET') OR some_filter(usersports.name, 'FOOTBALL'))" + + " and not (some_filter(usersports.name, 'ASD') OR some_filter(usersports.name, 'ZXC'))" + + " and myfunc(usersports.name) = 'CRT' and substr(usersports.name, 3) in ('CRI')"; + String hqlQuery = rewrite(query, hConf); + String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0," + + " collect_set(myfunc(usersports.name)) as balias1, collect_set(substr(usersports.name, 3)) as balias2" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id " + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", + null, " and array_contains(usersports.balias0,'CRICKET') and (array_contains(usersports.balias0, 'BB')" + + " OR array_contains(usersports.balias0, 'FOOTBALL'))" + + " and not array_contains(usersports.balias0, 'RANDOM'))" + + " and not (array_contains(usersports.balias0, 'xyz') OR array_contains(usersports.balias0, 'ABC'))" + + " and (some_filter(usersports.name, 'CRICKET') OR some_filter(usersports.name, 'FOOTBALL'))" + + " and not (some_filter(usersports.name, 'ASD') OR some_filter(usersports.name, 'ZXC'))" + + " and (array_contains(usersports.balias1, 'CRT') AND array_contains(usersports.balias2, 'CRI'))" + + "group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports, sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET' and " + + " sports in ('BB', 'FOOTBALL') and sports != 'RANDOM' and sports not in ('xyz', 'ABC')" + + " and (some_filter(sports, 'CRICKET') OR some_filter(sports, 'FOOTBALL'))" + + " and not (some_filter(sports, 'ASD') OR some_filter(sports, 'ZXC'))" + + " and myfunc(sports) = 'CRT' and sports_abbr in ('CRI')"; + hqlQuery = rewrite(query, hConf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testBridgeTablesWithFilterAndOrderby() throws Exception { + String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE + + " and usersports.name = 'CRICKET' order by usersports.name"; + String hqlQuery = rewrite(query, hConf); + String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id " + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", + null, + " and array_contains(usersports.balias0, 'CRICKET') group by usersports.balias0 order by usersports.balias0 asc", + null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports, sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET' order by " + + "sports"; + hqlQuery = rewrite(query, hConf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testFlattenBridgeTablesWithCustomFilter() throws Exception { + Configuration conf = new Configuration(hConf); + conf.set(CubeQueryConfUtil.BRIDGE_TABLE_FIELD_ARRAY_FILTER, "custom_filter"); + String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE + + " and usersports.name in ('CRICKET','FOOTBALL')"; + String hqlQuery = rewrite(query, conf); + String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", null, + " and (custom_filter(usersports.balias0, 'CRICKET') OR custom_filter(usersports.balias0, 'FOOTBALL'))" + + "group by usersports.balias0", + null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports, sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and sports in ('CRICKET','FOOTBALL')"; + hqlQuery = rewrite(query, conf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testBridgeTablesWithFilterAfterFlattening() throws Exception { + Configuration conf = new Configuration(hConf); + conf.setBoolean(CubeQueryConfUtil.DO_FLATTENING_OF_BRIDGE_TABLE_EARLY, true); + String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE + + " and usersports.name = 'CRICKET,FOOTBALL'"; + String hqlQuery = rewrite(query, conf); + String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports, sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET,FOOTBALL'"; + hqlQuery = rewrite(query, conf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testBridgeTablesWithMultipleFactsWithFilterBeforeFlattening() throws Exception { + String query = "select usersports.name, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + + " and usersports.name = 'CRICKET'"; + String hqlQuery = rewrite(query, hConf); + String expected1 = getExpectedQuery("basecube", + "select usersports.balias0 as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + "group by user_interests.user_id) usersports on userdim.id = usersports.user_id ", null, + " and array_contains(usersports.balias0,'CRICKET') group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + String expected2 = getExpectedQuery("basecube", + "select usersports.balias0 as `name`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports on userdim.id = usersports.user_id ", null, + " and array_contains(usersports.balias0,'CRICKET') group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); + TestCubeRewriter.compareContains(expected1, hqlQuery); + TestCubeRewriter.compareContains(expected2, hqlQuery); + String lower = hqlQuery.toLowerCase(); + assertTrue(lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq2.msr2 msr2, mq1.msr12 msr12 from ") + || lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq1.msr2 msr2, mq2.msr12 msr12 from "), hqlQuery); + + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), + hqlQuery); + // run with chain ref column + query = "select sports, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET'"; + hqlQuery = rewrite(query, hConf); + expected1 = getExpectedQuery("basecube", + "select usersports.balias0 as `sports`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports on userdim.id = usersports.user_id ", null, + "and array_contains(usersports.balias0,'CRICKET') group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + expected2 = getExpectedQuery("basecube", + "select usersports.balias0 as `sports`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports on userdim.id = usersports.user_id ", null, + " and array_contains(usersports.balias0,'CRICKET') group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); + TestCubeRewriter.compareContains(expected1, hqlQuery); + TestCubeRewriter.compareContains(expected2, hqlQuery); + lower = hqlQuery.toLowerCase(); + assertTrue(lower.startsWith("select coalesce(mq1.sports, mq2.sports) sports, mq2.msr2 msr2, mq1.msr12 msr12 from ") + || lower.startsWith("select coalesce(mq1.sports, mq2.sports) sports, mq1.msr2 msr2, mq2.msr12 msr12 from "), + hqlQuery); + + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.sports <=> mq2.sports"), + hqlQuery); + } + + @Test + public void testBridgeTablesWithMultipleFactsWithFilterAfterFlattening() throws Exception { + Configuration conf = new Configuration(hConf); + conf.setBoolean(CubeQueryConfUtil.DO_FLATTENING_OF_BRIDGE_TABLE_EARLY, true); + String query = "select usersports.name, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + + " and usersports.name = 'CRICKET,FOOTBALL'"; + String hqlQuery = rewrite(query, conf); + String expected1 = getExpectedQuery("basecube", + "select usersports.name as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + String expected2 = getExpectedQuery("basecube", + "select usersports.name as `name`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); + TestCubeRewriter.compareContains(expected1, hqlQuery); + TestCubeRewriter.compareContains(expected2, hqlQuery); + String lower = hqlQuery.toLowerCase(); + assertTrue(lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq2.msr2 msr2, mq1.msr12 msr12 from ") + || lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq1.msr2 msr2, mq2.msr12 msr12 from "), hqlQuery); + + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), + hqlQuery); + // run with chain ref column + query = "select sports, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + + " and sports = 'CRICKET,FOOTBALL'"; + hqlQuery = rewrite(query, conf); + expected1 = getExpectedQuery("basecube", + "select usersports.name as `sports`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + expected2 = getExpectedQuery("basecube", + "select usersports.name as `sports`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); + TestCubeRewriter.compareContains(expected1, hqlQuery); + TestCubeRewriter.compareContains(expected2, hqlQuery); + lower = hqlQuery.toLowerCase(); + assertTrue(lower.startsWith("select coalesce(mq1.sports, mq2.sports) sports, mq2.msr2 msr2, mq1.msr12 msr12 from ") + || lower.startsWith("select coalesce(mq1.sports, mq2.sports) sports, mq1.msr2 msr2, mq2.msr12 msr12 from "), + hqlQuery); + + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.sports <=> mq2.sports"), + hqlQuery); + } + + @Test + public void testBridgeTablesWithExpressionBeforeFlattening() throws Exception { + String query = "select substr(usersports.name, 3), sum(msr2) from basecube where " + TWO_DAYS_RANGE + + " and usersports.name = 'CRICKET'"; + String hqlQuery = rewrite(query, hConf); + String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(substr(usersports.name, 3)) as balias0" + + " collect_set(( usersports . name )) as balias1 from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id " + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", + null, " and array_contains(usersports.balias1, 'CRICKET') group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports_abbr, sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET'"; + hqlQuery = rewrite(query, hConf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testBridgeTablesWithExpressionAfterFlattening() throws Exception { + Configuration conf = new Configuration(hConf); + conf.setBoolean(CubeQueryConfUtil.DO_FLATTENING_OF_BRIDGE_TABLE_EARLY, true); + String query = "select substr(usersports.name, 3), sum(msr2) from basecube where " + TWO_DAYS_RANGE + + " and usersports.name = 'CRICKET,FOOTBALL'"; + String hqlQuery = rewrite(query, conf); + String expected = getExpectedQuery("basecube", "select substr(usersports.name, 3), sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by substr(usersports.name, 3)", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports_abbr, sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET,FOOTBALL'"; + hqlQuery = rewrite(query, conf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testBridgeTablesWithExpressionAndAliasesBeforeFlattening() throws Exception { + String query = "select userid as uid, usersports.name as uname, substr(usersports.name, 3) as `sub user`," + + " sum(msr2) from basecube where " + TWO_DAYS_RANGE + + " and usersports.name = 'CRICKET' and substr(usersports.name, 3) = 'CRI' and (userid = 4 or userid = 5)"; + String hqlQuery = rewrite(query, hConf); + String expected = getExpectedQuery("basecube", "select basecube.userid as `uid`, usersports.balias0 as `uname`, " + + " (usersports.balias1) as `sub user`, sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id, collect_set(usersports.name) as balias0, " + + "collect_set(substr(usersports.name, 3)) as balias1" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id " + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", + null, " and array_contains(usersports.balias0,'CRICKET') and (array_contains(usersports.balias1),'CRI')" + + " and ((basecube.userid) = 4) or (( basecube . userid ) = 5 )) " + + "group by basecube.userid, usersports.balias0, usersports.balias1", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select userid as uid, sports as uname, sports_abbr as `sub user`, sum(msr2) from basecube where " + + TWO_DAYS_RANGE + " and sports = 'CRICKET' and sports_abbr = 'CRI' and (userid = 4 or userid = 5)"; + hqlQuery = rewrite(query, hConf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testBridgeTablesWithExpressionAndAliasesAfterFlattening() throws Exception { + Configuration conf = new Configuration(hConf); + conf.setBoolean(CubeQueryConfUtil.DO_FLATTENING_OF_BRIDGE_TABLE_EARLY, true); + String query = "select usersports.name as uname, substr(usersports.name, 3) as `sub user`," + + " sum(msr2) from basecube where " + TWO_DAYS_RANGE + + " and usersports.name = 'CRICKET,FOOTBALL'"; + String hqlQuery = rewrite(query, conf); + String expected = getExpectedQuery("basecube", "select usersports.name as `uname`, substr(usersports.name, 3) as " + + "`sub user`, sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by usersports.name, substr(usersports.name, 3)", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports as uname, sports_abbr as `sub user`, sum(msr2) from basecube where " + TWO_DAYS_RANGE + + " and sports = 'CRICKET,FOOTBALL'"; + hqlQuery = rewrite(query, conf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + @Test + public void testBridgeTablesWithMultipleFactsWithExprBeforeFlattening() throws Exception { + String query = "select substr(usersports.name, 3), sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + + " and usersports.name in ('CRICKET', 'FOOTBALL')"; + String hqlQuery = rewrite(query, hConf); + String expected1 = getExpectedQuery("basecube", + "select usersports.balias0 as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id, collect_set(substr(usersports.name, 3)) as balias0, " + + " collect_set(usersports.name) as balias1 from" + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports on userdim.id = usersports.user_id ", null, + " and ( array_contains(usersports.balias1,'CRICKET') OR array_contains(usersports.balias1,'FOOTBALL')" + + " group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + String expected2 = getExpectedQuery("basecube", + "select usersports.balias0 as `name`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id, collect_set(substr(usersports.name, 3)) as balias0, " + + " collect_set(usersports.name) as balias1 from" + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports on userdim.id = usersports.user_id ", null, + " and ( array_contains(usersports.balias1,'CRICKET') OR array_contains(usersports.balias1,'FOOTBALL')" + + " group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); + TestCubeRewriter.compareContains(expected1, hqlQuery); + TestCubeRewriter.compareContains(expected2, hqlQuery); + String lower = hqlQuery.toLowerCase(); + assertTrue(lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq2.msr2 msr2, mq1.msr12 msr12 from ") + || lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq1.msr2 msr2, mq2.msr12 msr12 from "), hqlQuery); + + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), + hqlQuery); + // run with chain ref column + query = "select sports_abbr, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + " and sports in " + + "('CRICKET', 'FOOTBALL')"; + hqlQuery = rewrite(query, hConf); + expected1 = getExpectedQuery("basecube", + "select usersports.balias0 as `sports_abbr`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id, collect_set(substr((usersports.name), 3)) as balias0, " + + " collect_set(usersports.name) as balias1 from" + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports on userdim.id = usersports.user_id ", null, + " and ( array_contains(usersports.balias1,'CRICKET') OR array_contains(usersports.balias1,'FOOTBALL')" + + " group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + expected2 = getExpectedQuery("basecube", + "select usersports.balias0 as `sports_abbr`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id, collect_set(substr((usersports.name), 3)) as balias0," + + " collect_set(usersports.name) as balias1 from" + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports on userdim.id = usersports.user_id ", null, + " and ( array_contains(usersports.balias1,'CRICKET') OR array_contains(usersports.balias1,'FOOTBALL')" + + " group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); + TestCubeRewriter.compareContains(expected1, hqlQuery); + TestCubeRewriter.compareContains(expected2, hqlQuery); + lower = hqlQuery.toLowerCase(); + assertTrue(lower.startsWith( + "select coalesce(mq1.sports_abbr, mq2.sports_abbr) sports_abbr, mq2.msr2 msr2, mq1.msr12 msr12 from ") + || lower.startsWith( + "select coalesce(mq1.sports_abbr, mq2.sports_abbr) sports_abbr, mq1.msr2 msr2, mq2.msr12 msr12 from "), + hqlQuery); + + assertTrue(hqlQuery.contains("mq1 full outer join ") + && hqlQuery.endsWith("mq2 on mq1.sports_abbr <=> mq2.sports_abbr"), + hqlQuery); + } + + @Test + public void testBridgeTablesWithMultipleFactsWithExprAfterFlattening() throws Exception { + Configuration conf = new Configuration(hConf); + conf.setBoolean(CubeQueryConfUtil.DO_FLATTENING_OF_BRIDGE_TABLE_EARLY, true); + String query = "select substr(usersports.name, 3), sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + + " and usersports.name = 'CRICKET,FOOTBALL'"; + String hqlQuery = rewrite(query, conf); + String expected1 = getExpectedQuery("basecube", + "select substr(usersports.name, 3) as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by substr(usersports.name, 3)", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + String expected2 = getExpectedQuery("basecube", + "select substr(usersports.name, 3) as `name`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by substr(usersports.name, 3)", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); + TestCubeRewriter.compareContains(expected1, hqlQuery); + TestCubeRewriter.compareContains(expected2, hqlQuery); + String lower = hqlQuery.toLowerCase(); + assertTrue(lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq2.msr2 msr2, mq1.msr12 msr12 from ") + || lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq1.msr2 msr2, mq2.msr12 msr12 from "), hqlQuery); + + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), + hqlQuery); + // run with chain ref column + query = "select sports_abbr, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + " and sports = " + + "'CRICKET,FOOTBALL'"; + hqlQuery = rewrite(query, conf); + expected1 = getExpectedQuery("basecube", + "select substr(usersports.name, 3) as `sports_abbr`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by substr(usersports.name, 3)", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + expected2 = getExpectedQuery("basecube", + "select substr(usersports.name, 3) as `sports_abbr`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by substr(usersports.name, 3)", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); + TestCubeRewriter.compareContains(expected1, hqlQuery); + TestCubeRewriter.compareContains(expected2, hqlQuery); + lower = hqlQuery.toLowerCase(); + assertTrue(lower.startsWith( + "select coalesce(mq1.sports_abbr, mq2.sports_abbr) sports_abbr, mq2.msr2 msr2, mq1.msr12 msr12 from ") + || lower.startsWith( + "select coalesce(mq1.sports_abbr, mq2.sports_abbr) sports_abbr, mq1.msr2 msr2, mq2.msr12 msr12 from "), + hqlQuery); + + assertTrue(hqlQuery.contains("mq1 full outer join ") + && hqlQuery.endsWith("mq2 on mq1.sports_abbr <=> mq2.sports_abbr"), + hqlQuery); + } + + @Test + public void testBridgeTablesDimensionOnlyQuery() throws Exception { + Configuration conf = new Configuration(hConf); + conf.set(CubeQueryConfUtil.getValidFactTablesKey("basecube"), "testFact1_base"); + String query = "select userid as uid, usersports.name as uname, substr(usersports.name, 3) as `sub user`" + + " from basecube where " + TWO_DAYS_RANGE + + " and usersports.name = 'CRICKET' and substr(usersports.name, 3) = 'CRI' and (userid = 4 or userid = 5)"; + String hqlQuery = rewrite(query, conf); + String expected = getExpectedQuery("basecube", "select distinct basecube.userid as `uid`, usersports.balias0 as " + + "`uname`, (usersports.balias1) as `sub user` FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id, collect_set(usersports.name) as balias0, " + + "collect_set(substr(usersports.name, 3)) as balias1" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id " + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", + null, " and array_contains(usersports.balias0,'CRICKET') and (array_contains(usersports.balias1),'CRI')" + + " and ((basecube.userid) = 4) or (( basecube . userid ) = 5 )) ", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select userid as uid, sports as uname, sports_abbr as `sub user` from basecube where " + + TWO_DAYS_RANGE + " and sports = 'CRICKET' and sports_abbr = 'CRI' and (userid = 4 or userid = 5)"; + hqlQuery = rewrite(query, conf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testBridgeTableQueryJoinColumns() throws Exception { + Configuration conf = new Configuration(hConf); + conf.set(CubeQueryConfUtil.getValidFactTablesKey("basecube"), "testFact1_base"); + String query = "select userid as uid, userchain.id as udid, userInterestIds.sport_id as uisid, " + + "userInterestIds.user_id as uiuid, usersports.id as usid" + + " from basecube where " + TWO_DAYS_RANGE; + String hqlQuery = rewrite(query, conf); + String expected = getExpectedQuery("basecube", "select distinct basecube.userid as `uid`," + + "userchain.id as `udid`, userinterestids.balias0 as `uisid`, userinterestids.balias1 as " + + "`uiuid`, usersports . balias0 as `usid` FROM ", + " join " + getDbName() + "c1_usertable userchain ON basecube.userid = userchain.id " + + " join ( select userinterestids.user_id as user_id, collect_set(userinterestids.sport_id) as balias0," + + " collect_set(userinterestids.user_id) as balias1 from " + getDbName() + "c1_user_interests_tbl " + + " userinterestids group by userinterestids.user_id) userinterestids on userchain.id = userinterestids.user_id" + + " join (select userinterestids.user_id as user_id, collect_set(usersports . id) as balias0 from" + + getDbName() + " c1_user_interests_tbl userinterestids join " + getDbName() + "c1_sports_tbl" + + " usersports on userinterestids.sport_id = usersports.id group by userinterestids.user_id) usersports" + + " on userchain.id = usersports.user_id ", + null, null, null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } }
http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-cube/src/test/java/org/apache/lens/cube/parse/TestDefaultAliasDecider.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestDefaultAliasDecider.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestDefaultAliasDecider.java new file mode 100644 index 0000000..9310c4e --- /dev/null +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestDefaultAliasDecider.java @@ -0,0 +1,53 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.lens.cube.parse; + +import static org.testng.Assert.assertEquals; + +import org.apache.hadoop.hive.ql.parse.ASTNode; + +import org.testng.annotations.Test; + +public class TestDefaultAliasDecider { + + @Test + public void testDefaultAlias() throws Exception { + DefaultAliasDecider aliasDecider = new DefaultAliasDecider(); + ASTNode node = HQLParser.parseExpr("tbl1.col1"); + assertEquals(aliasDecider.decideAlias(node), "alias0"); + assertEquals(aliasDecider.decideAlias(node), "alias1"); + DefaultAliasDecider aliasDecider2 = new DefaultAliasDecider(); + assertEquals(aliasDecider2.decideAlias(node), "alias0"); + assertEquals(aliasDecider.decideAlias(node), "alias2"); + } + + @Test + public void testAliasPrefix() throws Exception { + DefaultAliasDecider aliasDecider = new DefaultAliasDecider("testAlias"); + ASTNode node = HQLParser.parseExpr("tbl1.col1"); + assertEquals(aliasDecider.decideAlias(node), "testAlias0"); + assertEquals(aliasDecider.decideAlias(node), "testAlias1"); + DefaultAliasDecider aliasDecider2 = new DefaultAliasDecider("testAlias"); + DefaultAliasDecider aliasDecider3 = new DefaultAliasDecider(); + assertEquals(aliasDecider2.decideAlias(node), "testAlias0"); + assertEquals(aliasDecider3.decideAlias(node), "alias0"); + assertEquals(aliasDecider.decideAlias(node), "testAlias2"); + } +} http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-cube/src/test/java/org/apache/lens/cube/parse/TestHQLParser.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestHQLParser.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestHQLParser.java index f9d7457..590d81b 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestHQLParser.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestHQLParser.java @@ -21,6 +21,10 @@ package org.apache.lens.cube.parse; import static org.apache.hadoop.hive.ql.parse.HiveParser.*; +import java.util.Arrays; +import java.util.HashSet; +import java.util.Set; + import org.apache.lens.server.api.error.LensException; import org.apache.hadoop.hive.conf.HiveConf; @@ -382,4 +386,82 @@ public class TestHQLParser { String infixString = HQLParser.getString(tree); Assert.assertEquals(infixString, expected); } + + @DataProvider + public Object[][] colsInExpr() { + return new Object[][] { + {" t1.c1", new String[]{}}, // simple selection + {" cie.c5", new String[]{"c5"}}, // simple selection + {" fun1(cie.c4)", new String[]{"c4"}}, // simple selection + {" t1.c1 + cie.c5+ t2.c3", new String[]{"c5"}}, // simple selection + {" t1.c1=x and cie.c2=y", new String[]{"c2"}}, //filter expression + {"case when t1.c1 then 1 when cie.c3 then 2 when cie.c4 then 3 when t2.c2 then 4 else cie.c6 end", + new String[]{"c3", "c4", "c6", }, }, // case when statement + {"complexfunc(round(t1.c1), myfunc(t2.c2), myfunc2(cie.c4, cie.c5, t2.c6))", new String[]{"c4", "c5"}}, + }; + } + + @Test(dataProvider = "colsInExpr") + public void testColsInExpr(String input, String[] expected) throws Exception { + String tableAlias = "cie"; + ASTNode inputAST = HQLParser.parseExpr(input); + Set<String> actual = HQLParser.getColsInExpr(tableAlias, inputAST); + Set<String> expectedSet = new HashSet<>(Arrays.asList(expected)); + Assert.assertEquals(actual, expectedSet, "Received " + actual + " for input:" + input); + } + + @Test + public void testGetDotAST() { + Assert.assertEquals(HQLParser.getString(HQLParser.getDotAST("tbl1", "col1")), "( tbl1 . col1 )"); + } + + @DataProvider + public Object[][] primitiveBool() { + return new Object[][] { + {" t1.c1", false}, + {" t1.c1 = 24", true}, + {" t1.c1 >= 24", true}, + {" t1.c1 <= 24", true}, + {" t1.c1 <=> 24", true}, + {" t1.c1 != 24", true}, + {" t1.c1 < 24", true}, + {" t1.c1 > 24", true}, + {" fun1(cie.c4)", false}, + {" arraycontains(arr1, v1)", false}, + {" t1.c1=x and cie.c2=y", false}, + {" t1.col1 in ('x', 'y', 'z')", false}, + }; + } + + @Test(dataProvider = "primitiveBool") + public void testIsPrimitiveBooleanExpr(String input, boolean expected) throws Exception { + ASTNode inputAST = HQLParser.parseExpr(input); + boolean actual = HQLParser.isPrimitiveBooleanExpression(inputAST); + Assert.assertEquals(actual, expected, "Received " + actual + " for input:" + input + ":" + inputAST.dump()); + } + + @DataProvider + public Object[][] primitiveBoolFunc() { + return new Object[][] { + {" t1.c1", false}, + {" t1.c1 = 24", false}, + {" t1.c1 >= 24", false}, + {" t1.c1 <= 24", false}, + {" t1.c1 <=> 24", false}, + {" t1.c1 != 24", false}, + {" t1.c1 < 24", false}, + {" t1.c1 > 24", false}, + {" fun1(cie.c4)", false}, + {" arraycontains(arr1, v1)", false}, + {" t1.c1=x and cie.c2=y", false}, + {" t1.col1 in ('x', 'y', 'z')", true}, + }; + } + + @Test(dataProvider = "primitiveBoolFunc") + public void testIsPrimitiveBooleanFunction(String input, boolean expected) throws Exception { + ASTNode inputAST = HQLParser.parseExpr(input); + boolean actual = HQLParser.isPrimitiveBooleanFunction(inputAST); + Assert.assertEquals(actual, expected, "Received " + actual + " for input:" + input); + } } http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-cube/src/test/java/org/apache/lens/cube/parse/TestQuery.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestQuery.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestQuery.java index cd20fef..b50ebd2 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestQuery.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestQuery.java @@ -43,7 +43,7 @@ public class TestQuery { private static HiveConf conf = new HiveConf(); private ASTNode ast; - private String actualQuery; + private String query; private String joinQueryPart = null; private String trimmedQuery = null; @@ -73,12 +73,12 @@ public class TestQuery { } public TestQuery(String query) { - this.actualQuery = query; + this.query = query; } public ASTNode getAST() throws LensException { if (this.ast == null) { - ast = HQLParser.parseHQL(this.actualQuery, conf); + ast = HQLParser.parseHQL(this.query, conf); } return ast; } @@ -86,7 +86,7 @@ public class TestQuery { public void processQueryAsString() { if (!processed) { processed = true; - this.trimmedQuery = getTrimmedQuery(actualQuery); + this.trimmedQuery = getTrimmedQuery(query); this.joinQueryPart = extractJoinStringFromQuery(trimmedQuery); /** * Get the join query part, pre-join query and post-join query part from the trimmed query. @@ -218,11 +218,11 @@ public class TestQuery { if (this == expected) { return true; } - if (this.actualQuery == null && expected.actualQuery == null) { + if (this.query == null && expected.query == null) { return true; - } else if (this.actualQuery == null) { + } else if (this.query == null) { return false; - } else if (expected.actualQuery == null) { + } else if (expected.query == null) { return false; } boolean equals = false; @@ -246,10 +246,10 @@ public class TestQuery { @Override public int hashCode() { - return Objects.hashCode(actualQuery, joinQueryPart, trimmedQuery, joinTypeStrings); + return Objects.hashCode(query, joinQueryPart, trimmedQuery, joinTypeStrings); } public String toString() { - return "Actual Query: " + actualQuery + "\n" + "JoinQueryString: " + joinTypeStrings; + return "Query: " + query + "\n" + "JoinQueryString: " + joinTypeStrings; } } http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-cube/src/test/java/org/apache/lens/cube/parse/join/TestBridgeTableJoinCtx.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/join/TestBridgeTableJoinCtx.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/join/TestBridgeTableJoinCtx.java new file mode 100644 index 0000000..06b9509 --- /dev/null +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/join/TestBridgeTableJoinCtx.java @@ -0,0 +1,122 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.lens.cube.parse.join; + +import static org.apache.hadoop.hive.ql.parse.HiveParser.TOK_INSERT; +import static org.apache.hadoop.hive.ql.parse.HiveParser.TOK_SELECT; +import static org.apache.hadoop.hive.ql.parse.HiveParser.TOK_WHERE; + +import static org.testng.Assert.*; + +import java.util.ArrayList; +import java.util.List; + +import org.apache.lens.cube.parse.HQLParser; +import org.apache.lens.cube.parse.join.BridgeTableJoinContext.BridgeTableSelectCtx; +import org.apache.lens.server.api.error.LensException; + +import org.apache.hadoop.hive.conf.HiveConf; +import org.apache.hadoop.hive.ql.parse.ASTNode; +import org.apache.hadoop.hive.ql.parse.HiveParser; + +import org.testng.annotations.DataProvider; +import org.testng.annotations.Test; + +public class TestBridgeTableJoinCtx { + + @DataProvider(name = "filterReplace") + public Object[][] filterReplace() { + return new Object[][] { + {"t1.c1 in ('XyZ', 'abc', 'PQR', 'lKg')", + "myfilter(( t1 . c1 ), 'XyZ' ) or myfilter(( t1 . c1 ), 'abc' )" + + " or myfilter(( t1 . c1 ), 'PQR' ) or myfilter(( t1 . c1 ), 'lKg' )", }, + {"t1.c1 = ('XyZ')", "myfilter(( t1 . c1 ), 'XyZ' )"}, + {"t1.c1 != ('XyZ')", "not myfilter(( t1 . c1 ), 'XyZ' )"}, + {"t1.c1 != x", "not myfilter(( t1 . c1 ), x )"}, + }; + } + + @Test(dataProvider = "filterReplace") + public void testReplaceDirectFiltersWithArrayFilter(String filter, String expected) throws LensException { + ASTNode replaced = BridgeTableJoinContext.replaceDirectFiltersWithArrayFilter(HQLParser.parseExpr(filter), + "myfilter"); + String replacedFilter = HQLParser.getString(replaced); + assertEquals(replacedFilter, expected); + } + + @Test + public void testBridgeTableSelectCtx() throws LensException { + String aggregator = "test_aggr"; + String arrayFilter = "test_filter"; + String bridgeTableAlias = "bt"; + + // Initialization + BridgeTableSelectCtx selectCtx = new BridgeTableSelectCtx(aggregator, arrayFilter, bridgeTableAlias); + assertTrue(selectCtx.getSelectedBridgeExprs().isEmpty()); + assertEquals(selectCtx.getTableAlias(), bridgeTableAlias); + assertEquals(selectCtx.getBridgeTableFieldAggr(), aggregator); + assertEquals(selectCtx.getArrayFilter(), arrayFilter); + assertNotNull(selectCtx.getAliasDecider()); + assertTrue(selectCtx.getExprToDotAST().isEmpty()); + + String query = "select t1.c1, t2.c2, bt.c3, f1(t1.c2), f2(bt.c4), f2(bt.c3), bt.c1 + bt.c2 from t1 where t1.c1 = x" + + " and bt.c3 = y and bt.c6 = 5 and t2.c2 = 4 and rand(bt.c7) = 6 group by t1.c1, bt.c3, bt.c8, f2(bt.c4)" + + " order by t2.c2, bt.c3 asc, f2(bt.c3), bt.c9, bt.c4 desc"; + ASTNode queryAST = HQLParser.parseHQL(query, new HiveConf()); + ASTNode select = HQLParser.findNodeByPath(queryAST, TOK_INSERT, TOK_SELECT); + ASTNode where = HQLParser.findNodeByPath(queryAST, TOK_INSERT, TOK_WHERE); + ASTNode groupBy = HQLParser.findNodeByPath(queryAST, TOK_INSERT, HiveParser.TOK_GROUPBY); + ASTNode orderBy = HQLParser.findNodeByPath(queryAST, TOK_INSERT, HiveParser.TOK_ORDERBY); + + List<String> expectedBridgeExprs = new ArrayList<>(); + expectedBridgeExprs.add(aggregator + "(( bt . c3 )) as balias0"); + expectedBridgeExprs.add(aggregator + "(f2(( bt . c4 ))) as balias1"); + expectedBridgeExprs.add(aggregator + "(f2(( bt . c3 ))) as balias2"); + expectedBridgeExprs.add(aggregator + "((( bt . c1 ) + ( bt . c2 ))) as balias3"); + + selectCtx.processSelectAST(select); + String modifiedSelect = HQLParser.getString(select); + assertEquals(modifiedSelect, "( t1 . c1 ), ( t2 . c2 ), ( bt . balias0 ), f1(( t1 . c2 )), ( bt . balias1 )," + + " ( bt . balias2 ), ( bt . balias3 )"); + assertEquals(selectCtx.getSelectedBridgeExprs(), expectedBridgeExprs); + + selectCtx.processWhereAST(where, null, 0); + String modifiedWhere = HQLParser.getString(where); + assertEquals(modifiedWhere, "((( t1 . c1 ) = x ) and test_filter(( bt . balias0 ), y ) and test_filter(( bt . " + + "balias4 ), 5 ) and (( t2 . c2 ) = 4 ) and test_filter(( bt . balias5 ), 6 ))"); + expectedBridgeExprs.add(aggregator + "(( bt . c6 )) as balias4"); + expectedBridgeExprs.add(aggregator + "(rand(( bt . c7 ))) as balias5"); + assertEquals(selectCtx.getSelectedBridgeExprs(), expectedBridgeExprs); + + selectCtx.processGroupbyAST(groupBy); + String modifiedGroupby = HQLParser.getString(groupBy); + assertEquals(modifiedGroupby, "( t1 . c1 ), ( bt . balias0 ), ( bt . balias6 ), ( bt . balias1 )"); + expectedBridgeExprs.add(aggregator + "(( bt . c8 )) as balias6"); + assertEquals(selectCtx.getSelectedBridgeExprs(), expectedBridgeExprs); + + selectCtx.processOrderbyAST(orderBy); + String modifiedOrderby = HQLParser.getString(orderBy); + assertEquals(modifiedOrderby, "t2 . c2 asc , bt . balias0 asc , bt . balias2 asc , bt . balias7 asc ," + + " bt . balias8 desc"); + expectedBridgeExprs.add(aggregator + "(( bt . c9 )) as balias7"); + expectedBridgeExprs.add(aggregator + "(( bt . c4 )) as balias8"); + assertEquals(selectCtx.getSelectedBridgeExprs(), expectedBridgeExprs); + } +} http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-dist/src/main/assembly/bin-dist.xml ---------------------------------------------------------------------- diff --git a/lens-dist/src/main/assembly/bin-dist.xml b/lens-dist/src/main/assembly/bin-dist.xml index dede085..eda1094 100644 --- a/lens-dist/src/main/assembly/bin-dist.xml +++ b/lens-dist/src/main/assembly/bin-dist.xml @@ -211,6 +211,22 @@ <fileSet> <directory>../lens-examples/src/main/resources/</directory> + <outputDirectory>/client/examples/data/customer_interests_local</outputDirectory> + <includes> + <include>customer-interests-local.data</include> + </includes> + </fileSet> + + <fileSet> + <directory>../lens-examples/src/main/resources/</directory> + <outputDirectory>/client/examples/data/interests_local</outputDirectory> + <includes> + <include>interests-local.data</include> + </includes> + </fileSet> + + <fileSet> + <directory>../lens-examples/src/main/resources/</directory> <outputDirectory>/client/examples/data/city_local</outputDirectory> <includes> <include>city-local.data</include> http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-examples/src/main/java/org/apache/lens/examples/PopulateSampleMetastore.java ---------------------------------------------------------------------- diff --git a/lens-examples/src/main/java/org/apache/lens/examples/PopulateSampleMetastore.java b/lens-examples/src/main/java/org/apache/lens/examples/PopulateSampleMetastore.java index cfe3465..4fc15a6 100644 --- a/lens-examples/src/main/java/org/apache/lens/examples/PopulateSampleMetastore.java +++ b/lens-examples/src/main/java/org/apache/lens/examples/PopulateSampleMetastore.java @@ -91,6 +91,8 @@ public class PopulateSampleMetastore { createDimTablePartitions("product-local-parts.xml", "product_table", "local"); createDimTablePartition("city-local-part.xml", "city_table", "local"); createDimTablePartition("customer-local-part.xml", "customer_table", "local"); + createDimTablePartition("customer-interests-local-part.xml", "customer_interests_table", "local"); + createDimTablePartition("interests-local-part.xml", "interests_table", "local"); } private void createDimTablePartition(String fileName, String dimTable, String storage) http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-examples/src/main/java/org/apache/lens/examples/SampleMetastore.java ---------------------------------------------------------------------- diff --git a/lens-examples/src/main/java/org/apache/lens/examples/SampleMetastore.java b/lens-examples/src/main/java/org/apache/lens/examples/SampleMetastore.java index 626655d..a04a67f 100644 --- a/lens-examples/src/main/java/org/apache/lens/examples/SampleMetastore.java +++ b/lens-examples/src/main/java/org/apache/lens/examples/SampleMetastore.java @@ -96,6 +96,8 @@ public class SampleMetastore { createDimension("city.xml"); createDimension("customer.xml"); createDimension("product.xml"); + createDimension("customer-interests.xml"); + createDimension("interests.xml"); } private void createStorage(String fileName) throws JAXBException, IOException { @@ -147,6 +149,8 @@ public class SampleMetastore { createDimTable("product_table.xml"); createDimTable("product_db_table.xml"); createDimTable("customer_table.xml"); + createDimTable("customer_interests_table.xml"); + createDimTable("interests_table.xml"); } private void createFact(String factSpec) { http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-examples/src/main/resources/cube-queries.sql ---------------------------------------------------------------------- diff --git a/lens-examples/src/main/resources/cube-queries.sql b/lens-examples/src/main/resources/cube-queries.sql index 9f4a353..0169175 100644 --- a/lens-examples/src/main/resources/cube-queries.sql +++ b/lens-examples/src/main/resources/cube-queries.sql @@ -105,4 +105,8 @@ cube select product_details.color, store_sales from sales where time_range_in(de cube select product_details.color, store_sales from sales where time_range_in(delivery_time, '2015-04-12-00', '2015-04-13-00') and product_details.category='Stationary' cube select store_sales from sales where time_range_in(order_time, 'now.second-2 days', 'now.second') -- The following query sees that ot part col is not present and falls back on querying on dt part col -cube select customer_city_name, store_cost from sales where time_range_in(order_time, '2015-04-13-03', '2015-04-13-04') -- \ No newline at end of file +cube select customer_city_name, store_cost from sales where time_range_in(order_time, '2015-04-13-03', '2015-04-13-04') +-- The following queries are for illustrating flattening of bridge table fields. +select customer_id, customer_interest, unit_sales from sales where time_range_in(order_time, '2015-04-11-00', '2015-04-13-00') +select customer_id, customer_interest, unit_sales from sales where time_range_in(order_time, '2015-04-11-00', '2015-04-13-00') and customer_interest not in ('Food') +select customer_id, customer_interest, unit_sales from sales where time_range_in(order_time, '2015-04-11-00', '2015-04-13-00') and customer_interest in ('Food') http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-examples/src/main/resources/customer-interests-local-part.xml ---------------------------------------------------------------------- diff --git a/lens-examples/src/main/resources/customer-interests-local-part.xml b/lens-examples/src/main/resources/customer-interests-local-part.xml new file mode 100644 index 0000000..b2e8a97 --- /dev/null +++ b/lens-examples/src/main/resources/customer-interests-local-part.xml @@ -0,0 +1,30 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. + +--> +<x_partition fact_or_dimension_table_name="customer_interests_table" + location="examples/data/customer_interests_local" + update_period="HOURLY" + xmlns="uri:lens:cube:0.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; + xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd "> + <time_partition_spec> + <part_spec_element key="dt" value="2016-03-16T12:00:00"/> + </time_partition_spec> +</x_partition> http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-examples/src/main/resources/customer-interests-local.data ---------------------------------------------------------------------- diff --git a/lens-examples/src/main/resources/customer-interests-local.data b/lens-examples/src/main/resources/customer-interests-local.data new file mode 100644 index 0000000..35e4e45 --- /dev/null +++ b/lens-examples/src/main/resources/customer-interests-local.data @@ -0,0 +1,6 @@ +1,1 +1,2 +3,1 +4,1 +4,2 +4,3 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-examples/src/main/resources/customer-interests.xml ---------------------------------------------------------------------- diff --git a/lens-examples/src/main/resources/customer-interests.xml b/lens-examples/src/main/resources/customer-interests.xml new file mode 100644 index 0000000..7a2e2b0 --- /dev/null +++ b/lens-examples/src/main/resources/customer-interests.xml @@ -0,0 +1,31 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. + +--> +<x_dimension name="customer_interests" xmlns="uri:lens:cube:0.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; + xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd "> + <attributes> + <dim_attribute name="customer_id" _type="INT"/> + <dim_attribute name="interest_id" _type="INT"/> + </attributes> + <properties> + <property name="dimension.customer_interests.timed.dimension" value="dt"/> + </properties> +</x_dimension> http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-examples/src/main/resources/customer_interests_table.xml ---------------------------------------------------------------------- diff --git a/lens-examples/src/main/resources/customer_interests_table.xml b/lens-examples/src/main/resources/customer_interests_table.xml new file mode 100644 index 0000000..de11a9f --- /dev/null +++ b/lens-examples/src/main/resources/customer_interests_table.xml @@ -0,0 +1,52 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. + +--> +<x_dimension_table dimension_name="customer_interests" table_name="customer_interests_table" weight="100.0" + xmlns="uri:lens:cube:0.1" + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd "> + <columns> + <column comment="ID" name="customer_id" _type="INT"/> + <column comment="ID" name="interest_id" _type="INT"/> + </columns> + <storage_tables> + <storage_table> + <update_periods> + <update_period>HOURLY</update_period> + </update_periods> + <storage_name>local</storage_name> + <table_desc external="true" field_delimiter="," table_location="/tmp/examples/customer_interests"> + <part_cols> + <column comment="Time column" name="dt" _type="STRING"/> + </part_cols> + <time_part_cols>dt</time_part_cols> + </table_desc> + </storage_table> + <storage_table> + <storage_name>mydb</storage_name> + <table_desc external="true" table_location="/tmp/db-storage.db" + storage_handler_name="org.apache.lens.storage.db.DBStorageHandler"> + <table_parameters> + <property name="lens.metastore.native.db.name" value="default"/> + </table_parameters> + </table_desc> + </storage_table> + </storage_tables> +</x_dimension_table> http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-examples/src/main/resources/db-storage-schema.sql ---------------------------------------------------------------------- diff --git a/lens-examples/src/main/resources/db-storage-schema.sql b/lens-examples/src/main/resources/db-storage-schema.sql index 0aceeab..9621459 100644 --- a/lens-examples/src/main/resources/db-storage-schema.sql +++ b/lens-examples/src/main/resources/db-storage-schema.sql @@ -72,3 +72,21 @@ INSERT INTO mydb_city_subset (id, name) values (1, 'Bangalore') INSERT INTO mydb_city_subset (id, name) values (2, 'Hyderabad') INSERT INTO mydb_city_subset (id, name) values (3, 'Austin') INSERT INTO mydb_city_subset (id, name) values (4, 'San Fransisco') + +DROP TABLE IF EXISTS mydb_customer_interests_table +CREATE TABLE mydb_customer_interests_table (customer_id integer, interest_id integer) + +INSERT INTO mydb_customer_interests_table (customer_id, interest_id) values (1,1) +INSERT INTO mydb_customer_interests_table (customer_id, interest_id) values (1,2) +INSERT INTO mydb_customer_interests_table (customer_id, interest_id) values (3,1) +INSERT INTO mydb_customer_interests_table (customer_id, interest_id) values (4,1) +INSERT INTO mydb_customer_interests_table (customer_id, interest_id) values (4,1) +INSERT INTO mydb_customer_interests_table (customer_id, interest_id) values (4,3) + +DROP TABLE IF EXISTS mydb_interests_table +CREATE TABLE mydb_interests_table (id integer, name varchar(255)) + +INSERT INTO mydb_interests_table (id, name) values (1,'Food') +INSERT INTO mydb_interests_table (id, name) values (2,'Fashion') +INSERT INTO mydb_interests_table (id, name) values (3,'Furniture') +INSERT INTO mydb_interests_table (id, name) values (4,'Electronics') http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-examples/src/main/resources/interests-local-part.xml ---------------------------------------------------------------------- diff --git a/lens-examples/src/main/resources/interests-local-part.xml b/lens-examples/src/main/resources/interests-local-part.xml new file mode 100644 index 0000000..5f6b721 --- /dev/null +++ b/lens-examples/src/main/resources/interests-local-part.xml @@ -0,0 +1,29 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. + +--> +<x_partition fact_or_dimension_table_name="interests_table" location="examples/data/interests_local" + update_period="HOURLY" + xmlns="uri:lens:cube:0.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; + xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd "> + <time_partition_spec> + <part_spec_element key="dt" value="2016-03-16T12:00:00"/> + </time_partition_spec> +</x_partition> http://git-wip-us.apache.org/repos/asf/lens/blob/dba885ca/lens-examples/src/main/resources/interests-local.data ---------------------------------------------------------------------- diff --git a/lens-examples/src/main/resources/interests-local.data b/lens-examples/src/main/resources/interests-local.data new file mode 100644 index 0000000..493a28e --- /dev/null +++ b/lens-examples/src/main/resources/interests-local.data @@ -0,0 +1,4 @@ +1,Food +2,Fashion +3,Furniture +4,Electronics \ No newline at end of file
