This Snap allows you to execute arbitrary Cassandra query language (CQL) supported by JDBC.

Valid JSON paths that are defined in the where clause for queries/statements will be substituted with values from an incoming document. Documents will be written to the error view if the document is missing a value to be substituted into the query/statement.

If a select query is executed, the query's results are merged into the incoming document and any existing keys will have their values overwritten. On the other hand, the original document is written if there are no results from the query.

• Expected upstream Snaps: Snaps that will either input data into Cassandra Execute, merge information with Cassandra, or required to precede execution before Cassandra execution.
• Expected downstream Snaps: Snaps that will use information gained from Cassandra or that is dependent on Cassandra having executed prior.

Snaps in this Snap Pack display an exception (raised by the JDBC driver) when you query a map column that has a timestamp as the key.

Example

1. Create a table containing a map whose key is a timestamp:
   CREATE COLUMNFAMILY t (userid text PRIMARY KEY, todo map<timestamp, text>);

2. Insert values into the newly-created table.
   INSERT INTO t (userid, todo) VALUES ('a', {'2013-09-22T12:01:00.000+0000': 'text'});

3. Once the insert operation succeeds, query the map column:
SELECT userid, todo FROM t;
   
   The Snap displays the following exception:

   com.datastax.driver.core.exceptions.CodecNotFoundException:
Codec not found for requested operation: [timestamp <-> java.sql.Timestamp]

Required. Specifies the SQL statement to execute on the server.

Cassandra Snaps do not support batch operations, which is why this field does not support SQL bind variables in it. There are two possible scenarios that you encounter when working with SQL statements in SnapLogic. You must understand the following scenarios to successfully execute your SQL statements:

Scenario 1: Executing SQL statements without expressions
If the expression toggle of the SQL statement field is not selected: 

• The SQL statement must not be within quotes. 
• The $<variable_name> parts of the SQL statement are expressions. In the below example, $id and $book.

Examples:

• email = 'you@example.com' or email = $email 

• emp=$emp

Additionally, the JSON path is allowed only in the WHERE clause. If the SQL statement starts with SELECT (case-insensitive), the Snap regards it as a select-type query and executes once per input document. If not, it regards it as write-type query and executes in batch mode.

Scenario 2: Executing SQL queries with expressions 
If the expression toggle of the SQL statement field is selected: 
The SQL statement must be within quotes. 

• The + $<variable_name> + parts of the SQL statement are expressions, and must not be within quotes.

Examples:

• "EMPNO=$EMPNO and ENAME=$EMPNAME"

• "emp='" + $emp + "'"

• "EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"

Schaum Series

'Schaum Series'

O'Reilly's Publication

Known issue: When the SQL statement property is an expression, the Pipeline parameters are shown in the suggest, but not the input schema. 

Default value: [None] 

This Snap does not allow you to inject SQL, such as select * from people where $columnName = "abc".

Only values can be substituted since it uses prepared statements for execution, which, for example, results in select * from people where address = ?.

Select one of the three modes in which the Snap executes. Available options are:

• Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.
• Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.

• Disabled: Disables the Snap and all Snaps that are downstream from it.