Connectivity Summary
An out of the box connector is available for PostgreSQL databases. It provides support for crawling database objects, profiling of sample data and lineage building.
The drivers used by the connector are given below:
Driver / API: JDBC driver
Version: 42.2.14 (latest version is 42.2.23)
Details: https://mvnrepository.com/artifact/org.postgresql/postgresql/
Technical Specifications
The connector capabilities are shown below:
Crawling
| Supported Objects | Supported Data Types |
| Tables, Table Columns, Views, Stored Procedures, Functions, Triggers, Roles, Users, Permissions, Triggers, Usage Statistics |
bigint, bigserial, bit, boolean, character, date, double, int, interval, money, numeric, path, real, smallint, text, time, timestamp, uuid |
Profiling
Please see Profiling Data for more details on profiling.
|
Feature |
Support |
Remarks |
|
Table Profiling |
Row count, Columns count, View sample data |
|
|
View Profiling |
Row count, Columns count, View sample data |
View is treated as a table for profiling purposes |
|
Column Profiling |
Min, Max, Null count, distinct, top 50 values |
|
|
Full Profiling |
Supported |
|
|
Sample Profiling |
Supported |
Lineage Building
| Lineage Entities | Details |
|
Table lineage |
Supported |
|
Column lineage |
Supported |
|
Lineage Sources |
Stored procedures, functions, triggers, views, SQL queries (from Query Sheet), query logs |
Querying
| Operation | Details |
|
Select |
Supported |
|
Insert |
Not supported, by default. |
|
Update |
Not supported, by default. |
|
Delete |
Not supported, by default. |
|
Joins within database |
Supported |
|
Joins outside database |
Not supported |
|
Aggregations |
Supported |
|
Group By |
Supported |
|
Order By |
Supported |
By default the service account provided for the connector will be used for any query operations. If the service account has write privileges, then Insert / Update / Delete queries can be executed.
Pre-requisites
To use the connector, the following need to be available:
- Connection details as specified in the following section should be available.
- An admin / service account with read privileges.
- JDBC driver is provided by default. In case it needs to be changed, add PostgreSQL drivers into the OvalEdge Jar path to communicate with the PostgreSQL database.
Check the Configuration section for further details on how to add the drivers to the jar path.
Connection Details
The following connection settings should be added for connecting to a PostgreSQL database:
- Database Type: POSTGRESQL
- Connection Name: Select a Connection name for the PostgreSQL database. The name that you specify is a reference name to easily identify your PostgreSQL database connection in OvalEdge. Example: PostgreSQL Connection DB1
- Hostname / IP Address: Database instance URL (on-premises/cloud-based)
Example: ovaledge-postgres.csklygkwz3dx.us-east-1.rds.amazonaws.com - Port number: 5432
- Sid / Database: Name of the database to connect.
- Username: User account login credential
- Password: Password
- Driver Name: JDBC driver name for PostgreSQL . It will be auto-populated.
Example: org.postgresql.Driver - Connection String: PostgreSQL connection string. Set the Connection string toggle button to automatic, to get the details automatically from the credentials provided. Alternatively, you can manually enter the string.
Format: jdbc:postgresql://{server}:5432/{sid}
Example: jdbc:postgresql://ovaledge-postgres.csklygkwz3dx.us-east-1.rds.amazonaws.com:5432/ovaledgedb
Once connectivity is established, additional configurations for Crawling and Profiling can be specified.
|
Property |
Details |
|
Profile Settings |
|
|
Order |
Priority of the rule |
|
Start time and End time |
Used when crawling / profiling are to be scheduled |
|
No. of threads |
No. of threads used to perform profiling |
|
Profile Type |
Disabled/ Auto/ Sample/ Query |
|
Row count Constraint |
No. of rows to be fetched |
|
Sample profile size |
Sample profile row limit |
|
Sample data count |
Sample amount of data to profile |
|
Query Timeout |
Time to wait for response |
|
Crawler options |
Procedures, Functions, Triggers & Views Source Code/ Tables, Views & Columns/ Relationship |
|
Profile options |
Tables and Columns/ Views and Columns |
|
Crawler Rules |
Include and Exclude Regex for table/ column/ procedures |
|
Profile Rules |
Include and Exclude Regex for table/ column |
Points to note
Port number may vary, 5432 is the default port number.
FAQs
- What should I know when upgrading my driver?
The Microsoft JDBC Driver 7.4 supports the JDBC 4.2, and 4.3 (partially) specifications and includes JAR class libraries in the installation package as follows:
JAR JDBC Specification JDK Version https://repo1.maven.org/maven2/org/postgresql/postgresql/42.2.14/postgresql-42.2.14.jar JDBC 42.2.x JDK 8.0 - How much does the driver cost?
The Microsoft JDBC Driver for PostgreSQL is available at no additional charge.