SAP HANA - Select
Overview
This Snap allows you to fetch data from a database by providing a table name and configuring the connection. The Snap produces the records from the database on its output view, which can then be processed by a downstream Snap.
JSON paths can be used in a query and will have values from an incoming document substituted into the query. However, documents missing values for a given JSON path will be written to the Snap's error view. After a query is executed, the query's results are merged into the incoming document overwriting any existing keys' values. The original document is output if there are no results from the query.
Queries produced by the Snap have an equivalent format:
SELECT * FROM [table] WHERE [where clause] ORDER BY [ordering] LIMIT [limit] OFFSET [offset]If more powerful functionality is desired, then the SAP HANA Execute Snap should be used.
A good example for a where clause is: SALARY =$SALARY (here we use the SALARY variable of the input document).
A bad example for a where clause is: SALARY = _param (this will not work since we do not evaluate pipeline parameters during prepared statement execution, instead an upstream Mapper needs to be used that maps _param into $SALARY).
- This is a Read-type Snap.
Works in Ultra Tasks
Supported Accounts
Prerequisites
None.
Limitations and known issues
None.
Snap views
| Type | Description | Examples of upstream and downstream Snaps |
|---|---|---|
| Input | This Snap has exactly one document input view. | |
| Output | This Snap has exactly one document output view. | |
| Learn more about Error handling. | ||
Examples
Retrieve data from a table: Retrieve data from a table using dynamic variables.
Snap settings
| Field/Field set | Description |
|---|---|
| Label
|
Required. Specify a unique name for the Snap. Modify this
to be more appropriate, especially if more than one of the same Snaps is in the
pipeline.
Default value: SAP HANA - Select Example: Select employee data |
| Schema name
|
The database schema name. In case it is not
defined, then the suggestion for the table name will retrieve all tables names of
all schemas. The property is suggestible and will retrieve available database
schemas during suggest values. Default value: [None] Example: myschema |
| Table name
|
Required. The name of the
table to execute a select query on. Default value: [None] Example: people |
| Where clause
|
Where clause of select statement. This supports
document value substitution (such as $person.firstname will be substituted with the
value found in the incoming document at the path). Default value: [None] Example: email = '[email protected]' or email = $email |
| Order by | The column names against which the results are ordered. |
| Column Names
|
Enter in the columns to order by. The default
database sort order will be used. Default value: None Example:
|
| Limit offset
|
Starting row for the query.
Default value: [None] Example: 0 |
| Limit rows
|
Number of rows to return from the query.
Default value: [None] Example: 10 |
| Output fields
|
Enter or select output field names for SQL SELECT
statement. To select all fields, leave it at default. Default value: [None] Example: email, address, first, last, etc. |
| Fetch Output Fields In Schema
|
Select this checkbox to return only the selected output fields or columns in the output schema (second output view) using the Output Fields field. If the Output Fields field is empty all the columns are visible.
Default status: Deselected |
| Pass through
|
If checked, the input document will be passed through to the output view under the key 'original'.
Default status: Selected |
| Ignore empty result
|
If checked, no document will be written to the output view when the SELECT operation does not produce any result. If this property is unchecked and the Pass through property is checked, the input document will be passed through to the output view.
Default status: Deselected |
| Auto commit
|
Select one of the options for this property to override the state of the Auto commit property on the account. The Auto commit at the Snap-level has three values: True, False, and Use account setting. The expected functionality for these modes are:
Default value: False |
| Match data types
|
This property applies only when the Output fields property is provided with any field value(s). If this property is selected, the Snap tries to match the output data types same as when the Output fields property is empty (SELECT * FROM ...). The output preview would be in the same format as the one when SELECT * FROM is implied and all the contents of the table are displayed.
Default status: Deselected |
| Number of retries
|
Specifies the maximum number of attempts to be
made to receive a response. The request is terminated if the attempts do not result
in a response. Default value: 0 Example: 3 |
| Retry interval (seconds)
|
Specifies the time interval between two successive
retry requests. A retry happens only when the previous attempt resulted in an
exception. Default value: 1 Example: 10 |
| Staging mode
|
Required when the value in the Number of
retries field is greater than 0. Specify the location to store input documents
between retries:
Default value: In Memory Example: On disk |
| Snap execution
|
Choose one of the three modes in which the Snap
executes. Available options are:
Default value: Validate & Execute Example: Execute only |