JDBC Data Sources

To connect to an external database via JDBC, you need to create a new JDBC-based Data Source in the Platform. This involves selecting the connector, configuring connection settings, defining a virtual schema (model), and optionally generating DDL statements. Follow the steps below to complete the setup.

Creating a JDBC Data Source

1. In the Tenant Settings screen, go to the PIP Settings tab.

2. From the Policy Authorization Agent dropdown, select the PAA where the new Data Source should be created.

3. Click New Data Source.

4. Enter a unique Name for the Data Source.

   

5. Under Connection Adapter, select the appropriate JDBC connector.

6. Fill in the Connection Settings, including:

   • Driver Class Name
   • JDBC URL
   • Username (and any other required credentials)

7. Click Test Connection and provide a valid validation query based on the selected database.

Defining the Data Model

8. In the Models section, enter a unique Model Name.

9. Under Data Model Properties, add key-value pairs prefixed with importer. to control how database objects are imported into the model.

   Common Importer Properties:

   Parameter	Description	Default
   catalog	Name of the catalog to fetch data objects from.	null
   schemaPattern	Exact schema name or wildcard pattern.	null
   tableNamePattern	Exact table name or wildcard pattern.	null
   tableTypes	Comma-separated list of table types (e.g., "TABLE","VIEW").
   excludeTables	Regex to exclude specific tables. Supports negative look-ahead patterns.
   importKeys	Whether to import primary and foreign key definitions.	true
   importIndexes	Whether to import index and cardinality metadata.	false

   For more options, refer to: Data Model Properties for JDBC

Generating and Using DDL

10. Click Generate DDL to create a DDL statement based on the importer properties you defined.

11. The generated DDL appears in the DDL field.

12. Review and edit the DDL if necessary.

    ⚠️ Regenerating the DDL will overwrite any manual changes.

13. Alternatively, you can manually enter a custom DDL statement in the field.

Examples and Notes:

• Use schemaPattern or tableNamePattern to limit DDL generation to specific schemas or tables.

• Oracle: Use the default schema name in the DDL.

• MSSQL and others: Use nameinsource to specify schema-qualified table names, for example:

  CREATE FOREIGN TABLE my_table (
    col1 string,
    col2 string
  ) OPTIONS (nameinsource 'schema123.table_name');
  

Visual Example:

• Importer properties set:

  

• Generated DDL:

  

• Model after saving:

  

Optional: Setting Translator Properties

14. Click Set to define additional Translator Properties (only if needed).
15. Enter each Key and Value pair.

Note: These settings are optional. For advanced usage, see Available Translator Properties for JDBC

16. Click Save to complete the Data Source configuration.

Supported JDBC Translators

The following JDBC connectors are supported:

• Microsoft SQL Server
• Oracle
• MySQL
• PostgreSQL
• DB2
• JDBC Simple
• Sybase
• Google BigQuery