列式存储ClickHouse（二）接口

深藏阁楼爱情的钟 2022-11-21 04:25 1235阅读 0赞

### 这里写目录标题 ###

*  HTTP
 *  JDBC
 *   *  官方driver
     *  ClickHouse-Native-JDBC
     *  clickhouse4j
 *  Command Line
 *  MySQL

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

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

--------------------

下面重点学习一下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依赖

<dependency>
        <groupId>com.github.housepower</groupId>
        <artifactId>clickhouse-native-jdbc-shaded</artifactId>
        <version>${clickhouse-native-jdbc.version}</version>
    </dependency>
    
    
    <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
 *  一些数据类型作为字符串形式发送

(完)(^\_^)