Python clickhouse driver

03.09.202203.09.2022 admin 0 Comments

Python clickhouse driver

clickhouse-connect 0.2.4

pip install clickhouse-connect Copy PIP instructions

Released: Aug 19, 2022

ClickHouse core driver, SqlAlchemy, and Superset libraries

Navigation

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: Apache Software License (Apache License 2.0)

Requires: Python

Maintainers

Classifiers

Project description

ClickHouse Connect

A suite of Python packages for connecting Python to ClickHouse, initially supporting Apache Superset using a minimal read only SQLAlchemy dialect. Uses the ClickHouse HTTP interface.

Installation

ClickHouse Connect requires Python 3.7 or higher. The cython package must be installed prior to installing clickhouse_connect to build and install the optional Cython/C extensions used for improving read and write performance using the ClickHouse Native format. After installing cython if desired, clone this repository and run python setup.py install from the project directory.

Getting Started

Simple ‘command’ that does not return a result set.

Bulk insert of a matrix of rows and columns.

Minimal SQLAlchemy Support

On installation ClickHouse Connect registers the clickhousedb SQLAlchemy Dialect entry point. This dialect supports basic table reflection for table columns and datatypes, and command and query execution using DB API 2.0 cursors. Most ClickHouse datatypes have full query/cursor support.

ClickHouse Connect does not yet implement the full SQLAlchemy API for DDL (Data Definition Language) or ORM (Object Relational Mapping). These features are in development.

Superset Support

On installation ClickHouse Connect registers the clickhousedb Superset Database Engine Spec entry point. Using the clickhousedb SQLAlchemy dialect, the engine spec supports complete data exploration and Superset SQL Lab functionality with all standard ClickHouse data types. See Connecting Superset for complete instructions.

ClickHouse Enum, UUID, and IP Address datatypes are treated as strings. For compatibility with Superset Pandas dataframes, unsigned UInt64 data types are interpreted as signed Int64 values. ClickHouse CSV Upload via SuperSet is not yet implemented.

Optional Features

SQLAlchemy and Superset require the corresponding SQLAlchemy and Apache Superset packages to be included in your Python installation. ClickHouse connect also includes C/Cython extensions for improved performance reading String and FixedString datatypes. These extensions will be installed automatically by setup.py if a C compiler is available.

Query results can be returned as either a numpy array or a pandas DataFrame if the numpy and pandas libraries are available. Use the client methods query_np and query_df respectively.

Tests

Main Client Interface

Interaction with the ClickHouse server is done through a clickhouse_connect Client instance. At this point only an HTTP(s) based Client is supported.

HTTP Client constructor/initialization parameters

Create a ClickHouse client using the clickhouse_connect.driver.create_client(. ) function or clickhouse_connect.get_client(. ) wrapper. All parameters are optional:

Any remaining keyword parameters are interpreted as ‘setting’ parameters to send to the ClickHouse server with every query/request

Querying data

Use the client query method to retrieve a QueryResult from ClickHouse. Parameters:

The query method results a QueryResult object with the following fields:

Numpy and Pandas queries

Datatype options for queries

There are some convenience methods in the clickhouse_connect.driver package that control the format of some ClickHouse datatypes. These are included in part to improve Superset compatibility.

Inserting data

Use the client insert method to insert data into a ClickHouse table. Parameters:

Notes on data inserts

The client insert_df can be used to insert a Pandas DataFrame, assuming the column names in the DataFrame match the ClickHouse table column names. Note that a Numpy array can be passed directly as the data parameter to the primary insert method so there is no separate insert_np method.

For column types that can be different native Python types (for example, UUIDs or IP Addresses), the driver will assume that the data type for the whole column matches the first non «None» value in the column and process insert data accordingly. So if the first data value for insert into a ClickHouse UUID column is a string, the driver will assume all data values in that insert column are strings.

DDL and other «simple» SQL statements

The client command method can be used for ClickHouse commands/queries that return a single result or row of results values. In this case the result is returned as a single row TabSeparated values and are cast to a single string, int, or list of string values. The command method parameters are:

Python clickhouse driver

External data for query processing

You can pass external data alongside with query:

There are a lot of ClickHouse server settings. Settings can be specified during Client initialization:

Client with compression support can be constructed as follows:

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.

Specifying query id

You can manually set query identificator for each query. UUID for example:

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.

Data types checking on INSERT

Data types check is disabled for performance on INSERT queries. You can turn it on by types_check option:

Query execution statistics

Client stores statistics about last query execution. It can be obtained by accessing last_query attribute. Statistics is sent from ClickHouse server and calculated on client side. last_query contains info about:

profile: rows before limit

Receiving server logs

Query logs can be received from server by using send_logs_level setting:

New in version 0.1.3.

Additional connection points can be defined by using alt_hosts. If main connection point is unavailable driver will use next one from alt_hosts.

This option is good for ClickHouse cluster with multiple replicas.

In example above on every new connection driver will use following sequence of hosts if previous host is unavailable:

All queries within established connection will be sent to the same host.

Python DB API 2.0

New in version 0.1.3.

This driver is also implements DB API 2.0 specification. It can be useful for various integrations.

Threads may share the module and connections.

:ref:`dbapi-connection` class is just wrapper for handling multiple cursors (clients) and do not initiate actual connections to the ClickHouse server.

There are some non-standard ClickHouse-related :ref:`Cursor methods ` for: external data, settings, etc.

For automatic disposal Connection and Cursor instances can be used as context managers:

You can use cursor_factory argument to get results as dicts or named tuples (since version 0.2.4):

New in version 0.1.6.

Direct loading into NumPy arrays increases performance and lowers memory requirements on large amounts of rows.

Direct loading into pandas DataFrame is also supported by using query_dataframe:

Writing pandas DataFrame is also supported with insert_dataframe:

Starting from version 0.2.2 nullable columns are also supported. Keep in mind that nullable columns have object dtype. For convenience np.nan and None is supported as NULL values for inserting. But only None is returned after selecting for NULL values.

It’s important to specify dtype during dataframe creation:

New in version 0.2.2.

Each Client instance can be used as a context manager:

Upon exit, any established connection to the ClickHouse server will be closed automatically.

clickhouse-driver 0.2.4

pip install clickhouse-driver Copy PIP instructions

Released: Jun 13, 2022

Python driver with native interface for ClickHouse

Navigation

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: MIT License (MIT)

Tags ClickHouse, db, database, cloud, analytics

Requires: Python >=3.4, xzkostyan

Classifiers

Project description

ClickHouse Python Driver

ClickHouse Python Driver with native (TCP) interface support.

Asynchronous wrapper is available here: https://github.com/mymarilyn/aioch

Features

Documentation

Usage

There are two ways to communicate with server:

Pure Client example:

License

ClickHouse Python Driver is distributed under the MIT license.

Project details

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: MIT License (MIT)

Tags ClickHouse, db, database, cloud, analytics

Requires: Python >=3.4, xzkostyan

ClickHouse and Python: Getting to Know the Clickhouse-driver Client

Python is a force in the world of analytics due to powerful libraries like numpy along with a host of machine learning frameworks. ClickHouse is an increasingly popular store of data. As a Python data scientist, you may wonder how to connect them.

Fortunately, the Altinity Blog is here to solve mysteries, at least those that involve ClickHouse. This post contains a review of the clickhouse-driver client. It’s a solidly engineered module that is easy to use and integrates easily with standard tools like Jupyter Notebooks and Anaconda. Clickhouse-driver is a great way to jump into ClickHouse Python connectivity.

So Many Python Choices

The first hurdle for Python users is just picking a suitable driver. Even a quick search on pypi.org shows 22 projects with ClickHouse references. They include SQLAlchemy drivers (3 choices), async clients (also 3), and a Pandas-to-ClickHouse interface among others.

Clickhouse-driver offers a straightforward interface that enables Python clients to connect to ClickHouse, issue SELECT and DDL commands, and process results. It’s a good choice for direct Python connectivity with 16 published releases on pypi.org. The latest version is 0.0.17, published on January 10, 2019. If you want to connect to the data warehouse, issue SQL commands, and fetch back data, clickhouse-driver is a great place to start.

Code and Community

The clickhouse-driver source code is published on Github under an MIT license. The main committer is Konstantin Lebedev (@xzkostyan) though there have been a few contributions from others.

Konstantin is very responsive to questions about the driver, which you can register as issues. Much of my understanding of the wire protocol started from Konstantin’s comprehensive responses to an issue related to CSV loading that I filed early on in my use of the code. He has helped a number of other users as well.

Installation

You can of course install clickhouse-driver straight from Github but since releases are posted on pypi.org it’s far easier to use pip, like the example below. Just a note: examples are based on Python 3.7. This installation command includes lz4 compression, which can reduce data transfer sizes enormously.

For testing purposes it’s a best practice to use a virtual environment, which means the installation usually looks like the following example:

If you use Anaconda there is conveniently a clickhouse package in Anaconda Cloud. You can install it with the following command:

After doing this you can use clickhouse-driver in Jupyter Notebooks served up by Anaconda. We will dig more deeply into Anaconda integration in a future blog article. Meanwhile, this should get you started.

Documentation

One of the strengths of clickhouse-driver is excellent documentation. The docs provide a nice introduction to the code as well as detailed descriptions of the API. In fact, it was somewhat challenging to make useful code-level observations for this article because the documentation already covered API behavior so well.

The docs should probably be the first stop for new clickhouse-driver users but are easy to overlook initially since they are referenced at the bottom of the project README.md. I only noticed them after writing a couple of test programs. It would be nice if docs were published in future using Github pages, which puts a prominent link on the top of the Github project. Once you find them though you’ll refer to them regularly.

Basic Operation

Clickhouse-driver is very simple to use. The main interface is the Client class, which most programs import directly.

To set up a connection you instantiate the class with appropriate arguments. Here’s the simplest example for a connection to a localhost server using the default ClickHouse user and unencrypted communications. This is sufficient for trivial tests.

Of course, real applications are more demanding. It’s typical to see something akin to the sample code below. It has a non-default user on a secure connection with self-signed certificates. The database is also different from the usual ‘default’. To top it off we are compressing data.

The option flexibility is great. In particular security options are robust and include basic features corporate InfoSec teams expect. With the foregoing options clickhouse-driver auto-negotiates to TLSv1.2 on a properly configured ClickHouse server. That meets current PCI standards among others. I was also very pleased to find easy support for self-signed certificates, which are common in test scenarios.

Creating a client sets up the connection information but does not actually touch the ClickHouse server. The connection is established when you invoke the Client.execute() method. Here’s an example of a simple SELECT, followed by some code to iterate through the query result so we can see how it is put together.

The output is shown below. It’s a list of tuples containing column values.

The result format has a couple of advantages. First, it’s easy to manipulate in Python. For example, you can just print any part of the output and it will show values, which is handy for debugging. Second, you can use values immediately rather than having to figure out conversions yourselves. That’s handy because Python does not automatically do even relatively simple coercions like str to int in numerical equations.

Let’s quickly tour operations to create a table, load some data, and fetch it back.

Data definition language (DDL) like CREATE TABLE uses a single string argument. The following example splits the string across lines for readability.

INSERT statements take an extra params argument to hold the values, as shown by the following example.

The format for values is the same as the result format for SELECT statements. Clickhouse-driver uses a similar format in both directions. The INSERT params also support dictionary organization as well as generators, as we’ll see in a later section. See the docs for more insert examples.

We already showed an example of a SELECT statement using functions to generate output. Selecting out of a table looks pretty much the same, as shown by the following example.

Clickhouse-driver has a lot of useful features related to SELECTs. For instance, you can enable progress tracking using the Client.execute_with_progress() method, which is great when pulling down large result sets. Similarly the Client.execute_iter() method allows you to chunk results from large datasets to avoid overflowing memory. There’s even cancellation which covers you when somebody accidentally selects a few billion rows. Again, see the docs for examples.

One place where you need to be a little wary is prevention of SQL injection attacks. The procedure for query parameterization uses Python dictionary substitutions, as in the following example.

You might try to circumvent the substitution scheme by setting ‘species’ to a string like “‘Iris-setosa’ AND evil_function() = 0”. The clickhouse-driver cleverly foils this attack by escaping strings and other common data types before doing substitutions. The query ends up looking like the following, which may break but won’t call evil_function() unexpectedly.

This approach will protect you from run-of-the-mill villany with strings but there are ways around it. For instance, it appears possible to pass in Python object types that will not be escaped properly. (Check the driver code here to see why this might be so.) You should review substitution format strings carefully and also check Python parameter types at runtime to ensure something bad does not weasel through. That’s especially the case for Internet-facing applications.

A Deeper Look at the ClickHouse Wire Protocol

This is a good time to discuss what’s actually happening on the wire when communicating between the Python client and ClickHouse. To set context, ClickHouse has two wire protocols: HTTP protocol which uses simple PUT and POST operations to issue queries, and a native TCP/IP protocol that ships data as typed values. These run on different ports so there’s no confusion.

Clickhouse-driver uses the native TCP/IP protocol. This choice is better for Pythonistas because the native protocol knows about types and avoids loss of precision due to binary-to-string conversions. The implementation is correct, at least for the samples that I tried. That is an impressive accomplishment, because the documentation for the native protocol is the C++ implementation code.

