ClickHouse Connector

Overview

The ClickHouse connector allows querying on an external ClickHouse database. This can be used to join data between different systems like ClickHouse and Hive, or between two different ClickHouse instances.

Configuration

Basic configuration

To configure the ClickHouse connector, create a catalog properties file inetc/catalog named, for example, clickhouse.properties, to mount the ClickHouse connector as the clickhouse catalog. Create the file with the following contents, replacing the connection properties as appropriate for your setup.

Base property setting:

connector.name=clickhouse
connection-url=jdbc:clickhouse://example.net:8123
connection-user=username
connection-password=yourpassword
  • Allow ClickHouse connector to drop table or not
allow-drop-table=true
  • Enable the query push down feature or not

The push down feature of ClickHouse connector is turn on by default, and you can also set as below:

clickhouse.query.pushdown.enabled=true
  • Table name is case sensitive or not.

The syntax of ClickHouse is case sensitive. If there are uppercase fields in your database table, you can set them as follows.

case-insensitive-name-matching=true

Multiple ClickHouse Servers

You can have as many catalogs as you need, so if you have additional ClickHouse servers, simply add another properties file to etc/catalog with a different name (making sure it ends in .properties). For example, if you name the property file clickhouse2.properties, openLooKeng will create a catalog named clickhouse2 using the configured connector.

Querying ClickHouse through openLooKeng

The ClickHouse connector provides a schema for every ClickHouse database. You can see the available ClickHouse databases by running SHOW SCHEMAS:

SHOW SCHEMAS FROM clickhouse;

If you have a ClickHouse database named data, you can view the tables in this database by running SHOW TABLES:

SHOW TABLES FROM clickhouse.data;

You can see a list of the columns in the hello table in the data database using either of the following:

DESCRIBE clickhouse.data.hello;
SHOW COLUMNS FROM clickhouse.data.hello;

Finally, you can also access the hello table in the data database:

SELECT * FROM clickhouse.data.hello;

If you used a different name for your catalog properties file, use that catalog name instead of clickhouse in the above examples.

Mapping Data Types Between openLooKeng and ClickHouse

ClickHouse-to-openLooKeng Type Mapping

openLooKeng support selecting the following ClickHouse Detabase types. The table shows the mapping from ClickHouse data type.

Data type projection table:

ClickHouse typeopenLooKeng type
Int8TINYINT
Int16SMALLINT
Int32INTEGER
Int64BIGINT
float32REAL
float64DOUBLE
DECIMAL(P,S)DECIMAL(P,S)
DECIMAL32(S)DECIMAL(P,S)
DECIMAL64(S)DECIMAL(P,S)
DECIMAL128(S)DECIMAL(P,S)
StringVARCHAR
DateTimeTIME
Fixedstring(N)CHAR
UInt8SMALLINT
UInt16INT
UInt32BIGINT
UInt64NA
Int128,Int256,UInt256NA

openLooKeng-to-ClickHouse Type Mapping

openLooKeng support creating tables with the following type into a ClickHouse Database. The table shows the mapping from openLooKeng to ClickHouse data types.

openLooKeng typeClickHouse type
BOOLEANInt8
TINYINTInt8
SMALLINTInt16
INTEGERInt32
BIGINTInt64
REALfloat32
DOUBLEfloat64
DECIMAL(P,S)DECIMAL(P,S)
varcharString
varchar(n)String
CHAR(n)FixedString(n)
VARBINARYString
JSONNA
DATEDate
TIMEDateTime
TIME WITH TIME ZONENA
TIMESTAMPTIMESTAMP
TIMESTAMP WITH TIME ZONENA

Functions that support pushdown

Note: The "$n" is placeholder to present an argument in a function.

Aggregate Functions

count($1)
min($1)
max($1)
sum($1)
avg($1)
CORR($1,$2)
STDDEV($1)
stddev_pop($1)
stddev_samp($1)
skewness($1)
kurtosis($1)
VARIANCE($1)
var_samp($1)

Math functions

ABS($1)
ACOS($1)
ASIN($1)
ATAN($1)
ATAN2($1,$2)
CEIL($1)
CEILING($1)
COS($1)
e()
EXP($1)
FLOOR($1)
LN($1)
LOG10($1)
LOG2($1)
MOD($1,$2)
pi()
POW($1,$2)
POWER($1,$2)
RAND()
RANDOM()
ROUND($1)
ROUND($1,$2)
SIGN($1)
SIN($1)
SQRT($1)
TAN($1)

Functions for Working with Strings

CONCAT($1,$2)
LENGTH($1)
LOWER($1)
LTRIM($1)
REPLACE($1,$2)
REPLACE($1,$2,$3)
RTRIM($1)
STRPOS($1,$2)
SUBSTR($1,$2,$3)
POSITION($1,$2)
TRIM($1)
UPPER($1)

Functions for Working with Dates and Times

YEAR($1)
MONTH($1)
QUARTER($1)
WEEK($1)
DAY($1)
HOUR($1)
MINUTE($1)
SECOND($1)
DAY_OF_WEEK($1)
DAY_OF_MONTH($1)
DAY_OF_YEAR($1)

Note: The functions supported by openLooKeng can also be used in the ClickHouse connector, but functions not in the above list will not be pushed down.

ClickHouse Connector Limitations

Syntax

CREATE TABLE statement is not supported.

The INSERT statement needs to use CAST, for example, the data type in the table_name_test table is smallint:

insert into table_name_test values (cast(1 as small int));

The ClickHouse syntax supports the use of aliases in where clauses, but not in openLooKeng.

Type

Types such as uuid in ClickHouse are not supported, and all supported types are listed in the mapping table.

有奖捉虫

“有虫”文档片段

0/500

存在的问题

文档存在风险与错误

● 拼写,格式,无效链接等错误;

● 技术原理、功能、规格等描述和软件不一致,存在错误;

● 原理图、架构图等存在错误;

● 版本号不匹配:文档版本或内容描述和实际软件不一致;

● 对重要数据或系统存在风险的操作,缺少安全提示;

● 排版不美观,影响阅读;

内容描述不清晰

● 描述存在歧义;

● 图形、表格、文字等晦涩难懂;

● 逻辑不清晰,该分类、分项、分步骤的没有给出;

内容获取有困难

● 很难通过搜索引擎,openLooKeng官网,相关博客找到所需内容;

示例代码有错误

● 命令、命令参数等错误;

● 命令无法执行或无法完成对应功能;

内容有缺失

● 关键步骤错误或缺失,无法指导用户完成任务,比如安装、配置、部署等;

● 逻辑不清晰,该分类、分项、分步骤的没有给出

● 图形、表格、文字等晦涩难懂

● 缺少必要的前提条件、注意事项等;

● 描述存在歧义

0/500

您对文档的总体满意度

非常不满意
非常满意

请问是什么原因让您参与到这个问题中

您的邮箱

创Issue赢奖品
根据您的反馈,会自动生成issue模板。您只需点击按钮,创建issue即可。
有奖捉虫