ClickHouse Connect Controller API | ClickHouse Documents (2023)

Observation:Passing keyword arguments is recommended for most API methods due to the number of possible arguments, many of which are optional.

client initialization​

Oclickhouse_connect.driver.clientThe class provides the main interface between a Python application and the ClickHouse database server. Use theclickhouse_connect.get_clientfunction to get a Customer instance, which accepts the following arguments:

connection arguments​

Argumentos HTTPS/TLS​

configuration argument​

Finally, thesettingsargument in favorget_customerit is used to pass additional ClickHouse configurations to the server for each client request. Please note that in most cases, users withjust reading=1Access cannot change settings sent with a query, so ClickHouse Connect will discard these settings in the final request and log a warning.

For other ClickHouse configurations that can be sent with each query, seethe ClickHouse documentation.

Customer Creation Examples​

• Without any parameters, a ClickHouse Connect client will connect to the default HTTP port onlocal hostwith default username and no password:

matterclickhouse_connect

client=clickhouse_connect.get_customer()
client.server_version
For[2]: '22.10.1.98'

• Connection to an external secure ClickHouse server (https)

matterclickhouse_connect

client=clickhouse_connect.get_customer(host='play.clickhouse.com',safe=TRUE,porta=443,of the user='play',password='house of clicks')
client.domain('Select time zone()')
For[2]: 'Etc/UTC'

• Connection with a session ID and other custom connection parameters and ClickHouse settings.

matterclickhouse_connect

