Load data command Quote character unexpected behavior.

[Harmeet]

Hey Team,

I am using load data command with specific Quote character. But after loading the data, the behavior of quote character is not working. Below is my example:

create table one (name string, description string, salary double, age int, dob timestamp) stored by 'carbondata';

csf File >>

name, description, salary, age, dob
tammy, $my name$, 900000, 22, 19/10/2019

0: jdbc:hive2://127.0.0.1:10000> load data local inpath 'hdfs://localhost:54310/home/harmeet/dollarquote.csv' into table one OPTIONS('QUOTECHAR'="$");

Results >>
0: jdbc:hive2://127.0.0.1:10000> select * from one;
+-------------------------------------------+--------------+-------+-----------+-------+--+
|                   name                    | description  |  dob  |  salary   |  age  |
+-------------------------------------------+--------------+-------+-----------+-------+--+
| tammy                                     |  $my name$   | NULL  | 900000.0  | 22    |
+-------------------------------------------+--------------+-------+-----------+-------+--+

I am assuming, in description column only "my name" data is loaded and dollars was exclude, but this is not working. The same behavior, if we are using ' (Single Quote) with data.

[Lion.X]

hi, Harmeet
By your descriptions above, I can not reproduce the problem which you
described. It returned the right result in my env.
Pls use the lasted version and check again, and give more details.
Lionx

2016-10-21 15:07 GMT+08:00 Harmeet <[hidden email]>:

> Hey Team,
>
> I am using load data command with specific Quote character. But after
> loading the data, the behavior of quote character is not working. Below is
> my example:
>
> *create table one (name string, description string, salary double, age int,
> dob timestamp) stored by 'carbondata';*
>
> csf File >>
>
> name, description, salary, age, dob
> tammy, $my name$, 900000, 22, 19/10/2019
>
> 0: jdbc:hive2://127.0.0.1:10000> load data local inpath
> 'hdfs://localhost:54310/home/harmeet/dollarquote.csv' into table one
> OPTIONS('QUOTECHAR'="$");
>
> Results >>
> 0: jdbc:hive2://127.0.0.1:10000> select * from one;
> +-------------------------------------------+--------------+
> -------+-----------+-------+--+
> |                   name                    | description  |  dob  |
> salary
> |  age  |
> +-------------------------------------------+--------------+
> -------+-----------+-------+--+
> | tammy                                     |  $my name$   | NULL  |
> 900000.0  | 22    |
> +-------------------------------------------+--------------+
> -------+-----------+-------+--+
>
> I am assuming, in description column only "my name" data is loaded and
> dollars was exclude, but this is not working. The same behavior, if we are
> using ' (Single Quote) with data.
>
>
>
> --
> View this message in context: http://apache-carbondata-
> mailing-list-archive.1130556.n5.nabble.com/Load-data-
> command-Quote-character-unexpected-behavior-tp2145.html
> Sent from the Apache CarbonData Mailing List archive mailing list archive
> at Nabble.com.
>

[Harmeet]

Hey Lion,

My code is up to date and i am still getting same error. I am verifying this on multiple machines.

Actual Output >>>

 -------+-----------+-------+--+
| tammy                                     |  $my name$   | NULL  | 900000.0  | 22    |
-------------------------------------------+--------------+
 

Expected Output >>>

 -------+-----------+-------+--+
| tammy                                     |  my name   | NULL  | 900000.0  | 22    |
-------------------------------------------+--------------+
 
Please correct me, If my expected output is wrong.

[Lion.X]

OK, get it.
I will handle it.

[Harmeet]

Okay Lion, I will Raise this issue on Jira.

[杰]

hi, harmeet
   
  i checked your csv file, i think that's because there is space between comma,
   which cause that $ not the start of filed, so the actual value is parsed as space+$my name$+space
   so this is not a problem. remove the space, it will be fine.


regards
Jay
------------------ Original ------------------
From:  "Harmeet";<[hidden email]>;
Date:  Fri, Oct 21, 2016 03:07 PM
To:  "dev"<[hidden email]>;

Subject:  Load data command Quote character unexpected behavior.



Hey Team,

I am using load data command with specific Quote character. But after
loading the data, the behavior of quote character is not working. Below is
my example:

*create table one (name string, description string, salary double, age int,
dob timestamp) stored by 'carbondata';*

csf File >>

name, description, salary, age, dob
tammy, $my name$, 900000, 22, 19/10/2019

0: jdbc:hive2://127.0.0.1:10000> load data local inpath
'hdfs://localhost:54310/home/harmeet/dollarquote.csv' into table one
OPTIONS('QUOTECHAR'="$");

Results >>
0: jdbc:hive2://127.0.0.1:10000> select * from one;
+-------------------------------------------+--------------+-------+-----------+-------+--+
|                   name                    | description  |  dob  |  salary  
|  age  |
+-------------------------------------------+--------------+-------+-----------+-------+--+
| tammy                                     |  $my name$   | NULL  |
900000.0  | 22    |
+-------------------------------------------+--------------+-------+-----------+-------+--+

I am assuming, in description column only "my name" data is loaded and
dollars was exclude, but this is not working. The same behavior, if we are
using ' (Single Quote) with data.



--
View this message in context: http://apache-carbondata-mailing-list-archive.1130556.n5.nabble.com/Load-data-command-Quote-character-unexpected-behavior-tp2145.html
Sent from the Apache CarbonData Mailing List archive mailing list archive at Nabble.com.

[Harmeet Singh]

Hello Jay,

This is working. I just remove the spaces.

Before storing the data, we must need to trim the outer spaces from values. I think this is a valid approach.