Manual - CQLSÍ

CQLSÍ - Cassandra Query Language Simple Interface (SÍ means Yes in Catalan).

Introduced in Catalonia Framework v.1.1.014 it can be used with all the Framework or with the minimal package for CQLSÍ

What is CQLSÍ

CQLSÍ is a simple interface, a wrapper, for working with CQL from PHP without Thrift.
After many problems with Thrift, fails trying different third party code solutions and compiling existing uncompilable PDO drivers, and Thrift being declared legacy by DataStax in favor of CQL for Cassandra 1.2 and 2.0, etc... I decided to create a painless simple way to interface with CQL while nothing better comes to the scene.
Basically CQLSÍ executes cqlsh through bash, captures the output and provides the data to the Catalonia Framework's Db abstraction layer, so switching from MySqli to Cassandra is just changing config file to use Db::TYPE_CONNECTION_CASSANDRA_CQLSI instead of Db::TYPE_CONNECTION_MYSQLI or Db::TYPE_CONNECTION_POSTGRE

Pros and Cons

The pros

• As it uses the CQLSH DataStax client, it is always compatible with the latest versions of CQL.
  If a new version is released, just update the cqlsh tool and you'll be able to use the new CQL commands and features
• Compatible with CQL 3 and collections, set, list, map
• No Thrift, boost, etc.. dependencies
• Very easy to use, simply send the cql query as you would do from command line
• Abstraction layer that gives you the results as an Array
• The Abstraction layer is the Db Class from Catalonia Framework, so moving your projects from MySql or Postgre to Cassandra is just switching the driver in config files
• Many CQL commands can be sent at once
• All the data is presented to you as String.
  You don't have to worry about the primitive types laying in Cassandra

The cons

• As all the wrappers, if the response format expected changes, it won't work
  This can happen if a new version of the cqlsh tool changes the output format.
  This problem can be solved by working with PreProduction and never upgrading Base Software in Production without testing first. Is just common sense
• Administration commands (no data commands) do not return a response.
  For example:
  

  DESCRIBE KEYSPACES;

  Use, instead:
  

  SELECT * FROM system.schema_keyspaces;

  
• Unexpected behavior that it has not been tested could happen
  Although I'm using CQLSÍ for my own projects and I've not discovered problems (I fixed things that were not Ok) new issues could appear (but this happens also with well known drivers)
• All the data is presented to you as String, that is wonderful, but causes confusion with:
  null, that is presented to you as a string with value 'null'
  Also strings starting with spaces, like ' Hello World!! ', will be wrapped as 'Hello World!! ' as spaces on the left are trimmed
• There is no pool of opened connections
  Every call to $o_db->queryWrite(); or $o_db->queryRead(); has to connect to the cluster, and after executing the command, the connection will be closed. So if you do 10 queries, you'll loss some time, is the cost of connecting to the Cluster everytime.
  Fortunatelly many INSERTS can be sent at once as a single request to CQLSH.
• Is only compatible with Unix. No windows support

Launching many queries at once

If you have to send several INSERT or UPDATE queries at once, you can group them in a single $s_cql string and you will save the time of connecting every time.
For example, this code:

$s_cql_long_query = '';
for ($i_userid=3000;$i_userid<4000;$i_userid++) {
    $s_cql_long_query .= "INSERT INTO users (user_id, fname, lname, longtext) VALUES ( ".$i_userid.", 'Carles', 'X', 'whatever');";
}
$o_db_cassandra->queryWrite($s_cql_long_query);

This code creates 1,000 inserts, in my modest and busy laptop takes 2.9599 seconds to process the inserts. This time includes all the time, so the cost of the execution of cqlsh, the time of establishing the connection, the time to process the cql and do the 1,000 inserts by Cassandra and the time to close the connection, parse the result and assembly the abstraction layer data Array.
A single insert takes 0.6536 seconds and 10 inserts send as a single $s_sql take 0.6992 seconds, so the most expensive is to launch the cqlsh command, and stablish the connection.

