alexeysetevoi / ansible-clickhouse Goto Github PK

View Code? Open in Web Editor NEW

160.0 160.0 96.0 177 KB

ansible role for clickhouse

Jinja 100.00%

ansible-clickhouse's People

Contributors

Stargazers

Watchers

Forkers

agalitsyn ggudavalli levonet hanks110 amarao krzyzakp mediapeers asabirov wedi-dev madiedinro sportsru dregor 0xqq daveperrett ashwets 14df397fca rnz arruzk free6k lonsofore sturlik dmytroboiko cimon-io sryazantsev dkew8 webboyvc e11it vkhodor daltinn tinybirdco fierybeard dddeeemmm ev-grebenyuk bksub zhwei820 sahilm vladget renjithraju flydreamwangping jzombi mixergit brektyme sancha18 rualexeyusman t-navi chyy br3d h0rny antonfriberg oleg-soroka-lt elli56 spheron1 hamidreza-hosseini vlourme panmonsters amixp sergeykosmovsky unencoded aberes shashankkoppar pykupejibc rowhe dimsunv kpehki 0gima amitroshchenko hruljov echastukhin kolesnevg scispike stephan242 1nxs mingaliev-e askarpoff namorninayzuk efanru vortura ted-vo vitaliysid ryzhakovks seleznev-ivan owirtifo eouru zatulik2606 day8 demoniumblack alexeyd3 maxooooon jwafle fairglen and-shalamov aleksgrayman

ansible-clickhouse's Issues

Idea/Feature Request - Refactor role with basic settings + free-form XML config

Hi!

This is just an idea that I would want to discuss with the maintainer and community of this project. I have written a couple of pull-requests due to needs that I have in my production environment that was not exposed in this role. I have noticed that it is quite cumbersome for this role to maintain all the small configuration options that ClickHouse allows and at the same time I feel limited if I am not able to do the changes I would like.

It would not be that difficult to simply deploy my own XML config files next to this roles generated files but that results in added complexity due to having configuration in multiple places. So instead I suggest a different approach.

Let this role handle basic configuration that pretty much everyone want to change but allow additional configuration that results in separate XML config files. Since the free-flow config options are just multi-line strings we can utilize ansible templating as normal which is very powerful.

Is this something that could be valuable for this role? Do you want me to provide a PR? Let us discuss!

It would look something like this in an Ansible Inventory file (yaml in my case).

---
all:
  hosts: # Host aliases
    clickhouse[01:04].domain.com:
      ansible_port: 22
      ansible_user: "clickhouse"
    clickhouse01:
      ansible_host: "clickhouse01.domain.com"
      # node specific variables
      clickhouse_macros: # Node specific variables
        cluster: "cluster-name"
        shard: "01"
        replica: "clickhouse01"
    clickhouse02:
      ansible_host: "clickhouse02.domain.com"
      clickhouse_macros:
        cluster: "cluster-name"
        shard: "01"
        replica: "clickhouse02"
    clickhouse03:
      ansible_host: "clickhouse03.domain.com"
      clickhouse_macros:
        cluster: "cluster-name"
        shard: "02"
        replica: "clickhouse03"
    clickhouse04:
      ansible_host: "clickhouse04.domain.com"
      clickhouse_macros:
        cluster: "cluster-name"
        shard: "02"
        replica: "clickhouse04"

  vars: # Variables for all groups

    # Very important config is on first level as expected
    clickhouse_version: "22.2.2.1"
    clickhouse_http_port: 8123
    clickhouse_tcp_port: 9000
    clickhouse_interserver_http: 9009
    clickhouse_listen_host:
      - "::"

    # Stable configuration options that most users want is also available
    clickhouse_config:
      max_connections: 4096
      keep_alive_timeout: 3
      max_concurrent_queries: 100
      uncompressed_cache_size: 8589934592
      mark_cache_size: 5368709120
      builtin_dictionaries_reload_interval: 3600
      dictionaries_lazy_load: true
      max_session_timeout: 3600
      default_session_timeout: 60
      default_profile: "default"
      default_database: "some_database"

    # Expose ability to define free-form configuration
    # These will be added as files under config.d/
    clickhouse_custom_config:
      - file_name: "clickhouse_ldap_servers.xml"
        content: |
          <clickhouse>
              <ldap_servers>
                  <company_ldap>
                      <host>ldap.company.com</host>
                      <port>636</port>
                      <bind_dn>COMPANYDOMAIN\\{user_name}</bind_dn>
                      <enable_tls>yes</enable_tls>
                      <user_dn_detection>
                          <base>OU=Company,DC=company,DC=com</base>
                          <search_filter>(&amp;(objectClass=user)(sAMAccountName={user_name}))</search_filter>
                      </user_dn_detection>
                  </company_ldap>
              </ldap_servers>
          </clickhouse>
      - file_name: "clickhouse_s3_endpoints.xml"
        content: |
          <clickhouse>
            <s3>
                <aws-datalake>
                    <endpoint>https://bucket-name.s3.us-east-1.amazonaws.com</endpoint>
                    <access_key_id>some_id_string</access_key_id>
                    <secret_access_key>some_secret_string</secret_access_key>
                    <region>us-east-1</region>
                </aws-datalake>
            </s3>
          </clickhouse>

