Skip to content

Подключение к Clickhouse

Bases: JDBCConnection

Clickhouse JDBC connection. support hooks

Based on Maven package com.clickhouse:clickhouse-jdbc:0.7.2 (official Clickhouse JDBC driver).

See also

Before using this connector please take into account [clickhouse-prerequisites][]

Added in 0.1.0

Parameters

host : str Host of Clickhouse database. For example: test.clickhouse.domain.com or 193.168.1.11

int, default: 8123

Port of Clickhouse database

str

User, which have proper access to the database. For example: some_user

str

Password for database connection

str, optional

Database (==schema) in Clickhouse.

pyspark.sql.SparkSession

Spark session.

dict, default: None

Specifies one or more extra parameters by which clients can connect to the instance.

For example: {"continueBatchOnError": "false"}.

See: * Clickhouse JDBC driver properties documentation * Clickhouse core settings documentation * Clickhouse query complexity documentation * Clickhouse query level settings

Examples

Create and check Clickhouse connection:

from onetl.connection import Clickhouse
from pyspark.sql import SparkSession

# Create Spark session with Clickhouse driver loaded
maven_packages = Clickhouse.get_packages()
spark = (
    SparkSession.builder.appName("spark-app-name")
    .config("spark.jars.packages", ",".join(maven_packages))
    .getOrCreate()
)

