Search in sources :

Example 16 with Query

use of com.yahoo.elide.datastores.aggregation.query.Query in project elide by yahoo.

the class AggregateBeforeJoinOptimizerTest method testWhereOnTimeDimensionInProjectionNotRequiringJoin.

@Test
public void testWhereOnTimeDimensionInProjectionNotRequiringJoin() {
    SQLTable gameRevenueTable = (SQLTable) metaDataStore.getTable("gameRevenue", NO_VERSION);
    FilterExpression having = new FilterPredicate(new Path(GameRevenue.class, dictionary, "revenue"), Operator.GT, Arrays.asList(9000));
    FilterExpression where = new FilterPredicate(new Path(GameRevenue.class, dictionary, "saleDate"), Operator.IN, Arrays.asList(new Day(new Date())));
    Query query = Query.builder().source(gameRevenueTable).metricProjection(gameRevenueTable.getMetricProjection("revenue")).dimensionProjection(gameRevenueTable.getDimensionProjection("countryIsoCode")).timeDimensionProjection(gameRevenueTable.getTimeDimensionProjection("saleDate")).havingFilter(having).whereFilter(where).build();
    String expected = "SELECT MAX(`example_GameRevenue_XXX`.`INNER_AGG_XXX`) AS `revenue`," + "`example_GameRevenue_XXX_country_XXX`.`iso_code` AS `countryIsoCode`," + "`example_GameRevenue_XXX`.`saleDate` AS `saleDate` " + "FROM (SELECT MAX(`example_GameRevenue`.`revenue`) AS `INNER_AGG_XXX`," + "`example_GameRevenue`.`country_id` AS `country_id`," + "PARSEDATETIME(FORMATDATETIME(`example_GameRevenue`.`saleDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd') AS `saleDate` " + "FROM `gameRevenue` AS `example_GameRevenue` " + "WHERE PARSEDATETIME(FORMATDATETIME(`example_GameRevenue`.`saleDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd') IN (:XXX) " + "GROUP BY `example_GameRevenue`.`country_id`, " + "PARSEDATETIME(FORMATDATETIME(`example_GameRevenue`.`saleDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd') ) " + "AS `example_GameRevenue_XXX` " + "LEFT OUTER JOIN `countries` AS `example_GameRevenue_XXX_country_XXX` " + "ON `example_GameRevenue_XXX`.`country_id` = `example_GameRevenue_XXX_country_XXX`.`id` " + "GROUP BY `example_GameRevenue_XXX_country_XXX`.`iso_code`, " + "`example_GameRevenue_XXX`.`saleDate` " + "HAVING MAX(`example_GameRevenue_XXX`.`INNER_AGG_XXX`) > :XXX\n";
    compareQueryLists(expected, engine.explain(query));
    testQueryExecution(query);
}
Also used : Path(com.yahoo.elide.core.Path) Query(com.yahoo.elide.datastores.aggregation.query.Query) SQLTable(com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable) GameRevenue(example.GameRevenue) FilterPredicate(com.yahoo.elide.core.filter.predicates.FilterPredicate) OrFilterExpression(com.yahoo.elide.core.filter.expression.OrFilterExpression) FilterExpression(com.yahoo.elide.core.filter.expression.FilterExpression) AndFilterExpression(com.yahoo.elide.core.filter.expression.AndFilterExpression) Day(com.yahoo.elide.datastores.aggregation.timegrains.Day) Date(java.util.Date) SQLUnitTest(com.yahoo.elide.datastores.aggregation.framework.SQLUnitTest) Test(org.junit.jupiter.api.Test)

Example 17 with Query

use of com.yahoo.elide.datastores.aggregation.query.Query in project elide by yahoo.

the class AggregateBeforeJoinOptimizerTest method testWhereOnTimeDimensionInProjectionRequiringJoinWithMatchingArguments.

