Generic JDBC - Select
In this article
Overview
You can use this Snap to fetch data from the connected database by providing a table name and configuring the connection. This Snap also supports DML operation (SELECT) when using the AWS Athena database. This Snap produces the records from the database on its output, view which can then be processed by a downstream Snap.
Queries produced by the Snap have an equivalent format:
SELECT * FROM [table] WHERE [where clause] ORDER BY [ordering] LIMIT [limit] OFFSET [offset]
The WHERE clause can only use variables, not constants or Pipeline parameters.
A good example for a where clause is: SALARY = $SALARY (here, we use the SALARY variable of the input document).
Snap Type
Generic JDBC - Select Snap is a Read-type Snap that executes SQL SELECT statement.
Prerequisites
None.
Support for Ultra Pipelines
Works in Ultra Pipelines. However, we recommend that you not use this Snap in an Ultra Pipeline.
Known Issues
The metadata output in the second output preview is not displayed in a table format when your target database is AWS Athena.
The suggestions list is not populated for the Table name field when your target database is AWS Athena.
When the Generic JDBC—Select Snap connects to the Sybase database to retrieve
BigTime
-type data, the Snap displays both date and time for the data type.
Limitations
None.
Snap Views
Type | Format | Number of Views | Examples of Upstream and Downstream Snaps | Description |
---|---|---|---|---|
Input | Document |
|
| Document that provides values for one or more properties of the Snap or simply for pass through purposes. This Snap has at most one document input view. If the input view is defined, then the where clause can substitute incoming values for a given expression, such as a table name or as a variable as part of the WHERE clause. |
Output | Document |
|
| Document for each record retrieved. Special types such as TIMESTAMP, TIMESTAMPTZ and TIMESTAMPLTZ are converted into SnapLogic internal date type representations which then can be consumed by downstream Snaps just like any other data type. This Snap has one document output view by default A second view can be added to dump out the metadata for the table as a document. The metadata document can then be fed into the second input view of a database Insert or Bulk Load Snap so that the table is created in the database with a similar schema as the source table. |
Error | Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab:
Learn more about Error handling in Pipelines. |
Snap Settings
Asterisk (*): Indicates a mandatory field.
Suggestion icon (