Create a connection to PostgreSQL

Follow these steps to create a connection to PostgreSQL:

Console

API

Python

Open the Visualization page in the console.
Select your workbook or click Create workbook to make a new one.
Click Create → Connection in the upper-right corner of your workbook page.
Select PostgreSQL. You'll see the following page:
Specify the connection parameters:
- Hostname. IP address or the domain name (FQDN) of your host.
- Port (by default, 6432).
- Username and Password for your database.
- Path to database. Provide the path to your PostgreSQL database within the specified host. Usually, it's the database name.
- Cache TTL. Specifies how long to store cache. You can keep the Default value or select Customized to set this value manually.
- Raw SQL level:
  - Forbid is the default setting. It handles all SQL queries automatically.
  - Allow subselects in datasets gives access to manual operations with SQL queries and subqueries. Use with caution and only if necessary.
  - Allow subselects in datasets and queries from charts allows you to use your connection to create a QL chart.
Under Advanced connection settings → Specifying collate in the request:
- Auto (default): let Visualization decide whether to enable the en_US locale.
- On: enable the en_US locale for individual expressions in a query. This makes the server use the appropriate sorting logic, regardless of the server settings and specific tables.
  
  Tip
  
  Use this setting if the database locale causes compatibility issues with DoubleCloud Visualization.
- Off: Visualization only uses the existing parameters of the database locale.
Click Check connection:
- If your connection is operational, you'll see a green icon.
- If there is a problem, a red icon will appear. You'll also see a popup message with a Details button to check service information about the error.
Click Create connection.
Name your connection and click Create.

Note

This configuration is a segment in the overall description of Visualization assets in the workbook. For the complete tutorial, see Use Visualization API.

Use the Connection API model and specify the PostgresConnection configuration:

kind: clickhouse - specify the connection type.
raw_sql_level - set the lowest level for an SQL database interaction. This is an enum with three options: off, subselect and dashsql.
cache_ttl_sec - (optional) specify how long to store cache (in seconds).
host - this is the name of the ClickHouse® to which you want to connect.
port - this is the host's interface port (443 by default for API calls)
username - the ClickHouse® cluster's username
secure - a boolean to enable (true) or disable using a secure connection.

API example

 kind: postgres
 raw_sql_level: off
 cache_ttl_sec: null
 host: <your_postgres_host>
 port: 443
 username: user
 database_name: <your_db_name>

Note

This configuration is a segment in the overall description of Visualization assets in the workbook. For the complete tutorial, see Use Visualization API.

"""
[!!!] Don’t work with secrets like that for your environemnts.

It's just an example. Ask your system administator for your secrets provider.
"""

import json
import logging

from google.protobuf.wrappers_pb2 import BoolValue

import doublecloud

from doublecloud.v1.operation_pb2 import Operation
from doublecloud.visualization.v1.workbook_pb2 import Connection
from doublecloud.visualization.v1.workbook_service_pb2 import CreateWorkbookConnectionRequest
from doublecloud.visualization.v1.workbook_service_pb2_grpc import WorkbookServiceStub

def create_workbook_connection(svc, workbook_id: str) -> Operation:
   connection = Connection()
   connection.config.struct_value.update(
      {
         "kind": "postgres",
         "raw_sql_level": "off",
         "cache_ttl_sec": 600,
         "host": "<your_postgres_host>",
         "port": 443,
         "username": "<your_postgres_user_name>",
         "database": "<your_postgres_database>
         "secure": True,
      }
   )

   operation = svc.CreateConnection(
      CreateWorkbookConnectionRequest(
         workbook_id=workbook_id, # this specifies the workbook which will contain this connection as an asset.
         connection_name="<connection_name>", # the name of the connection to be dispalyed
         connection=connection,
         secret=Secret(plain_secret=PlainSecret(secret="<your_postgres_password>")), # the authorization secret
      )
   )
   return operation

Create a connection to PostgreSQL

See also