@Test
public void testWhereOnTimeDimensionInProjectionRequiringJoinWithMatchingArguments() {
    SQLTable gameRevenueTable = (SQLTable) metaDataStore.getTable("gameRevenue", NO_VERSION);
    // Grain in filter matches grain in projected dimension.
    Set<Argument> arguments = new HashSet<>();
    arguments.add(Argument.builder().name("grain").value("MONTH").build());
    FilterExpression where = new FilterPredicate(new Path(GameRevenue.class, dictionary, "sessionDate", "sessionDate", arguments), Operator.IN, Arrays.asList(new Day(new Date())));
    Query query = Query.builder().source(gameRevenueTable).timeDimensionProjection(gameRevenueTable.getTimeDimensionProjection("sessionDate", arguments)).metricProjection(gameRevenueTable.getMetricProjection("revenue")).whereFilter(where).build();
    String expected = "SELECT MAX(`example_GameRevenue_XXX`.`INNER_AGG_XXX`) AS `revenue`," + "PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-01'), 'yyyy-MM-dd') AS `sessionDate` " + "FROM (SELECT MAX(`example_GameRevenue`.`revenue`) AS `INNER_AGG_XXX`," + "`example_GameRevenue`.`player_stats_id` AS `player_stats_id` " + "FROM `gameRevenue` AS `example_GameRevenue` " + "GROUP BY `example_GameRevenue`.`player_stats_id` ) AS `example_GameRevenue_XXX` " + "LEFT OUTER JOIN `playerStats` AS `example_GameRevenue_XXX_playerStats_XXX` " + "ON `example_GameRevenue_XXX`.`player_stats_id` = `example_GameRevenue_XXX_playerStats_XXX`.`id` " + "WHERE PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-01'), 'yyyy-MM-dd') IN (:XXX) " + "GROUP BY PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-01'), 'yyyy-MM-dd')\n";
    compareQueryLists(expected, engine.explain(query));
    testQueryExecution(query);
}
Also used : Path(com.yahoo.elide.core.Path) Argument(com.yahoo.elide.core.request.Argument) Query(com.yahoo.elide.datastores.aggregation.query.Query) SQLTable(com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable) GameRevenue(example.GameRevenue) FilterPredicate(com.yahoo.elide.core.filter.predicates.FilterPredicate) OrFilterExpression(com.yahoo.elide.core.filter.expression.OrFilterExpression) FilterExpression(com.yahoo.elide.core.filter.expression.FilterExpression) AndFilterExpression(com.yahoo.elide.core.filter.expression.AndFilterExpression) Day(com.yahoo.elide.datastores.aggregation.timegrains.Day) Date(java.util.Date) HashSet(java.util.HashSet) SQLUnitTest(com.yahoo.elide.datastores.aggregation.framework.SQLUnitTest) Test(org.junit.jupiter.api.Test)

Example 18 with Query

use of com.yahoo.elide.datastores.aggregation.query.Query in project elide by yahoo.

the class AggregateBeforeJoinOptimizerTest method testHavingOnTimeDimensionInProjectionRequiringJoinWithArguments.