As you go deeper into Python access to ClickHouse it’s helpful to understand what the TCP/IP protocol is actually doing. When you run a query, ClickHouse returns results in a binary block format that contains column results in a typed binary format. Here’s an example:

Unlike many databases, ClickHouse results are column-oriented (like the storage). This means that compression works well on query results just as it does on stored values. Compression is invisible to users but can vastly reduce network traffic.

Where ClickHouse differs from many other DBMS implementations is on upload. Let’s look at the INSERT statement again from the previous section.

This format may be a little confusing if you are used to executing INSERT statements as a single string, which is typical for many DBMS types. What you are seeing is a side-effect of the native TCP/IP wire protocol, which ships typed values in both directions. The data values use a column-oriented format, just like the query output.

The TCP/IP protocol has another curious effect, which is that sending INSERTs as a single string won’t even work in clickhouse-driver. It just hangs and will eventually time out.

What’s going on? The server has the first part of the INSERT and is now waiting for data from the client to complete the INSERT in the native protocol. Meanwhile, the client is waiting for the server to respond. This behavior is clearly documented in the clickhouse-driver documentation so one could argue it’s not a bug: you are doing something the protocol does not expect. I don’t completely agree with that view, mostly because it’s confusing to newcomers. This seems like a nice pull request for somebody to work on in future.

But wait, you might ask. The C++ clickhouse-client binary will process an INSERT like the one shown above. How can that possibly work? Well, the trick is that clickhouse-client runs the same code as the ClickHouse server and can parse the query on the client side. It extracts and sends the INSERT statement up to the VALUES clause, waits for the server to send back data types, then converts and sends the data as column-oriented blocks.

Overall the wire protocol is quite reasonable once you understand what is going on. Problems like hanging INSERTs easy to avoid. If you have further questions I suggest firing up WireShark and watching the packets on an unencrypted, uncompressed connection. It’s relatively easy to figure out what’s happening.

Loading CSV

Armed with a better understanding of what the clickhouse-driver is doing under the covers we can tackle a final topic: how to load CSV.

As we now know you can’t just pipe raw CSV into the the driver the way that the clickhouse-client program does it. Fortunately, there’s an easy solution. You can parse CSV into a list of tuples as shown in the following example.

This code works for the Iris dataset values used in this sample, which are relatively simple and automatically parse into types that load properly. For more diverse tables you may need to add additional logic to coerce types. Here’s another approach that works by assigning values in each line to a dictionary. It’s more complex but ensures types are correctly assigned. You can also rearrange the order of columns in the input and do other manipulations to clean up data.

As files run into the 100s of megabytes or more you may want to consider alternatives to Python to get better throughput. Parsing and converting data in Python is relatively slow compared to the C++ clickhouse-client. I would recommend load testing any Python solution for large scale data ingest to ensure you don’t hit bottlenecks.

Summary and Acknowledgments

The clickhouse-driver is relatively young but it is very capable. I am impressed by the thoughtful design, quality of the implementation, and excellent documentation. It looks like a solid base for future Python work with ClickHouse. We’ll review more Python client solutions in the future but for new users, clickhouse-driver is a great place to start.

Thanks to Konstantin Lebedev for reviewing a draft of this article!

Originally published on the Altinity blog on February 1, 2019.

Как записать данные в Clickhouse с помощью Python

Как развернуть быстро Clickhouse с помощью Docker

Как собрать свой образ Clickhouse (docker-compose из оффициальной репы)

В репозитории Github Clickhouse лежит docker-compose.yml файл со следующим содержимым:

Данный файл запускает сборку образов и после сборки запускает контейнеры. Запустится три контейнера:

Сразу скажу, что не очень подходящий способ для использования Clickhouse, т.к. сборка образов требует много времени и лучше использовать уже сформированный официальный образ от Clickhouse.

Но если вы хотите пойти по пути сборки своих образов, то Вам понадобится запустить следующие команды на сервере:

Установка Clickhouse из официального образа с hub.docker.com

Официальная инструкция как задеплоить образ находится здесь https://hub.docker.com/r/clickhouse/clickhouse-server/.

Скачается официальный докер образ (но пока еще не запустится):

Далее необходимо запустить из образа контейнер:

Чтобы проверить, что у Вас запустился контейнер с Clickhouse — запустите команду:

Проверить работу Clickhouse-server можно перейдя по url http : //localhost:8123/ :

Далее запускаем команду:

С помощью этой команды мы подключимся к Clickhouse через native client:

Клиент командной строки

Установка на Ubuntu:

Клиенты и серверы различных версий совместимы, однако если клиент старее сервера, то некоторые новые функции могут быть недоступны. Рекомендуется использовать одинаковые версии клиента и сервера.

Видео «Установка базы данных ClickHouse в виде контейнера Docker»

Установка Clickhouse с помощью docker-compose

Далее создаем папку db, куда clickhouse будет сохранять файлы:

Далее создаем файл docker-compose.yml

Далее запускаем установку с помощью docker-compose:

Зайти внутрь клиента кликхауса можно с помощью команды:

Подключаемся к Clickhouse с помощью DBeaver

Установить dbeaver в Ubuntu можно через Ubuntu Software:

Выбираем коннектор к Clickhouse:

Настройки подключения с дефолтным юзером:

show databases — проверочный запрос к clickhouse:

Интерфейсы для доступа к Clickhouse

ClickHouse имеет богатый набор функций для управления сетевыми подключениями для клиентов, а также для других серверов в кластере. Тем не менее, новым пользователям может быть сложно проработать возможные варианты, а опытным пользователям может быть сложно обеспечить полный доступ к развернутым системам для приложений и их надлежащую защиту.

ClickHouse предоставляет три сетевых интерфейса (они могут быть обернуты в TLS для дополнительной безопасности):

В большинстве случаев рекомендуется использовать подходящий инструмент или библиотеку, а не напрямую взаимодействовать с ClickHouse. Официально поддерживаемые Яндексом:

Существует также широкий спектр сторонних библиотек для работы с ClickHouse:

Что такое http-интерфейс

HTTP интерфейс позволяет использовать ClickHouse на любой платформе, из любого языка программирования. HTTP интерфейс более ограничен по сравнению с родным интерфейсом, но является более совместимым. По умолчанию clickhouse-server слушает HTTP на порту 8123. Запрос отправляется в виде URL параметра с именем query. Или как тело запроса при использовании метода POST. Или начало запроса в URL параметре query, а продолжение POST-ом. Размер URL ограничен 16KB, это следует учитывать при отправке больших запросов.

Порт 8123 является конечной точкой интерфейса HTTP по умолчанию. Вы будете использовать этот порт, если используете команды curl для отправки запросов серверу. Кроме того, ряд библиотек, таких как JDBC-драйвер Yandex ClickHouse, скрытно используют HTTP-запросы, так что вы можете использовать http-интерфейс, даже не подозревая об этом.

Что такое Native TCP (Родной интерфейс)

Нативный протокол используется в клиенте командной строки, для взаимодействия между серверами во время обработки распределенных запросов, а также в других программах на C++. К сожалению, у родного протокола ClickHouse пока нет формальной спецификации.

Порт 9000 является конечной точкой Native TCP интерфейса (по-умолчанию). Он широко используется клиентами, как показано на следующих примерах.

Что такое gRPC

ClickHouse поддерживает интерфейс gRPC. Это система удаленного вызова процедур с открытым исходным кодом, которая использует HTTP/2 и Protocol Buffers.

gRPC — мощный фреймворк для работы с удаленными вызовами процедур. RPC позволяют писать код так, как если бы он был запущен на локальном компьютере, даже если он может выполняться на другом компьютере.

Как правило, gRPC считается лучшей альтернативой протоколу REST для микросервисной архитектуры. Букву g в gRPC можно отнести к компании Google, которая изначально разработала эту технологию. gRPC создан для преодоления ограничений REST в связи с микросервисами.

gRPC — это новейшая структура, созданная на основе протокола RPC. Он использует свои преимущества и пытается исправить проблемы традиционного RPC. gRPC использует буферы протокола в качестве языка определения интерфейса для сериализации и связи вместо JSON/XML.

Буферы протокола могут описывать структуру данных, и на основе этого описания может быть сгенерирован код для генерации или анализа потока байтов, представляющего структурированные данные. По этой причине gRPC предпочтительнее для многоязычных веб-приложений (реализованных с использованием различных технологий). Формат двоичных данных позволяет облегчить общение. gRPC также можно использовать с другими форматами данных, но предпочтительным является буфер протокола.

Кроме того, gRPC построен на основе HTTP/2, который поддерживает двунаправленную связь наряду с традиционным запросом/ответом. gRPC допускает слабую связь между сервером и клиентом. На практике клиент открывает долговременное соединение с сервером gRPC, и новый поток HTTP/2 открывается для каждого вызова RPC.

В отличие от REST, который использует JSON (в основном), gRPC использует буферы протокола, которые являются лучшим способом кодирования данных. Поскольку JSON — это текстовый формат, он будет намного тяжелее, чем сжатые данные в формате protobuf.

Network Listener Configuration

ClickHouse позволяет легко включать и отключать порты слушателей, а также назначать им новые номера. Для каждого типа порта существуют простые теги config.xml, как показано в следующей таблице. Столбец обычных значений показывает номер порта, который большинство клиентов предполагает для определенного соединения. Если вы измените значение, вам может потребоваться соответствующее изменение клиентов.

Тег	Описание	Условное значение
http_port	Порт для незашифрованных HTTP-запросов	8123
https_port	Порт для зашифрованных запросов HTTPS	8443
interserver_http_port	Порт для незашифрованного трафика HTTP-репликации	9009
interserver_https_port	Порт для зашифрованного трафика репликации HTTPS
tcp_port	Порт для незашифрованных собственных запросов TCP/IP	9000
tcp_port_secure	Порт для зашифрованных TLS собственных запросов TCP/IP	9440

Как создать database в Clickhouse, таблицу и вставить тестовые данные

Идем в dbeaver и запускаем скрипты.

1. Создаем базу данных в Clickhouse

ClickHouse and Python: Getting to Know the Clickhouse-driver Client

Fortunately the Altinity Blog is here to solve mysteries, at least those that involve ClickHouse. This post contains a review of the clickhouse-driver client. It’s a solidly engineered module that is easy to use and integrates easily with standard tools like Jupyter Notebooks and Anaconda. Clickhouse-driver is a great way to jump into ClickHouse Python connectivity.

So Many Python Choices

Code and Community

The clickhouse-driver source code is published on Github under an MIT license. The main committer is Konstantin Lebedev (@xzkostyan) though there have been a few contributions from others.

Installation

For testing purposes it’s a best practice to use a virtual environment, which means the installation usually looks like the following example:

If you use Anaconda there is conveniently a clickhouse package in Anaconda Cloud. You can install it with the following command:

After doing this you can use clickhouse-driver in Jupyter Notebooks served up by Anaconda. We will dig more deeply into Anaconda integration in a future blog article. Meanwhile this should get you started.

Documentation

Basic Operation

Clickhouse-driver is very simple to use. The main interface is the Client class, which most programs import directly.

Of course real applications are more demanding. It’s typical to see something akin to the sample code below. It has a non-default user on a secure connection with self-signed certificates. The database is also different from the usual ‘default’. To top it off we are compressing data.

The output is shown below. It’s a list of tuples containing column values.

The result format has a couple of advantages. First, it’s easy to manipulate in Python. For example you can just print any part of the output and it will show values, which is handy for debugging. Second, you can use values immediately rather than having to figure out conversions yourselves. That’s handy because Python does not automatically do even relatively simple coercions like str to int in numerical equations.

Let’s quickly tour operations to create a table, load some data, and fetch it back.

Data definition language (DDL) like CREATE TABLE uses a single string argument. The following example splits the string across lines for readability.

INSERT statements take an extra params argument to hold the values, as shown by the following example.

We already showed an example of a SELECT statement using functions to generate output. Selecting out of a table looks pretty much the same, as shown by the following example.

One place where you need to be a little wary is prevention of SQL injection attacks. The procedure for query parameterization uses Python dictionary substitutions, as in the following example.

A Deeper Look at the ClickHouse Wire Protocol

Unlike many databases ClickHouse results are column-oriented (like the storage). This means that compression works well on query results just as it does on stored values. Compression is invisible to users but can vastly reduce network traffic.

Where ClickHouse is differs from many other DBMS implementations is on upload. Let’s look at the INSERT statement again from the previous section.

This format may be a little confusing if you are used to executing INSERT statements as a single string, which is typical for many DBMS types. What you are seeing is a side-effect of the native TCP/IP wire protocol, which ships typed values in both directions. The data values use a column-oriented format, just like the query output.

The TCP/IP protocol has another curious effect, which is that sending INSERTs as a single string won’t even work in clickhouse-driver. It just hangs and will eventually time out.

But wait, you might ask. The C++ clickhouse-client binary will process an INSERT like the one shown above. How can that possibly work? Well, the trick is that clickhouse-client runs the same code as the ClickHouse server and can parse the query on the client side. It extracts and sends the INSERT statement up to the VALUES clause, waits for the server to send back data types, then converts and sends the data as column-oriented blocks.

