[CARBONDATA-2073][CARBONDATA-1516][Tests] Add test cases for timeseries datamape
This closes #1856 Project: http://git-wip-us.apache.org/repos/asf/carbondata/repo Commit: http://git-wip-us.apache.org/repos/asf/carbondata/commit/0e6fe6ca Tree: http://git-wip-us.apache.org/repos/asf/carbondata/tree/0e6fe6ca Diff: http://git-wip-us.apache.org/repos/asf/carbondata/diff/0e6fe6ca Branch: refs/heads/master Commit: 0e6fe6caea44531d7f4fe8b349ea3245f2ac0086 Parents: 8bda43b Author: xubo245 <[email protected]> Authored: Fri Feb 2 11:22:20 2018 +0800 Committer: Jacky Li <[email protected]> Committed: Thu Mar 29 20:58:32 2018 +0800 ---------------------------------------------------------------------- .../src/test/resources/timeseriestest.csv | 12 +- .../timeseries/TestTimeSeriesCreateTable.scala | 369 ++++++--- .../timeseries/TestTimeSeriesDropSuite.scala | 193 ++++- .../TestTimeSeriesMatchStrategySuite.scala | 401 ++++++++++ .../TestTimeSeriesUnsupportedSuite.scala | 265 ++++++ .../timeseries/TestTimeseriesDataLoad.scala | 384 +++++++-- .../TestTimeseriesTableSelection.scala | 798 +++++++++++++++++-- .../testsuite/sortcolumns/TestSortColumns.scala | 7 +- .../apache/spark/sql/test/util/QueryTest.scala | 10 + .../command/timeseries/TimeSeriesUtil.scala | 3 +- .../apache/spark/util/CarbonCommandSuite.scala | 3 - 11 files changed, 2139 insertions(+), 306 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/carbondata/blob/0e6fe6ca/integration/spark-common-test/src/test/resources/timeseriestest.csv ---------------------------------------------------------------------- diff --git a/integration/spark-common-test/src/test/resources/timeseriestest.csv b/integration/spark-common-test/src/test/resources/timeseriestest.csv index 1674ac9..6a0c433 100644 --- a/integration/spark-common-test/src/test/resources/timeseriestest.csv +++ b/integration/spark-common-test/src/test/resources/timeseriestest.csv @@ -1,7 +1,7 @@ mytime,name,age -2016-2-23 01:01:30,vishal,10 -2016-2-23 01:01:40,kunal,20 -2016-2-23 01:01:50,shahid,30 -2016-2-23 01:02:30,kk,40 -2016-2-23 01:02:40,rahul,50 -2016-2-23 01:02:50,ravi,50 \ No newline at end of file +2016-2-23 09:01:30,vishal,10 +2016-2-23 09:01:40,kunal,20 +2016-2-23 09:01:50,shahid,30 +2016-2-23 09:02:30,kk,40 +2016-2-23 09:02:40,rahul,50 +2016-2-23 09:02:50,ravi,50 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/carbondata/blob/0e6fe6ca/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesCreateTable.scala ---------------------------------------------------------------------- diff --git a/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesCreateTable.scala b/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesCreateTable.scala index e8e8f79..d68195c 100644 --- a/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesCreateTable.scala +++ b/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesCreateTable.scala @@ -18,17 +18,26 @@ package org.apache.carbondata.integration.spark.testsuite.timeseries import org.apache.spark.sql.AnalysisException import org.apache.spark.sql.test.util.QueryTest -import org.scalatest.BeforeAndAfterAll +import org.scalatest.{BeforeAndAfterAll, BeforeAndAfterEach} import org.apache.carbondata.common.exceptions.sql.{MalformedCarbonCommandException, MalformedDataMapCommandException} import org.apache.carbondata.core.metadata.schema.datamap.DataMapClassProvider.TIMESERIES +import org.apache.carbondata.core.constants.CarbonCommonConstants +import org.apache.carbondata.core.util.CarbonProperties -class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll { +class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll with BeforeAndAfterEach{ val timeSeries = TIMESERIES.toString + var timestampFormat: String = _ override def beforeAll: Unit = { + timestampFormat = CarbonProperties.getInstance() + .getProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, + CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) sql("DROP TABLE IF EXISTS mainTable") + CarbonProperties.getInstance() + .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, + CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) sql("CREATE TABLE mainTable(dataTime timestamp, name string, city string, age int) STORED BY 'org.apache.carbondata.format'") sql( s""" @@ -82,121 +91,181 @@ class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll { """.stripMargin) } + override def afterEach(): Unit = { + dropDataMaps("mainTable", "agg1_second", "agg1_minute", + "agg1_hour", "agg1_day", "agg1_month", "agg1_year") + } + test("test timeseries create table 1") { checkExistence(sql("DESCRIBE FORMATTED mainTable_agg0_second"), true, "maintable_agg0_second") - sql("drop datamap agg0_second on table mainTable") + sql("DROP DATAMAP agg0_second ON TABLE mainTable") } test("test timeseries create table 2") { checkExistence(sql("DESCRIBE FORMATTED mainTable_agg0_hour"), true, "maintable_agg0_hour") - sql("drop datamap agg0_hour on table mainTable") + sql("DROP DATAMAP agg0_hour ON TABLE mainTable") } + test("test timeseries create table 3") { checkExistence(sql("DESCRIBE FORMATTED maintable_agg0_day"), true, "maintable_agg0_day") - sql("drop datamap agg0_day on table mainTable") + sql("DROP DATAMAP agg0_day ON TABLE mainTable") } + test("test timeseries create table 4") { checkExistence(sql("DESCRIBE FORMATTED mainTable_agg0_month"), true, "maintable_agg0_month") - sql("drop datamap agg0_month on table mainTable") + sql("DROP DATAMAP agg0_month ON TABLE mainTable") } + test("test timeseries create table 5") { checkExistence(sql("DESCRIBE FORMATTED mainTable_agg0_year"), true, "maintable_agg0_year") - sql("drop datamap agg0_year on table mainTable") + sql("DROP DATAMAP agg0_year ON TABLE mainTable") } - test("test timeseries create table 6") { - intercept[Exception] { + test("test timeseries create table 6: TIMESERIES should define time granularity") { + sql("DROP DATAMAP IF EXISTS agg0_second ON TABLE mainTable") + val e = intercept[MalformedCarbonCommandException] { sql( - s""" - | CREATE DATAMAP agg0_second ON TABLE mainTable - | USING '$timeSeries' - | DMPROPERTIES ( - | 'EVENT_TIME'='dataTime', - | 'SEC_GRANULARITY'='1') - | AS SELECT dataTime, SUM(age) FROM mainTable - | GROUP BY dataTime + s"""CREATE DATAMAP agg0_second ON TABLE mainTable USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='dataTime', + | 'SEC_GRANULARITY'='1') + |AS SELECT dataTime, SUM(age) FROM mainTable + |GROUP BY dataTime """.stripMargin) } + assert(e.getMessage.contains("TIMESERIES should define time granularity")) } - test("test timeseries create table 7") { - intercept[Exception] { + test("test timeseries create table 7: Granularity only support 1") { + sql("DROP DATAMAP IF EXISTS agg0_second ON TABLE mainTable") + val e = intercept[MalformedDataMapCommandException] { sql( s""" | CREATE DATAMAP agg0_second ON TABLE mainTable | USING '$timeSeries' | DMPROPERTIES ( | 'EVENT_TIME'='dataTime', - | 'SECOND_GRANULARITY'='2') + | 'DAY_GRANULARITY'='1.5') | AS SELECT dataTime, SUM(age) FROM mainTable | GROUP BY dataTime """.stripMargin) } + assert(e.getMessage.equals("Granularity only support 1")) } - test("test timeseries create table 8") { - intercept[Exception] { + test("test timeseries create table 8: Granularity only support 1") { + dropDataMaps("mainTable", "agg1_hour") + val e = intercept[MalformedCarbonCommandException] { sql( - s""" - | CREATE DATAMAP agg0_second ON TABLE mainTable - | USING '$timeSeries' - | DMPROPERTIES ( - | 'EVENT_TIME'='dataTime', - | 'SECOND_GRANULARITY'='1') - | AS SELECT dataTime, SUM(age) FROM mainTable - | GROUP BY dataTime - """.stripMargin) + s"""CREATE DATAMAP agg1_hour ON TABLE mainTable USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='dataTime', + | 'HOUR_GRANULARITY'='hour=-2') + |AS SELECT dataTime, SUM(age) FROM mainTable + |GROUP BY dataTime + """.stripMargin) + } + assert(e.getMessage.contains("Granularity only support ")) + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), false, "maintable_agg1_hour") + } + + test("test timeseries create table 9: SECOND_GRANULARITY is null") { + sql("DROP DATAMAP IF EXISTS agg1 ON TABLE mainTable") + val e = intercept[MalformedCarbonCommandException] { sql( - s""" - | CREATE DATAMAP agg0_second ON TABLE mainTable - | USING '$timeSeries' - | DMPROPERTIES ( - | 'EVENT_TIME'='dataTime', - | 'SECOND_GRANULARITY'='1') - | AS SELECT dataTime, SUM(age) FROM mainTable - | GROUP BY dataTime - """.stripMargin) + s"""CREATE DATAMAP agg0_hour ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='dataTime', + | 'HOUR_GRANULARITY'='') + |AS SELECT dataTime, SUM(age) FROM mainTable + |GROUP BY dataTime + """.stripMargin) } + assert(e.getMessage.contains("Granularity only support 1")) } - test("test timeseries create table 9") { - intercept[Exception] { + test("test timeseries create table 10: Table already exists in database") { + val e = intercept[MalformedDataMapCommandException] { + sql( + s"""CREATE DATAMAP agg1_hour ON TABLE mainTable USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='dataTime', + | 'HOUR_GRANULARITY'='1') + |AS SELECT dataTime, SUM(age) FROM mainTable + |GROUP BY dataTime + """.stripMargin) + sql( + s"""CREATE DATAMAP agg1_hour 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( + "DataMap name 'agg1_hour' already exist")) + } + + test("test timeseries create table 11: don't support create timeseries table on non timestamp") { + sql("DROP DATAMAP IF EXISTS agg0_hour ON TABLE mainTable") + val e = intercept[MalformedCarbonCommandException] { sql( s""" - | CREATE DATAMAP agg0_second ON TABLE mainTable + | CREATE DATAMAP agg0_hour ON TABLE mainTable | USING '$timeSeries' | DMPROPERTIES ( | 'EVENT_TIME'='name', - | 'SECOND_GRANULARITY'='1') + | 'HOUR_GRANULARITY'='1') | AS SELECT dataTime, SUM(age) FROM mainTable | GROUP BY dataTime """.stripMargin) } + assert(e.getMessage.equals("Timeseries event time is only supported on Timestamp column")) } - test("test timeseries create table 10") { - intercept[Exception] { + test("test timeseries create table 12: Time series column dataTime does not exists in select") { + sql("DROP DATAMAP IF EXISTS agg0_hour ON TABLE mainTable") + val e = intercept[MalformedCarbonCommandException] { sql( s""" - | CREATE DATAMAP agg0_second ON TABLE mainTable + | CREATE DATAMAP agg0_hour ON TABLE mainTable | USING '$timeSeries' | DMPROPERTIES ( - | 'EVENT_TIME'='name', - | 'SECOND_GRANULARITY'='1') - | AS SELECT dataTime, SUM(age) FROM mainTable - | GROUP BY dataTime - """.stripMargin) + | 'EVENT_TIME'='dataTime', + | 'HOUR_GRANULARITY'='1') + | AS SELECT name, SUM(age) FROM mainTable + | GROUP BY name + """.stripMargin) + } + assert(e.getMessage.equals("Time series column dataTime does not exists in select")) + } + + test("test timeseries create table 13: don't support create timeseries table on non timestamp") { + sql("DROP DATAMAP IF EXISTS agg0_hour ON TABLE mainTable") + val e = intercept[MalformedCarbonCommandException] { + sql( + s"""CREATE DATAMAP agg0_hour ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='name', + | 'HOUR_GRANULARITY'='1') + |AS SELECT name, SUM(age) FROM mainTable + |GROUP BY name + """.stripMargin) } + assert(e.getMessage.contains("Timeseries event time is only supported on Timestamp column")) } - test("test timeseries create table 11: USING") { + test("test timeseries create table 14: USING") { val e: Exception = intercept[MalformedDataMapCommandException] { sql( - """CREATE DATAMAP agg1 ON TABLE mainTable + """CREATE DATAMAP agg0_hour ON TABLE mainTable | USING 'abc' | DMPROPERTIES ( | 'EVENT_TIME'='dataTime', - | 'SECOND_GRANULARITY'='1') + | 'HOUR_GRANULARITY'='1') | AS SELECT dataTime, SUM(age) FROM mainTable | GROUP BY dataTime """.stripMargin) @@ -204,14 +273,14 @@ class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll { assert(e.getMessage.equals("DataMap 'abc' not found")) } - test("test timeseries create table 12: USING and catch MalformedCarbonCommandException") { + test("test timeseries create table 15: USING and catch MalformedCarbonCommandException") { val e: Exception = intercept[MalformedCarbonCommandException] { sql( - """CREATE DATAMAP agg1 ON TABLE mainTable + """CREATE DATAMAP agg0_hour ON TABLE mainTable | USING 'abc' | DMPROPERTIES ( | 'EVENT_TIME'='dataTime', - | 'SECOND_GRANULARITY'='1') + | 'HOUR_GRANULARITY'='1') | AS SELECT dataTime, SUM(age) FROM mainTable | GROUP BY dataTime """.stripMargin) @@ -219,12 +288,12 @@ class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll { assert(e.getMessage.equals("DataMap 'abc' not found")) } - test("test timeseries create table 13: Only one granularity level can be defined 1") { - sql("DROP DATAMAP IF EXISTS agg0_second ON TABLE mainTable") + test("test timeseries create table 16: Only one granularity level can be defined 1") { + sql("DROP DATAMAP IF EXISTS agg0_hour ON TABLE mainTable") val e: Exception = intercept[MalformedCarbonCommandException] { sql( s""" - | CREATE DATAMAP agg0_second ON TABLE mainTable + | CREATE DATAMAP agg0_hour ON TABLE mainTable | USING '$timeSeries' | DMPROPERTIES ( | 'EVENT_TIME'='dataTime', @@ -241,12 +310,12 @@ class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll { assert(e.getMessage.equals("Only one granularity level can be defined")) } - test("test timeseries create table 14: Only one granularity level can be defined 2") { - sql("DROP DATAMAP IF EXISTS agg0_second ON TABLE mainTable") + test("test timeseries create table 17: Only one granularity level can be defined 2") { + sql("DROP DATAMAP IF EXISTS agg0_hour ON TABLE mainTable") val e: Exception = intercept[MalformedDataMapCommandException] { sql( s""" - | CREATE DATAMAP agg0_second ON TABLE mainTable + | CREATE DATAMAP agg0_hour ON TABLE mainTable | USING '$timeSeries' | DMPROPERTIES ( | 'EVENT_TIME'='dataTime', @@ -259,12 +328,12 @@ class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll { assert(e.getMessage.equals("Only one granularity level can be defined")) } - test("test timeseries create table 15: Only one granularity level can be defined 3") { - sql("DROP DATAMAP IF EXISTS agg0_second ON TABLE mainTable") + test("test timeseries create table 18: Only one granularity level can be defined 3") { + sql("DROP DATAMAP IF EXISTS agg0_hour ON TABLE mainTable") val e: Exception = intercept[MalformedDataMapCommandException] { sql( s""" - | CREATE DATAMAP agg0_second ON TABLE mainTable + | CREATE DATAMAP agg0_hour ON TABLE mainTable | USING '$timeSeries' | DMPROPERTIES ( | 'EVENT_TIME'='dataTime', @@ -277,46 +346,12 @@ class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll { assert(e.getMessage.equals("Only one granularity level can be defined")) } - test("test timeseries create table 16: Granularity only support 1") { - sql("DROP DATAMAP IF EXISTS agg0_second ON TABLE mainTable") - val e = intercept[MalformedDataMapCommandException] { - sql( - s""" - | CREATE DATAMAP agg0_second ON TABLE mainTable - | USING '$timeSeries' - | DMPROPERTIES ( - | 'EVENT_TIME'='dataTime', - | 'DAY_GRANULARITY'='2') - | AS SELECT dataTime, SUM(age) FROM mainTable - | GROUP BY dataTime - """.stripMargin) - } - assert(e.getMessage.equals("Granularity only support 1")) - } - - test("test timeseries create table 17: Granularity only support 1 and throw Exception") { - sql("DROP DATAMAP IF EXISTS agg0_second ON TABLE mainTable") - val e = intercept[MalformedCarbonCommandException] { - sql( - s""" - | CREATE DATAMAP agg0_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.equals("Granularity only support 1")) - } - - test("test timeseries create table 18: timeSeries should define time granularity") { - sql("DROP DATAMAP IF EXISTS agg0_second ON TABLE mainTable") + test("test timeseries create table 19: timeSeries should define time granularity") { + sql("DROP DATAMAP IF EXISTS agg0_hour ON TABLE mainTable") val e = intercept[MalformedDataMapCommandException] { sql( s""" - | CREATE DATAMAP agg0_second ON TABLE mainTable + | CREATE DATAMAP agg0_hour ON TABLE mainTable | USING '$timeSeries' | DMPROPERTIES ( | 'EVENT_TIME'='dataTime') @@ -327,7 +362,7 @@ class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll { assert(e.getMessage.equals(s"$timeSeries should define time granularity")) } - test("test timeseries create table 19: should support if not exists") { + test("test timeseries create table 20: should support if not exists, create when same table exists") { sql("DROP DATAMAP IF EXISTS agg1 ON TABLE mainTable") sql( @@ -354,6 +389,22 @@ class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll { checkExistence(sql("DESC FORMATTED mainTable_agg1"), true, "maintable_age_sum") } + test("test timeseries create table 32: should support if not exists, create when same table not exists") { + sql("DROP DATAMAP IF EXISTS agg1_year ON TABLE mainTable") + sql( + s""" + |CREATE DATAMAP if not exists agg1_year ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='dataTime', + | 'YEAR_GRANULARITY'='1') + |AS SELECT dataTime, SUM(age) FROM mainTable + |GROUP BY dataTime + """.stripMargin) + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), true, "agg1_year") + checkExistence(sql("DESC FORMATTED mainTable_agg1_year"), true, "maintable_age_sum") + } + test("test timeseries create table 20: don't support 'create datamap if exists'") { val e: Exception = intercept[AnalysisException] { sql( @@ -369,6 +420,106 @@ class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll { assert(e.getMessage.contains("identifier matching regex")) } + test("test timeseries create table 26: test different data type") { + sql("drop table if exists dataTable") + sql( + s""" + | CREATE TABLE dataTable( + | shortField SHORT, + | booleanField BOOLEAN, + | intField INT, + | bigintField LONG, + | doubleField DOUBLE, + | stringField STRING, + | decimalField DECIMAL(18,2), + | charField CHAR(5), + | floatField FLOAT, + | dataTime timestamp + | ) + | STORED BY 'carbondata' + """.stripMargin) + + + sql( + s"""CREATE DATAMAP agg0_hour ON TABLE dataTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='dataTime', + | 'HOUR_GRANULARITY'='1') + | AS SELECT + | dataTime, + | SUM(intField), + | shortField, + | booleanField, + | intField, + | bigintField, + | doubleField, + | stringField, + | decimalField, + | charField, + | floatField + | FROM dataTable + | GROUP BY + | dataTime, + | shortField, + | booleanField, + | intField, + | bigintField, + | doubleField, + | stringField, + | decimalField, + | charField, + | floatField + """.stripMargin) + checkExistence(sql("SHOW DATAMAP ON TABLE dataTable"), true, "datatable_agg0_hour") + sql("DROP TABLE IF EXISTS dataTable") + } + + test("test timeseries create table 27: test data map name") { + sql( + s"""CREATE DATAMAP agg1_hour ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='dataTime', + | 'HOUR_GRANULARITY'='1') + |AS SELECT dataTime, SUM(age) FROM mainTable + |GROUP BY dataTime + """.stripMargin) + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), true, "agg1_hour") + checkExistence(sql("DESC FORMATTED mainTable_agg1_hour"), true, "maintable_age_sum") + } + + test("test timeseries create table 28: event_time is null") { + sql("DROP DATAMAP IF EXISTS agg1 ON TABLE mainTable") + intercept[NullPointerException] { + sql( + s"""CREATE DATAMAP agg1 ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='', + | 'HOUR_GRANULARITY'='1') + |AS SELECT name, SUM(age) FROM mainTable + |GROUP BY name + """.stripMargin) + } + } + + test("test timeseries create table 29: table not exists") { + sql("DROP DATAMAP IF EXISTS agg1 ON TABLE mainTable") + val e = intercept[AnalysisException] { + sql( + s"""CREATE DATAMAP agg1 ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='dataTime', + | 'HOUR_GRANULARITY'='1') + |AS SELECT dataTime, SUM(age) FROM mainTableNo + |GROUP BY dataTime + """.stripMargin) + } + assert(e.getMessage.contains("Table or view not found: maintableno")) + } + test("test timeseries create table 33: support event_time and granularity key with space") { sql("DROP DATAMAP IF EXISTS agg1_month ON TABLE maintable") sql( @@ -430,7 +581,7 @@ class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll { checkExistence(sql("SHOW DATAMAP ON TABLE maintable"), true, "maintable_agg1_month") } - test("test timeseries create table 37: unsupport event_time error value") { + test("test timeseries create table 37: unsupport event_time error value") { sql("DROP DATAMAP IF EXISTS agg1_month ON TABLE maintable") intercept[NullPointerException] { sql( @@ -440,12 +591,14 @@ class TestTimeSeriesCreateTable extends QueryTest with BeforeAndAfterAll { | 'MONTH_GRANULARITY'='1') |AS SELECT dataTime, SUM(age) FROM mainTable |GROUP BY dataTime - """.stripMargin) + """.stripMargin) } sql("DROP DATAMAP IF EXISTS agg1_month ON TABLE maintable") } override def afterAll: Unit = { - sql("DROP TABLE IF EXISTS mainTable") + dropTable("mainTable") + CarbonProperties.getInstance() + .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, timestampFormat) } } http://git-wip-us.apache.org/repos/asf/carbondata/blob/0e6fe6ca/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesDropSuite.scala ---------------------------------------------------------------------- diff --git a/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesDropSuite.scala b/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesDropSuite.scala index 5fe21e8..2c984ea 100644 --- a/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesDropSuite.scala +++ b/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesDropSuite.scala @@ -16,15 +16,28 @@ */ package org.apache.carbondata.integration.spark.testsuite.timeseries +import org.apache.spark.sql.AnalysisException import org.apache.spark.sql.test.util.QueryTest import org.scalatest.{BeforeAndAfterAll, BeforeAndAfterEach} -import org.apache.carbondata.common.exceptions.sql.MalformedCarbonCommandException +import org.apache.carbondata.common.exceptions.sql.{MalformedCarbonCommandException, NoSuchDataMapException} +import org.apache.carbondata.core.constants.CarbonCommonConstants +import org.apache.carbondata.core.util.CarbonProperties +import org.apache.carbondata.spark.exception.ProcessMetaDataException class TestTimeSeriesDropSuite extends QueryTest with BeforeAndAfterAll with BeforeAndAfterEach { + val timeSeries = "timeseries" + var timestampFormat: String = _ + override def beforeAll: Unit = { - sql(s"DROP TABLE IF EXISTS mainTable") + timestampFormat = CarbonProperties.getInstance() + .getProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, + CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) + CarbonProperties.getInstance() + .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, + CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) + dropTable("mainTable") sql( """ | CREATE TABLE mainTable( @@ -36,41 +49,66 @@ class TestTimeSeriesDropSuite extends QueryTest with BeforeAndAfterAll with Befo """.stripMargin) } - test("test timeseries drop datamap 1: drop datamap should throw exception if no datamap") { + override def afterEach(): Unit = { + dropDataMaps("mainTable", "agg1_second", "agg1_minute", + "agg1_hour", "agg1_day", "agg1_month", "agg1_year") + } + + test("test timeseries drop datamap 1: drop datamap should throw exception, maintable hasn't datamap") { // DROP DATAMAP DataMapName if the DataMapName not exists checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), false, "agg1_month") - val e: Exception = intercept[Exception] { + val e: Exception = intercept[NoSuchDataMapException] { sql(s"DROP DATAMAP agg1_month ON TABLE mainTable") } - assert(e.getMessage.equals("Datamap with name agg1_month does not exist under table mainTable")) + assert(e.getMessage.equals( + "Datamap with name agg1_month does not exist under table mainTable")) } - test("test timeseries drop datamap 2: drop datamap should SUCCESS if have IF EXISTS") { - // DROP DATAMAP DataMapName if the DataMapName not exists + test("test timeseries drop datamap 2: should support drop datamap IF EXISTS, maintable hasn't datamap") { + // DROP DATAMAP IF EXISTS DataMapName checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), false, "agg1_month") - try { - sql(s"DROP DATAMAP IF EXISTS agg1_month ON TABLE mainTable") - assert(true) - } catch { - case _: Exception => - assert(false) - } + sql(s"DROP DATAMAP IF EXISTS agg1_month ON TABLE mainTable") + assert(true) } - test("test timeseries drop datamap 3: drop datamap should throw proper exception") { + test("test timeseries drop datamap 3: should support drop datamap, maintable has datamap") { sql( - """ - | CREATE DATAMAP agg1_month ON TABLE mainTable - | USING 'timeseries' - | DMPROPERTIES ( - | 'event_Time'='dataTime', - | 'month_granularity'='1') - | AS SELECT dataTime, SUM(age) FROM mainTable - | GROUP BY dataTime - """.stripMargin) + s""" + | CREATE DATAMAP agg1_month ON TABLE mainTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='dataTime', + | 'MONTH_GRANULARITY'='1') + | AS SELECT dataTime, SUM(age) from mainTable + | GROUP BY dataTime + """.stripMargin) // Before DROP DATAMAP - checkExistence(sql("show datamap on table mainTable"), true, "agg1_month") + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), true, "agg1_month") + + // DROP DATAMAP DataMapName + sql(s"DROP DATAMAP agg1_month ON TABLE mainTable") + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), false, "agg1_month") + val e: Exception = intercept[NoSuchDataMapException] { + sql(s"DROP DATAMAP agg1_month ON TABLE mainTable") + } + assert(e.getMessage.equals( + "Datamap with name agg1_month does not exist under table mainTable")) + } + + test("test timeseries drop datamap 4: should support drop datamap with IF EXISTS, maintable has datamap") { + sql( + s""" + | CREATE DATAMAP agg1_month ON TABLE mainTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='dataTime', + | 'MONTH_GRANULARITY'='1') + | AS SELECT dataTime, SUM(age) from mainTable + | GROUP BY dataTime + """.stripMargin) + // DROP DATAMAP IF EXISTS DataMapName + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), true, "agg1_month") // DROP DATAMAP DataMapName sql(s"DROP DATAMAP agg1_month ON TABLE mainTable") @@ -78,31 +116,116 @@ class TestTimeSeriesDropSuite extends QueryTest with BeforeAndAfterAll with Befo val e: Exception = intercept[MalformedCarbonCommandException] { sql(s"DROP DATAMAP agg1_month ON TABLE mainTable") } - assert(e.getMessage.equals("Datamap with name agg1_month does not exist under table mainTable")) + assert(e.getMessage.equals( + "Datamap with name agg1_month does not exist under table mainTable")) + } + + test("test timeseries drop datamap 5: drop datamap without IF EXISTS when table not exists, catch MalformedCarbonCommandException") { + // DROP DATAMAP DataMapName if the DataMapName not exists + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), false, "agg1_month") + val e: Exception = intercept[MalformedCarbonCommandException] { + sql(s"DROP DATAMAP agg1_month ON TABLE mainTableNotExists") + } + assert(e.getMessage.contains( + "Dropping datamap agg1_month failed: Table or view 'maintablenotexists' not found")) } - test("test timeseries drop datamap 4: drop datamap should throw exception if table not exist") { + test("test timeseries drop datamap 6: drop datamap with IF EXISTS when table not exists, catch MalformedCarbonCommandException") { + // DROP DATAMAP DataMapName if the DataMapName not exists + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), false, "agg1_month") + val e: Exception = intercept[MalformedCarbonCommandException] { + sql(s"DROP DATAMAP IF EXISTS agg1_month ON TABLE mainTableNotExists") + } + assert(e.getMessage.contains( + "Dropping datamap agg1_month failed: Table or view 'maintablenotexists' not found")) + } + + test("test timeseries drop datamap 7: drop datamap should throw exception if table not exist, catch ProcessMetaDataException") { // DROP DATAMAP DataMapName if the DataMapName not exists and checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), false, "agg1_month") - val e: Exception = intercept[Exception] { - sql(s"DROP DATAMAP agg1_month ON TABLE mainTableNotExist") + val e: Exception = intercept[ProcessMetaDataException] { + sql(s"DROP DATAMAP agg1_month ON TABLE mainTableNotExists") } assert(e.getMessage.contains( - "Dropping datamap agg1_month failed: Table or view 'maintablenotexist' not found ")) + "Dropping datamap agg1_month failed: Table or view 'maintablenotexists' not found ")) } - test("test timeseries drop datamap 5: should throw exception if table not exist with IF EXISTS") { + test("test timeseries drop datamap 8: should throw exception if table not exist with IF EXISTS, catch ProcessMetaDataException") { // DROP DATAMAP DataMapName if the DataMapName not exists // DROP DATAMAP should throw exception if table not exist, even though there is IF EXISTS" checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), false, "agg1_month") - val e: Exception = intercept[Exception] { - sql(s"DROP DATAMAP IF EXISTS agg1_month ON TABLE mainTableNotExist") + val e: Exception = intercept[ProcessMetaDataException] { + sql(s"DROP DATAMAP IF EXISTS agg1_month ON TABLE mainTableNotExists") + } + assert(e.getMessage.contains( + "Dropping datamap agg1_month failed: Table or view 'maintablenotexists' not found ")) + } + + test("test timeseries drop datamap 9: drop datamap when table not exists, there are datamap in database") { + sql( + s"""CREATE DATAMAP agg1_minute ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='dataTime', + | 'minute_GRANULARITY'='1') + |AS SELECT dataTime, SUM(age) from mainTable + |GROUP BY dataTime + """.stripMargin) + + // DROP DATAMAP DataMapName if the DataMapName not exists + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), false, "agg1_month") + val e: Exception = intercept[ProcessMetaDataException] { + sql(s"DROP DATAMAP agg1_month ON TABLE mainTableNotExists") + } + assert(e.getMessage.contains( + "Dropping datamap agg1_month failed: Table or view 'maintablenotexists' not found")) + } + + + test("test timeseries drop datamap 10: drop datamap when table not exists, there are datamap in database") { + sql( + s"""CREATE DATAMAP agg3 ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='dataTime', + | 'month_GRANULARITY'='1') + |AS SELECT dataTime, SUM(age) from mainTable + |GROUP BY dataTime + """.stripMargin) + + // DROP DATAMAP DataMapName if the DataMapName not exists + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), false, "agg1_month") + val e: Exception = intercept[ProcessMetaDataException] { + sql(s"DROP DATAMAP IF EXISTS agg1_month ON TABLE mainTableNotExists") } assert(e.getMessage.contains( - "Dropping datamap agg1_month failed: Table or view 'maintablenotexist' not found ")) + "Dropping datamap agg1_month failed: Table or view 'maintablenotexists' not found")) + } + + test("test timeseries drop datamap 11: drop datamap when table not exists, there are datamap in database") { + sql( + s""" + |CREATE DATAMAP agg4 ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='dataTime', + | 'month_GRANULARITY'='1') + |AS SELECT dataTime, SUM(age) from mainTable + |GROUP BY dataTime + """.stripMargin) + + // DROP DATAMAP DataMapName if the DataMapName not exists + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), false, "agg1_month") + val e: Exception = intercept[AnalysisException] { + sql(s"DROP DATAMAP IF NOT EXISTS agg1_month ON TABLE mainTableNotExists") + } + assert(e.getMessage.contains("failure")) } override def afterAll: Unit = { - sql(s"DROP TABLE IF EXISTS mainTable") + dropTable("mainTable") + dropTable("mainTableNotExists") + CarbonProperties.getInstance() + .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, timestampFormat) } } http://git-wip-us.apache.org/repos/asf/carbondata/blob/0e6fe6ca/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesMatchStrategySuite.scala ---------------------------------------------------------------------- diff --git a/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesMatchStrategySuite.scala b/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesMatchStrategySuite.scala new file mode 100644 index 0000000..55aa264 --- /dev/null +++ b/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesMatchStrategySuite.scala @@ -0,0 +1,401 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.carbondata.integration.spark.testsuite.timeseries + +import java.sql.Timestamp + +import org.apache.spark.sql.{CarbonDatasourceHadoopRelation, Row} +import org.apache.spark.sql.catalyst.plans.logical.LogicalPlan +import org.apache.spark.sql.execution.datasources.LogicalRelation +import org.apache.spark.sql.hive.CarbonRelation +import org.apache.spark.sql.test.util.QueryTest +import org.apache.spark.util.SparkUtil4Test +import org.scalatest.{BeforeAndAfterAll, BeforeAndAfterEach} + +import org.apache.carbondata.core.constants.CarbonCommonConstants +import org.apache.carbondata.core.util.CarbonProperties + +class TestTimeSeriesMatchStrategySuite extends QueryTest with BeforeAndAfterAll with BeforeAndAfterEach { + + var timestampFormat: String = _ + + override def beforeAll: Unit = { + timestampFormat = CarbonProperties.getInstance() + .getProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, + CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) + SparkUtil4Test.createTaskMockUp(sqlContext) + CarbonProperties.getInstance() + .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, + CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) + + } + + override protected def beforeEach(): Unit = { + sql("drop table if exists mainTable") + sql( + """ + | CREATE TABLE mainTable( + | mytime TIMESTAMP, + | name STRING, + | age INT) + | STORED BY 'org.apache.carbondata.format' + """.stripMargin) + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE mainTable") + } + + val timeSeries = "TIMESERIES" + + test("test timeseries match 1: select small one when create big_agg and then create small_agg") { + + dropDataMaps("maintable", "big_agg", "small_agg") + sql( + s""" + | CREATE DATAMAP big_agg ON TABLE mainTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age), count(age), max(age), min(age) + | FROM mainTable + | GROUP BY mytime + """.stripMargin) + + val df1 = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM mainTable + | WHERE + | timeseries(mytime,'minute')<'2016-02-23 09:02:00' and + | timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + checkAnswer(df1, Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 60))) + + preAggTableValidator(df1.queryExecution.analyzed, "maintable_big_agg") + + sql( + s""" + | CREATE DATAMAP small_agg ON TABLE mainTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age) + | FROM mainTable + | GROUP BY mytime + """.stripMargin) + + val df2 = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM mainTable + | WHERE + | timeseries(mytime,'minute')<'2016-02-23 09:02:00' and + | timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + checkAnswer(df2, Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 60))) + + preAggTableValidator(df2.queryExecution.analyzed, "maintable_small_agg") + } + + test("test timeseries match 2: select small one when create small_agg and then create big_agg") { + dropDataMaps("maintable", "big_agg", "small_agg") + + sql( + s""" + | CREATE DATAMAP small_agg ON TABLE mainTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age) + | FROM mainTable + | GROUP BY mytime + """.stripMargin) + + val df2 = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM mainTable + | WHERE + | timeseries(mytime,'minute')<'2016-02-23 09:02:00' and + | timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + checkAnswer(df2, Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 60))) + preAggTableValidator(df2.queryExecution.analyzed, "maintable_small_agg") + + sql( + s""" + | CREATE DATAMAP big_agg ON TABLE mainTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age), count(age), max(age), min(age) + | FROM mainTable + | GROUP BY mytime + """.stripMargin) + + val df1 = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM mainTable + | WHERE + | timeseries(mytime,'minute')<'2016-02-23 09:02:00' and + | timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + checkAnswer(df1, Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 60))) + + intercept[Exception]{ + preAggTableValidator(df1.queryExecution.analyzed, "maintable_big_agg") + } + preAggTableValidator(df1.queryExecution.analyzed, "maintable_small_agg") + } + + test("test timeseries match 3: select small one when create big_agg and then create small_agg") { + + dropDataMaps("maintable", "big_agg", "small_agg") + sql( + s""" + | CREATE DATAMAP big_agg ON TABLE mainTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age), count(age), max(age), min(age) + | FROM mainTable + | GROUP BY mytime + """.stripMargin) + + val df1 = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM mainTable + | WHERE + | timeseries(mytime,'minute')<'2016-02-23 09:02:00' and + | timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + checkAnswer(df1, Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 60))) + + preAggTableValidator(df1.queryExecution.analyzed, "maintable_big_agg") + + sql( + s""" + | CREATE DATAMAP small_agg ON TABLE mainTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age) + | FROM mainTable + | GROUP BY mytime + """.stripMargin) + + val df2 = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM mainTable + | WHERE + | timeseries(mytime,'minute')<'2016-02-23 09:02:00' and + | timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + checkAnswer(df2, Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 60))) + + preAggTableValidator(df2.queryExecution.analyzed, "maintable_small_agg") + intercept[Exception] { + preAggTableValidator(df1.queryExecution.analyzed, "maintable_small_agg") + } + preAggTableValidator(df1.queryExecution.analyzed, "maintable_big_agg") + } + + test("test timeseries match 4: select small one when create big_agg, small_agg, and middle_agg") { + + dropDataMaps("maintable", "big_agg", "small_agg", "middle_agg") + sql( + s""" + | CREATE DATAMAP big_agg ON TABLE mainTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age), count(age), max(age), min(age) + | FROM mainTable + | GROUP BY mytime + """.stripMargin) + + val df1 = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM mainTable + | WHERE + | timeseries(mytime,'minute')<'2016-02-23 09:02:00' and + | timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + checkAnswer(df1, Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 60))) + + preAggTableValidator(df1.queryExecution.analyzed, "maintable_big_agg") + + sql( + s""" + | CREATE DATAMAP small_agg ON TABLE mainTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age) + | FROM mainTable + | GROUP BY mytime + """.stripMargin) + + val df2 = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM mainTable + | WHERE + | timeseries(mytime,'minute')<'2016-02-23 09:02:00' and + | timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + checkAnswer(df2, Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 60))) + + preAggTableValidator(df2.queryExecution.analyzed, "maintable_small_agg") + intercept[Exception] { + preAggTableValidator(df1.queryExecution.analyzed, "maintable_small_agg") + } + preAggTableValidator(df1.queryExecution.analyzed, "maintable_big_agg") + + sql( + s""" + | CREATE DATAMAP middle_agg ON TABLE mainTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age), count(age) + | FROM mainTable + | GROUP BY mytime + """.stripMargin) + + val df3 = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM mainTable + | WHERE + | timeseries(mytime,'minute')<'2016-02-23 09:02:00' and + | timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + preAggTableValidator(df3.queryExecution.analyzed, "maintable_small_agg") + } + + def preAggTableValidator(plan: LogicalPlan, actualTableName: String) : Unit ={ + var isValidPlan = false + plan.transform { + // first check if any preaTable1 scala function is applied it is present is in plan + // then call is FROM create preaTable1regate table class so no need to transform + // the query plan + case ca:CarbonRelation => + if (ca.isInstanceOf[CarbonDatasourceHadoopRelation]) { + val relation = ca.asInstanceOf[CarbonDatasourceHadoopRelation] + if(relation.carbonTable.getTableName.equalsIgnoreCase(actualTableName)) { + isValidPlan = true + } + } + ca + case logicalRelation:LogicalRelation => + if(logicalRelation.relation.isInstanceOf[CarbonDatasourceHadoopRelation]) { + val relation = logicalRelation.relation.asInstanceOf[CarbonDatasourceHadoopRelation] + if(relation.carbonTable.getTableName.equalsIgnoreCase(actualTableName)) { + isValidPlan = true + } + } + logicalRelation + } + if(!isValidPlan) { + assert(false) + } else { + assert(true) + } + } + + override def afterAll: Unit = { + dropDataMaps("maintable", "agg0_second", "agg0_hour", "agg0_day", "agg0_month", "agg0_year") + sql("drop table if exists mainTable") + CarbonProperties.getInstance() + .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, timestampFormat) + } +} http://git-wip-us.apache.org/repos/asf/carbondata/blob/0e6fe6ca/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesUnsupportedSuite.scala ---------------------------------------------------------------------- diff --git a/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesUnsupportedSuite.scala b/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesUnsupportedSuite.scala new file mode 100644 index 0000000..1bcd6ec --- /dev/null +++ b/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeSeriesUnsupportedSuite.scala @@ -0,0 +1,265 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.carbondata.integration.spark.testsuite.timeseries + +import java.sql.Timestamp + +import org.apache.spark.sql.Row +import org.apache.spark.sql.test.util.QueryTest +import org.scalatest.{BeforeAndAfterAll, BeforeAndAfterEach} + +import org.apache.carbondata.core.constants.CarbonCommonConstants +import org.apache.carbondata.core.util.CarbonProperties + +class TestTimeSeriesUnsupportedSuite extends QueryTest with BeforeAndAfterAll with BeforeAndAfterEach { + + val timeSeries = "TIMESERIES" + var timestampFormat: String = _ + + override def beforeAll: Unit = { + timestampFormat = CarbonProperties.getInstance() + .getProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, + CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) + CarbonProperties.getInstance() + .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, + CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) + } + + override def beforeEach(): Unit = { + sql("drop table if exists mainTable") + sql( + """ + | CREATE TABLE mainTable( + | mytime TIMESTAMP, + | name STRING, + | age INT) + | STORED BY 'org.apache.carbondata.format' + """.stripMargin) + } + + test("test timeseries unsupported 1: don't support insert") { + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE mainTable") + sql( + s"""CREATE DATAMAP agg1_minute ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + |AS SELECT mytime, SUM(age) FROM mainTable + |GROUP BY mytime + """.stripMargin) + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), true, "maintable_agg1_minute") + checkAnswer(sql("SELECT * FROM maintable_agg1_minute"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00.0"), 60), + Row(Timestamp.valueOf("2016-02-23 09:02:00.0"), 140))) + val e = intercept[UnsupportedOperationException] { + sql(s"INSERT INTO maintable_agg1_minute VALUES('2016-02-23 09:01:00.0', 60)") + } + assert(e.getMessage.equalsIgnoreCase( + "Cannot insert/load data directly into pre-aggregate table")) + + // check value after inserting + checkAnswer(sql("SELECT * FROM maintable_agg1_minute"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00.0"), 60), + Row(Timestamp.valueOf("2016-02-23 09:02:00.0"), 140))) + } + + test("test timeseries unsupported 2: don't support insert") { + sql( + s"""CREATE DATAMAP agg1_minute ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + |AS SELECT mytime, SUM(age) FROM mainTable + |GROUP BY mytime + """.stripMargin) + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), true, "maintable_agg1_minute") + val e = intercept[UnsupportedOperationException] { + sql(s"INSERT INTO maintable_agg1_minute VALUES('2016-02-23 09:01:00.0', 60)") + } + assert(e.getMessage.equalsIgnoreCase( + "Cannot insert/load data directly into pre-aggregate table")) + } + + test("test timeseries unsupported 3: don't support insert") { + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE mainTable") + sql( + s"""CREATE DATAMAP agg1_minute ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + |AS SELECT + | mytime, + | name, + | SUM(age) + |from mainTable + |GROUP BY mytime, name + """.stripMargin) + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), true, "maintable_agg1_minute") + + val e = intercept[UnsupportedOperationException] { + sql(s"INSERT INTO maintable_agg1_minute VALUES('2016-02-23 09:01:00.0', 'hello', 60)") + } + assert(e.getMessage.equalsIgnoreCase( + "Cannot insert/load data directly into pre-aggregate table")) + } + + test("test timeseries unsupported 4: don't support load") { + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE mainTable") + sql( + s"""CREATE DATAMAP agg1_minute ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + |AS SELECT + | mytime, + | name, + | SUM(age) AS age + |from mainTable + |GROUP BY mytime, name + """.stripMargin) + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), true, "maintable_agg1_minute") + + val e = intercept[UnsupportedOperationException] { + sql( + s""" + | LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' + | INTO TABLE maintable_agg1_minute + | OPTIONS('FILEHEADER'='maintable_mytime,maintable_name,maintable_age_SUM') + """.stripMargin) + } + assert(e.getMessage.equalsIgnoreCase( + "Cannot insert/load data directly into pre-aggregate table")) + } + + test("test timeseries unsupported 5: don't support update") { + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE mainTable") + sql( + s"""CREATE DATAMAP agg1_minute ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + |AS SELECT mytime, SUM(age) FROM mainTable + |GROUP BY mytime + """.stripMargin) + checkAnswer(sql("SELECT * FROM maintable_agg1_minute"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00.0"), 60), + Row(Timestamp.valueOf("2016-02-23 09:02:00.0"), 140))) + val e = intercept[Exception] { + sql("update maintable_agg1_minute SET (maintable_age_SUM) = (maintable_age_SUM+1)").show() + } + assert(e.getMessage.equalsIgnoreCase( + "Update operation is not supported for pre-aggregate table")) + + // check value after inserting + checkAnswer(sql("SELECT * FROM maintable_agg1_minute"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00.0"), 60), + Row(Timestamp.valueOf("2016-02-23 09:02:00.0"), 140))) + } + + test("test timeseries unsupported 6: don't support update") { + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE mainTable") + sql( + s"""CREATE DATAMAP agg1_minute ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + |AS SELECT mytime, SUM(age) FROM mainTable + |GROUP BY mytime + """.stripMargin) + checkAnswer(sql("SELECT * FROM maintable_agg1_minute"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00.0"), 60), + Row(Timestamp.valueOf("2016-02-23 09:02:00.0"), 140))) + val e = intercept[Exception] { + sql( + """ + | update maintable_agg1_minute + | SET (maintable_mytime, maintable_age_SUM)=('2016-02-23 09:11:00.0', 160) + | WHERE maintable_age_SUM = '60' + """.stripMargin).show + } + checkAnswer(sql("SELECT * FROM maintable_agg1_minute"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00.0"), 60), + Row(Timestamp.valueOf("2016-02-23 09:02:00.0"), 140))) + assert(e.getMessage.equalsIgnoreCase( + "Update operation is not supported for pre-aggregate table")) + } + + test("test timeseries unsupported 7: don't support delete") { + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE mainTable") + sql( + s"""CREATE DATAMAP agg1_minute ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + |AS SELECT mytime, SUM(age) FROM mainTable + |GROUP BY mytime + """.stripMargin) + checkAnswer(sql("SELECT * FROM maintable_agg1_minute"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00.0"), 60), + Row(Timestamp.valueOf("2016-02-23 09:02:00.0"), 140))) + val e = intercept[UnsupportedOperationException] { + sql("delete FROM maintable_agg1_minute") + } + + assert(e.getMessage.equalsIgnoreCase( + "Delete operation is not supported for pre-aggregate table")) + + // check value after inserting + checkAnswer(sql("SELECT * FROM maintable_agg1_minute"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00.0"), 60), + Row(Timestamp.valueOf("2016-02-23 09:02:00.0"), 140))) + } + + test("test timeseries unsupported 8: don't support alter") { + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE mainTable") + sql( + s"""CREATE DATAMAP agg1_minute ON TABLE mainTable + |USING '$timeSeries' + |DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + |AS SELECT mytime, SUM(age) FROM mainTable + |GROUP BY mytime + """.stripMargin) + // before alter + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), true, "maintable_agg1_minute") + + // alter + val e = intercept[Exception] { + sql("alter table maintable_agg1_minute rename to maintable_agg1_minute_new") + } + assert(e.getMessage.contains( + "Rename operation for pre-aggregate table is not supported.")) + + // check datamap after alter + checkExistence(sql("SHOW DATAMAP ON TABLE mainTable"), true, "maintable_agg1_minute") + } + + override def afterAll: Unit = { + dropTable("mainTable") + CarbonProperties.getInstance() + .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, timestampFormat) + } +} http://git-wip-us.apache.org/repos/asf/carbondata/blob/0e6fe6ca/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeseriesDataLoad.scala ---------------------------------------------------------------------- diff --git a/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeseriesDataLoad.scala b/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeseriesDataLoad.scala index 1d1fd94..244cc44 100644 --- a/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeseriesDataLoad.scala +++ b/integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeseriesDataLoad.scala @@ -19,7 +19,6 @@ package org.apache.carbondata.integration.spark.testsuite.timeseries import java.sql.Timestamp import org.apache.spark.sql.Row -import org.apache.spark.sql.catalyst.analysis.TableAlreadyExistsException import org.apache.spark.sql.test.util.QueryTest import org.apache.spark.util.SparkUtil4Test import org.scalatest.BeforeAndAfterAll @@ -32,13 +31,17 @@ import org.apache.carbondata.core.util.CarbonProperties class TestTimeseriesDataLoad extends QueryTest with BeforeAndAfterAll { val timeSeries = TIMESERIES.toString + var timestampFormat: String = _ override def beforeAll: Unit = { + timestampFormat = CarbonProperties.getInstance() + .getProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, + CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) SparkUtil4Test.createTaskMockUp(sqlContext) CarbonProperties.getInstance() .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) - sql("drop table if exists mainTable") - sql("drop table if exists table_03") + sql("DROP TABLE IF EXISTS mainTable") + sql("DROP TABLE IF EXISTS table_03") sql("CREATE TABLE mainTable(mytime timestamp, name string, age int) STORED BY 'org.apache.carbondata.format'") sql( s""" @@ -167,59 +170,60 @@ class TestTimeseriesDataLoad extends QueryTest with BeforeAndAfterAll { """.stripMargin) } - test("test Year level timeseries data validation1 ") { - checkAnswer( sql("select count(*) from table_03_ag1_year"), + + test("test timeseries table selection 1: year level timeseries data validation1 ") { + checkAnswer(sql("SELECT COUNT(*) FROM table_03_ag1_year"), Seq(Row(4))) } - test("test month level timeseries data validation1 ") { - checkAnswer( sql("select count(*) from table_03_ag1_month"), + test("test timeseries table selection 2: month level timeseries data validation1 ") { + checkAnswer(sql("SELECT COUNT(*) FROM table_03_ag1_month"), Seq(Row(4))) } - test("test day level timeseries data validation1 ") { - checkAnswer( sql("select count(*) from table_03_ag1_day"), + test("test timeseries table selection 3: day level timeseries data validation1 ") { + checkAnswer(sql("SELECT COUNT(*) FROM table_03_ag1_day"), Seq(Row(12))) } - test("test Year level timeseries data validation") { - checkAnswer( sql("select * from maintable_agg0_year"), - Seq(Row(Timestamp.valueOf("2016-01-01 00:00:00.0"),200))) + test("test timeseries table selection 4: year level timeseries data validation") { + checkAnswer(sql("SELECT * FROM maintable_agg0_year"), + Seq(Row(Timestamp.valueOf("2016-01-01 00:00:00.0"), 200))) } - test("test month level timeseries data validation") { - checkAnswer( sql("select * from maintable_agg0_month"), - Seq(Row(Timestamp.valueOf("2016-02-01 00:00:00.0"),200))) + test("test timeseries table selection 5: month level timeseries data validation") { + checkAnswer(sql("SELECT * FROM maintable_agg0_month"), + Seq(Row(Timestamp.valueOf("2016-02-01 00:00:00.0"), 200))) } - test("test day level timeseries data validation") { - checkAnswer( sql("select * from maintable_agg0_day"), - Seq(Row(Timestamp.valueOf("2016-02-23 00:00:00.0"),200))) + test("test timeseries table selection 6: day level timeseries data validation") { + checkAnswer(sql("SELECT * FROM maintable_agg0_day"), + Seq(Row(Timestamp.valueOf("2016-02-23 00:00:00.0"), 200))) } - test("test hour level timeseries data validation") { - checkAnswer( sql("select * from maintable_agg0_hour"), - Seq(Row(Timestamp.valueOf("2016-02-23 01:00:00.0"),200))) + test("test timeseries table selection 7: hour level timeseries data validation") { + checkAnswer(sql("SELECT * FROM maintable_agg0_hour"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:00:00.0"), 200))) } - test("test minute level timeseries data validation") { - checkAnswer( sql("select * from maintable_agg0_minute"), - Seq(Row(Timestamp.valueOf("2016-02-23 01:01:00.0"),60), - Row(Timestamp.valueOf("2016-02-23 01:02:00.0"),140))) + test("test timeseries table selection 8: minute level timeseries data validation") { + checkAnswer(sql("SELECT * FROM maintable_agg0_minute"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00.0"), 60), + Row(Timestamp.valueOf("2016-02-23 09:02:00.0"), 140))) } - test("test second level timeseries data validation") { - checkAnswer( sql("select * from maintable_agg0_second"), - Seq(Row(Timestamp.valueOf("2016-02-23 01:01:30.0"),10), - Row(Timestamp.valueOf("2016-02-23 01:01:40.0"),20), - Row(Timestamp.valueOf("2016-02-23 01:01:50.0"),30), - Row(Timestamp.valueOf("2016-02-23 01:02:30.0"),40), - Row(Timestamp.valueOf("2016-02-23 01:02:40.0"),50), - Row(Timestamp.valueOf("2016-02-23 01:02:50.0"),50))) + test("test timeseries table selection 9: second level timeseries data validation") { + checkAnswer(sql("SELECT * FROM maintable_agg0_second"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:30.0"), 10), + Row(Timestamp.valueOf("2016-02-23 09:01:40.0"), 20), + Row(Timestamp.valueOf("2016-02-23 09:01:50.0"), 30), + Row(Timestamp.valueOf("2016-02-23 09:02:30.0"), 40), + Row(Timestamp.valueOf("2016-02-23 09:02:40.0"), 50), + Row(Timestamp.valueOf("2016-02-23 09:02:50.0"), 50))) } - test("test if timeseries load is successful ON TABLE creation") { - sql("drop table if exists mainTable") + test("test timeseries table selection 10: if timeseries load is successful ON TABLE creation") { + sql("DROP TABLE IF EXISTS mainTable") sql("CREATE TABLE mainTable(mytime timestamp, name string, age int) STORED BY 'org.apache.carbondata.format'") sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' into table mainTable") sql( @@ -232,17 +236,49 @@ class TestTimeseriesDataLoad extends QueryTest with BeforeAndAfterAll { | AS SELECT mytime, SUM(age) FROM mainTable | GROUP BY mytime """.stripMargin) - checkAnswer( sql("select * FROM maintable_agg0_second"), - Seq(Row(Timestamp.valueOf("2016-02-23 01:01:30.0"),10), - Row(Timestamp.valueOf("2016-02-23 01:01:40.0"),20), - Row(Timestamp.valueOf("2016-02-23 01:01:50.0"),30), - Row(Timestamp.valueOf("2016-02-23 01:02:30.0"),40), - Row(Timestamp.valueOf("2016-02-23 01:02:40.0"),50), - Row(Timestamp.valueOf("2016-02-23 01:02:50.0"),50))) + checkAnswer( sql("SELECT * FROM maintable_agg0_second"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:30.0"),10), + Row(Timestamp.valueOf("2016-02-23 09:01:40.0"),20), + Row(Timestamp.valueOf("2016-02-23 09:01:50.0"),30), + Row(Timestamp.valueOf("2016-02-23 09:02:30.0"),40), + Row(Timestamp.valueOf("2016-02-23 09:02:40.0"),50), + Row(Timestamp.valueOf("2016-02-23 09:02:50.0"),50))) } - test("create datamap without 'if not exists' after load data into mainTable and create datamap") { - sql("drop table if exists mainTable") + test("test timeseries table selection 11: if timeseries load twice is successful ON TABLE creation") { + sql("DROP TABLE IF EXISTS mainTable") + sql( + """ + | CREATE TABLE mainTable( + | mytime TIMESTAMP, + | name STRING, + | age INT) + | STORED BY 'org.apache.carbondata.format' + """.stripMargin) + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE mainTable") + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE mainTable") + sql( + s""" + | CREATE DATAMAP agg0_second ON TABLE mainTable + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'SECOND_GRANULARITY'='1') + | AS SELECT mytime, SUM(age) + | FROM mainTable + | GROUP BY mytime + """.stripMargin) + checkAnswer(sql("SELECT * FROM maintable_agg0_second"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:30.0"), 20), + Row(Timestamp.valueOf("2016-02-23 09:01:40.0"), 40), + Row(Timestamp.valueOf("2016-02-23 09:01:50.0"), 60), + Row(Timestamp.valueOf("2016-02-23 09:02:30.0"), 80), + Row(Timestamp.valueOf("2016-02-23 09:02:40.0"), 100), + Row(Timestamp.valueOf("2016-02-23 09:02:50.0"), 100))) + } + + test("test timeseries table selection 12: create datamap without 'if not exists' after load data into mainTable and create datamap") { + sql("DROP TABLE IF EXISTS mainTable") sql( """ | CREATE TABLE mainTable( @@ -265,12 +301,12 @@ class TestTimeseriesDataLoad extends QueryTest with BeforeAndAfterAll { """.stripMargin) checkAnswer(sql("select * from maintable_agg0_second"), - Seq(Row(Timestamp.valueOf("2016-02-23 01:01:30.0"), 10), - Row(Timestamp.valueOf("2016-02-23 01:01:40.0"), 20), - Row(Timestamp.valueOf("2016-02-23 01:01:50.0"), 30), - Row(Timestamp.valueOf("2016-02-23 01:02:30.0"), 40), - Row(Timestamp.valueOf("2016-02-23 01:02:40.0"), 50), - Row(Timestamp.valueOf("2016-02-23 01:02:50.0"), 50))) + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:30.0"), 10), + Row(Timestamp.valueOf("2016-02-23 09:01:40.0"), 20), + Row(Timestamp.valueOf("2016-02-23 09:01:50.0"), 30), + Row(Timestamp.valueOf("2016-02-23 09:02:30.0"), 40), + Row(Timestamp.valueOf("2016-02-23 09:02:40.0"), 50), + Row(Timestamp.valueOf("2016-02-23 09:02:50.0"), 50))) val e: Exception = intercept[MalformedDataMapCommandException] { sql( s""" @@ -287,7 +323,7 @@ class TestTimeseriesDataLoad extends QueryTest with BeforeAndAfterAll { sql("DROP DATAMAP agg0_second ON TABLE mainTable") } - test("create datamap with 'if not exists' after load data into mainTable and create datamap") { + test("test timeseries table selection 13: create datamap with 'if not exists' after load data into mainTable and create datamap") { sql("drop table if exists mainTable") sql( """ @@ -297,7 +333,7 @@ class TestTimeseriesDataLoad extends QueryTest with BeforeAndAfterAll { | age int) | STORED BY 'org.apache.carbondata.format' """.stripMargin) - sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' into table mainTable") + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE mainTable") sql( s""" @@ -311,12 +347,12 @@ class TestTimeseriesDataLoad extends QueryTest with BeforeAndAfterAll { """.stripMargin) checkAnswer(sql("select * from maintable_agg0_second"), - Seq(Row(Timestamp.valueOf("2016-02-23 01:01:30.0"), 10), - Row(Timestamp.valueOf("2016-02-23 01:01:40.0"), 20), - Row(Timestamp.valueOf("2016-02-23 01:01:50.0"), 30), - Row(Timestamp.valueOf("2016-02-23 01:02:30.0"), 40), - Row(Timestamp.valueOf("2016-02-23 01:02:40.0"), 50), - Row(Timestamp.valueOf("2016-02-23 01:02:50.0"), 50))) + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:30.0"), 10), + Row(Timestamp.valueOf("2016-02-23 09:01:40.0"), 20), + Row(Timestamp.valueOf("2016-02-23 09:01:50.0"), 30), + Row(Timestamp.valueOf("2016-02-23 09:02:30.0"), 40), + Row(Timestamp.valueOf("2016-02-23 09:02:40.0"), 50), + Row(Timestamp.valueOf("2016-02-23 09:02:50.0"), 50))) sql( s""" @@ -330,16 +366,232 @@ class TestTimeseriesDataLoad extends QueryTest with BeforeAndAfterAll { """.stripMargin) checkAnswer(sql("select * from maintable_agg0_second"), - Seq(Row(Timestamp.valueOf("2016-02-23 01:01:30.0"), 10), - Row(Timestamp.valueOf("2016-02-23 01:01:40.0"), 20), - Row(Timestamp.valueOf("2016-02-23 01:01:50.0"), 30), - Row(Timestamp.valueOf("2016-02-23 01:02:30.0"), 40), - Row(Timestamp.valueOf("2016-02-23 01:02:40.0"), 50), - Row(Timestamp.valueOf("2016-02-23 01:02:50.0"), 50))) + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:30.0"), 10), + Row(Timestamp.valueOf("2016-02-23 09:01:40.0"), 20), + Row(Timestamp.valueOf("2016-02-23 09:01:50.0"), 30), + Row(Timestamp.valueOf("2016-02-23 09:02:30.0"), 40), + Row(Timestamp.valueOf("2016-02-23 09:02:40.0"), 50), + Row(Timestamp.valueOf("2016-02-23 09:02:50.0"), 50))) + } + + test("test timeseries table selection 14: load data into mainTable after create timeseries datamap ON TABLE and SELECT sub table") { + sql("DROP TABLE IF EXISTS main_table") + sql( + """ + | CREATE TABLE main_table( + | mytime timestamp, + | name string, + | age int) + | STORED BY 'org.apache.carbondata.format' + """.stripMargin) + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE main_table") + sql( + s""" + | CREATE DATAMAP agg0_second ON TABLE main_table + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'SECOND_GRANULARITY'='1') + | AS SELECT mytime, SUM(age) + | FROM main_table + | GROUP BY mytime""".stripMargin) + + + sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE main_table") + + checkAnswer(sql("SELECT * FROM main_table_agg0_second"), + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:30.0"), 10), + Row(Timestamp.valueOf("2016-02-23 09:01:40.0"), 20), + Row(Timestamp.valueOf("2016-02-23 09:01:50.0"), 30), + Row(Timestamp.valueOf("2016-02-23 09:02:30.0"), 40), + Row(Timestamp.valueOf("2016-02-23 09:02:40.0"), 50), + Row(Timestamp.valueOf("2016-02-23 09:02:50.0"), 50), + Row(Timestamp.valueOf("2016-02-23 09:01:30.0"), 10), + Row(Timestamp.valueOf("2016-02-23 09:01:40.0"), 20), + Row(Timestamp.valueOf("2016-02-23 09:01:50.0"), 30), + Row(Timestamp.valueOf("2016-02-23 09:02:30.0"), 40), + Row(Timestamp.valueOf("2016-02-23 09:02:40.0"), 50), + Row(Timestamp.valueOf("2016-02-23 09:02:50.0"), 50))) + } + + test("test timeseries table selection 15: load data into main_table after create timeseries datamap ON TABLE 1") { + sql("DROP TABLE IF EXISTS main_table") + sql( + """ + | CREATE TABLE main_table( + | mytime timestamp, + | name string, + | age int) + | STORED BY 'org.apache.carbondata.format' + """.stripMargin) + sql(s"LOAD DATA INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE main_table") + + sql( + s""" + | CREATE DATAMAP agg0_minute ON TABLE main_table + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age) + | FROM main_table + | GROUP BY mytime""".stripMargin) + + sql(s"LOAD DATA INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE main_table") + val df = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM main_table + | WHERE timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + checkAnswer(df, + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 120), + Row(Timestamp.valueOf("2016-02-23 09:02:00"), 280))) + } + + test("test timeseries table selection 16: load data into main_table after create timeseries datamap ON TABLE 2") { + sql("DROP TABLE IF EXISTS main_table") + sql( + """ + | CREATE TABLE main_table( + | mytime timestamp, + | name string, + | age int) + | STORED BY 'org.apache.carbondata.format' + """.stripMargin) + + sql(s"LOAD DATA INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE main_table") + sql(s"LOAD DATA INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE main_table") + sql( + s""" + | CREATE DATAMAP agg0_minute ON TABLE main_table + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age) + | FROM main_table + | GROUP BY mytime""".stripMargin) + + + val df = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM main_table + | WHERE timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + checkAnswer(df, + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 120), + Row(Timestamp.valueOf("2016-02-23 09:02:00"), 280))) + } + + test("test timeseries table selection 17: load data into main_table after create timeseries datamap ON TABLE 3") { + sql("DROP TABLE IF EXISTS main_table") + sql( + """ + | CREATE TABLE main_table( + | mytime timestamp, + | name string, + | age int) + | STORED BY 'org.apache.carbondata.format' + """.stripMargin) + + sql(s"LOAD DATA INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE main_table") + sql(s"LOAD DATA INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE main_table") + sql(s"LOAD DATA INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE main_table") + sql( + s""" + | CREATE DATAMAP agg0_minute ON TABLE main_table + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age) + | FROM main_table + | GROUP BY mytime""".stripMargin) + + + val df = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM main_table + | WHERE timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + checkAnswer(df, + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 180), + Row(Timestamp.valueOf("2016-02-23 09:02:00"), 420))) + } + + test("test timeseries table selection 18: load data into main_table after create timeseries datamap ON TABLE 4") { + sql("DROP TABLE IF EXISTS main_table") + sql( + """ + | CREATE TABLE main_table( + | mytime timestamp, + | name string, + | age int) + | STORED BY 'org.apache.carbondata.format' + """.stripMargin) + + sql(s"LOAD DATA INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE main_table") + + sql( + s""" + | CREATE DATAMAP agg0_minute ON TABLE main_table + | USING '$timeSeries' + | DMPROPERTIES ( + | 'event_time'='mytime', + | 'MINUTE_GRANULARITY'='1') + | AS SELECT mytime, SUM(age) + | FROM main_table + | GROUP BY mytime""".stripMargin) + + sql(s"LOAD DATA INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE main_table") + sql(s"LOAD DATA INPATH '$resourcesPath/timeseriestest.csv' INTO TABLE main_table") + val df = sql( + """ + | SELECT + | timeseries(mytime,'minute') AS minuteLevel, + | SUM(age) AS SUM + | FROM main_table + | WHERE timeseries(mytime,'minute')>='2016-02-23 09:01:00' + | GROUP BY + | timeseries(mytime,'minute') + | ORDER BY + | timeseries(mytime,'minute') + """.stripMargin) + + checkAnswer(df, + Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 180), + Row(Timestamp.valueOf("2016-02-23 09:02:00"), 420))) } override def afterAll: Unit = { - sql("drop table if exists mainTable") - sql("drop table if exists table_03") + sql("DROP TABLE IF EXISTS main_table") + sql("DROP TABLE IF EXISTS mainTable") + sql("DROP TABLE IF EXISTS table_03") + CarbonProperties.getInstance() + .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, timestampFormat) } }
