http://git-wip-us.apache.org/repos/asf/carbondata/blob/14624953/integration/spark-common-cluster-test/src/test/scala/org/apache/carbondata/cluster/sdv/generated/DataLoadingTestCase.scala ---------------------------------------------------------------------- diff --git a/integration/spark-common-cluster-test/src/test/scala/org/apache/carbondata/cluster/sdv/generated/DataLoadingTestCase.scala b/integration/spark-common-cluster-test/src/test/scala/org/apache/carbondata/cluster/sdv/generated/DataLoadingTestCase.scala new file mode 100644 index 0000000..a931710 --- /dev/null +++ b/integration/spark-common-cluster-test/src/test/scala/org/apache/carbondata/cluster/sdv/generated/DataLoadingTestCase.scala @@ -0,0 +1,1476 @@ + +/* + * 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.cluster.sdv.generated + +import java.text.SimpleDateFormat +import java.util.Date + +import org.apache.spark.sql.Row +import org.apache.spark.sql.common.util._ +import org.apache.spark.sql.test.TestQueryExecutor +import org.scalatest.BeforeAndAfterAll + +import org.apache.carbondata.core.constants.CarbonCommonConstants +import org.apache.carbondata.core.util.CarbonProperties + +/** + * Test Class for DataLoadingTestCase to verify all scenerios + */ + +class DataLoadingTestCase extends QueryTest with BeforeAndAfterAll { + + + + //Data load--->Action--->Redirect--->Logger-->True + test("DataSight_Carbon_BadRecord_Dataload_001", Include) { + sql(s"""drop table if exists uniqdata""").collect + sql(s"""CREATE TABLE uniqdata (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/2000_UniqData.csv' into table uniqdata OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='CUST_ID,CUST_NAME,ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN1,Double_COLUMN2,INTEGER_COLUMN1')""").collect + checkAnswer(s"""select count(*) from uniqdata""", + Seq(Row(2013)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_001") + sql(s"""drop table uniqdata""").collect + } + + + //Data load--->Action--->FORCE--->Logger-->True + test("DataSight_Carbon_BadRecord_Dataload_002", Include) { + sql(s"""drop table if exists uniqdata""").collect + sql(s"""CREATE TABLE uniqdata (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/2000_UniqData.csv' into table uniqdata OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='CUST_ID,CUST_NAME,ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN1,Double_COLUMN2,INTEGER_COLUMN1')""").collect + checkAnswer(s"""select count(*) from uniqdata""", + Seq(Row(2013)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_002") + sql(s"""drop table uniqdata""").collect + } + + + //Data load--->Action--->IGNORE--->Logger-->True + test("DataSight_Carbon_BadRecord_Dataload_003", Include) { + sql(s"""CREATE TABLE uniqdata (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/2000_UniqData.csv' into table uniqdata OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='IGNORE','FILEHEADER'='CUST_ID,CUST_NAME,ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN1,Double_COLUMN2,INTEGER_COLUMN1')""").collect + checkAnswer(s"""select count(*) from uniqdata""", + Seq(Row(2010)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_003") + sql(s"""drop table uniqdata""").collect + } + + + //Data load--->Action--->Ignore--->Logger-->False + test("DataSight_Carbon_BadRecord_Dataload_004", Include) { + sql(s"""drop table if exists uniqdata""").collect + sql(s""" CREATE TABLE uniqdata (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/2000_UniqData.csv' into table uniqdata OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='FALSE', 'BAD_RECORDS_ACTION'='IGNORE','FILEHEADER'='CUST_ID,CUST_NAME,ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN1,Double_COLUMN2,INTEGER_COLUMN1')""").collect + checkAnswer(s"""select count(*) from uniqdata""", + Seq(Row(2010)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_004") + sql(s"""drop table if exists uniqdata""").collect + } + + + //Data load--->Action--->FORCE--->Logger-->False + test("DataSight_Carbon_BadRecord_Dataload_005", Include) { + sql(s"""drop table if exists uniqdata""").collect + sql(s""" CREATE TABLE uniqdata (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/2000_UniqData.csv' into table uniqdata OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='FALSE', 'BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='CUST_ID,CUST_NAME,ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN1,Double_COLUMN2,INTEGER_COLUMN1')""").collect + checkAnswer(s"""select count(*) from uniqdata""", + Seq(Row(2013)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_005") + sql(s"""drop table uniqdata""").collect + } + + + //Data load--->Action--->Redirect--->Logger-->False + test("DataSight_Carbon_BadRecord_Dataload_006", Include) { + sql(s"""drop table if exists uniqdata""").collect + sql(s""" CREATE TABLE uniqdata (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/2000_UniqData.csv' into table uniqdata OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='FALSE', 'BAD_RECORDS_ACTION'='REDIRECT','FILEHEADER'='CUST_ID,CUST_NAME,ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN1,Double_COLUMN2,INTEGER_COLUMN1')""").collect + checkAnswer(s"""select count(*) from uniqdata""", + Seq(Row(2010)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_006") + sql(s"""drop table uniqdata""").collect + } + + + //Data load-->Dictionary_Exclude + test("DataSight_Carbon_BadRecord_Dataload_007", Include) { + sql(s"""CREATE TABLE uniq_exclude (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('DICTIONARY_EXCLUDE'='CUST_NAME,ACTIVE_EMUI_VERSION')""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/2000_UniqData.csv' into table uniq_exclude OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='REDIRECT','FILEHEADER'='CUST_ID,CUST_NAME,ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN1,Double_COLUMN2,INTEGER_COLUMN1')""").collect + checkAnswer(s"""select count(*) from uniq_exclude""", + Seq(Row(2010)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_007") + sql(s"""drop table uniq_exclude""").collect + } + + + //Data load-->Extra_Column_in table + test("DataSight_Carbon_BadRecord_Dataload_010", Include) { + sql(s"""CREATE TABLE exceed_column_in_table (cust_id int ,CUST_NAME String,date timestamp,date2 timestamp) STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/extra_column.csv' into table exceed_column_in_table OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='cust_id,CUST_NAME,date,date2')""").collect + checkAnswer(s"""select count(*) from exceed_column_in_table""", + Seq(Row(2)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_010") + sql(s"""drop table exceed_column_in_table""").collect + } + + + //Data load-->Empty BadRecords Parameters + test("DataSight_Carbon_BadRecord_Dataload_011", Include) { + try { + sql(s"""CREATE TABLE badrecords_test1 (ID int,CUST_ID int,sal int,cust_name string) STORED BY 'org.apache.carbondata.format'""") + + .collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/bad_records1.csv' into table badrecords_test1 OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='', 'BAD_RECORDS_ACTION'='','FILEHEADER'='ID,CUST_ID,sal,cust_name')""") + .collect + checkAnswer( + s"""select count(*) from badrecords_test1""", + Seq(Row(0)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_011") + assert(false) + } catch { + case _ => assert(true) + } + sql(s"""drop table badrecords_test1""").collect + } + + + //Data load-->Range Exceed + test("DataSight_Carbon_BadRecord_Dataload_012", Include) { + sql(s"""CREATE TABLE all_data_types_range (integer_column int,string_column string,double_Column double,decimal_column decimal,bigint_Column bigint) STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/all_data_types_range.csv' into table all_data_types_range OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='integer_column,string_column,double_Column,decimal_column,bigint_Column')""").collect + checkAnswer(s"""select count(*) from all_data_types_range""", + Seq(Row(2)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_012") + sql(s"""drop table all_data_types_range""").collect + } + + + //Data load-->Escape_Character + test("DataSight_Carbon_BadRecord_Dataload_013", Include) { + sql(s"""CREATE TABLE Escape_test(integer_col int,String_col String,Integer_column2 int) STORED BY 'org.apache.carbondata.format'""").collect + + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/EScape_Test.csv' into table Escape_test OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='integer_col,String_col,Integer_column2')""").collect + checkAnswer(s"""select count(*) from Escape_test""", + Seq(Row(3)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_013") + sql(s"""drop table Escape_test""").collect + } + + + //Data load-->All_Bad_Records_IN CSV + test("DataSight_Carbon_BadRecord_Dataload_014", Include) { + sql(s"""CREATE TABLE test25(integer_col int,integer_col2 int,String_col String,decimal_col decimal,double_col double,date timestamp) STORED BY 'org.apache.carbondata.format'""").collect + + + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/badrecords_test6.csv' into table test25 OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='\','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='integer_col,integer_col2,String_col,decimal_col,double_col,date')""").collect + checkAnswer(s"""select count(*) from test25""", + Seq(Row(1)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_014") + sql(s"""drop table test25""").collect + } + + + //Data load-->CSV_Contain_Single_Space + test("DataSight_Carbon_BadRecord_Dataload_015", Include) { + sql(s"""CREATE TABLE test3 (ID int,CUST_ID int,cust_name string) STORED BY 'org.apache.carbondata.format'""").collect + + + + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/test3.csv' into table test3 OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','FILEHEADER'='ID,CUST_ID,Cust_name')""").collect + checkAnswer(s"""select count(*) from test3""", + Seq(Row(4)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_015") + sql(s"""drop table test3""").collect + } + + + //Data load-->Multiple_Csv + test("DataSight_Carbon_BadRecord_Dataload_016", Include) { + sql(s"""CREATE TABLE multicsv_check(integer_col int,integer_col2 int,String_col String,decimal_col decimal,double_col double,date timestamp) STORED BY 'org.apache.carbondata.format'""").collect + + + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/Test' into table multicsv_check OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='\','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='REDIRECT','FILEHEADER'='integer_col,integer_col2,String_col,decimal_col,double_col,date')""").collect + checkAnswer(s"""select count(*) from multicsv_check""", + Seq(Row(2)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_016") + sql(s"""drop table multicsv_check""").collect + } + + + //Data load-->Empty csv + test("DataSight_Carbon_BadRecord_Dataload_017", Include) { + intercept[Exception] { + sql(s"""CREATE TABLE emptycsv_check(integer_col int,integer_col2 int,String_col String,decimal_col decimal,double_col double,date timestamp) STORED BY 'org.apache.carbondata.format'""").collect + + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/empty.csv' into table emptycsv_check OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='\','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='integer_col,integer_col2,String_col,decimal_col,double_col,date')""") + .collect + } + checkAnswer(s"""select count(*) from emptycsv_check """, + Seq(Row(0)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_017") + + sql(s"""drop table emptycsv_check """).collect + } + + + //Data load-->Datatype contain value of Other Datatype + test("DataSight_Carbon_BadRecord_Dataload_018", Include) { + sql(s"""CREATE TABLE datatype_check(integer_col int,integer_col2 int,String_col String) STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/datatype.csv' into table datatype_check OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='\','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='integer_col,integer_col2,String_col')""").collect + checkAnswer( + s"""select count(*) from datatype_check""", + Seq(Row(1)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_018") + sql(s"""drop table datatype_check""").collect + } + + + //Data load-->Extra_Column_incsv + test("DataSight_Carbon_BadRecord_Dataload_019", Include) { + sql(s"""CREATE TABLE exceed_column_in_Csv (CUST_NAME String,date timestamp) STORED BY 'org.apache.carbondata.format'""").collect + intercept[Exception] { + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/extra_column.csv' into table exceed_column_in_Csv OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='REDIRECT','FILEHEADER'='CUST_NAME,date')""").collect + checkAnswer( + s"""select count(*) from exceed_column_in_Csv """, + Seq(Row(0)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_019") + } + sql(s"""drop table exceed_column_in_Csv """).collect + } + + + //Data load-->Timestamp Exceed Range + test("DataSight_Carbon_BadRecord_Dataload_020", Include) { + sql(s"""CREATE TABLE timestamp_range (date timestamp) STORED BY 'org.apache.carbondata.format'""").collect + intercept[Exception] { + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/timetsmap.csv' into table timestamp_range OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='REDIRECT','FILEHEADER'='date')""").collect + } + checkAnswer(s"""select count(*) from timestamp_range""", + Seq(Row(0)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_020") + sql(s"""drop table timestamp_range""").collect + } + + + //Show loads-->Delimeter_check + test("DataSight_Carbon_BadRecord_Dataload_021", Include) { + sql(s"""CREATE TABLE bad_records_test5 (String_col string,integer_col int,decimal_column decimal,date timestamp,double_col double) STORED BY 'org.apache.carbondata.format'""").collect + intercept[Exception] { + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/badrecords_test5.csv' into table bad_records_test5 OPTIONS('DELIMITER'='*' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='FALSE', 'BAD_RECORDS_ACTION'='IGNORE','FILEHEADER'='String_col,integer_col,decimal_column,date,double_col') """).collect + } + checkAnswer(s"""select count(*) from bad_records_test5""", + Seq(Row(0)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_021") + sql(s"""drop table bad_records_test5 """).collect + } + + + //Data load--->Action--->FAIL--->Logger-->True + test("DataSight_Carbon_BadRecord_Dataload_022", Include) { + dropTable("bad_records_test5") + sql(s"""CREATE TABLE bad_records_test5 (String_col string,integer_col int,decimal_column decimal,date timestamp,double_col double) STORED BY 'org.apache.carbondata.format'""").collect + intercept[Exception] { + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/badrecords_test5.csv' into table bad_records_test5 OPTIONS('DELIMITER'='*' , 'QUOTECHAR'='"','BAD_RECORDS_LOGGER_ENABLE'='TRUE', 'BAD_RECORDS_ACTION'='FAIL','FILEHEADER'='String_col,integer_col,decimal_column,date,double_col') """).collect + } + checkAnswer(s"""select count(*) from bad_records_test5""", + Seq(Row(0)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_022") + sql(s"""drop table bad_records_test5 """).collect + } + + + //Data load without any any action parameter + test("DataSight_Carbon_BadRecord_Dataload_023", Include) { + dropTable("bad_records_test5") + sql(s"""CREATE TABLE bad_records_test5 (String_col string,integer_col int,decimal_column decimal,date timestamp,double_col double) STORED BY 'org.apache.carbondata.format'""").collect + + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/badrecords_test5.csv' into table bad_records_test5 OPTIONS('DELIMITER'='*' , 'QUOTECHAR'='"','FILEHEADER'='String_col,integer_col,decimal_column,date,double_col') """).collect + checkAnswer(s"""select count(*) from bad_records_test5""", + Seq(Row(1)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_023") + sql(s"""drop table bad_records_test5 """).collect + } + + + //Check for insert into carbon table with all columns selected from Hive table where both tables having same number of columns + test("DataSight_Carbon_Insert_Func_005", Include) { + sql(s"""drop table IF EXISTS T_Hive1""").collect + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""create table T_Hive1(Active_status String, Item_type_cd INT, Qty_day_avg INT, Qty_total INT, Sell_price BIGINT, Sell_pricep DOUBLE, Discount_price DOUBLE , Profit DECIMAL(3,2), Item_code STRING, Item_name VARCHAR(50), Outlet_name CHAR(100), Update_time TIMESTAMP, Create_date String)row format delimited fields terminated by ',' collection items terminated by '$DOLLAR'""").collect + sql(s"""load data INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' overwrite into table T_Hive1""").collect + sql(s"""insert into T_Carbn01 select * from T_Hive1""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Hive1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_005") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with all columns selected from Parquet table where both tables having same number of columns + ignore("DataSight_Carbon_Insert_Func_006", Include) { + sql(s"""drop table IF EXISTS T_Parq1""").collect + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Parq1(Active_status BOOLEAN, Item_type_cd TINYINT, Qty_day_avg SMALLINT, Qty_total INT, Sell_price BIGINT, Sell_pricep FLOAT, Discount_price DOUBLE , Profit DECIMAL(3,2), Item_code STRING, Item_name VARCHAR(50), Outlet_name CHAR(100), Update_time TIMESTAMP, Create_date DATE) stored as 'parquet'""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""Insert into T_Parq1 select * from T_hive1""").collect + sql(s"""insert into T_Carbn01 select * from T_Parq1""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_hive1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_006") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with all columns selected from Carbon table where both tables having same number of columns + test("DataSight_Carbon_Insert_Func_007", Include) { + sql(s"""drop table IF EXISTS T_Carbn1""").collect + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""create table T_Carbn1(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table T_Carbn1 options ('DELIMITER'=',', 'QUOTECHAR'='\', 'FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date')""").collect + sql(s"""insert into T_Carbn01 select * from T_Carbn1""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + Seq(Row("TRUE",1,450,304034400,200000343430000000L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("TRUE",2,423,3046340,200000000003454300L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("TRUE",3,453,3003445,200000000000003450L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("TRUE",4,4350,3044364,200000000000000000L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("TRUE",114,4520,30000430,200000000004300000L,121.5,4.99,2.44,"RE3423ee","asfdsffdfg"),Row("FALSE",123,454,30000040,200000000000000000L,121.5,4.99,2.44,"RE3423ee","asfrewerfg"),Row("TRUE",11,4530,3000040,200000000000000000L,121.5,4.99,2.44,"SE3423ee","asfdsffder"),Row("TRUE",14,4590,3000400,200000000000000000L,121.5,4.99,2.44,"ASD423ee","asfertfdfg"),Row("FALSE",41,4250,0,200000000000000000L,121.5,4.99,2.44,"SAD423ee","asrtsffdfg"),Row("TRUE",13,4510,30400,200000000000000000L,121.5,4.99,2.44,"DE3423ee","asfrtffdfg")), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_007") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""drop table IF EXISTS T_Carbn1""").collect + } + + + //Check for insert into table providing values in the query + test("DataSight_Carbon_Insert_Func_001", Include) { + sql(s"""drop table IF EXISTS T_Carbn04""").collect + sql(s"""create table T_Carbn04(Item_code STRING, Item_name STRING)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn04 values('abc',1)""").collect + checkAnswer(s"""select * from T_Carbn04""", + Seq(Row("abc","1")), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_001") + sql(s"""drop table IF EXISTS T_Carbn04""").collect + } + + + //Check for insert into carbon table with all columns selected from Hive table where selected query is having more columns and the additional columns come after the equivalent columns + test("DataSight_Carbon_Insert_Func_008", Include) { + sql(s"""drop table IF EXISTS t_hive2""").collect + sql(s"""create table T_Hive2(Active_status String, Item_type_cd INT, Qty_day_avg SMALLINT, Qty_total INT, Sell_price BIGINT, Sell_pricep DOUBLE, Discount_price DOUBLE , Profit DECIMAL(3,2), Item_code STRING, Item_name VARCHAR(50), Outlet_name CHAR(100), Update_time TIMESTAMP, Create_date String,Profit_perc DECIMAL(4,3),name string)row format delimited fields terminated by ',' collection items terminated by '$DOLLAR'""").collect + sql(s"""load data INPATH '$resourcesPath/Data/InsertData/T_Hive2.csv' overwrite into table T_Hive2""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg SMALLINT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select * from T_Hive2""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Hive2 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_008") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with all columns selected from Parquet table where selected query is having more columns + ignore("DataSight_Carbon_Insert_Func_010", Include) { + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""drop table IF EXISTS T_Parq2""").collect + sql(s"""create table T_Parq2(Active_status String, Item_type_cd INT, Qty_day_avg SMALLINT, Qty_total INT, Sell_price BIGINT, Sell_pricep DOUBLE, Discount_price DOUBLE , Profit DECIMAL(3,2), Item_code STRING, Item_name VARCHAR(50), Outlet_name CHAR(100), Update_time TIMESTAMP, Create_date String,Profit_perc DECIMAL(4,3),name string) stored as 'parquet'""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""Insert into t_parq2 select * from T_Hive2""").collect + sql(s"""create table if not exists T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select * from T_Parq2""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Hive2 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_010") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with all columns selected from Carbon table where selected query is having more columns + test("DataSight_Carbon_Insert_Func_011", Include) { + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""drop table IF EXISTS t_carbn2""").collect + sql(s"""create table T_Carbn2(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String,Profit_perc DECIMAL(4,3), name string)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive2.csv' INTO table T_Carbn2 options ('DELIMITER'=',', 'QUOTECHAR'='\', 'FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date,Profit_perc,name')""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select * from T_Carbn2""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn2 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_011") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with select on hive when hiveis having TINYINT, SMALLINT data types + test("DataSight_Carbon_Insert_Func_015", Include) { + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select * from T_Hive1""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Hive1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_015") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with select on Hive table where selected query is having multiple values associated with DATE and TIMESTAMP data type + test("DataSight_Carbon_Insert_Func_016", Include) { + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select * from T_Hive1""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Hive1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_016") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with all columns selected from Hive table where data transformations done in the selected query on DATE + test("DataSight_Carbon_Insert_Func_018", Include) { + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,date_sub(Create_date, 200) from T_Hive1""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from (select Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,date_sub(Create_date, 200) from T_Hive1) t1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_018") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with all columns selected from Hive table where multiple tables are joined + ignore("DataSight_Carbon_Insert_Func_019", Include) { + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""drop table IF EXISTS T_hive4""").collect + sql(s"""drop table IF EXISTS T_hive5""").collect + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Hive4(Item_code STRING, Item_name VARCHAR(50))row format delimited fields terminated by ',' collection items terminated by '$DOLLAR'""").collect + sql(s"""create table T_Hive5(Item_code STRING, Profit DECIMAL(3,2))row format delimited fields terminated by ',' collection items terminated by '$DOLLAR'""").collect + sql(s"""load data INPATH '$resourcesPath/Data/InsertData/T_Hive4.csv' overwrite into table T_Hive4""").collect + sql(s"""load data INPATH '$resourcesPath/Data/InsertData/T_Hive5.csv' overwrite into table T_Hive5""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_carbn01 select x.Active_status,x.Item_type_cd,x.Qty_day_avg,x.Qty_total,x.Sell_price,x.Sell_pricep,x.Discount_price,z.Profit,x.Item_code,y.Item_name,x.Outlet_name,x.Update_time,x.Create_date from T_Hive1 x,T_Hive4 y, T_Hive5 z where x.Item_code = y.Item_code and x.Item_code = z.Item_code""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from (select x.Active_status,x.Item_type_cd,x.Qty_day_avg,x.Qty_total,x.Sell_price,x.Sell_pricep,x.Discount_price,z.Profit,x.Item_code,y.Item_name,x.Outlet_name,x.Update_time,x.Create_date from T_Hive1 x,T_Hive4 y, T_Hive5 z where x.Item_code = y.Item_code and x.Item_code = z.Item_code) t1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_019") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with all columns selected from Hive table where table is having the columns in different name + test("DataSight_Carbon_Insert_Func_020", Include) { + sql(s"""drop table IF EXISTS t_hive7""").collect + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Hive7(Active_status1 BOOLEAN, Item_type_cd1 TINYINT, Qty_day_avg1 SMALLINT, Qty_total1 INT, Sell_price1 BIGINT, Sell_pricep1 FLOAT, Discount_price1 DOUBLE , Profit1 DECIMAL(3,2), Item_code1 STRING, Item_name1 VARCHAR(50), Outlet_name1 CHAR(100), Update_time TIMESTAMP, Create_date DATE)row format delimited fields terminated by ',' collection items terminated by '$DOLLAR'""").collect + sql(s"""load data INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' overwrite into table T_Hive7""").collect + sql(s"""load data INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' into table T_Hive7""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select * from T_Hive7""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + Seq(Row("true",1,450,304034400,200000343430000000L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("true",1,450,304034400,200000343430000000L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("true",2,423,3046340,200000000003454300L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("true",2,423,3046340,200000000003454300L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("true",3,453,3003445,200000000000003450L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("true",3,453,3003445,200000000000003450L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("true",4,4350,3044364,200000000000000000L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("true",4,4350,3044364,200000000000000000L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("true",114,4520,30000430,200000000004300000L,121.5,4.99,2.44,"RE3423ee","asfdsffdfg"),Row("true",114,4520,30000430,200000000004300000L,121.5,4.99,2.44,"RE3423ee","asfdsffdfg"),Row("false",123,454,30000040,200000000000000000L,121.5,4.99,2.44,"RE3423ee","asfrewerfg"),Row("false",123,454,300 00040,200000000000000000L,121.5,4.99,2.44,"RE3423ee","asfrewerfg"),Row("true",11,4530,3000040,200000000000000000L,121.5,4.99,2.44,"SE3423ee","asfdsffder"),Row("true",11,4530,3000040,200000000000000000L,121.5,4.99,2.44,"SE3423ee","asfdsffder"),Row("true",14,4590,3000400,200000000000000000L,121.5,4.99,2.44,"ASD423ee","asfertfdfg"),Row("true",14,4590,3000400,200000000000000000L,121.5,4.99,2.44,"ASD423ee","asfertfdfg"),Row("false",41,4250,0,200000000000000000L,121.5,4.99,2.44,"SAD423ee","asrtsffdfg"),Row("false",41,4250,0,200000000000000000L,121.5,4.99,2.44,"SAD423ee","asrtsffdfg"),Row("true",13,4510,30400,200000000000000000L,121.5,4.99,2.44,"DE3423ee","asfrtffdfg"),Row("true",13,4510,30400,200000000000000000L,121.5,4.99,2.44,"DE3423ee","asfrtffdfg")), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_020") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with select on all column from a Hive table where table has no records + test("DataSight_Carbon_Insert_Func_021", Include) { + sql(s"""drop table IF EXISTS T_Hive8""").collect + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Hive8(Active_status BOOLEAN, Item_type_cd TINYINT, Qty_day_avg SMALLINT, Qty_total INT, Sell_price BIGINT, Sell_pricep FLOAT, Discount_price DOUBLE , Profit DECIMAL(3,2), Item_code STRING, Item_name VARCHAR(50), Outlet_name CHAR(100), Update_time TIMESTAMP, Create_date DATE)row format delimited fields terminated by ',' collection items terminated by '$DOLLAR'""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select * from T_Hive8""").collect + checkAnswer(s"""select count(*) from T_Carbn01""", + Seq(Row(0)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_021") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with select on all column from a Carbon table where table has no records + test("DataSight_Carbon_Insert_Func_023", Include) { + sql(s"""drop table IF EXISTS T_Carbn02""").collect + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Carbn02(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select * from T_Carbn02""").collect + checkAnswer(s"""select count(*) from T_Carbn01""", + Seq(Row(0)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_023") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with select on all column from a Hive table where already records already present in the Carbon table from Load + test("DataSight_Carbon_Insert_Func_027", Include) { + sql(s"""drop table IF EXISTS t_carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table T_Carbn01 options ('DELIMITER'=',', 'QUOTECHAR'='\','FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date')""").collect + sql(s"""create table T_Hive_1(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String) row format delimited fields terminated by ',' collection items terminated by '\n'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table T_Hive_1""").collect + sql(s"""insert into T_Carbn01 select * from T_Hive_1""").collect + checkAnswer(s"""select count(*) from T_Carbn01""", + Seq(Row(20)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_027") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with select on all column from the same carbon table + test("DataSight_Carbon_Insert_Func_028", Include) { + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table T_Carbn01 options ('DELIMITER'=',', 'QUOTECHAR'='\', 'FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date')""").collect + sql(s"""insert into T_Carbn01 select * from T_Carbn01""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + Seq(Row("TRUE",1,450,304034400,200000343430000000L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("TRUE",1,450,304034400,200000343430000000L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("TRUE",2,423,3046340,200000000003454300L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("TRUE",2,423,3046340,200000000003454300L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("TRUE",3,453,3003445,200000000000003450L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("TRUE",3,453,3003445,200000000000003450L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("TRUE",4,4350,3044364,200000000000000000L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("TRUE",4,4350,3044364,200000000000000000L,121.5,4.99,2.44,"SE3423ee","asfdsffdfg"),Row("TRUE",114,4520,30000430,200000000004300000L,121.5,4.99,2.44,"RE3423ee","asfdsffdfg"),Row("TRUE",114,4520,30000430,200000000004300000L,121.5,4.99,2.44,"RE3423ee","asfdsffdfg"),Row("FALSE",123,454,30000040,200000000000000000L,121.5,4.99,2.44,"RE3423ee","asfrewerfg"),Row("FALSE",123,454,300 00040,200000000000000000L,121.5,4.99,2.44,"RE3423ee","asfrewerfg"),Row("TRUE",11,4530,3000040,200000000000000000L,121.5,4.99,2.44,"SE3423ee","asfdsffder"),Row("TRUE",11,4530,3000040,200000000000000000L,121.5,4.99,2.44,"SE3423ee","asfdsffder"),Row("TRUE",14,4590,3000400,200000000000000000L,121.5,4.99,2.44,"ASD423ee","asfertfdfg"),Row("TRUE",14,4590,3000400,200000000000000000L,121.5,4.99,2.44,"ASD423ee","asfertfdfg"),Row("FALSE",41,4250,0,200000000000000000L,121.5,4.99,2.44,"SAD423ee","asrtsffdfg"),Row("FALSE",41,4250,0,200000000000000000L,121.5,4.99,2.44,"SAD423ee","asrtsffdfg"),Row("TRUE",13,4510,30400,200000000000000000L,121.5,4.99,2.44,"DE3423ee","asfrtffdfg"),Row("TRUE",13,4510,30400,200000000000000000L,121.5,4.99,2.44,"DE3423ee","asfrtffdfg")), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_028") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with select on all column from a hive table limiting the records selected + test("DataSight_Carbon_Insert_Func_038", Include) { + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select * from T_Hive1 limit 10""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from (select * from T_Hive1 limit 10) order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_038") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with select statement having subquery and join + test("DataSight_Carbon_Insert_Func_039", Include) { + sql(s"""drop table IF EXISTS t_hive5""").collect + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Hive5(Item_code STRING, Profit DECIMAL(3,2))row format delimited fields terminated by ',' collection items terminated by '$DOLLAR'""").collect + sql(s"""load data INPATH '$resourcesPath/Data/InsertData/T_Hive5.csv' overwrite into table T_Hive5""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select * from T_Hive1 x where exists (select * from T_Hive5 y where x.Item_code= y.Item_code) """).collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from (select * from T_Hive1 x where exists (select * from T_Hive5 y where x.Item_code= y.Item_code)) t1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_039") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with select statement having filter + test("DataSight_Carbon_Insert_Func_044", Include) { + sql(s"""drop table if exists t_hive4""").collect + sql(s"""drop table IF EXISTS T_Carbn01""").collect + sql(s"""create table T_Hive4(Item_code STRING, Item_name VARCHAR(50))row format delimited fields terminated by ','""").collect + sql(s"""load data INPATH '$resourcesPath/Data/InsertData/T_Hive4.csv' overwrite into table T_Hive4""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select * from T_Hive1 a where a.Item_code in (select b.item_code from T_Hive4 b)""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from (select * from T_Hive1 a where a.Item_code in (select b.item_code from T_Hive4 b)) t1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_044") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check for insert into carbon table with select on all columns from Hive table where Carbon table is created with block size of 1 mb + test("DataSight_Carbon_Insert_Func_045", Include) { + sql(s"""drop table IF EXISTS T_Carbn011""").collect + sql(s"""create table T_Carbn011(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('table_blocksize'='1')""").collect + sql(s"""insert into T_Carbn011 select * from T_Hive1""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn011 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Hive1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_045") + sql(s"""drop table IF EXISTS T_Carbn011""").collect + } + + + //Check for insert into carbon table with select on all columns from Hive table where Carbon table is created with block size of 100 mb + test("DataSight_Carbon_Insert_Func_046", Include) { + sql(s"""drop table IF EXISTS t_carbn011""").collect + sql(s"""create table T_Carbn011(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('table_blocksize'='100')""").collect + sql(s"""insert into T_Carbn011 select * from T_Hive1""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn011 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Hive1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_046") + sql(s"""drop table IF EXISTS T_Carbn011""").collect + } + + + //Check for insert into carbon table with select on all columns from Hive table where Carbon table is created with block size of 500 mb + test("DataSight_Carbon_Insert_Func_047", Include) { + sql(s"""drop table IF EXISTS t_carbn011""").collect + sql(s"""create table T_Carbn011(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('table_blocksize'='1024')""").collect + sql(s"""insert into T_Carbn011 select * from T_Hive1""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn011 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Hive1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_047") + sql(s"""drop table IF EXISTS T_Carbn011""").collect + } + + + //Check for insert into carbon table with select on all columns from Hive table where Carbon table is created with block size of 2gb mb + test("DataSight_Carbon_Insert_Func_048", Include) { + sql(s"""drop table IF EXISTS t_carbn011""").collect + sql(s"""create table T_Carbn011(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('table_blocksize'='2048')""").collect + sql(s"""insert into T_Carbn011 select * from T_Hive1""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn011 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Hive1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_048") + sql(s"""drop table IF EXISTS T_Carbn011""").collect + } + + + //Check for insert into carbon table with select on Hive and applying cast on the selected columns to suite the target table data type before inserting + test("DataSight_Carbon_Insert_Func_050", Include) { + sql(s"""drop table IF EXISTS t_carbn04""").collect + sql(s"""create table T_Carbn04(Item_code STRING, Item_name STRING)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn04 select Item_code, cast(Profit as STRING) from T_Hive5""").collect + checkAnswer(s"""select * from T_Carbn04""", + Seq(Row("BE3423ee","4.99"),Row("BE3423ee","4.99"),Row("BE3423ee","4.99"),Row("BE3423ee","4.99"),Row("RE3423ee","4.99"),Row("RE3423ee","4.99"),Row("SE3423ee","4.99"),Row("SE3423ee","4.99"),Row("SE3423ee","4.99"),Row("SE3423ee","4.99"),Row("ASD423ee","4.99"),Row("DE3423ee","4.99"),Row("DE3423ee","4.99"),Row("FE3423ee","4.99"),Row("FE3423ee","4.99"),Row("FE3423ee","4.99"),Row("RE3423ee","4.99"),Row("RE3423ee","4.99"),Row("SAD423ee","4.99"),Row("SE3423ee","4.99")), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_050") + sql(s"""drop table IF EXISTS T_Carbn04""").collect + } + + + //Check for insert into carbon table with select on Hive table and inserted carbon table created with one dimension excluded from dictionary. + test("DataSight_Carbon_Insert_Func_060", Include) { + sql(s"""drop table IF EXISTS t_carbn020""").collect + sql(s"""create table T_Carbn020(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('DICTIONARY_EXCLUDE'='Item_code')""").collect + sql(s"""Insert into T_Carbn020 select * from T_Hive1""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn020 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Hive1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_060") + sql(s"""drop table IF EXISTS T_Carbn020""").collect + } + + + //Check for insert into carbon table with select on a Carbon table and inserted carbon table created with one dimension excluded from dictionary. + test("DataSight_Carbon_Insert_Func_061", Include) { + sql(s"""drop table IF EXISTS t_carbn020""").collect + dropTable("T_Carbn01") + sql(s"""create table T_Carbn020(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('DICTIONARY_EXCLUDE'='Item_code')""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table T_Carbn01 options ('DELIMITER'=',', 'QUOTECHAR'='\', 'FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date')""").collect + sql(s"""Insert into T_Carbn020 select * from T_Carbn01""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn020 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_061") + sql(s"""drop table IF EXISTS T_Carbn020""").collect + } + + + //Check that Segment deletion for the inserted data in to Carbon table clears all the data + test("DataSight_Carbon_Insert_Func_074", Include) { + sql(s"""drop table IF EXISTS t_carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""Insert into T_Carbn01 select * from T_Hive1""").collect + sql(s"""delete from table T_Carbn01 where segment.id in (0)""").collect + sql(s"""select count(*) from T_Carbn01""").collect + checkAnswer(s"""select count(*) from T_Carbn01""", + Seq(Row(0)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_074") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check that when load and insert is made, deletion of segments associated with load should not delete inserted records + test("DataSight_Carbon_Insert_Func_075", Include) { + sql(s"""drop table IF EXISTS t_carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table T_Carbn01 options ('DELIMITER'=',', 'QUOTECHAR'='\', 'FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date')""").collect + sql(s"""Insert into T_Carbn01 select * from T_Hive1""").collect + sql(s"""delete from table T_Carbn01 where segment.id in (0)""").collect + sql(s"""select count(*) from T_Carbn01""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""", + s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Hive1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_075") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check that when load and insert is made, deletion of segments associated with insert operation should not delete loaded records + test("DataSight_Carbon_Insert_Func_076", Include) { + sql(s"""drop table IF EXISTS t_carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table T_Carbn01 options ('DELIMITER'=',', 'QUOTECHAR'='\', 'FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date')""").collect + sql(s"""drop table if exists T_Hive1""").collect + sql(s"""create table T_Hive1(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String) row format delimited fields terminated by ',' collection items terminated by '\n'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table T_Hive1""").collect + sql(s"""Insert into T_Carbn01 select * from T_Hive1""").collect + sql(s"""delete from table T_Carbn01 where segment.id in (0)""").collect + sql(s"""select count(*) from T_Carbn01""").collect + checkAnswer(s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Carbn01 order by update_time""",s"""select active_status,item_type_cd,qty_day_avg,qty_total,sell_price,sell_pricep,discount_price, profit,item_code,item_name from T_Hive1 order by update_time""", "DataLoadingTestCase_DataSight_Carbon_Insert_Func_076") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check insert into Carbon table with select from Hive , repeat this query multiple times in the same terminal + test("DataSight_Carbon_Insert_Func_082", Include) { + sql(s"""drop table IF EXISTS t_carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""Insert into T_Carbn01 select * from T_Hive1""").collect + sql(s"""Insert into T_Carbn01 select * from T_Hive1""").collect + checkAnswer(s"""select count(*) from T_Carbn01""", + Seq(Row(20)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_082") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check insert into Carbon table with select from Hive , and Load table done sequentially + test("DataSight_Carbon_Insert_Func_083", Include) { + sql(s"""drop table IF EXISTS t_carbn01""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""insert into T_Carbn01 select * from T_hive1""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table T_Carbn01 options ('DELIMITER'=',', 'QUOTECHAR'='\', 'FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date')""").collect + checkAnswer(s"""select count(*) from T_Carbn01""", + Seq(Row(20)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_083") + sql(s"""drop table IF EXISTS T_Carbn01""").collect + } + + + //Check insert into Carbon table with select done on a Hive partitioned table + test("DataSight_Carbon_Insert_Func_109", Include) { + sql(s"""drop table IF EXISTS t_hive14""").collect + sql(s"""create table T_Hive14(Item_code STRING, Profit DECIMAL(3,2)) partitioned by (Qty_total INT, Item_type_cd TINYINT) row format delimited fields terminated by ',' collection items terminated by '$DOLLAR'""").collect + sql(s"""drop table IF EXISTS T_Carbn014""").collect + sql(s"""create table T_Carbn014(Item_code STRING, Profit DECIMAL(3,2), Qty_total INT, Item_type_cd INT) STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""load data INPATH '$resourcesPath/Data/InsertData/T_Hive14.csv' overwrite into table T_Hive14 partition(Qty_total=100, Item_type_cd=2)""").collect + sql(s"""insert into T_carbn014 select * from T_Hive14 where Qty_total =100""").collect + checkAnswer(s"""select item_code, profit from T_Carbn014 order by item_code, profit""", + Seq(Row("BE3423ee",4.99),Row("BE3423ee",4.99),Row("SE3423ee",4.99),Row("SE3423ee",4.99),Row("SE3423ee",4.99),Row("SE3423ee",4.99)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_109") + sql(s"""drop table IF EXISTS T_Carbn014""").collect + } + + + //Check for select column from 2 tables joined using alias names for columns of both tables. + test("DataSight_Carbon_Insert_Func_110", Include) { + sql(s"""create table employees(name string, empid string, mgrid string, mobileno bigint) stored by 'carbondata'""").collect + sql(s"""create table managers(name string, empid string, mgrid string, mobileno bigint) stored by 'carbondata'""").collect + sql(s"""insert into managers select 'harry','h2399','v788232',99823230205""").collect + sql(s"""insert into employees select 'tom','t23717','h2399',99780207526""").collect + checkAnswer(s"""select e.empid from employees e join managers m on e.mgrid=m.empid""", + Seq(Row("t23717")), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_110") + sql(s"""drop table employees""").collect + sql(s"""drop table managers""").collect + } + + + //Show loads--->Action=Fail--->Logger=True + test("DataSight_Carbon_BadRecord_Dataload_024", Include) { + dropTable("uniqdata") + sql(s"""CREATE TABLE uniqdata (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format'""").collect + intercept[Exception] { + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/2000_UniqData.csv' into table uniqdata OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_ACTION'='FAIL','FILEHEADER'='CUST_ID,CUST_NAME,ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN1,Double_COLUMN2,INTEGER_COLUMN1')""").collect + } + checkAnswer(s"""select count(*) from uniqdata""", + Seq(Row(0)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_024") + sql(s"""drop table uniqdata""").collect + } + + + //Show loads--->Action=Fail--->Logger=False + test("DataSight_Carbon_BadRecord_Dataload_025", Include) { + dropTable("uniqdata") + sql(s"""CREATE TABLE uniqdata (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format'""").collect + intercept[Exception] { + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/2000_UniqData.csv' into table uniqdata OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_ACTION'='FAIL','FILEHEADER'='CUST_ID,CUST_NAME,ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN1,Double_COLUMN2,INTEGER_COLUMN1')""").collect + checkAnswer( + s"""select count(*) from uniqdata""", + Seq(Row(0)), "DataLoadingTestCase_DataSight_Carbon_BadRecord_Dataload_025") + } + sql(s"""drop table uniqdata""").collect + } + + + //when insert into null data,query table output NullPointerException + test("HQ_DEFECT_2016111509706", Include) { + sql(s"""drop table IF EXISTS t_carbn01""").collect + sql(s"""drop table IF EXISTS t_carbn02""").collect + sql(s"""create table T_Carbn01(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""create table T_Carbn02(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1_Bad.csv' INTO table T_Carbn01 options ('BAD_RECORDS_ACTION'='FORCE','DELIMITER'=',', 'QUOTECHAR'='\', 'FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date')""").collect + sql(s"""insert into t_carbn02 select Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date from t_carbn01""").collect + checkAnswer(s"""select count(*) from t_carbn02""", + Seq(Row(10)), "DataLoadingTestCase_HQ_DEFECT_2016111509706") + sql(s"""drop table t_carbn01""").collect + sql(s"""drop table t_carbn02""").collect + } + + + //Check insert into T_Carbn01 with select from T_Carbn02 from diff database + test("DataSight_Carbon_Insert_Func_112", Include) { + sql(s"""drop database if exists Insert1 cascade""").collect + sql(s"""create database Insert1""").collect + sql(s"""create table Insert1.Carbon_Insert_Func_1 (imei string,AMSize string,channelsId string,ActiveCountry string, Activecity string,gamePointId double,deviceInformationId double,productionDate Timestamp,deliveryDate timestamp,deliverycharge double) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('table_blocksize'='1')""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/vardhandaterestruct.csv' INTO TABLE Insert1.Carbon_Insert_Func_1 OPTIONS('DELIMITER'=',', 'QUOTECHAR'= '"', 'FILEHEADER'= 'imei,deviceInformationId,AMSize,channelsId,ActiveCountry,Activecity,gamePointId,productionDate,deliveryDate,deliverycharge')""").collect + sql(s"""drop database if exists Insert2""").collect + sql(s"""create database Insert2""").collect + sql(s"""create table Insert2.Carbon_Insert_Func_2 (imei string,AMSize string,channelsId string,ActiveCountry string, Activecity string,gamePointId double,deviceInformationId double,productionDate Timestamp,deliveryDate timestamp,deliverycharge double) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('table_blocksize'='1')""").collect + sql(s"""insert into Insert2.Carbon_Insert_Func_2 select * from Insert1.Carbon_Insert_Func_1""").collect + checkAnswer(s"""select count(*) from Insert2.Carbon_Insert_Func_2""", + Seq(Row(99)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_112") + sql(s"""drop database Insert1 cascade""").collect + sql(s"""drop database Insert2 cascade""").collect + } + + + //Check for Data insert into select for table with blocksize configured. + test("TC-PTS-TOR-AR-DataSight_Carbon_TableBlockSize-05-09-01", Include) { + sql(s"""CREATE TABLE BlockSize_Dataload_1 (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('table_blocksize'='2')""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/join1.csv' into table BlockSize_Dataload_1 OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='CUST_ID,CUST_NAME,ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,Double_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN2,INTEGER_COLUMN1')""").collect + sql(s"""CREATE TABLE BlockSize_Dataload_2 (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('table_blocksize'='2')""").collect + sql(s"""insert into BlockSize_Dataload_2 select * from BlockSize_Dataload_1""").collect + checkAnswer(s"""select count(*) from BlockSize_Dataload_2""", + Seq(Row(16)), "DataLoadingTestCase_TC-PTS-TOR-AR-DataSight_Carbon_TableBlockSize-05-09-01") + sql(s"""drop table BlockSize_Dataload_1""").collect + sql(s"""drop table BlockSize_Dataload_2""").collect + } + + + //Check for insert into carbon table with select from Hive table where only Measures columns are present. + test("DataSight_Carbon_Insert_Func_066", Include) { + sql(s"""create table Measures_Dataload_H (Item_code STRING, Qty int)row format delimited fields terminated by ',' LINES TERMINATED BY '\n'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/vardhandaterestruct.csv' INTO TABLE Measures_Dataload_H""").collect + sql(s"""create table Measures_Dataload_C (Item_code STRING, Qty int)stored by 'org.apache.carbondata.format'""").collect + sql(s"""insert into Measures_Dataload_C select * from Measures_Dataload_H""").collect + checkAnswer(s"""select count(*) from Measures_Dataload_C""", + Seq(Row(99)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_066") + sql(s"""drop table Measures_Dataload_H""").collect + sql(s"""drop table Measures_Dataload_C""").collect + } + + + //Check insert into carbon table with select when mulitple tables are joined through union. + ignore("DataSight_Carbon_Insert_Func_097", Include) { + sql(s"""CREATE TABLE Table_Union_1 (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), Double_COLUMN1 double,DECIMAL_COLUMN2 decimal(36,10), Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('table_blocksize'='1')""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/join1.csv' into table Table_Union_1 OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='CUST_ID,CUST_NAME, ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,Double_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN2,INTEGER_COLUMN1')""").collect + sql(s"""CREATE TABLE Table_Union_2 (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), Double_COLUMN1 double,DECIMAL_COLUMN2 decimal(36,10), Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('table_blocksize'='1')""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/join1.csv' into table Table_Union_2 OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='CUST_ID,CUST_NAME, ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,Double_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN2,INTEGER_COLUMN1')""").collect + sql(s"""CREATE TABLE Table_Union_3 (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), Double_COLUMN1 double,DECIMAL_COLUMN2 decimal(36,10), Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('table_blocksize'='1')""").collect + sql(s"""insert into Table_Union_3 select * from Table_Union_1 union select * from Table_Union_2""").collect + checkAnswer(s"""select count(*) from Table_Union_3""", + Seq(Row(16)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_097") + sql(s"""drop table Table_Union_1""").collect + sql(s"""drop table Table_Union_2""").collect + sql(s"""drop table Table_Union_3""").collect + } + + + //Check for insert into carbon table with select statement having logical operators + test("DataSight_Carbon_Insert_Func_043", Include) { + sql(s"""create table Logical_Dataload_H (Item_code STRING, Qty int)row format delimited fields terminated by ',' LINES TERMINATED BY '\n'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/Measures.csv' INTO TABLE Logical_Dataload_H""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/Measures.csv' INTO TABLE Logical_Dataload_H""").collect + sql(s"""create table Logical_Dataload_C (Item_code STRING, Qty int)stored by 'org.apache.carbondata.format'""").collect + sql(s"""insert into Logical_Dataload_C select * from Logical_Dataload_H where Item_Code != 'D' and Qty < 40""").collect + checkAnswer(s"""select count(*) from Logical_Dataload_C""", + Seq(Row(6)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_043") + sql(s"""drop table Logical_Dataload_H""").collect + sql(s"""drop table Logical_Dataload_C""").collect + } + + + //Check that select query fetches the correct data after doing insert and load . + test("DataSight_Carbon_Insert_Func_073", Include) { + sql(s"""create table Dataload_H (Item_code STRING, Qty int)row format delimited fields terminated by ',' LINES TERMINATED BY '\n'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/Measures.csv' INTO TABLE Dataload_H""").collect + sql(s"""create table Dataload_C (Item_code STRING, Qty int)stored by 'org.apache.carbondata.format'""").collect + + + sql(s"""insert into Dataload_C select * from Dataload_H""").collect + checkAnswer(s"""select count(*) from Dataload_C""", + Seq(Row(6)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_073") + sql(s"""drop table Dataload_H""").collect + sql(s"""drop table Dataload_C""").collect + } + + + //Check insert into T_Carbn01 with select from T_Hive1 from diff database + test("DataSight_Carbon_Insert_Func_111", Include) { + sql(s"""create database insert1""").collect + sql(s"""create table insert1.DiffDB_Dataload_H(Item_code STRING, Qty int)row format delimited fields terminated by ',' LINES TERMINATED BY '\n'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/Measures.csv' INTO TABLE insert1.DiffDB_Dataload_H""").collect + sql(s"""create database insert2""").collect + sql(s"""create table insert2.DiffDB_Dataload_C(Item_code STRING, Qty int)stored by 'org.apache.carbondata.format'""").collect + sql(s"""insert into insert2.DiffDB_Dataload_C select * from insert1.DiffDB_Dataload_H""").collect + checkAnswer(s"""select count(*) from insert2.DiffDB_Dataload_C""", + Seq(Row(6)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_111") + sql(s"""drop database insert1 cascade""").collect + sql(s"""drop database insert2 cascade""").collect + } + + + //Check for insert into carbon table with select from Hive table where only Dimension columns are present. + ignore("DataSight_Carbon_Insert_Func_065", Include) { + sql(s"""create table Dimension_Dataload_H (Item_code STRING)row format delimited fields terminated by ',' LINES TERMINATED BY '\n'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/Measures.csv' INTO TABLE Dimension_Dataload_H""").collect + sql(s"""create table Dimension_Dataload_C (Item_code STRING)stored by 'org.apache.carbondata.format'""").collect + sql(s"""insert into Dimension_Dataload_C select * from Dimension_Dataload_H""").collect + checkAnswer(s"""select count(*) from Dimension_Dataload_C""", + Seq(Row(6)), "DataLoadingTestCase_DataSight_Carbon_Insert_Func_065") + sql(s"""drop table Dimension_Dataload_H""").collect + sql(s"""drop table Dimension_Dataload_C""").collect + } + + + //Check data load after retension. + ignore("PTS-TOR_AR-DataSight_Carbon-LCM_002_001-001-TC-006_827", Include) { + sql(s"""create table DL_RETENCTION (Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table DL_RETENCTION options ('DELIMITER'=',', 'QUOTECHAR'='\', 'FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date')""").collect + val dateFormat = new SimpleDateFormat(CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT) + val date = dateFormat.format(new Date(System.currentTimeMillis())) + println(date) + sql(s"""delete from table DL_RETENCTION where segment.STARTTIME BEFORE '${date}'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table DL_RETENCTION options ('DELIMITER'=',', 'QUOTECHAR'='\', 'FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date')""").collect + checkAnswer(s"""select count(*) from DL_RETENCTION""", + Seq(Row(10)), "DataLoadingTestCase_PTS-TOR_AR-DataSight_Carbon-LCM_002_001-001-TC-006_827") + sql(s"""drop table DL_RETENCTION""").collect + } + + + //Check for the incremental load data DML without "DELIMITER" specified loading the data successfully. + test("PTS-TOR_AR-DataSight_Carbon-Maintenance_Incremental_Data_Load_001_001-001-TC-09_840", Include) { + sql(s"""create table DL_WithOutDELIMITER(Active_status String,Item_type_cd INT,Qty_day_avg INT,Qty_total INT,Sell_price BIGINT,Sell_pricep DOUBLE,Discount_price DOUBLE,Profit DECIMAL(3,2),Item_code String,Item_name String,Outlet_name String,Update_time TIMESTAMP,Create_date String)STORED BY 'org.apache.carbondata.format'""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table DL_WithOutDELIMITER options ('QUOTECHAR'='\', 'FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date')""").collect + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/InsertData/T_Hive1.csv' INTO table DL_WithOutDELIMITER options ('QUOTECHAR'='\', 'FILEHEADER'='Active_status,Item_type_cd,Qty_day_avg,Qty_total,Sell_price,Sell_pricep,Discount_price,Profit,Item_code,Item_name,Outlet_name,Update_time,Create_date')""").collect + checkAnswer(s"""select count(*) from DL_WithOutDELIMITER""", + Seq(Row(20)), "DataLoadingTestCase_PTS-TOR_AR-DataSight_Carbon-Maintenance_Incremental_Data_Load_001_001-001-TC-09_840") + sql(s"""drop table DL_WithOutDELIMITER""").collect + } + + + //Check for correct result set displayed for query execution after historical data loading. + test("PTS-TOR_AR-DataSight_Carbon-Maintenance_History_Data_Load_001_001-002-TC-01_749", Include) { + sql(s"""CREATE TABLE DL_HistoricalData( CUST_ID String,CUST_COUNTRY String,CUST_STATE String,CUST_CITY String,CUST_JOB_TITLE String,CUST_BUY_POTENTIAL String,PROD_UNQ_MDL_ID String,PROD_BRAND_NAME String,PRODUCT_NAME String,PRODUCT_MODEL String,PROD_MODEL_ID String,PROD_COLOR String,ITM_ID String,ITM_NAME String,PRMTION_ID String,PRMTION_NAME String,SHP_MODE_ID String,SHP_MODE String,DELIVERY_COUNTRY String,DELIVERY_STATE String,DELIVERY_CITY String,DELIVERY_DISTRICT String,TRACKING_NO Str
<TRUNCATED>
