Create a connection to ClickHouse®

Follow these steps to create a connection to ClickHouse®:

  1. Open the Visualization page in the console.

  2. Select your workbook or click Create workbook to make a new one.

  3. Click CreateConnection in the upper-right corner of your workbook page.

  4. Select ClickHouse®. You'll see the following page:

    clickhouse-connection-interface

  5. 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).

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

  7. Click Create connection.

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

See also

Previous
Next