Steps

1. restart ClickHouse server and sleep 10 seconds for each case
2. execute same case consecutively 5 times with 5-second interval
3. pick the median elapsed time as result

Queries

Clients

• clickhouse-client - ClickHouse native command-line client

  # read
  clickhouse-client --compression=$compression --host=$db_host --port=$db_tcp_port \
      --database="$db_name" --query="$query" --format=$format > $outfile
  # write
  cat $outfile | clickhouse-client --compression=$compression --host=$db_host --port=$db_tcp_port \
   --database=$db_name --query="insert into test_load format $format"

• curl - A command-line tool for transferring data specified with URL syntax

  # read - when compression is 1, compress_header_opt will be "-H 'Accept-Encoding: lz4'"
  curl -s -u "$db_user:$db_passwd" --get --data-urlencode "compress=$compression" \
      --data-urlencode "query=$query" $compress_header_opt \
      -H "X-ClickHouse-Format: $format" "http://$db_host:$db_http_port" > $outfile
  # write - don't use -d @file or it will end up with OOM error
  curl -s -u "$db_user:$db_passwd" -X POST -H "X-ClickHouse-Database: $db_name" -H "Transfer-Encoding: chunked" -T $outfile \
      "http://$db_host:$db_http_port/?query=insert%20into%20test_load%20format%20$format&decompress=$compression"

• Java(dump) - Java client to dump query result without decompression and deserialization

  try (FileOutputStream out = new FileOutputStream(outfile);
      ClickHouseResponse response = request.query(query).format(format.defaultInputFormat())
          .output(out).executeAndWait()) {
      return (int) response.getSummary().getWrittenRows();
  }

• Java(load) - Java client to load data from file without compression and serialization

  try (FileOutputStream out = new FileOutputStream(outfile);
      ClickHouseResponse response = request.query(query).format(format.defaultInputFormat())
          .output(out).executeAndWait()) {
      return (int) response.getSummary().getWrittenRows();
  }

• Java(custom) - Java client with decompression and custom implementation for deserializing RowBinary response

  try (ClickHouseResponse response = request.query(query).executeAndWait();
      ClickHouseInputStream input = response.getInputStream()) {
        int count = 0;
        try (FileOutputStream out = new FileOutputStream(outfile)) {
            byte b = (byte) 0;
            long l = 0L;
            String s = "";
            input.setCopyToTarget(out);
            while (input.available() > 0) {
                b = input.readByte(); // read Int8
                l = input.readBuffer(8).asLong(); // read UInt64
                s = input.readUnicodeString(); // read String
                count++;
            }
            if (count > (0xFF & b) && count > l && count > s.length()) {
                out.flush();
            }
       }
  }

• Java(default) - Java client with decompression and ClickHouseDataProcessor & ClickHouseRecord for deserialization

  try (ClickHouseResponse response = request.query(query).executeAndWait();
      ClickHouseInputStream input = response.getInputStream()) {
        int count = 0;
        byte b = (byte) 0;
        long l = 0L;
        String s = "";
        input.setCopyToTarget(out);
        for (ClickHouseRecord r : response.records()) {
            b = r.getValue(0).asByte();
            l = r.getValue(1).asLong();
            s = r.getValue(2).asString();
            count++;
        }
        if (count > (0xFF & b) && count > l && count > s.length()) {
            out.flush();
        }
  }

• Java(write) - Java client to insert generated data using ClickHouseDataProcess for serialization and ClickHouseWriter for streaming

  try (ClickHouseResponse response = request.write().format(format.defaultInputFormat()).table("test_load").data(o -> {
      ClickHouseDataProcessor processor = ClickHouseDataStreamFactory.getInstance()
          .getProcessor(request.getConfig(), null, o, null, null);
      int len = columns.length;
      ClickHouseValue[] values = new ClickHouseValue[len];
      for (int i = 0; i < len; i++) {
          values[i] = columns[i].newValue(request.getConfig());
      }
  
      int range = len == 1 ? 500000000 : 300000000;
      for (int i = 0; i < range; i++) {
          for (int j = 0; j < len; j++) {
              processor.write(values[j].update(i), columns[j]);
          }
      }
      o.flush();
  }).executeAndWait()) {
      return (int) response.getSummary().getWrittenRows();
  }

• JDBC - JDBC driver with standard API

  // read
  try (Connection conn = driver.connect(url, props); Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(query)) {
      int count = 0;
      byte b = (byte) 0;
      long l = 0L;
      String s = "";
      rs.unwrap(ClickHouseResponse.class).getInputStream().setCopyToTarget(out);
      while (rs.next()) {
          b = rs.getByte(1);
          l = rs.getLong(2);
          s = rs.getString(3);
          count++;
      }
      if (count > (0xFF & b) && count > l && count > s.length()) {
          out.flush();
      }
  }
  
  // write - batch insert
  String insertQuery = "insert into test_load values(?,?,?)"; // or insert into test_load values(?) for other cases
  int range = 300000000; // 500000000 for Int8, UInt64 and String
  try (Connection conn = driver.connect(url, props); PreparedStatement ps = conn.prepareStatement(insertQuery)) {
      int batchSize = 100000;
      int count = 0;
      for (int i = 0; i < range; i++) {
          ps.setByte(1, (byte) (i % 256));
          ps.setLong(2, i);
          ps.setString(3, Integer.toString(i));
          ps.addBatch();
          if ((count = i % batchSize) == 0) {
              ps.executeBatch();
          }
      }
      if (count > 0) {
          ps.executeBatch();
      }
  }