dirmngr is needed for debian stretch

I had an error upon installation, which was then fixed by manually installing dirmngr (apt-get install dirmngr) on Debian stretch before running the clickhouse-installation playbook. I don't know ansible well enough to create a PR, so maybe my issue will help other people.

Ch change the repo address,need change to role

Alexey Milovidov, [13.03.18 22:30]
[In reply to Александр Ярославцев]
Только что изменили адрес репозитория.
Теперь
sudo apt-add-repository "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/"

applying SSL to clickhouse : certificate verify failed

Hello,
I tried to apply ssl to clickhouse by using commands provided in config.xml (commented) and I got the server.key and server.crt file and then my expectation is my clickhouse-client should be able to connect to clickhouse-server securely, but I got this error
Code: 210. DB::NetException: SSL Exception: error:14000086:SSL routines::certificate verify failed

Then, I have successfully connected to the clickhouse-server though by un-commenting <name>AcceptCertificateHandler</name> but it's not practical. is there more elegant way using this role to config it in playbook ? thank you

Failed to validate GPG signature

Please, add gpgcheck variable

Add access_management configuration for clickhouse_users_custom

During development, it would be nice to be able to set up a user who has the ability to do access management using SQL in ClickHouse.

https://clickhouse.com/docs/en/operations/access-rights/#enabling-access-control

I can provide a PR.

Playbook in readme is not valid

