Cassandra "no viable alternative at input"

Question

I am trying to insert a simple row into the table. Can someone point out what is happening here ?

CREATE TABLE recommendation_engine_poc.user_by_category (
        game_category text,
        customer_id text,
        amount double,
        game_date timestamp,
        PRIMARY KEY (game_category, customer_id)
    ) WITH CLUSTERING ORDER BY (customer_id ASC)
        AND bloom_filter_fp_chance = 0.01
        AND caching = '{"keys":"ALL", "rows_per_partition":"NONE"}'
        AND comment = ''
        AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy'}
        AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
        AND dclocal_read_repair_chance = 0.1
        AND default_time_to_live = 0
        AND gc_grace_seconds = 864000
        AND max_index_interval = 2048
        AND memtable_flush_period_in_ms = 0
        AND min_index_interval = 128
        AND read_repair_chance = 0.0
        AND speculative_retry = '99.0PERCENTILE';

    cqlsh:recommendation_engine_poc> insert into user_by_category  ('game_category','customer_id') VALUES ('Goku','12') ;
    SyntaxException: <ErrorMessage code=2000 [Syntax error in CQL query] message="line 1:31 no viable alternative at input 'game_category' (insert into user_by_category  (['game_categor]...)">

Inflect

there can be one mistake that every dev potentially ignores while re-checking, single or double quotes !!!!! – Procora

Answer 1

Wrong syntax. Here you are:

insert into user_by_category (game_category,customer_id) VALUES ('Goku','12');

or:

insert into user_by_category ("game_category","customer_id") VALUES ('Kakarot','12');

The second one is normally used for case-sensitive column names.

Madra

It worked can you provide where this is documented ? docs.datastax.com/en//cql/3.0/cql/cql_reference/… examples here are wrong – Inflect

In example which you have provided column names are listed without single/double quotation marks. Here: #20332962 I've seen the double quotation marks, however I can't find the documentation for this. – Madra

Answer 2

When using placeholders (either for preparing a statement, or just to use a dict for populating values), there is another problem that can occur: whatever you put into the dict might not evaluate into a simple type like int, str etc. For me this happened with the IntEnum, which appeared as <MyIntEnum.CONSTANT: 4> instead of 4, but it can happen with any complex object which will then appear like <object object at 0x7fbf08a0bdf0>.

So if you use the form:

session.execute(
        INSERT INTO table (id, name)
        VALUES (%(id)s, %(name)s),
        {"id": 123456, "name": "example"}
)

make sure that everything in your dict is actually a string, int, float etc.

That was not the problem in this question, but since it's another wrong encoding and this is the first Google hit for the question I hope this helps someone :)

Coeternity

Answer 3

There are two reasons for this issue,

1. double(") quotes instead of single quote(')
2. Enter keys after values in the IN command
3. Character encoding

so while using filters like IN command
Select column from database where column_name IN('xyz','yzx')
IN command in single line

It should be in single line instead of
Select column from database where column_name IN('xyz',
'yzx')
multi line IN command

Cybele