JDBC Data Sources

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.

The Data Source name must be unique.

5. Under Connection Adapter, select the relevant JDBC connector.
6. Fill in the Connection Settings details (including the Driver Class Name, URL, Username.
7. Click Test Connection using the proper validation query that fits the DB you chose.
8. 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:

• 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:

2. Click Generate DDL

Generated DDL:

3. Model was created according to generated DDL:

4. Click Set to define Translator Properties.

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

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.

6. 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