from_clickhouse

This reference documents the from_clickhouse operator. You’ll learn how to read table rows, run SQL queries, and inspect metadata in ClickHouse.

from_clickhouse [table=string, sql=string, show=string, database=string,
                 host=string, port=int, user=string, password=string,
                 tls=record]

Description

Use exactly one of table, sql, or show.

Currently, from_clickhouse is available only with the new pipeline executor. Run it with tenzir --neo until it is available in the legacy executor.

table = string

The table to read from.

You can qualify the table as <database>.<table>. If you omit the database, ClickHouse uses the current database.

Use this mode when you want to read a whole table and preserve named tuple fields from the table schema.

sql = string

A custom SQL query to execute.

Use this mode when you want ClickHouse to filter, project, sort, or cast data before Tenzir reads it.

show = string

Shows ClickHouse metadata instead of table rows.

Supported values are:

• "tables": Lists tables in the selected database and yields events with the fields database and table.
• "columns": Lists columns for table and yields events with the fields name and type.

database = string (optional)

The database to use for unqualified table names.

This argument also filters show="tables" to one database. If you specify both database and a database-qualified table, they must match.

host = string (optional)

The hostname for the ClickHouse server.

Defaults to "localhost".

port = int (optional)

The port for the ClickHouse server.

Defaults to 9000 without TLS and 9440 with TLS.

user = string (optional)

The user to use for authentication.

Defaults to "default".

password = string (optional)

The password for the given user.

Defaults to "".

tls = record (optional)

TLS configuration. Provide an empty record (tls={}) to enable TLS with defaults or set fields to customize it.

{
  skip_peer_verification: bool, // skip certificate verification.
  cacert: string,               // CA bundle to verify peers.
  certfile: string,             // client certificate to present.
  keyfile: string,              // private key for the client certificate.
  min_version: string,          // minimum TLS version (`"1.0"`, `"1.1"`, `"1.2"`, "1.3"`).
  ciphers: string,              // OpenSSL cipher list string.
  client_ca: string,            // CA to validate client certificates.
  require_client_cert,          // require clients to present a certificate.
}

The client_ca and require_client_cert options are only applied for operators that accept incoming client connections, and otherwise ignored.

Any value not specified in the record will either be picked up from the configuration or if not configured will not be used by the operator.

See the Node TLS Setup guide for more details.

Types

Tenzir maps ClickHouse types to Tenzir types as follows:

ClickHouse	Tenzir	Comment
Bool	bool
Int8, Int16, Int32, Int64	int64
UInt8, UInt16, UInt32, UInt64	uint64
Float32, Float64	double
String, FixedString(N)	string
UUID	string	Emitted as canonical UUID text.
Enum8, Enum16	string	Emitted as the enum label.
Decimal, Decimal32, Decimal64, Decimal128	string	Emitted as decimal text to preserve precision.
Date, Date32, DateTime, DateTime64	time
IPv4, IPv6	ip
Tuple(...)	record
Array(T)	list<T>
Array(UInt8)	blob
Nullable(T)	T	Null values stay null.

Map(...) is not currently supported. Cast unsupported columns in sql or omit them from the query result.

Some ClickHouse types are not perfect inverses of to_clickhouse. For example, to_clickhouse writes bool as UInt8, so reading the same table back yields uint64 unless the ClickHouse column type is Bool.

Examples

Read all rows from a table

from_clickhouse table="events", tls=false

Run a filtered SQL query

from_clickhouse sql="SELECT * FROM events WHERE severity >= 3 ORDER BY time DESC",
                tls=false

List tables in the current database

from_clickhouse show="tables", tls=false

Show the columns for a table

from_clickhouse show="columns", table="events", tls=false

See Also

• to_clickhouse
• Read from data stores
• ClickHouse