当前位置: 首页 > 工具软件 > clickhouse-go > 使用案例 >

列式存储ClickHouse(二)接口

濮阳繁
2023-12-01


Click提供了丰富的接口来访问数据库以及数据库管理系统,如下表:

接口类型描述
httpClickHouse默认提供了HTTP接口,通过http进行数据库和数据库管理系统相关操作。ClickHouse默认监听8123端口(可修改),http接口比其他接口受到更多的限制,但是兼容性更好,文档齐全,上手简单
tcp用于命令行客户端通信,集群服务器之间的通信,以及其他C++程序
command-line通过命令行提供数据库和数据库管理系统相关操作
jdbc包括官方JDBC驱动,和第三方的JDBC驱动,官方JDBC驱动只支持部分功能。第三方的JDBC驱动包括ClickHouse-Native-JDBC,clickhouse4j
odbc官方提供ODBC驱动,可以如同数据源一样访问ClickHouse
c++ client libary提供了C++客户端clickhouse-cpp
Third Party第三方开发者提供了基于Python,Go,Php,nodeJS,Perl,Ruby,R,Java,Scala,Kotlin,C#,Elixir,Nim等语言的客户端库,但是官方并没有针对每一个库进行测试,不保证质量。第三方同样提供了和其他组件如Hadoop,Kafka,MySQL,PostgreSQL,Flink,S3,Prometheus等的集成库,同样有多个可视化的接口供大家选择如Tabix,HouseOps,LightHouse等,同时第三方还提供了丰富的代理服务器
MySQLClickHouse默认提供了MySQL服务,通过MySQL客户端访问数据库,进行数据库管理

下面重点学习一下HTTP接口,JDBC接口,命令行接口,MySQL接口

HTTP

ClickHouse默认监听8123端口提供http服务,可以在config.xml中修改配置。
测试接口

# curl http://127.0.0.1:8123/
Ok.

健康状态检查

# curl http://127.0.0.1:8123/ping
Ok.

可以作为url查询参数,或者post消息体的方式发送请求。在POST发送请求是,在url的query参数中传递查询的开头,在POST消息体中传递其余参数。url的大小限制为最大16KB,因此在发送大型查询是,要使用POST的方式。
如果发送成功,返回200状态码和结果,如果查询错误返回500错误码和错误描述文本。
使用GET方法时,设置为只读,只能查询数据。对于修改数据或其他配置的请求,只能通过POST的方式发送请求。POST请求URL传递参数,或者消息体中传递查询参数。
如:
GET,查询events全部数据,sql为:select * from test1.events

# curl http://127.0.0.1:8123/?query=select%20*%20from%20test1.events
1	1	2020-10-11	西安	违法停车	1001
3	2	2020-10-12	上海	超速50%	1003
4	2	2020-10-20	大连	占用应急车道	1004
2	1	2020-09-01	北京	闯红灯	1002
5	1	2020-09-30	深圳	逆行	1002

GET更新数据,SQL为:insert into test1.events(id, status,eventDate, summary, description, userId)values(6,1,‘2020-11-01’,‘深圳’,‘酒驾’,1004)
报错

# curl 'http://127.0.0.1:8123/?query=insert%20into%20test1.events(id,status,eventDate,summary,description,userId)values(6,1,%272020-11-01%27,%27%E6%B7%B1%E5%9C%B3%27,%27%E9%85%92%E9%A9%BE%27,1004)'
Code: 164, e.displayText() = DB::Exception: default: Cannot execute query in readonly mode (version 20.10.3.30 (official build))

POST,创建表user1

# curl --location --request POST 'http://127.0.0.1:8123/' --data-binary 'create table user1(id UInt64, username String)engine=MergeTree() PARTITION BY id ORDER BY ( intHash32(id)) SAMPLE BY intHash32(id) SETTINGS index_granularity = 8192'
# curl --location --request POST 'http://127.0.0.1:8123/' --data-binary 'show tables from test1'
events
user
user1

POST分别在URL和消息体中传递参数,关联查询user和events表
SQL为: select * from test1.events t1 left join test1.user on t1.userId = t2.id

# curl --location --request POST 'http://127.0.0.1:8123/?query=select%20*%20from' --header 'Content-Type: text/plain' --data-binary 'test1.events t1 left join test1.user t2 on t1.userId = t2.id'
2	1	2020-09-01	北京	闯红灯	1002	1002	王二
5	1	2020-09-30	深圳	逆行	1002	1002	王二
1	1	2020-10-11	西安	违法停车	1001	1001	张一
3	2	2020-10-12	上海	超速50%	1003	1003	李三
4	2	2020-10-20	大连	占用应急车道	1004	1004	赵四

