| /* |
| * 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.spark.carbondata.restructure |
| |
| import java.io.File |
| import java.math.{BigDecimal, RoundingMode} |
| import java.sql.Timestamp |
| |
| import org.apache.spark.sql.Row |
| import org.apache.spark.sql.common.util.Spark2QueryTest |
| import org.scalatest.BeforeAndAfterAll |
| |
| import org.apache.carbondata.core.constants.CarbonCommonConstants |
| import org.apache.carbondata.core.util.CarbonProperties |
| import org.apache.carbondata.spark.exception.ProcessMetaDataException |
| |
| class AlterTableValidationTestCase extends Spark2QueryTest with BeforeAndAfterAll { |
| |
| override def beforeAll { |
| |
| sql("drop table if exists restructure") |
| sql("drop table if exists table1") |
| sql("drop table if exists restructure_test") |
| sql("drop table if exists restructure_new") |
| sql("drop table if exists restructure_bad") |
| sql("drop table if exists restructure_badnew") |
| sql("drop table if exists allKeyCol") |
| sql("drop table if exists testalterwithboolean") |
| sql("drop table if exists testalterwithbooleanwithoutdefaultvalue") |
| sql("drop table if exists test") |
| sql("drop table if exists retructure_iud") |
| sql("drop table if exists restructure_random_select") |
| sql("drop table if exists alterTable") |
| sql("drop table if exists alterPartitionTable") |
| |
| // clean data folder |
| CarbonProperties.getInstance() |
| sql( |
| "CREATE TABLE restructure (empno int, empname String, designation String, doj Timestamp, " + |
| "workgroupcategory int, workgroupcategoryname String, deptno int, deptname String, " + |
| "projectcode int, projectjoindate Timestamp, projectenddate Timestamp,attendance int," + |
| "utilization int,salary int) STORED BY 'org.apache.carbondata.format'") |
| sql( |
| s"""LOAD DATA LOCAL INPATH '$resourcesPath/data.csv' INTO TABLE restructure OPTIONS |
| |('DELIMITER'= ',', 'QUOTECHAR'= '\"')""".stripMargin) |
| sql( |
| "CREATE TABLE restructure_test (empno int, empname String, designation String, doj " + |
| "Timestamp, workgroupcategory int, workgroupcategoryname String, deptno int, deptname " + |
| "String, projectcode int, projectjoindate Timestamp, projectenddate Timestamp,attendance " + |
| "int,utilization int,salary int) STORED BY 'org.apache.carbondata.format'") |
| sql( |
| s"""LOAD DATA LOCAL INPATH '$resourcesPath/data.csv' INTO TABLE restructure_test OPTIONS |
| |('DELIMITER'= ',', 'QUOTECHAR'= '\"')""".stripMargin) |
| |
| sql( |
| """CREATE TABLE IF NOT EXISTS restructure_bad(ID BigInt, date Timestamp, country String, |
| actual_price Double, Quantity int, sold_price Decimal(19,2)) STORED BY 'carbondata'""") |
| |
| sql( |
| s"""LOAD DATA LOCAL INPATH '$resourcesPath/badrecords/datasample.csv' INTO TABLE |
| |restructure_bad OPTIONS |
| |('DELIMITER'= ',', 'QUOTECHAR'= '\"', 'bad_records_logger_enable'='true', |
| |'bad_records_action'='force')""" |
| .stripMargin) |
| |
| } |
| |
| test("test add dictionary column") { |
| sql( |
| "alter table restructure add columns(dict int) TBLPROPERTIES ('DICTIONARY_INCLUDE'='dict', " + |
| "'DEFAULT.VALUE.dict'= '9999')") |
| checkAnswer(sql("select distinct(dict) from restructure"), Row(9999)) |
| } |
| test("test add no dictionary column") { |
| sql( |
| "alter table restructure add columns(nodict string) TBLPROPERTIES " + |
| "('DICTIONARY_EXCLUDE'='nodict', 'DEFAULT.VALUE.NoDict'= 'abcd')") |
| checkAnswer(sql("select distinct(nodict) from restructure"), Row("abcd")) |
| } |
| |
| ignore ("test add timestamp no dictionary column") { |
| sql( |
| "alter table restructure add columns(tmpstmp timestamp) TBLPROPERTIES ('DEFAULT.VALUE" + |
| ".tmpstmp'= '17-01-2007')") |
| sql("select tmpstmp from restructure").show(200,false) |
| sql("select distinct(tmpstmp) from restructure").show(200,false) |
| checkAnswer(sql("select distinct(tmpstmp) from restructure"), |
| Row(new java.sql.Timestamp(107, 0, 17, 0, 0, 0, 0))) |
| checkExistence(sql("desc restructure"), true, "tmpstmp", "timestamp") |
| } |
| |
| test ("test add timestamp direct dictionary column") { |
| sql( |
| "alter table restructure add columns(tmpstmp1 timestamp) TBLPROPERTIES ('DEFAULT.VALUE" + |
| ".tmpstmp1'= '17-01-3007','DICTIONARY_INCLUDE'='tmpstmp1')") |
| checkAnswer(sql("select distinct(tmpstmp1) from restructure"), |
| Row(null)) |
| checkExistence(sql("desc restructure"), true, "tmpstmp", "timestamp") |
| } |
| |
| ignore ("test add timestamp column and load as dictionary") { |
| sql("create table table1(name string) stored by 'carbondata'") |
| sql("insert into table1 select 'abc'") |
| sql("alter table table1 add columns(tmpstmp timestamp) TBLPROPERTIES " + |
| "('DEFAULT.VALUE.tmpstmp'='17-01-3007','DICTIONARY_INCLUDE'= 'tmpstmp')") |
| sql("insert into table1 select 'name','17-01-2007'") |
| checkAnswer(sql("select * from table1"), |
| Seq(Row("abc",null), |
| Row("name",Timestamp.valueOf("2007-01-17 00:00:00.0")))) |
| } |
| |
| test("test add msr column") { |
| sql( |
| "alter table restructure add columns(msrField decimal(5,2))TBLPROPERTIES ('DEFAULT.VALUE" + |
| ".msrfield'= '123.45')") |
| sql("desc restructure").show(2000,false) |
| checkExistence(sql("desc restructure"), true, "msrfield", "decimal(5,2)") |
| val output = sql("select msrField from restructure").collect |
| sql("select distinct(msrField) from restructure").show(2000,false) |
| checkAnswer(sql("select distinct(msrField) from restructure"), |
| Row(new BigDecimal("123.45").setScale(2, RoundingMode.HALF_UP))) |
| } |
| |
| // test alter add LONG datatype before load, see CARBONDATA-2131 |
| test("test add long column before load") { |
| sql("drop table if exists alterLong") |
| sql("create table alterLong (name string) stored by 'carbondata'") |
| sql("alter table alterLong add columns(newCol long)") |
| sql("insert into alterLong select 'a',60000") |
| checkAnswer(sql("select * from alterLong"), Row("a", 60000)) |
| sql("drop table if exists alterLong") |
| } |
| |
| // test alter add LONG datatype after load, see CARBONDATA-2131 |
| test("test add long column after load") { |
| sql("drop table if exists alterLong1") |
| sql("create table alterLong1 (name string) stored by 'carbondata'") |
| sql("insert into alterLong1 select 'a'") |
| sql("alter table alterLong1 add columns(newCol long)") |
| checkAnswer(sql("select * from alterLong1"), Row("a", null)) |
| sql("insert into alterLong1 select 'b',70") |
| checkAnswer(sql("select * from alterLong1"), Seq(Row("a", null),Row("b", 70))) |
| sql("drop table if exists alterLong1") |
| } |
| |
| test("test add all datatype supported dictionary column") { |
| sql( |
| "alter table restructure add columns(strfld string, datefld date, tptfld timestamp, " + |
| "shortFld smallInt, " + |
| "intFld int, longFld bigint, dblFld double,dcml decimal(5,4))TBLPROPERTIES" + |
| "('DICTIONARY_INCLUDE'='datefld,shortFld,intFld,longFld,dblFld,dcml', 'DEFAULT.VALUE" + |
| ".dblFld'= '12345')") |
| checkAnswer(sql("select distinct(dblFld) from restructure"), |
| Row(java.lang.Double.parseDouble("12345"))) |
| checkExistence(sql("desc restructure"), true, "strfld", "string") |
| checkExistence(sql("desc restructure"), true, "datefld", "date") |
| checkExistence(sql("desc restructure"), true, "tptfld", "timestamp") |
| checkExistence(sql("desc restructure"), true, "shortfld", "smallint") |
| checkExistence(sql("desc restructure"), true, "intfld", "int") |
| checkExistence(sql("desc restructure"), true, "longfld", "bigint") |
| checkExistence(sql("desc restructure"), true, "dblfld", "double") |
| checkExistence(sql("desc restructure"), true, "dcml", "decimal(5,4)") |
| } |
| |
| test("test drop all keycolumns in a table") { |
| sql( |
| "create table allKeyCol (name string, age int, address string) stored by 'org.apache" + |
| ".carbondata.format'") |
| try { |
| sql("alter table allKeyCol drop columns(name,address)") |
| assert(true) |
| } catch { |
| case _: Exception => |
| assert(false) |
| } |
| } |
| |
| ignore( |
| "test add decimal without scale and precision, default precision and scale (10,0) should be " + |
| "used") |
| { |
| sql("alter table restructure add columns(dcmldefault decimal)") |
| checkExistence(sql("desc restructure"), true, "dcmldefault", "decimal(10,0)") |
| } |
| |
| test("test adding existing measure as dimension") { |
| sql("alter table restructure add columns(dcmlfld decimal(5,4))") |
| try { |
| sql("alter table restructure add columns(dcmlfld string)") |
| sys.error("Exception should be thrown as dcmlfld is already exist as measure") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| |
| test("test adding existing dimension as measure") { |
| sql("alter table restructure add columns(dimfld string)") |
| try { |
| sql("alter table restructure add columns(dimfld decimal(5,4))") |
| sys.error("Exception should be thrown as dimfld is already exist as dimension") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| |
| test("test adding existing column again") { |
| sql("alter table restructure add columns(dimfld1 string, msrCol double)") |
| try { |
| sql( |
| "alter table restructure add columns(dimfld1 int)TBLPROPERTIES" + |
| "('DICTIONARY_INCLUDE'='dimfld1')") |
| sys.error("Exception should be thrown as dimfld1 is already exist") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| try { |
| sql("alter table restructure add columns(msrCol decimal(5,3))") |
| sys.error("Exception should be thrown as msrCol is already exist") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| } |
| |
| test("test adding no dictionary column with numeric type") { |
| try { |
| sql( |
| "alter table restructure add columns(dimfld2 double) TBLPROPERTIES" + |
| "('DICTIONARY_EXCLUDE'='dimfld2')") |
| sys.error("Exception should be thrown as msrCol is already exist") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| |
| test("test adding complex datatype column") { |
| try { |
| sql("alter table restructure add columns(arr array<string>)") |
| assert(false, "Exception should be thrown for complex column add") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| |
| test("test drop and add same column with different datatype and default value") { |
| sql("alter table restructure drop columns(empname)") |
| sql( |
| "alter table restructure add columns(empname int) TBLPROPERTIES" + |
| "('DICTIONARY_INCLUDE'='empname', 'DEFAULT.VALUE.empname'='12345')") |
| checkAnswer(sql("select distinct(empname) from restructure"), Row(12345)) |
| checkAnswer(sql("select count(empname) from restructure"), Row(10)) |
| } |
| |
| test("test drop column and select query on dropped column should fail") { |
| sql("alter table restructure drop columns(empname)") |
| try { |
| sql("select distinct(empname) from restructure") |
| sys.error("Exception should be thrown as selecting dropped column") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| sql( |
| "alter table restructure add columns(empname string) TBLPROPERTIES" + |
| "('DICTIONARY_EXCLUDE'='empname', 'DEFAULT.VALUE.empname'='testuser')") |
| checkAnswer(sql("select distinct(empname) from restructure"), Row("testuser")) |
| checkAnswer(sql("select count(empname) from restructure"), Row(10)) |
| } |
| |
| test("test add duplicate column names") { |
| try { |
| sql("alter table restructure add columns(newField string, newField int)") |
| sys.error("Exception should be thrown for duplicate column add") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| |
| test("test drop duplicate column names") { |
| try { |
| sql("alter table restructure drop columns(empname, empname)") |
| sys.error("Exception should be thrown for duplicate column drop") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| |
| test("test dropping non-existing column") { |
| try { |
| sql("alter table restructure drop columns(abcd)") |
| sys.error("Exception should be thrown for non-existing column drop") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| |
| test("test drop dimension, measure column") { |
| sql("alter table default.restructure drop columns(empno, designation, doj)") |
| checkExistence(sql("desc restructure"), false, "empno") |
| checkExistence(sql("desc restructure"), false, "designation") |
| checkExistence(sql("desc restructure"), false, "doj") |
| assert(sql("select * from restructure").schema |
| .filter(p => p.name.equalsIgnoreCase("empno") || |
| p.name.equalsIgnoreCase("designation") || p.name.equalsIgnoreCase("doj")) |
| .size == 0) |
| sql("alter table restructure add columns(empno int, designation string, doj timestamp)") |
| } |
| |
| test ("test drop & add same column multiple times as dict, nodict, timestamp and msr") { |
| CarbonProperties.getInstance() |
| .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, "dd-MM-yyyy") |
| // drop and add dict column |
| sql("alter table restructure drop columns(designation)") |
| sql( |
| "alter table default.restructure add columns(designation int) TBLPROPERTIES" + |
| "('DICTIONARY_INCLUDE'='designation', 'DEFAULT.VALUE.designation'='12345')") |
| checkAnswer(sql("select distinct(designation) from restructure"), Row(12345)) |
| // drop and add nodict column |
| sql("alter table restructure drop columns(designation)") |
| sql( |
| "alter table restructure add columns(designation string) TBLPROPERTIES" + |
| "('DICTIONARY_EXCLUDE'='designation', 'DEFAULT.VALUE.designation'='abcd')") |
| checkAnswer(sql("select distinct(designation) from restructure"), Row("abcd")) |
| // drop and add directdict column |
| sql("alter table restructure drop columns(designation)") |
| sql( |
| "alter table restructure add columns(designation timestamp) TBLPROPERTIES ('DEFAULT.VALUE" + |
| ".designation'= '17-01-2007')") |
| checkAnswer(sql("select distinct(designation) from restructure"), |
| Row(new java.sql.Timestamp(107, 0, 17, 0, 0, 0, 0))) |
| // drop and add msr column |
| sql("alter table restructure drop columns(designation)") |
| sql( |
| "alter table default.restructure add columns(designation int) TBLPROPERTIES" + |
| "('DEFAULT.VALUE.designation'='67890')") |
| checkAnswer(sql("select distinct(designation) from restructure"), Row(67890)) |
| CarbonProperties.getInstance() |
| .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, |
| CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) |
| |
| } |
| |
| test("test change datatype of int and decimal column") { |
| sql("alter table restructure add columns(intfield int, decimalfield decimal(10,2))") |
| sql("alter table default.restructure change intfield intField bigint") |
| checkExistence(sql("desc restructure"), true, "intfield", "bigint") |
| sql("alter table default.restructure change decimalfield deciMalfield Decimal(11,3)") |
| sql("alter table default.restructure change decimalfield deciMalfield Decimal(12,3)") |
| intercept[ProcessMetaDataException] { |
| sql("alter table default.restructure change decimalfield deciMalfield Decimal(12,3)") |
| } |
| intercept[ProcessMetaDataException] { |
| sql("alter table default.restructure change decimalfield deciMalfield Decimal(13,1)") |
| } |
| intercept[ProcessMetaDataException] { |
| sql("alter table default.restructure change decimalfield deciMalfield Decimal(13,5)") |
| } |
| sql("alter table default.restructure change decimalfield deciMalfield Decimal(13,4)") |
| } |
| |
| test("test change datatype of string to int column") { |
| try { |
| sql("alter table restructure change empname empname bigint") |
| sys.error("Exception should be thrown as empname string type change to bigint") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| |
| test("test change datatype of int to string column") { |
| try { |
| sql("alter table restructure change empno Empno string") |
| sys.error("Exception should be thrown as empno int type change to string") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| |
| test("test change datatype of non-existing column") { |
| try { |
| sql("alter table restructure change abcd abcd string") |
| sys.error("Exception should be thrown for datatype change on non-existing column") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| |
| test("test change datatype of decimal column from higher to lower precision/scale") { |
| sql("alter table restructure add columns(decField decimal(10,2))") |
| try { |
| sql("alter table restructure change decField decField decimal(10,1)") |
| sys.error("Exception should be thrown for downgrade of scale in decimal type") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| try { |
| sql("alter table restructure change decField decField decimal(5,3)") |
| sys.error("Exception should be thrown for downgrade of precision in decimal type") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| |
| test("test to rename table") { |
| sql("alter table restructure_test rename to restructure_new") |
| val result = sql("select * from restructure_new") |
| assert(result.count().equals(10L)) |
| } |
| |
| ignore("test to check if bad record folder name is changed") { |
| sql("alter table restructure_bad rename to restructure_badnew") |
| val oldLocation = new File("./target/test/badRecords/default/restructure_bad") |
| val newLocation = new File("./target/test/badRecords/default/restructure_badnew") |
| assert(!oldLocation.exists()) |
| assert(newLocation.exists()) |
| } |
| |
| test("test to rename table with invalid table name") { |
| try { |
| sql("alter table restructure_invalid rename to restructure_new") |
| sys.error("Invalid table name error should be thrown") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| } |
| } |
| |
| test("test to rename table with table already exists") { |
| try { |
| sql("alter table restructure rename to restructure") |
| sys.error("same table name exception should be thrown") |
| } catch { |
| case e: Exception => |
| println(e.getMessage) |
| assert(true) |
| } |
| } |
| |
| test("test to load data after rename") { |
| sql( |
| s"""LOAD DATA LOCAL INPATH '$resourcesPath/data.csv' INTO TABLE restructure_new OPTIONS |
| |('DELIMITER'= ',', 'QUOTECHAR'= '\"')""".stripMargin) |
| val result = sql("select * from restructure_new") |
| assert(result.count().equals(20L)) |
| } |
| |
| test("test table rename without use db, let current db be default") { |
| sql("drop database if exists testdb cascade") |
| sql("create database testdb") |
| sql("create table testdb.test1(name string, id int) stored by 'carbondata'") |
| sql("insert into testdb.test1 select 'xx',1") |
| sql("insert into testdb.test1 select 'xx',11") |
| sql("alter table testdb.test1 rename to testdb.test2") |
| checkAnswer(sql("select * from testdb.test2"), Seq(Row("xx", 1), Row("xx", 11))) |
| sql("drop table testdb.test2") |
| sql("drop database testdb") |
| } |
| |
| test("test to check if the lock file is successfully deleted") { |
| sql("create table lock_check(id int, name string) stored by 'carbondata'") |
| sql("alter table lock_check rename to lock_rename") |
| assert(!new File(s"${ CarbonCommonConstants.STORE_LOCATION } + /lock_rename/meta.lock") |
| .exists()) |
| } |
| |
| test("table rename with dbname in Camel Case") { |
| sql("drop table if exists uniqdata") |
| sql("drop table if exists uniqdata1") |
| sql("""CREATE TABLE uniqdata (CUST_ID int,CUST_NAME String) STORED BY 'org.apache.carbondata.format'""") |
| sql("""insert into table uniqdata values(1,"hello")""") |
| sql("alter table Default.uniqdata rename to uniqdata1") |
| checkAnswer(sql("select * from Default.uniqdata1"), Row(1,"hello")) |
| } |
| |
| // test query before&after renaming, see CARBONDATA-1690 |
| test("RenameTable_query_before_and_after_renaming") { |
| try { |
| sql(s"""create table test1 (name string, id int) stored by 'carbondata'""").collect |
| sql(s"""create table test2 (name string, id int) stored by 'carbondata'""").collect |
| sql(s"""insert into test1 select 'xx1',1""").collect |
| sql(s"""insert into test2 select 'xx2',2""").collect |
| // query before rename |
| checkAnswer(sql(s"""select * from test1"""), Seq(Row("xx1", 1))) |
| sql(s"""alter table test1 RENAME TO test3""").collect |
| sql(s"""alter table test2 RENAME TO test1""").collect |
| // query after rename |
| checkAnswer(sql(s"""select * from test1"""), Seq(Row("xx2", 2))) |
| } catch { |
| case e: Exception => |
| assert(false) |
| } finally { |
| sql(s"""drop table if exists test1""").collect |
| sql(s"""drop table if exists test3""").collect |
| sql(s"""drop table if exists test2""").collect |
| } |
| } |
| |
| // after changing default sort_scope to no_sort, all dimensions are not selected for sorting. |
| ignore("describe formatted for default sort_columns pre and post alter") { |
| sql("CREATE TABLE defaultSortColumnsWithAlter (empno int, empname String, designation String,role String, doj Timestamp) STORED BY 'org.apache.carbondata.format' " + |
| "tblproperties('DICTIONARY_INCLUDE'='empno','DICTIONARY_EXCLUDE'='role')") |
| sql("alter table defaultSortColumnsWithAlter drop columns (designation)") |
| sql("alter table defaultSortColumnsWithAlter add columns (designation12 String)") |
| checkExistence(sql("describe formatted defaultSortColumnsWithAlter"),true,"Sort Columns empno, empname, role, doj") |
| } |
| test("describe formatted for specified sort_columns pre and post alter") { |
| sql("CREATE TABLE specifiedSortColumnsWithAlter (empno int, empname String, designation String,role String, doj Timestamp) STORED BY 'org.apache.carbondata.format' " + |
| "tblproperties('sort_columns'='empno,empname,designation,role,doj','DICTIONARY_INCLUDE'='empno','DICTIONARY_EXCLUDE'='role')") |
| sql("alter table specifiedSortColumnsWithAlter drop columns (designation)") |
| sql("alter table specifiedSortColumnsWithAlter add columns (designation12 String)") |
| checkExistence(sql("describe formatted specifiedSortColumnsWithAlter"),true,"Sort Columns empno, empname, role, doj") |
| } |
| |
| test("test to check select columns after alter commands with null values"){ |
| sql("drop table if exists restructure") |
| sql("drop table if exists restructure1") |
| sql( |
| "CREATE TABLE restructure (empno int, empname String, designation String, doj Timestamp, " + |
| "workgroupcategory int, workgroupcategoryname String, deptno int, deptname String, " + |
| "projectcode int, projectjoindate Timestamp, projectenddate Timestamp,attendance int," + |
| "utilization int,salary int) STORED BY 'org.apache.carbondata.format'") |
| sql( |
| s"""LOAD DATA LOCAL INPATH '$resourcesPath/data.csv' INTO TABLE restructure OPTIONS |
| |('DELIMITER'= ',', 'QUOTECHAR'= '\"')""".stripMargin) |
| sql("ALTER TABLE restructure rename to restructure1") |
| sql("ALTER TABLE restructure1 ADD COLUMNS (projId int)") |
| sql("ALTER TABLE restructure1 DROP COLUMNS (projId)") |
| sql("ALTER TABLE restructure1 CHANGE empno empno BIGINT") |
| sql("ALTER TABLE restructure1 ADD COLUMNS (a1 INT, b1 STRING) TBLPROPERTIES('DICTIONARY_EXCLUDE'='b1')") |
| checkAnswer(sql("select a1,b1,empname from restructure1 where a1 is null and b1 is null and empname='arvind'"),Row(null,null,"arvind")) |
| sql("drop table if exists restructure1") |
| sql("drop table if exists restructure") |
| } |
| test("test alter command for boolean data type with correct default measure value") { |
| sql("create table testalterwithboolean(id int,name string) stored by 'carbondata' ") |
| sql("insert into testalterwithboolean values(1,'anubhav') ") |
| sql( |
| "alter table testalterwithboolean add columns(booleanfield boolean) tblproperties('default.value.booleanfield'='true')") |
| checkAnswer(sql("select * from testalterwithboolean"),Seq(Row(1,"anubhav",true))) |
| } |
| test("test alter command for boolean data type with out default measure value") { |
| sql("create table testalterwithbooleanwithoutdefaultvalue(id int,name string) stored by 'carbondata' ") |
| sql("insert into testalterwithbooleanwithoutdefaultvalue values(1,'anubhav') ") |
| sql( |
| "alter table testalterwithbooleanwithoutdefaultvalue add columns(booleanfield boolean)") |
| checkAnswer(sql("select * from testalterwithbooleanwithoutdefaultvalue"),Seq(Row(1,"anubhav",null))) |
| } |
| test("test alter command for filter on default values on date datatype") { |
| sql("drop table if exists test") |
| sql( |
| "create table test(id int,vin string,phonenumber long,area string,salary int,country " + |
| "string,longdate date) stored by 'carbondata'") |
| sql("insert into test select 1,'String1',12345,'area',20,'country','2017-02-12'") |
| sql("alter table test add columns (c3 date) TBLPROPERTIES('DEFAULT.VALUE.c3' = '1993-01-01')") |
| sql("alter table test add columns (c4 date)") |
| sql("insert into test select 2,'String1',12345,'area',20,'country','2017-02-12','1994-01-01','1994-01-01'") |
| sql("insert into test select 3,'String1',12345,'area',20,'country','2017-02-12','1995-01-01','1995-01-01'") |
| sql("insert into test select 4,'String1',12345,'area',20,'country','2017-02-12','1996-01-01','1996-01-01'") |
| checkAnswer(sql("select id from test where c3='1993-01-01'"), Seq(Row(1))) |
| checkAnswer(sql("select id from test where c3!='1993-01-01'"), Seq(Row(2),Row(3),Row(4))) |
| checkAnswer(sql("select id from test where c3<'1995-01-01'"), Seq(Row(1), Row(2))) |
| checkAnswer(sql("select id from test where c3>'1994-01-01'"), Seq(Row(3), Row(4))) |
| checkAnswer(sql("select id from test where c3>='1995-01-01'"), Seq(Row(3), Row(4))) |
| checkAnswer(sql("select id from test where c3<='1994-01-01'"), Seq(Row(1), Row(2))) |
| checkAnswer(sql("select id from test where c4 IS NULL"), Seq(Row(1))) |
| checkAnswer(sql("select id from test where c4 IS NOT NULL"), Seq(Row(2),Row(3),Row(4))) |
| } |
| |
| test("test alter command for filter on default values on timestamp datatype") { |
| def testFilterWithDefaultValue(flag: Boolean) = { |
| try { |
| CarbonProperties.getInstance() |
| .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, |
| "yyyy/MM/dd HH:mm:ss") |
| sql("drop table if exists test") |
| sql( |
| "create table test(id int,vin string,phonenumber long,area string,salary int,country " + |
| "string,longdate date) stored by 'carbondata'") |
| sql("insert into test select 1,'String1',12345,'area',20,'country','2017-02-12'") |
| if (flag) { |
| sql( |
| "alter table test add columns (c3 timestamp) TBLPROPERTIES('DEFAULT.VALUE.c3' = " + |
| "'1996/01/01 11:11:11', 'DICTIONARY_INCLUDE' = 'c3')") |
| } else { |
| sql( |
| "alter table test add columns (c3 timestamp) TBLPROPERTIES('DEFAULT.VALUE.c3' = " + |
| "'1996/01/01 11:11:11')") |
| } |
| sql("alter table test add columns (c4 timestamp)") |
| sql( |
| "insert into test select 2,'String1',12345,'area',20,'country','2017-02-12','1994-01-01 10:10:10','1994-01-01 10:10:10'") |
| sql( |
| "insert into test select 3,'String1',12345,'area',20,'country','2017-02-12','1995-01-01 11:11:11','1995-01-01 11:11:11'") |
| sql( |
| "insert into test select 4,'String1',12345,'area',20,'country','2017-02-12','1996-01-01 10:10:10','1996-01-01 10:10:10'") |
| checkAnswer(sql("select id from test where c3='1996-01-01 11:11:11'"), Seq(Row(1))) |
| checkAnswer(sql("select id from test where c3!='1996-01-01 11:11:11'"), Seq(Row(2),Row(3),Row(4))) |
| checkAnswer(sql("select id from test where c3<'1995-01-01 11:11:11'"), Seq(Row(2))) |
| checkAnswer(sql("select id from test where c3>'1994-01-02 11:11:11'"), |
| Seq(Row(3), Row(4), Row(1))) |
| checkAnswer(sql("select id from test where c3>='1995-01-01 11:11:11'"), |
| Seq(Row(3), Row(4), Row(1))) |
| checkAnswer(sql("select id from test where c3<='1995-01-02 11:11:11'"), Seq(Row(2), Row(3))) |
| checkAnswer(sql("select id from test where c4 IS NULL"), Seq(Row(1))) |
| checkAnswer(sql("select id from test where c4 IS NOT NULL"), Seq(Row(2),Row(3),Row(4))) |
| } |
| finally { |
| CarbonProperties.getInstance() |
| .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, |
| CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) |
| } |
| } |
| |
| testFilterWithDefaultValue(false) |
| testFilterWithDefaultValue(true) |
| |
| } |
| |
| test("test alter command for filter on default values on int") { |
| def testFilterWithDefaultValue(flag: Boolean) = { |
| sql("drop table if exists test") |
| sql( |
| "create table test(id int,vin string,phonenumber long,area string,salary int,country " + |
| "string,longdate date) stored by 'carbondata'") |
| sql("insert into test select 1,'String1',12345,'area',5000,'country','2017/02/12'") |
| if (flag) { |
| sql(s"alter table test add columns (c3 int) TBLPROPERTIES('DEFAULT.VALUE.c3' = '23', " + |
| s"'DICTIONARY_INCLUDE'='c3')") |
| } else { |
| sql(s"alter table test add columns (c3 int) TBLPROPERTIES('DEFAULT.VALUE.c3' = '23')") |
| } |
| sql(s"alter table test add columns (c4 int)") |
| sql("insert into test select 2,'String1',12345,'area',5000,'country','2017/02/12',25,25") |
| sql("insert into test select 3,'String1',12345,'area',5000,'country','2017/02/12',35,35") |
| sql("insert into test select 4,'String1',12345,'area',5000,'country','2017/02/12',45,45") |
| checkAnswer(sql("select id from test where c3=23"), Seq(Row(1))) |
| checkAnswer(sql("select id from test where c3!=23"), Seq(Row(2), Row(3), Row(4))) |
| checkAnswer(sql("select id from test where c3<34"), Seq(Row(1), Row(2))) |
| checkAnswer(sql("select id from test where c3>24"), Seq(Row(2), Row(3), Row(4))) |
| checkAnswer(sql("select id from test where c3>=35"), Seq(Row(3), Row(4))) |
| checkAnswer(sql("select id from test where c3<=35"), Seq(Row(1), Row(2), Row(3))) |
| checkAnswer(sql("select id from test where c4 IS NULL"), Seq(Row(1))) |
| checkAnswer(sql("select id from test where c4 IS NOT NULL"), Seq(Row(2),Row(3),Row(4))) |
| } |
| |
| testFilterWithDefaultValue(true) |
| testFilterWithDefaultValue(false) |
| } |
| test("Filter query on Restructure and updated table") { |
| sql( |
| """ |
| CREATE TABLE retructure_iud(id int, name string, city string, age int) |
| STORED BY 'org.apache.carbondata.format' |
| """) |
| val testData = s"$resourcesPath/sample.csv" |
| sql(s"LOAD DATA LOCAL INPATH '$testData' into table retructure_iud") |
| sql("ALTER TABLE retructure_iud ADD COLUMNS (newField STRING) " + |
| "TBLPROPERTIES ('DEFAULT.VALUE.newField'='def', 'DICTIONARY_INCLUDE'='newField')").show() |
| sql("ALTER TABLE retructure_iud ADD COLUMNS (newField1 STRING) " + |
| "TBLPROPERTIES ('DEFAULT.VALUE.newField1'='def', 'DICTIONARY_EXCLUDE'='newField1')").show() |
| // update operation |
| sql("""update retructure_iud d set (d.id) = (d.id + 1) where d.id > 2""").show() |
| checkAnswer( |
| sql("select count(*) from retructure_iud where id = 2 and newfield1='def'"), |
| Seq(Row(1)) |
| ) |
| } |
| |
| test("Alter table selection in random order"){ |
| def test(): Unit ={ |
| sql("drop table if exists restructure_random_select") |
| sql("create table restructure_random_select (imei string,channelsId string,gamePointId double,deviceInformationId double," + |
| " deliverycharge double) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('table_blocksize'='2000','sort_columns'='imei')") |
| sql("insert into restructure_random_select values('abc','def',50.5,30.2,40.6) ") |
| sql("Alter table restructure_random_select add columns (age int,name String)") |
| checkAnswer( |
| sql("select gamePointId,deviceInformationId,age,name from restructure_random_select where name is NULL or channelsId=4"), |
| Seq(Row(50.5,30.2,null,null))) |
| checkAnswer( |
| sql("select age,name,gamePointId,deviceInformationId from restructure_random_select where name is NULL or channelsId=4"), |
| Seq(Row(null,null,50.5,30.2))) |
| } |
| try { |
| test() |
| CarbonProperties.getInstance() |
| .addProperty(CarbonCommonConstants.ENABLE_VECTOR_READER, "false") |
| test() |
| } |
| finally { |
| CarbonProperties.getInstance() |
| .addProperty(CarbonCommonConstants.ENABLE_VECTOR_READER, |
| CarbonCommonConstants.ENABLE_VECTOR_READER_DEFAULT) |
| } |
| } |
| |
| test("load table after alter drop column scenario") { |
| sql("drop table if exists alterTable") |
| sql( |
| "create table alterTable(empno string, salary string) stored by 'carbondata' tblproperties" + |
| "('sort_columns'='')") |
| sql("alter table alterTable drop columns(empno)") |
| sql("alter table alterTable add columns(empno string)") |
| sql(s"load data local inpath '$resourcesPath/double.csv' into table alterTable options" + |
| s"('header'='true')") |
| checkAnswer(sql("select salary from alterTable limit 1"), Row(" 775678765456789098765432.789")) |
| } |
| |
| test("load partition table after alter drop column scenario") { |
| val timestampFormat = CarbonProperties.getInstance().getProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT) |
| CarbonProperties.getInstance().addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, "dd-MM-yyyy") |
| sql("drop table if exists alterPartitionTable") |
| sql( |
| """ |
| | CREATE TABLE alterPartitionTable (empname String, designation String, doj Timestamp, |
| | workgroupcategory int, workgroupcategoryname String, deptno int, deptname String, |
| | projectcode int, projectjoindate Timestamp, projectenddate Timestamp,attendance int, |
| | utilization int,salary int) |
| | PARTITIONED BY (empno int) |
| | STORED BY 'org.apache.carbondata.format' |
| | TBLPROPERTIES('SORT_COLUMNS'='empname,deptno,projectcode,projectjoindate, |
| | projectenddate,attendance') |
| """.stripMargin) |
| sql("alter table alterPartitionTable drop columns(projectenddate)") |
| sql("alter table alterPartitionTable add columns(projectenddate timestamp)") |
| sql(s"LOAD DATA local inpath '$resourcesPath/data.csv' INTO TABLE alterPartitionTable OPTIONS('DELIMITER'= ',', " + |
| "'QUOTECHAR'= '\"')") |
| sql("select * from alterPartitionTable where empname='bill'").show(false) |
| CarbonProperties.getInstance() |
| .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, timestampFormat) |
| } |
| |
| |
| test("Alter Table Change Sort Scope 1") { |
| sql("DROP TABLE IF EXISTS t1") |
| sql(s"CREATE TABLE t1(age int, name string) STORED BY 'carbondata' TBLPROPERTIES" + |
| s"('sort_columns'='age', 'sort_scope'='local_sort')") |
| sql("ALTER TABLE t1 SET TBLPROPERTIES('sort_scope'='global_sort')") |
| assert(sortScopeInDescFormatted("t1").equalsIgnoreCase("global_sort")) |
| sql("DROP TABLE t1") |
| } |
| |
| test("Alter Table Change Sort Scope 2") { |
| sql("DROP TABLE IF EXISTS t1") |
| sql(s"CREATE TABLE t1(age int, name string) STORED BY 'carbondata' TBLPROPERTIES" + |
| s"('sort_columns'='age', 'sort_scope'='local_sort')") |
| sql("ALTER TABLE t1 SET TBLPROPERTIES('sort_scope'='no_sort')") |
| assert(sortScopeInDescFormatted("t1").equalsIgnoreCase("NO_SORT")) |
| sql("DROP TABLE t1") |
| } |
| |
| test("Alter Table Change Sort Scope 3") { |
| sql("DROP TABLE IF EXISTS t1") |
| sql(s"CREATE TABLE t1(age int, name string) STORED BY 'carbondata' TBLPROPERTIES" + |
| s"('sort_columns'='')") |
| |
| // This throws exception as SORT_COLUMNS is empty |
| intercept[RuntimeException] { |
| sql("ALTER TABLE t1 SET TBLPROPERTIES('sort_scope'='local_sort')") |
| } |
| |
| // Even if we change the SORT_SCOPE to LOCAL_SORT |
| // the SORT_SCOPE should remain to NO_SORT |
| // because SORT_COLUMNS does not contain anything. |
| assert(sortScopeInDescFormatted("t1").equalsIgnoreCase("NO_SORT")) |
| sql("DROP TABLE t1") |
| } |
| |
| test("Alter Table Change Sort Scope 4") { |
| sql("DROP TABLE IF EXISTS t1") |
| sql(s"CREATE TABLE t1(age int, name string) STORED BY 'carbondata' TBLPROPERTIES" + |
| s"('sort_columns'='age', 'sort_scope'='local_sort')") |
| sql("ALTER TABLE t1 UNSET TBLPROPERTIES('sort_scope')") |
| |
| // Unsetting the SORT_SCOPE should change the SORT_SCOPE to |
| // CarbonCommonConstants.LOAD_SORT_SCOPE_DEFAULT |
| assert(sortScopeInDescFormatted("t1") |
| .equalsIgnoreCase(CarbonCommonConstants.LOAD_SORT_SCOPE_DEFAULT)) |
| sql("DROP TABLE t1") |
| } |
| |
| test("Alter Table Change Sort Scope 5") { |
| sql("DROP TABLE IF EXISTS t1") |
| sql(s"CREATE TABLE t1(age int, name string) STORED BY 'carbondata' TBLPROPERTIES" + |
| s"('sort_scope'='local_sort', 'sort_columns'='age')") |
| intercept[RuntimeException] { |
| sql("ALTER TABLE t1 SET TBLPROPERTIES('sort_scope'='fake_sort')") |
| } |
| |
| // SORT_SCOPE should remain unchanged |
| assert(sortScopeInDescFormatted("t1").equalsIgnoreCase("LOCAL_SORT")) |
| sql("DROP TABLE t1") |
| } |
| |
| def sortScopeInDescFormatted(tableName: String): String = { |
| sql(s"DESCRIBE FORMATTED $tableName").filter( |
| (x: Row) => x.getString(0).equalsIgnoreCase("sort scope") |
| ).collectAsList().get(0).get(1).toString |
| } |
| |
| |
| override def afterAll { |
| sql("DROP TABLE IF EXISTS restructure") |
| sql("drop table if exists table1") |
| sql("DROP TABLE IF EXISTS restructure_new") |
| sql("DROP TABLE IF EXISTS restructure_test") |
| sql("DROP TABLE IF EXISTS restructure_bad") |
| sql("DROP TABLE IF EXISTS restructure_badnew") |
| sql("DROP TABLE IF EXISTS lock_rename") |
| sql("drop table if exists uniqdata") |
| sql("drop table if exists uniqdata1") |
| sql("drop table if exists defaultSortColumnsWithAlter") |
| sql("drop table if exists specifiedSortColumnsWithAlter") |
| sql("drop table if exists allKeyCol") |
| sql("drop table if exists testalterwithboolean") |
| sql("drop table if exists testalterwithbooleanwithoutdefaultvalue") |
| sql("drop table if exists test") |
| sql("drop table if exists retructure_iud") |
| sql("drop table if exists restructure_random_select") |
| sql("drop table if exists alterTable") |
| sql("drop table if exists alterPartitionTable") |
| } |
| } |
