Write

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

Open

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.

Validated Cassandra account, verified network connectivity to Cassandra server and port, and the Cassandra server running.

• Works in Ultra Tasks.

• The Cassandra-Execute Snap using Apache Cassandra V3 does not support $$ as an escape character for enclosing string input, as $ is a reserved character for variable substitution in SnapLogic.

• The Cassandra Snap Pack does not support the following data types introduced in Apache Cassandra V3.x, as the underlying SnapLogic JDBC driver is designed to work with Apache Cassandra V2.1:

  • DateRange

  • Duration

  • Geo-spatial data types such as Point, Polygon and LineString

This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Configuring Cassandra Accounts for information on setting up this type of account.

Required. The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.

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:

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:

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 the type of query for your SQL statement (Read or Write).

When Auto is selected, the Snap tries to determine the query type automatically.
If the execution result of the query is not as expected, you can change the query type to Read or Write.

Default Value: Auto
Example: Read

If checked, the input document will be passed through to the output view under the key 'original'. This property applies only to the Execute Snaps with SELECT statement.

Default value: True

If selected, no document will be written to the output view when a SELECT operation does not produce any result. If this property is not selected and the Pass through property is selected, the input document will be passed through to the output view.
Default value: Not selected

Specify the maximum number of retry attempts the Snap must make in case of network failure. 

When you set the Number of retries to more than 0, the Snap generates duplicate records when the connection is not established. To prevent duplicate records, we recommend that you follow one of the following:

• Set the Number of retries to 0 (default value) to prevent duplicate records from being passed downstream while executing a pipeline.

• Use a Primary key to prevent records from being inserted into the database.

• Use an Upsert instead of an Insert statement.

Number of seconds between retries.