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,
#Keep copy of all configuration file before editing.
<cassandra-bin-path>/cassandra
command.<cassandra-bin-path>/nodetools status
commandProblem #-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
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