@Nullable public Long sumMetric(final Task task, final DimFilter filter, final String metric) { // Do a query. TimeseriesQuery query = Druids.newTimeseriesQueryBuilder() .dataSource("test_ds") .filters(filter) .aggregators( ImmutableList.of( new LongSumAggregatorFactory(metric, metric) ) ).granularity(Granularities.ALL) .intervals("2000/3000") .build(); List<Result<TimeseriesResultValue>> results = task.getQueryRunner(query).run(QueryPlus.wrap(query), ImmutableMap.of()).toList(); if (results.isEmpty()) { return 0L; } else { return results.get(0).getValue().getLongMetric(metric); } } }
@Nullable public Long sumMetric(final Task task, final DimFilter filter, final String metric) { // Do a query. TimeseriesQuery query = Druids.newTimeseriesQueryBuilder() .dataSource("test_ds") .filters(filter) .aggregators( ImmutableList.of( new LongSumAggregatorFactory(metric, metric) ) ).granularity(Granularities.ALL) .intervals("2000/3000") .build(); List<Result<TimeseriesResultValue>> results = task.getQueryRunner(query).run(QueryPlus.wrap(query), ImmutableMap.of()).toList(); if (results.isEmpty()) { return 0L; } else { return results.get(0).getValue().getLongMetric(metric); } }
@Test public void testTimeseriesWithFilterOnNonExistentDimensionSkipBuckets() { TimeseriesQuery query = Druids.newTimeseriesQueryBuilder() .dataSource(QueryRunnerTestHelper.dataSource) .granularity(QueryRunnerTestHelper.dayGran) .filters("bobby", "billy") .intervals(QueryRunnerTestHelper.firstToThird) .aggregators(aggregatorFactoryList) .postAggregators(QueryRunnerTestHelper.addRowsIndexConstant) .context(ImmutableMap.of("skipEmptyBuckets", "true")) .descending(descending) .build(); List<Result<TimeseriesResultValue>> expectedResults = Collections.emptyList(); Iterable<Result<TimeseriesResultValue>> results = runner.run(QueryPlus.wrap(query), new HashMap<String, Object>()).toList(); assertExpectedResults(expectedResults, results); }
@Test public void testNullStringEquality() throws Exception { testQuery( "SELECT COUNT(*)\n" + "FROM druid.foo\n" + "WHERE NULLIF(dim2, 'a') = null", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .granularity(Granularities.ALL) .filters(EXPRESSION_FILTER("case_searched((\"dim2\" == 'a')," + (NullHandling.replaceWithDefault() ? "1" : "0") + ",(\"dim2\" == null))")) .aggregators(AGGS(new CountAggregatorFactory("a0"))) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), NullHandling.replaceWithDefault() ? // Matches everything but "abc" ImmutableList.of(new Object[]{5L}) : // null is not eqaual to null or any other value ImmutableList.of() ); }
@Test public void testCountStarOnView() throws Exception { testQuery( "SELECT COUNT(*) FROM druid.aview WHERE dim1_firstchar <> 'z'", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .filters(AND( SELECTOR("dim2", "a", null), NOT(SELECTOR("dim1", "z", new SubstringDimExtractionFn(0, 1))) )) .granularity(Granularities.ALL) .aggregators(AGGS(new CountAggregatorFactory("a0"))) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of( new Object[]{2L} ) ); }
@Test public void testTimeseriesWithMultiValueDimFilter1() { TimeseriesQuery query = Druids .newTimeseriesQueryBuilder() .dataSource(QueryRunnerTestHelper.dataSource) .granularity(QueryRunnerTestHelper.dayGran) .filters(QueryRunnerTestHelper.placementishDimension, "preferred") .intervals(QueryRunnerTestHelper.firstToThird) .aggregators(aggregatorFactoryList) .postAggregators(QueryRunnerTestHelper.addRowsIndexConstant) .descending(descending) .build(); TimeseriesQuery query1 = Druids .newTimeseriesQueryBuilder() .dataSource(QueryRunnerTestHelper.dataSource) .granularity(QueryRunnerTestHelper.dayGran) .intervals(QueryRunnerTestHelper.firstToThird) .aggregators(aggregatorFactoryList) .postAggregators(QueryRunnerTestHelper.addRowsIndexConstant) .descending(descending) .build(); Iterable<Result<TimeseriesResultValue>> expectedResults = runner.run(QueryPlus.wrap(query1), CONTEXT).toList(); Iterable<Result<TimeseriesResultValue>> actualResults = runner.run(QueryPlus.wrap(query), CONTEXT).toList(); TestHelper.assertExpectedResults(expectedResults, actualResults); }
@Test public void testCountStarOnCommonTableExpression() throws Exception { testQuery( "WITH beep (dim1_firstchar) AS (SELECT SUBSTRING(dim1, 1, 1) FROM foo WHERE dim2 = 'a')\n" + "SELECT COUNT(*) FROM beep WHERE dim1_firstchar <> 'z'", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .filters(AND( SELECTOR("dim2", "a", null), NOT(SELECTOR("dim1", "z", new SubstringDimExtractionFn(0, 1))) )) .granularity(Granularities.ALL) .aggregators(AGGS(new CountAggregatorFactory("a0"))) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of( new Object[]{2L} ) ); }
@Test public void testColumnIsNull() throws Exception { // Doesn't conform to the SQL standard, but it's how we do it. // This example is used in the sql.md doc. testQuery( "SELECT COUNT(*) FROM druid.foo WHERE dim2 IS NULL\n", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .granularity(Granularities.ALL) .filters(SELECTOR("dim2", null, null)) .aggregators(AGGS(new CountAggregatorFactory("a0"))) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of( new Object[]{NullHandling.replaceWithDefault() ? 3L : 2L} ) ); }
@Test public void testGroupByWithFilterMatchingNothing() throws Exception { // This query should actually return [0, null] rather than an empty result set, but it doesn't. // This test just "documents" the current behavior. testQuery( "SELECT COUNT(*), MAX(cnt) FROM druid.foo WHERE dim1 = 'foobar'", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .filters(SELECTOR("dim1", "foobar", null)) .granularity(Granularities.ALL) .aggregators(AGGS( new CountAggregatorFactory("a0"), new LongMaxAggregatorFactory("a1", "cnt") )) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of() ); }
@Test public void testCountStarWithFilterOnCastedString() throws Exception { testQuery( "SELECT COUNT(*) FROM druid.foo WHERE CAST(dim1 AS bigint) = 2", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .granularity(Granularities.ALL) .filters(NUMERIC_SELECTOR("dim1", "2", null)) .aggregators(AGGS(new CountAggregatorFactory("a0"))) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of( new Object[]{1L} ) ); }
@Test public void testCountStarWithLongColumnFiltersOnTwoPoints() throws Exception { testQuery( "SELECT COUNT(*) FROM druid.foo WHERE cnt = 1 OR cnt = 2", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .granularity(Granularities.ALL) .filters(IN("cnt", ImmutableList.of("1", "2"), null)) .aggregators(AGGS(new CountAggregatorFactory("a0"))) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of( new Object[]{6L} ) ); }
@Test public void testFilterOnDouble() throws Exception { testQuery( "SELECT COUNT(*) FROM druid.foo WHERE m2 = 1.0", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .granularity(Granularities.ALL) .aggregators(AGGS(new CountAggregatorFactory("a0"))) .filters(SELECTOR("m2", "1.0", null)) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of( new Object[]{1L} ) ); }
@Test public void testFilterOnFloat() throws Exception { testQuery( "SELECT COUNT(*) FROM druid.foo WHERE m1 = 1.0", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .granularity(Granularities.ALL) .aggregators(AGGS(new CountAggregatorFactory("a0"))) .filters(SELECTOR("m1", "1.0", null)) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of( new Object[]{1L} ) ); }
@Test public void testCountStarWithTimeAndDimFilter() throws Exception { testQuery( "SELECT COUNT(*) FROM druid.foo " + "WHERE dim2 <> 'a' " + "and __time BETWEEN TIMESTAMP '2000-01-01 00:00:00' AND TIMESTAMP '2000-12-31 23:59:59.999'", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Intervals.of("2000-01-01/2001-01-01"))) .filters(NOT(SELECTOR("dim2", "a", null))) .granularity(Granularities.ALL) .aggregators(AGGS(new CountAggregatorFactory("a0"))) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of( new Object[]{2L} ) ); }
@Test public void testCountStarWithBoundFilterSimplifyOnMetric() throws Exception { testQuery( "SELECT COUNT(*) FROM druid.foo WHERE 2.5 < m1 AND m1 < 3.5", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .granularity(Granularities.ALL) .filters(BOUND("m1", "2.5", "3.5", true, true, null, StringComparators.NUMERIC)) .aggregators(AGGS(new CountAggregatorFactory("a0"))) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of( new Object[]{1L} ) ); }
@Test public void testCountStarWithBoundFilterSimplifyOr() throws Exception { testQuery( "SELECT COUNT(*) FROM druid.foo WHERE (dim1 >= 'a' and dim1 < 'b') OR dim1 = 'ab'", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .granularity(Granularities.ALL) .filters(BOUND("dim1", "a", "b", false, true, null, StringComparators.LEXICOGRAPHIC)) .aggregators(AGGS(new CountAggregatorFactory("a0"))) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of( new Object[]{1L} ) ); }
@Test public void testGroupByWithFilterMatchingNothingWithGroupByLiteral() throws Exception { testQuery( "SELECT COUNT(*), MAX(cnt) FROM druid.foo WHERE dim1 = 'foobar' GROUP BY 'dummy'", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .filters(SELECTOR("dim1", "foobar", null)) .granularity(Granularities.ALL) .aggregators(AGGS( new CountAggregatorFactory("a0"), new LongMaxAggregatorFactory("a1", "cnt") )) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of() ); }
@Test public void testCountStarWithBoundFilterSimplifyAnd() throws Exception { testQuery( "SELECT COUNT(*) FROM druid.foo WHERE (dim1 >= 'a' and dim1 < 'b') and dim1 = 'abc'", ImmutableList.of( Druids.newTimeseriesQueryBuilder() .dataSource(CalciteTests.DATASOURCE1) .intervals(QSS(Filtration.eternity())) .granularity(Granularities.ALL) .filters(SELECTOR("dim1", "abc", null)) .aggregators(AGGS(new CountAggregatorFactory("a0"))) .context(TIMESERIES_CONTEXT_DEFAULT) .build() ), ImmutableList.of( new Object[]{1L} ) ); }
public TimeseriesQuery withDimFilter(DimFilter dimFilter) { return Druids.TimeseriesQueryBuilder.copy(this).filters(dimFilter).build(); }
public static TimeseriesQueryBuilder copy(TimeseriesQuery query) { return new TimeseriesQueryBuilder() .dataSource(query.getDataSource()) .intervals(query.getQuerySegmentSpec()) .descending(query.isDescending()) .virtualColumns(query.getVirtualColumns()) .filters(query.getDimensionsFilter()) .granularity(query.getGranularity()) .aggregators(query.getAggregatorSpecs()) .postAggregators(query.getPostAggregatorSpecs()) .limit(query.getLimit()) .context(query.getContext()); }