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

  • 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)