Playbook is invalid

  - hosts: clickhouse_cluster
    remote_user: root
    vars:
      clickhouse_users_custom:
          - { name: "testuser",
              password_sha256_hex: "f2ca1bb6c7e907d06dafe4687e579fce76b37e4e93b7605022da52e6ccc26fd2",
              networks: "{{ clickhouse_networks_default }}",
              profile: "default",
              quota: "default",
              dbs: [ testu1 ] ,
              comment: "classic user with plain password"}
          - { name: "testuser2",
              password: "testplpassword",
              networks: "{{ clickhouse_networks_default }}",
              profile: "default",
              quota: "default",
              dbs: [ testu2 ] ,
              comment: "classic user with hex password"}
          - { name: "testuser3",
              password: "testplpassword",
              networks: { 192.168.0.0/24, 10.0.0.0/8 },
              profile: "default",
              quota: "default",
              dbs: [ testu1,testu2,testu3 ] ,
              comment: "classic user with multi dbs and multi-custom network allow password"}
      clickhouse_query_log_ttl: 'event_date + INTERVAL 7  DELETE'
      clickhouse_dicts:
          test1:
            name: test_dict
            odbc_source:
              connection_string: "DSN=testdb"
              source_table: "dict_source"
            lifetime:
              min: 300
              max: 360
            layout: hashed
            structure:
              key: "testIntKey"
              attributes:
                - { name: testAttrName, type: UInt32, null_value: 0 }
          test2:
            name: test_dict
            odbc_source:
              connection_string: "DSN=testdb"
              source_table: "dict_source"
            lifetime:
              min: 300
              max: 360
            layout: complex_key_hashed
            structure:
              key:
                attributes:
                  - { name: testAttrComplexName, type: String }
              attributes:
                - { name: testAttrName, type: String, null_value: "" }
      clickhouse_dbs_custom:
         - { name: testu1 }
         - { name: testu2, state:present }
         - { name: testu3, state:absent }
    clickhouse_clusters:
      your_cluster_name:
       shard_1:
          - { host: "db_host_1", port: 9000 }
          - { host: "db_host_2", port: 9000 }
       shard_2:
          - { host: "db_host_3", port: 9000 }
          - { host: "db_host_4", port: 9000 }        
    clickhouse_zookeeper_nodes:
      - { host: "zoo_host_1", port: 2181 }
      - { host: "zoo_host_2", port: 2181 }
      - { host: "zoo_host_3", port: 2181 }
    roles:
      - ansible-clickhouse

Can`t create distributed clickhouse cluster

Hello!
Can`t create distributed clickhouse cluster

install zookeeper cluster
inventory:

[zookeeper]
172.26.9.211 zookeeper_myid=1
172.26.9.212 zookeeper_myid=2
172.26.9.213 zookeeper_myid=3

[zookeeper-quorum]
host[1:3]

playbook

- hosts: zookeeper
  roles:
    - andrewrothstein.zookeeper-cluster

install clickhouse cluster
inventory:

[clickhouse_cluster]
172.26.9.211
172.26.9.212
172.26.9.213

playbook

- hosts: clickhouse_cluster
  become: yes
  vars:
    clickhouse_shards:
      your_shard_name:
        - { host: "172.26.9.211", port: 9000 }
        - { host: "172.26.9.212", port: 9000 }
        - { host: "172.26.9.213", port: 9000 }
    clickhouse_zookeeper_nodes:
      - { host: "172.26.9.211", port: 2181 }
      - { host: "172.26.9.212", port: 2181 }
      - { host: "172.26.9.213", port: 2181 }
  roles:
    - ansible-clickhouse

Config:

[root@nginx-log-collector-apatsev-2 ~]# cat /etc/clickhouse-server/config.xml |grep -A 10 remote
    <remote_servers incl="clickhouse_remote_servers" />


    <!-- If element has 'incl' attribute, then for it's value will be used corresponding substitution from another file.
         By default, path to file with substitutions is /etc/metrika.xml. It could be changed in config in 'include_from' element.
         Values for substitutions are specified in /yandex/name_of_substitution elements in that file.
      -->

    <!-- ZooKeeper is used to store metadata about replicas, when using Replicated tables.
         Optional. If you don't use replicated tables, you could omit that.

Missing <interval> block in user.j2

Below is the current version:

    {% for quota in clickhouse_quotas_default %}
    <{{ quota.name }}>
    {% for interval in quota.intervals %}
        <duration>{{ interval.duration }}</duration>
        <queries>{{ interval.queries }}</queries>
        <errors>{{ interval.errors }}</errors>
        <result_rows>{{ interval.result_rows }}</result_rows>
        <read_rows>{{ interval.read_rows }}</read_rows>
        <execution_time>{{ interval.execution_time }}</execution_time>
    {% endfor %}
    </{{ quota.name }}>
    {% endfor %}

