Details
-
Bug
-
Status: Closed
-
Minor
-
Resolution: Fixed
-
1.1.0
-
None
-
spark 2.1
Description
Unexpected characters displays in result-set while using join query.
Steps to reproduce:
1) Create tables:
a) In carbondata:
table 1 : create table Comp_TABLE_ONE_JOIN (customer_uid String,customer_id int, gender String, first_name String, middle_name String, last_name String,customer_address String, country String) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('DICTIONARY_EXCLUDE'='customer_uid')
table 2: create table Comp_TABLE_TWO_JOIN (customer_payment_id String,customer_id int,payment_amount Decimal(15,5), payment_mode String,payment_details String) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('DICTIONARY_EXCLUDE'='customer_payment_id')
b) In hive:
table 1: create table Comp_TABLE_ONE_JOIN_h (customer_uid String,customer_id int, gender String, first_name String, middle_name String, last_name String,customer_address String, country String)ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";
table 2:create table Comp_TABLE_TWO_JOIN_h (customer_payment_id String,customer_id int,payment_amount Decimal(15,5), payment_mode String,payment_details String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";
2) Load Data :
a) In Carbondata:
table 1 : LOAD DATA INPATH 'HDFS_URL/BabuStore/Data/customer_C1.csv' INTO table Comp_TABLE_ONE_JOIN options ('DELIMITER'=',','QUOTECHAR'='"','BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='customer_uid,customer_id,gender,first_name,middle_name,last_name,customer_address,country')
table 2:LOAD DATA INPATH 'HDFS_URL/BabuStore/Data/payment_C1.csv' INTO table Comp_TABLE_TWO_JOIN options ('DELIMITER'=',','QUOTECHAR'='"','BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='customer_payment_id ,customer_id,payment_amount ,payment_mode, payment_details')
b) In hive:
table 1: LOAD DATA LOCAL INPATH '/home/knoldus/Desktop/csv/TestData2/Data/customer_C1.csv' INTO table Comp_TABLE_ONE_JOIN_h;
table 2: LOAD DATA LOCAL INPATH '/home/knoldus/Desktop/csv/TestData2/Data/payment_C1.csv' INTO table Comp_TABLE_TWO_JOIN_h;
3)Execute query:
select * from Comp_TABLE_ONE_JOIN join Comp_TABLE_TWO_JOIN on Comp_TABLE_ONE_JOIN.customer_id=Comp_TABLE_TWO_JOIN.customer_id limit 2;
Actual Result:
a) In Carbondata:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
customer_uid | customer_id | gender | first_name | middle_name | last_name | customer_address | country | customer_payment_id | customer_id | payment_amount | payment_mode | payment_details |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
UID31a31 | 31 | female | fname31 | mname31 | lname31 | address 31 | country31 | Cust_payment_ID31a31 | 31 | 193288.72000 | p | |
UID31a31 | 31 | female | fname31 | mname31 | lname31 | address 31 | country31 | Cust_payment_ID31a31 | 31 | 193288.72000 | p |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows selected (0.499 seconds)
b) In Hive
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
customer_uid | customer_id | gender | first_name | middle_name | last_name | customer_address | country | customer_payment_id | customer_id | payment_amount | payment_mode | payment_details |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
UID1a1 | 1 | female | fname1 | mname1 | lname1 | address 1 | country1 | Cust_payment_ID1a1 | 1 | 6235.12000 | debit | details of the payment for : 1 |
UID1a1 | 1 | female | fname1 | mname1 | lname1 | address 1 | country1 | Cust_payment_ID1a1 | 1 | 6235.12000 | debit | details of the payment for : 1 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows selected (0.224 seconds)
Expected result: unwanted characters should not be displayed.