Create a connection to ClickHouse®

Follow these steps to create a connection to ClickHouse®:

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 ClickHouse®. You'll see the following page:
Specify the connection parameters:
- Hostname. Copy the host's name from the Hosts tab on the information page of your cluster.
- HTTP Interface Port (by default, 8443).
- Username and Password for your cluster. You can find them in the Overview tab on your cluster's information page.
- Cache TTL. Specifies how long to store cache (in seconds). 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.
- HTTPS if you need this security layer (enabled by default).
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 ClickhouseConnection 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 (8443 by default).
username - the ClickHouse® cluster's username.
secure - a boolean to enable (true) or disable using a secure connection.

API example

kind: clickhouse
raw_sql_level: subselect
cache_ttl_sec: null
host: <your_ClickHouse_host>
port: 443
username: user
secure: true

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": "clickhouse",
         "raw_sql_level": "off",
         "cache_ttl_sec": 600,
         "host": "<your_clickhouse_host>",
         "port": 443,
         "username": "<your_clickhouse_user_name>",
         "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_clickhouse_password>")), # the authorization secret
      )
   )
   return operation

Create a connection to ClickHouse®

See also