use of org.apache.calcite.sql.dialect.SparkSqlDialect in project hopsworks by logicalclocks.
the class TestPitJoinController method testGenerateSqlWithFilter.
@Test
public void testGenerateSqlWithFilter() {
List<Feature> leftFeatures = new ArrayList<>();
leftFeatures.add(new Feature("pk1", "fg0", fgLeft, true));
leftFeatures.add(new Feature("pk2", "fg0", fgLeft));
leftFeatures.add(new Feature("ts", "fg0", fgLeft));
leftFeatures.add(new Feature("label", "fg0", fgLeft));
List<Feature> rightFeatures = new ArrayList<>();
rightFeatures.add(new Feature("pk1", "fg1", fgRight));
rightFeatures.add(new Feature("pk2", "fg1", fgRight));
rightFeatures.add(new Feature("ts", "fg1", fgRight));
rightFeatures.add(new Feature("ft1", "fg1", fgRight));
List<Feature> rightFeatures1 = new ArrayList<>();
rightFeatures1.add(new Feature("pk1", "fg2", fgRight1));
rightFeatures1.add(new Feature("ts", "fg2", fgRight1));
rightFeatures1.add(new Feature("ft1", "fg2", fgRight1));
List<Feature> leftOn = Arrays.asList(new Feature("pk1", "fg0", fgLeft), new Feature("pk2", "fg0", fgLeft));
List<Feature> rightOn = Arrays.asList(new Feature("pk1", "fg1", fgRight), new Feature("pk2", "fg1", fgRight));
// join on different pks
List<Feature> leftOn1 = Collections.singletonList(new Feature("pk1", "fg0", fgLeft));
List<Feature> rightOn1 = Collections.singletonList(new Feature("pk1", "fg2", fgRight1));
List<SqlCondition> joinOperator = Arrays.asList(SqlCondition.EQUALS, SqlCondition.EQUALS);
List<SqlCondition> joinOperator1 = Collections.singletonList(SqlCondition.EQUALS);
FilterLogic filter = new FilterLogic(new Filter(Arrays.asList(new Feature("label", "fg0", "int", null, null)), SqlCondition.EQUALS, "1"));
Query query = new Query("fs", "project", fgLeft, "fg0", leftFeatures, leftFeatures, false, filter);
Query right = new Query("fs", "project", fgRight, "fg1", rightFeatures, rightFeatures, false, null);
Query right1 = new Query("fs", "project", fgRight, "fg2", rightFeatures1, rightFeatures1, false, null);
Join join = new Join(query, right, leftOn, rightOn, JoinType.INNER, null, joinOperator);
Join join1 = new Join(query, right1, leftOn1, rightOn1, JoinType.INNER, "R_", joinOperator1);
query.setJoins(Arrays.asList(join, join1));
String result = pitJoinController.generateSQL(query, false).toSqlString(new SparkSqlDialect(SqlDialect.EMPTY_CONTEXT)).getSql();
String expected = "WITH right_fg0 AS (SELECT *\n" + "FROM (SELECT `fg0`.`pk1`, `fg0`.`pk2`, `fg0`.`ts`, `fg0`.`label`, `fg0`.`pk1` `join_pk_pk1`, `fg0`.`ts` `join_evt_ts`, `fg1`.`pk1`, `fg1`.`pk2`, `fg1`.`ts`, `fg1`.`ft1`, " + "RANK() OVER (PARTITION BY `fg0`.`pk1`, `fg0`.`pk2`, `fg0`.`ts` ORDER BY `fg1`.`ts` DESC) pit_rank_hopsworks\n" + "FROM `fs`.`fg0_1` `fg0`\n" + "INNER JOIN `fs`.`fg1_1` `fg1` ON `fg0`.`pk1` = `fg1`.`pk1` AND `fg0`.`pk2` = `fg1`.`pk2` AND `fg0`.`ts` >= `fg1`.`ts`\n" + "WHERE `fg0`.`label` = 1) NA\n" + "WHERE `pit_rank_hopsworks` = 1), " + "right_fg1 AS (SELECT *\n" + "FROM (SELECT `fg0`.`pk1`, `fg0`.`pk2`, `fg0`.`ts`, `fg0`.`label`, `fg0`.`pk1` `join_pk_pk1`, `fg0`.`ts` `join_evt_ts`, `fg2`.`pk1` `R_pk1`, `fg2`.`ts` `R_ts`, `fg2`.`ft1` `R_ft1`, " + "RANK() OVER (PARTITION BY `fg0`.`pk1`, `fg0`.`ts` ORDER BY `fg2`.`ts` DESC) pit_rank_hopsworks\n" + "FROM `fs`.`fg0_1` `fg0`\n" + "INNER JOIN `fs`.`fg1_1` `fg2` ON `fg0`.`pk1` = `fg2`.`pk1` AND `fg0`.`ts` >= `fg2`.`ts`\n" + "WHERE `fg0`.`label` = 1) NA\n" + "WHERE `pit_rank_hopsworks` = 1) (" + "SELECT `right_fg0`.`pk1`, `right_fg0`.`pk2`, `right_fg0`.`ts`, `right_fg0`.`label`, `right_fg0`.`pk1`, `right_fg0`.`pk2`, `right_fg0`.`ts`, `right_fg0`.`ft1`, `right_fg1`.`R_pk1`, `right_fg1`.`R_ts`, `right_fg1`.`R_ft1`\n" + "FROM right_fg0\n" + "INNER JOIN right_fg1 ON `right_fg0`.`join_pk_pk1` = `right_fg1`.`join_pk_pk1` AND `right_fg0`.`join_evt_ts` = `right_fg1`.`join_evt_ts`)";
Assert.assertEquals(expected, result);
}
use of org.apache.calcite.sql.dialect.SparkSqlDialect in project hopsworks by logicalclocks.
the class TestFilterController method testGenerateFilterLogicNodeCase4.
@Test
public void testGenerateFilterLogicNodeCase4() throws Exception {
FilterLogic filterLogic = new FilterLogic(SqlFilterLogic.OR);
Filter leftFilter = new Filter(Arrays.asList(fg1Features.get(0)), SqlCondition.EQUALS, "abc");
Filter rightFilter = new Filter(Arrays.asList(fg2Features.get(1)), SqlCondition.LESS_THAN_OR_EQUAL, "10");
FilterLogic leftLogic = new FilterLogic(SqlFilterLogic.AND);
Filter middleFilter = new Filter(Arrays.asList(fg3Features.get(0)), SqlCondition.NOT_EQUALS, "abc");
Filter middleFilter2 = new Filter(Arrays.asList(fg3Features.get(1)), SqlCondition.NOT_EQUALS, "abc");
FilterLogic rightLogic = new FilterLogic(SqlFilterLogic.OR);
leftLogic.setLeftFilter(leftFilter);
leftLogic.setRightFilter(middleFilter);
rightLogic.setLeftFilter(middleFilter2);
rightLogic.setRightFilter(rightFilter);
filterLogic.setLeftLogic(leftLogic);
filterLogic.setRightLogic(rightLogic);
String result = filterController.generateFilterLogicNode(filterLogic, false).toSqlString(new SparkSqlDialect(SqlDialect.EMPTY_CONTEXT)).getSql();
String expected = "`fg1`.`fg1_pk` = 'abc' AND `fg3`.`fg3_pk` <> 'abc' " + "OR (CASE WHEN `fg3`.`fg3_ft` IS NULL THEN 'default' ELSE `fg3`.`fg3_ft` END <> 'abc' " + "OR CASE WHEN `fg2`.`fg2_ft` IS NULL THEN 10.0 ELSE `fg2`.`fg2_ft` END <= 10)";
Assert.assertEquals(expected, result);
}
use of org.apache.calcite.sql.dialect.SparkSqlDialect in project hopsworks by logicalclocks.
the class TestFilterController method testGenerateFilterLogicNodeSingleIN.
@Test
public void testGenerateFilterLogicNodeSingleIN() throws Exception {
FilterLogic filterLogic = new FilterLogic(SqlFilterLogic.SINGLE);
Filter filter = new Filter(Arrays.asList(fg1Features.get(0)), SqlCondition.IN, "[\"ab\", \"cd\"]");
filterLogic.setLeftFilter(filter);
String result = filterController.generateFilterLogicNode(filterLogic, false).toSqlString(new SparkSqlDialect(SqlDialect.EMPTY_CONTEXT)).getSql();
String expected = "`fg1`.`fg1_pk` IN ('ab', 'cd')";
Assert.assertEquals(expected, result);
}
use of org.apache.calcite.sql.dialect.SparkSqlDialect in project hopsworks by logicalclocks.
the class TestFilterController method testBuildFilterNodeTripleJoin.
@Test
public void testBuildFilterNodeTripleJoin() throws Exception {
Query leftQuery = new Query("fs1", "project_fs1", fg1, "fg0", fg1Features, fg1Features);
Query secondQuery = new Query("fs1", "project_fs1", fg2, "fg1", fg2Features, fg2Features);
Query thirdQuery = new Query("fs1", "project_fs1", fg3, "fg2", fg3Features, fg3Features);
FilterLogic firstFilter = new FilterLogic(SqlFilterLogic.AND);
firstFilter.setLeftFilter(new Filter(Arrays.asList(fg1Features.get(1)), SqlCondition.EQUALS, "10"));
FilterLogic rightLogic = new FilterLogic(SqlFilterLogic.OR);
rightLogic.setLeftFilter(new Filter(Arrays.asList(fg3Features.get(1)), SqlCondition.EQUALS, "10"));
rightLogic.setRightFilter(new Filter(Arrays.asList(fg3Features.get(2)), SqlCondition.EQUALS, "10"));
firstFilter.setRightLogic(rightLogic);
leftQuery.setFilter(firstFilter);
FilterLogic secondFilter = new FilterLogic(SqlFilterLogic.SINGLE);
secondFilter.setLeftFilter(new Filter(Arrays.asList(fg2Features.get(1)), SqlCondition.NOT_EQUALS, "10"));
secondQuery.setFilter(secondFilter);
Join join = new Join(leftQuery, secondQuery, joinFeatures, joinFeatures, JoinType.INNER, null, Arrays.asList(SqlCondition.EQUALS));
Join secondJoin = new Join(leftQuery, thirdQuery, leftOn, rightOn, JoinType.INNER, null, Arrays.asList(SqlCondition.EQUALS, SqlCondition.EQUALS));
leftQuery.setJoins(Arrays.asList(join, secondJoin));
String result = filterController.buildFilterNode(leftQuery, leftQuery, leftQuery.getJoins().size() - 1, false).toSqlString(new SparkSqlDialect(SqlDialect.EMPTY_CONTEXT)).getSql();
String expected = "`fg1`.`fg1_ft` = 10 " + "AND (CASE WHEN `fg3`.`fg3_ft` IS NULL THEN 'default' ELSE `fg3`.`fg3_ft` END = '10' OR `fg3`.`join` = '10') " + "AND CASE WHEN `fg2`.`fg2_ft` IS NULL THEN 10.0 ELSE `fg2`.`fg2_ft` END <> 10";
Assert.assertEquals(expected, result);
}
use of org.apache.calcite.sql.dialect.SparkSqlDialect in project hopsworks by logicalclocks.
the class TestConstructorController method testThreeWayJoinSQLNodeWithFilters.
@Test
public void testThreeWayJoinSQLNodeWithFilters() throws Exception {
List<Feature> availableFirst = new ArrayList<>();
availableFirst.add(new Feature("ft1", "fg0", "Float", null, null));
List<Feature> availableSecond = new ArrayList<>();
availableSecond.add(new Feature("ft2", "fg1", "Float", null, null));
List<Feature> availableThird = new ArrayList<>();
availableThird.add(new Feature("ft1", "fg2", "Float", null, null));
availableThird.add(new Feature("ft2", "fg2", "Float", null, null));
Query leftQuery = new Query("fs1", "project_fs1", fg1, "fg0", availableFirst, availableFirst);
Query secondQuery = new Query("fs1", "project_fs1", fg2, "fg1", availableSecond, availableSecond);
Query thirdQuery = new Query("fs1", "project_fs1", fg3, "fg2", availableThird, availableThird);
FilterLogic firstFilter = new FilterLogic(SqlFilterLogic.AND);
firstFilter.setLeftFilter(new Filter(Arrays.asList(availableFirst.get(0)), SqlCondition.EQUALS, "10"));
FilterLogic rightLogic = new FilterLogic(SqlFilterLogic.OR);
rightLogic.setLeftFilter(new Filter(Arrays.asList(availableThird.get(0)), SqlCondition.EQUALS, "10"));
rightLogic.setRightFilter(new Filter(Arrays.asList(availableThird.get(1)), SqlCondition.EQUALS, "10"));
firstFilter.setRightLogic(rightLogic);
leftQuery.setFilter(firstFilter);
FilterLogic secondFilter = new FilterLogic(SqlFilterLogic.SINGLE);
secondFilter.setLeftFilter(new Filter(Arrays.asList(availableSecond.get(0)), SqlCondition.NOT_EQUALS, "10"));
secondQuery.setFilter(secondFilter);
Join join = new Join(leftQuery, secondQuery, availableFirst, availableSecond, JoinType.INNER, null, singleEqualsJoinOperator);
Join secondJoin = new Join(leftQuery, thirdQuery, availableFirst, availableFirst, JoinType.INNER, null, singleEqualsJoinOperator);
leftQuery.setJoins(Arrays.asList(join, secondJoin));
String query = target.generateSQL(leftQuery, false).toSqlString(new SparkSqlDialect(SqlDialect.EMPTY_CONTEXT)).getSql().replace("\n", " ");
Assert.assertEquals("SELECT `fg0`.`ft1`, `fg1`.`ft2`, `fg2`.`ft1`, `fg2`.`ft2` " + "FROM `fs1`.`fg1_1` `fg0` " + "INNER JOIN `fs1`.`fg2_1` `fg1` ON `fg0`.`ft1` = `fg1`.`ft2` " + "INNER JOIN `fs1`.`fg3_1` `fg2` ON `fg0`.`ft1` = `fg2`.`ft1` " + "WHERE `fg0`.`ft1` = 10 AND (`fg2`.`ft1` = 10 OR `fg2`.`ft2` = 10) AND `fg1`.`ft2` <> 10", query);
}
Aggregations