读取表的内容,因为并行处理查询,数据以随机的顺序输出。

数据分隔符
默认结果用制表符分隔,结果展现形式可以参考Format章节,如果要指定分隔符,可以用Format命令,也可以在http请求url中传递default_format参数,或者在http请求Header中添加X-ClickHouse-Format来指定出制表符外的其他分隔符。
数据库名称
同时,也可以在http请求url中传递default_database参数,或者在http请求Header中添加X-ClickHouse-Database来指定数据库。默认情况下使用‘default’数据库,也可以在表情之前使用db.来指定数据库
用户名密码
可以通过三种方式指定用户名密码

  1. 使用http协议基础验证
  2. 通过url参数传递username和password
  3. 在http请求头添加X-ClickHouse-User和X-ClickHouse-Key完成验证
    如果没有指定用户名,则使用default用户名,如果没有指定密码,使用空密码。
    可以在url请求参数中指定各种各样的参数,如:

:http://localhost:8123/?profile=web&max_rows_to_read=1000000000&query=SELECT 1

session
也可以在http请求中使用session,只需要将session_id添加到GET请求参数中,可以使用任何String类型参数作为sessionID,默认情况下,session会在等待60秒后失效,想要修改超时时间,修改服务器配置中的default_session_timeout参数,或者将session_timeout参数添加到GET请求参数中,要检查session状态,使用session_check=1参数来查询。

JDBC

包括官方driver和第三方的ClickHouse-Native-JDBC和clickhouse4j三个JDBC驱动。

官方driver

clickhouse基本的、受限的jdbc驱动实现,支持小的可用功能子集。需要jdk1.6或者更高版本。
使用
maven依赖

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.2.4</version>
</dependency>

url连接:

jdbc:clickhouse://<host>:<port>[/<database>],如:jdbc:clickhouse://192.168.10.63:8123/test1

JDBC驱动类:

ru.yandex.clickhouse.ClickHouseDriver

如果有多个实例,使用BalancedClickhouseDataSource
扩展API
官方driver同时提供了专有的API,ClickHouseStatement,可以导入数据,发送配置,发送用户自定义回调的二进制数据等。

ClickHouse-Native-JDBC

使用Java访问clickhouse的Native JDBC,同时也提供了和Apache Spark集成的库。需要Java 8 or Java 11。
JDBC驱动
与官方不同的是:

  • 根据列组织压缩数据
  • 使用tcp协议实现,比http性能更高
    限制:
  • 不支持非值格式
  • 不支持复杂的表达式,如

insert into test1.user values(1, toDate(‘2020-11-01’))

  • 不支持更多的压缩方法,如ZSTD
    使用
    maven依赖
<!-- (recommended) shaded version, available since 2.3-stable -->
<dependency>
    <groupId>com.github.housepower</groupId>
    <artifactId>clickhouse-native-jdbc-shaded</artifactId>
    <version>${clickhouse-native-jdbc.version}</version>
</dependency>

<!-- normal version -->
<dependency>
    <groupId>com.github.housepower</groupId>
    <artifactId>clickhouse-native-jdbc</artifactId>
    <version>${clickhouse-native-jdbc.version}</version>
</dependency>

查询:

Connection connection = DriverManager.getConnection("jdbc:clickhouse://127.0.0.1:9000");
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT (number % 3 + 1) as n, sum(number) FROM numbers(10000000) GROUP BY n");
while (rs.next()) {
    System.out.println(rs.getInt(1) + "\t" + rs.getLong(2));
}

DDL,DML:

Connection connection = DriverManager.getConnection("jdbc:clickhouse://127.0.0.1:9000");
Statement stmt = connection.createStatement();
stmt.executeQuery("drop table if exists test_jdbc_example");
stmt.executeQuery("create table test_jdbc_example(day default toDate(toDateTime(timestamp)), timestamp UInt32, name String, impressions UInt32) Engine=MergeTree()");
stmt.executeQuery("alter table test_jdbc_example add column costs Float32");
stmt.executeQuery("drop table test_jdbc_example");

批量插入

