Search code examples
cassandradatastaxcqlcqlsh

Cassandra CLI command with cqlsh -e how to declare

In my Cassandra table, it had been created with all columns in Upper case. When we tried to select the columns in cqlsh terminal, we were able to select those columns, but when we tried to pull same query based on the cqlsh -e facing some issue with escaping character.

cqlsh:key1> select "PLAN_ID" ,"A_ADVERTISER_ID" ,"ADVERTISER_NAME"  from key1.plan_advertiser where "PLAN_ID" = '382633' and "A_ADVERTISER_ID" = 15019;

 PLAN_ID | A_ADVERTISER_ID | ADVERTISER_NAME
---------+-----------------+----------------------
  382633 |           15019 | Hanesbrands, Updated

NMH206576286LM:sparklatest0802 KarthikeyanDurairaj$ cqlsh -e 'select "PLAN_ID" ,
  "A_ADVERTISER_ID" ,"ADVERTISER_NAME"  from key1.plan_advertiser 
  where "PLAN_ID" = '382633' and "A_ADVERTISER_ID" = 15019'
<stdin>:1:InvalidRequest: Error from server: code=2200 [Invalid query] 
  message="Invalid INTEGER constant (382633) for "PLAN_ID" of type text"

NMH206576286LM:sparklatest0802 KarthikeyanDurairaj$
Solution

  • cqlsh can be a little tricky in this regard. While it doesn't allow you to escape single quotes, it does allow you to escape double quotes. This works for me:

    $ bin/cqlsh -u cassdba -p flynnLives -e "SELECT * FROM stackoverflow.plan_advertiser
   where \"PLAN_ID\" = '382633' and \"A_ADVERTISER_ID\" = 15019"

 PLAN_ID | A_ADVERTISER_ID | ADVERTISER_NAME
---------+-----------------+----------------------
  382633 |           15019 | Hanesbrands, Updated

(1 rows)

    In this way, we switch from single quotes to double quotes for the CQL statement, use single quotes for column values, and then escape out the double quotes around the column names.