It should be:

    {% for quota in clickhouse_quotas_default %}
    <{{ quota.name }}>
    {% for interval in quota.intervals %}
        <interval>
            <duration>{{ interval.duration }}</duration>
            <queries>{{ interval.queries }}</queries>
            <errors>{{ interval.errors }}</errors>
            <result_rows>{{ interval.result_rows }}</result_rows>
            <read_rows>{{ interval.read_rows }}</read_rows>
            <execution_time>{{ interval.execution_time }}</execution_time>
        </interval>
    {% endfor %}

The clickhouse_quotas_custom section should also be modified.

Ability to change the default user as the one running the tasks

Hi! First of all, great work with the role!

I'm trying to configure a clickhouse instance without the default user (with the idea that if that db get attacked, the default user will not be usefull at all) but even if i create a custom user for been used by my team, the role still uses the default clickhouse user for running the different tasks, its there now an option to select with which user i want to do the clickhouse tasks? If not, i can try to work on it and send you a pr with the changes!

Thanks!

Adding 0.0.0.0 to clickhouse_listen_host_custom results in "Address already in use" error

Hi! Thanks for writing this role and great job!

I noticed that simply adding 0.0.0.0 to clickhouse_listen_host_custom does not work with ClickHouse version 21.12.3.32 due to errors that the address is already in use.

2022.01.10 13:48:18.227905 [ 13691 ] {} <Error> Application: DB::Exception: Listen [0.0.0.0]:8123 failed: Poco::Exception. Code: 1000, e.code() = 98, Net Exception: Address already in use: 0.0.0.0:8123 (version 21.12.3.32 (official build))

This seems to be due to the construction of clickhouse_listen_host where clickhouse_listen_host_default is added together with clickhouse_listen_host_custom. https://github.com/AlexeySetevoi/ansible-clickhouse/blob/master/defaults/main.yml#L34

clickhouse_listen_host_default:
  - "::1"
  - "127.0.0.1"

clickhouse_listen_host_custom: []

clickhouse_listen_host: "{{ clickhouse_listen_host_default + clickhouse_listen_host_custom }}"

It seems ClickHouse does not allow overlapping IP addresses in the listen configuration. I solved it by simply setting the clickhouse_listen_host directly. So if you want to listen on all interfaces and both ipv6 and ipv4 it would look like this.

clickhouse_listen_host: 
  - "::"

Since this is the case I think it would be valuable for the reader to know in the README.md. Do you want me to add how to listen on all interfaces as an example in the README.md as a PR?

Be able to set internal_replication

At the moment, internal_replication is hardcoded in template.
Would be great to be able to define this in a variable.

https://github.com/AlexeySetevoi/ansible-clickhouse/blob/master/templates/remote_servers.j2#L8

Clickhouse-keeper

Hello!
I would like to ask if it is planned to use clickhouse-keeper in the future?
Thanks!

Add ability to manage LDAP / Active Directory configuration for external user directories

Hi again!

For my use case, I need to integrate our ClickHouse cluster with the existing user configuration that exists in Active Directory (LDAP). The configuration is documented here https://clickhouse.com/docs/en/operations/external-authenticators/ldap

This role does not currently expose this functionality but since I need it myself I can upload a PR with a working implementation! I would gladly take any inputs you have regarding the configuration structure and implementation details.

These resources were really helpful when debugging.

Add support for endpoint-based s3 configuration

Hi!

I would like support for the endpoint-based s3 configuration. I will provide a PR so that you can have a look.

https://clickhouse.com/docs/en/engines/table-engines/integrations/s3/#endpoint-settings

Consider overriding configuration instead of replacing the default ones

Hi!

According to the ClickHouse Tutorial and recommendation from users in the Telegram channel, one should not replace the default configuration but instead override the config by placing them under config.d directory or users.d instead.

If you want to adjust the configuration, it’s not handy to directly edit config.xml file, considering it might get rewritten on future package updates. The recommended way to override the config elements is to create files in config.d directory which serve as “patches” to config.xml.