# Create connection
clickhouse = Clickhouse(
    host="database.host.or.ip",
    user="user",
    password="*****",
    extra={"continueBatchOnError": "false"},
    spark=spark,
).check()
Source code in onetl/connection/db_connection/clickhouse/connection.py 
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
@support_hooks
class Clickhouse(JDBCConnection):
    """Clickhouse JDBC connection. [![support hooks](https://img.shields.io/badge/%20-support%20hooks-blue)](/hooks/)

    Based on Maven package [com.clickhouse:clickhouse-jdbc:0.7.2](https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc/0.7.2)
    ([official Clickhouse JDBC driver](https://github.com/ClickHouse/clickhouse-jdbc)).

    !!! info "See also"

        Before using this connector please take into account [clickhouse-prerequisites][]

    !!! success "Added in 0.1.0"

    Parameters
    ----------
    host : str
        Host of Clickhouse database. For example: `test.clickhouse.domain.com` or `193.168.1.11`

    port : int, default: `8123`
        Port of Clickhouse database

    user : str
        User, which have proper access to the database. For example: `some_user`

    password : str
        Password for database connection

    database : str, optional
        Database (==schema) in Clickhouse.

    spark : `pyspark.sql.SparkSession`
        Spark session.

    extra : dict, default: `None`
        Specifies one or more extra parameters by which clients can connect to the instance.

        For example: `{"continueBatchOnError": "false"}`.

        See:
            * [Clickhouse JDBC driver properties documentation](https://clickhouse.com/docs/en/integrations/java#configuration)
            * [Clickhouse core settings documentation](https://clickhouse.com/docs/en/operations/settings/settings)
            * [Clickhouse query complexity documentation](https://clickhouse.com/docs/en/operations/settings/query-complexity)
            * [Clickhouse query level settings](https://clickhouse.com/docs/en/operations/settings/query-level)

    Examples
    --------

    Create and check Clickhouse connection:

    ```python
    from onetl.connection import Clickhouse
    from pyspark.sql import SparkSession

    # Create Spark session with Clickhouse driver loaded
    maven_packages = Clickhouse.get_packages()
    spark = (
        SparkSession.builder.appName("spark-app-name")
        .config("spark.jars.packages", ",".join(maven_packages))
        .getOrCreate()
    )

    # Create connection
    clickhouse = Clickhouse(
        host="database.host.or.ip",
        user="user",
        password="*****",
        extra={"continueBatchOnError": "false"},
        spark=spark,
    ).check()
    ```
    """

    host: Host
    port: int = 8123
    database: Optional[str] = None
    extra: ClickhouseExtra = ClickhouseExtra()

    Extra = ClickhouseExtra
    Dialect = ClickhouseDialect

    ReadOptions = ClickhouseReadOptions
    WriteOptions = ClickhouseWriteOptions
    SQLOptions = ClickhouseSQLOptions
    FetchOptions = ClickhouseFetchOptions
    ExecuteOptions = ClickhouseExecuteOptions

    DRIVER: ClassVar[str] = "com.clickhouse.jdbc.ClickHouseDriver"

    @slot
    @classmethod
    def get_packages(
        cls,
        package_version: str | None = None,
        apache_http_client_version: str | None = None,
    ) -> list[str]:
        """
        Get package names to be downloaded by Spark. [![support hooks](https://img.shields.io/badge/%20-support%20hooks-blue)](/hooks/)

        Allows specifying custom JDBC and Apache HTTP Client versions.

        !!! success "Added in 0.9.0"

        Parameters
        ----------
        package_version : str, optional
            ClickHouse JDBC version client packages. Defaults to `0.7.2`.

            Versions 0.8.0-0.9.2 are not supported,
            see [issue #2625](https://github.com/ClickHouse/clickhouse-java/issues/2625).

            !!! success "Added in 0.11.0"

        apache_http_client_version : str, optional
            Apache HTTP Client version package. Defaults to `5.4.2`.

            Used only if `package_version` is in range `0.5.0-0.7.0`.

            !!! success "Added in 0.11.0"

        Examples
        --------

        ```python
        from onetl.connection import Clickhouse

        Clickhouse.get_packages()
        Clickhouse.get_packages(package_version="0.7.2")
        Clickhouse.get_packages(package_version="0.6.0", apache_http_client_version="5.4.2")
        ```
        """

        # 0.9.3+ is not a default due to performance issues: https://github.com/ClickHouse/clickhouse-java/issues/2516
        default_jdbc_version = "0.7.2"
        jdbc_version = Version(package_version or default_jdbc_version).min_digits(3)

        # https://github.com/ClickHouse/clickhouse-java/issues/2625
        if jdbc_version >= Version("0.9.3"):
            return [f"com.clickhouse:clickhouse-jdbc-all:{jdbc_version}"]

        result = [
            f"com.clickhouse:clickhouse-jdbc:{jdbc_version}",
            f"com.clickhouse:clickhouse-http-client:{jdbc_version}",
        ]

        if Version("0.5.0") <= jdbc_version <= Version("0.7.0"):
            # Before 0.5.0 builtin Java HTTP Client was used.
            # In 0.5.0-0.7.0 users may choose one of 2 HTTP Clients by using Maven classifier,
            # which is not supported by Spark, unfortunately, so we need to add HTTP client manually.
            # After 0.7.1 Apache HTTP Client is a new default.
            default_http_version = "5.4.2"
            http_version = Version(apache_http_client_version or default_http_version).min_digits(3)
            result.append(f"org.apache.httpcomponents.client5:httpclient5:{http_version}")

        return result

    @classproperty
    def package(self) -> str:
        """Get a single string of package names to be downloaded by Spark for establishing a Clickhouse connection."""
        msg = "`Clickhouse.package` will be removed in 1.0.0, use `Clickhouse.get_packages()` instead"
        warnings.warn(msg, UserWarning, stacklevel=3)
        return (
            "com.clickhouse:clickhouse-jdbc:0.7.2,"
            "com.clickhouse:clickhouse-http-client:0.7.2,"
            "org.apache.httpcomponents.client5:httpclient5:5.4.2"
        )

    @property
    def jdbc_url(self) -> str:
        if self.database:
            return f"jdbc:clickhouse://{self.host}:{self.port}/{self.database}"

        return f"jdbc:clickhouse://{self.host}:{self.port}"

    @property
    def jdbc_params(self) -> dict:
        result = super().jdbc_params
        result.update(self.extra.dict(by_alias=True))
        # https://github.com/ClickHouse/clickhouse-java/issues/691#issuecomment-975545784
        result["client_name"] = result.get("client_name", get_client_info(self.spark))
        return result

    @property
    def instance_url(self) -> str:
        return f"{self.__class__.__name__.lower()}://{self.host}:{self.port}"

    def __str__(self):
        return f"{self.__class__.__name__}[{self.host}:{self.port}]"

    @staticmethod
    def _build_statement(
        statement: str,
        statement_type: JDBCStatementType,
        jdbc_connection,
        statement_args,
    ):
        # Clickhouse does not support prepared statements, as well as calling functions/procedures
        return jdbc_connection.createStatement(*statement_args)

