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

Example 1 with SQLTable

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

the class SQLQueryEngine method constructTable.

@Override
protected Table constructTable(Namespace namespace, Type<?> entityClass, EntityDictionary metaDataDictionary) {
    String dbConnectionName = null;
    Annotation annotation = EntityDictionary.getFirstAnnotation(entityClass, Arrays.asList(FromTable.class, FromSubquery.class));
    if (annotation instanceof FromTable) {
        dbConnectionName = ((FromTable) annotation).dbConnectionName();
    } else if (annotation instanceof FromSubquery) {
        dbConnectionName = ((FromSubquery) annotation).dbConnectionName();
    }
    ConnectionDetails connectionDetails = connectionDetailsLookup.apply(dbConnectionName);
    return new SQLTable(namespace, entityClass, metaDataDictionary, connectionDetails);
}

Example 2 with SQLTable

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

the class DefaultQueryValidator method validateColumnArguments.

protected void validateColumnArguments(Query query, ColumnProjection projection) {
    SQLTable table = (SQLTable) query.getSource();
    Column column = table.getColumn(Column.class, projection.getName());
    column.getArgumentDefinitions().forEach(columnArgument -> {
        Argument clientArgument = projection.getArguments().get(columnArgument.getName());
        validateArgument(Optional.ofNullable(clientArgument), columnArgument, "column '" + projection.getAlias() + "'");
    });
}

Example 3 with SQLTable

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

the class JoinExpressionExtractor method visitJoinReference.

@Override
public Set<String> visitJoinReference(JoinReference reference) {
    JoinPath joinPath = reference.getPath();
    List<PathElement> pathElements = joinPath.getPathElements();
    ColumnContext currentCtx = this.context;
    for (int i = 0; i < pathElements.size() - 1; i++) {
        PathElement pathElement = pathElements.get(i);
        Type<?> joinClass = pathElement.getFieldType();
        String joinFieldName = pathElement.getFieldName();
        SQLJoin sqlJoin = currentCtx.getQueryable().getJoin(joinFieldName);
        ColumnContext joinCtx;
        String onClause;
        JoinType joinType;
        String fullExpression;
        if (sqlJoin != null) {
            joinType = sqlJoin.getJoinType();
            joinCtx = (ColumnContext) currentCtx.get(joinFieldName);
            if (joinType.equals(JoinType.CROSS)) {
                onClause = EMPTY;
            } else {
                onClause = ON + currentCtx.resolve(sqlJoin.getJoinExpression());
            }
        } else {
            joinType = JoinType.LEFT;
            SQLTable table = metaDataStore.getTable(joinClass);
            joinCtx = ColumnContext.builder().queryable(table).alias(appendAlias(currentCtx.getAlias(), joinFieldName)).metaDataStore(currentCtx.getMetaDataStore()).column(currentCtx.getColumn()).tableArguments(mergedArgumentMap(table.getArguments(), currentCtx.getTableArguments())).build();
            onClause = ON + String.format("%s.%s = %s.%s", currentCtx.getAlias(), dictionary.getAnnotatedColumnName(pathElement.getType(), joinFieldName), joinCtx.getAlias(), dictionary.getAnnotatedColumnName(joinClass, dictionary.getIdFieldName(joinClass)));
        }
        SQLDialect sqlDialect = currentCtx.getQueryable().getDialect();
        String joinAlias = applyQuotes(joinCtx.getAlias(), sqlDialect);
        String joinKeyword = currentCtx.getQueryable().getDialect().getJoinKeyword(joinType);
        String joinSource = constructTableOrSubselect(joinCtx, joinClass);
        if (sqlDialect.useASBeforeTableAlias()) {
            fullExpression = String.format("%s %s AS %s %s", joinKeyword, joinSource, joinAlias, onClause);
        } else {
            fullExpression = String.format("%s %s %s %s", joinKeyword, joinSource, joinAlias, onClause);
        }
        joinExpressions.add(fullExpression);
        /**
         * If this `for` loop runs more than once, context should be switched to join context.
         */
        currentCtx = joinCtx;
    }
    // If reference within current join reference is of type PhysicalReference, then below visitor doesn't matter.
    // If it is of type LogicalReference, then visitLogicalReference method will recreate visitor with correct
    // value of ColumnProjection in context.
    JoinExpressionExtractor visitor = new JoinExpressionExtractor(currentCtx);
    joinExpressions.addAll(reference.getReference().accept(visitor));
    return joinExpressions;
}

Example 4 with SQLTable

use of com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable 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 5 with SQLTable

use of com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable 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);
}