https://clickhouse.com/docs/en/getting-started/tutorial/#clickhouse-tutorial

The resulting configuration of all system settings and user settings can be seen in /var/lib/clickhouse/preprocessed_configs/config.xml and /var/lib/clickhouse/preprocessed_configs/users.xml which is useful during debugging.

I tried making a small change in https://github.com/AlexeySetevoi/ansible-clickhouse/blob/master/tasks/configure/sys.yml that seems to work as expected.

 - name: Config | Generate system config
   template:
     src: config.j2
-     dest: "{{ clickhouse_path_configdir }}/config.xml"
+     dest: "{{ clickhouse_path_configdir }}/config.d/config.xml"

 - name: Config | Generate users config
   template:
     src: users.j2
-     dest: "{{ clickhouse_path_configdir }}/users.xml"
+     dest: "{{ clickhouse_path_configdir }}/users.d/users.xml"

Additionally, I think it is better if we move the conf.d configuration to config.d since then it will not be included in /var/lib/clickhouse/preprocessed_configs/users.xml and only in /var/lib/clickhouse/preprocessed_configs/config.xml

I could provide a PR with the changes so that you can try it if you want?

RPM repository is outdated

ClickHouse has changed their repository URL for Redhat/CentOS: https://packages.clickhouse.com/rpm/clickhouse.repo.

No support for config.xml

Ability to have multiple shards per cluster

It seems to me there is some confusion around cluster and shards.

When looking at the example config:

clickhouse_shards:                           (to me this seems it should been "clickhouse_clusters")
   your_shard_name:                          (this should have been the cluster name)
    - { host: "db_host_1", port: 9000 }      (this is a replica)

The current variable structure seems to be missing a level of abstraction.
It shold have three levels:

clusters:
  shards:
    replicas:

but currently on has two:

shards:
  replicas:

The configuration structure for remote_servers / clusters looks like this:

<remote_servers>
  <cluster1> <-- this is a cluster, not a named shard. Currently the role is calling this level shards.
    </shard> <-- this is a shard and it has no name (variable says shard_name)
      <replica>... <host>, <port> ..</replica>
      <replica>... <host>, <port> ..</replica>
    </shard>
    </shard>
      <replica>... <host>, <port> ..</replica>
      <replica>... <host>, <port> ..</replica>
    </shard>
  </cluster1>
  <cluster2>
    ...
  </cluster2>
</remote_servers>

I've looked through the remote_servers.j2 template and it actually seems to be impossible to create this configuration from the current structure of the variables - unless I'm missing something. I'm not exactly an ansible wizard yet :)

Use CH Multiprofile

Hello,
Clickhouse can use multiprofile for user, like


 <!-- Default users. -->
    <default>
        <password></password>
        <profile>default</profile>
        <profile>readonly</profile>
          </default>

I tried to create user, as

{ name: "name1", profile: "default, readonly", quota: "default", dbs: [ default ] }
or
{ name: "name2", profile: [ default,readonly ], quota: "default", dbs: [ default ] }

But in /etc/clickhouse-server/users.d/users.xml it pastes like
<profile>default, readonly</profile>
or
<profile>['default', 'readonly']</profile>

Is there a way to use multiprofile, like you paste multi dbs in users dict ?

Thanks for your work.

The variable {{ clickhouse_repo_old }} is not defined

Debian stretch have problems ... on file line

https://github.com/AlexeySetevoi/ansible-clickhouse/blob/master/tasks/install_apt.yml#L12

The variable {{ clickhouse_repo_old }} is not defined

fatal: [55.55.55.5]: FAILED! => {"msg": "The task includes an option with an undefined variable. The error was: 'clickhouse_repo_old' is undefined\n\nThe error appears to have been in '/etc/ansible/roles/alexeysetevoi.clickhouse/tasks/install_apt.yml': line 10, column 3, but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe offending line appears to be:\n\n\n- name: Install by APT | Remove old repo link of exists\n  ^ here\n\nexception type: <class 'ansible.errors.AnsibleUndefinedVariable'>\nexception: 'clickhouse_repo_old' is undefined"}