Loading CSV

Armed with a better understanding of what the clickhouse-driver is doing under the covers we can tackle a final topic: how to load CSV.

Summary and Acknowledgments

Thanks to Konstantin Lebedev for reviewing a draft of this article!

Python clickhouse driver

Copy raw contents

This page gives a good introduction to clickhouse-driver. It assumes you already have clickhouse-driver installed. If you do not, head over to the :ref:`installation` section.

A minimal working example looks like this:

This code will show all tables from ‘default’ database.

There are two conceptual types of queries:

Every query should be executed by calling one of the client’s execute methods: execute, execute_with_progress, execute_iter method.

Simple select query looks like:

Of course queries can and should be parameterized to avoid SQL injections:

Percent symbols in inlined constants should be doubled if you mix constants with % symbol and %(myvar)s parameters.

NOTE: formatting queries using Python’s f-strings or concatenation can lead to SQL injections. Use %(myvar)s parameters instead.

Customisation SELECT output with FORMAT clause is not supported.

Selecting data with progress statistics

You can get query progress statistics by using execute_with_progress. It can be useful for cancelling long queries.

When you are dealing with large datasets block by block results streaming may be useful:

Insert queries in Native protocol are a little bit tricky because of ClickHouse’s columnar nature. And because we’re using Python.

INSERT query consists of two parts: query statement and query values. Query values are split into chunks called blocks. Each block is sent in binary columnar form.

As data in each block is sent in binary we should not serialize into string by using substitution %(a)s and then deserialize it back into Python types.

This INSERT would be extremely slow if executed with thousands rows of data:

To insert data efficiently, provide data separately, and end your statement with a VALUES clause:

You can use any iterable yielding lists, tuples or dicts.

If data is not passed, connection will be terminated after a timeout.

The following WILL NOT work:

ClickHouse will execute this query like a usual SELECT query.

Inserting data in different formats with FORMAT clause is not supported.

See :ref:`insert-from-csv-file` if you need to data in custom format.

DDL queries can be executed in the same way SELECT queries are executed:

Async and multithreading

To utilize ClickHouse’s asynchronous capability you should either use multiple Client instances or implement a queue.

The same thing is applied to multithreading. Queries from different threads can’t use one Client instance with single connection. You should use different clients for different threads.

However, if you are using DB API for communication with the server each cursor create its own Client instance. This makes communication thread-safe.

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

QuickstartВ¶

This page gives a good introduction to clickhouse-driver. It assumes you already have clickhouse-driver installed. If you do not, head over to the Installation section.

A minimal working example looks like this:

This code will show all tables from ‘default’ database.

There are two conceptual types of queries:

Selecting dataВ¶

Simple select query looks like:

Of course queries can and should be parameterized to avoid SQL injections:

Selecting data with progress statisticsВ¶

Streaming resultsВ¶

When you are dealing with large datasets block by block results streaming may be useful:

Inserting dataВ¶

Insert queries in Native protocol are a little bit tricky because of ClickHouseвЂ™s columnar nature. And because weвЂ™re using Python.

INSERT query consists of two parts: query statement and query values. Query values are split into chunks called blocks. Each block is sent in binary columnar form.

As data in each block is sent in binary we should not serialize into string by using substitution %(a)s and then deserialize it back into Python types.

This INSERT would be extremely slow if executed with thousands rows of data:

To insert data efficiently, provide data separately, and end your statement with a VALUES clause:

You can use any iterable yielding lists, tuples or dicts.

If data is not passed, connection will be terminated after a timeout.

The following WILL NOT work:

Of course for INSERT вЂ¦ SELECT queries data is not needed:

ClickHouse will execute this query like a usual SELECT query.

DDL queries can be executed in the same way SELECT queries are executed:

Asynchronous behaviorВ¶

Every ClickHouse query is assigned an identifier to enable request execution tracking. However, ClickHouse native protocol is synchronous: all incoming queries are executed consecutively. Clickhouse-driver does not yet implement a connection pool. To utilize ClickHouseвЂ™s asynchronous capability you should either use multiple Client instances or implement a queue.

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

This part of the documentation covers basic classes of the driver: Client, Connection and others.

ClientВ¶

Client for communication with the ClickHouse server. Single connection is established per each connected instance of the client.

The following keys when passed in settings are used for configuring the client itself:

Disconnects from the server.

execute ( query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False, columnar=False ) В¶

Establishes new connection if it wasnвЂ™t established yet. After query execution connection remains intact for next queries. If connection canвЂ™t be reused it will be closed and new connection will be created.

execute_iter ( query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False ) В¶

New in version 0.0.14.

execute_with_progress ( query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False, columnar=False ) В¶

classmethod from_url ( url ) В¶

Return a client configured from the given URL.

Any additional querystring arguments will be passed along to the Connection classвЂ™s initializer.

insert_dataframe ( query, dataframe, external_tables=None, query_id=None, settings=None ) В¶

New in version 0.2.0.

Inserts pandas DataFrame with specified query.

number of inserted rows.

query_dataframe ( query, params=None, external_tables=None, query_id=None, settings=None ) В¶

New in version 0.2.0.

Queries DataFrame with specified SELECT query.

ConnectionВ¶

Represents connection between client and ClickHouse server.

Closes connection between server and client. Frees resources: e.g. closes socket.

QueryResultВ¶

Stores query result from multiple blocks.

get_result ( ) В¶

Returns:	stored query result.

ProgressQueryResultВ¶

Stores query result and progress information from multiple blocks. Provides iteration over query progress.

get_result ( ) В¶

Returns:	stored query result.

IterQueryResultВ¶

Provides iteration over returned data by chunks (streaming by chunks).

Immowelt/PyClickhouse

Use Git or checkout with SVN using the web URL.

Work fast with our official CLI. Learn more.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

Minimalist Clickhouse Python driver with an API roughly resembling Python DB API 2.0 specification.

To develop or run anything in this project, it is recommended to setup a virtual environment using the provided Pipfile:

This will recreate the virtual environment as well, if necessary.

Makefile and running tests

The Makefile target test is provided to run the project’s tests. These require access to a running instance of Clickhouse, which is provided through docker. This assumes that docker is installed and the current user can use it without sudo.

A one-liner to run the tests in the virtual environment would be:

About

Minimalist Clickhouse Python driver with an API roughly resembling Python DB API 2.0 specification.

ClickHouse/clickhouse-connect

Use Git or checkout with SVN using the web URL.

Work fast with our official CLI. Learn more.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

A suite of Python packages for connecting Python to ClickHouse, initially supporting Apache Superset using a minimal read only SQLAlchemy dialect. Uses the ClickHouse HTTP interface.

Simple ‘command’ that does not return a result set.

Bulk insert of a matrix of rows and columns.

Minimal SQLAlchemy Support

ClickHouse Connect does not yet implement the full SQLAlchemy API for DDL (Data Definition Language) or ORM (Object Relational Mapping). These features are in development.

Query results can be returned as either a numpy array or a pandas DataFrame if the numpy and pandas libraries are available. Use the client methods query_np and query_df respectively.

Main Client Interface

Interaction with the ClickHouse server is done through a clickhouse_connect Client instance. At this point only an HTTP(s) based Client is supported.

HTTP Client constructor/initialization parameters

Create a ClickHouse client using the clickhouse_connect.driver.create_client(. ) function or clickhouse_connect.get_client(. ) wrapper. All parameters are optional:

Any remaining keyword parameters are interpreted as ‘setting’ parameters to send to the ClickHouse server with every query/request

Use the client query method to retrieve a QueryResult from ClickHouse. Parameters:

The query method results a QueryResult object with the following fields:

Numpy and Pandas queries

Datatype options for queries

There are some convenience methods in the clickhouse_connect.driver package that control the format of some ClickHouse datatypes. These are included in part to improve Superset compatibility.

Use the client insert method to insert data into a ClickHouse table. Parameters:

Notes on data inserts

DDL and other «simple» SQL statements

About

Python driver/sqlalchemy/superset connectors

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

FeaturesВ¶

External data for query processingВ¶

You can pass external data alongside with query:

SettingsВ¶

There are a lot of ClickHouse server settings. Settings can be specified during Client initialization:

Each setting can be overridden in an execute statement:

CompressionВ¶

Client with compression support can be constructed as follows:

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В¶

Specifying query idВ¶

You can manually set query identificator for each query. UUID for example:

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.

Data types checking on INSERTВ¶

Data types check is disabled for performance on INSERT queries. You can turn it on by types_check option:

Query execution statisticsВ¶

profile: rows before limit

Receiving server logsВ¶

Query logs can be received from server by using send_logs_level setting:

Multiple hostsВ¶

New in version 0.1.3.

This option is good for ClickHouse cluster with multiple replicas.

In example above on every new connection driver will use following sequence of hosts if previous host is unavailable:

All queries within established connection will be sent to the same host.

Python DB API 2.0В¶

New in version 0.1.3.

This driver is also implements DB API 2.0 specification. It can be useful for various integrations.

Threads may share the module and connections.

Connection class is just wrapper for handling multiple cursors (clients) and do not initiate actual connections to the ClickHouse server.

There are some non-standard ClickHouse-related Cursor methods for: external data, settings, etc.

For automatic disposal Connection and Cursor instances can be used as context managers:

Python clickhouse driver

Clickhouse-driver supports Python 3.4 and newer and PyPy.

Starting from version 0.1.0 for building from source gcc, python and linux headers are required.

Example for python:alpine docker image:

By default there are wheels for Linux, Mac OS X and Windows.

Starting from version 0.2.3 there are wheels for musl-based Linux distributions.

These distributions will be installed automatically when installing clickhouse-driver.

These distributions will not be installed automatically. Clickhouse-driver will detect and use them if you install them.

Installation from PyPI

The package can be installed using pip :

You can install extras packages if you need compression support. Example of LZ4 compression requirements installation:

You also can specify multiple extras by using comma. Install LZ4 and ZSTD requirements:

You can install additional packages (NumPy and Pandas) if you need NumPy support:

NumPy supported versions are limited by numpy package python support.

Installation from github

Development version can be installed directly from github:

long2ice/asynch

Use Git or checkout with SVN using the web URL.

Work fast with our official CLI. Learn more.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

asynch is an asyncio ClickHouse Python Driver with native (TCP) interface support, which reuse most of clickhouse-driver and comply with PEP249.

Connect to ClickHouse

Create table by sql

Use DictCursor to get result with dict

Insert data with dict

Insert data with tuple

Use connection pool

This project is licensed under the Apache-2.0 License.

About

An asyncio ClickHouse Python Driver with native (TCP) interface support.

Topics

Resources

License

Stars

Watchers

Forks

Releases 10

Packages 0

Contributors 6

Languages

Footer

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

aio-clickhouse 0.0.5

pip install aio-clickhouse Copy PIP instructions

Released: Nov 19, 2021

Library for accessing a ClickHouse database over native interface from the asyncio

Navigation

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: MIT License (MIT)

Tags ClickHouse, db, database, cloud, analytics, asyncio

Maintainers

Classifiers

Project description

aioch

aioch is a library for accessing a ClickHouse database over native interface from the asyncio. It wraps features of clickhouse-driver for asynchronous usage.

Installation

The package can be installed using pip :

Usage

For more information see clickhouse-driver usage examples.

Parameters

Other parameters are passing to wrapped clickhouse-driver’s Client.

License

aioch is distributed under the MIT license.

Project details

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: MIT License (MIT)

Tags ClickHouse, db, database, cloud, analytics, asyncio

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

Welcome to clickhouse-driverВ¶

Welcome to clickhouse-driverвЂ™s documentation. Get started with Installation and then get an overview with the Quickstart where common queries are described.

UserвЂ™s GuideВ¶

This part of the documentation focuses on step-by-step instructions for development with clickhouse-driver.

Clickhouse-driver is designed to communicate with ClickHouse server from Python over native protocol.

ClickHouse server provider two protocols for communication: HTTP protocol and Native (TCP) protocol.

Each protocol has own advantages and disadvantages. Here we focus on advantages of native protocol:

There is an asynchronous wrapper for clickhouse-driver: aioch. ItвЂ™s available here.

API ReferenceВ¶

If you are looking for information on a specific function, class or method, this part of the documentation is for you.

Additional NotesВ¶

Legal information, changelog and contributing are here for the interested.

pavelmaksimov/clickhousepy

Use Git or checkout with SVN using the web URL.

Work fast with our official CLI. Learn more.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

Python wrapper for database queries Clickhouse

The wrapper is done around clickhouse-driver

Written in python version 3.5

Getting Data from Clickhouse in Pandas Dataframe Format

Brief documentation of some methods

Method of copying data from one table to another with checking the number of rows after copying

A method of copying data from one table to another while removing duplicate rows.

You can contact me at Telegram, Facebook

Удачи тебе, друг! Поставь звездочку 😉

About

Python обертка для запросов в БД Clickhouse

Topics

Resources

License

Stars

Watchers

Forks

Releases 7

Packages 0

Contributors 2

Languages

Footer

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

maximdanilchenko/aiochclient

