Cassandra

eSources: cassandra.apache.org, dbeaver GUI, setup Auth

Cassandra Setup Single node.

download cassandra version 2.2.5

if not having python 2.7, do below on centos machine only:

yum install -y centos-release-SCL

yum install -y python27

scl enable python27 bash

java driver for connectivity at:http://mvnrepository.com/artifact/org.apache.cassandra/cassandra-all/2.2.4 copied jdk and set paths in /etc/bashrc

python 2.7 at:https://github.com/h2oai/h2o-2/wiki/Installing-python-2.7-on-centos-6.3.-Follow-this-sequence- started cassandra using cassandra -f

stopped by cntrl + c

started normally cassandra

connected cqlsh

Queries

cqlsh>describe keyspaces; #lists all available keyspaces

cqlsh>create keyspace myApp with replication={'class':'SimpleStrategy', 'replication_factor':1} ;

cqlsh>create keyspace myApp with replication={'class':'NetworkTopologyStrategy', 'dc1':2} ; #in case of multi node setup where datacenter "dc1" exists having 2 or more nodes and we want our data to be replicated on them. 

cqlsh>use myapp;

cqlsh:myapp>describe tables;

cqlsh:myapp>describe columnfamilies;

cqlsh:myapp>select * from student;

cqlsh:myapp>insert into student(id) values(1);

cqlsh:myapp>insert into student(id,name) values(2, 'rohit') ;

cqlsh:myapp>create index on student(name);

cqlsh:myapp>select * from student where name = 'rohit';

Table: #Create:

• create KEYSPACE rv with replication = {'class': 'SimpleStrategy', 'replication_factor':'1'} ;
• use rv;
• create table test_tbl ( id int, name text, Primary key(id));

Alter :

• alter TABLE test_tbl add address text;
• alter TABLE test_tbl add address3 boolean;

Insert:

• insert into test_tbl(id, address, address3 ) values (1,'telkj', true) ;

Update:

• UPDATE test_tbl SET address = 'New Value' WHERE id = 1;
• insert into test_tbl(id, address ) values (1,'another') ;

Select:

• select * from test_tbl ;

#DBeaver, GUI for accessing cassandra..

Installed plugin available in eclipse, downloaded java driver for cassandra from cdata. install driver. create connection metadata. use same to connect and play.

alternatives --install /usr/bin/dbeaver dbeaver /opt/dbeaver/dbeaver 1

#How to export table data into file..

COPY <SCHEMA.TABLE> (<FIELD_1>, <FIELD_N>) to '<OUTPUT_FILE_PATH>' with delimiter = '\001' ; #"^A" separated output file to avoid delimiters within data.

#How to import data from file into table..

COPY <SCHEMA.TABLE> (<FIELD_1>, <FIELD_N>) from '<OUTPUT_FILE_PATH>' with delimiter = '\001' ; #"^A" separated output file to avoid delimiters within data.

#Execute queries without getting into cqlsh. source

cqlsh <HOST> -u<USER_NAME> -e<"QUERY">

Views:

#Create:

create materialized view <KEYSPACE>.<VIEW_NAME> as select

* from <KEYSPACE>.<BASE_TABLE> where <ANY_BASE_COLUMN> < '<SOME_VALUE>' and  <PRIMARY_KEY_COLUMN> is not null aND <ANOTHER_PRIMARY_KEY_COLUMN> IS NOT null

primary key(<ANY_FIELD_IN_VIEW>, <BASE_TABLE_PRIMARY_KEY_1>, <BASE_TABLE_PRIMARY_KEY_2..>);

#Drop:

drop materialized view <KEYSPACE>.<VIEW_NAME>;

#com.datastax.driver.core.Session & com.datastax.driver.core.Cluster

Update.Where

QueryBuilder

QueryBuilder.eq, set, and,

Setup data center:

#Keep copy of all configuration file before editing.

1. Download & Extract cassandra on all available nodes in desired folder. let's assume we have 2 data centers, each have 2 racks of 3 nodes each. IP address of all nodes (ip1...ip12)
2. Update cassandra-rackdc.properties on each machine
   • check dc name and rack name, update if required. eg-node1-node3 should have dc1, rack1 & node4-node6 might have dc2, rack1 and so on..
3. Update cassandra-topology.properties on any node.
   • add all node IPs and corresponding dc & rack names. copy on rest of the nodes.
4. Update cassandra.yaml on each node:
   • listen-address to IP of node
   • seeds to "all node ips as CSV"
   • rpc_address to IP of node in case we need thrift server running
5. telnet 7000 port from/to each other nodes. if not connected update firewall entries.
   • sudo iptables -I INPUT -p tcp -s <IP_TO_ALLOW> --dport 7000 -j ACCEPT
6. start cassandra on all nodes using <cassandra-bin-path>/cassandra command.
7. check health using <cassandra-bin-path>/nodetools status command

Problem #-1: Error while starting cassandra : ERROR [main] 2018-05-14 14:35:26,523 CassandraDaemon.java:752 - Exception encountered during startup

java.lang.AbstractMethodError: org.apache.cassandra.utils.JMXServerUtils$Exporter.exportObject(Ljava/rmi/Remote;ILjava/rmi/server/RMIClientSocketFactory;Ljava/rmi/server/RMIServerSocketFactory;Lsun/misc/ObjectInputFilter;)Ljava/rmi/Remote;

Soln: upgrading java to 1.8.0_172(Java TM) from _131(Open JDK) worked.. source...

Problem #-2: Cannot connect to cassandra.

Observations: Log indicates, unable to connect to internal IPs of cassandra slave nodes. and connection timeout as well. ERROR: Pool was closed during initialization

Soln: Need to check with configuration of listen_address and rpc_address.. as process at port 9042 is started with internal ip address.

Keys- Primary(single primary), composite(multi primary), partition(distribution across nodes), cluster(sort within node)

for a table.

##Caused by: java.lang.ClassNotFoundException: org.springframework.jdbc.CannotGetJdbcConnectionException

##No qualifying bean of type 'org.springframework.data.cassandra.repository.query.CassandraEntityInformation

##org.springframework.data.cassandra.core.mapping.VerifierMappingExceptions: com.org_name.dal.entity.Product:

- At least one of the @PrimaryKeyColumn annotations must have a type of PARTITIONED

https://stackoverflow.com/questions/24949676/difference-between-partition-key-composite-key-and-clustering-key-in-cassandra

Problem#3: While running dbeaver(3.8/5.2) got error " java: /builddir/build/BUILD/oxygen-gtk2-1.3.4/src/animations/oxygencomboboxdata.cpp:87: void Oxygen::ComboBoxData::setButton(GtkWidget*): Assertion `!_button._widget' failed." and it closes immediately after start-up.

Soln: changed 'GTK+Appearance' to "Adwaita" from oxygen-gtk. sources: eclipse-bugs