@Test
public void testHavingOnTimeDimensionInProjectionRequiringJoinWithArguments() {
    SQLTable gameRevenueTable = (SQLTable) metaDataStore.getTable("gameRevenue", NO_VERSION);
    Set<Argument> arguments = new HashSet<>();
    arguments.add(Argument.builder().name("grain").value("MONTH").build());
    FilterExpression expression = new OrFilterExpression(new FilterPredicate(new Path(GameRevenue.class, dictionary, "revenue"), Operator.GT, Arrays.asList(9000)), new FilterPredicate(new Path(GameRevenue.class, dictionary, "sessionDate", "sessionDate", arguments), Operator.IN, Arrays.asList(new Day(new Date()))));
    Query query = Query.builder().source(gameRevenueTable).metricProjection(gameRevenueTable.getMetricProjection("revenue")).timeDimensionProjection(gameRevenueTable.getTimeDimensionProjection("sessionDate", arguments)).havingFilter(expression).build();
    compareQueryLists("SELECT MAX(`example_GameRevenue_XXX`.`INNER_AGG_XXX`) AS `revenue`," + "PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-01'), 'yyyy-MM-dd') AS `sessionDate` " + "FROM (SELECT MAX(`example_GameRevenue`.`revenue`) AS `INNER_AGG_XXX`," + "`example_GameRevenue`.`player_stats_id` AS `player_stats_id` " + "FROM `gameRevenue` AS `example_GameRevenue` " + "GROUP BY `example_GameRevenue`.`player_stats_id` ) " + "AS `example_GameRevenue_XXX` " + "LEFT OUTER JOIN `playerStats` AS `example_GameRevenue_XXX_playerStats_XXX` " + "ON `example_GameRevenue_XXX`.`player_stats_id` = `example_GameRevenue_XXX_playerStats_XXX`.`id` " + "GROUP BY PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-01'), 'yyyy-MM-dd') " + "HAVING (MAX(`example_GameRevenue_XXX`.`INNER_AGG_XXX`) > :XXX " + "OR PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-01'), 'yyyy-MM-dd') IN (:XXX))\n", engine.explain(query));
    testQueryExecution(query);
}
Also used : Path(com.yahoo.elide.core.Path) Argument(com.yahoo.elide.core.request.Argument) Query(com.yahoo.elide.datastores.aggregation.query.Query) OrFilterExpression(com.yahoo.elide.core.filter.expression.OrFilterExpression) SQLTable(com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable) FilterPredicate(com.yahoo.elide.core.filter.predicates.FilterPredicate) OrFilterExpression(com.yahoo.elide.core.filter.expression.OrFilterExpression) FilterExpression(com.yahoo.elide.core.filter.expression.FilterExpression) AndFilterExpression(com.yahoo.elide.core.filter.expression.AndFilterExpression) Day(com.yahoo.elide.datastores.aggregation.timegrains.Day) Date(java.util.Date) HashSet(java.util.HashSet) SQLUnitTest(com.yahoo.elide.datastores.aggregation.framework.SQLUnitTest) Test(org.junit.jupiter.api.Test)

Example 19 with Query

use of com.yahoo.elide.datastores.aggregation.query.Query in project elide by yahoo.

the class AggregateBeforeJoinOptimizerTest method testSortAndPaginate.

