JDBC Snap Pack
Overview
The JDBC Snap Pack enables seamless integration and data connectivity with diverse databases through JDBC (Java Database Connectivity) technology. This Snap Pack supports DCL commands (Grant and Revoke). However, to connect to popular databases such as Snowflake, MySQL, Oracle, or SQL Server, we recommend that you use the dedicated Data Snaps available in the SnapLogic application. This will address all your database integration needs and help optimize performance.
-
This Snap Pack supports integration with AWS Athena database; however, it supports only create, alter, insert, and select operations.
-
Athena JDBC Driver 1.x versions are deprecated, and the Generic JDBC Snaps are incompatible with the Athena JDBC 1.x drivers. Hence, we recommend you to use Athena JDBC Driver 2.x or higher version when connecting to the Athena database.
- Upgraded the JOOQ library of the JDBC Snap Pack from v3.9.1 to v3.17.x. Learn More
- Generic JDBC - Execute : Execute a SQL statement/query against a SQL database.
- Generic JDBC - Select : Retrieves data from a SQL database by executing a SQL SELECT statement/query.
- Generic JDBC - Update : Updates data in a SQL database by executing a SQL UPDATE statement/query.
- Generic JDBC - Insert : Inserts data into a SQL database by executing a SQL INSERT statement/query.
- Generic JDBC - Schema List : Obtains a list of schemas for the specified account from the database.
- Generic JDBC - Table List : Obtains a list of tables associated with a specified schema from the database.
Prerequisites
-
Appropriate permissions to perform CRUD operations on the database.
-
The Snaplex should have access to database IP and Port.
-
Timestamp format with respect to the database you are connecting must match.
-
A JDBC driver and .jar files must be installed and configured for the appropriate database to make a successful JDBC connection. The JDBC driver for each type of database you want to connect to may contain typically one .jar file or in some cases multiple .jar files.
-
Contact your database vendor to get the appropriate driver. Additionally, there are third-party companies that sell commercial JDBC drivers.
Breaking change
-
With the
439patches29008Snap Pack version, existing pipelines using the Generic JDBC - Execute and Generic JDBC - Select Snaps may experience issues when connecting to the Oracle endpoint, specifically when handling the encoded ROWID (manually processing the Base64-encoded data). To avoid potential pipeline failures, it is recommended to downgrade the Generic JDBC Snap Pack to the previous version until the issue is resolved. -
Your existing pipelines that use JDBC Snaps to integrate with Oracle or Redshift databases using the bundled Oracle or Redshift JDBC drivers might experience issues with the
438patches28052.Workaround
You must manually upload the Oracle or Redshift JDBC driver in the JDBC Database account to run your pipelines successfully.
Download the drivers:
Supported versions
This Snap Pack is tested against the following databases:
| Database | Version | Driver |
|---|---|---|
| Cassandra | 3.0 | cassandra-jdbc-wrapper-3.7.2.jar |
| DB2 | v11.5.4.0 | db2jcc.jar |
| Informix | Informix Dynamic Server 14.10.FC5DE | shared/ifxjdbc.jar |
| MySQL |
5.6.34 8.0.17 |
|
| Maria DB | 10.5.5 | mariadb-java-client-2.6.0.jar |
| Mongo DB |
2.612 3.4.2 |
|
| SQL Server |
11.00.2100, 12.0.20008, 13.0.4224.16, 14.0.1000.169 (Windows AD) 16.0 |
|
| Oracle | 19c | v11.2.0.4.0 or higher |
| SAPHANA | 2.00.040.00.1553674765 | ngdbc.jar |
| Redshift |
RedshiftJDBC42-2.1.0.11 RedshiftJDBC42-1.2.10.1009 |
Redshift-JDBC 42-2.0.0.4.jar |
| Snowflake | 3.13.25 | Snowflake JDBC jar 3.0.0 or higher |
| PostgreSQL |
PostgreSQL 9.6.19, PostgreSQL 10.12 |
postgresql-9.4.1207.jar |
| Teradata | 15.10.07.08,16.20.22.01 | terajdbc4_13.jar, tdgssconfig_13.jar |
| Vertica | v10.0 | shared/Vertica-6.1.0.jar |
| Snap | Operation |
|---|---|
| Generic JDBC - Insert | Database Insert |
| Generic JDBC - Execute |
Database Query, Database Update Database Insert, Database Delete |
| Generic JDBC - Select | Database Select |
| Generic JDBC - Update | Database Update |