Integrating Cassandra

The Cassandra Lookup UDX lets you query tables in Apache Cassandra. To do so, you need to create a function and then call it. Often, this means runing a SELECT statement that returns rows and columns.. For more information on Apache Cassandra, see http://cassandra.apache.org/. As with all UDXs, to create a Cassandra Lookup UDX, you define it as a function. For more informaiton on defining functions, see the topic CREATE FUNCTION in the s-Server Streaming SQL Reference Guide.

Parameter Type Definition
InputRows CURSOR CQL statement that defines input rows from Cassandra.
tableName VARCHAR(512) Name of Cassandra table.
columnNames VARCHAR(128) Comma separated list of columns in Cassandra table.
hosts VARCHAR(128) Host on which Cassandra resides.
keySpace VARCHAR(128) Name of Cassandra keyspace in which table is located. A keyspace is similar to a database in RDBMS systems.

Here’s an example of a function definition for the Cassandra Lookup UDX:

CREATE OR REPLACE FUNCTION lookup(inputRows CURSOR, tableName VARCHAR(512),
    columnNames VARCHAR(128), hosts VARCHAR(128), keySpace VARCHAR(128))
    RETURNS TABLE(
        inputRows.*,
        id VARCHAR(128),
        lastname VARCHAR(128))
    LANGUAGE JAVA
    PARAMETER STYLE SYSTEM DEFINED JAVA
    NO SQL
    EXTERNAL NAME '"CassandraJar":com.sqlstream.plugin.cassandra.Functions.lookup';

After defining the function, you can call this UDX like this:

SELECT STREAM * FROM STREAM (cassandra.lookup(cursor(
    SELECT STREAM * from cassandra."join_test"),
    'drivers',
    'firstname',
    '127.0.0.1',
    'buses') );

You can also specify the query that you want to run on Cassandra calling another function already defined on s-Server:

CREATE OR REPLACE FUNCTION lookup_with_query(inputRows CURSOR, tableName VARCHAR(512),
    columnNames VARCHAR(128), hosts VARCHAR(128), keySpace VARCHAR(128))
    RETURNS TABLE(
        inputRows.*,
        id VARCHAR(128),
        lastname VARCHAR(128))
    LANGUAGE JAVA
    PARAMETER STYLE SYSTEM DEFINED JAVA
    NO SQL
    EXTERNAL NAME '"CassandraJar":com.sqlstream.plugin.cassandra.Functions.lookup_with_query';

You would call this function with code along the following lines:

SELECT STREAM * FROM STREAM (cassandra.lookup_with_query(cursor(
    SELECT STREAM * from cassandra."join_test"),
    'select * from drivers where firstname=? ALLOW FILTERING',
    'firstname',
    '127.0.0.1',
    'buses') );