Use Git or checkout with SVN using the web URL.

Work fast with our official CLI. Learn more.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

An async http(s) ClickHouse client for python 3.6+ supporting type conversion in both directions, streaming, lazy decoding on select queries, and a fully typed interface.

Table of Contents

You can use it with either aiohttp or httpx http connectors.

To use with aiohttp install it with command:

Or aiochclient[aiohttp-speedups] to install with extra speedups.

To use with httpx install it with command:

Or aiochclient[httpx-speedups] to install with extra speedups.

Installing with [*-speedups] adds the following:

Additionally the installation process attempts to use Cython for a speed boost (roughly 30% faster).

Connecting to ClickHouse

aiochclient needs aiohttp.ClientSession or httpx.AsyncClient to connect to ClickHouse:

Querying the database

For fetching all rows at once use the fetch method:

For fetching first row from result use the fetchrow method:

You can also use fetchval method, which returns first value of the first row from query result:

With async iteration on the query results stream you can fetch multiple rows without loading them all into memory at once:

Use fetch / fetchrow / fetchval / iterate for SELECT queries and execute or any of last for INSERT and all another queries.

Working with query results

All fetch queries return rows as lightweight, memory efficient objects. Before v 1.0.0 rows were only returned as tuples. All rows have a full mapping interface, where you can get fields by names or indexes:

To check out the api docs, visit the readthedocs site..

aiochclient automatically converts types from ClickHouse to python types and vice-versa.

Connection Pool Settings

aiochclient uses the aiohttp.TCPConnector to determine pool size. By default, the pool limit is 100 open connections.

It’s highly recommended using uvloop and installing aiochclient with speedups for the sake of speed. Some recent benchmarks on our machines without parallelization:

Note: these benchmarks are system dependent

About

Lightweight async http(s) ClickHouse client for python 3.6+ with types converting

mymarilyn/clickhouse-driver

Use Git or checkout with SVN using the web URL.

Work fast with our official CLI. Learn more.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.rst

ClickHouse Python Driver

ClickHouse Python Driver with native (TCP) interface support.

Asynchronous wrapper is available here: https://github.com/mymarilyn/aioch

There are two ways to communicate with server:

Pure Client example:

ClickHouse Python Driver is distributed under the MIT license.

About

ClickHouse Python Driver with native interface support

Ускоряем драйвер ClickHouse

ClickHouse – самая быстрая в мире аналитическая СУБД. Для тех, кто с ним ещё не знаком, очень рекомендую попробовать, пересаживаться обратно на MySQL или Postgress потом не захочется.

Обычно данные хранятся в ClickHouse в сыром, неагрегированном виде, и агрегируются на лету при выполнении SQL запросов. Но при решении data science задач часто возникает необходимость выгрузки именно сырых данных, для дальнейшей их обработки в памяти (например, для обучения модели по этим данным). Если выгружать данные в текстовый файл с помощью родного клиента ClickHouse, всё происходит достаточно шустро – “ClickHouse не тормозит”™. Но если пользоваться драйвером для Python, то процесс выгрузки затягивается надолго. Почему?

Но Python представляет все числа, как объекты. Это означает, что драйвер проходит по загруженным данным, преобразует каждое число в объект, и потом уже из этих объектов собирает питоновский массив (состоящий из указателей). Такая операция называется boxing, и при больших объемах данных она отнимает значительное время. Собственно, в ходе загрузки данных через Python-драйвер основное занятие CPU это переупаковка чисел из машинного представления в объекты.

В то же время в data science принято работать c numpy массивами (pandas тоже работает через numpy), которые содержат числа в машинном представлении, как в С. То есть, сначала мы долго упаковывали числа в объекты, а потом, при конвертировании из Python массива в numpy массив будем распаковывать объекты обратно в числа (unboxing). Очевидно, что промежуточное объектное представление здесь только мешает, и если бы драйвер умел выгружать данные сразу в numpy массивы, процесс пошёл бы намного бодрее. Но драйвер этого не умеет, поэтому я его немного доработал, чтобы такая возможность появилась.

Инсталляция

Использование

В data будет содержаться набор колонок. Колонки, представляющие собой числа или timestamp, будут numpy-массивами, остальные колонки (например, строки) будут стандартными Python массивами. В numpy формат конвертируются следующие типы Clickhouse: Int8/16/32/64, UInt8/16/32/64, DateTime.

Полученные данные часто преобразуются в pandas DataFrame с именами колонок, соответствующими именам колонок в БД. Чтобы не делать это каждый раз вручную, в класс Client добавлен метод query_dataframe() :

Результатом будет DataFrame с двумя колонками, a и b.

Benchmarks

Замерялась скорость выполнения запроса SELECT x1,x2. xn FROM table на таблице со 100 млн. записей (реальные данные из Logs API Яндекс.Метрики), engine=MergeTree. Запросы выполнялись на локальном ClickHouse c дефолтными настройками драйвера.

Запрос	Время, numpy	Время, standard	Ускорение	Memory, numpy	Memory, standard
4 колонки Int8	0.34 s	5.8 s	×17	0.82 Gb	3.3 Gb
2 колонки Int64	1.38 s	12 s	×8.7	2.61 Gb	9.7 Gb
1 колонка DateTime	12.1 s	7.1 m	×35	1.16 Gb	4.8 Gb

Использование numpy ускоряет чтение на порядок. Особенно заметно ускорение на типе DateTime, потому что работа c временем на уровне Питоновских datetime-объектов происходит очень медленно. Фактически, без использования numpy время выполнения запроса, включающего колонку со временем, выходит за рамки разумного.

В последних двух колонках – объём памяти, занимаемый процессом после выполнения запроса. Видно, что использование numpy не только ускоряет загрузку данных, но и уменьшает объём требуемой памяти примерно в 4 раза.

Ограничения

Ограничения на чтение никак не мешают функционированию драйвера, просто для некоторых типов данных чтение ускоряется, а для некоторых – работает, как обычно.

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

This part of the documentation covers basic classes of the driver: Client, Connection and others.

ClientВ¶

Client for communication with the ClickHouse server. Single connection is established per each connected instance of the client.

Parameters:	settings вЂ“ Dictionary of settings that passed to every query. Defaults to None (no additional settings). See all available settings in ClickHouse docs.

Disconnects from the server.

execute ( query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False, columnar=False ) В¶

execute_iter ( query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False ) В¶

New in version 0.0.14.

execute_with_progress ( query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False, columnar=False ) В¶

classmethod from_url ( url ) В¶

Return a client configured from the given URL.

Any additional querystring arguments will be passed along to the Connection classвЂ™s initializer.

ConnectionВ¶

Represents connection between client and ClickHouse server.

Closes connection between server and client. Frees resources: e.g. closes socket.

QueryResultВ¶

Stores query result from multiple blocks.

get_result ( ) В¶

Returns:	stored query result.

ProgressQueryResultВ¶

Stores query result and progress information from multiple blocks. Provides iteration over query progress.

get_result ( ) В¶

Returns:	stored query result.

IterQueryResultВ¶

Provides iteration over returned data by chunks (streaming by chunks).

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

FeaturesВ¶

External data for query processingВ¶

You can pass external data alongside with query:

SettingsВ¶

There are a lot of ClickHouse server settings. Settings can be specified during Client initialization:

Each setting can be overridden in an execute statement:

CompressionВ¶

Client with compression support can be constructed as follows:

CityHash algorithm notesВ¶

Secure connectionВ¶

Specifying query idВ¶

You can manually set query identificator for each query. UUID for example:

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.

Data types checking on INSERTВ¶

Data types check is disabled for performance on INSERT queries. You can turn it on by types_check option:

Query execution statisticsВ¶

profile: rows before limit

Receiving server logsВ¶

Query logs can be received from server by using send_logs_level setting:

Multiple hostsВ¶

New in version 0.1.3.

This option is good for ClickHouse cluster with multiple replicas.

In example above on every new connection driver will use following sequence of hosts if previous host is unavailable:

All queries within established connection will be sent to the same host.

Python DB API 2.0В¶

New in version 0.1.3.

This driver is also implements DB API 2.0 specification. It can be useful for various integrations.

Threads may share the module and connections.

Connection class is just wrapper for handling multiple cursors (clients) and do not initiate actual connections to the ClickHouse server.

There are some non-standard ClickHouse-related Cursor methods for: external data, settings, etc.

For automatic disposal Connection and Cursor instances can be used as context managers:

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

This part of the documentation covers basic classes of the driver: Client, Connection and others.

ClientВ¶

Client for communication with the ClickHouse server. Single connection is established per each connected instance of the client.

The following keys when passed in settings are used for configuring the client itself:

Disconnects from the server.

execute ( query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False, columnar=False ) В¶

execute_iter ( query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False ) В¶

New in version 0.0.14.

execute_with_progress ( query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False, columnar=False ) В¶

classmethod from_url ( url ) В¶

Return a client configured from the given URL.

Any additional querystring arguments will be passed along to the Connection classвЂ™s initializer.

insert_dataframe ( query, dataframe, transpose=True, external_tables=None, query_id=None, settings=None ) В¶

New in version 0.2.0.

Inserts pandas DataFrame with specified query.

number of inserted rows.

query_dataframe ( query, params=None, external_tables=None, query_id=None, settings=None ) В¶

New in version 0.2.0.

Queries DataFrame with specified SELECT query.

ConnectionВ¶

Represents connection between client and ClickHouse server.

Closes connection between server and client. Frees resources: e.g. closes socket.

QueryResultВ¶

Stores query result from multiple blocks.

get_result ( ) В¶

Returns:	stored query result.

ProgressQueryResultВ¶

Stores query result and progress information from multiple blocks. Provides iteration over query progress.

get_result ( ) В¶

Returns:	stored query result.

IterQueryResultВ¶

Provides iteration over returned data by chunks (streaming by chunks).

mymarilyn / clickhouse-driver Goto Github PK

ClickHouse Python Driver with native interface support

clickhouse-driver’s Introduction

ClickHouse Python Driver

ClickHouse Python Driver with native (TCP) interface support.

Asynchronous wrapper is available here: https://github.com/mymarilyn/aioch

There are two ways to communicate with server:

Pure Client example:

ClickHouse Python Driver is distributed under the MIT license.

clickhouse-driver’s People

Contributors

Stargazers

Watchers

Forkers

clickhouse-driver’s Issues

Support Interval Types

Recent Clickhouse supports intervals / timedeltas but they have special types.

input_format_skip_unknown_fields setting seems to have no effect

According to the Clickhouse documentation, an exception should be raised if input_format_skip_unknown_fields is set to false

I can reproduce this behavior as expected with clickhouse-client, but clickhouse-driver seems to ignore this setting. In the example below, «z» is not part of the schema.

clickhouse-driver==0.0.10
ClickHouse server version 1.1.54362

output: (no exception rasied in either case)

Conda forge feedstock

I’m working on clickhouse backend for ibis.
Ibis is installable from both pip and conda. I’d like to use clickhouse-driver, but currently clickhouse-driver and clickhouse-cityhash don’t have conda packages.

I’ve already created the recipes, but conda forge packages require maintainers. Would You please create the feedstocks?

DBAPI Support

Thanks for the hard working on this great project.

Does this driver already implement the DBAPI? If not, do we have a plan?

Wrong DateTime insert

After insert datetime.datetime(2018, 1, 19, 10) through this driver I see ‘2018-01-19 13:00:00’ value in table.
Timezone on my computer and clickhouse server is Moskow.

What I must do to see ‘2018-01-19 10:00:00’ after insert?

Pandas interop

@xzkostyan would You like to include pandas support?

Doesn’t work with ipv6-only hosts

SELECT INTO OUTFILE

current version of driver ignore select into outfile?
there are no difference between queries
select * from log LIMIT 10000 INTO OUTFILE ‘/var/tmp/test123.csv’ FORMAT TabSeparated
and
select * from log LIMIT 10000

clickhouse-driver version 0.0.16

Feature ‘format JSON’ needed

Can you provide ‘format JSON’ as the clickhouse-client does?

Extended support of IPv4 and IPv6 column types

I am afraid to come with a form of specific request here. I opened this issue last week on Yandex/clickhouse repository: ClickHouse/ClickHouse#2605. It was about issues with support of specific column and types to store IPv4 and IPv6 data.

I didn’t get any sort of positive answer from them. at least short term.

I was wondering if it could make sense to develop a form of additional types in your code such that column named IPv4_. or IPv6_. benefits from a specific behaviour. the idea would be to apply conversion function in you code and introduce those column as type INET (similar to postGRESQL) : https://www.postgresql.org/docs/9.1/static/datatype-net-types.html.

By having this feature, This type could be handled by upper layers like your SQLAlchemy driver and related UI and Frontend.

Memory Overflow

I’m running Clickhouse on production, using the asyncio wrapper. But sometimes I get a issue when inserting into database.

I don’t know if the issue is the server or the client.

execute wait long time

[[email protected] tmp]# python
Python 2.6.6 (r266:84292, Aug 18 2016, 15:13:37)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-17)] on linux2
Type «help», «copyright», «credits» or «license» for more information.

