Working with Yandex MPP Analytics for PostgreSQL databases

This section covers the basics of working with Yandex MPP Analytics for PostgreSQL.

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

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

Query example for reading data from Yandex MPP Analytics for PostgreSQL:

SELECT * FROM greenplum_mdb_connection.my_table

Where:

• greenplum_mdb_connection: Your database connection name.
• my_table: Database table name.

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. Navigate to Yandex Query.

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

4. Click Create new.

5. Specify the connection parameters:

   1. Under General parameters:

      • Name: Yandex MPP Analytics for PostgreSQL connection name.
      • 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. Assign it the managed-greenplum.viewer role allowing 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 you will use when working with the Greenplum® database.

      • Login: Username you will use to connect to Greenplum® databases.

      • Password: Password you will use to connect to Greenplum® databases.

6. Click Create.

A service account is necessary to detect Yandex MPP Analytics for PostgreSQL cluster connection endpoints inside Yandex Cloud. To access data, you need a separate username and password.

Query syntaxQuery syntax

Greenplum® uses the following SQL syntax:

SELECT * FROM <connection>.<table_name>

Where:

• <connection>: Your database connection name.
• <table_name>: Database table name.

LimitsLimits

Working with Greenplum® clusters comes with certain limitations.

The following limitations apply:

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 reads are performed in a single thread via the master host (coordinator host) of the Greenplum® cluster. Massively parallel reads are not currently supported.
3. YQ uses the Yandex Managed Service for YDB type system. However, the valid value ranges for YDB date and time types, i.e., Date, Datetime, and Timestamp, are often too narrow to accommodate the values of the corresponding Greenplum® types, i.e., date and timestamp.
   Therefore, YQ returns date and time values read from Greenplum® as plain strings (Optional<Utf8>) 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 Greenplum® databases, the column nullability flag, i.e., whether or not the column can contain NULL values, is not part of the type system. The NOT NULL constraint for each column is implemented via an attnotnull attribute in the pg_attribute system catalog, i.e., at the table metadata level. Therefore, all Greenplum® base types may contain NULL values by default, and within the YQ type system they must be mapped to optional types.

The table below shows the mapping of Greenplum® types to Yandex Query types. Only the listed types are supported.