.. _features:
Features
========
- Compression support:
* `LZ4/LZ4HC `_
* `ZSTD `_
- TLS support (since server version 1.1.54304).
.. _external-tables:
External data for query processing
----------------------------------
You can pass `external data `_
alongside with query:
.. code-block:: python
>>> tables = [{
... 'name': 'ext',
... 'structure': [('x', 'Int32'), ('y', 'Array(Int32)')],
... 'data': [
... {'x': 100, 'y': [2, 4, 6, 8]},
... {'x': 500, 'y': [1, 3, 5, 7]},
... ]
... }]
>>> client.execute(
... 'SELECT sum(x) FROM ext', external_tables=tables
... )
[(600,)]
Settings
--------
There are a lot of ClickHouse server `settings `_.
Settings can be specified during Client initialization:
.. code-block:: python
# Set max number threads for all queries execution.
>>> settings = {'max_threads': 2}
>>> client = Client('localhost', settings=settings)
Each setting can be overridden in an `execute` statement:
.. code-block:: python
# Set lower priority to query and limit max number threads
# to execute the request.
>>> settings = {'max_threads': 2, 'priority': 10}
>>> client.execute('SHOW TABLES', settings=settings)
[('first_table',)]
Compression
-----------
Native protocol supports two types of compression: `LZ4 `_ and
`ZSTD `_. When compression is enabled compressed data
should be hashed using `CityHash algorithm `_.
Additional packages should be install in order by enable compression suport, see :ref:`installation-pypi`.
Enabled client-side compression can save network traffic.
Client with compression support can be constructed as follows:
.. code-block:: python
>>> from clickhouse_driver import Client
>>> client_with_lz4 = Client('localhost', compression=True)
>>> client_with_lz4 = Client('localhost', compression='lz4')
>>> client_with_zstd = Client('localhost', compression='zstd')
.. _compression-cityhash-notes:
CityHash algorithm notes
~~~~~~~~~~~~~~~~~~~~~~~~
Unfortunately ClickHouse server comes with built-in old version of CityHash algorithm (1.0.2).
That's why we can't use original `CityHash `_ package.
An older version is published separately at `PyPI `_.
Secure connection
-----------------
.. code-block:: python
>>> from clickhouse_driver import Client
>>>
>>> client = Client('localhost', secure=True)
>>> # Using self-signed certificate.
... self_signed_client = Client(
... 'localhost', secure=True,
... ca_certs='/etc/clickhouse-server/server.crt'
... )
>>> # Disable verification.
... no_verifyed_client = Client(
... 'localhost', secure=True, verify=False
... )
>>>
>>> # Example of secured client with Let's Encrypt certificate.
... import certifi
>>>
>>> client = Client(
... 'remote-host', secure=True, ca_certs=certifi.where()
... )
Specifying query id
-------------------
You can manually set query identificator for each query. UUID for example:
.. code-block:: python
>>> from uuid import uuid4
>>>
>>> query_id = str(uuid4())
>>> print(query_id)
bbd7dea3-eb63-4a21-b727-f55b420a7223
>>> client.execute(
... 'SELECT * FROM system.processes', query_id=query_id
... )
[(1, 'default', 'bbd7dea3-eb63-4a21-b727-f55b420a7223', '127.0.0.1', 57664, 'default', 'bbd7dea3-eb63-4a21-b727-f55b420a7223', '127.0.0.1', 57664, 1, 'klebedev', 'klebedev-ThinkPad-T460', 'ClickHouse python-driver', 18, 10, 3, 54406, 0, '', '', 0.004916541, 0, 0, 0, 0, 0, 0, 0, 0, 'SELECT * FROM system.processes', (25,), ('Query', 'SelectQuery', 'NetworkReceiveElapsedMicroseconds', 'ContextLock', 'RWLockAcquiredReadLocks'), (1, 1, 54, 9, 1), ('use_uncompressed_cache', 'load_balancing', 'max_memory_usage'), ('0', 'random', '10000000000'))]
You can cancel query with specific id by sending another query with the same
query id if option `replace_running_query
`_ is set to 1.
Query results are fetched by the same instance of Client that emitted query.
Retrieving results in columnar form
-----------------------------------
Columnar form sometimes can be more useful.
.. code-block:: python
>>> client.execute('SELECT arrayJoin(range(3))', columnar=True)
[(0, 1, 2)]
Data types checking on INSERT
-----------------------------
Data types check is disabled for performance on ``INSERT`` queries.
You can turn it on by `types_check` option:
.. code-block:: python
>>> client.execute(
... 'INSERT INTO test (x) VALUES', [('abc', )],
... types_check=True
... )
Reading query profile info
--------------------------
Last query's profile info can be examined. `rows_before_limit` examine example:
.. code-block:: python
>>> rows = client.execute('SELECT arrayJoin(range(100)) LIMIT 3')
>>> print(rows, client.last_query.profile_info.rows_before_limit)
([(0,), (1,), (2,)], 100)
Receiving server logs
---------------------
Query logs can be received from server by using `send_logs_level` setting:
.. code-block:: python
>>> from logging.config import dictConfig
>>> # Simple logging configuration.
... dictConfig({
... 'version': 1,
... 'disable_existing_loggers': False,
... 'formatters': {
... 'standard': {
... 'format': '%(asctime)s %(levelname)-8s %(name)s: %(message)s'
... },
... },
... 'handlers': {
... 'default': {
... 'level': 'INFO',
... 'formatter': 'standard',
... 'class': 'logging.StreamHandler',
... },
... },
... 'loggers': {
... '': {
... 'handlers': ['default'],
... 'level': 'INFO',
... 'propagate': True
... },
... }
... })
>>>
>>> settings = {'send_logs_level': 'debug'}
>>> client.execute('SELECT 1', settings=settings)
2018-12-14 10:24:53,873 INFO clickhouse_driver.log: {b328ad33-60e8-4012-b4cc-97f44a7b28f2} [ 25 ] executeQuery: (from 127.0.0.1:57762) SELECT 1
2018-12-14 10:24:53,874 INFO clickhouse_driver.log: {b328ad33-60e8-4012-b4cc-97f44a7b28f2} [ 25 ] executeQuery: Query pipeline:
Expression
Expression
One
2018-12-14 10:24:53,875 INFO clickhouse_driver.log: {b328ad33-60e8-4012-b4cc-97f44a7b28f2} [ 25 ] executeQuery: Read 1 rows, 1.00 B in 0.004 sec., 262 rows/sec., 262.32 B/sec.
2018-12-14 10:24:53,875 INFO clickhouse_driver.log: {b328ad33-60e8-4012-b4cc-97f44a7b28f2} [ 25 ] MemoryTracker: Peak memory usage (for query): 40.23 KiB.
[(1,)]