Accessing Data from PostgreSQL

Magpie offers the PostgreSQL data source, which provides users the ability to connect to PostgreSQL databases through standard JDBC connections or through the Copy Manager. This is the preferred method for accessing PostgreSQL databases in Magpie as this datasource yields much faster performance for large scale reads and writes when accessing databases through the Copy Manager.

Postgres sources are configured very similarly to standard JDBC data sources in Magpie. Please view Data Source JSON specification to see more details. The configuration is set during the initial creation of the source or through an ALTER statement using a JSON specification to set the values.

An example of such a command is shown below.

create data source {
  "name": "my_postgres_source",
  "sourceType": "postgres",
  "host": "mydb.mycompany.com",
  "port": 5432,
  "database": "analytics",
  "user": "my_user_name",
  "password": "**********"
}

Once a Postgres source has been created, the tables within it can be used as the underlying storage for tables within Magpie. The commands present in the "Accessing Data from JDBC Sources" page are broadly applicable to PostgreSQL data sources as well.

• List Data Source Schemas

• List Data Source Tables

• Create Schema From Database

• Create Table From Database Table

• Create Table From Database SQL

• Execute SQL

Note that the PostgreSQL data source does not support array type fields when processing through the Copy Manager. To use fields in PostgreSQL databases containing array types users must not use the bulk access option when creating tables and schemas and must specify the jdbc persistence mapping type in save commands. This will force Magpie to connect to the database using JDBC rather than the Copy Manager. An example of specifying the persistence mapping type on a save command is below.

save table as table {
  "name": "consumer_analytics",
    "sourceName": "my_postgres_source",
    "expression": {
      "tableName": "consumer"
    },
    "mappingType": "jdbc"
  }
}

Security

To access this type of data source, you may need to adjust the security configuration of your cloud environment. In particular, you need to generate database user credentials for the Magpie cluster, along with appropriate permissions to access relevant data. Please reach out to a member of the Silectis team with any questions or support requests.