Certificate file /etc/clickhouse-server/server.crt No such file or directory

Hello! Thanks for role clickhouse
I try install and get error

CertificateReloader: Cannot obtain modification time for key file /etc/clickhouse-server/server.key, skipping update. errno: 2, strerror: No such file or directory

Playbook

  - hosts: clickhouse_cluster
    become: true
    pre_tasks:
      - name: Update apt cache.
        apt: update_cache=true cache_valid_time=600
        when: ansible_os_family == 'Debian'
      - name: Remove 127.0.1.1 from /etc/hosts for Yandex Cloud
        lineinfile:
          path: /etc/hosts
          state: absent
          regexp: '^127.0.1.1'
    roles:
      - ansible-clickhouse

Inventory

all:
  children:
    clickhouse_cluster:
      hosts:
        clickhouse0:
          ansible_host: xxx
        clickhouse1:
          ansible_host: xxx
        clickhouse2:
          ansible_host: xxx
  vars:
    ansible_user:  ubuntu
    ansible_ssh_private_key_file: ~/.ssh/id_rsa
    clickhouse_users_custom:
      - { name: "testuser",
          password_sha256_hex: "f2ca1bb6c7e907d06dafe4687e579fce76b37e4e93b7605022da52e6ccc26fd2",
          networks: "{{ clickhouse_networks_default }}",
          profile: "default",
          quota: "default",
          dbs: [ testu1 ] ,
          comment: "classic user with plain password"}
      - { name: "testuser2",
          password: "testplpassword",
          networks: "{{ clickhouse_networks_default }}",
          profile: "default",
          quota: "default",
          dbs: [ testu2 ] ,
          comment: "classic user with hex password"}
      - { name: "testuser3",
          password: "testplpassword",
          networks: { 192.168.0.0/24, 10.0.0.0/8 },
          profile: "default",
          quota: "default",
          dbs: [ testu1,testu2,testu3 ] ,
          comment: "classic user with multi dbs and multi-custom network allow password"}
    clickhouse_query_log_ttl: 'event_date + INTERVAL 7  DELETE'
    clickhouse_dicts:
      test1:
        name: test_dict
        odbc_source:
          connection_string: "DSN=testdb"
          source_table: "dict_source"
        lifetime:
          min: 300
          max: 360
        layout: hashed
        structure:
          key: "testIntKey"
          attributes:
            - { name: testAttrName, type: UInt32, null_value: 0 }
      test2:
        name: test_dict
        odbc_source:
          connection_string: "DSN=testdb"
          source_table: "dict_source"
        lifetime:
          min: 300
          max: 360
        layout: complex_key_hashed
        structure:
          key:
            attributes:
              - { name: testAttrComplexName, type: String }
          attributes:
            - { name: testAttrName, type: String, null_value: "" }
    clickhouse_dbs_custom:
      - { name: testu1 }
      - { name: testu2, state:present }
      - { name: testu3, state:absent }
    clickhouse_clusters:
      your_cluster_name:
        shard_1:

          - { host: "clickhouse0", port: 9000 }

          - { host: "clickhouse1", port: 9000 }

          - { host: "clickhouse2", port: 9000 }
     
    clickhouse_zookeeper_nodes:
      - { host: "clickhouse0", port: 2181 }
      - { host: "clickhouse1", port: 2181 }
      - { host: "clickhouse2", port: 2181 }

Ansible output

TASK [ansible-clickhouse : Wait for Clickhouse Server to Become Ready] *****************************************************************************************************************
Monday 04 April 2022  13:39:15 +0600 (0:00:01.407)       0:02:23.761 **********
fatal: [clickhouse1]: FAILED! => changed=false
  elapsed: 300
  msg: Timeout when waiting for 127.0.0.1:9000