client=clickhouse_connect.get_customer(host='play.clickhouse.com',
of the user='play',
password='house of clicks',
porta=443,
session id='example_session_1',
connect_timeout=15,
database='github',
settings={'distribuido_ddl_task_timeout':300)
client.database
For[2]: 'github'

Common method arguments​

Multiple client methods use one or both of the common methodsparametersmisettingsarguments These keyword arguments are described below.

Parameter argument​

Cliente ClickHouse Connectconsultation*midomainmethods accept an optionalparameterskeyword argument used to bind Python expressions to a ClickHouse value expression. There are two types of binding available.

server side binding​

Click House Supportserver side bindingfor most query values, where the bound value is sent separately from the query as an HTTP query parameter. ClickHouse Connect will add the appropriate query parameters if it detects a binding expression of the form {<name>:<data type>}. For server-side binding, theparametersThe argument must be a Python dictionary.

• Server-side binding with python dictionary, datetime value, and string value

matterDate and Time

my meeting=Date and Time.Date and Time(2022, 10, 01, 15, 20, 5)

parameters= {'low hill': 'my table',v1': my meeting, 'v2': "a string with single quotes""}
client.consultation('SELECT * FROM {table:Identifier} WHERE data >= {v1:DateTime} AND string ILIKE {v2:String}',parameters=parameters)

# Generate the following query on the server
# SELECT * FROM my_table WHERE date >= '2022-10-01 15:20:05' AND string ILIKE 'uma string com aspas simples\''

Client side connection​

ClickHouse Connect also supports client-side parameter binding, which can allow for more flexibility in model SQL query generation. For the client-side binding, theparametersThe argument must be a dictionary or a string. Client side link uses pythonprintf stylestring format for parameter substitution.

Note that, unlike server-side binding, client-side binding does not work for database identifiers, such as database, table, or column names, since Python-style formatting does not you can distinguish between the different types of strings and they must have a different format ( backticks or double quotes for database identifiers, single quotes for data values).

• Example with python dictionary, datetime value, and string escape

matterDate and Time

my meeting=Date and Time.Date and Time(2022, 10, 01, 15, 20, 5)

parameters= {'v1':my meeting, 'v2': "a string with single quotes"}
client.consultation('SELECT * FROM some_table WHERE data >= %(v1)s AND string ILIKE %(v2)s',parameters=parameters)

# Generate the following query:
# SELECT * FROM some_table WHERE data >= '2022-10-01 15:20:05' AND string ILIKE 'uma string com aspas simples\''

• Example with Python Sequence (Tuple), Float64 and IPv4Address

matterIP adress

parameters= (35200,44,IP adress.IPv4 address(0x443d04fe))
client.consultation('SELECT * FROM some_table WHERE metric >= %s AND ip_address = %s',parameters=parameters)

# Generate the following query:
# SELECT * FROM some_table WHERE metric >= 35200.44 AND ip_address = '68.61.4.254''

configuration argument​

All SQL Client ClickHouse main database server accepts an optionsettingskeyword argument used to pass the ClickHouse serverUser settingsfor the included SQL statement. EITHERsettingsargument must be a dictionary. Each element must be a ClickHouse configuration name and its associated value. Note that the values ​​will be converted to strings when sent to the server as query parameters.

As with client-level settings, ClickHouse Connect will clear any settings that the server marks asjust reading=1, with an associated log message. Settings that only apply to queries through the ClickHouse HTTP interface are always valid. These settings are described in theget_customer API.

Example of using the ClickHouse configuration:

settings= {'merge_tree_min_rows_for_concurrent_read': 65535,
 'session id': 'sesion_1234',
 'use_skip_indexes': FALSE}
client.consultation("SELECT event_type, sum(timeout) FROM event_errors WHERE event_time > '2022-08-01'",settings=settings) 

ClientdomainMethod​

use theclient.commandmethod for submitting SQL queries to ClickHouse Server that normally return no data or return a simple single value instead of an entire data set. This method takes the following parameters:

• domaincan be used for DDL statements

client.domain('CREATE TABLE test_command (col_1 String, col_2 DateTime) Mecanismo MergeTree ORDER BY tuple()')
client.domain('SHOW CREATE TABLE test_command')
For[6]: 'CREATE TABLE default.test_command\\n(\\n `col_1` String,\\n `col_2` DateTime\\n)\\nENGINE = MergeTree\\nORDER BY tuple()\\nSETTINGS index_granularity = 8192'

• domaincan also be used for simple queries that only return a single row

result=client.domain('SELECT count() FROM system.tables')
result
For[7]: 110

ClientconsultationMethod​

Oconsult.the.customerThe method is the primary way to retrieve a single "batch" data set from ClickHouse Server. It uses the NativeClickHouse format over HTTP to stream large data sets (up to about a million rows) efficiently. This method takes the following parameters.

The QueryResult object​

a basisconsultationThe method returns a QueryResult object with the following public properties:

• result_rows-- An array of the returned data in the form of a Rowstring, where each row element is a sequence of column values.
• result_columns-- An array of the returned data in the form of a Sequence of Columns, where each element in the column is a sequence of the row values ​​for that column
• column_names-- A tuple of strings representing the names of the columns in theresult_set
• column_types-- A tuple of ClickHouseType instances representing the ClickHouse data type for each column in theresult_columns
• query_id-- The ClickHouse query_id (useful for examining the query in thesystem.query_loglow hill)
• summary-- Any data returned by theX-ClickHouse-ResumenHTTP response header
• first element-- A convenient property to retrieve the first line of the response as a dictionary (keys are column names)
• first line-- A convenience property to return the first row of the result
• column_block_stream-- A query result generator in column-oriented format. This property must not be referenced directly (see below).
• row_block_flow-- A query output generator in line-oriented format. This property must not be referenced directly (see below).
• row_flow-- A query result generator that produces a single row per call. This property must not be referenced directly (see below).

O*_flowThe properties return a Python context that can be used as an iterator for the returned data. They can only be accessed indirectly using the Client.*_flowmethods. In a future release, the QueryResult object returned by the main clientconsultationthe method will have consumed the stream and will contain all the populated contentresult_setto provide a clear separation between full "batch" results retrieved via the clientconsultationmethod and transmission of retrieved results via theClientquery_*_flowmethods.

Full details of the streaming query results (using StreamContext objects) are described inAdvanced queries (streaming queries).

Note: The streaming behavior from v0.5.0-v0.5.3 that uses the QueryResult object as a Python context has been deprecated in v0.5.4 and will be removed in a future release. The QueryResult methodsstream_column_blocks,stream_row_blocks,mistream_rowsthey should not be used and are included for backwards compatibility only.

Specialized Customer Inquiry Methods​

There are three specialized versions of the mainconsultationmethod:

• query_np-- This version returns a Numpy Array instead of a ClickHouse Connect QueryResult.
• query_df-- This version returns a Pandas dataframe instead of a ClickHouse Connect query result.
• query_arrow-- This version returns a PyArrow table. Use ClickHouseSetaformat directly, then accept only three arguments in common with the mainquery method:consultation,parameters, misettings. Furthermore, there is an additional argumentuse_stringswhich determines whether the arrow table will represent ClickHouseString types as strings (if true) or bytes (if false).

Client Stream Query Methods​

ClickHouse Connect Client provides several methods to retrieve data as a stream (implemented as a Python generator):

• query_column_block_stream-- Return query data in blocks as a sequence of columns using the native Python object
• query_column_rows_stream-- Returns the query data as a block of rows using a native Python object
• query_rows_stream-- Returns the query data as a linear string using the native Python object
• query_np_stream-- Returns each ClickHouse query data block as a Numpy array
• query_df_stream-- Return each ClickHouse block of query data as a Pandas dataframe

Each of these methods returns acontext flowobject that must be opened through acomto start consuming the stream. SeeAdvanced queries (streaming queries)for details and examples.

ClientinsertMethod​

For the common use case of inserting multiple records into ClickHouse, there is theclient.insertmethod. It takes the following parameters:

This method does not return a value. An exception will be thrown if the insert fails for any reason.

There are two specialized versions of the mainconsultationmethod:

• insert_df-- Instead of Python Sequence of Sequencesdataargument, the second parameter of this method requires ad.f.argument that must be a Pandas Dataframe instance. ClickHouse Connect automatically renders the data frame as a column-oriented data source, so thecolumn_orientedthe parameter is not required or is not available.
• insert_arrow-- Instead of a Python sequence of sequencesdataargument, this method requires aarrow_table.ClickHouseConnect passes the Arrow table unchanged to the ClickHouse server for processing, so only thedatabasemisettingsArguments are available in addition tolow hillmiarrow_table.

Observation:A Numpy array is a valid sequence of sequences and can be used asdatamain argumentinsertmethod, so a specialized method is not required.

file inserts​

Oclickhouse_connect.controller.toolsincludes theinsert_filemethod that allows you to insert data directly from the file system into an existing ClickHouse table. The analysis is delegated to the ClickHouse server.insert_fileaccepts the following parameters:

For files with inconsistent data or date/time values ​​in an unusual format, settings that apply to data imports (such asinput_format_allow_errors_nummiinput_format_allow_errors_num) are recognized by this method.

matterclickhouse_connect
ofclickhouse_connect.conductor.toolsmatterinsert_file

client=clickhouse_connect.get_customer()
insert_file(client, 'example_table', 'mis_datos.csv',
settings={'input_format_allow_errors_ratio': .2,
 'input_format_allow_errors_num': 5})

Raw API​

For use cases that do not require transformation between ClickHouse data and native or third-party data types and structures, the ClickHouse Connect client provides two methods for direct use of the ClickHouse connection.

Clientraw_queryMethod​

Oclient.raw_queryThe method allows direct use of ClickHouse's HTTP query interface via the client connection. The return value is a raw valuebytesobject. It provides a convenient wrapper with parameter binding, error handling, retries, and configuration management using a minimal interface:

It is the caller's responsibility to handle the result.bytesobject. Please note that theCustomer.query_arrowit's just a wrapper around this method using ClickHouseSetaOutput format.

Clientraw_insertMethod​

OCliente.raw_insertThe method allows direct insertions ofbytesobjects orbytesobject generators using the client connection. Since it does not process the payload of the insert, it has high performance. The method provides options for specifying settings and entering the format:

It is the responsibility of the caller that theinsert_blockis in the specified format. ClickHouse Connect uses these rawinserts to load PyArrow files and tables, delegating the analysis to the ClickHouse server.