Connection connection = DriverManager.getConnection("jdbc:clickhouse://127.0.0.1:9000");
PreparedStatement pstmt = connection.prepareStatement("INSERT INTO test_jdbc_example VALUES(?, ?, ?)");
for (int i = 0; i < 200; i++) {
    pstmt.setDate(1, new Date(System.currentTimeMillis()));
    pstmt.setString(2, "Zhang San" + i);
    pstmt.setByte(3, (byte)i);
    pstmt.addBatch();
}
pstmt.executeBatch();
stmt.executeQuery("drop table test_jdbc_example");

集成Spark
Java 8, Scala 2.11, Spark 2.4.x,Spark 2.3.x
maven

<dependency>
    <groupId>com.github.housepower</groupId>
    <artifactId>clickhouse-integration-spark_2.11</artifactId>
    <version>${clickhouse-native-jdbc.version}</version>
</dependency>

确认在使用ClickHouseDialects之前先注册ClickHouseDialects

JdbcDialects.registerDialect(ClickHouseDialect)

从clickhouse读数据到DataFrame

val df = spark.read
  .format("jdbc")
  .option("driver", "com.github.housepower.jdbc.ClickHouseDriver")
  .option("url", "jdbc:clickhouse://127.0.0.1:9000")
  .option("user", "default")
  .option("password", "")
  .option("dbtable", "db.test_source")
  .load

clickhouse4j

官方ClickHouse JDBC驱动更轻量和快速替代方法,需要Java8以上版本
和官方jdbc驱动的区别是

  • 移除了Guava,Jackson和Apache Http Client依赖
  • 体积更小,此依赖只有850kb,而官方需要5.6MB,小了七倍
  • 进行了一些小的优化
  • 添加CopyManager
  • 支持JSON,JSONCompact查询
  • 针对Java8和其他东西进行编译
    依赖
<dependency>
    <groupId>cc.blynk.clickhouse</groupId>
    <artifactId>clickhouse4j</artifactId>
    <version>1.4.4</version>
</dependency>

从官方升级

  • JDBC驱动由ru.yandex.clickhouse.ClickHouseDriver变为cc.blynk.clickhouse.ClickHouseDriver
  • URL变为: jdbc:clickhouse://<host>:<port>[/<database>],如:jdbc:clickhouse://192.168.10.63:8123/test
    使用CopyManager
    CopyManager用来从文件,流,reader来读、写数据
    从数据库查询数据并写到文件
String query = "SELECT * from copy_manager_test.my_table FORMAT CSVWithNames";
Path outputFile = ...;
try (CopyManager copyManager = CopyManagerFactory.create(dataSource)) {
    copyManager.copyFromDb(query, outputFile);
}
//outputFile now has all the data and headers from the copy_manager_test DB and my_table table

使用preparedStatement从数据库查询数据并写到文件

try (Connection connection = dataSource.getConnection();
     PreparedStatement ps = connection.prepareStatement(sql);
     CopyManager copyManager = CopyManagerFactory.create(connection)) {
        ps.setLong(1, id);
        copyManager.copyFromDb(ps, outputStream);
}

从文件插入数据到数据库

String query = "INSERT INTO copy_manager_test.my_table FORMAT CSV";
Path inputFile = ...;

try (CopyManager copyManager = CopyManagerFactory.create(dataSource)) {
    copyManager.copyToDb(query, inputFile);
}

//DB copy_manager_test and my_table table now has all csv data from the inputFile

Command Line

ClickHouse提供了本地的命令行客户端clickhouse-cli,客户端支持命令行参数和配置文件。使用clienthouse-client命令打开命令行客户端,上一篇已经测试过了,这里不再举例。
客户端不同的版本和不同的server版本兼容,但是旧的版本客户端不能使用一些新的功能。建议使用服务端版本对应的客户端,如果版本不对应,clienthouse也会给出响应的提示。
使用
客户端可以再交互模式和非交互模式的批处理模式下使用。
非交互模式下使用如:

# clickhouse-client --database=test --query="insert into user(id,username)values('1005','钱五')"
#cat file.csv | clienthouse-client --database=test --query="insert into test FORMAT CSV"

