Connect to external Clickhouse database#

Connect to Clickhouse Cloud#

Aspects can be connected with Clickhouse Cloud following the steps below:

  1. Disable the Clickhouse service:

RUN_CLICKHOUSE: false

  1. Create a Clickhouse Cloud account and a cluster, and get the credentials.

  2. Once you get the credentials, set the following variables in your config.yaml file:

CLICKHOUSE_HOST: <clickhouse_host>
CLICKHOUSE_SECURE_CONNECTION: true
CLICKHOUSE_ADMIN_USER: <clickhouse_admin_user>
CLICKHOUSE_ADMIN_PASSWORD: <clickhouse_admin_password>

  1. Apply the changes by running the following command:

tutor config save

  1. Depending on how you have configured your ClickHouse user, you may need to bootstrap the default user with permissions required to run the Aspects init. The current permissions required to successfully init are listed below, but please note that you may need to replace the users and databases based on your configuration:

GRANT CREATE USER, ALTER USER, CREATE FUNCTION, DROP FUNCTION, CREATE DATABASE, CREATE TEMPORARY TABLE, S3
    ON *.* to ch_admin;

CREATE DATABASE IF NOT EXISTS xapi;
GRANT ALTER UPDATE, ALTER COLUMN, DROP COLUMN, RENAME COLUMN, ALTER RENAME COLUMN, CREATE DICTIONARY,
      DROP DICTIONARY, DROP TABLE, CREATE TABLE, CREATE VIEW, DROP VIEW, SELECT, INSERT, DELETE, OPTIMIZE,
      dictGet
  ON xapi.* to ch_admin WITH GRANT OPTION;

CREATE DATABASE IF NOT EXISTS openedx;
GRANT ALTER UPDATE, ALTER COLUMN, DROP COLUMN, RENAME COLUMN, ALTER RENAME COLUMN, CREATE DICTIONARY,
      DROP DICTIONARY, DROP TABLE, CREATE TABLE, CREATE VIEW, DROP VIEW, SELECT, INSERT, DELETE, OPTIMIZE,
      dictGet
  ON openedx.* to ch_admin WITH GRANT OPTION;

CREATE DATABASE IF NOT EXISTS event_sink;
GRANT ALTER UPDATE, ALTER COLUMN, DROP COLUMN, RENAME COLUMN, ALTER RENAME COLUMN, CREATE DICTIONARY,
      DROP DICTIONARY, DROP TABLE, CREATE TABLE, CREATE VIEW, DROP VIEW, SELECT, INSERT, DELETE, OPTIMIZE,
      dictGet
  ON event_sink.* to ch_admin WITH GRANT OPTION;

CREATE DATABASE IF NOT EXISTS reporting;
GRANT ALTER UPDATE, ALTER COLUMN, DROP COLUMN, RENAME COLUMN, ALTER RENAME COLUMN, CREATE DICTIONARY,
      DROP DICTIONARY, DROP TABLE, CREATE TABLE, CREATE VIEW, DROP VIEW, SELECT, INSERT, DELETE, OPTIMIZE,
      dictGet
  ON reporting.* to ch_admin WITH GRANT OPTION;

-- These are used for the ClickHouse status reports in the operator dashboard
GRANT SELECT ON system.asynchronous_metrics TO ch_admin WITH GRANT OPTION;
GRANT SELECT ON system.disks TO ch_admin WITH GRANT OPTION;
GRANT SELECT ON system.events TO ch_admin WITH GRANT OPTION;
GRANT SELECT ON system.metrics TO ch_admin WITH GRANT OPTION;
GRANT SELECT ON system.replication_queue TO ch_admin WITH GRANT OPTION;

  1. Restart your local or production environment. After this change, you need to run the initialization tasks. To do so, run the following command according to your environment:

tutor [local|dev|k8s] do init -l aspects

Connect to on Premise Clickhouse#

Aspects provide several configuration parameters that can be customized to connect to an on premise Clickhouse instance. To do so, set the following variables in your config.yaml file:

CLICKHOUSE_HOST: <clickhouse_host>
CLICKHOUSE_SECURE_CONNECTION: true|false
CLICKHOUSE_ADMIN_USER: <clickhouse_admin_user>
CLICKHOUSE_ADMIN_PASSWORD: <clickhouse_admin_password>
CLICKHOUSE_HOST_HTTP_PORT: <clickhouse_http_port> # defaults to 8123 or 8443 for secure TLS connections
CLICKHOUSE_HOST_NATIVE_PORT: <clickhouse_client_port> # default to 9000 | 9440 used by initialization tasks

Additionally, there are some other variables that are calculated based on the above variables, but can be customized if needed:

CLICKHOUSE_REPORT_SQLALCHEMY_URI: <clickhouse_url> # used by superset to perform queries
CLICKHOUSE_ADMIN_SQLALCHEMY_URI: <clickhouse_url> # used by initialization tasks to create tables and views