1

I insert data to Cassandra table using datastax library and spring framework

I use separator character "\t" to concat two strings this.pid + "\t" + this.cid;

If I select inserted data, I can see that data is saved 

select * from table1;

 pid | cid       | key  | value | update_time
-----+-----------+------+-------+--------------------------
  1 | data1 | key1 | 01\tdata1 | 2019-xx-xx 
  2 | data2 | key2 | 02\tdata2 | 2019-xx-xx

But I cannot select that data with select statement

select * from table1 where pid=1 AND cid='data1' AND key='key1' AND value='1\tdata1';

should I escape '\t' character?

below is table scheme

CREATE TABLE table1 (
    pid int,
    cid text,
    key text,
    value text,
    update_time timestamp,
    PRIMARY KEY (pid, cid, key, value)
)

there is difference between inserted data by cql and java

I use Mac OS, when I select on Mac console, I can see that "\t" character color is different

inserted data using java

enter image description here

inserted data using cql

enter image description here

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
장동현
  • 13
  • 3

2 Answers2

0

You already answered yourself in one of the comments, but for posterity I want to explain what's going on:

First, You are using the "cqlsh" tool to do the SELECT request. This tool tries to be friendly by converting non-printable characters into the traditional Unix representation (see, e.g., "cat -v") so for example the tab character is converted, on printing to a "\t". But the actual character stored in the database is a tab (a character with ASCII value 9), not "\t".

The second thing is that the CQL itself does not support these "escaped" characters. Things like \t or \011 do not have any special meaning in CQL, and "\t" is simply the two characters backslash and t! You need to use the actual tab character in the query string. In Java code (or other modern languages), it's easy, you can use \t in a string constant and Java (not Cassandra) will convert it into an actual tab inside that query string. But if you're using cqlsh for the query, nobody will do this conversion for you; You'll need to actually type that tab. Because cqlsh has special handling of tabs, you need to use control-V and tab, to tell cqlsh to really put an actual tab. I see you already discovered this solution yourself.

Nadav Har'El
  • 11,785
  • 1
  • 24
  • 45
-1

You are using pid as string but that is an integer - try below query and it will work -

select * from table1 where pid=1 AND cid='data1' AND key='key1' AND value='01\tdata1';

enter image description here

M P
  • 280
  • 1
  • 6
  • sorry I made you confused, i edited select statement but still print nothing – 장동현 Feb 27 '19 at 07:39
  • can you try like, select * from table1 where pid=1 AND cid='data1' AND key='key1' AND value=$$01\tdata1$$; – M P Feb 27 '19 at 08:16
  • 1
    I found how to select that record, use 「Control + V, followed by Tab 」instead of \t anyway thanks for your help have a good day(https://stackoverflow.com/questions/6392249/how-to-enter-a-tab-char-on-command-line) – 장동현 Feb 27 '19 at 08:24
  • @Mpadikka your answer is not relevant to his question. In *your* example, which worked, you used cqlsh for both insertion and selection. The two characters backslash-t were literally inserted into the data, and then searched, and everything just worked. But in the OP's question, he inserted an actual tab into the data because his programming language converted "\t" into a real tab, but then, in cqlsh, he couldn't search for this using "\t" because cqlsh doesn't convert \t into a tab. I explain this in more detail in my answer below. – Nadav Har'El Feb 27 '19 at 14:09
  • 1
    the query posted initially was wrong pid = '01' and I was showing that with the above cqlsh example and I was trying to show having \t inside a string doesnt need special escaping. – M P Feb 28 '19 at 04:52