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

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input

Document

  • Min: 0

  • Max: 1

  • Mapper Snap

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

  • Min: 1

  • Max: 2

  • Join Snap

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:

  • Stop Pipeline Execution: Stops the current pipeline execution if the Snap encounters an error.

  • Discard Error Data and Continue: Ignores the error, discards that record, and continues with the remaining records.

  • Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Snap Settings

  • Asterisk (*): Indicates a mandatory field.

  • Suggestion icon (