@Test
public void testSortAndPaginate() {
    Map<String, Sorting.SortOrder> sortMap = new TreeMap<>();
    sortMap.put("highScore", Sorting.SortOrder.desc);
    // WHERE filter
    FilterPredicate predicate = new FilterPredicate(new Path(PlayerStats.class, dictionary, "highScore"), Operator.GT, Arrays.asList(9000));
    Query query = Query.builder().source(playerStatsTable).metricProjection(playerStatsTable.getMetricProjection("highScore")).dimensionProjection(playerStatsTable.getDimensionProjection("overallRating")).timeDimensionProjection(playerStatsTable.getTimeDimensionProjection("recordedDate")).pagination(new ImmutablePagination(10, 5, false, true)).sorting(new SortingImpl(sortMap, PlayerStats.class, dictionary)).havingFilter(predicate).whereFilter(parseFilterExpression("countryIsoCode==USA", playerStatsType, false)).build();
    String expectedQueryStr1 = "SELECT COUNT(*) FROM " + "(SELECT `example_PlayerStats_XXX`.`overallRating`, " + "`example_PlayerStats_XXX`.`recordedDate` " + "FROM (SELECT MAX(`example_PlayerStats`.`highScore`) AS `INNER_AGG_XXX`," + "`example_PlayerStats`.`overallRating` AS `overallRating`," + "`example_PlayerStats`.`country_id` AS `country_id`," + "PARSEDATETIME(FORMATDATETIME(`example_PlayerStats`.`recordedDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd') AS `recordedDate` " + "FROM `playerStats` AS `example_PlayerStats` " + "GROUP BY `example_PlayerStats`.`overallRating`, " + "`example_PlayerStats`.`country_id`, " + "PARSEDATETIME(FORMATDATETIME(`example_PlayerStats`.`recordedDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd') ) " + "AS `example_PlayerStats_XXX` " + "LEFT OUTER JOIN `countries` AS `example_PlayerStats_XXX_country_XXX` " + "ON `example_PlayerStats_XXX`.`country_id` = `example_PlayerStats_XXX_country_XXX`.`id` " + "WHERE `example_PlayerStats_XXX_country_XXX`.`iso_code` IN (:XXX) " + "GROUP BY `example_PlayerStats_XXX`.`overallRating`, " + "`example_PlayerStats_XXX`.`recordedDate` " + "HAVING MAX(`example_PlayerStats_XXX`.`INNER_AGG_XXX`) > :XXX ) AS `pagination_subquery`\n";
    String expectedQueryStr2 = "SELECT MAX(`example_PlayerStats_XXX`.`INNER_AGG_XXX`) AS `highScore`," + "`example_PlayerStats_XXX`.`overallRating` AS `overallRating`," + "`example_PlayerStats_XXX`.`recordedDate` AS `recordedDate` " + "FROM (SELECT MAX(`example_PlayerStats`.`highScore`) AS `INNER_AGG_XXX`," + "`example_PlayerStats`.`overallRating` AS `overallRating`," + "`example_PlayerStats`.`country_id` AS `country_id`," + "PARSEDATETIME(FORMATDATETIME(`example_PlayerStats`.`recordedDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd') AS `recordedDate` " + "FROM `playerStats` AS `example_PlayerStats` " + "GROUP BY `example_PlayerStats`.`overallRating`, " + "`example_PlayerStats`.`country_id`, " + "PARSEDATETIME(FORMATDATETIME(`example_PlayerStats`.`recordedDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd') ) " + "AS `example_PlayerStats_XXX` " + "LEFT OUTER JOIN `countries` AS `example_PlayerStats_XXX_country_XXX` " + "ON `example_PlayerStats_XXX`.`country_id` = `example_PlayerStats_XXX_country_XXX`.`id` " + "WHERE `example_PlayerStats_XXX_country_XXX`.`iso_code` IN (:XXX) " + "GROUP BY `example_PlayerStats_XXX`.`overallRating`, " + "`example_PlayerStats_XXX`.`recordedDate` " + "HAVING MAX(`example_PlayerStats_XXX`.`INNER_AGG_XXX`) > :XXX " + "ORDER BY MAX(`example_PlayerStats_XXX`.`INNER_AGG_XXX`) " + "DESC LIMIT 5 OFFSET 10\n";
    List<String> expectedQueryList = new ArrayList<>();
    expectedQueryList.add(expectedQueryStr1);
    expectedQueryList.add(expectedQueryStr2);
    compareQueryLists(expectedQueryList, engine.explain(query));
    testQueryExecution(query);
}
Also used : Path(com.yahoo.elide.core.Path) Query(com.yahoo.elide.datastores.aggregation.query.Query) SortingImpl(com.yahoo.elide.core.sort.SortingImpl) ArrayList(java.util.ArrayList) ImmutablePagination(com.yahoo.elide.datastores.aggregation.query.ImmutablePagination) FilterPredicate(com.yahoo.elide.core.filter.predicates.FilterPredicate) PlayerStats(example.PlayerStats) TreeMap(java.util.TreeMap) SQLUnitTest(com.yahoo.elide.datastores.aggregation.framework.SQLUnitTest) Test(org.junit.jupiter.api.Test)

Example 20 with Query

use of com.yahoo.elide.datastores.aggregation.query.Query in project elide by yahoo.

the class AggregateBeforeJoinOptimizerTest method testWhereOnMultiReferenceDimensionNotInProjection.