from clickhouse_driver import Client
client = Client(‘192.168.133.2′,18123,’client_report’,’admin’,’************’,insert_block_size=1)
client.execute(‘SHOW TABLES’)
^CTraceback (most recent call last):
File «», line 1, in
File «/usr/lib/python2.6/site-packages/clickhouse_driver/client.py», line 159, in execute
self.connection.force_connect()
File «/usr/lib/python2.6/site-packages/clickhouse_driver/connection.py», line 122, in force_connect
self.connect()
File «/usr/lib/python2.6/site-packages/clickhouse_driver/connection.py», line 188, in connect
self.receive_hello()
File «/usr/lib/python2.6/site-packages/clickhouse_driver/connection.py», line 263, in receive_hello
packet_type = read_varint(self.fin)
File «/usr/lib/python2.6/site-packages/clickhouse_driver/reader.py», line 29, in read_varint
i = _read_one(f)
File «/usr/lib/python2.6/site-packages/clickhouse_driver/reader.py», line 14, in _read_one
c = f.read(1)
File «/usr/lib64/python2.6/socket.py», line 383, in read
data = self._sock.recv(left)
KeyboardInterrupt

Extremely slow on large select, http protocol almost 10 times faster

It seems that selecting large datasets using the native client is extremely slow. Here is my benchmark https://gist.github.com/dmitriyshashkin/6a4849bdcf882ba340cdfbc1990da401

Initially, I’ve encountered this behavior on my own dataset, but I was able to reproduce it using the dataset and the structure described here https://clickhouse.yandex/docs/en/getting_started/example_datasets/ontime/

To simplify things a little bit I’ve used the data for just one month: http://transtats.bts.gov/PREZIP/On_Time_On_Time_Performance_2017_12.zip

As you can see the fastest way to get the data is by using HTTP protocol with requests and pandas. The problem gets worse as the number of the rows grows, on my own dataset with 5M rows I waited for 1 hour before I had to interrupt the process. The bottleneck is not CH itself, the «top» command shows that all the work is done by python with 100% CPU utilization, while CH is almost idle.

Set query settings

If a setting is set to a value different from the default one, then it should be sent to the server.
It could be done in connection.send_query() before write_binary_str(», self.fout) # end of query settings

Better support for writing bytes

insert data into Date column face error

when i try insert into Datetime format column, it’s success.

how to fix this problem? thanks.

Timeout error

I’m getting a timeout error when trying to fetch results from clickhouse using this package on windows.
Any ideas how I can fix it?

Ps.
I checked this connection credentials via jdbc-driver

Error if enum key is blank

How to reproduce

Create table with enum field and insert a few rows

Try to select with python driver:

Links related issues

Do you need to explicitly close the connection using clickhouse_driver?

Set settings.limits

Multithreaded client

I’ve had some issues with using clickhouse_driver in a multithreaded asyncio environment. The connection seems not to be thread safe. I’m not sure that is the best approach to solve the issues I’ve encountered, but here is a pooled connection implementation I am using:

column/string value may be None NoneType’ object has no attribute ‘encode’

column/string value may be None

def try_encode(self, value):
if not isinstance(value, bytes):
return value.encode(‘utf-8’)
return value

Unknown type Tuple(Float64, Float64)

Can’t execute query with column of type Array(Tuple(Float64, Float64)) as result.

/.pyenv/versions/jupyter3.6.4/lib/python3.6/site-packages/clickhouse_driver/columns/service.py in read_column(context, column_spec, n_items, buf) 65 def read_column(context, column_spec, n_items, buf): 66 column_options = <'context': context>—> 67 column = get_column_by_spec(column_spec, column_options=column_options) 68 return column.read_data(n_items, buf) 69

Accessing rows_before_limit property through API

The BlockStreamProfileInfo.rows_before_limit property is useful to get the rows count for pagination without running an extra query, but there does not seem to be any way to access it through the API (i.e. the client silently ignores the PROFILE_INFO packet).

As a workaround I hacked together a small change in Client.receive_packet where it saves the last packet.profile_info in the Client instance and we can use it like this:

So it’s not pretty, but it seems to work fine. Anyway, I think it would make sense to have it in the main API without additional hacks, but I’m not sure what’s the best place to put it in. Do you have any thoughts on that? Or perhaps are there any additional caveats that might have caused leaving this out of the API scope?

Error writing string while using supserset and clickhouse driver (text encode not set to UTF-8)

Something new is happening while adding a clickhouse db to supserset at the time superset is trying to get table meta data.

Get progress info

It seems the Progress packets are received and managed but there is no way to get the info from the Client or Connection objects. Here an API proposition with a fetch* method, this is common on a database API.

AttributeError when receiving ServerPacketTypes.PROFILE_INFO

After updating to 0.0.16 the following error appears:

AttributeError: ‘Client’ object has no attribute ‘execute_iter’

looks like there is no execute_iter method in version ‘0.0.10’.

from clickhouse_driver import Client

client = Client(host=’localhost’, port=2102, database=’shard01′)

rows_gen = client.execute_iter(‘select * from query.TCC_S1’, settings=settings)

AttributeError Traceback (most recent call last)
in ()
2 client = Client(host=’localhost’, port=2102, database=’shard01′)
3 settings = <'max_block_size': 100000>
—-> 4 rows_gen = client.execute_iter(‘select * from query.TCC_S1’, settings=settings)

AttributeError: ‘Client’ object has no attribute ‘execute_iter’

SQL injections?

Thanks for this great library. I was thinking whether it has any protection against SQL injection? https://github.com/mymarilyn/clickhouse-driver/blob/master/src/util/escape.py does not seem to have any checks for injected queries.

Iterator support

Is there any way that the return of a SELECT query would be a row iterator instead of loading the whole query result into memory. Thank you!

Insert NULL values for Nullable types

Hi, I have created table with Nullable columns. How to pass NULL values via bulk insert?

It seems there are no analogue value for NULL, None is not working atm.

Actually None is working and it inserts NULL values 🙂

TooLargeStringSize: Code: 131.

I often get the error TooLargeStringSize: Code: 131. on inserting data into a table. How I can prevent it? I already tried to insert really small batches.

Meta/Column names from query

Meta/Column names. how do I get them?

how to get column names with select

Is not working under CentOS 7 after install from pip

Hi,
I’m experiencing some strange issues with module and there are no other issues with any other modules:

Installed from pip without issues:

Example code test.py

When using domain instead of IP address, the DNS resolve don’t change.

I use domain instead of IP address, in order to do load balance and failover.

But when I change the domain mapping, the script still continuing connect to the origin IP address.

For example, I use domain ‘xxx.test.com’ which mapping ip1, and ip2, and start the script, the data will write into ip1 and ip2 round-robin.

After the mapping of domain to ip2 and ip3, that means replace ip1 with ip3, the script still continues to write to ip1.

Big INSERT ends in timeout

I have an issue with a big insert query > 2000 cols.
Finally it runs with native clickhouse client.
After some config changes runs in 0.5 seconds

Now I tried to run the same query with the clickhouse-driver.
It times out.

Before I had the same script working with pymysql.

Any idea how to fix this?

Traceback (most recent call last):
File «Click_v01.py», line 154, in
client.execute(sql)
File «/usr/lib/python3.4/site-packages/clickhouse_driver/client.py», line 73, in execute
query_id=query_id, settings=settings
File «/usr/lib/python3.4/site-packages/clickhouse_driver/client.py», line 88, in process_ordinary_query
return self.receive_result(with_column_types=with_column_types)
File «/usr/lib/python3.4/site-packages/clickhouse_driver/client.py», line 19, in receive_result
block = self.receive_block()
File «/usr/lib/python3.4/site-packages/clickhouse_driver/client.py», line 38, in receive_block
packet = self.connection.receive_packet()
File «/usr/lib/python3.4/site-packages/clickhouse_driver/connection.py», line 234, in receive_packet
packet.type = packet_type = read_varint(self.fin)
File «/usr/lib/python3.4/site-packages/clickhouse_driver/reader.py», line 46, in read_varint
i = _read_one(f)
File «/usr/lib/python3.4/site-packages/clickhouse_driver/reader.py», line 31, in _read_one
c = f.read(1)
File «/usr/lib64/python3.4/socket.py», line 378, in readinto
return self._sock.recv_into(b)
socket.timeout: timed out

Nothing type

Recent clickhouse has a new type Nothing which is currently unhandled by clickhouse-driver.

Selecting Null raises:

Return column type spec

Currently the client returns typenames.

It would be great to get the typespec instead, including nullable flag and/or inner type spec.

clickhouse-driver==0.0.10
ClickHouse server version 1.1.54362

It is also possible to omit values in which case the default value of the column is inserted.

However, clickhouse-driver instead throws an exception:

Working example using clickhouse-client:

I was able to get past the KeyError in the Traceback above by changing

I get to the point of sending the data to the server but get the following error, which I unfortunately don’t have the time to dig into right now to come up with a possible PR/solution:

This would be a great feature so that JSON objects don’t always have to be fully specified in the client code!

Dates are lower by 1

date.fromtimestamp is supposed to take a local timestamp not a UTC one. I am in a timezone that is behind UTC, so all dates are 1 day behind.

To fix this you need to use datetime.datetime.utcfromtimestamp().date() instead.

return datetime.datetime.utcfromtimestamp(value * self.offset).date()

Insert Buffer?

The documentation recommends to insert chunks of at least 1000 items at once. Is there an easy way to add some sort of buffer to gather inserts and thus enhance to performance? Of course, one could use a simple list and append to it/insert if it reaches a specific size, but things get more complicated when someone uses multiprocessing etc.

Asyncio client

First of all, great to have a native driver!

Nested datatype

Tuple support

I can’t return unique combinations of two columns (e.g. src and dst ).
Executing a query with SELECT DISTINCT(field1, field2) causes the following error when returning data from Clickhouse:

Set a query ID

Clickhouse allows to set an ID on queries, you can see this IDs with a:

this is useful when you want to cancel a query from an external process as explain here: https://stackoverflow.com/questions/40546983/how-to-kill-a-process-query-in-clickhouse

I propose that the connection.send_query() method accepts a string argument to name the query. It could be used in client.process_*_query() functions and execute() as an optional argument:

Column names

client.execute(‘SELECT * FROM test3’)

client.execute(‘SELECT a, b, c FROM test3’)

and I will get list of tuples (one tuple for each row), but I don’t have column names.

Is it somehow possible to get all columns names (named tuple, or something) with the data itself, in order to simplify further data manipulation?

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

FeaturesВ¶

External data for query processingВ¶

You can pass external data alongside with query:

SettingsВ¶

There are a lot of ClickHouse server settings. Settings can be specified during Client initialization:

Each setting can be overridden in an execute statement:

CompressionВ¶

Client with compression support can be constructed as follows:

CityHash algorithm notesВ¶

Secure connectionВ¶

Specifying query idВ¶

You can manually set query identificator for each query. UUID for example:

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.

Data types checking on INSERTВ¶

Data types check is disabled for performance on INSERT queries. You can turn it on by types_check option:

Query execution statisticsВ¶

profile: rows before limit

Receiving server logsВ¶

Query logs can be received from server by using send_logs_level setting:

Multiple hostsВ¶

New in version 0.1.3.

This option is good for ClickHouse cluster with multiple replicas.

In example above on every new connection driver will use following sequence of hosts if previous host is unavailable:

All queries within established connection will be sent to the same host.

Python DB API 2.0В¶

New in version 0.1.3.

This driver is also implements DB API 2.0 specification. It can be useful for various integrations.

Threads may share the module and connections.

Connection class is just wrapper for handling multiple cursors (clients) and do not initiate actual connections to the ClickHouse server.

There are some non-standard ClickHouse-related Cursor methods for: external data, settings, etc.

For automatic disposal Connection and Cursor instances can be used as context managers:

NumPy arrays supportВ¶

New in version 0.1.6.

NumPy arrays are not used when reading nullable columns and columns of unsupported types.

Direct loading into NumPy arrays increases performance and lowers memory requirements on large amounts of rows.

Direct loading into pandas DataFrame is also supported by using query_dataframe :

Writing pandas DataFrame is also supported with insert_dataframe :

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

QuickstartВ¶

This page gives a good introduction to clickhouse-driver. It assumes you already have clickhouse-driver installed. If you do not, head over to the Installation section.

A minimal working example looks like this:

This code will show all tables from ‘default’ database.

There are two conceptual types of queries:

Selecting dataВ¶

Simple select query looks like:

Of course queries can and should be parameterized to avoid SQL injections:

Selecting data with progress statisticsВ¶

Streaming resultsВ¶

When you are dealing with large datasets block by block results streaming may be useful:

Inserting dataВ¶

Insert queries in Native protocol are a little bit tricky because of ClickHouseвЂ™s columnar nature. And because weвЂ™re using Python.

INSERT query consists of two parts: query statement and query values. Query values are split into chunks called blocks. Each block is sent in binary columnar form.

As data in each block is sent in binary we should not serialize into string by using substitution %(a)s and then deserialize it back into Python types.

This INSERT would be extremely slow if executed with thousands rows of data:

To insert data efficiently, provide data separately, and end your statement with a VALUES clause:

You can use any iterable yielding lists, tuples or dicts.

