Examples with SQLTable com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable used on opensource projects

Example 21 with SQLTable

use of com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable in project elide by yahoo.

the class AggregateBeforeJoinOptimizerTest method testHavingOnTimeDimensionInProjectionRequiringJoin.

@Test
public void testHavingOnTimeDimensionInProjectionRequiringJoin() {
    SQLTable gameRevenueTable = (SQLTable) metaDataStore.getTable("gameRevenue", NO_VERSION);
    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"), Operator.IN, Arrays.asList(new Day(new Date()))));
    Query query = Query.builder().source(gameRevenueTable).metricProjection(gameRevenueTable.getMetricProjection("revenue")).timeDimensionProjection(gameRevenueTable.getTimeDimensionProjection("sessionDate")).havingFilter(expression).build();
    compareQueryLists("SELECT MAX(`example_GameRevenue_XXX`.`INNER_AGG_XXX`) AS `revenue`," + "PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-dd'), '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-dd'), 'yyyy-MM-dd') " + "HAVING (MAX(`example_GameRevenue_XXX`.`INNER_AGG_XXX`) > :XXX " + "OR PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd') IN (:XXX))\n", engine.explain(query));
    testQueryExecution(query);
}

Example 22 with SQLTable

use of com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable in project elide by yahoo.

the class AggregateBeforeJoinOptimizerTest method testWhereOnTimeDimensionNotInProjectionNotRequiringJoin.

@Test
public void testWhereOnTimeDimensionNotInProjectionNotRequiringJoin() {
    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, "saleDate"), Operator.IN, Arrays.asList(new Day(new Date()))));
    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` " + "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` ) " + "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)\n";
    compareQueryLists(expected, engine.explain(query));
    testQueryExecution(query);
}

Example 23 with SQLTable

use of com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable in project elide by yahoo.

the class AggregateBeforeJoinOptimizerTest method testHavingOnMultiReferenceTimeDimensionInProjection.

@Test
public void testHavingOnMultiReferenceTimeDimensionInProjection() {
    SQLTable gameRevenueTable = (SQLTable) metaDataStore.getTable("gameRevenue", NO_VERSION);
    FilterExpression having = new OrFilterExpression(new FilterPredicate(new Path(GameRevenue.class, dictionary, "revenue"), Operator.GT, Arrays.asList(100)), new FilterPredicate(new Path(GameRevenue.class, dictionary, "lastDate"), Operator.IN, Arrays.asList(new Day(new Date()))));
    Query query = Query.builder().source(gameRevenueTable).metricProjection(gameRevenueTable.getMetricProjection("revenue")).timeDimensionProjection(gameRevenueTable.getTimeDimensionProjection("lastDate")).havingFilter(having).build();
    String expected = "SELECT MAX(`example_GameRevenue_XXX`.`INNER_AGG_XXX`) AS `revenue`," + "PARSEDATETIME(FORMATDATETIME(CASE WHEN `example_GameRevenue_XXX_playerStats_XXX`.`recordedDate` > `example_GameRevenue_XXX`.`saleDate` THEN `example_GameRevenue_XXX_playerStats_XXX`.`recordedDate` ELSE `example_GameRevenue_XXX`.`saleDate` END, 'yyyy-MM-dd'), 'yyyy-MM-dd') AS `lastDate` " + "FROM (SELECT MAX(`example_GameRevenue`.`revenue`) AS `INNER_AGG_XXX`," + "`example_GameRevenue`.`player_stats_id` AS `player_stats_id`," + "`example_GameRevenue`.`saleDate` AS `saleDate` " + "FROM `gameRevenue` AS `example_GameRevenue` " + "GROUP BY `example_GameRevenue`.`player_stats_id`, " + "`example_GameRevenue`.`saleDate` ) " + "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(CASE WHEN `example_GameRevenue_XXX_playerStats_XXX`.`recordedDate` > `example_GameRevenue_XXX`.`saleDate` THEN `example_GameRevenue_XXX_playerStats_XXX`.`recordedDate` ELSE `example_GameRevenue_XXX`.`saleDate` END, 'yyyy-MM-dd'), 'yyyy-MM-dd') " + "HAVING (MAX(`example_GameRevenue_XXX`.`INNER_AGG_XXX`) > :XXX " + "OR PARSEDATETIME(FORMATDATETIME(CASE WHEN `example_GameRevenue_XXX_playerStats_XXX`.`recordedDate` > `example_GameRevenue_XXX`.`saleDate` THEN `example_GameRevenue_XXX_playerStats_XXX`.`recordedDate` ELSE `example_GameRevenue_XXX`.`saleDate` END, 'yyyy-MM-dd'), 'yyyy-MM-dd') IN (:XXX))\n";
    compareQueryLists(expected, engine.explain(query));
    testQueryExecution(query);
}

Example 24 with SQLTable

use of com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable in project elide by yahoo.

the class AggregateBeforeJoinOptimizerTest method testSortOnDimensionInProjectionNotRequiringJoin.

@Test
public void testSortOnDimensionInProjectionNotRequiringJoin() {
    SQLTable gameRevenueTable = (SQLTable) metaDataStore.getTable("gameRevenue", NO_VERSION);
    Map<String, Sorting.SortOrder> sortMap = new TreeMap<>();
    sortMap.put("category", Sorting.SortOrder.desc);
    Query query = Query.builder().source(gameRevenueTable).dimensionProjection(gameRevenueTable.getDimensionProjection("countryIsoCode")).dimensionProjection(gameRevenueTable.getDimensionProjection("category")).metricProjection(gameRevenueTable.getMetricProjection("revenue")).sorting(new SortingImpl(sortMap, GameRevenue.class, dictionary)).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`.`category` AS `category` " + "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` " + "GROUP BY `example_GameRevenue_XXX_country_XXX`.`iso_code`, " + "`example_GameRevenue_XXX`.`category` " + "ORDER BY `example_GameRevenue_XXX`.`category` DESC\n";
    compareQueryLists(expected, engine.explain(query));
    testQueryExecution(query);
}

Example 25 with SQLTable

use of com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable in project elide by yahoo.

the class HasColumnArgsVisitorTest method testSimpleJoinToLogicalColumn.

@Test
public void testSimpleJoinToLogicalColumn() throws Exception {
    SQLTable table = metaDataStore.getTable(ClassType.of(TableC.class));
    ColumnProjection projection = table.getColumnProjection("simpleJoinToLogical");
    assertFalse(matches(table, projection));
}