@Test
public void testWhereOnMultiReferenceDimensionNotInProjection() {
    SQLTable gameRevenueTable = (SQLTable) metaDataStore.getTable("gameRevenue", NO_VERSION);
    FilterExpression where = new AndFilterExpression(new FilterPredicate(new Path(GameRevenue.class, dictionary, "countryIsoCode"), Operator.IN, Arrays.asList("foo")), new FilterPredicate(new Path(GameRevenue.class, dictionary, "countryCategory"), Operator.IN, Arrays.asList("US")));
    Query query = Query.builder().source(gameRevenueTable).metricProjection(gameRevenueTable.getMetricProjection("revenue")).whereFilter(where).build();
    String expected = "SELECT MAX(`example_GameRevenue_XXX`.`INNER_AGG_XXX`) AS `revenue` " + "FROM (SELECT MAX(`example_GameRevenue`.`revenue`) AS `INNER_AGG_XXX`," + "`example_GameRevenue`.`country_id` AS `country_id`," + "`example_GameRevenue`.`category` AS `category` " + "FROM `gameRevenue` AS `example_GameRevenue` " + "GROUP BY `example_GameRevenue`.`country_id`, " + "`example_GameRevenue`.`category` ) " + "AS `example_GameRevenue_XXX` " + "LEFT OUTER JOIN `countries` AS `example_GameRevenue_XXX_country_XXX` " + "ON `example_GameRevenue_XXX`.`country_id` = `example_GameRevenue_XXX_country_XXX`.`id` " + "WHERE (`example_GameRevenue_XXX_country_XXX`.`iso_code` IN (:XXX) " + "AND CASE WHEN `example_GameRevenue_XXX_country_XXX`.`iso_code` = 'US' THEN `example_GameRevenue_XXX`.`category` ELSE 'UNKNONWN' END IN (:XXX))\n";
    compareQueryLists(expected, engine.explain(query));
    testQueryExecution(query);
}
Also used : Path(com.yahoo.elide.core.Path) Query(com.yahoo.elide.datastores.aggregation.query.Query) SQLTable(com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable) FilterPredicate(com.yahoo.elide.core.filter.predicates.FilterPredicate) OrFilterExpression(com.yahoo.elide.core.filter.expression.OrFilterExpression) FilterExpression(com.yahoo.elide.core.filter.expression.FilterExpression) AndFilterExpression(com.yahoo.elide.core.filter.expression.AndFilterExpression) AndFilterExpression(com.yahoo.elide.core.filter.expression.AndFilterExpression) SQLUnitTest(com.yahoo.elide.datastores.aggregation.framework.SQLUnitTest) Test(org.junit.jupiter.api.Test)

Aggregations

Query (com.yahoo.elide.datastores.aggregation.query.Query)242 Test (org.junit.jupiter.api.Test)229 SQLUnitTest (com.yahoo.elide.datastores.aggregation.framework.SQLUnitTest)214 SQLTable (com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable)51 FilterExpression (com.yahoo.elide.core.filter.expression.FilterExpression)46 Argument (com.yahoo.elide.core.request.Argument)43 ArrayList (java.util.ArrayList)42 FilterPredicate (com.yahoo.elide.core.filter.predicates.FilterPredicate)37 Path (com.yahoo.elide.core.Path)35 Day (com.yahoo.elide.datastores.aggregation.timegrains.Day)31 AndFilterExpression (com.yahoo.elide.core.filter.expression.AndFilterExpression)29 SortingImpl (com.yahoo.elide.core.sort.SortingImpl)29 PlayerStats (example.PlayerStats)29 TreeMap (java.util.TreeMap)29 OrFilterExpression (com.yahoo.elide.core.filter.expression.OrFilterExpression)28 HashMap (java.util.HashMap)26 Date (java.util.Date)19 HashSet (java.util.HashSet)17 ToString (lombok.ToString)16 SQLDimensionProjection (com.yahoo.elide.datastores.aggregation.queryengines.sql.query.SQLDimensionProjection)14