If data is not passed, connection will be terminated after a timeout.

The following WILL NOT work:

Of course for INSERT вЂ¦ SELECT queries data is not needed:

ClickHouse will execute this query like a usual SELECT query.

DDL queries can be executed in the same way SELECT queries are executed:

Asynchronous behaviorВ¶

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

QuickstartВ¶

This page gives a good introduction to clickhouse-driver. It assumes you already have clickhouse-driver installed. If you do not, head over to the Installation section.

A minimal working example looks like this:

This code will show all tables from ‘default’ database.

There are two conceptual types of queries:

Selecting dataВ¶

Simple select query looks like:

Of course queries can and should be parameterized to avoid SQL injections:

Customisation SELECT output with FORMAT clause is not supported.

Selecting data with progress statisticsВ¶

Streaming resultsВ¶

When you are dealing with large datasets block by block results streaming may be useful:

Inserting dataВ¶

Insert queries in Native protocol are a little bit tricky because of ClickHouseвЂ™s columnar nature. And because weвЂ™re using Python.

INSERT query consists of two parts: query statement and query values. Query values are split into chunks called blocks. Each block is sent in binary columnar form.

As data in each block is sent in binary we should not serialize into string by using substitution %(a)s and then deserialize it back into Python types.

This INSERT would be extremely slow if executed with thousands rows of data:

To insert data efficiently, provide data separately, and end your statement with a VALUES clause:

You can use any iterable yielding lists, tuples or dicts.

If data is not passed, connection will be terminated after a timeout.

The following WILL NOT work:

ClickHouse will execute this query like a usual SELECT query.

Inserting data in different formats with FORMAT clause is not supported.

See Inserting data from CSV file if you need to data in custom format.

DDL queries can be executed in the same way SELECT queries are executed:

Async and multithreadingВ¶

To utilize ClickHouseвЂ™s asynchronous capability you should either use multiple Client instances or implement a queue.

The same thing is applied to multithreading. Queries from different threads canвЂ™t use one Client instance with single connection. You should use different clients for different threads.

However, if you are using DB API for communication with the server each cursor create its own Client instance. This makes communication thread-safe.

aiochclient 2.2.0

pip install aiochclient Copy PIP instructions

Released: Aug 18, 2022

Async http clickhouse client for python 3.6+

Navigation

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: MIT License (MIT)

Tags clickhouse, async, python, aiohttp

Maintainers

Classifiers

Project description

aiochclient

An async http(s) ClickHouse client for python 3.6+ supporting type conversion in both directions, streaming, lazy decoding on select queries, and a fully typed interface.

Installation

You can use it with either aiohttp or httpx http connectors.

To use with aiohttp install it with command:

Or aiochclient[aiohttp-speedups] to install with extra speedups.

To use with httpx install it with command:

Or aiochclient[httpx-speedups] to install with extra speedups.

Installing with [*-speedups] adds the following:

Additionally the installation process attempts to use Cython for a speed boost (roughly 30% faster).

Quick Start

Connecting to ClickHouse

aiochclient needs aiohttp.ClientSession or httpx.AsyncClient to connect to ClickHouse:

Querying the database

For fetching all rows at once use the fetch method:

For fetching first row from result use the fetchrow method:

You can also use fetchval method, which returns first value of the first row from query result:

With async iteration on the query results stream you can fetch multiple rows without loading them all into memory at once:

Use fetch / fetchrow / fetchval / iterate for SELECT queries and execute or any of last for INSERT and all another queries.

Working with query results

Documentation

To check out the api docs, visit the readthedocs site..

Type Conversion

aiochclient automatically converts types from ClickHouse to python types and vice-versa.

Connection Pool Settings

aiochclient uses the aiohttp.TCPConnector to determine pool size. By default, the pool limit is 100 open connections.

Notes on Speed

It’s highly recommended using uvloop and installing aiochclient with speedups for the sake of speed. Some recent benchmarks on our machines without parallelization:

Note: these benchmarks are system dependent

airflow-clickhouse-plugin 0.8.2

pip install airflow-clickhouse-plugin Copy PIP instructions

Released: Jun 11, 2022

Navigation

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: MIT License

Tags clickhouse, airflow

Requires: Python >=3.6.*

Maintainers

Classifiers

Project description

Airflow ClickHouse Plugin

Features

Installation and dependencies

Requires apache-airflow and clickhouse-driver (installed automatically by pip ). Primarily supports Airflow 2.0–2.3. Later versions are expected to work properly but may be not fully tested. Use plugin versions below 0.6.0 (e.g. 0.5.7.post1) to preserve compatibility with Airflow 1.10.6 (this version has long-term support on Google Cloud Composer).

Note on pandas dependency

Usage

ClickHouseOperator Reference

To import ClickHouseOperator use: from airflow_clickhouse_plugin.operators.clickhouse_operator import ClickHouseOperator

The result of the last query is pushed to XCom.

ClickHouseHook Reference

To import ClickHouseHook use: from airflow_clickhouse_plugin.hooks.clickhouse_hook import ClickHouseHook

Supported kwargs of constructor ( __init__ method):

Supports all the methods of the Airflow BaseHook including:

ClickHouseSqlSensor Reference

Sensor fully inherits from Airflow SQLSensor and therefore fully implements its interface using ClickHouseHook to fetch the SQL execution result and supports templating of sql argument.

ClickHouse Connection schema

clickhouse_driver.Client is initiated with attributes stored in Airflow Connection attributes. The mapping of the attributes is listed below:

Airflow Connection attribute	Client.__init__ argument
host	host
port	port
schema	database
login	user
password	password

If you pass database argument to ClickHouseOperator or ClickHouseHook explicitly then it is passed to the Client instead of the schema attribute of the Airflow connection.

Extra arguments

For example, if Airflow connection contains extra= <"secure":true>then the Client.__init__ will receive secure=True keyword argument in addition to other non-empty connection attributes.

Default values

If the Airflow connection attribute is not set then it is not passed to the Client at all. In that case the default value of the corresponding clickhouse_driver.Connection argument is used (e.g. user defaults to ‘default’ ).

This means that Airflow ClickHouse Plugin does not itself define any default values for the ClickHouse connection. You may fully rely on default values of the clickhouse-driver version you use. The only exception is host : if the attribute of Airflow connection is not set then ‘localhost’ is used.

Default connection

Examples

ClickHouseOperator Example

ClickHouseHook Example

Important note: don’t try to insert values using ch_hook.run(‘INSERT INTO some_ch_table VALUES (1)’) literal form. clickhouse-driver requires values for INSERT query to be provided via parameters due to specifics of the native ClickHouse protocol.

ClickHouseSqlSensor Example

How to run tests

Unit tests

Integration tests

Integration tests require access to ClickHouse server. Tests use connection URI defined via environment variable AIRFLOW_CONN_CLICKHOUSE_DEFAULT with clickhouse://localhost as default.

All tests

Github Actions

Github Action is set up for this project.

Run tests using Docker

Run ClickHouse server inside Docker:

The above command will open bash inside the container.

Install dependencies into container and run tests (execute inside container):

How to upload to PyPI

Run tests for test PyPI version:

Pandas test may fail.

Test public PyPI (run clickhouse container), with pandas:

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

QuickstartВ¶

This page gives a good introduction to clickhouse-driver. It assumes you already have clickhouse-driver installed. If you do not, head over to the Installation section.

A minimal working example looks like this:

This code will show all tables from ‘default’ database.

There are two conceptual types of queries:

Selecting dataВ¶

Simple select query looks like:

Of course queries can and should be parameterized to avoid SQL injections:

Selecting data with progress statisticsВ¶

Streaming resultsВ¶

When you are dealing with large datasets block by block results streaming may be useful:

Inserting dataВ¶

Insert queries in Native protocol are a little bit tricky because of ClickHouseвЂ™s columnar nature. And because weвЂ™re using Python.

INSERT query consists of two parts: query statement and query values. Query values are split into chunks called blocks. Each block is sent in binary columnar form.

As data in each block is sent in binary we should not serialize into string by using substitution %(a)s and then deserialize it back into Python types.

This INSERT would be extremely slow if executed with thousands rows of data:

To insert data efficiently, provide data separately, and end your statement with a VALUES clause:

You can use any iterable yielding lists, tuples or dicts.

If data is not passed, connection will be terminated after a timeout.

The following WILL NOT work:

Of course for INSERT вЂ¦ SELECT queries data is not needed:

ClickHouse will execute this query like a usual SELECT query.

DDL queries can be executed in the same way SELECT queries are executed:

Async and multithreadingВ¶

To utilize ClickHouseвЂ™s asynchronous capability you should either use multiple Client instances or implement a queue.

The same thing is applied to multithreading. Queries from different threads canвЂ™t use one Client instance with single connection. You should use different clients for different threads.

However, if you are using DB API for communication with the server each cursor create its own Client instance. This makes communication thread-safe.

madiedinro/simple-clickhouse

Use Git or checkout with SVN using the web URL.

Work fast with our official CLI. Learn more.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

Simple ClickHouse lib

Install using pip from pypi repository

Or latest version from git

При использовании в Rockstat, параметры указывать не требуется. Они подставляются автоматически из переменных окружения.

Selecting without decoding

Selecting as dict’s steam

Disabling decoding for streaming data

Чтобы получить результат в виде строки воспользуйтесь bytes_decoder

Executing sql statements

Для для записи данных, управления БД и других операция (не select) слудует использовать метод run

Можно использовать для «ручной» записи данных

Microbatch writing using context manager

new

On exit context all data will be flushed.

Old manual conrolled mechanic.

Some Simpe Magick

To create instance of TableDiscovery call

One of records or columns should be filled.

Detect using present data

Next times after use table auto discovery you shoud use fixed layout. To to this easy try TableDiscovery.pycode()

will be returned

Correct detected / implicit set data-types

TableDiscovery.int(*args) set columnts to int

Set date columns

Set date column

Set str columns

Set strinmg column

Set primary key columns

Set metrics

other marked as dimensions

Set dimensions

other marked as metrics

Print table create statement / execute query

Difference handling. Be careful currently it Proof of concept

All records will be flushed to DB on context exit

Выполнение запроса и чтение всего результата сразу

Получение записей потоком

Выполнение SQL операций

all data will be flushed on exit context

The MIT License (MIT)

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the «Software»), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED «AS IS», WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

asynch 0.1.9

pip install asynch Copy PIP instructions

Released: Jun 2, 2021

A asyncio driver for ClickHouse with native tcp protocol

Navigation

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: Apache Software License (Apache-2.0)

Author: long2ice

Tags ClickHouse, asyncio, driver

Requires: Python >=3.7, long2ice

Classifiers

Project description

asynch

Introduction

asynch is an asyncio ClickHouse Python Driver with native (TCP) interface support, which reuse most of clickhouse-driver and comply with PEP249.

Install

Usage

Connect to ClickHouse

Create table by sql

Use DictCursor to get result with dict

Insert data with dict

Insert data with tuple

Use connection pool

License

This project is licensed under the Apache-2.0 License.

Project details

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: Apache Software License (Apache-2.0)

Author: long2ice

Tags ClickHouse, asyncio, driver

Requires: Python >=3.7, long2ice

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

QuickstartВ¶

This page gives a good introduction to clickhouse-driver. It assumes you already have clickhouse-driver installed. If you do not, head over to the Installation section.

A minimal working example looks like this:

This code will show all tables from ‘default’ database.

There are two conceptual types of queries:

Selecting dataВ¶

Simple select query looks like:

Of course queries can and should be parameterized to avoid SQL injections:

Selecting data with progress statisticsВ¶

Streaming resultsВ¶

When you are dealing with large datasets block by block results streaming may be useful:

Inserting dataВ¶

Insert queries in Native protocol are a little bit tricky because of ClickHouseвЂ™s columnar nature. And because weвЂ™re using Python.

INSERT query consists of two parts: query statement and query values. Query values are split into chunks called blocks. Each block is sent in binary columnar form.

As data in each block is sent in binary we should not serialize into string by using substitution %(a)s and then deserialize it back into Python types.

This INSERT would be extremely slow if executed with thousands rows of data:

To insert data efficiently, provide data separately, and end your statement with a VALUES clause:

You can use any iterable yielding lists, tuples or dicts.

If data is not passed, connection will be terminated after a timeout.

The following WILL NOT work:

Of course for INSERT вЂ¦ SELECT queries data is not needed:

ClickHouse will execute this query like a usual SELECT query.

DDL queries can be executed in the same way SELECT queries are executed:

Asynchronous behaviorВ¶

clickhouse-sqlalchemy 0.2.2

pip install clickhouse-sqlalchemy Copy PIP instructions

Released: Aug 24, 2022

Simple ClickHouse SQLAlchemy Dialect

Navigation

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: MIT License (MIT)

Tags ClickHouse, db, database, cloud, analytics

Requires: Python >=3.6, xzkostyan

Classifiers

Project description

ClickHouse SQLAlchemy

ClickHouse dialect for SQLAlchemy to ClickHouse database.

Documentation

Usage

native [recommended] (TCP) via clickhouse-driver

http via requests

Insert some data

And query inserted data

License

