Connecting to a PostgreSQL cluster from DataLens
Yandex DataLens is a business intelligence service, that lets you upload, process and visualize data for analysing and testing hypotheses. It can help you make marketing decisions for your product.
To use an existing cluster as a data source for Yandex DataLens:
- Activate DataLens.
- Enable access to the cluster from DataLens.
- Create a cluster connection in DataLens.
Activating DataLens
Limit
You can only create one DataLens instance in the cloud.
- In the management console, go to the folder page.
- Go to DataLens.
- Click Activate DataLens.
The DataLens instance will be activated in the selected folder.
Enabling DataLens access to the cluster
To allow DataLens to connect to your cluster and work with its data, enable DataLens access when creating the cluster or updating its settings.
Creating a cluster connection
-
Make sure DataLens is activated in the relevant folder and has permission to access the cluster.
-
Go to Managed Service for PostgreSQL.
-
Click the relevant cluster’s name and open the DataLens tab.
-
Click Create connection.
I have no Create connection button
If you see the DataLens not activated message instead of the Create connection button, you have either not yet activated DataLens or activated it in a different folder. To connect your cluster to a DataLens instance from another folder, follow this DataLens guide.
-
Check that the settings are correct by clicking Check connection.
-
After a successful connection test, click Create connection.
-
In the window that opens, specify the connection name and click Create.
After creating the connection, you can:
- Update or delete this connection.
- Use this connection to create datasets from the cluster data.
- Visualize the datasets using charts and dashboards.
Viewing a list of cluster connections
-
Go to Managed Service for PostgreSQL.
-
Click the name of your cluster and open the DataLens tab.
Updating a cluster connection
Deleting a cluster connection
-
Go to Managed Service for PostgreSQL.
-
Click the name of your cluster and open the DataLens tab.
-
Select the connection from the list.
-
Click at the top of the page and select Delete.
-
In the window that opens, click Delete.
Connection settings
-
If your cluster and DataLens are in the same organization:
-
Navigate to the Select in organization tab.
-
Select the cloud and folder where your cluster is located.
-
Select the cluster and its host from the drop-down lists or create a new cluster in the folder with DataLens by clicking Create new.
-
Leave the Port setting unchanged.
-
Select Path to database from the drop-down list.
-
Select the user from the drop-down list.
-
Enter this user's password.
-
Specify the cache TTL or leave the default value. The recommended value is 300 seconds (5 minutes).
-
Configure the Raw SQL level.
-
To explicitly specify collation for your database queries, navigate to Advanced connection settings. Select a Query-level collation mode:
- Auto: Applies the default setting. The decision to enable the
en_USlocale is delegated to DataLens. - On: Applies the DataLens setting. The
en_USlocale is specified for individual expressions within a query. This ensures the server uses the appropriate sorting logic, regardless of its settings or the specific tables involved. Use the DataLens setting if your database locale is incompatible with DataLens. For more information about the locale and theLC_COLLATEsetting, see Creating a cluster. - Off: Applies the default setting. DataLens uses database-level locale settings.
- Auto: Applies the default setting. The decision to enable the
-
-
If the cluster and DataLens belong to different organizations, apply the configuration from this DataLens tutorial.