Apache Cassandra Connector

This adapter nables the connection to an Apache Cassandra database and enables communication via CQL statements.

Properties

Operation

Defines the Operation executed by the adapter.

Possible values:

Execute: Executes the statement specified in the input document.
Terminate: Closes and removes the connection pooling with all open connections

Parameters

`Adapter`	Main adapter class (do not change!) Possible values: de.softproject.integration.adapter.apache.cassandra.ApacheCassandraConnectorAdapter: Hauptklasse (default)
`host`	Host within the cluster Cluster cluster1 = Cluster.builder().addContactPoints("172.30.210.11","172.30.210.12").build(); Multiple IPs or host names can be added that correspond to different servers running as an Apache Cassandra executable within the module.
`port`	Native protocol port
`login`	Login if the cluster requires credentials. Possible values: Any string containing the login, e.g. `myLogin`.
`password`	Password if the cluster requires credentials. Possible values: Any string containing the password, e.g. `myPassword`

Status values

`1`	The operation was executed successfully.
`-1`	An error occurred during the operation's execution.

Input

The adapter expects a specific XML document as input:

Sample Input

XML

<RootElement>
    <Preparedstatement resulttag="result element name" timeZone="timezone">
        <Cql>
			select * from keyspace.table;
		</Cql>
		<Parameters>
			<parameter index="n" type="Cassandra_Type">Value</parameter>
		</Parameters>
    </Preparedstatement>
    <Preparedstatement resulttag="result 2 element name" timeZone="timezone 2">
       <Cql>
			INSERT INTO keyspace.table (id, lastname, firstname) VALUES (uuid(), myLastname,'myFirstName');
		</Cql>
    </Preparedstatement>
    ....
</RootElement>

The input XML document must comply with the following rules:

Element / Attribute	Description
`resulttag`	Required attribute of the element `Preparedstatement`.
`timeZone`	Optional attribute of the element `Preparedstatement`. Defines the time zone corresponding to `java.util.TimeZone` (e.g. `GMT+2` ). By default, the UTC time zone is used for dates.
`Cql`	Element within the element `Preparedstatement`. Contains the CQL statement. Can be used any number of times within `Preparedstatement`. If characters that are preset in XML are used within the CQL statement, then the SQL statement must be escaped with a CDATA block.
`Parameters`	Element within the element `Preparedstatement`. May only be used once within `Preparedstatement`. Contains any number of elements `parameter` with parameter values.
`parameter`	Element within the element `Parameters`. Can be used any number of times within `Parameters`. Attributes: `index`: Defines the position where the parameter value replaces the `?` within the CQL statement. `type`: Defines the parameter type. The following values are possible with Apache Cassandra: `TEXT`: UTF-8 encoded text `VARCHAR`: UTF-8 encoded text `ASCII`: US-ASCII encoded text `BLOB`: Hexadecimal encoded binary data without validation `BOOLEAN`: Boolean values `BIGINT`: 64 bit integer `COUNTER`: 64 bit counter `INT`: 32 bit integer `SMALLINT`: 2 byte integer `TINYINT`: 1 byte integer `DATE`: Datum in the format `yyyy-mm-dd` Apacha Cassandra encodes dates as a 32 bit integer representing the number of days since January 1, 1970. `DECIMAL`: Decimal `DOUBLE`: 64 bit IEEE floating point value `FLOAT`: 32 bit IEEE floating point value `INET`: IP address in IPv4 or IPv6 format. `TIME`: Time Apache Cassandra encodes times as a 64 bit integer representing the number of nanoseconds since midnight. `TIMESTAMP`: Date and time The format must be defined using the `format` attribute, for example: XML `<parameter index="n" type="TIMESTAMP" format="yyyy-MM-dd HH:mm:ss">2012-06-01 08:00:00</parameter>` `TIMEUUID`: Version 1 UUID `UUID`: UUID in the standard UUID format `VARINT`: Integer `MAP`: Array of literals in the JSON format `LIST`: Collection of one or more ordered elements, e.g. XML `<parameter index="n" type="LIST">["tcodd@relational.com","ted.codd@relational.com"]</parameter>` The use of double quotes is required. `SET`: Collection of one or more elements, e.g. XML `<parameter index="n" type="SET">["Element 1","Element 1","Element 3"]</parameter>` The use of double quotes is required. `TUPLE`: Group of comma-separated fields. Can contain different simple types: `keySpace`: Name of the key space of the table `table`: Name of the table `column`: Name of the column If the tuple contains date, time or timestamp fields, the following attributes are required: `formatWithoutTime`: Required if the tuple contains date fields. `format`: Required if the tuple contains timestamp fields. `formatTime`: Required if the tuple contains time fields. Example Tuple XML `<parameter index="n" type="TUPLE" keySpace="videodb" table="videos" column="tupleColl" formatWithoutTime="yyyy-MM-dd" format="yyyy-MM-dd HH:mm:ss" formatTime="HH:mm:ss:SSS">1, first tuple element, V1, 1.01, 14:25:16:555, 2012-06-01 08:00:00, 2018-04-03, 127.0.0.1, 5.678, 9e816244-f90a-4394-86a5-ddcf4fb9f7aa, true, 97, blobValue</parameter>` In the example above, the tuple field is defined as follows during table creation: `tupleColl tuple<int, text, varchar, float, time, timestamp, date, inet, decimal, uuid, boolean, ascii, blob>`

Output

