JDBC Collect Slice

Overview

Java Database Connectivity (JDBC) is an API defined for Java programming to provide an unified interface to connect to various relational databases. It is based on Java technology and is part of Java Standard Edition platform from Oracle Corporation.

JDBC library provides API for following tasks:

  1. Making a connection to a database
  2. Preparing SQL statements.
  3. Executing SQL statements in the connected database.
  4. Viewing and modifying the results.

Supported SQL dialects in JDBC slice

JDBC is SQL dialect (DBMS-specific versions of SQL) agnostic and only provides the uniform interface.

The JDBC slice currently supports following SQL dialects (more to follow):

  1. PostgreSQL
  2. MS SQL Server
  3. Open Access (for Netsuite)
  4. Snowflake

Prerequisites before adding the slice

Following parameters are mandatory before adding a JDBC slice:

JDBC Driver path (in AWS S3):

Every database provider will provide a driver library in the form of a Java Archive (jar) file. Typically the jar file is packaged along with the additional documentation and example code in the form of a zip file. This zip file has to be downloaded and uploaded to an S3 path that is accessible by the JDBC slice.

The path to the jar file is usually as follows: e.g., s3://<installation_name.datacoral>/jdbc_libraries/<driver_file.zip>

JDBC Jar file name

Name of the actual JDBC jar file in the downloaded archive in the previous step. For e.g., snowflake-jdbc-3.13.3.jar

Java Driver Class name

JDBC driver jar will have an main entry point for the invocation and is usually specified by the JDBC library provider.

Examples of common driver class names:

  1. PostgreSQL - org.postgresql.Driver
  2. MS SQL Server - com.microsoft.sqlserver.jdbc.SQLServerDriver
  3. Open Access - com.netsuite.jdbc.openaccess.OpenAccessDriver
  4. Snowflake - net.snowflake.client.jdbc.SnowflakeDriver

Database type

Datacoral specific naming convention for identifying the database that the slice is connecting to fetch data.

  1. PostgreSQL - postgres
  2. MS SQL Server - sqlserver
  3. Open Access - netsuite
  4. Snowflake snowflake

JDBC connection URL

JDBC connection URL follows the JDBC connection string format: e.g., jdbc:<databasetype>://server_ip_or_name:<port_number>/<database_name>

Steps to add this slice to your installation

Step 1. Select 'Add a connector' menu option in the home screen

Step 2. Select 'JDBC' under 'Database & Query Engines' category Step 2

Step 3. Provide a suitable connector name and input other slice configuration parameters and click 'Next' Step 3

Step 4. Enter JDBC connection URL, Username & Password and click 'Check Connection' Step 4

Step 5. Once the connection is successful, click 'Next' Step 5

Step 6. Provide filter options for finding right set of tables for sync and click 'Next' Step 6

Step 7. Select tables from source that need to be synced, modify execution mode settings by clicking 'Edit' option once you hover on the loadunit Step 7

Step 8. Change the execution mode and pagination settings, if necessary. Click 'Update' to close the settings popup and click 'Finish' Step 8

Step 9. After the tables are successfully created, you will be re-directed to the connector page Step 9

Slice output

Output of this slice is stored in S3 and chosen warehouse(s).

AWS S3 Data stored in AWS S3 is partitioned by date and time in the following bucket s3://datacoral_installation_name.datacoral/<connector_name>

Questions? Interested?

If you have questions or feedback, feel free to reach out at hello@datacoral.co or Request a demo