Integrate data from Progress OpenEdge RDBMS to Databricks using Matillion

Creating a User in Progress OpenEdge RDBMS

Progress OpenEdge RDBMS does not manage user accounts internally in the same way as some other RDBMSs, such as Oracle or SQL Server. Instead, OpenEdge defers authentication to the operating system or external security mechanisms by default. However, you can use OpenEdge’s Security Administration features to register identities, assign permissions, and, when using SQL access, create SQL-level users and roles.

Below are two common scenarios for creating and managing users in OpenEdge:

1. Creating a SQL User (SQL-92 User) via SQL

To enable SQL access (for ODBC/JDBC or SQL scripting), you need to create a user identity in the OpenEdge SQL catalog. This is typically performed using the

CREATE USER

sqlexp

-- Replace 'testuser' and 'password123' as appropriate
CREATE USER testuser
    WITH PASSWORD 'password123';

Notes: - Use secure passwords that comply with your organization's policies. - The created SQL user identity can be associated with SQL privileges and roles.

Grant Permissions to the User

GRANT SELECT, INSERT, UPDATE, DELETE
    ON PUB.customer
    TO testuser;

2. Registering an ABL User (Data Administration)

For ABL (Advanced Business Language) clients, user accounts typically correspond to operating system users. Database-level user information (such as users and their properties) can be managed using the Data Administration Tool (Data Dictionary) or programmatically through ABL code.

Using Data Administration Tool

1. Connect to the database from Data Administration.
2. Navigate to Admin > Security > Edit User List.
3. Add the operating system username (e.g.,

   DOMAIN\username

   or simply

   username

   ), and optionally assign User Levels or Security Groups.

Programmatically with ABL

```abl DEFINE VARIABLE hSec AS HANDLE NO-UNDO.

hSec = SESSION:GET-SECURITY-POLICY(). hSec:REGISTER-USER("john_doe"). hSec:SET-USER-GROUP("john_doe", "MyGroup"). ```

Note: These examples assume you use the OpenEdge Security Policy API (available in certain OpenEdge releases). Adapt as needed for your environment.

Additional Considerations

• For detailed audit and authentication control, consider configuring OpenEdge Authentication Gateway.
• Always consult your organization's security policies and the OpenEdge documentation matching your database version for compliance and further options.
• Use

  GRANT

  and

  REVOKE

  to manage SQL-based privileges, as required.

For more details, refer to the Progress OpenEdge SQL Reference Guide.

Installing the JDBC driver

At the time of writing, the JDBC driver for the Progress OpenEdge RDBMS database is not included within Matillion Data Productivity Cloud by default. This is due to licensing and redistribution restrictions imposed by Progress Software. Therefore, users must manually download and install the JDBC driver before establishing connections to OpenEdge databases from Matillion.

Downloading the Progress OpenEdge JDBC Driver

1. Navigate to the official Progress Software downloads page: https://www.progress.com/openedge/downloads
2. Locate and download the "Type 4 JDBC driver" for OpenEdge. Type 4 drivers are recommended due to their platform independence and direct connectivity to the target database over TCP/IP.
3. After downloading, extract the driver files if they are provided as a compressed archive. The main driver file will typically have a

   .jar

   extension.

Installing the JDBC Driver in Matillion Data Productivity Cloud

1. Follow the detailed installation instructions published at this URL: https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/
2. Typically, the process involves uploading the JDBC driver

   .jar

   file to the Matillion agent environment, either through the user interface or via a specific directory. Please refer to the above documentation for the precise steps required in your deployment.

Using the Driver in Matillion

Once the driver is installed, you can use it to connect from within Matillion's Data Productivity Cloud, including leveraging JDBC connection capabilities for visual database connectors or code-driven workflows.

To get started with OpenEdge connections, please review the usage guidance at: https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/
This documentation includes details on configuring connection properties, authentication, and usage examples for querying databases via JDBC within Matillion.

