登录
当前位置： 首页 > 工具软件 > 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
  • 一些数据类型作为字符串形式发送

(完)(^_^)

 类似资料：

相关阅读

如何在localStorage中存储阵列？iOS 缓存存储方式有哪些策略？在Elasticsearch中存储日期格式存储大型JSON-二进制或文本接口{}的范围，它存储一个切片

相关文章

ClickHouse-2-安装ClickHouse-1-概述中科曙光存储java二面二叉树的顺序存储结构腾讯存储引擎二面面经

相关问答

利用通用存储库接口谷歌云存储放不存为二进制C#MVC4和EF5中存储库设计模式的接口问题无效的密钥存储格式：java.io.ioException：无效的密钥存储格式RDF4j和GraphDB存储库连接

相关文档

小米对象存储服务 FDS 用户文档Aliyun 阿里云 OSS 对象储存 API 帮助文档Aliyun 阿里云 OSS 对象储存 Java API 开发手册 1.0Aliyun 阿里云 OSS 对象储存 PHP API 开发手册 2.0小米结构化数据存储服务 SDS 开发指南
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.