Working with Greenplum databases

This section provides basic information on working with external Greenplum databases. Since Greenplum is based on PostgreSQL, integrations with them are similar, and some links below may lead to PostgreSQL documentation.

Follow these steps to work with an external Greenplum database:

1. Create a secret containing the password for connecting to the database.

   CREATE OBJECT greenplum_datasource_user_password (TYPE SECRET) WITH (value = "<password>");
   

2. Create an external data source that describes a specific database within the Greenplum cluster. In the LOCATION parameter, pass the network address of the master node of Greenplum. By default, the namespace public is used for reading, but this value can be changed using the optional SCHEMA parameter. The network connection is made using the standard Frontend/Backend Protocol over TCP transport (PROTOCOL="NATIVE"). You can enable encryption of connections to the external database using the USE_TLS="TRUE" parameter.

   CREATE EXTERNAL DATA SOURCE greenplum_datasource WITH (
       SOURCE_TYPE="Greenplum",
       LOCATION="<host>:<port>",
       DATABASE_NAME="<database>",
       AUTH_METHOD="BASIC",
       LOGIN="user",
       PASSWORD_SECRET_NAME="greenplum_datasource_user_password",
       PROTOCOL="NATIVE",
       USE_TLS="TRUE",
       SCHEMA="<schema>"
   );
   

3. Deploy the connector and configure the YDB dynamic nodes to interact with it. Additionally, ensure network access from the YDB dynamic nodes to the external data source (at the address specified in the LOCATION parameter of the CREATE EXTERNAL DATA SOURCE request). If network connection encryption to the external source was enabled in the previous step, the connector will use the system's root certificates. More details on TLS configuration can be found in the guide on deploying the connector.

4. Execute a query to the database.

Query syntax

The following SQL query format is used to work with Greenplum:

SELECT * FROM greenplum_datasource.<table_name>

where:

• greenplum_datasource - identifier of the external data source;
• <table_name> - table name within the external data source.

Limitations

When working with Greenplum clusters, there are a number of limitations:

1. External sources are available only for reading data through SELECT queries. The federated query processing engine currently does not support queries that modify tables in external sources.

2. If the date value stored in the external data source is outside the allowed range for YDB (all dates used must be later than 1970-01-01 but earlier than 2105-12-31), such a value in YDB will be converted to NULL.

3. The YDB federated query processing system is capable of delegating the execution of certain parts of a query to the system acting as the data source. Query fragments are passed through YDB directly to the external system and processed within it. This optimization, known as "predicate pushdown", significantly reduces the volume of data transferred from the source to the federated query processing engine. This reduces network load and saves computational resources for YDB.

   A specific case of predicate pushdown, where filtering expressions specified after the WHERE keyword are passed down, is called "filter pushdown". Filter pushdown is possible when using:

   Description	Example
   Filters like IS NULL/IS NOT NULL	WHERE column1 IS NULL or WHERE column1 IS NOT NULL
   Logical conditions OR, NOT, AND	WHERE column IS NULL OR column2 is NOT NULL
   Comparison conditions =, <>, <, <=, >, >= with other columns or constants	WHERE column3 > column4 OR column5 <= 10

   Supported data types for filter pushdown:

   YDB Data Type
   Bool
   Int8
   Int16
   Int32
   Int64
   Float
   Double

Supported data types

In the Greenplum database, the optionality of column values (whether a column can contain NULL values) is not part of the data type system. The NOT NULL constraint for each column is implemented as the attnotnull attribute in the system catalog pg_attribute, i.e., at the metadata level of the table. Therefore, all basic Greenplum types can contain NULL values by default, and in the YDB type system, they should be mapped to optional types.

Below is a correspondence table between Greenplum and YDB types. All other data types, except those listed, are not supported.