Connecting to PostgreSQL in Astera Centerprise

PostgreSQL is an open source relational database management system that stores data workloads. It helps in managing all kinds of datasets with its extensive range of features. Astera Centerprise provides connectivity to PostgreSQL databases through its database objects. You can connect to PostgreSQL databases in Astera Centerprise using the Database Table Source, Database Table Destination, Database Lookup, SQL Query Lookup, SQL script task in a workflow, and database write strategies.

In this article we will cover:

1. A use case where PostgreSQL is being used as a database in Astera Centerprise.

2. How to connect to PostgreSQL database in Centerprise.

3. Different data loading and reading options for PostgreSQL databases in Centerprise.

Use Case

We have some sample Customer data stored in a PostgreSQL database. The data contains multiple fields containing customer information. One of these fields is for Countries that the customers reside in. We want to replace these country names with the respective country code. We can then use these codes further to obtain additional insights from this data.

We’ll be using the Database Table Source object to source data from the PostgreSQL database.

1. To configure the Database Table Source object, right-click on its header and select Properties from the context menu.

This will open a new window where you can configure connection with the PostgreSQL database.

2. The first step is to select the Data Provider. Select PostgreSQL as your data provider from the drop-down list.

• Put in the required credentials for your PostgreSQL account.

1. User ID
2. Password
3. Server Name
4. Database
5. Port
6. Schema

• Test Connection to make sure that your database connection is successful and click Next to proceed to the next screen.

3. Here, you can Pick Source Table and modify Reading Options for your PostgreSQL database.

In this case, we will select the Customers table.

To learn more about data reading options in a database table source, click here.

4. Now, we will use the List Lookup transformation and provide coded values for all the country records in the data.

To know more about how the List Lookup Transformation works in Astera Centerprise, click here.

5. Next, we will pick a table in the PostgreSQL database and write this data to it using the Database Table Destination in Astera Centerprise.

To configure the Database Table Destination object, right-click on its header and select Properties from the context menu.

This will open a new window on your screen.

Here you can configure the properties for the Database Table Destination object.

The first step is to specify the Database Connection for the destination object.

Select PostgreSQL as your data provider from the drop-down list.

• Put in the required credentials for your PostgreSQL data provider account.

1. User ID
2. Password
3. Server Name
4. Database
5. Port
6. Schema

• Test Connection to make sure that your database connection is successful and click Next.

You can Pick Table or Create Table and modify Writing Options for PostgreSQL Database on the Properties screen.

Note: In this case, we will pick the Customers table which is the same as the source table.

To read more on the different data writing options available for a database table, click here.

Once you’ve set the data reading options, click OK.

We have successfully configured PostgreSQL as our database provider for the Database Table Destination object. The data will now be written to the PostgreSQL Database once the dataflow is run.

If we now preview the data in the database table, we can see that the country names have been replaced with country codes and the data has been written into the destination table.

We have successfully connected to PostgreSQL Database and completed our use case in Astera Centerprise.