列式存储ClickHouse（二）接口

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

接口类型	描述
http	ClickHouse默认提供了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等，同时第三方还提供了丰富的代理服务器
MySQL	ClickHouse默认提供了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
• 一些数据类型作为字符串形式发送

(完)(^_^)