ClickHouse SQLAlchemy is distributed under the MIT license.

Project details

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: MIT License (MIT)

Tags ClickHouse, db, database, cloud, analytics

Requires: Python >=3.6, xzkostyan

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

PerformanceВ¶

This section compares clickhouse-driver performance over Native interface with TSV and JSONEachRow formats available over HTTP interface.

clickhouse-driver returns already parsed row items in Python data types. Driver performs all transformation for you.

When you read data over HTTP you may need to cast strings into Python types.

Test dataВ¶

Sample data for testing is taken from ClickHouse docs.

Create database and table:

Download some data for 2017 year:

Insert data into ClickHouse:

Required packagesВ¶

For fast json parsing weвЂ™ll use ujson package:

VersionsВ¶

Machine: Linux ThinkPad-T460 4.4.0-177-generic #207-Ubuntu SMP Mon Mar 16 01:16:10 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

Python: CPython 3.6.5 (default, May 30 2019, 14:48:31) [GCC 5.4.0 20160609]

BenchmarkingВ¶

Scripts below can be benchmarked with following one-liner:

Time will measure:

Plain text without parsingВ¶

LetвЂ™s take get plain text response from ClickHouse server as baseline.

Fetching not parsed data with pure requests (1)

Parsed rowsВ¶

Line split into elements will be consider as вЂњparsedвЂќ for TSV format (2)

Now we cast each element to itвЂ™s data type (2.5)

JSONEachRow format can be loaded with json loads (3)

Get fully parsed rows with clickhouse-driver in Native format (4)

Iteration over rowsВ¶

Iteration over TSV (5)

Now we cast each element to itвЂ™s data type (5.5)

Iteration over JSONEachRow (6)

Iteration over rows with clickhouse-driver in Native format (7)

Iteration over string rowsВ¶

OK, but what if we need only string columns?

Iteration over TSV (8)

Iteration over JSONEachRow (9)

Iteration over string rows with clickhouse-driver in Native format (10)

Iteration over int rowsВ¶

Iteration over TSV (11)

Iteration over JSONEachRow (12)

Iteration over int rows with clickhouse-driver in Native format (13)

ResultsВ¶

This table contains memory and timing benchmark results of snippets above.

JSON in table is shorthand for JSONEachRow.

Rows
50k	131k	217k	450k	697k
Plain text without parsing: timing
Naive requests.get TSV (1)	0.40 s	0.67 s	0.95 s	1.67 s	2.52 s
Naive requests.get JSON (1)	0.61 s	1.23 s	2.09 s	3.52 s	5.20 s
Plain text without parsing: memory
Naive requests.get TSV (1)	49 MB	107 MB	165 MB	322 MB	488 MB
Naive requests.get JSON (1)	206 MB	564 MB	916 MB	1.83 GB	2.83 GB
Parsed rows: timing
requests.get TSV (2)	0.81 s	1.81 s	3.09 s	7.22 s	11.87 s
requests.get TSV with cast (2.5)	1.78 s	4.58 s	7.42 s	16.12 s	25.52 s
requests.get JSON (3)	2.14 s	5.65 s	9.20 s	20.43 s	31.72 s
clickhouse-driver Native (4)	0.73 s	1.40 s	2.08 s	4.03 s	6.20 s
Parsed rows: memory
requests.get TSV (2)	171 MB	462 MB	753 MB	1.51 GB	2.33 GB
requests.get TSV with cast (2.5)	135 MB	356 MB	576 MB	1.15 GB	1.78 GB
requests.get JSON (3)	139 MB	366 MB	591 MB	1.18 GB	1.82 GB
clickhouse-driver Native (4)	135 MB	337 MB	535 MB	1.05 GB	1.62 GB
Iteration over rows: timing
requests.get TSV (5)	0.49 s	0.99 s	1.34 s	2.58 s	4.00 s
requests.get TSV with cast (5.5)	1.38 s	3.38 s	5.40 s	10.89 s	16.59 s
requests.get JSON (6)	1.89 s	4.73 s	7.63 s	15.63 s	24.60 s
clickhouse-driver Native (7)	0.62 s	1.28 s	1.93 s	3.68 s	5.54 s
Iteration over rows: memory
requests.get TSV (5)	19 MB	19 MB	19 MB	19 MB	19 MB
requests.get TSV with cast (5.5)	19 MB	19 MB	19 MB	19 MB	19 MB
requests.get JSON (6)	20 MB	20 MB	20 MB	20 MB	20 MB
clickhouse-driver Native (7)	56 MB	70 MB	71 MB	71 MB	71 MB
Iteration over string rows: timing
requests.get TSV (8)	0.40 s	0.67 s	0.80 s	1.55 s	2.18 s
requests.get JSON (9)	1.14 s	2.64 s	4.22 s	8.48 s	12.96 s
clickhouse-driver Native (10)	0.46 s	0.91 s	1.35 s	2.49 s	3.67 s
Iteration over string rows: memory
requests.get TSV (8)	19 MB	19 MB	19 MB	19 MB	19 MB
requests.get JSON (9)	20 MB	20 MB	20 MB	20 MB	20 MB
clickhouse-driver Native (10)	46 MB	56 MB	57 MB	57 MB	57 MB
Iteration over int rows: timing
requests.get TSV (11)	0.84 s	2.06 s	3.22 s	6.27 s	10.06 s
requests.get JSON (12)	0.95 s	2.15 s	3.55 s	6.93 s	10.82 s
clickhouse-driver Native (13)	0.43 s	0.61 s	0.86 s	1.53 s	2.27 s
Iteration over int rows: memory
requests.get TSV (11)	19 MB	19 MB	19 MB	19 MB	19 MB
requests.get JSON (12)	20 MB	20 MB	20 MB	20 MB	20 MB
clickhouse-driver Native (13)	41 MB	48 MB	48 MB	48 MB	49 MB

ConclusionВ¶

If you need to get significant number of rows from ClickHouse server as text then TSV format is your choice. See Iteration over string rows results.

It doesnвЂ™t matter which interface to use if you manipulate small amount of rows.

ClickSQL 0.1.9.4

pip install ClickSQL Copy PIP instructions

Released: Nov 24, 2021

A python client for Clickhouse

Navigation

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: MIT Licence

Author: sn0wfree

Tags ClickHouse, Databases, SQL, Python, Client

Maintainers

Project description

ClickSQL: ClickHouse client for Humans

ClickSQL is a python client for ClickHouse database, which may help users to use ClickHouse more easier and pythonic. More information for ClickHouse can be found at here

Installation

pip install ClickSQL

Usage

Initial connection

to setup a database connection and send a heartbeat-check signal

Query

execute a SQL Query

execute a Query without SQL

Insert data

insert data into database by various ways

Insert data via DataFrame

Insert data via SQL(Inner)

Create table

Create table by SQL

Create table by DataFrame

Contribution

Welcome to improve this package or submit an issue or any others

Author

Available functions or properties

In Process

schedule

Project details

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: MIT Licence

Author: sn0wfree

Tags ClickHouse, Databases, SQL, Python, Client

Python clickhouse driver

Each ClickHouse type is deserialized to a corresponding Python type when SELECT queries are prepared. When serializing INSERT queries, clickhouse-driver accepts a broader range of Python types. The following ClickHouse types are supported by clickhouse-driver:

Date32 support is new in version 0.2.2.

Timezone support is new in version 0.0.11. DateTime64 support is new in version 0.1.3.

Integers are interpreted as seconds without timezone (UNIX timestamps). Integers can be used when insertion of datetime column is a bottleneck.

Setting use_client_time_zone is taken into consideration.

You can cast DateTime column to integers if you are facing performance issues when selecting large amount of rows.

Due to Python’s current limitations minimal DateTime64 resolution is one microsecond.

String column is encoded/decoded with encoding specified by strings_encoding setting. Default encoding is UTF-8.

You can specify custom encoding:

Encoding is applied to all string fields in query.

String columns can be returned without any decoding. In this case return values are bytes:

If a column has FixedString type, upon returning from SELECT it may contain trailing zeroes in accordance with ClickHouse’s storage format. Trailing zeroes are stripped by driver for convenience.

During INSERT, if strings_as_bytes setting is not specified and string cannot be encoded with encoding, a UnicodeEncodeError will be raised.

Currently clickhouse-driver can’t handle empty enum value due to Python’s Enum mechanics. Enum member name must be not empty. See issue and workaround.

ClickHouse/clickhouse-odbc

Use Git or checkout with SVN using the web URL.

Work fast with our official CLI. Learn more.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

ODBC Driver for ClickHouse

This is the official ODBC driver implementation for accessing ClickHouse as a data source.

For more information on ClickHouse go to ClickHouse home page.

For more information on what ODBC is go to ODBC Overview.

The canonical repo for this driver is located at https://github.com/ClickHouse/clickhouse-odbc.

See LICENSE file for licensing information.

Table of contents

Pre-built binary packages of the release versions of the driver available for the most common platforms at:

Note, that since ODBC drivers are not used directly by a user, but rather accessed through applications, which in their turn access the driver through ODBC driver manager, user have to install the driver for the same architecture (32- or 64-bit) as the application that is going to access the driver. Moreover, both the driver and the application must be compiled for (and actually use during run-time) the same ODBC driver manager implementation (we call them «ODBC providers» here). There are three supported ODBC providers:

If you have Homebrew installed (usually applicable to macOS only, but can also be available in Linux), just execute:

If you don’t see a package that matches your platforms under Releases, or the version of your system is significantly different than those of the available packages, or maybe you want to try a bleeding edge version of the code that hasn’t been released yet, you can always build the driver manually from sources:

Native packages will have all the dependency information so when you install the driver using a native package, all required run-time packages will be installed automatically. If you use manual packaging, i.e., just extract driver binaries to some folder, you also have to make sure that all the run-time dependencies are satisfied in your system manually:

The first step usually consists of registering the driver so that the corresponding ODBC provider is able to locate it.

The next step is defining one or more DSNs, associated with the newly registered driver, and setting driver-specific parameters in the body of those DSN definitions.

All this involves modifying a dedicated registry keys in case of MDAC, or editing odbcinst.ini (for driver registration) and odbc.ini (for DSN definition) files for UnixODBC or iODBC, directly or indirectly.

This will be performed automatically using some default values if you are installing the driver using native installers.

Otherwise, if you are configuring manually, or need to modify the default configuration created by the installer, please see the exact locations of files (or registry keys) that need to be modified in the corresponding section below:

The list of DSN parameters recognized by the driver is as follows:

URL query string

Some of configuration parameters can be passed to the server as a part of the query string of the URL.

The list of parameters in the query string of the URL that are also recognized by the driver is as follows:

Parameter	Default value	Description
database	default	Database name to connect to
default_format	ODBCDriver2	Default wire format of the resulting data that the server will send to the driver. Formats supported by the driver are: ODBCDriver2 and RowBinaryWithNamesAndTypes

Note, that currently there is a difference in timezone handling between ODBCDriver2 and RowBinaryWithNamesAndTypes formats: in ODBCDriver2 date and time values are presented to the ODBC application in server’s timezone, wherease in RowBinaryWithNamesAndTypes they are converted to local timezone. This behavior will be changed/parametrized in future. If server and ODBC application timezones are the same, date and time values handling will effectively be identical between these two formats.

Troubleshooting: driver manager tracing and driver logging

To debug issues with the driver, first things that need to be done are:

Building from sources

The general requirements for building the driver from sources are as follows:

Additional requirements exist for each platform, which also depend on whether packaging and/or testing is performed.

See the exact steps for each platform in the corresponding section below:

The list of configuration options recognized during the CMake generation step is as follows:

Run-time dependencies: Windows

All modern Windows systems come with preinstalled MDAC driver manager.

Run-time dependencies: macOS

Execute the following in the terminal (assuming you have Homebrew installed):

Run-time dependencies: Red Hat/CentOS

Execute the following in the terminal:

Run-time dependencies: Debian/Ubuntu

Execute the following in the terminal:

Configuration: MDAC/WDAC (Microsoft/Windows Data Access Components)

To configure already installed drivers and DSNs, or create new DSNs, use Microsoft ODBC Data Source Administrator tool:

For full description of ODBC configuration mechanism in Windows, as well as for the case when you want to learn how to manually register a driver and have a full control on configuration in general, see:

Note, that the keys are subject to «Registry Redirection» mechanism, with caveats.

You can find sample configuration for this driver here (just map the keys to corresponding sections in registry):

In short, usually you will end up editing /etc/odbcinst.ini and /etc/odbc.ini for system-wide driver and DSN entries, and

/.odbc.ini for user-wide driver and DSN entries.

For more info, see:

You can find sample configuration for this driver here:

These samples can be added to the corresponding configuration files using the odbcinst tool (assuming the package is installed under /usr/local ):

In short, usually you will end up editing /etc/odbcinst.ini and /etc/odbc.ini for system-wide driver and DSN entries, and

/.odbc.ini for user-wide driver and DSN entries.

In macOS, if those INI files exist, they usually are symbolic or hard links to /Library/ODBC/odbcinst.ini and /Library/ODBC/odbc.ini for system-wide, and

/Library/ODBC/odbc.ini for user-wide configs respectively.

