[Yandex Cloud documentation](../../index.md) > [Yandex Query](../index.md) > Data sources and sinks > Working with Yandex MPP Analytics for PostgreSQL databases

# Working with Yandex MPP Analytics for PostgreSQL databases

This section covers the basics of working with [Yandex MPP Analytics for PostgreSQL](https://yandex.cloud/ru/services/managed-greenplum).

To start working with a Yandex MPP Analytics for PostgreSQL database, follow these steps:
1. Create a [connection](../concepts/glossary.md#connection) containing your database access credentials.
1. [Run a query](#query) against the database.

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

```sql
SELECT * FROM greenplum_mdb_connection.my_table
```

Where:
* `greenplum_mdb_connection`: Your database connection name.
* `my_table`: Database table name.


## Setting up a connection {#create_connection}

To create a connection to Yandex MPP Analytics for PostgreSQL:
1. In the [management console](https://console.yandex.cloud), select the folder where you want to create a connection.
1. Navigate to **Yandex Query**.
1. In the left-hand panel, switch to the **Connections** tab.
1. Click ![info](../../_assets/console-icons/plus.svg) **Create new**.
1. Specify the connection settings:

   1. Under **General parameters**:

      * **Name**: Yandex MPP Analytics for PostgreSQL connection name.
      * **Type**: `Managed Service for Greenplum`.

   1. 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](../../iam/concepts/users/service-accounts.md) or create a new one. Assign it the [`managed-greenplum.viewer`](../../managed-greenplum/security/index.md#mgp-viewer) role allowing it to connect to `Yandex MPP Analytics for PostgreSQL` clusters.

        To use the service account on your behalf, you need the `iam.serviceAccounts.user` [role](../../iam/security/index.md#iam-serviceAccounts-user).

      * **Database**: Select the database you will use when working with the Greenplum® cluster.
      * **Schema**: Specify the [namespace](https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/admin_guide-ddl-ddl-schema.html) 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.


1. 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.

{% note warning %}

First, grant network access from Yandex Query to Yandex MPP Analytics for PostgreSQL clusters. To do this, enable **Access from Yandex Query** in your target database settings.

{% endnote %}

## Query syntax {#query}
Greenplum® uses the following SQL syntax:

```sql
SELECT * FROM <connection>.<table_name>
```

Where:
* `<connection>`: Your database connection name.
* `<table_name>`: Database table name.

## Limits {#limits}

Working with Greenplum® clusters comes with certain limitations.

The following limitations apply:
1. External sources are available for read-only access via `SELECT` queries. Yandex Query does not currently support data-modifying queries against external sources.
1. Data reads are performed in a single thread via the master host ([coordinator host](https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/admin_guide-intro-arch_overview.html)) of the Greenplum® cluster. Massively parallel reads are not currently supported.
1. YQ uses the Yandex Managed Service for YDB [type system](https://ydb.tech/docs/en//yql/reference/types/primitive). 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](https://www.iso.org/iso-8601-date-and-time-format.html) format.

## Filter pushdown {#predicate_pushdown}

Yandex Query can push parts of query processing down to the source data system by sending filter expressions, e.g., `WHERE` conditions, directly to the database. This approach is known as `filter pushdown`.

Filter pushdown is possible when using:

|Description|Example|
|---|---|
|`NULL` check|`WHERE column1 IS NULL` or `WHERE column1 IS NOT NULL`|
|Logical operators `AND`, `OR`, `NOT`, and parentheses to control operator precedence |`WHERE column1 IS NULL OR (column2 IS NOT NULL AND column3 > 10)`.|
|Comparison operators `=`, `==`, `!=`, `<>`, `>`, `<`, `>=`, and `<=` that compare a column with other columns or constants|`WHERE column1 > column2 OR column3 <= 10`, `WHERE column1 + column2 > 10`, `WHERE column1 = (10 + 10)`|

Other filter types do not support source pushdown: the external table rows are 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:

|Yandex Query data type|
|----|
|`Bool`|
|`Int8`|
|`Int16`|
|`Int32`|
|`Int64`|
|`Float`|
|`Double`|

## Supported data types {#supported_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](https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/ref_guide-system_catalogs-pg_attribute.html) 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](https://ydb.tech/docs/en//yql/reference/types/optional) types. 

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

| Greenplum® data type | Yandex Query data type | Notes |
| :---: | :---: | :--- |
| `boolean` | `Optional<Bool>` | |
| `smallint` | `Optional<Int16>` | |
| `int2` | `Optional<Int16>` | |
| `integer` | `Optional<Int32>` | |
| `int` | `Optional<Int32>` | |
| `int4` | `Optional<Int32>` | |
| `serial` | `Optional<Int32>` | |
| `serial4` | `Optional<Int32>` | |
| `bigint` | `Optional<Int64>` | |
| `int8` | `Optional<Int64>` | |
| `bigserial` | `Optional<Int64>` | |
| `serial8` | `Optional<Int64>` | |
| `real` | `Optional<Float>` | |
| `float4` | `Optional<Float>` | |
| `double precision` | `Optional<Double>` | |
| `float8` | `Optional<Double>` | |
| `date` | `Optional<Utf8>` | |
| `timestamp` | `Optional<Utf8>` | |
| `bytea` | `Optional<String>` | |
| `character` | `Optional<Utf8>` | Default collation rules apply. The string is padded with spaces to the required length. |
| `character varying` | `Optional<Utf8>` | Default collation rules apply. |
| `text` | `Optional<Utf8>` | Default collation rules apply. |
| `json` | `Optional<Json>` | |