Checking network connectivity

To ensure successful data integration with Matillion Data Productivity Cloud, you must make sure that the Progress OpenEdge RDBMS database accepts incoming connections according to your deployment configuration:

• Full SaaS Agent Configuration:
  Allow inbound connections from all the Matillion IP addresses specified at this URL. Update the database server’s network access controls (for example, firewall or security group settings) with these IP addresses to permit communication from the Matillion cloud service.

• Hybrid SaaS Deployment:
  Incoming traffic should be permitted from within your own virtual private cloud (VPC) where the Matillion Hybrid SaaS agent resides. For guidance on confirming connectivity and troubleshooting network access, you can visit the network check utilities on the Matillion Exchange.

Additionally, if you reference your Progress OpenEdge RDBMS database by its DNS hostname (instead of a direct IP address), ensure that the relevant Matillion agent—whether Full SaaS or Hybrid SaaS—is able to successfully resolve this DNS name. Proper DNS resolution is required for establishing and maintaining connections to your database.

How to Query Data from a Progress OpenEdge RDBMS Database

Example: Progress OpenEdge SQL SELECT Statements

To retrieve data from a Progress OpenEdge database, you can use standard SQL queries via the OpenEdge SQL interface or compatible ODBC/JDBC drivers. Some SQL syntax examples:

``` -- Retrieve all records from a table SELECT * FROM pub.customer;

-- Select specific columns SELECT customer_num, name, address FROM pub.customer;

-- Filter data SELECT * FROM pub.orders WHERE order_date >= '2024-01-01';

-- Aggregate functions SELECT COUNT(*) AS total_orders FROM pub.orders WHERE shipped = TRUE; ```

Remark: The

pub

Datatype Conversion with Databricks

When querying and loading data from Progress OpenEdge RDBMS into Databricks, be aware that datatype mapping occurs:

• OpenEdge data types (e.g.,

  INTEGER

  ,

  CHARACTER

  ,

  DATE

  ) are automatically translated to Databricks-compatible types (e.g.,

  int

  ,

  string

  ,

  date

  ).
• Some complex or proprietary types may require explicit conversion/casting, either in the SQL query (using

  CAST

  ), during ETL, or in Databricks.

Example of explicit casting:

SELECT customer_num, CAST(balance AS DOUBLE) AS balance_amount FROM pub.customer;

Pattern: Initial and Incremental Loads

The recommended approach uses:

1. Once-off Initial Load: Extract the entire dataset.
2. Incremental Loads: Extract only data changed or added since the last load.

Both should use the same Data Integration tool/component (such as the Database Query component). The main difference is the presence of a filtering clause in the incremental phase.

Initial Load Example

Database Query component SQL:

SELECT * FROM pub.customer;

No filter: All data is extracted.

Incremental Load Example

Introduce a filter on a column that tracks changes (e.g.,

updated_at

last_modified

Database Query component SQL:

SELECT * FROM pub.customer WHERE updated_at > '2024-06-01 00:00:00';

Update the filter value based on the last successfully loaded timestamp from Databricks.

For more on incremental loading strategies using ETL tools, refer to the Matillion article.

Data Integration Architecture

Loading data in advance of integration is a proven strategy for simplifying complex data integration workflows, allowing organizations to divide and conquer the problem by separating it into two distinct steps: data loading (Extract and Load, or EL) and data transformation (Transform, or T). This approach exemplifies the key advantages of the ELT (Extract, Load, Transform) architecture. Once the raw data is loaded into your Databricks database, integration tasks that require data transformation are best accomplished through robust data transformation pipelines, which ensure data quality, repeatability, and scalability. Another notable benefit of the ELT architecture is that all data processing—especially transformation and integration—takes place inside the target Databricks environment. This makes processing both fast and scalable, as Databricks leverages its built-in compute resources on-demand, and eliminates the need for maintaining and paying for additional, external data processing infrastructure.