JDBC Data Sources

  • Updated on Nov 24, 2024
  • Published on May 21, 2023
  • 3 minute(s) read
Prev Next

Creating a New JDBC Data Source

To create a Data Source based on JDBC repository:

  1. In the Tenant Settings screen, select the PIP Settings tab.
  2. From the Policy Authorization Agent drop down list, select the PAA in which you wish to create the new Data Source.
  3. Click New Data Source.
  4. In the Name field, enter the name of the new Data Source.

image.png
The Data Source name must be unique.

  1. Under Connection Adapter, select the relevant JDBC connector.
  2. Fill in the Connection Settings details (including the Driver Class Name, URL, Username.
  3. Click Test Connection using the proper validation query that fits the DB you chose.
  4. Under the Models section:
  • In the Name field, enter a virtual schema name.
  • The Model name must be unique.*
  • Under Data Model Properties you can add key value pairs of importer configuration, each key should be prefixed with ‘importer.’ These properties will be used for DDL generation (see below).

Some of the common configuration keys:

Parameter Description Default
catalog Name of a catalog in your DB that you want to use for fetching data objects null
schemaPattern Pattern represents the schema names that you want to fetch from your external DB. IT could be the exact schema name or pattern using wildcards according to what your DB is supporting null
tableNamePattern Pattern represents the table names that you want to fetch from your external DB. It could be the exact table name or pattern using wildcards according to what your DB is supporting null
tableTypes Comma separated list - without spaces - of imported table types. Usually you will use "TABLE" and/or "VIEW"
excludeTables use Regex to define a filter for excluding tables from being imported to the model. It's possible to have an inclusion filter with a Regex negative look-ahead (?!)
importKeys Defines if the imported table will import the primary and foreign keys true
importIndexes Defines if the imported table will import its index/unique key/cardinality information false
  • You can find more properties for advance usage in Available Data Model Properties for JDBC.

Generate DDL – allows the user to generate a DDL statement according to the data model importer properties.

  1. After setting the relevant and needed importer properties and click the generate DDL button.
  2. A DDL statement will be generated and fill in the DDL field.
  3. Review the DDL, delete and update according to your need, or change properties and regenerate.
  4. When saving, the model will be created using the DDL.
    The importer properties depend on the Data Source you are configuring.

For example, if you have multiple schemas, you might need to use schemaPattern key and useFullSchemaName key.

Another example, use the key tableNamePattern to generate DDL only for a specific table rather than for all tables in the schema.

DDL Field – in this field you can type in a DDL statement that will create foreign table as your model. You can either type in your DDL manually or use the above mention Generate DDL capability, and use the generated DDL as is or with your modifications.
If you regenerate DDL it will delete your manual DDL inserts/changes.

Use schema names in DDL statements –

  1. Oracle – you need to use the default schema name so data can be fetched using the foriegn table.
  2. MSSQL (and others) - you can add the schema name by using the name in source option like this – ‘CREATE FOREIGN TABLE ( string, string,…) OPTIONS (nameinsource ‘.<table/view>’)’.
  • When using the name in source option we can set any name we want in the create foreign table name part. example - CREATE FOREIGN TABLE ABC (colA string, colB string…) OPTIONS (nameinsource ‘schema123.tbl_xyz’)

Example for Generated DDL process and result:

  1. Set importer properties:

image.png

  1. Click Generate DDL

Generated DDL:

image.png

image.png

  1. Model was created according to generated DDL:

image.png

  1. Click Set to define Translator Properties.

  2. Enter the Key and Value for the new Property.

    Check out our Translator and Model Properties article for more information about Translator Properties.

image.png

Note: Usually, you will not need to set Translator properties. You can find list of possible properties for advance usage in Available Translator Properties for JDBC.

  1. Save Data Source.

Supported JDBC Translators

Detailed list of supported JDBC translators can be found below:

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