Creating a connection to PostgreSQL in Yandex DataLens

To create a PostgreSQL connection:

1. Open the new connection creation page.

2. Under Databases, select the PostgreSQL connection.

   

3. Select the connection type:

   Select in organization

   Specify manually

   Connection Manager

   Select a managed database in the current Yandex Cloud organization and specify the internal network connection parameters for it:

   • Cloud and folder: Select the folder the cluster is located in.

   • Cluster: Specify the cluster from the list of available PostgreSQL clusters. Cluster settings must have the DataLens access option enabled. If you do not have an available cluster, click Create new.

     Note

     The list shows the following clusters:

     • With permissions for the user creating the connection.
     • Created in the same organization as the DataLens instance.

   • Host type: Select the host type:

     • Regular (default): Allows you to select regular hosts to connect to.
     • Special FQDNs: Enables you to select special FQDNs for connection, which always point to the current master host or to its closest replica in the PostgreSQL cluster.

   • Host name: Select the host name from the list of hosts available in the PostgreSQL cluster. You can select multiple hosts. If you fail to connect to the first host, DataLens will select the next one from the list.

   • Port: Specify the PostgreSQL connection port. In Yandex Cloud, the default port is 6432.

   • Path to database: Specify the name of the database to connect to.

   • Username: Specify the username for the PostgreSQL connection.

   • Password: Enter the password for the user.

   • Cache TTL in seconds: Specify the cache TTL or leave the default value. The recommended value is 300 seconds (5 minutes).

   • Raw SQL level: Enables you to use an ad-hoc SQL query to generate a dataset. This option is disabled by default. When enabling it, you will need to select the raw SQL level:

     • Allow subqueries in datasets: Describe dataset sources using SQL queries.
     • Allow subqueries in datasets and source parameterization: Describe dataset sources using SQL queries and use source parameterization.
     • Allow subqueries in datasets, source parameterization, and QL charts: Describe dataset sources using SQL queries, use source parameterization, and create QL charts.

   Click Check connection to make sure the parameters are correct.

   

   Manually specify your external database details to connect to via a public network:

   • Host name: Specify the path to a master host or a PostgreSQL master host IP address. You can specify multiple hosts in a comma-separated list. If you fail to connect to the first host, DataLens will select the next one from the list.
   • Port: Specify the PostgreSQL connection port. In Yandex Cloud, the default port is 6432.
   • Path to database: Specify the name of the database to connect to.
   • Username: Specify the username for the PostgreSQL connection.
   • Password: Enter the password for the user.
   • Cache TTL in seconds: Specify the cache TTL or leave the default value. The recommended value is 300 seconds (5 minutes).

   • Raw SQL level: Enables you to use an ad-hoc SQL query to generate a dataset. This option is disabled by default. When enabling it, you will need to select the raw SQL level:

     • Allow subqueries in datasets: Describe dataset sources using SQL queries.
     • Allow subqueries in datasets and source parameterization: Describe dataset sources using SQL queries and use source parameterization.
     • Allow subqueries in datasets, source parameterization, and QL charts: Describe dataset sources using SQL queries, use source parameterization, and create QL charts.

   Click Check connection to make sure the parameters are correct.

   

   Note

   To use a Connection Manager connection in DataLens, the user must have the connection-manager.user role for this connection.

   Select the connection to a PostgreSQL managed database cluster created in Yandex Connection Manager:

   • Cloud and folder: Select the folder where you created the connection to the cluster.
   • Connection ID: Select an available connection in Connection Manager or create a new one.
   • Host: Select the host from the list of available hosts in the PostgreSQL cluster.
   • Port: It is set automatically depending on the selected host.
   • Database: Specify the name of the database to connect.
   • Username: It is set automatically from the selected connection data.
   • Cache TTL in seconds: Specify the cache TTL or leave the default value. The recommended value is 300 seconds (5 minutes).

   • Raw SQL level: Enables you to use an ad-hoc SQL query to generate a dataset. This option is disabled by default. When enabling it, you will need to select the raw SQL level:

     • Allow subqueries in datasets: Describe dataset sources using SQL queries.
     • Allow subqueries in datasets and source parameterization: Describe dataset sources using SQL queries and use source parameterization.
     • Allow subqueries in datasets, source parameterization, and QL charts: Describe dataset sources using SQL queries, use source parameterization, and create QL charts.

   

4. Click Create connection.

5. In the window that opens:

   1. Select the workbook to save your connection to or create a new one. If using legacy folder navigation, select a folder to save the connection to.

   2. Enter the connection name.

   3. Click Create.

   

Additional settingsAdditional settings

You can specify additional connection settings under Advanced connection settings:

• Setting collate in a query: To explicitly define a collation for database queries, select a mode:

  • Auto: Applies the default setting. DataLens decides whether to enable the en_US locale.
  • On: Applies the DataLens setting. The en_US locale is specified for individual expressions within a query. Thus the server uses the appropriate sorting logic, regardless of the server settings and specific tables. Use the DataLens setting if your database locale is incompatible with DataLens. For more information on the locale and LC_COLLATE, see Creating a cluster.
  • Off: Applies the default setting. DataLens only uses database-level locale settings.

• TLS: Indicates whether TLS is required. When the option is enabled, the sslmode parameter is set to required. When the option is disabled, the parameter is set to prefer.

• CA Certificate: To upload a certificate, click Attach file and select the certificate file. When the certificate is uploaded, the field shows the file name.

• Disable data export: When this option is on, the data export item will not be available in the menu for the charts based on this connection. However, you will still be able to copy chart data and take screenshots.