Multi-Database Scanning with JSON Credentials

The Data Discovery rbdms-scanner can scan multiple databases using a JSON credential format for the following data sources:

JSON Connection Properties

  • When using a multi-database connection, database_list is required, as each will replace the {db} placeholder in the URL string when establishing a connection.

  • The base_database property is also required as it is used for the initial connection and retrieving data source-level metadata.

Property

Description

Required

Example Value

connectionURL

Complete connection URL string used to establish the connection.

Yes

jdbc:postgresql://1.1.1.1:5432/{db}

driver_class_name

Name of the driver used to connect.

Yes

org.postgresql.Driver

database_list

CSV list of databases to scan. The same credentials must be configured for all databases in the list.

Yes

database_1,database_2,demo_database

base_database

The database used for the initial connection.

Yes

database_1

user

Username used for authentication.

Yes

username@postgreserver

password

Password used for authentication.

Yes

***********

is-multi_connection_supported

Set to true for Schema-Table structures.

No

c

is-multi_db_supported

Set to true for Database-Schema-Table structures.

No

true

jdbc_query_timeout

The maximum time a query can run before the driver cancels it.

No

Format: PT(XX)(increment)

PT10M (10 minutes)

PT10S (10 seconds)

fetch_size

The quantity in bytes of data that will be fetched in one attempt.

No

1024

auto_commit

Controls whether each SQL statement is committed immediately.

No

false

Generic JDBC

Example for connecting to PostgreSQL using Generic JDBC:

JSON 
{
  "connectionUrl": "jdbc:postgresql://<host>:<port>/{db}",
  "driver_class_name": "org.postgresql.Driver",
  "database_list": "database1,database_demo,db_2",
  "base_database": "database1",
  "user": "username@host",
  "password": "*****",
  "is_multi_connection_supported": "true",
  "jdbc_query_timeout": "PT10M",
  "fetch_size": "1024",
  "auto_commit": "false"
}

Amazon Athena

Note:

When scanning multiple databases using other connectors, the format of the connectionURL changes in some cases, but the other parameters remain the same, except for Amazon Athena.

Amazon Athena does not require base_database or database_list , as the connection is established for the Amazon account.

JSON 
{
  "S3OutputLocation": "s3://dd-athena-storage/Unsaved",
  "connectionUrl": "jdbc:awsathena://AwsRegion=<region>;UID=<username>;PWD=<password>;WorkGroup=primary;S3OutputLocation=s3://dd-athena-storage/Unsaved",
  "password": "<password>",
  "username": "<username>"
}

IBM DB2

JSON 
{
  "base_database": "<DB-Name>",
  "connectionUrl": "jdbc:db2://<Host>:<Port>/{db}",
  "database_list": "<DB1>,<DB2>",
  "password": "<Password>",
  "user": "<User>"
}

MicrosoftSQL

JSON 
{
  "connectionUrl": "jdbc:sqlserver://<host>;database={db}",
  "user": "<user>",
  "password": "<password>",
  "base_database": "<db-name>",
  "database_list": "<db1>,<db2>"
}

Oracle

JSON 
{
  "database_list": "XE,OTHERDB",
  "base_database": "XE",
  "password": "password",
  "user": "username",
  "connectionU
  
  
  rl": "jdbc:oracle:thin:system/oracle@localhost:55182:{db}"
}

Troubleshooting

1. Database Missing Errors

CODE 
Message:[SQLException: No specific database specified for parameterized url for dialect postgresql.] Arguments:[[Dialect: postgresql]] Date:[2024-05-30T19:11:38]
java.util.concurrent.CompletionException: com.onetrust.discovery.scanner.common.exceptions.DataDiscoveryException: SQLException: No specific database specified for parameterized url for dialect postgresql.

Indicates there is no database to connect to in the URL. Check if the base_database property is missing from the credential JSON.

CODE 
Message:[PSQLException: FATAL: database "username" does not exist] Arguments:[[Dialect: postgresql]] Date:[2024-05-30T19:16:34]
java.util.concurrent.CompletionException: com.onetrust.discovery.scanner.common.exceptions.DataDiscoveryException: PSQLException: FATAL: database "intg1user@intpostgre" does not exist

Indicates the base_database specified does not exist when the "username" appears in the error, as the database it may indicate the value for is an empty string.

2. Connection Errors

CODE 
Message:[PSQLException: FATAL: password authentication failed for user "username"] Arguments:[[Dialect: postgresql]] Date:[2024-05-30T19:26:09]
java.util.concurrent.CompletionException: com.onetrust.discovery.scanner.common.exceptions.DataDiscoveryException: PSQLException: FATAL: password authentication failed for user "username"

Username or password is incorrect.

3. Incorrect Formats

CODE 
Message:[PSQLException: FATAL: Invalid Username specified. Please check the Username and retry connection. The Username should be in <username@hostname> format.] Arguments:[[Dialect: postgresql]] Date:[2024-05-30T19:27:30]
java.util.concurrent.CompletionException: com.onetrust.discovery.scanner.common.exceptions.DataDiscoveryException: PSQLException: FATAL: Invalid Username specified. Please check the Username and retry connection. The Username should be in <username@hostname> format.

The specified username should include the server where the connection will be attempted (e.g., user@server)