The output is an XML document containing the errors and the result of the execution. Depending on the executed operation, the adapter outputs data in different XML structures:

Batch operations (INSERT, UPDATE, DROP, CREATE, ...)

Example Batch Operations

XML

<Result>
	<resulttag_name> Operation executed successfully</resulttag_name>
</Result>

SELECT

Example SELECT

XML

<Result>
	<resulttag_name>
		<Object>
			<Value name="field_name" type="String|Number|Boolean...">field_value</Value>
			...
			<Object name="Map_field_name">
				<Value name="key_name" type="String|Number|Boolean...">object_value</Value>
			</Object>
			<Array name="Colleciton_field_name">
				<Value type="String|Number|Boolean...">value_of_the_array_at_this_position</Value>
				...
			</Array>
			...
		</Object>
	</resulttag_name>
</Result>

If the field has the data type MAP, its value is represented by an object node. If the field has the data type LIST, SET or TUPLE, its value is represented by an array node. If the field is a simple type, its value is represented within the value node.

Example

CREATE

Example CREATE

XML

<RootElement>
	<Preparedstatement resulttag="createTableVideos">
		<Cql>
			<![CDATA[CREATE TABLE videodb.videos (
				videoid uuid,
				videoname varchar,
				username varchar,
				description varchar,
				location map<varchar,varchar>,
				tags set<varchar>,
				tupleColl tuple<int, text, varchar, float, time, timestamp, date, inet, decimal, uuid, boolean, ascii, blob>,
				upload_date timestamp,
				upload_date_noTime date,
				PRIMARY KEY (videoid));]]>
		</Cql>
	</Preparedstatement>
</RootElement>

DROP

Example DROP

XML

<RootElement>
	<Preparedstatement resulttag="drop_Users">
		<Cql>
			<![CDATA[DROP TABLE IF EXISTS videodb.users;]]>
		</Cql>
	</Preparedstatement>
</RootElement>

INSERT

Example INSERT

XML

<RootElement>
	<Preparedstatement resulttag="insertVideos" timeZone="UTC">
		<Cql>
			INSERT INTO videodb.videos (videoid, videoname, username, description, location, tags, tupleColl, upload_date, upload_date_noTime)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
		</Cql>
		<Parameters>
			<parameter index="0" type="UUID">99051fe9-6a9c-46c2-b949-38ef78858dd0</parameter>
			<parameter index="1" type="VARCHAR">My funny cat</parameter>
			<parameter index="2" type="VARCHAR">tcodd</parameter>
			<parameter index="3" type="VARCHAR">My cat likes to play the piano! So funny.</parameter>
			<parameter index="4" type="MAP">{"US":"/us/vid/99/99051fe9-6a9c-46c2-b949-38ef78858dd0"}</parameter>
			<parameter index="5" type="SET" >["cats","piano","lol"]</parameter>
			<parameter index="6" type="TUPLE" keySpace="videodb" table="videos" column="tupleColl" formatWithoutTime="yyyy-MM-dd" format="yyyy-MM-dd HH:mm:ss" formatTime="HH:mm:ss:SSS">1, fisrt tuple element, V1, 1.01, 14:25:16:555, 2012-06-01 08:00:00, 2018-04-03, 127.0.0.1, 5.678, 9e816244-f90a-4394-86a5-ddcf4fb9f7aa, true, 97, blobValue</parameter>
			<parameter index="7" type="TIMESTAMP" format="yyyy-MM-dd HH:mm:ss">2012-06-01 08:45:32</parameter>
			<parameter index="8" type="DATE" formatWithoutTime="yyyy-MM-dd">2012-06-01</parameter>
		</Parameters>
	</Preparedstatement>
</RootElement>

UPDATE

Example UPDATE

XML

<rootElement>
	<Preparedstatement resulttag="add_email_in_list">
		<Cql>
			<![CDATA[ UPDATE videodb.users set email = ? + email where username = ?]]>
		</Cql>
		<Parameters>
			<parameter type="LIST" index="0">["test1@mail.com","test0@mail.com"]</parameter>
			<parameter type="TEXT" index="1">pmcfadin</parameter>
		</Parameters>
	</Preparedstatement>
</rootElement>

SELECT

Example SELECT

XML

<mainNode>
	<Preparedstatement resulttag="select_NY_rides" timezone="UTC">
		<Cql>
			<![CDATA[SELECT * from NY.rides
				where vendor_id = ?
				and pickup_datetime = ?
				and rate_code = ?
				and total_amount = ?
				limit 10 ALLOW FILTERING;
			]]>
		</Cql>
		<Parameters>
			<parameter index="0" type="TEXT">2</parameter>
			<parameter index="1" type="TIMESTAMP" format="yyyy-MM-dd hh:mm:ss">2016-01-02 17:02:28</parameter>
			<parameter index="2" type="INT">1</parameter>
			<parameter index="3" type="DECIMAL">64.56</parameter>
		</Parameters>
	</Preparedstatement>
</mainNode>

DELETE

Example DELETE

XML

<mainNode>
	<Preparedstatement resulttag="delete_video_one_record" timezone="UTC">
		<Cql>
			DELETE FROM videodb.videos WHERE videoid = ?;
		</Cql>
		<Parameters>
			<parameter index="0" type="UUID">7dacb4da-9155-4613-b04e-8c5305a20342</parameter>
		</Parameters>
	</Preparedstatement>
</mainNode>