Examples with Query com.yahoo.elide.datastores.aggregation.query.Query used on opensource projects

Example 6 with Query

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

the class SQLQueryEngine method toPageTotalSQL.

/**
 * Takes a SQLQuery and creates a new clone that instead returns the total number of records of the original
 * query.
 *
 * @param query The client query
 * @param sql The generated SQL query
 * @param sqlDialect the SQL dialect
 * @return A new query that returns the total number of records.
 */
private NativeQuery toPageTotalSQL(Query query, NativeQuery sql, SQLDialect sqlDialect) {
    // TODO: refactor this method
    String groupByDimensions = query.getAllDimensionProjections().stream().map(SQLColumnProjection.class::cast).filter(SQLColumnProjection::isProjected).map((column) -> column.toSQL(query, metaDataStore)).collect(Collectors.joining(", "));
    if (groupByDimensions.isEmpty()) {
        // Metric projection without group by dimension will return onely 1 record.
        return null;
    }
    NativeQuery innerQuery = NativeQuery.builder().projectionClause(groupByDimensions).fromClause(sql.getFromClause()).joinClause(sql.getJoinClause()).whereClause(sql.getWhereClause()).groupByClause(String.format("GROUP BY %s", groupByDimensions)).havingClause(sql.getHavingClause()).build();
    return NativeQuery.builder().projectionClause("COUNT(*)").fromClause(QueryTranslator.getFromClause("(" + innerQuery + ")", applyQuotes("pagination_subquery", sqlDialect), sqlDialect)).build();
}

Example 7 with Query

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

the class DefaultQueryValidator method validateHavingClause.

@Override
public void validateHavingClause(Query query) {
    FilterExpression havingClause = query.getHavingFilter();
    if (havingClause == null) {
        return;
    }
    havingClause.accept(new PredicateExtractionVisitor()).forEach(predicate -> {
        Path path = predicate.getPath();
        if (path.getPathElements().size() > 1) {
            throw new InvalidOperationException("Relationship traversal not supported for analytic queries.");
        }
        validatePredicate(query, predicate);
        extractFilterProjections(query, havingClause).stream().forEach(projection -> {
            Predicate<ColumnProjection> filterByNameAndArgs = (column) -> (column.getAlias().equals(projection.getAlias()) || column.getName().equals(projection.getName())) && column.getArguments().equals(projection.getArguments());
            // Query by (alias or name) and arguments.   The filter may or may not be using the alias.
            if (query.getColumnProjection(filterByNameAndArgs) == null) {
                Predicate<ColumnProjection> filterByName = (column) -> (column.getAlias().equals(projection.getAlias()) || column.getName().equals(projection.getName()));
                // The column wasn't projected at all.
                if (query.getColumnProjection(filterByName) == null) {
                    throw new InvalidOperationException(String.format("Post aggregation filtering on '%s' requires the field to be projected in the response", projection.getAlias()));
                // The column was projected but arguments didn't match.
                } else {
                    throw new InvalidOperationException(String.format("Post aggregation filtering on '%s' requires the field to be projected " + "in the response with matching arguments", projection.getAlias()));
                }
            }
        });
    });
}

Example 8 with Query

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

the class AggregateBeforeJoinOptimizerTest method testWhereOnTimeDimensionInProjectionNotRequiringJoinWithDefaultMatchingArguments.

@Test
public void testWhereOnTimeDimensionInProjectionNotRequiringJoinWithDefaultMatchingArguments() {
    SQLTable gameRevenueTable = (SQLTable) metaDataStore.getTable("gameRevenue", NO_VERSION);
    Set<Argument> arguments = new HashSet<>();
    arguments.add(Argument.builder().name("grain").value("DAY").build());
    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", "saleDate", arguments), 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);
}

Example 9 with Query

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

the class AggregateBeforeJoinOptimizerTest method testWhereOnTimeDimensionInProjectionRequiringJoin.

@Test
public void testWhereOnTimeDimensionInProjectionRequiringJoin() {
    SQLTable gameRevenueTable = (SQLTable) metaDataStore.getTable("gameRevenue", NO_VERSION);
    FilterExpression where = new FilterPredicate(new Path(GameRevenue.class, dictionary, "sessionDate"), Operator.IN, Arrays.asList(new Day(new Date())));
    Query query = Query.builder().source(gameRevenueTable).timeDimensionProjection(gameRevenueTable.getTimeDimensionProjection("sessionDate")).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-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` " + "WHERE PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd') IN (:XXX) " + "GROUP BY PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd')\n";
    compareQueryLists(expected, engine.explain(query));
    testQueryExecution(query);
}

Example 10 with Query

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

the class AggregateBeforeJoinOptimizerTest method testWhereOnTimeDimensionInProjectionNotRequiringJoinWithMatchingArguments.

@Test
public void testWhereOnTimeDimensionInProjectionNotRequiringJoinWithMatchingArguments() {
    SQLTable gameRevenueTable = (SQLTable) metaDataStore.getTable("gameRevenue", NO_VERSION);
    Set<Argument> arguments = new HashSet<>();
    arguments.add(Argument.builder().name("grain").value("MONTH").build());
    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", "saleDate", arguments), 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", arguments)).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-01'), 'yyyy-MM-dd') AS `saleDate` " + "FROM `gameRevenue` AS `example_GameRevenue` " + "WHERE PARSEDATETIME(FORMATDATETIME(`example_GameRevenue`.`saleDate`, 'yyyy-MM-01'), 'yyyy-MM-dd') IN (:XXX) " + "GROUP BY `example_GameRevenue`.`country_id`, " + "PARSEDATETIME(FORMATDATETIME(`example_GameRevenue`.`saleDate`, 'yyyy-MM-01'), '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);
}