fatal: [clickhouse2]: FAILED! => changed=false
  elapsed: 300
  msg: Timeout when waiting for 127.0.0.1:9000
fatal: [clickhouse0]: FAILED! => changed=false
  elapsed: 300
  msg: Timeout when waiting for 127.0.0.1:9000

clickhouse-server.err.log

8. main @ 0xa4d81ea in /usr/bin/clickhouse
9. __libc_start_main @ 0x7f99c27ce0b3 in ?
10. _start @ 0xa2d15ee in /usr/bin/clickhouse
 (version 22.3.2.1)
2022.04.04 07:54:31.484906 [ 5289 ] {} <Error> Application: DB::Exception: Syntax error (Storage to create table for query_log): failed at position 85 ('DELETE'): DELETE ORDER BY (event_date, event_time). Expected one of: token, DoubleColon, MOD, DIV, NOT, BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, IS, AND, OR, QuestionMark, alias, AS, SECOND, SQL_TSI_SECOND, SS, S, MINUTE, SQL_TSI_MINUTE, MI, N, HOUR, SQL_TSI_HOUR, HH, DAY, SQL_TSI_DAY, DD, D, WEEK, SQL_TSI_WEEK, WK, WW, MONTH, SQL_TSI_MONTH, MM, M, QUARTER, SQL_TSI_QUARTER, QQ, Q, YEAR, SQL_TSI_YEAR, YYYY, YY, end of query
2022.04.04 07:55:01.955613 [ 5364 ] {} <Warning> Application: Calculated checksum of the binary: 51010DC62C0638E7259D2BDDE72C485C. There is no information about the reference checksum.
2022.04.04 07:55:01.974060 [ 5364 ] {} <Error> CertificateReloader: Cannot obtain modification time for certificate file /etc/clickhouse-server/server.crt, skipping update. errno: 2, strerror: No such file or directory
2022.04.04 07:55:01.974087 [ 5364 ] {} <Error> CertificateReloader: Cannot obtain modification time for key file /etc/clickhouse-server/server.key, skipping update. errno: 2, strerror: No such file or directory
2022.04.04 07:55:01.974386 [ 5364 ] {} <Error> CertificateReloader: Poco::Exception. Code: 1000, e.code() = 0, SSL context exception: Error loading private key from file /etc/clickhouse-server/server.key: error:02000002:system library:OPENSSL_internal:No such file or directory (version 22.3.2.1)
2022.04.04 07:55:01.982144 [ 5364 ] {} <Error> Application: Caught exception while loading metadata: Code: 62. DB::Exception: Syntax error (Storage to create table for query_log): failed at position 85 ('DELETE'): DELETE ORDER BY (event_date, event_time). Expected one of: token, DoubleColon, MOD, DIV, NOT, BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, IS, AND, OR, QuestionMark, alias, AS, SECOND, SQL_TSI_SECOND, SS, S, MINUTE, SQL_TSI_MINUTE, MI, N, HOUR, SQL_TSI_HOUR, HH, DAY, SQL_TSI_DAY, DD, D, WEEK, SQL_TSI_WEEK, WK, WW, MONTH, SQL_TSI_MONTH, MM, M, QUARTER, SQL_TSI_QUARTER, QQ, Q, YEAR, SQL_TSI_YEAR, YYYY, YY, end of query. (SYNTAX_ERROR), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int, bool) @ 0xa4dde1a in /usr/bin/clickhouse
1. DB::parseQueryAndMovePosition(DB::IParser&, char const*&, char const*, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, bool, unsigned long, unsigned long) @ 0x159d3a9f in /usr/bin/clickhouse
2. DB::SystemLogs::SystemLogs(std::__1::shared_ptr<DB::Context const>, Poco::Util::AbstractConfiguration const&) @ 0x147b831b in /usr/bin/clickhouse
3. DB::Context::initializeSystemLogs() @ 0x140d442f in /usr/bin/clickhouse
4. DB::Server::main(std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0xa564827 in /usr/bin/clickhouse
5. Poco::Util::Application::run() @ 0x164cb146 in /usr/bin/clickhouse
6. DB::Server::run() @ 0xa556c51 in /usr/bin/clickhouse
7. mainEntryClickHouseServer(int, char**) @ 0xa554227 in /usr/bin/clickhouse

Version

clickhouse-server/stable,stable,now 22.3.2.2

Environment

Version

ansible [core 2.12.2]
  python version = 3.8.10 (default, Nov 26 2021, 20:14:08) [GCC 9.3.0]

Server where ansible

cat /etc/*release*
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=20.04
DISTRIB_CODENAME=focal
DISTRIB_DESCRIPTION="Ubuntu 20.04.3 LTS"
NAME="Ubuntu"
VERSION="20.04.3 LTS (Focal Fossa)"

Remote server

cat /etc/*release*
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=20.04
DISTRIB_CODENAME=focal
DISTRIB_DESCRIPTION="Ubuntu 20.04.3 LTS"
NAME="Ubuntu"
VERSION="20.04.3 LTS (Focal Fossa)"

Remove legacy code related to CentOS distributions

With #79 and #81 there will no longer be any tests on CentOS and only on Debian and Ubuntu. Should we remove the code complexity that exists due to supporting dnf and yum as well as the only tested package manager which will be apt?

Allow to use custom repository

We have a policy to use local mirrors for all external packages. That means, to use clickhouse in production I need to create a local mirror and to change the ansible-clickhouse role to use that mirror.

When I've tried to use custom clickhouse_repo variable I found that ansible-clickhouse uses 'include_vars' which overrides variables from the calling playbook. Moreover, I found that all three files to include (vars/Ubuntu_trusty.yml, Ubuntu_precise.yml and Ubuntu_xenial.yml) are identical (except for a single empty line).

To achieve my goal (use of custom repositories) I'd like to do four things:

Move all variables from Ubuntu* files into defaults.
Remove include_var statement
Add variable clickhouse_repo_key_data (not defined by default).
In case of clickhouse_repo_key_data make role to use it instead of clickhouse_repo_keyserver and clickhouse_repo_key to add that key into apt_key (with data option).

Is it ok?

Make profile and quotas optional for users

Hello.

Next improvement is a bit more intrusive and I'd like to discuss it beforehand.
I want to make profile and quota optional, by using default value (clickhouse_default_user_profile=default, and clickhouse_default_user_quota=default).

Moreover, I have two more ideas:

To make networks defaulting to 0.0.0.0/0 and ::/0.
To use clickhouse_dbs as default for clickhouse_users_custom[user].dbs.

Is it OK or it's too daring and insecure?

My goal is to have a very small config for initial use:

    clickhouse_dbs:
     - name: somedatabase
    clickhouse_users_custom:
     - name: someuser
       password: somepassword

User-level setting max_partitions_per_insert_block is specified in the wrong place

The max_partitions_per_insert_block is currently specified in config.xml (ansible-clickhouse/templates/config.j2), but it
should be specified under the <profiles> element in users.xml config file (see 1, 2):

<yandex>
    <profiles>
        <default>
            <max_partitions_per_insert_block>100</max_partitions_per_insert_block>
...

Since version 20.5.2.7 this misconfiguration prevents the clickhouse-server from starting (ClickHouse/ClickHouse#11449).

Multiple ClickHouse instances on a single host?

Is it possible to deploy multiple ClickHouse instances on a single host, with unique config and data directory for each instance?
The deploy looks like:
host1: shard1 replica1, shard2 replica1
host2: shard1 replica2, shard2 replica2
host3: shard3 replica1, shard4 replica1
host4: shard3 replica2, shard4 replica2
host5: shard5 replica1, shard6 replica1
host6: shard5 replica2, shard6 replica2