Top Utility for Cassandra Clusters – cass_top

Featured

DataStax’s OpsCenter is pretty, but sometimes you don’t want to chop holes in your firewall for the server and agents.

So here’s cass_top. It works like top, but colorizes the output of nodetool status. It also lets you build nodetool commands using menus, run and log the output.

What’s especially nice is that it uses bash (no python required), and uses minimal screen real estate, so you can view all your clusters on one monitor using eterms.

$ cass_top 10.0.1.140


cass_top Screenshot
cass_top Help Screenshot

Please leave a comment with your suggestions.

github: Cassandra Top cass_top

Perl Clients for Cassandra

Featured

The Perl module Net::Async::CassandraCQL supports CQL3 and comes with a few sample programs and several tests.

To get the perldoc sample code to work:

  1. Perl 5.10 or higher is required. If you’re using Redhat 5 or CentOS 5, then use perlbrew or source to install a user-local version of perl (you should leave the vendor perl alone)
  2. install the Perl Cassandra client modules and dependencies with path-to-new-perl/bin/cpan Net::Async::CassandraCQL
  3. run the CREATE commands:
$ cqlsh
create keyspace if not exists "my_keyspace" with replication = { 
   'class' : 'SimpleStrategy', 'replication_factor' : 3
};
create table my_keyspace.numbers (v int primary key);
quit
# Program: cass_sample.pl
# Note: includes bug fixes for Net::Async::CassandraCQL 0.11 version

use strict;
use warnings;
use 5.10.0;

use IO::Async::Loop;
use Net::Async::CassandraCQL;
use Protocol::CassandraCQL qw( CONSISTENCY_QUORUM );

 my $loop = IO::Async::Loop->new;

 my $cass = Net::Async::CassandraCQL->new(
    host => "localhost",
    keyspace => "my_keyspace", # changed dash to underscore in keyspace
    default_consistency => CONSISTENCY_QUORUM,
 );
 $loop->add( $cass );

 $cass->connect->get;

 my @f;
 for my $number (1 .. 100) {  # added brackets around VALUES
    push @f, $cass->query( "INSERT INTO numbers (v) VALUES ($number)" );
 }

 Future->needs_all( @f )->get;

 my $get_stmt = $cass->prepare( "SELECT v FROM numbers" )->get;

 my ( undef, $result ) = $get_stmt->execute( [] )->get;

 for my $row ( $result->rows_hash ) {
    say "We have a number " . $row->{v};
 }

To cleanup the test keyspace:

$ cqlsh
drop keyspace my_keyspace;
quit

RT#97260: lost a sequence Future

Perl Support For Older Versions of Cassandra (Thrift-based)

SO: Using perl to connect to apache cassandra 2.0.1
Perlcassa – an Apache Cassandra Perl Client

Notes on “Getting Started with Time Series Data Modeling” in Cassandra

Featured

Patrick McFadin’s tutorial on Getting Started with Time Series Data Modeling aka “The Weather Model” is a very good introduction to a few important concepts in Cassandra:

  1. it’s the canonical time series use case in Cassandra. (Try to DESCRIBE the OpsCenter keyspace for how DataStax does time series rollups.)
  2. basic intro to CQL syntax.

Here’s some notes on getting more out of Patrick’s tutorial:

  • INSERT and UPDATE are the same in CQL and are known as UPSERT. That implies that if event_time was not part of the key, then the time and temp would just keep updating the same row each hour. It also implies that if we do a double-insert with the same timestamp, we will only see the last one to make it even if event_time is part of the key.
  • “PRIMARY KEY ((weatherstation_id,date),event_time)” and “PRIMARY KEY (weatherstation_id,event_time),) WITH CLUSTERING ORDER BY (event_time DESC)” both use event_time as a clustering (sorted) key, but only the latter syntax allows the descending ordering
  • try to do some SELECT statements without specifying the weather station id. What happens? Why?
  • the reason that Patrick is so excited with TTL is that with RDBMS, either you have to do DELETE FROM table where event_time < now() or setup partitions and DROP old ones and CREATE new ones. The first one causes massive IO and the second one increases administration overhead. Also, Cassandra's row TTL is much more granular than table-level
    partitions

  • there is no GROUP BY syntax, but denormalizing into PRIMARY KEY ((station_id), temperature) does something similar
  • although Patrick only inserted 4 test records, that’s still more than most database developers bother to test new features with, so do your own acceptance tests :)