Simultaneous queries have to be of he same type: Write. It is not recommended although is possible to send many INSERT and a final SELECT at the end and process everything at once (execute with queryRead).

Installation

CQLSÍ does not require you to install anything, you just need to have cqlsh client installed in the system.

This is tipically done for Ubuntu/Debian by:

Edit /etc/apt/sources.list and add:

deb http://debian.datastax.com/community stable main

As descrived in http://www.datastax.com/docs/1.0/install/install_deb

Add the key:

curl -L http://debian.datastax.com/debian/repo_key | sudo apt-key add -

Then install the cqlsh tool and if you want Cassandra (although you don't need to have it running on the webserver)

sudo apt-get update
sudo apt-get install python-cql=1.0.10-1
sudo apt-get install dsc=1.0.10 cassandra=1.0.10

Look for upgrades, just in case a new version has been releases:

sudo apt-get upgrade

Ubuntu on Amazon EC2 will return: The following packages have been kept back, showing cassandra and not updating.
To force the newest version to be installed do:

sudo apt-get install cassandra

If Cassandra fails to restart after upgrading, probably has a problem with logs from old version.
If your installation is new you can simple ignore the old logs and everything will work:

root@ip-10-10-10-10:/var/lib/cassandra/commitlog# mkdir old_logs
root@ip-10-10-10-10:/var/lib/cassandra/commitlog# mv *.log old_logs/
root@ip-10-10-10-10:/var/lib/cassandra/commitlog# service cassandra start

Please keep in mind that cqlsh has to be the version 4.1.0 at least to work with CQLSÍ.

Probably your system does not allow the webserver's user (www-data) to create history in home folder. This is required for bash, so probably you will have to do:

sudo mkdir /var/www/.cassandra
sudo chmod 777 /var/www/.cassandra

Or similar, so bash can create cqlsh_history file.

Working with different clusters / keyspaces

You can define as many Db objects as you want, each one configured to use a different cluster or keyspace.
Take this sample init/boostrap.php custom file:

$st_other_config1['database'] = Array(  'read'  => Array(   'servers'   => Array(0 => Array('connection_type'   => Db::TYPE_CONNECTION_CASSANDRA_CQLSI,
                                                                                            'connection_method' => Db::CONNECTION_METHOD_TCPIP,
                                                                                            'server_hostname'   => '127.0.0.1',
                                                                                            'server_port'       => Db::PORT_DEFAULT_CASSANDRA,
                                                                                            'username'          => 'www_cassandra',
                                                                                            'password'          => 'yourpassword',
                                                                                            'database'          => 'mykeyspace',
                                                                                            'client_encoding'   => 'utf8'
                                                                                            )
                                                                                )
                                                        ),
                                        'write' => Array(   'servers'   => Array(0 => Array('connection_type'   => Db::TYPE_CONNECTION_CASSANDRA_CQLSI,
                                                                                            'connection_method' => Db::CONNECTION_METHOD_TCPIP,
                                                                                            'server_hostname'   => '127.0.0.1',
                                                                                            'server_port'       => Db::PORT_DEFAULT_CASSANDRA,
                                                                                            'username'          => 'www_cassandra',
                                                                                            'password'          => 'yourpassword',
                                                                                            'database'          => 'mykeyspace',
                                                                                            'client_encoding'   => 'utf8'
                                                                                            )
                                                                                )

                                                        )
                                    );

$o_db_cassandra = new Db($st_other_config1['database']);
$s_cql = "INSERT INTO users (user_id, fname, lname, longtext) VALUES ( 1714, 'Carles', 'Mateo', 'whatever...');";
$st_result = $o_db_cassandra->queryWrite( $s_cql);

You can change the keyspace used for the next queries:

$o_db->setDatabaseOrKeyspace('cataloniasample', Db::CONNECTION_READ);
$o_db->setDatabaseOrKeyspace('cataloniasample', Db::CONNECTION_WRITE);

It is not recommended, but is possible to use different keyspaces for Read and Write.

In order to insert apostrophe, that is saved using '' and avoid injection use Db::prepareInsert(), for example:

$i_user_id = prepareInsert($i_user_id, self::DATA_TYPE_INT, self::TYPE_CONNECTION_CASSANDRA_CQLSI);
$s_fname = prepareInsert($s_fname, self::DATA_TYPE_STRING, self::TYPE_CONNECTION_CASSANDRA_CQLSI);
$s_lname = prepareInsert($s_lname, self::DATA_TYPE_STRING, self::TYPE_CONNECTION_CASSANDRA_CQLSI);
$s_longtext = prepareInsert($s_longtext, self::DATA_TYPE_STRING, self::TYPE_CONNECTION_CASSANDRA_CQLSI);
$s_cql = "INSERT INTO users (user_id, fname, lname, longtext) VALUES ( $i_user_id, '$s_fname', '$s_lname', '$s_longtext');";
$st_result = $o_db_cassandra->queryWrite($s_cql);

Working with data

For queries that return data, like SELECT $o_db->queryRead($s_cql) must be used.
For queries that INSERT, UPDATE data, or ALTER the Schema, $o_db->queryWrite($s_cql) must be used.
For MySql and Postgre this has another meaning (going to primary or secondaries), but for CQLSÍ it is required by the wrapper, as the error handling is performed in a different way for insertion commands and data fetching commands.

The enter characters will be returned as \n, so you will have a string like:
"Several\nnew\nlines"
Null data types will be provided as "null" string.
Fully UTF-8 characters are supported, so accents, and other special chars codified by default with LANG=ca_ES.UTF-8.

Let's see a sample code to fetch data:

$st_results = $o_db_cassandra->queryRead('SELECT * FROM users LIMIT 10;');
$i_row_num = 0;
$s_first_data = '';
if ($st_results['result']['status'] == Db::QUERY_RESULT_STATUS_EXECUTED && $st_results['result']['error'] == 0) {
    echo '<table border="1">';
    foreach($st_results['data'] as $i_row=>$st_data) {
        $i_row_num++;
        echo '<tr>';
        foreach($st_data as $s_key => $s_value) {
            if ($i_row_num == 1) {
                // Write headers
                echo '<th>'.$s_key.'</th>';
                $s_first_data .= '<td>'.$s_value.'</td>';
            } else {
                echo '<td>'.$s_value.'</td>';
            }
        }
        echo '</tr>';
        if ($i_row_num == 1) {
            echo '<tr>'.$s_first_data.'</tr>';
        }
    }
    echo '</table>';
} else {
    echo 'Error: '.$st_results['result']['error_description'];
}

This code in init/bootstrap.php produces this output:


In case of error echo 'Error: '.$st_results['result']['error_description']; will output:

Enable debug

CQLSÍ generates temp files under /tmp.
Those are the .sh files executed and .cqlsi with the commands send to cqlsh.
If you need to debug and see what is the exact error you get, you can indicate to CQLSÍ to do not delete those files after use.

For example:

$s_cql = "CREATE TABLE IF NOT EXISTS test (userid int,
                                           firstname text,
                                           lastname text,
                                           tele set<text>,
                                           emails set<text>,
                                           skills list<text>,
                                           todos map<timestamp,text>,
                     PRIMARY KEY (userid) );";

$o_db_cassandra->setKeepCqlFiles(true);

$st_result = $o_db_cassandra->queryWrite($s_cql);

Common errors

• If you have installed cqlsh in other than the default path /usr/bin/cqlsh:
  
  Use $o_db->setCqlshPath($s_new_path); to setup it.


• If cqlsh can't connect to the db server:
  
  Check it your firewall, dns, etc...


• If you forget to end the cql with ;
  Some commands are accepted without ending ; but most of them aren't, so to be sure end always your CQL commands with ;


• To insert now() with apostrophes, like in 'now()'. now() corresponds to timeuuid and not to string type

Back to Main Manual page