在批处理模式下,默认只能处理单查询。可以使用–mutiquery参数,在一个脚本执行多个查询,但是不支持insert操作,查询结果连续输出,没有附加的分隔符。同时处理大量的查询,也可以为每一个查询运行clickhouse-client命令。
在交互模式下,clickhouse-client提供了一个交互式的命令行,来执行SQL查询。客户端由两种模式,多行模式和非多行模式(默认),多行模式下,语句以分号结尾,加回车键执行。在非多行模式下,语句不用使用分号结尾,直接回车执行,如果在非多行模式下,有多行的SQL需要执行,需要在行后加反斜杠\。
可以指定\G参数,如同MySQL一样,让每一行按照列的形式打印,对于宽表数据展示比较直观。
当执行查询是,clickhouse-client提供了一下信息

  1. 进度,大概一秒钟刷新10次,对于非常快的查询(小于100ms),进度没有时间展示
  2. 格式化的查询语句,帮助你debug,查找问题。
  3. 指定格式的结果
  4. 结果的行数,消耗时间,查询处理平均速度
    命令行客户端可以使用外部数据进行查询,后面会逐渐了解到。
    使用参数查询
    clickhouse-client支持传递动态参数查询,如:
# clickhouse-client --param_paraname="[1001,1002]" --query "select * from test1.events where userId in {paraname:Array(UInt32)}"
1	1	2020-10-11	西安	违法停车	1001
2	1	2020-09-01	北京	闯红灯	1002
5	1	2020-09-30	深圳	逆行	1002
  • 首先通过–param_<name>=value的格式传递参数,参数名称为name
  • 在查询语句中通过{<name>:<data type>}的格式使用,其中name为参数名称,data type为数据类型
    数据类型可以是复杂的数据类型,如:
# clickhouse-client --param_tuple_in_tuple="(10, ('dt', 10))" -q "SELECT * FROM table WHERE val = {tuple_in_tuple:Tuple(UInt8, Tuple(String, UInt8))}"

clickhouse-client命令的参数如下:

  • –host, -h – 服务器名称,默认为localhost,可以指定IPV4或者IPV6地址
  • –port – 要连接的clickhouse服务端口,默认为9000。PS:http接口和Native接口使用不同的端口
  • –user, -u – 用户名,默认为default.
  • –password – 密码,默认为空字符串
  • –query, -q – 使用批处理模式下,要执行的查询
  • –database, -d – 指定要执行查询的数据库,默认为default
  • –multiline, -m – 指定允许多行查询,按enter不执行,需要分号+Enter才执行
  • –multiquery, -n – 指定执行使用分隔相隔的多个查询
  • –format, -f – 指定输出结果的格式
  • –vertical, -E – 指定使用垂直格式输出结果,和-format=Vetical一样,在这种格式中,每个值单独占一行,展示宽表非常直观
  • –time, -t – 在批处理模式下打印执行时间.
  • –stacktrace – 在异常情况下,打印堆栈信息.
  • –config-file – 配置文件名称
  • –secure – 通过安全连接连接到服务器
  • –param_ — 动态查询参数

MySQL

ClickHouse支持MySQL通信协议,MySQL接口在配置文件中使用mysql_port来生效
连接客户端使用mysql命令行工具,如下:

# mysql --protocol tcp -u default -P 9004
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 20.10.3.30-ClickHouse 0

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------------------+
| name                           |
+--------------------------------+
| _temporary_and_external_tables |
| default                        |
| system                         |
| test                           |
| test1                          |
+--------------------------------+
5 rows in set (0.00 sec)
Read 5 rows, 734.00 B in 0.00058118 sec., 8603 rows/sec., 1.20 MiB/sec.

mysql> use test1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from events;
+------+--------+------------+---------+--------------------+--------+
| id   | status | eventDate  | summary | description        | userId |
+------+--------+------------+---------+--------------------+--------+
|    2 |      1 | 2020-09-01 | 北京    | 闯红灯             |   1002 |
|    5 |      1 | 2020-09-30 | 深圳    | 逆行               |   1002 |
|    1 |      1 | 2020-10-11 | 西安    | 违法停车           |   1001 |
|    3 |      2 | 2020-10-12 | 上海    | 超速50%            |   1003 |
|    4 |      2 | 2020-10-20 | 大连    | 占用应急车道       |   1004 |
+------+--------+------------+---------+--------------------+--------+
5 rows in set (0.00 sec)
Read 5 rows, 269.00 B in 0.001195306 sec., 4183 rows/sec., 219.77 KiB/sec.

为了兼容所有MySQL客户端兼容,建议在配置文件中使用双sha1指定用户名密码。如果使用sha256指定用户密码,某些客户端将无法进行身份验证。(如:mysqljs和旧版本的mysql命令行工具)
限制条件

  • 不支持预编译 PreparedStatement
  • 一些数据类型作为字符串形式发送

(完)(^_^)

 类似资料: