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

Example 81 with Query

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

the class PhysicalRefColumnContextTest method testSqlHelper.

@Test
public void testSqlHelper() {
    Map<String, Argument> revenueArg = new HashMap<>();
    revenueArg.put("format", Argument.builder().name("format").value("11D00").build());
    SQLMetricProjection revenueUsingSqlHelper = (SQLMetricProjection) revenueFactTable.getMetricProjection("revenueUsingSqlHelper", "revenueUsingSqlHelper", revenueArg);
    SQLMetricProjection impressionsPerUSD = (SQLMetricProjection) revenueFactTable.getMetricProjection("impressionsPerUSD");
    Map<String, Argument> impressionsPerUSDArg = new HashMap<>();
    impressionsPerUSDArg.put("format", Argument.builder().name("format").value("11D00").build());
    SQLMetricProjection impressionsPerUSDWithArg = (SQLMetricProjection) revenueFactTable.getMetricProjection("impressionsPerUSD", "impressionsPerUSDWithArg", impressionsPerUSDArg);
    // impressionsPerUSD2 invokes 'revenueUsingSqlHelper' instead of 'revenue'.
    SQLMetricProjection impressionsPerUSD2 = (SQLMetricProjection) revenueFactTable.getMetricProjection("impressionsPerUSD2");
    Query query = Query.builder().source(revenueFactTable).metricProjection(revenueUsingSqlHelper).metricProjection(impressionsPerUSD).metricProjection(impressionsPerUSD2).arguments(queryArgs).build();
    Query.QueryBuilder builder = Query.builder().source(query.getSource()).metricProjections(query.getMetricProjections()).arguments(query.getArguments());
    Query expandedQuery = addHiddenProjections(metaDataStore, builder, query).build();
    // definition: TO_CHAR(SUM({{$revenue}}) * {{sql from='rate' column='conversionRate[format:9999D0000]'}}, {{$$column.args.format}})
    // -> value of 'format' argument is passed in the query for "revenueUsingSqlHelper" column and same is used for
    // resolving this column.
    // -> pinned value (9999D0000) of 'format' argument in SQL helper is used while resolving referenced column "rate.conversionRate".
    assertEquals("TO_CHAR(SUM({{$revenue}}) * TO_CHAR({{rate.$conversion_rate}}, 9999D0000), 11D00)", revenueUsingSqlHelper.toPhysicalReferences(expandedQuery, metaDataStore));
    // definition: TO_CHAR({{sql column='impressions[aggregation:SUM]'}} / {{sql column='revenue[format:99999D00000]'}}, {{$$table.args.format}})
    // -> {{$$table.args.format}} is resolved using query argument 'format' (999999D000000).
    // -> pinned value (SUM) of 'aggregation' argument in SQL helper is used while resolving invoked column "impressions".
    // -> pinned value (99999D00000) of 'format' argument in SQL helper is used while resolving invoked column "revenue".
    // -> revenue definition is : TO_CHAR(SUM({{$revenue}}) * {{rate.conversionRate}}, {{$$column.args.format}}),
    // Available value of 'format' argument in "revenue" column is passed to joined table's "conversionRate" column.
    assertEquals("TO_CHAR(SUM({{$impressions}}) / TO_CHAR(SUM({{$revenue}}) * TO_CHAR({{rate.$conversion_rate}}, 99999D00000), 99999D00000), 999999D000000)", impressionsPerUSD.toPhysicalReferences(expandedQuery, metaDataStore));
    // -> Even 'format' is passed in query column args, pinned value (9999D0000) of 'format' argument in SQL helper is used while
    // resolving "revenue" column and same is passed to joined table's "conversionRate" column.
    assertEquals("TO_CHAR(SUM({{$impressions}}) / TO_CHAR(SUM({{$revenue}}) * TO_CHAR({{rate.$conversion_rate}}, 99999D00000), 99999D00000), 999999D000000)", impressionsPerUSDWithArg.toPhysicalReferences(expandedQuery, metaDataStore));
    // definition: TO_CHAR({{sql column='impressions[aggregation:SUM]'}} / {{sql column='revenueUsingSqlHelper[format:99999D00000]'}}, {{$$table.args.format}})
    // -> As "rate.conversionRate" is invoked using SQL helper from "revenue" column, this uses the fixed value(9999D0000) of
    // 'format' argument provided in definition of "revenueUsingSqlHelper" column.
    assertEquals("TO_CHAR(SUM({{$impressions}}) / TO_CHAR(SUM({{$revenue}}) * TO_CHAR({{rate.$conversion_rate}}, 9999D0000), 99999D00000), 999999D000000)", impressionsPerUSD2.toPhysicalReferences(expandedQuery, metaDataStore));
}

Example 82 with Query

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

the class DruidExplainQueryTest method testNestedMetricQuery.

@Test
public void testNestedMetricQuery() {
    Query query = TestQuery.NESTED_METRIC_QUERY.getQuery();
    String exptectedQueryStr = getExpectedNestedMetricQuery().replace(BACKTICK, DOUBLE_QUOTE);
    List<String> expectedQueryList = new ArrayList<>();
    expectedQueryList.add(exptectedQueryStr);
    compareQueryLists(expectedQueryList, engine.explain(query));
    testQueryExecution(TestQuery.NESTED_METRIC_QUERY.getQuery());
}

Example 83 with Query

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

the class DruidExplainQueryTest method testExplainComplicated.

/* TODO: Druid doesn't support this. To make this work, we'd need to push the ORDER BY field into the SELECT
    then drop it before returning the data.
    @Test
    public void testExplainOrderByNotInSelect() {
        String expectedQueryStr =
                "SELECT MAX(\"example_PlayerStats\".\"highScore\") AS \"highScore\" "
                        + "FROM \"playerStats\" AS \"example_PlayerStats\" "
                        + "ORDER BY \"example_PlayerStats\".\"overallRating\" DESC";
        List<String> expectedQueryList = Arrays.asList(expectedQueryStr);
        compareQueryLists(expectedQueryList, engine.explain(TestQuery.ORDER_BY_DIMENSION_NOT_IN_SELECT.getQuery()));

        testQueryExecution(TestQuery.ORDER_BY_DIMENSION_NOT_IN_SELECT.getQuery());
    }
    */
@Test
public void testExplainComplicated() {
    Query query = TestQuery.COMPLICATED.getQuery();
    String expectedQueryStr1 = "SELECT COUNT(*) FROM " + "(SELECT \"example_PlayerStats\".\"overallRating\", " + "PARSEDATETIME(FORMATDATETIME(\"example_PlayerStats\".\"recordedDate\", 'yyyy-MM-dd'), 'yyyy-MM-dd') " + "FROM \"playerStats\" AS \"example_PlayerStats\" " + "LEFT OUTER JOIN \"countries\" AS \"example_PlayerStats_country_XXX\" " + "ON \"example_PlayerStats\".\"country_id\" = " + "\"example_PlayerStats_country_XXX\".\"id\" " + "WHERE \"example_PlayerStats_country_XXX\".\"iso_code\" " + "IN (:XXX) " + "GROUP BY \"example_PlayerStats\".\"overallRating\", " + "PARSEDATETIME(FORMATDATETIME(\"example_PlayerStats\".\"recordedDate\", 'yyyy-MM-dd'), 'yyyy-MM-dd') " + "HAVING MIN(\"example_PlayerStats\".\"lowScore\") > :XXX ) AS \"pagination_subquery\"";
    String expectedQueryStr2 = "SELECT MAX(\"example_PlayerStats\".\"highScore\") AS \"highScore\"," + "\"example_PlayerStats\".\"overallRating\" AS \"overallRating\"," + "PARSEDATETIME(FORMATDATETIME(" + "\"example_PlayerStats\".\"recordedDate\", 'yyyy-MM-dd'), " + "'yyyy-MM-dd') AS \"recordedDate\" " + "FROM \"playerStats\" AS \"example_PlayerStats\" " + "LEFT OUTER JOIN \"countries\" AS \"example_PlayerStats_country_XXX\" " + "ON \"example_PlayerStats\".\"country_id\" = " + "\"example_PlayerStats_country_XXX\".\"id\" " + "WHERE \"example_PlayerStats_country_XXX\".\"iso_code\" " + "IN (:XXX) " + "GROUP BY \"example_PlayerStats\".\"overallRating\", " + "PARSEDATETIME(FORMATDATETIME(" + "\"example_PlayerStats\".\"recordedDate\", 'yyyy-MM-dd'), 'yyyy-MM-dd') " + "HAVING MIN(\"example_PlayerStats\".\"lowScore\") > :XXX " + "ORDER BY MIN(\"example_PlayerStats\".\"lowScore\") DESC LIMIT 5 OFFSET 10";
    List<String> expectedQueryList = new ArrayList<>();
    expectedQueryList.add(expectedQueryStr1);
    expectedQueryList.add(expectedQueryStr2);
    compareQueryLists(expectedQueryList, engine.explain(query));
    testQueryExecution(TestQuery.COMPLICATED.getQuery());
}

