Onboarding Data from Generic JDBC Source Types

Prerequisites

• The JDBC Driver must be JDBC Type 4 compliant and SQL-92 compliant.
• The no. of Generic JDBC Source types that can be registered depends on your License. Please make sure you are licensed to use Generic JDBC Source Types and you have free slots to register new source types.

$inline[badge,Limitation,warning] The performance of the ingestion or load on the source system machine when using a generic JDBC source depends on the driver used.

Registering a Generic Source Type

To register a generic source type, perform the following steps:

1. Navigate to Admin > Extensions > Generic Source Types.
2. Click the Add Generic Source Type button.

$inline[badge,NOTE,primary] You can delete a generic source type only after deleting the sources that use the specific generic source type.

3. Enter the following details:

4. Click Save. The Generic JDBC Source will be registered as a new source type.

5. Click Manage Source Type Registrations to view all the generic source types.

Creating a Generic JDBC Source

For onboarding data from a Generic JDBC source, see $link[page,301726,auto$]. The name of the generic JDBC source you registered will be displayed in the Source Type drop-down list.

Generic JDBC Source Configurations

Source SQL Functions

You can override the default SQL functions at the source level and customize the generated SQLs for the corresponding driver. You can override the default SQL select clause at the table level.

Once the settings are saved, you can test the connection.

Configuring a Generic JDBC Table

With the source metadata in the catalog, you can now configure the table for CDC and incremental synchronization.

1. Click the Configuration link, for the desired table.
2. Provide the ingestion configuration details.

Advanced Configurations

Following are the steps to set advanced configuration for a table:

1. Click the Data Catalog menu and click Ingest for the required source.

$inline[badge,NOTE,primary] For an already ingested table, click View Source, click the Tables tab, click Configure for the required table and click the Advanced Configuration tab.

2. Click the Configure Tables -> Advanced Configuration -> Add Configuration.
3. Enter key, value, and description. You can also select the configuration from the list that appears.

$inline[badge,NOTE,primary] You can also configure Catalog separator and Schema separator through Advanced configurations.

Sync Data to Target

Using this option, you can configure the Target connections and sync data as described in the section $link[page,301739,auto$]

The following are the steps to sync data to target.

1. From the Data Sources menu, select one of the tables and click View Source/Ingest button.
2. Select the source table to be synchronized to Target.
3. Click the Sync Data to Target button.
4. Enter the mandatory fields as listed in the table below:

Previewing Query

You can preview the queries used for ingesting the table in the Preview Query tab.

$inline[badge,Limitation,warning] The derived split-by option is currently not supported.

Click $link[page,301726,auto$] to navigate back to complete the onboarding process.

Adding a column to the table

After metadata crawl is complete, you have the flexibility to add a target column to the table.

Target Column refers to adding a target column if you need any special columns in the target table apart from what is present in that source.

You can select the datatype you want to give for the specific column

You can select either of the following transformation modes: Simple and Advanced

Simple Mode

In this mode, you must add a transformation function that has to be applied for that column. Target Column with no transformation function applied will have null values in the target.

Advanced Mode

In this mode, you can provide the Spark expression in this field. For more information, refer to $link[page,301743,Adding Transform Derivation,editing-table-schema].

$inline[badge,NOTE,primary] When table is in ready state (already ingested), schema editing is disabled.

Ingesting Data

For ingesting a Generic JDBC source, see $link[page,301744,auto$]

Reference

For details on the additional drivers supported by Infoworks, see $link[page,301725,auto$].