JDBC Load

​

Properties

​

Connect

​

Configure

​

Destination

​

Advanced Settings

​

Manifest file

[
    {
      "name": "<database_type>",
      "driverClassName": "<driver_class_name>",
      "baseUrl": "<base_url>",
      "limitStrategy": "<limit_strategy>",
      "fetchSize": <fetch_size>,
      "defaultProperties": {
        "<property>": "<value>",
        ...
      },
      "blacklistedProperties": [
        "<property>",
        ...
      ],
      "propertyOverrides": {
        "<property>": "<value>",
        ...
      },
      "skipQueryValidation": "true"
    }
]

• name: This should be a simple name for the driver that identifies the database type it’s used with. For example MySQL, Mailchimp, or PostgreSQL. The name will appear in the component’s Database Type drop-down.
• driverClassName: The fully qualified name of a class that implements the java.sql.Driver interface in Java. The fully qualified name of the driver class includes the package name followed by the class name, ensuring it’s unique across all Java packages. For example, the driver class name for MySQL’s Connector/J driver is com.mysql.cj.jdbc.Driver (version 8.0 and above), and the class name for the PostgreSQL driver is org.postgresql.Driver.
• baseUrl: A template URL used to prompt the user to enter the correct format of URL in the component’s Connection URL property. This should be the basic URL for the driver, such as jdbc:postgresql:// or jdbc:cdata:mailchimp:.
• limitStrategy: Controls how the component gets metadata about the query. Different databases have different ways of supporting this. Enter the method required by the data source you are configuring:
  • limit-inline: The most straightforward and widely supported method for limiting query results, commonly used in MySQL, PostgreSQL, SQLite, and others.
  • top-n: Similar to limit-inline, but commonly used in SQL Server and Sybase.
  • fetch-first-n: Similar to limit-inline, but supported by newer versions of databases such as DB2, Oracle (12c and later), and PostgreSQL.
  • rownum: Uses a pseudocolumn in Oracle to limit the number of rows returned, and is particularly useful for complex queries or pagination.
  • limit-outer: A sophisticated technique to limit query results by wrapping the main query within an outer query where the limit is applied. This strategy is particularly useful for complex queries that require pre-processing steps before the application of the limit. Commonly used in databases that can use optimized nested queries.
  • none: No limit is applied to the query, either due to retrieving all records for processing or when the dataset is known to be within acceptable size limits. Commonly used in all SQL databases.
• fetchSize: Used in conjunction with the limitStrategy, this is the number of rows fetched at once, if supported by the database driver. Enter this as a JSON integer value, i.e. not within " ".
• defaultProperties: Define any required defaults for driver properties. These defaults can be overridden by the user when the component is configured (but see blacklistedProperties, below, for how to prevent overrides). List the name of each default property and its default value as a name:value pair inside the defaultProperties object. This is a JSON object requiring { } delimiters. For example:
  "defaultProperties": {
      "connectTimeout": "20000",
      "allowLoadLocalInfile": "false",
      "allowUrlInLocalInfile": "false",
      "allowLocalInfile": "false"
  },
  
  If there are no default properties, this element is still required and should be an empty object:
  "defaultProperties": {},
  
• blacklistedProperties: List any properties that you don’t want to be accessible from the UI. For example, if you have set a default property here in the manifest file that you don’t want the pipeline creator to be able to override in the UI, you should list it here. List the name of each blacklisted property inside the blacklistedProperties array. This is a JSON array requiring [ ] delimiters. For example:
  "blacklistedProperties": [
      "allowLoadLocalInfile",
      "allowUrlInLocalInfile",
      "allowLocalInfile"
  ],
  
  If there are no blacklisted properties, this element is still required and should be an empty array:
  "blacklistedProperties": [],
  
• propertyOverrides: If the driver is expecting property names that are different from those used by the component, you can map the expected names here. For example, MySQL expects “user” instead of “username” as used by the component, so in a manifest file for MySQL you would specify: "username": "user". List each required override inside the propertyOverrides object. This is a JSON object requiring { } delimiters. For example:
  "propertyOverrides": {
      "username": "user",
      "password": "pass"
  }
  
  If there are no required overrides, this element is still required and should be an empty object:
  "propertyOverrides": {}
  
• skipQueryValidation: The component’s default behavior is to validate a query written in Advanced mode by fetching metadata from the data source. In some cases, returning metadata takes a considerable time, resulting in slow validation of the component. To avoid such delays, include this property in your manifest file and set it to true. This property is optional, and should be omitted if not needed.

​

MySQL

[
    {
      "name": "MySQL",
      "driverClassName": "com.mysql.cj.jdbc.Driver",
      "baseUrl": "jdbc:mysql://",
      "limitStrategy": "limit-inline",
      "fetchSize": 500,
      "defaultProperties": {
        "connectTimeout": "20000",
        "allowLoadLocalInfile": "false",
        "allowUrlInLocalInfile": "false",
        "allowLocalInfile": "false"
      },
      "blacklistedProperties": [
      ],
      "propertyOverrides": {
        "username": "user",
        "password": "password"
      }
    }
]

​

SAP Hana

[
    {
      "name" : "SAPHana",
      "driverClassName" : "com.sap.db.jdbc.Driver",
      "baseUrl" : "jdbc:sap://",
      "fetchSize" : 500,
      "limitStrategy" : "top-n",
      "defaultProperties": {
      },
      "blacklistedProperties": [
      ],
      "propertyOverrides": {
        "username": "user",
        "password": "password"
      }
    }
]

​

InterSystems Caché

[
   {
    "name": "Intersystems Cache",
    "driverClassName": "com.intersys.jdbc.CacheDriver",
    "baseUrl": "jdbc:Cache://",
    "limitStrategy": "top-n",
    "fetchSize": 500,
    "defaultProperties": {
      "connectTimeout": "20000"
    },
    "blacklistedProperties": [],
    "propertyOverrides": {
      "username": "user",
      "password": "password"
    }
  }
]

​

Mailchimp

[
   {
    "name": "mailchimp",
    "baseUrl": "jdbc:cdata:mailchimp:",
    "limitStrategy": "limit-inline",
    "driverClassName": "cdata.jdbc.mailchimp.MailChimpDriver",
    "blacklistedProperties": [
        "logfile",
        "verbosity"
    ],
    "defaultProperties": {
        "rtk": "434D524A5641535552425641454E54503156474A3138323200000000000000000000000000000000384A36435347324400005239584145445A39344D545A0000"
    }
  }
]

​

Informix

[
   {
    "name": "Informix",
    "driverClassName": "com.informix.jdbc.IfxDriver",
    "baseUrl": "jdbc:informix-sqli://",
    "limitStrategy": "limit-inline",
    "fetchSize": 500,
    "defaultProperties": {},
    "blacklistedProperties": [],
    "propertyOverrides": {
    "username": "user"
    }
  }
]