Some more general CSQL tips are:

  • Datastax is promoting CSQL to users more so than Thrift
  • queries on the same node in the same partition are relatively fast, everything else being slower.
  • column names are stored as strings in each column, so keep them short if you want to save space. Remember that wasting space inflates memtables, SStables, snapshots, backups, AWS data transfer, etc.
  • SECONDARY INDEX is generally not recommended as it will usually do a scatter-gather across multiple nodes, thus either taking a long time or even timing out
  • prior to CQL3, you could use the cassandra-cli program to list the column families in more low-level detail to see how they were stored
  • to change the row key or clustering you need to drop the table (column family) and create it again

For those new to csqlsh, here’s the full list of commands to get the sample code working:

$ cqlsh

CREATE KEYSPACE "weather_model" WITH REPLICATION = {
   'class' : 'SimpleStrategy', 'replication_factor' : 3 };

SELECT * FROM system.schema_keyspaces;

USE weather_model;

CREATE TABLE temperature (
   weatherstation_id text,
   event_time timestamp,
   temperature text,
   PRIMARY KEY (weatherstation_id,event_time)
);

INSERT INTO temperature (weatherstation_id,event_time,temperature)
VALUES ('1234ABCD','2013-04-03 07:01:00','72F');

INSERT INTO temperature (weatherstation_id,event_time,temperature)
VALUES ('1234ABCD','2013-04-03 07:02:00','73F');

INSERT INTO temperature (weatherstation_id,event_time,temperature)
VALUES ('1234ABCD','2013-04-03 07:03:00','73F');

INSERT INTO temperature (weatherstation_id,event_time,temperature)
VALUES ('1234ABCD','2013-04-03 07:04:00','74F');
 
SELECT event_time,temperature FROM temperature
WHERE weatherstation_id='1234ABCD';

Display the read and write stats. (Use -h host if you get zero reads or writes.)

$ nodetool cfstats weather_model

Keyspace: weather_model
	Read Count: 2
	Read Latency: 0.1925 ms.
	Write Count: 4
	Write Latency: 0.141 ms.
[...]

Then clean up:

DROP TABLE temperature;

DROP KEYSPACE weather_model;

Links

Cassandra Query Language (CQL) v3
ebaytechblog.com: Cassandra Data Modeling Best Practices

MariaDB Patch: CREATE [[NO] FORCE] VIEW Options

Featured

MariaDB LogoBelow is my patch that implements the CREATE [[NO] FORCE] VIEW options against MySQL/MariaDB 10.1.0.

It adds two new options that look like this:

  1. CREATE NO FORCE VIEW v1 AS SELECT * FROM TABLE1; — base TABLE1 must exist, as before
  2. CREATE FORCE VIEW v1 AS SELECT * FROM TABLE1; — base TABLE1 doesn’t need to exist

Notes:

  • these options follow the Oracle Enterprise options fairly closely. NO FORCE works like the old default – a user needs database, table, column access and CREATE VIEW grant to create a view (more or less). FORCE allows a user to create a view with only database access and CREATE VIEW grant and no underlying base table. At SELECT time, full access control and grant checking is performed, and an error will occur if those constraints are not met.
  • views are more complicated than one would expect, and can be composed of base tables, derived tables, INFORMATION_SCHEMA (IS), and other views. The only table object not allowed is a temporary table
  • CREATE FORCE VIEW is an important option when managing large sets of views when you don’t want to track the creation sequence, or when creating views via program. An example is mysqldump, which can be simplified by replacing the current temporary tables ordering workarounds with FORCE VIEW.
  • It’s a fairly solid patch. I think the best thing is to commit it to alpha and let it bake for a while.
  • One permutation that will need special handling is this: CREATE FORCE VIEW view1 AS SELECT * FROM table1; Since * is not resolved to column names by FORCE, currently ” AS SELECT * AS ” is generated, causing an error. So just use explicit column names like CREATE FORCE VIEW view1 SELECT id, col1, col2 FROM table1; See this bug.
  • it passes t/view.test:
    # ./mysql-test-run.pl view
    Logging: ./mysql-test-run.pl  view
    vardir: /usr/local/mariadb-10.1.0/mysql-test/var
    MariaDB Version 10.1.0-MariaDB-debug
    
    TEST                                  RESULT   TIME (ms) or COMMENT
    -------------------------------------------------------------------
    main.view                            [ pass ]   1896
    -------------------------------------------------------------------
    The servers were restarted 0 times
    Spent 1.896 of 7 seconds executing testcases
    Completed: All 1 tests were successful.
    
  • I wrote tests/view.pl which does 8,000+ test permutations. It passes. :)