For more info, see:

You can find sample configuration for this driver here:

Enabling driver manager tracing: MDAC/WDAC (Microsoft/Windows Data Access Components)

Comprehensive explanations (possibly, with some irrelevant vendor-specific details though) on how to enable ODBC driver manager tracing could be found at the following links:

Enabling driver manager tracing: UnixODBC

Comprehensive explanations (possibly, with some irrelevant vendor-specific details though) on how to enable ODBC driver manager tracing could be found at the following links:

Enabling driver manager tracing: iODBC

Comprehensive explanations (possibly, with some irrelevant vendor-specific details though) on how to enable ODBC driver manager tracing could be found at the following links:

Building from sources: Windows

CMake bundled with the recent versions of Visual Studio can be used.

An SDK required for building the ODBC driver is included in Windows SDK, which in its turn is also bundled with Visual Studio.

All of the following commands have to be issued in Visual Studio Command Prompt:

Clone the repo with submodules:

Enter the cloned source tree, create a temporary build folder, and generate the solution and project files in it:

Build the generated solution in-place:

. and, optionally, run tests (note, that for non-unit tests, preconfigured driver and DSN entries must exist, that point to the binaries generated in this build folder):

Building from sources: macOS

You will need macOS 10.14 or later, Xcode 10 or later with Command Line Tools installed, as well as up-to-date Homebrew available in the system.

Install Homebrew using the following command, and follow the printed instructions on any additional steps required to complete the installation:

Then, install the latest Xcode from App Store. Open it at least once to accept the end-user license agreement and automatically install the required components.

Then, make sure that the latest Command Line Tools are installed and selected in the system:

Build-time dependencies: iODBC

Execute the following in the terminal:

Build-time dependencies: UnixODBC

Execute the following in the terminal:

Clone the repo recursively with submodules:

Enter the cloned source tree, create a temporary build folder, and generate a Makefile for the project in it:

Build the generated solution in-place:

. and, optionally, run tests (note, that for non-unit tests, preconfigured driver and DSN entries must exist, that point to the binaries generated in this build folder):

Building from sources: Red Hat/CentOS

Build-time dependencies: UnixODBC

Execute the following in the terminal:

Build-time dependencies: iODBC

Execute the following in the terminal:

All of the following commands have to be issued right after this one command issued in the same terminal session:

Clone the repo with submodules:

Enter the cloned source tree, create a temporary build folder, and generate a Makefile for the project in it:

Build the generated solution in-place:

. and, optionally, run tests (note, that for non-unit tests, preconfigured driver and DSN entries must exist, that point to the binaries generated in this build folder):

Building from sources: Debian/Ubuntu

Build-time dependencies: UnixODBC

Execute the following in the terminal:

Build-time dependencies: iODBC

Execute the following in the terminal:

Assuming, that the system cc and c++ are pointing to the compilers that satisfy the minimum requirements from Building from sources.

If the version of cmake is not recent enough, you can install a newer version by folowing instructions from one of these pages:

Clone the repo with submodules:

Enter the cloned source tree, create a temporary build folder, and generate a Makefile for the project in it:

Build the generated solution in-place:

. and, optionally, run tests (note, that for non-unit tests, preconfigured driver and DSN entries must exist, that point to the binaries generated in this build folder):

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

QuickstartВ¶

This page gives a good introduction to clickhouse-driver. It assumes you already have clickhouse-driver installed. If you do not, head over to the Installation section.

A minimal working example looks like this:

This code will show all tables from ‘default’ database.

There are two conceptual types of queries:

Selecting dataВ¶

Simple select query looks like:

Of course queries can and should be parameterized to avoid SQL injections:

Selecting data with progress statisticsВ¶

Streaming resultsВ¶

When you are dealing with large datasets block by block results streaming may be useful:

Inserting dataВ¶

Insert queries in Native protocol are a little bit tricky because of ClickHouseвЂ™s columnar nature. And because weвЂ™re using Python.

INSERT query consists of two parts: query statement and query values. Query values are split into chunks called blocks. Each block is sent in binary columnar form.

As data in each block is sent in binary we should not serialize into string by using substitution %(a)s and then deserialize it back into Python types.

This INSERT would be extremely slow if executed with thousands rows of data:

To insert data efficiently, provide data separately, and end your statement with a VALUES clause:

You can use any iterable yielding lists, tuples or dicts.

If data is not passed, connection will be terminated after a timeout.

The following WILL NOT work:

Of course for INSERT вЂ¦ SELECT queries data is not needed:

ClickHouse will execute this query like a usual SELECT query.

DDL queries can be executed in the same way SELECT queries are executed:

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

This part of the documentation covers basic classes of the driver: Client, Connection and others.

ClientВ¶

Client for communication with the ClickHouse server. Single connection is established per each connected instance of the client.

Parameters:	settings вЂ“ Dictionary of settings that passed to every query. Defaults to None (no additional settings). See all available settings in ClickHouse docs.

Disconnects from the server.

execute ( query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False, columnar=False ) В¶

execute_iter ( query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False ) В¶

New in version 0.0.14.

execute_with_progress ( query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False ) В¶

ConnectionВ¶

Represents connection between client and ClickHouse server.

Closes connection between server and client. Frees resources: e.g. closes socket.

QueryResultВ¶

Stores query result from multiple blocks.

get_result ( ) В¶

Returns:	Stored query result.

ProgressQueryResultВ¶

Stores query result and progress information from multiple blocks. Provides iteration over query progress.

get_result ( ) В¶

Returns:	Stored query result.

IterQueryResultВ¶

Provides iteration over returned data by chunks (streaming by chunks).

clickhouse-driver 0.2.4

pip install clickhouse-driver==0.2.4 Copy PIP instructions

Released: Jun 13, 2022

Python driver with native interface for ClickHouse

Navigation

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: MIT License (MIT)

Tags ClickHouse, db, database, cloud, analytics

Requires: Python >=3.4, xzkostyan

Classifiers

Project description

ClickHouse Python Driver

ClickHouse Python Driver with native (TCP) interface support.

Asynchronous wrapper is available here: https://github.com/mymarilyn/aioch

Features

Documentation

Usage

There are two ways to communicate with server:

Pure Client example:

License

ClickHouse Python Driver is distributed under the MIT license.

Project details

Project links

Statistics

View statistics for this project via Libraries.io, or by using our public dataset on Google BigQuery

License: MIT License (MIT)

Tags ClickHouse, db, database, cloud, analytics

Requires: Python >=3.4, xzkostyan

Infinidat/infi.clickhouse_orm

Use Git or checkout with SVN using the web URL.

Work fast with our official CLI. Learn more.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

This project is simple ORM for working with the ClickHouse database. It allows you to define model classes whose instances can be written to the database and read from it.

Let’s jump right in with a simple example of monitoring CPU usage. First we need to define the model class, connect to the database and create a table for the model:

Now we can collect usage statistics per CPU, and write them to the database:

Querying the table is easy, using either the query builder or raw SQL:

This and other examples can be found in the examples folder.

To learn more please visit the documentation.

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

QuickstartВ¶

This page gives a good introduction to clickhouse-driver. It assumes you already have clickhouse-driver installed. If you do not, head over to the Installation section.

A minimal working example looks like this:

This code will show all tables from ‘default’ database.

There are two conceptual types of queries:

Selecting dataВ¶

Simple select query looks like:

Of course queries can and should be parameterized to avoid SQL injections:

Selecting data with progress statisticsВ¶

Streaming resultsВ¶

When you are dealing with large datasets block by block results streaming may be useful:

Inserting dataВ¶

Insert queries in Native protocol are a little bit tricky because of ClickHouseвЂ™s columnar nature. And because weвЂ™re using Python.

INSERT query consists of two parts: query statement and query values. Query values are split into chunks called blocks. Each block is sent in binary columnar form.

As data in each block is sent in binary we should not serialize into string by using substitution %(a)s and then deserialize it back into Python types.

This INSERT would be extremely slow if executed with thousands rows of data:

To insert data efficiently, provide data separately, and end your statement with a VALUES clause:

You can use any iterable yielding lists, tuples or dicts.

If data is not passed, connection will be terminated after a timeout.

The following WILL NOT work:

Of course for INSERT вЂ¦ SELECT queries data is not needed:

ClickHouse will execute this query like a usual SELECT query.

DDL queries can be executed in the same way SELECT queries are executed:

Async and multithreadingВ¶

To utilize ClickHouseвЂ™s asynchronous capability you should either use multiple Client instances or implement a queue.

The same thing is applied to multithreading. Queries from different threads canвЂ™t use one Client instance with single connection. You should use different clients for different threads.

However, if you are using DB API for communication with the server each cursor create its own Client instance. This makes communication thread-safe.

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

DB API 2.0В¶

This part of the documentation covers driver DB API.

clickhouse_driver.dbapi. connect ( dsn=None, host=None, user=’default’, password=», port=9000, database=’default’, **kwargs ) В¶

Create a new database connection.

The connection can be specified via DSN:

or using database and credentials arguments:

The basic connection parameters are:

See defaults in Connection constructor.

DSN or host is required.

Any other keyword parameter will be passed to the underlying Connection class.

Returns:	a new connection.

exception clickhouse_driver.dbapi. Warning В¶ with_traceback ( ) В¶

Exception.with_traceback(tb) вЂ“ set self.__traceback__ to tb and return self.

exception clickhouse_driver.dbapi. Error В¶ with_traceback ( ) В¶

Exception.with_traceback(tb) вЂ“ set self.__traceback__ to tb and return self.

exception clickhouse_driver.dbapi. DataError В¶ with_traceback ( ) В¶

Exception.with_traceback(tb) вЂ“ set self.__traceback__ to tb and return self.

exception clickhouse_driver.dbapi. DatabaseError В¶ with_traceback ( ) В¶

Exception.with_traceback(tb) вЂ“ set self.__traceback__ to tb and return self.

exception clickhouse_driver.dbapi. ProgrammingError В¶ with_traceback ( ) В¶

Exception.with_traceback(tb) вЂ“ set self.__traceback__ to tb and return self.

exception clickhouse_driver.dbapi. IntegrityError В¶ with_traceback ( ) В¶

Exception.with_traceback(tb) вЂ“ set self.__traceback__ to tb and return self.

exception clickhouse_driver.dbapi. InterfaceError В¶ with_traceback ( ) В¶

Exception.with_traceback(tb) вЂ“ set self.__traceback__ to tb and return self.

exception clickhouse_driver.dbapi. InternalError В¶ with_traceback ( ) В¶

Exception.with_traceback(tb) вЂ“ set self.__traceback__ to tb and return self.

exception clickhouse_driver.dbapi. NotSupportedError В¶ with_traceback ( ) В¶

Exception.with_traceback(tb) вЂ“ set self.__traceback__ to tb and return self.

exception clickhouse_driver.dbapi. OperationalError В¶ with_traceback ( ) В¶

Exception.with_traceback(tb) вЂ“ set self.__traceback__ to tb and return self.

ConnectionВ¶

Creates new Connection for accessing ClickHouse database.

Connection is just wrapper for handling multiple cursors (clients) and do not initiate actual connections to the ClickHouse server.

Close the connection now. The connection will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the connection. The same applies to all cursor objects trying to use the connection.

Do nothing since ClickHouse has no transactions.

cursor ( ) В¶

Returns:	a new Cursor Object using the connection.

rollback ( ) В¶

Do nothing since ClickHouse has no transactions.

CursorВ¶

Close the cursor now. The cursor will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the cursor.

Prepare and execute a database operation (query or command).

executemany ( operation, seq_of_parameters ) В¶

Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples).

Returns:	list of fetched rows.

fetchmany ( size=None ) В¶

Fetch the next set of rows of a query result, returning a sequence of sequences (e.g. a list of tuples). An empty sequence is returned when no more rows are available.

Parameters:	size вЂ“ amount of rows to return.
Returns:	list of fetched rows or empty list.

fetchone ( ) В¶

Fetch the next row of a query result set, returning a single sequence, or None when no more data is available.

Adds external table to cursor context.

If the same table is specified more than once the last one is used.

set_query_id ( query_id ) В¶

Specifies the query identifier for cursor.

Parameters:	query_id вЂ“ the query identifier.
Returns:	None

set_settings ( settings ) В¶

Specifies settings for cursor.

Parameters:	settings вЂ“ dictionary of query settings
Returns:	None

set_stream_results ( stream_results, max_row_buffer ) В¶

Toggles results streaming from server. Driver will consume block-by-block of max_row_buffer size and yield row-by-row from each block.

set_types_check ( types_check ) В¶

Toggles type checking for sequence of INSERT parameters. Disabled by default.

clickhouse-driver

Python driver for ClickHouse

Navigation

Quick search

DB API 2.0В¶

This part of the documentation covers driver DB API.

clickhouse_driver.dbapi. connect ( dsn=None, host=None, user=’default’, password=», port=9000, database=’default’, **kwargs ) В¶

Create a new database connection.

The connection can be specified via DSN:

or using database and credentials arguments:

The basic connection parameters are:

See defaults in Connection constructor.

DSN or host is required.

Any other keyword parameter will be passed to the underlying Connection class.

Returns:	a new connection.