Example 84 with Query

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

the class DruidExplainQueryTest method testExplainHavingMetricsAndDims.

/*
    @Test
    public void testExplainHavingDimsOnly() throws Exception {
        String expectedQueryStr =
                "SELECT DISTINCT \"example_PlayerStats\".\"overallRating\" AS \"overallRating\" "
                        + "FROM \"playerStats\" AS \"example_PlayerStats\" "
                        + "HAVING \"example_PlayerStats\".\"overallRating\" IS NOT NULL";
        compareQueryLists(expectedQueryStr, engine.explain(TestQuery.HAVING_DIMS_ONLY.getQuery()));

        //Druid does not allow HAVING on a column not in the GROUP BY list.
        //testQueryExecution(TestQuery.HAVING_DIMS_ONLY.getQuery());
    }
    */
@Test
public void testExplainHavingMetricsAndDims() throws Exception {
    Query query = TestQuery.HAVING_METRICS_AND_DIMS.getQuery();
    String expectedQueryStr = "SELECT MAX(\"example_PlayerStats\".\"highScore\") AS \"highScore\"," + "\"example_PlayerStats\".\"overallRating\" AS \"overallRating\" " + "FROM \"playerStats\" AS \"example_PlayerStats\" " + "GROUP BY \"example_PlayerStats\".\"overallRating\" " + "HAVING (\"example_PlayerStats\".\"overallRating\" IS NOT NULL " + "AND MAX(\"example_PlayerStats\".\"highScore\") > :XXX)";
    compareQueryLists(expectedQueryStr, engine.explain(query));
    testQueryExecution(TestQuery.HAVING_METRICS_AND_DIMS.getQuery());
}

Example 85 with Query

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

the class DruidExplainQueryTest method testNestedMetricWithAliasesQuery.

@Test
public void testNestedMetricWithAliasesQuery() {
    Query query = TestQuery.NESTED_METRIC_WITH_ALIASES_QUERY.getQuery();
    String queryStr = engine.explain(query).get(0);
    queryStr = repeatedWhitespacePattern.matcher(queryStr).replaceAll(" ");
    queryStr = queryStr.replaceAll(":[a-zA-Z0-9_]+", ":XXX");
    queryStr = queryStr.replaceAll("PlayerStats_\\d+", "PlayerStats_XXX");
    queryStr = queryStr.replaceAll("PlayerStats_country_\\d+", "PlayerStats_country_XXX");
    String expectedStr = getExpectedNestedMetricWithAliasesSQL(true).replace(BACKTICK, DOUBLE_QUOTE);
    assertEquals(expectedStr, queryStr);
    testQueryExecution(query);
}