Github user kunal642 commented on a diff in the pull request:
https://github.com/apache/carbondata/pull/1856#discussion_r174066445
--- Diff:
integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeseriesTableSelection.scala
---
@@ -99,124 +113,763 @@ class TestTimeseriesTableSelection extends QueryTest
with BeforeAndAfterAll {
sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' into
table mainTable")
}
- test("test PreAggregate table selection 1") {
- val df = sql("select mytime from mainTable group by mytime")
+ test("test timeseries table selection 1") {
+ val df = sql("SELECT mytime FROM mainTable GROUP BY mytime")
preAggTableValidator(df.queryExecution.analyzed, "maintable")
}
- test("test PreAggregate table selection 2") {
- val df = sql("select timeseries(mytime,'hour') from mainTable group by
timeseries(mytime,'hour')")
+ test("test timeseries table selection 2") {
+ val df = sql("SELECT TIMESERIES(mytime,'hour') FROM mainTable GROUP BY
TIMESERIES(mytime,'hour')")
preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
}
- test("test PreAggregate table selection 3") {
- val df = sql("select timeseries(mytime,'milli') from mainTable group
by timeseries(mytime,'milli')")
- preAggTableValidator(df.queryExecution.analyzed, "maintable")
+ test("test timeseries table selection 3: No enum constant MILLI") {
+ val e = intercept[Exception] {
+ val df = sql(
+ """
+ | SELECT TIMESERIES(mytime,'milli')
+ | FROM mainTable
+ | GROUP BY TIMESERIES(mytime,'milli')
+ """.stripMargin)
+ preAggTableValidator(df.queryExecution.analyzed, "maintable")
+ df.show()
+ }
+ assert(e.getMessage.contains(
+ "No enum constant
org.apache.carbondata.core.preagg.TimeSeriesFunctionEnum.MILLI"))
}
- test("test PreAggregate table selection 4") {
- val df = sql("select timeseries(mytime,'year') from mainTable group by
timeseries(mytime,'year')")
+ test("test timeseries table selection 4") {
+ val df = sql("SELECT TIMESERIES(mytime,'year') FROM mainTable GROUP BY
TIMESERIES(mytime,'year')")
preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_year")
}
- test("test PreAggregate table selection 5") {
- val df = sql("select timeseries(mytime,'day') from mainTable group by
timeseries(mytime,'day')")
+ test("test timeseries table selection 5") {
+ val df = sql("SELECT TIMESERIES(mytime,'day') FROM mainTable GROUP BY
TIMESERIES(mytime,'day')")
preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_day")
}
- test("test PreAggregate table selection 6") {
- val df = sql("select timeseries(mytime,'month') from mainTable group
by timeseries(mytime,'month')")
+ test("test timeseries table selection 6") {
+ val df = sql("SELECT TIMESERIES(mytime,'month') FROM mainTable GROUP
BY TIMESERIES(mytime,'month')")
preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_month")
}
- test("test PreAggregate table selection 7") {
- val df = sql("select timeseries(mytime,'minute') from mainTable group
by timeseries(mytime,'minute')")
+ test("test timeseries table selection 7") {
+ val df = sql("SELECT TIMESERIES(mytime,'minute') FROM mainTable GROUP
BY TIMESERIES(mytime,'minute')")
preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_minute")
}
- test("test PreAggregate table selection 8") {
- val df = sql("select timeseries(mytime,'second') from mainTable group
by timeseries(mytime,'second')")
+ test("test timeseries table selection 8") {
+ val df = sql("SELECT TIMESERIES(mytime,'second') FROM mainTable GROUP
BY TIMESERIES(mytime,'second')")
preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_second")
}
- test("test PreAggregate table selection 9") {
- val df = sql("select timeseries(mytime,'hour') from mainTable where
timeseries(mytime,'hour')='x' group by timeseries(mytime,'hour')")
- preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_hour")
+ test("test timeseries table selection 9") {
+ val df = sql(
+ """
+ | SELECT TIMESERIES(mytime,'hour')
+ | FROM mainTable
+ | WHERE TIMESERIES(mytime,'hour')='x'
+ | GROUP BY TIMESERIES(mytime,'hour')
+ """.stripMargin)
+ preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
}
- test("test PreAggregate table selection 10") {
- val df = sql("select timeseries(mytime,'hour') from mainTable where
timeseries(mytime,'hour')='x' group by timeseries(mytime,'hour') order by
timeseries(mytime,'hour')")
- preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_hour")
+ test("test timeseries table selection 10") {
+ val df = sql(
+ """
+ | SELECT TIMESERIES(mytime,'hour')
+ | FROM mainTable
+ | WHERE TIMESERIES(mytime,'hour')='x'
+ | GROUP BY TIMESERIES(mytime,'hour')
+ | ORDER BY TIMESERIES(mytime,'hour')
+ """.stripMargin)
+ preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
+ }
+
+ test("test timeseries table selection 11") {
+ val df = sql(
+ """
+ | SELECT TIMESERIES(mytime,'hour'),SUM(age)
+ | FROM mainTable
+ | WHERE TIMESERIES(mytime,'hour')='x'
+ | GROUP BY TIMESERIES(mytime,'hour')
+ | ORDER BY TIMESERIES(mytime,'hour')
+ """.stripMargin)
+ preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
}
- test("test PreAggregate table selection 11") {
- val df = sql("select timeseries(mytime,'hour'),sum(age) from mainTable
where timeseries(mytime,'hour')='x' group by timeseries(mytime,'hour') order by
timeseries(mytime,'hour')")
- preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_hour")
+ test("test timeseries table selection 12") {
+ val df = sql(
+ """
+ | SELECT TIMESERIES(mytime,'hour') AS hourlevel,SUM(age) AS SUM
+ | FROM mainTable
+ | WHERE TIMESERIES(mytime,'hour')='x'
+ | GROUP BY TIMESERIES(mytime,'hour')
+ | ORDER BY TIMESERIES(mytime,'hour')
+ """.stripMargin)
+ preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
}
- test("test PreAggregate table selection 12") {
- val df = sql("select timeseries(mytime,'hour')as hourlevel,sum(age) as
sum from mainTable where timeseries(mytime,'hour')='x' group by
timeseries(mytime,'hour') order by timeseries(mytime,'hour')")
- preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_hour")
+ test("test timeseries table selection 13") {
+ val df = sql(
+ """
+ | SELECT TIMESERIES(mytime,'hour')as hourlevel,SUM(age) AS SUM
+ | FROM mainTable
+ | WHERE TIMESERIES(mytime,'hour')='x' AND name='vishal'
+ | GROUP BY TIMESERIES(mytime,'hour')
+ | ORDER BY TIMESERIES(mytime,'hour')
+ """.stripMargin)
+ preAggTableValidator(df.queryExecution.analyzed, "maintable")
}
- test("test PreAggregate table selection 13") {
- val df = sql("select timeseries(mytime,'hour')as hourlevel,sum(age) as
sum from mainTable where timeseries(mytime,'hour')='x' and name='vishal' group
by timeseries(mytime,'hour') order by timeseries(mytime,'hour')")
- preAggTableValidator(df.queryExecution.analyzed,"maintable")
+ test("test timeseries table selection 14: TIMESERIES(mytime,'hour')
match") {
+ val df = sql(
+ """
+ | SELECT TIMESERIES(mytime,'hour')
+ | FROM mainTable
+ | WHERE TIMESERIES(mytime,'hour')='2016-02-23 09:00:00'
+ | GROUP BY TIMESERIES(mytime,'hour')
+ """.stripMargin)
+ preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
+ checkAnswer(df, Row(Timestamp.valueOf("2016-02-23 09:00:00.0")))
}
- test("test timeseries table selection 14: Granularity only support 1 and
throw Exception") {
- val e = intercept[MalformedCarbonCommandException] {
- sql(
- s"""
- | CREATE DATAMAP agg3_second ON TABLE mainTable
- | USING '$timeSeries'
- | DMPROPERTIES (
- | 'EVENT_TIME'='dataTime',
- | 'HOUR_GRANULARITY'='2')
- | AS SELECT dataTime, SUM(age) FROM mainTable
- | GROUP BY dataTime
- """.stripMargin)
- }
- assert(e.getMessage.contains("Granularity only support 1"))
- }
-
- test("test timeseries table selection 15: Granularity only support 1 and
throw Exception") {
- val e = intercept[MalformedCarbonCommandException] {
- sql(
- s"""
- | CREATE DATAMAP agg3_second ON TABLE mainTable
- | USING '$timeSeries'
- | DMPROPERTIES (
- | 'EVENT_TIME'='dataTime',
- | 'HOUR_GRANULARITY'='1.5')
- | AS SELECT dataTime, SUM(age) FROM mainTable
- | GROUP BY dataTime
- """.stripMargin)
- }
- assert(e.getMessage.contains("Granularity only support 1"))
- }
-
- test("test timeseries table selection 16: Granularity only support 1 and
throw Exception") {
- val e = intercept[MalformedCarbonCommandException] {
- sql(
- s"""
- | CREATE DATAMAP agg3_second ON TABLE mainTable
- | USING '$timeSeries'
- | DMPROPERTIES (
- | 'EVENT_TIME'='dataTime',
- | 'HOUR_GRANULARITY'='-1')
- | AS SELECT dataTime, SUM(age) FROM mainTable
- | GROUP BY dataTime
- """.stripMargin)
- }
- assert(e.getMessage.contains("Granularity only support 1"))
+ test("test timeseries table selection 15: TIMESERIES(mytime,'hour') not
match") {
+ val df = sql(
+ """
+ | SELECT TIMESERIES(mytime,'hour')
+ | FROM mainTable
+ | WHERE TIMESERIES(mytime,'hour')='2016-02-23 09:01:00'
+ | GROUP BY TIMESERIES(mytime,'hour')
+ """.stripMargin)
+ preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
+ checkExistence(df, false, "2016-02-23 09:00:00", "2016-02-23 09:01:00")
+ }
+
+ test("test timeseries table selection 16: TIMESERIES(mytime,'minute')
match") {
+ checkExistence(sql("SELECT * FROM mainTable"), true,
+ "2016-02-23 09:01:30", "2016-02-23 09:02:40")
+ checkExistence(sql("SELECT * FROM mainTable"), false,
+ "2016-02-23 09:02:00", "2016-02-23 09:01:00")
+ val df = sql(
+ """
+ |SELECT TIMESERIES(mytime,'minute')
+ |FROM mainTable
+ |GROUP BY TIMESERIES(mytime,'minute')
+ """.stripMargin)
+ preAggTableValidator(df.queryExecution.analyzed,
"maintable_agg0_minute")
+ checkExistence(df, true, "2016-02-23 09:02:00", "2016-02-23 09:01:00")
+ checkAnswer(df,
+ Seq(Row(Timestamp.valueOf("2016-02-23 09:02:00.0")),
+ Row(Timestamp.valueOf("2016-02-23 09:01:00.0"))))
+
+ val df2 = sql(
+ """
+ | SELECT
+ | TIMESERIES(mytime,'minute')as minutelevel,
+ | SUM(age) AS SUM
+ | FROM mainTable
+ | WHERE TIMESERIES(mytime,'minute')='2016-02-23 09:01:00'
+ | GROUP BY TIMESERIES(mytime,'minute')
+ | ORDER BY TIMESERIES(mytime,'minute')
+ """.stripMargin)
+ preAggTableValidator(df2.queryExecution.analyzed,
"maintable_agg0_minute")
+ checkAnswer(df2, Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"),
60)))
+ }
+
+ test("test timeseries table selection 17: TIMESERIES(mytime,'minute')
not match pre agg") {
+ val df = sql(
+ """
+ | SELECT
+ | TIMESERIES(mytime,'minute')as minutelevel,
+ | SUM(age) AS SUM
+ | FROM mainTable
+ | WHERE TIMESERIES(mytime,'minute')='2016-02-23 09:01:00' AND
name='vishal'
+ | GROUP BY TIMESERIES(mytime,'minute')
+ | ORDER BY TIMESERIES(mytime,'minute')
+ """.stripMargin)
+ checkAnswer(df, Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 10)))
+ preAggTableValidator(df.queryExecution.analyzed, "maintable")
+ }
+
+ test("test timeseries table selection 18: select with many GROUP BY AND
one filter") {
+ val df = sql(
+ """
+ | SELECT
+ | TIMESERIES(mytime,'year') AS yearLevel,
+ | TIMESERIES(mytime,'month') AS monthLevel,
+ | TIMESERIES(mytime,'day') AS dayLevel,
+ | TIMESERIES(mytime,'hour') AS hourLevel,
+ | TIMESERIES(mytime,'minute') AS minuteLevel,
+ | TIMESERIES(mytime,'second') AS secondLevel,
+ | SUM(age) AS SUM
+ | FROM mainTable
+ | WHERE TIMESERIES(mytime,'minute')='2016-02-23 09:01:00'
+ | GROUP BY
+ | TIMESERIES(mytime,'year'),
+ | TIMESERIES(mytime,'month'),
+ | TIMESERIES(mytime,'day'),
+ | TIMESERIES(mytime,'hour'),
+ | TIMESERIES(mytime,'minute'),
+ | TIMESERIES(mytime,'second')
+ | ORDER BY
+ | TIMESERIES(mytime,'year'),
+ | TIMESERIES(mytime,'month'),
+ | TIMESERIES(mytime,'day'),
+ | TIMESERIES(mytime,'hour'),
+ | TIMESERIES(mytime,'minute'),
+ | TIMESERIES(mytime,'second')
+ """.stripMargin)
+
+ checkExistence(df, true,
+ "2016-01-01 00:00:00",
+ "2016-02-01 00:00:00",
+ "2016-02-23 09:00:00",
+ "2016-02-23 09:01:00",
+ "2016-02-23 09:01:50",
+ "30"
+ )
+ }
+
+ test("test timeseries table selection 19: select with many GROUP BY AND
many filter") {
+ val df = sql(
+ """
+ | SELECT
+ | TIMESERIES(mytime,'year') AS yearLevel,
+ | TIMESERIES(mytime,'month') AS monthLevel,
+ | TIMESERIES(mytime,'day') AS dayLevel,
+ | TIMESERIES(mytime,'hour') AS hourLevel,
+ | TIMESERIES(mytime,'minute') AS minuteLevel,
+ | TIMESERIES(mytime,'second') AS secondLevel,
+ | SUM(age) AS SUM
+ | FROM mainTable
+ | WHERE
+ | TIMESERIES(mytime,'second')='2016-02-23 09:01:50' and
+ | TIMESERIES(mytime,'minute')='2016-02-23 09:01:00' and
+ | TIMESERIES(mytime,'hour')='2016-02-23 09:00:00' and
+ | TIMESERIES(mytime,'month')='2016-02-01 00:00:00' and
+ | TIMESERIES(mytime,'year')='2016-01-01 00:00:00'
+ | GROUP BY
+ | TIMESERIES(mytime,'year'),
+ | TIMESERIES(mytime,'month'),
+ | TIMESERIES(mytime,'day'),
+ | TIMESERIES(mytime,'hour'),
+ | TIMESERIES(mytime,'minute'),
+ | TIMESERIES(mytime,'second')
+ | ORDER BY
+ | TIMESERIES(mytime,'year'),
+ | TIMESERIES(mytime,'month'),
+ | TIMESERIES(mytime,'day'),
+ | TIMESERIES(mytime,'hour'),
+ | TIMESERIES(mytime,'minute'),
+ | TIMESERIES(mytime,'second')
+ """.stripMargin)
+
+ checkExistence(df, true,
+ "2016-01-01 00:00:00",
+ "2016-02-01 00:00:00",
+ "2016-02-23 09:00:00",
+ "2016-02-23 09:01:00",
+ "2016-02-23 09:01:50",
+ "30"
+ )
+ }
+
+ test("test timeseries table selection 20: filter < AND >") {
--- End diff --
Filter scenarios are already covered in
TestTimeSeriesMatchStrategySuite.scala. Please remove duplicate scenarios
---
