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') );