get_packages(package_version=None, apache_http_client_version=None) classmethod

Get package names to be downloaded by Spark. support hooks

Allows specifying custom JDBC and Apache HTTP Client versions.

Added in 0.9.0

Parameters

package_version : str, optional ClickHouse JDBC version client packages. Defaults to 0.7.2.

Versions 0.8.0-0.9.2 are not supported,
see [issue #2625](https://github.com/ClickHouse/clickhouse-java/issues/2625).

!!! success "Added in 0.11.0"
str, optional

Apache HTTP Client version package. Defaults to 5.4.2.

Used only if package_version is in range 0.5.0-0.7.0.

Added in 0.11.0

Examples

from onetl.connection import Clickhouse

Clickhouse.get_packages()
Clickhouse.get_packages(package_version="0.7.2")
Clickhouse.get_packages(package_version="0.6.0", apache_http_client_version="5.4.2")
Source code in onetl/connection/db_connection/clickhouse/connection.py 
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
@slot
@classmethod
def get_packages(
    cls,
    package_version: str | None = None,
    apache_http_client_version: str | None = None,
) -> list[str]:
    """
    Get package names to be downloaded by Spark. [![support hooks](https://img.shields.io/badge/%20-support%20hooks-blue)](/hooks/)

    Allows specifying custom JDBC and Apache HTTP Client versions.

    !!! success "Added in 0.9.0"

    Parameters
    ----------
    package_version : str, optional
        ClickHouse JDBC version client packages. Defaults to `0.7.2`.

        Versions 0.8.0-0.9.2 are not supported,
        see [issue #2625](https://github.com/ClickHouse/clickhouse-java/issues/2625).

        !!! success "Added in 0.11.0"

    apache_http_client_version : str, optional
        Apache HTTP Client version package. Defaults to `5.4.2`.

        Used only if `package_version` is in range `0.5.0-0.7.0`.

        !!! success "Added in 0.11.0"

    Examples
    --------

    ```python
    from onetl.connection import Clickhouse

    Clickhouse.get_packages()
    Clickhouse.get_packages(package_version="0.7.2")
    Clickhouse.get_packages(package_version="0.6.0", apache_http_client_version="5.4.2")
    ```
    """

    # 0.9.3+ is not a default due to performance issues: https://github.com/ClickHouse/clickhouse-java/issues/2516
    default_jdbc_version = "0.7.2"
    jdbc_version = Version(package_version or default_jdbc_version).min_digits(3)

    # https://github.com/ClickHouse/clickhouse-java/issues/2625
    if jdbc_version >= Version("0.9.3"):
        return [f"com.clickhouse:clickhouse-jdbc-all:{jdbc_version}"]

    result = [
        f"com.clickhouse:clickhouse-jdbc:{jdbc_version}",
        f"com.clickhouse:clickhouse-http-client:{jdbc_version}",
    ]

    if Version("0.5.0") <= jdbc_version <= Version("0.7.0"):
        # Before 0.5.0 builtin Java HTTP Client was used.
        # In 0.5.0-0.7.0 users may choose one of 2 HTTP Clients by using Maven classifier,
        # which is not supported by Spark, unfortunately, so we need to add HTTP client manually.
        # After 0.7.1 Apache HTTP Client is a new default.
        default_http_version = "5.4.2"
        http_version = Version(apache_http_client_version or default_http_version).min_digits(3)
        result.append(f"org.apache.httpcomponents.client5:httpclient5:{http_version}")

    return result