community.clickhouse.clickhouse_client module – Execute queries in a ClickHouse database using the clickhouse-driver Client interface

Note

This module is part of the community.clickhouse collection (version 2.1.0).

You might already have this collection installed if you are using the ansible package. It is not included in ansible-core. To check whether it is installed, run ansible-galaxy collection list.

To install it, use: ansible-galaxy collection install community.clickhouse. You need further requirements to be able to use this module, see Requirements for details.

To use it in a playbook, specify: community.clickhouse.clickhouse_client.

New in community.clickhouse 0.1.0

Synopsis
Requirements
Parameters
Notes
Examples
Return Values

Synopsis

Execute arbitrary queries in a ClickHouse database using the clickhouse-driver Client interface.
Always returns that the state changed.

Requirements

The below requirements are needed on the host that executes this module.

clickhouse-driver

Parameters

Parameter	Comments
client_kwargs dictionary	Any additional keyword arguments you want to pass to the Client interface when instantiating its object. Default: `{}`
execute string / required	A query to the `Client.execute()` method.
execute_kwargs dictionary	All additional keyword arguments you want to pass to the `Client.execute()` method. For example, you can pass substitution parameters for the query you pass through the execute argument. Default: `{}`
login_db string	The same as the `Client(database='...')` argument. If not passed, relies on the driver’s default argument value.
login_host string	The same as the `Client(host='...')` argument. Default: `"localhost"`
login_password string	The same as the `Client(password='...')` argument. If not passed, relies on the driver’s default argument value.
login_port integer	The same as the `Client(port='...')` argument. If not passed, relies on the driver’s default argument value.
login_user string	The same as the `Client(user='...')` argument. If not passed, relies on the driver’s default argument value. Be sure your the user has permissions to read the system tables listed in the RETURN section.
set_settings dictionary added in community.clickhouse 0.5.0	The dict of settings that need to be set in the session before executing the request. Default: `{}`

Notes

Note

Does not support check_mode.
See the clickhouse-driver documentation for more information about the driver interface.

Examples

- name: Query DB using non-default user & DB to connect to
  register: result
  community.clickhouse.clickhouse_client:
    execute: SELECT * FROM my_table
    login_host: localhost
    login_user: alice
    login_db: foo
    login_password: my_password

- name: Print returned rows
  ansible.builtin.debug:
    var: result.result

- name: Create table
  register: result
  community.clickhouse.clickhouse_client:
    execute: CREATE TABLE test_table_1 (x String) ENGINE = Memory
    set_settings:
      flatten_nested: 0
      short_circuit_function_evaluation: 'disable'

- name: Insert into test table using named parameters
  register: result
  community.clickhouse.clickhouse_client:
    execute: "INSERT INTO test_table_1 (x) VALUES (%(a)s), (%(b)s), (%(c)s)"
    execute_kwargs:
      params:
        a: one
        b: two
        c: three

- name: Check the result
  ansible.builtin.assert:
    that:
      - result.substituted_query == "INSERT INTO test_table_1 (x) VALUES ('one'), ('two'), ('three')"
      - result.statistics["processed_rows"] == 3

- name: Check rows were inserted into test table
  register: result
  community.clickhouse.clickhouse_client:
    execute: "SELECT * FROM test_table_1"

- name: Check returned values
  ansible.builtin.assert:
    that:
    - result.result == [["one"], ["two"], ["three"]]

Return Values

Common return values are documented here, the following are the fields unique to this module:

Key	Description
result list / elements=string	Result returned by Client.execute(). Returned: on success Sample: `[["one"], ["two"], ["three"]]`
statistics dictionary	Last executed query statistics retrieved from the last_query attribute. Returned items depend on server version. Returned: on success
substituted_query string	Executed query with substituted arguments if any. Returned: on success Sample: `"SELECT * FROM test_table_1"`

Authors

Andrew Klychkov (@Andersson007)
Aleks Vagachev (@aleksvagachev)

Collection links

© 2012–2018 Michael DeHaan
© 2018–2025 Red Hat, Inc.
Licensed under the GNU General Public License version 3.
https://docs.ansible.com/projects/ansible/latest/collections/community/clickhouse/clickhouse_client_module.html