'CLUSTERING' columns

[ICHIBA Sara <ic...@gmail.com>]

Hey there,

I have imported the following schema to my cassandra db

Re: 'CLUSTERING' columns

[Vova Shelgunov <vv...@gmail.com>]

You need to add id column to your primary key, and also you should restrict
remaining part of the primary key in your queries.

Clustering key is responsible for data sorting within the partition.

2015-06-23 10:17 GMT+03:00 ICHIBA Sara <ic...@gmail.com>:

> Hey there,
>
> I have imported the following schema to my cassandra db
>
> =====================the schema
> CREATE KEYSPACE radius WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
> USE radius;
>
> CREATE TABLE nas (
>   id uuid PRIMARY KEY,
>   nasname text,
>   shortname text,
>   type text,
>   secret text,
>   server text
> );
>
> CREATE TABLE radpostauth (
>   username text,
>   authdate bigint,
>   reply text,
>   PRIMARY KEY (username, authdate)
> ) WITH CLUSTERING ORDER BY (authdate ASC);
>
> CREATE TABLE radusergroup (
>   username text,
>   priority int,
>   groupname text,
>   PRIMARY KEY (username, priority)
> ) WITH CLUSTERING ORDER BY (priority ASC);
>
> CREATE TABLE radreply (
>   id uuid,
>   username text,
>   attribute text,
>   op text,
>   value text,
>   PRIMARY KEY (username, attribute)
> );
>
> CREATE TABLE radgroupreply (
>   id uuid,
>   groupname text,
>   attribute text,
>   op text,
>   value text,
>   PRIMARY KEY (groupname, attribute)
> );
>
> CREATE TABLE radgroupcheck (
>   id uuid,
>   groupname text,
>   attribute text,
>   op text,
>   value text,
>   PRIMARY KEY (groupname, attribute)
> );
>
> CREATE TABLE radcheck (
>   id uuid,
>   username text,
>   attribute text,
>   op text,
>   value text,
>   PRIMARY KEY (username, attribute)
> );
>
> CREATE TABLE radacct (
>   id uuid,
>   sessionid text,
>   username text,
>   realm text,
>   nasipaddress text,
>   nasportid text,
>   nasporttype text,
>   starttime bigint,
>   updatetime bigint,
>   stoptime bigint,
>   sessiontime int,
>   authentic text,
>   connectinfo_start text,
>   connectinfo_stop text,
>   inputoctetslo bigint,
>   inputoctetshi bigint,
>   outputoctetslo bigint,
>   outputoctetshi bigint,
>   calledstationid text,
>   callingstationid text,
>   servicetype text,
>   terminatecause text,
>   framedprotocol text,
>   framedipaddress text,
>   PRIMARY KEY (username, nasipaddress, sessionid)
> );
>
> CREATE TABLE radnasflip (
>   nasipaddress text,
>   timestamp bigint,
>   PRIMARY KEY (timestamp, nasipaddress)
> );
>
> CREATE TABLE radsess (
>   id uuid,
>   sessionid text,
>   username text,
>   nasipaddress text,
>   nasportid text,
>   callingstationid text,
>   framedprotocol text,
>   framedipaddress text,
>   PRIMARY KEY (nasipaddress, username, sessionid)
> );
> CREATE INDEX ON radsess(username);
>
> ==============end of the schema
>
>  When A client ( freeradius server) try to execute the following  query
>  --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'aerohive1' ORDER BY id
> I get this error:  Order by is currently only supported on the clustered columns of the PRIMARY KEY, got id
>
> Can anyone please tell me how to fix this. And what is exactly the effect of clustered columns??
>
> In advance, thank you for your response,
>
> B.regards,
> Sara
>
>

Re: 'CLUSTERING' columns

[ICHIBA Sara <ic...@gmail.com>]

Hey there,

I have imported the following schema to my cassandra db

=====================the schema
CREATE KEYSPACE radius WITH REPLICATION = { 'class' :
'SimpleStrategy', 'replication_factor' : 1 };
USE radius;

CREATE TABLE nas (
  id uuid PRIMARY KEY,
  nasname text,
  shortname text,
  type text,
  secret text,
  server text
);

CREATE TABLE radpostauth (
  username text,
  authdate bigint,
  reply text,
  PRIMARY KEY (username, authdate)
) WITH CLUSTERING ORDER BY (authdate ASC);

CREATE TABLE radusergroup (
  username text,
  priority int,
  groupname text,
  PRIMARY KEY (username, priority)
) WITH CLUSTERING ORDER BY (priority ASC);

CREATE TABLE radreply (
  id uuid,
  username text,
  attribute text,
  op text,
  value text,
  PRIMARY KEY (username, attribute)
);

CREATE TABLE radgroupreply (
  id uuid,
  groupname text,
  attribute text,
  op text,
  value text,
  PRIMARY KEY (groupname, attribute)
);

CREATE TABLE radgroupcheck (
  id uuid,
  groupname text,
  attribute text,
  op text,
  value text,
  PRIMARY KEY (groupname, attribute)
);

CREATE TABLE radcheck (
  id uuid,
  username text,
  attribute text,
  op text,
  value text,
  PRIMARY KEY (username, attribute)
);

CREATE TABLE radacct (
  id uuid,
  sessionid text,
  username text,
  realm text,
  nasipaddress text,
  nasportid text,
  nasporttype text,
  starttime bigint,
  updatetime bigint,
  stoptime bigint,
  sessiontime int,
  authentic text,
  connectinfo_start text,
  connectinfo_stop text,
  inputoctetslo bigint,
  inputoctetshi bigint,
  outputoctetslo bigint,
  outputoctetshi bigint,
  calledstationid text,
  callingstationid text,
  servicetype text,
  terminatecause text,
  framedprotocol text,
  framedipaddress text,
  PRIMARY KEY (username, nasipaddress, sessionid)
);

CREATE TABLE radnasflip (
  nasipaddress text,
  timestamp bigint,
  PRIMARY KEY (timestamp, nasipaddress)
);

CREATE TABLE radsess (
  id uuid,
  sessionid text,
  username text,
  nasipaddress text,
  nasportid text,
  callingstationid text,
  framedprotocol text,
  framedipaddress text,
  PRIMARY KEY (nasipaddress, username, sessionid)
);
CREATE INDEX ON radsess(username);

==============end of the schema

 When A client ( freeradius server) try to execute the following  query
 --> SELECT id, username, attribute, value, op FROM radcheck WHERE
username = 'aerohive1' ORDER BY id
I get this error:  Order by is currently only supported on the
clustered columns of the PRIMARY KEY, got id

Can anyone please tell me how to fix this. And what is exactly the
effect of clustered columns??

In advance, thank you for your response,

B.regards,
Sara