Working with Yandex MPP Analytics for PostgreSQL databases

This section provides the basic information about working with Yandex MPP Analytics for PostgreSQL.

To work with a Yandex MPP Analytics for PostgreSQL database, follow these steps:

1. Create a connection containing your database connection credentials.
2. Run a query to the database.

Example of a query for reading data from Yandex MPP Analytics for PostgreSQL:

SELECT * FROM greenplum_mdb_connection.my_table

Where:

• greenplum_mdb_connection: Name of the DB connection you created.
• my_table: Name of the table in the database.

Setting up a connectionSetting up a connection

To create a connection to Yandex MPP Analytics for PostgreSQL:

1. In the management console, select the folder where you want to create a connection.

2. In the list of services, select Yandex Query.

3. In the left-hand panel, go to the Connections tab.

4. Click Create new.

5. Specify the connection parameters:

   1. Under General parameters:

      • Name: Name of the connection to Yandex MPP Analytics for PostgreSQL.
      • Type: Managed Service for Greenplum.

   2. Under Connection type parameters:

      • Cluster: Select an existing Yandex MPP Analytics for PostgreSQL cluster or create a new one.

      • Service account: Select an existing Yandex MPP Analytics for PostgreSQL service account, or create a new one with the managed-greenplum.viewer role, and use it to connect to Yandex MPP Analytics for PostgreSQL clusters.

        To use a service account, the iam.serviceAccounts.user role is required.

      • Database: Select the database you will use when working with the Greenplum® cluster.

      • Schema: Specify the namespace to use when working with the Greenplum® database.

      • Login: Username to use when connecting to Greenplum® databases.

      • Password: User password to use when connecting to Greenplum® databases.

6. Click Create.

You need a service account to detect Yandex MPP Analytics for PostgreSQL cluster connection points inside Yandex Cloud. You need a separate login/password pair to access data.

Query syntaxQuery syntax

Here is the SQL query format used to access Greenplum®:

SELECT * FROM <connection>.<table_name>

Where:

• <connection>: Name of the DB connection you created.
• <table_name>: Name of the table in the database.

LimitationsLimitations

Some limitations apply when working with Greenplum® clusters.

Limitations:

1. External sources are only available for data reading through SELECT requests. Yandex Query does not currently support requests that modify tables in external sources.

2. Data is read in a single stream via the master host, i.e., the coordinator host of a Greenplum® cluster. Currently, massive parallel reading is not supported.
3. YQ uses the Yandex Managed Service for YDB type system. However, the ranges of acceptable values for types used in YDB for date and time operations (Date, Datetime, and Timestamp) often turn out to be insufficiently wide to cover the values of the relevant Greenplum® types (date and timestamp).
   Therefore, YQ returns date and time values read from Greenplum® as plain strings (Optional<Utf8> type) in ISO-8601 format.

Filter pushdownFilter pushdown

Yandex Query can transfer processing of request parts to the data source system. This means that filter expressions are provided through Yandex Query directly to the database for processing; these are typically the query conditions specified in WHERE. This kind of processing is called filter pushdown.

Filter pushdown is possible when using:

Other filter types do not support source pushdown: the external table rows will be filtered on the federated Yandex Query side, i.e., Yandex Query will perform a full scan of the external table when processing the query.

Supported data types for filter pushdown:

Supported data typesSupported data types

In a Greenplum® DB, the optionality of column values (whether or not the column can contain NULL values) does not depend on the type system. The NOT NULL constraint for each column is implemented by means of the attnotnull attribute in the pg_attribute system folder, i.e., at the table metadata level. Thus, by default, all Greenplum® base types can contain NULL values and the YQ type system will represent them as optional types.

The table below shows how Greenplum® and Yandex Query types map. All other data types except those listed are not supported.