$ cat create_force_view.patch

--- ../mariadb-10.1.0/sql/sql_view.h 2014-06-27 04:50:36.000000000 -0700
+++ sql/sql_view.h 2014-09-02 02:35:42.000000000 -0700
@@ -29,10 +29,10 @@
/* Function declarations */

bool create_view_precheck(THD *thd, TABLE_LIST *tables, TABLE_LIST *view,
- enum_view_create_mode mode);
+ enum_view_create_mode mode, enum_view_create_force force);

bool mysql_create_view(THD *thd, TABLE_LIST *view,
- enum_view_create_mode mode);
+ enum_view_create_mode mode, enum_view_create_force force);

bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table,
uint flags);
--- ../mariadb-10.1.0/sql/sql_lex.h 2014-06-27 04:50:33.000000000 -0700
+++ sql/sql_lex.h 2014-09-02 01:21:10.000000000 -0700
@@ -170,6 +170,12 @@
VIEW_CREATE_OR_REPLACE // check only that there are not such table
};

+enum enum_view_create_force
+{
+ VIEW_CREATE_NO_FORCE, // default - check that there are not such VIEW/table
+ VIEW_CREATE_FORCE, // check that there are not such VIEW/table, then ignore table object dependencies
+};
+
enum enum_drop_mode
{
DROP_DEFAULT, // mode is not specified
@@ -2442,6 +2448,7 @@
};
enum enum_var_type option_type;
enum enum_view_create_mode create_view_mode;
+ enum enum_view_create_force create_view_force;
enum enum_drop_mode drop_mode;

uint profile_query_id;
--- ../mariadb-10.1.0/sql/sql_parse.cc 2014-06-27 04:50:34.000000000 -0700
+++ sql/sql_parse.cc 2014-09-02 02:34:31.000000000 -0700
@@ -4943,7 +4943,7 @@
Note: SQLCOM_CREATE_VIEW also handles 'ALTER VIEW' commands
as specified through the thd->lex->create_view_mode flag.
*/
- res= mysql_create_view(thd, first_table, thd->lex->create_view_mode);
+ res= mysql_create_view(thd, first_table, thd->lex->create_view_mode, thd->lex->create_view_force);
break;
}
case SQLCOM_DROP_VIEW:
--- ../mariadb-10.1.0/sql/sql_yacc.yy 2014-06-27 04:50:37.000000000 -0700
+++ sql/sql_yacc.yy 2014-09-05 17:19:29.000000000 -0700
@@ -1851,7 +1851,7 @@
statement sp_suid
sp_c_chistics sp_a_chistics sp_chistic sp_c_chistic xa
opt_field_or_var_spec fields_or_vars opt_load_data_set_spec
- view_algorithm view_or_trigger_or_sp_or_event
+ view_algorithm view_or_trigger_or_sp_or_event view_force_option
definer_tail no_definer_tail
view_suid view_tail view_list_opt view_list view_select
view_check_option trigger_tail sp_tail sf_tail udf_tail event_tail
@@ -2446,6 +2446,7 @@
VIEW_CREATE_OR_REPLACE);
Lex->create_view_algorithm= DTYPE_ALGORITHM_UNDEFINED;
Lex->create_view_suid= TRUE;
+ Lex->create_view_force= VIEW_CREATE_NO_FORCE; /* initialize just in case */
}
view_or_trigger_or_sp_or_event
{
@@ -15887,6 +15888,15 @@
| event_tail
;

+view_force_option:
+ /* empty */ /* 411 - is there a cleaner way of initializing here? */
+ { Lex->create_view_force = VIEW_CREATE_NO_FORCE; }
+ | NO_SYM FORCE_SYM
+ { Lex->create_view_force = VIEW_CREATE_NO_FORCE; }
+ | FORCE_SYM
+ { Lex->create_view_force = VIEW_CREATE_FORCE; }
+ ;
+
/**************************************************************************

DEFINER clause support.
@@ -15944,7 +15954,7 @@
;

view_tail:
- view_suid VIEW_SYM table_ident
+ view_suid view_force_option VIEW_SYM table_ident
{
LEX *lex= thd->lex;
lex->sql_command= SQLCOM_CREATE_VIEW;
--- ../mariadb-10.1.0/sql/sql_view.cc 2014-06-27 04:50:36.000000000 -0700
+++ sql/sql_view.cc 2014-09-05 19:33:58.000000000 -0700
@@ -248,7 +248,7 @@
*/

bool create_view_precheck(THD *thd, TABLE_LIST *tables, TABLE_LIST *view,
- enum_view_create_mode mode)
+ enum_view_create_mode mode, enum_view_create_force force)
{
LEX *lex= thd->lex;
/* first table in list is target VIEW name => cut off it */
@@ -259,7 +259,7 @@
DBUG_ENTER("create_view_precheck");

/*
- Privilege check for view creation:
+ Privilege check for view creation with default (NO FORCE):
- user has CREATE VIEW privilege on view table
- user has DROP privilege in case of ALTER VIEW or CREATE OR REPLACE
VIEW
@@ -272,6 +272,7 @@
checked that we have not more privileges on correspondent column of view
table (i.e. user will not get some privileges by view creation)
*/
+
if ((check_access(thd, CREATE_VIEW_ACL, view->db,
&view->grant.privilege,
&view->grant.m_internal,
@@ -285,6 +286,11 @@
check_grant(thd, DROP_ACL, view, FALSE, 1, FALSE))))
goto err;

+ if (force) {
+ res = false;
+ DBUG_RETURN(res || thd->is_error());
+ }
+
for (sl= select_lex; sl; sl= sl->next_select())
{
for (tbl= sl->get_table_list(); tbl; tbl= tbl->next_local)
@@ -369,7 +375,7 @@
#else

bool create_view_precheck(THD *thd, TABLE_LIST *tables, TABLE_LIST *view,
- enum_view_create_mode mode)
+ enum_view_create_mode mode, enum_view_create_force force)
{
return FALSE;
}
@@ -391,7 +397,7 @@
*/

bool mysql_create_view(THD *thd, TABLE_LIST *views,
- enum_view_create_mode mode)
+ enum_view_create_mode mode, enum_view_create_force force)
{
LEX *lex= thd->lex;
bool link_to_local;
@@ -425,14 +431,13 @@
goto err;
}

