Contents x
    ClickHouse
    • 3 Minutes to read
    • Dark
      Light
    Contents

    ClickHouse

    • 3 Minutes to read
    • Dark
      Light
    Article summary

    ClickHouse is an open-source columnar database management system designed for high-performance analytics processing. It excels at handling large volumes of data and executing complex analytical queries in real-time, making it popular for applications that require fast data retrieval and analysis.

    Prerequisites

    • You have a running ClickHouse instance with public IP or hostname.
    • You have configured a ClickHouse user with CREATE, SELECT, UPDATE, INSERT, DELETE permissions.
    • You have configured your network firewall to allow incoming connection from Dataddo IPs.

    Authorize Connection to ClickHouse

    In ClickHouse

    Please keep in mind that this guide provides general instructions, and your individual setup might need a different approach or some extra steps. Always take your organization's security guidelines into account when you're making these changes. Additionally, it's a good idea to seek assistance from a database administrator or an IT professional to make sure everything is set up correctly.

    Create a ClickHouse User

    1. In your ClickHouse client, run a command to create a new user, e.g.
      CREATE USER dataddo IDENTIFIED WITH plaintext_password BY '***';
    2. Run a command to grant permissions to the newly created user.
      GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON *.* TO dataddo;

    Allow Network Connection

    To enable incoming connections, it's important to ensure that the ClickHouse server is properly configured. By default, Clickhouse listens on port 8123 for HTTP connections and on port 9000 for native connections.

    To avoid any potential connection issues, review the listen_host parameter in the ClickHouse server configuration file (usually found at /etc/clickhouse-server/config.xml). The parameter should be set as shown in the example below."

    <yandex>
    <!-- Listen wildcard address (default) -->
    <listen_host>::</listen_host>
</yandex>

    Besides ensuring that the Clickhouse server accepts all incoming connections, it is important to set the network as well.

    For this destination, whitelisting IP addresses is not available in the database system. Instead, you can configure the network access at the firewall level. Here are the general steps:

    1. Identify the firewall that's protecting your service. This could be a Windows or Linux firewall running on the server itself, or it could be a network firewall.
    2. Open the firewall's configuration settings. This process will vary depending on the type of your firewall.
    3. Look for the setting that allows you to define inbound rules or policies.
    4. Create a new rule or policy that allows inbound traffic on the port that the service is using (the default is 9000, 8123 respectively) from the Dataddo IP addresses.
    5. Save your changes and test the connection from Dataddo to make sure everything is working as intended.

    In Dataddo

    1. In the Authorizer tab, click on Authorize New Service and select Clickhouse.
    2. You will be asked to fill the following fields.
      1. Server IP or Hostname: Public IP or Hostname of your Clickhouse server.
      2. Database: Name of the database you will use for writing or reading the data.
      3. Username: Username for your Clickhouse user.
      4. Password: Password associated with the Clickhouse username.
      5. Port: Dataddo uses native connection with default port 9000.
      6. TLS/SSL Settings: Configuration of TLS connection. Unless having specific requirements, keep on default value.
      7. Client Certificate: Additional CA certificate for establishing the TLS connection.
    3. [Optional] Configure the connection via SSH tunnel.
    4. After filling-in all the details, click on Save to save the authorization details.

    Create a New Clickhouse Destination

    1. On the Destinations page, click on the Create Destination button and select the destination from the list.
    2. Select your authorizer from the drop-down menu.
    3. Name your destination and click on Save.
    Need to authorize another connection?

    Click on Add new Account in drop-down menu during authorizer selection and follow the on-screen prompts. You can also go to the Authorizers tab and click on Add New Service.

    Create a Flow to Clickhouse

    1. Navigate to Flows and click on Create Flow.
    2. Click on Connect Your Data to add your source(s).
    3. Click on Connect Your Data Destination to add the destination.
    4. Choose the write mode and fill in the other required information.
    5. Check the Data Preview to see if your configuration is correct.
    6. Name your flow and click on Create Flow to finish the setup.

    Was this article helpful?
    What's Next