ClickHouse-Native-JDBC 使用hikariCP+Spring jdbcTemplate

简介

ClickHouse 通过两种方式进行通讯,一种是tcp,一种是http,官方默认推荐使用http方式,而tcp方式更多使用命令行(如:clickhouse-client)或者一些三方工具；但目前数据接入等方式可能由于某种需求通过程序进行接入,但http方式不适合大量数据库段时间灌入，效率不如tcp,官方默认是http的jdbc。
github上有个开源的项目。
ClickHouse-Native-JDBC
与http方式性能对比。
https://github.com/housepower/ClickHouse-Native-JDBC/blob/master/docs/dev/benchmark.md

集成方式

默认Java原生方式

官方示例
关键代码:

• 批量

   try (Connection connection = DriverManager.getConnection("jdbc:clickhouse://127.0.0.1:9000")) {
          try (Statement stmt = connection.createStatement()) {
              try (ResultSet rs = stmt.executeQuery("drop table if exists test_jdbc_example")) {
                  System.out.println(rs.next());
              }
              try (ResultSet rs = stmt.executeQuery("create table test_jdbc_example(day Date, name String, age UInt8) Engine=Log")) {
                  System.out.println(rs.next());
              }
              try (PreparedStatement pstmt = connection.prepareStatement("INSERT INTO test_jdbc_example VALUES(?, ?, ?)")) {
                  for (int i = 1; i <= 200; i++) {
                      pstmt.setDate(1, new Date(System.currentTimeMillis()));
                      if (i % 2 == 0)
                          pstmt.setString(2, "Zhang San" + i);
                      else
                          pstmt.setString(2, "Zhang San");
                      pstmt.setByte(3, (byte) ((i % 4) * 15));
                      System.out.println(pstmt);
                      pstmt.addBatch();
                  }
                  pstmt.executeBatch();
              }
          }
      }

• SQL语句

  try (Connection connection = DriverManager.getConnection("jdbc:clickhouse://127.0.0.1:9000")) {
          try (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(day, (timestamp, name), 8192)");
              stmt.executeQuery("alter table test_jdbc_example add column costs Float32");
              stmt.executeQuery("drop table test_jdbc_example");
          }
      }

• 简单查询

  try (Connection connection = DriverManager.getConnection("jdbc:clickhouse://127.0.0.1:9000")) {
          try (Statement stmt = connection.createStatement()) {
              try (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));
                  }
              }
          }
      }

  HikariCP+Spring JdbcTemplate

  1. 添加maven依赖

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

  2. 添加url连接

     spring.datasource.url=jdbc:clickhouse://127.0.0.1:9000
     spring.datasource.username=default
     spring.datasource.password=
     spring.datasource.driver-class-name=com.github.housepower.jdbc.ClickHouseDriver

  3. 注入JdbcTemplate

     @Autowired
     private JdbcTemplate jdbcTemplate;

  4. 执行语句
     • update语句

       jdbcTemplate.execute("CREATE database test;");
       jdbcTemplate.execute("CREATE table  test.xxxx.............");

     • 批量插入

       List<PhoneApp> list=new ArrayList<>(3);
       //........list.add();
       String sql = "INSERT INTO test.xxxx(name,age) VALUES (?,?)";
       jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter(){
       @Override
       public void setValues(PreparedStatement pstmt, int i) throws SQLException {
           pstmt.setString(1, list.get(i).getName());
           pstmt.setString(2, list.get(i).getAge());
       }
       @Override
       public int getBatchSize() {
           return list.size();
       }
       });

     • 查询语句

       String sql = "select * from test.xxxx where name = ?";
       User User = jdbcTemplate.queryForObject(sql, (resultSet, i) -> {
       User user=new User();
       user.setName(resultSet.getString("name"));
       return user;
       }, "张三");

注意事项

1. hikariCP 对接默认根据JDBC协议进行对接
2. ClickHouse-Native-JDBC 未完全实现JDBC标准接口
3. jdbcTemplate中的部分方法未实现,会报noSupport相关错误
4. 尽量使用ClickHouse-Native-JDBC 来做入库等高频量大得操作