- if ((res= create_view_precheck(thd, tables, view, mode)))
+ if (res= create_view_precheck(thd, tables, view, mode, force))
goto err;

lex->link_first_table_back(view, link_to_local);
view->open_type= OT_BASE_ONLY;

- if (open_temporary_tables(thd, lex->query_tables) ||
- open_and_lock_tables(thd, lex->query_tables, TRUE, 0))
+ if (open_temporary_tables(thd, lex->query_tables) || (!force && open_and_lock_tables(thd, lex->query_tables, TRUE, 0)))
{
view= lex->unlink_first_table(&link_to_local);
res= TRUE;
@@ -513,6 +518,7 @@
}
}

+if (!force) {
/* prepare select to resolve all fields */
lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW;
if (unit->prepare(thd, 0, 0))
@@ -612,6 +618,7 @@
}
}
#endif
+}

res= mysql_register_view(thd, view, mode);

@@ -621,7 +628,7 @@
meta-data changes after ALTER VIEW.
*/

- if (!res)
+ // if (!res)
+ if (!res && !force) /* 411 - solves segfault problems with CREATE FORCE VIEW option sometimes */
tdc_remove_table(thd, TDC_RT_REMOVE_ALL, view->db, view->table_name, false);

if (mysql_bin_log.is_open())
@@ -908,6 +915,8 @@
fn_format(path_buff, file.str, dir.str, "", MY_UNPACK_FILENAME);
path.length= strlen(path_buff);

if (ha_table_exists(thd, view->db, view->table_name, NULL))
{
if (mode == VIEW_CREATE_NEW)
--- ../mariadb-10.1.0/mysql-test/t/view.test 2014-06-27 04:50:30.000000000 -0700
+++ mysql-test/t/view.test 2014-09-06 00:23:32.000000000 -0700
@@ -5263,4 +5263,17 @@
--echo # -----------------------------------------------------------------
--echo # -- End of 10.0 tests.
--echo # -----------------------------------------------------------------
+
+create no force view v1 as select 1;
+drop view if exists v1;
+
+create force view v1 as select 1;
+drop view if exists v1;
+
+create force view v1 as select * from missing_base_table;
+drop view if exists v1;
+
+--echo # -----------------------------------------------------------------
+--echo # -- End of 10.1 tests.
+--echo # -----------------------------------------------------------------
SET optimizer_switch=@save_optimizer_switch;